实例：简单的JDBC复习+MySql入门学习
万变不离其宗。。持久层怎么变也是JDBC，框架怎么新也是反射机制。。
?
今天刚好复习一下JDBC，顺便对MySql进行一个入门的学习。
?
环境：MySql 5.5 + Navicat for MySql 10.0.5 + MyEclipse 9.0
?
从MySql官方：http://www.mysql.com/?下载了 mysql-connector-java-5.1.17-bin.jar
?
从MyEclipse的DB Browser中得到测试成功后的
?
驱动类：com.mysql.jdbc.Driver
链接URL：jdbc:mysql://localhost:3306/accp
?
准备的差不多了，实例就是 简单粗暴，直接有效 直接上代码。。
?
---------------------------------------我是华丽的无所不在的分割线-------------------------------------------
?
?
用户实体类：
package com.accp.jdbc.entity;/** * * @author Maxpin on 2011-10-04 * * 用户实体类 */public class Userinfo {private int userid; // 编号private String loginid; // 用户名private String loginpwd; // 密码private String username; // 姓名/** * 构造方法 */public Userinfo() {}/** * @param loginid * @param loginpwd * @param username */public Userinfo(String loginid, String loginpwd, String username) {this.loginid = loginid;this.loginpwd = loginpwd;this.username = username;}/** * @param userid * @param loginid * @param loginpwd * @param username */public Userinfo(int userid, String loginid, String loginpwd, String username) {this.userid = userid;this.loginid = loginid;this.loginpwd = loginpwd;this.username = username;}//getter & setter methods 略??
?
Dao基类：包含了数据库链接、关闭、CRUD操作及MySql分页查询
package com.accp.jdbc.base;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.ArrayList;import java.util.List;import com.accp.jdbc.entity.Userinfo;/** * * @author Maxpin on 2011-10-04 * * Dao基类：包含了数据库链接、关闭、CRUD操作及MySql分页查询 */public class BaseDao {// 连接地址private static final String url = "jdbc:mysql://localhost:3306/accp";// 驱动类private static final String driverClass = "com.mysql.jdbc.Driver";// 用户名private static final String uname = "root";// 密码private static final String pwd = "admin";/** * 获取数据库连接 * * @return 连接对象 */protected static Connection getConnection() {Connection conn = null;try {Class.forName(driverClass);conn = DriverManager.getConnection(url, uname, pwd);} catch (ClassNotFoundException e) {System.out.println("找不到驱动类");} catch (SQLException e) {System.out.println("建立连接错误！");}return conn;}/** * 关闭数据库连接 * * @param conn * 数据库连接 * @param rs * 结果集 * @param pstmt * 命令对象 */public static void closeAll(Connection conn, ResultSet rs, Statement pstmt) {try {if (null != rs && !rs.isClosed()) {rs.close();rs = null;}} catch (SQLException e) {System.out.println("关闭结果集出错！");}try {if (null != pstmt && !pstmt.isClosed()) {pstmt.close();pstmt = null;}} catch (SQLException e) {System.out.println("关闭命令对象出错！");}try {if (null != conn && !conn.isClosed()) {conn.close();conn = null;}} catch (SQLException e) {System.out.println("关闭链接出错");}}/** * 保存指定用户信息 * * @param user * 用户对象 * @throws Exception * 抛出异常 */public static void saveUserinfo(Userinfo user) throws Exception {if (null != user) {Connection conn = getConnection();PreparedStatement pstmt = null;String sql = "insert into USERINFO values(null,?,?,?)";try {pstmt = conn.prepareStatement(sql);pstmt.setString(1, user.getLoginid());pstmt.setString(2, user.getLoginpwd());pstmt.setString(3, user.getUsername());pstmt.executeUpdate();} catch (SQLException e) {e.printStackTrace();} finally {closeAll(conn, null, pstmt);}} else {throw new Exception("用户信息不能为空");}}/** * 删除指定用户信息 * * @param user * 用户对象 * @throws Exception * 抛出异常 */public static void deleteUserinfo(Userinfo user) throws Exception {if (null != user) {Connection conn = getConnection();PreparedStatement pstmt = null;String sql = "delete from USERINFO where userid = ?";try {pstmt = conn.prepareStatement(sql);pstmt.setInt(1, user.getUserid());pstmt.executeUpdate();} catch (SQLException e) {e.printStackTrace();} finally {closeAll(conn, null, pstmt);}} else {throw new Exception("用户信息不能为空");}}/** * 更新指定用户信息 * * @param user * 用户对象 * @throws Exception * 抛出异常 */public static void updateUserinfo(Userinfo user) throws Exception {if (null != user) {Connection conn = getConnection();PreparedStatement pstmt = null;String sql = "update USERINFO set loginid = ?,loginpwd = ?,username = ? where userid = ?";try {pstmt = conn.prepareStatement(sql);pstmt.setString(1, user.getLoginid());pstmt.setString(2, user.getLoginpwd());pstmt.setString(3, user.getUsername());pstmt.setInt(4, user.getUserid());pstmt.executeUpdate();} catch (SQLException e) {e.printStackTrace();} finally {closeAll(conn, null, pstmt);}} else {throw new Exception("用户信息不能为空");}}/** * 查询指定用户信息 * * @param id * 用户编号 * @return 用户对象 * @throws Exception * 抛出异常 */public static Userinfo queryUserinfo(int id) throws Exception {Userinfo user = null;Connection conn = getConnection();PreparedStatement pstmt = null;ResultSet rs = null;String sql = "select * from USERINFO where userid = ?";try {pstmt = conn.prepareStatement(sql);pstmt.setInt(1, id);rs = pstmt.executeQuery();if (rs.next()) {user = new Userinfo(id, rs.getString(2), rs.getString(3),rs.getString(4));}} catch (SQLException e) {e.printStackTrace();} finally {closeAll(conn, rs, pstmt);}return user;}/** * 分页查询用户信息列表 * * @param currentPage * 要查询页码 * @param pageSize * 每页显示条数 * @return 用户对象集合 * @throws Exception * 抛出异常 */public static List<Userinfo> queryUserinfoList(int currentPage, int pageSize)throws Exception {// 计算当前页索引int pageIndex = (currentPage - 1) * pageSize;List<Userinfo> userList = new ArrayList<Userinfo>();Connection conn = getConnection();PreparedStatement pstmt = null;ResultSet rs = null;// MySql分页可使用limit关键字:select * from tableName limit pageIndex,pageSizeString sql = "select * from USERINFO limit ?,?";try {pstmt = conn.prepareStatement(sql);pstmt.setInt(1, pageIndex);pstmt.setInt(2, pageSize);rs = pstmt.executeQuery();while (rs.next()) {userList.add(new Userinfo(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4)));}} catch (SQLException e) {e.printStackTrace();} finally {closeAll(conn, rs, pstmt);}return userList;}}?
?
测试类：
?
package com.accp.jdbc.test;import java.util.List;import com.accp.jdbc.base.BaseDao;import com.accp.jdbc.entity.Userinfo;/** * * @author Maxpin on 2011-10-04 * * 测试类 */public class Test {public static void main(String[] args) {try {/* * MySql中的初始数据：（编号、用户名、密码、姓名） * 1 admin 123123 管理员 * 2 zhangsan 123123 张三 * 3 lisi 123123 李四 * 4 wangwu 123123 王五 * */// 测试保存：赵六BaseDao.saveUserinfo(new Userinfo("zhaoliu", "123123", "赵六"));// 测试更新：赵六BaseDao.updateUserinfo(new Userinfo(5, "zhaoliu", "321321", "赵六2"));// 测试删除：王五BaseDao.deleteUserinfo(new Userinfo(4, null, null, null));// 测试查询：管理员Userinfo user = BaseDao.queryUserinfo(1);System.out.println(user.getUserid() + " " + user.getLoginid() + " "+ user.getLoginpwd() + " " + user.getUsername());// 测试分页：查询第2页，每页2条。王五已被删除。List<Userinfo> userList = BaseDao.queryUserinfoList(2, 2);for (Userinfo u : userList) {System.out.println(u.getUserid() + " " + u.getLoginid() + " "+ u.getLoginpwd() + " " + u.getUsername());}} catch (Exception e) {System.out.println(e.getMessage());}}}?
---------------------------------------我是华丽的无所不在的分割线-------------------------------------------
?
MySql给我的感觉还可以，就是在安装完成后要配置一下my.ini
好在5.5提供了MySQLInstanceConfig.exe可以很方便的进行配置操作。
操作步骤我是参照的 http://www.duote.com/tech/1/2430.html#contentbody?
?
另外：
?
MySql自增列的关键字是：AUTO_INCREMENT
插入数据时，可以选择对该列赋值为 null 即可。
?
MySql分页可使用limit关键字:select * from tableName limit pageIndex,pageSize