一、步骤
- 注册驱动 (只做一次)
- 建立连接(Connection)
- 创建执行SQL的语句(Statement)
- 执行语句
- 处理执行结果(ResultSet)
- 释放资源
二、详细
注册驱动：
Class.forName(“com.mysql.jdbc.Driver”);
建立连接：
Connection conn = DriverManager.getConnection(url, user, password);
执行SQL语句：
Statement
Statement st = conn.createStatement();
st.executeQuery(sql);
PreparedStatement
String sql = “select * from table_name where col_name=?”;
PreparedStatement ps = conn.preparedStatement(sql);
ps.setString(1, “col_value”);
ps.executeQuery();
处理执行结果：ResultSet rs = statement.executeQuery(sql);
//ResultSet rs = ps.executeQuery();
While(rs.next()){
rs.getString(“col_name”);
rs.getInt(“col_name”);
//…
}
释放资源：
释放ResultSet, Statement,Connection.
数据库连接（Connection）是非常稀有的资源，用完后必须马上释放，如Connection不能
及时正确的关闭将导致系统宕机。Connection的使用原则是尽量晚创建，尽量早的释放。
三、CRUD的实现
POJO/Domain
public class User { private int id; private String name; private Date birthday; private float money; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public Date getBirthday() { return birthday; } public void setBirthday(Date birthday) { this.birthday = birthday; } public float getMoney() { return money; } public void setMoney(float money) { this.money = money; }} JdbcUtils：提取出数据库操作的共同的部分
public final class JdbcUtils { private static String url = "jdbc:mysql://localhost:3306/jdbc"; private static String user = "root"; private static String password = ""; private JdbcUtils() { } static { try { Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException e) { throw new ExceptionInInitializerError(e); } } public static Connection getConnection() throws SQLException { return DriverManager.getConnection(url, user, password); } public static void free(ResultSet rs, Statement st, Connection conn) { try { if (rs != null) rs.close(); } catch (SQLException e) { e.printStackTrace(); } finally { try { if (st != null) st.close(); } catch (SQLException e) { e.printStackTrace(); } finally { if (conn != null) try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } }} 改进：考虑到可能同时有很多人需要使用此类，因此可将之改进为单例实现。 JdbcUtilsSing.java
public final class JdbcUtilsSing { private String url = "jdbc:mysql://localhost:3306/jdbc"; private String user = "root"; private String password = ""; // private static JdbcUtilsSing instance = new JdbcUtilsSing(); private static JdbcUtilsSing instance = null; private JdbcUtilsSing() { } public static JdbcUtilsSing getInstance() { if (instance == null) { synchronized (JdbcUtilsSing.class) { if (instance == null) { instance = new JdbcUtilsSing(); } } } return instance; } static { try { Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException e) { throw new ExceptionInInitializerError(e); } } public Connection getConnection() throws SQLException { return DriverManager.getConnection(url, user, password); } public void free(ResultSet rs, Statement st, Connection conn) { try { if (rs != null) rs.close(); } catch (SQLException e) { e.printStackTrace(); } finally { try { if (st != null) st.close(); } catch (SQLException e) { e.printStackTrace(); } finally { if (conn != null) try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } }} DAO(Interface)
public interface UserDao { public void addUser(User user); public User getUser(int userId); public User findUser(String loginName, String password); public void update(User user); public void delete(User user);} DAOImpl public class UserDaoJdbcImpl implements UserDao { public void addUser(User user) { Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); String sql = "insert into user(name,birthday, money) values (?,?,?) "; ps = conn.prepareStatement(sql); ps.setString(1, user.getName()); ps.setDate(2, new java.sql.Date(user.getBirthday().getTime())); ps.setFloat(3, user.getMoney()); ps.executeUpdate(); } catch (SQLException e) { throw new DaoException(e.getMessage(), e); } finally { JdbcUtils.free(rs, ps, conn); } } public void delete(User user) { Connection conn = null; Statement st = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); st = conn.createStatement(); String sql = "delete from user where id=" + user.getId(); st.executeUpdate(sql); } catch (SQLException e) { throw new DaoException(e.getMessage(), e); } finally { JdbcUtils.free(rs, st, conn); } } public User findUser(String loginName, String password) { Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; User user = null; try { conn = JdbcUtils.getConnection(); String sql = "select id, name, money, birthday from user where name=?"; ps = conn.prepareStatement(sql); ps.setString(1, loginName); rs = ps.executeQuery(); while (rs.next()) { user = mappingUser(rs); } } catch (SQLException e) { throw new DaoException(e.getMessage(), e); } finally { JdbcUtils.free(rs, ps, conn); } return user; } public User getUser(int userId) { Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; User user = null; try { conn = JdbcUtils.getConnection(); String sql = "select id, name, money, birthday from user where id=?"; ps = conn.prepareStatement(sql); ps.setInt(1, userId); rs = ps.executeQuery(); while (rs.next()) { user = mappingUser(rs); } } catch (SQLException e) { throw new DaoException(e.getMessage(), e); } finally { JdbcUtils.free(rs, ps, conn); } return user; } private User mappingUser(ResultSet rs) throws SQLException { User user = new User(); user.setId(rs.getInt("id")); user.setName(rs.getString("name")); user.setMoney(rs.getFloat("money")); user.setBirthday(rs.getDate("birthday")); return user; } public void update(User user) { Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); String sql = "update user set name=?, birthday=?, money=? where id=? "; ps = conn.prepareStatement(sql); ps.setString(1, user.getName()); ps.setDate(2, new java.sql.Date(user.getBirthday().getTime())); ps.setFloat(3, user.getMoney()); ps.setInt(4, user.getId()); ps.executeUpdate(); } catch (SQLException e) { throw new DaoException(e.getMessage(), e); } finally { JdbcUtils.free(rs, ps, conn); } }} 改进：注意上述代码中异常的处理方式，不是随意的调用printStacktrace()，因为SQL异常是一种编译时异常，如果我们不做处理的话，在业务逻辑层(Service)调用DAO层的代码的时候将会产生众多的try...catch代码块，这会让我们的代码看起来很杂乱。因此，此处我们将此异常转化为运行时异常，交给DaoException类处理。
DaoException
public class DaoException extends RuntimeException { private static final long serialVersionUID = 1L; public DaoException() { // TODO Auto-generated constructor stub } public DaoException(String message) { super(message); // TODO Auto-generated constructor stub } public DaoException(Throwable cause) { super(cause); // TODO Auto-generated constructor stub } public DaoException(String message, Throwable cause) { super(message, cause); // TODO Auto-generated constructor stub }} 下面我们思考这样一个问题，我们的业务逻辑层需要调用DaoImpl，但是当我们使用其他的框架如hibernate的时候，如何实现无缝的切换，即不改变我们的业务逻辑层，而使程序正确运行，在不了解Spring框架的时候，我们可以按照下面的方法实现。
首先，使用一个properties文件定义我们的DaoImpl类：daoconfig.properties
userDaoClass=cn.jdbc.dao.impl.UserDaoJdbcImpl然后用一个工厂类来生产DaoImpl对象：DaoFactory
public class DaoFactory { private static UserDao userDao = null; private static DaoFactory instance = new DaoFactory(); private DaoFactory() { try { Properties prop = new Properties(); InputStream inStream = DaoFactory.class.getClassLoader() .getResourceAsStream("daoconfig.properties"); prop.load(inStream); String userDaoClass = prop.getProperty("userDaoClass"); Class clazz = Class.forName(userDaoClass); userDao = (UserDao) clazz.newInstance(); } catch (Throwable e) { throw new ExceptionInInitializerError(e); } } public static DaoFactory getInstance() { return instance; } public UserDao getUserDao() { return userDao; }} 注意上述代码的部分代码须小心：
private static UserDao userDao = null;private static DaoFactory instance = new DaoFactory();这2行代码的顺序不可搞错，否则外部类获取的userDao对象将会是空，原因是类的初始化顺序。
在业务逻辑层这样调用即可：
UserDao userDao = DaoFactory.getInstance().getUserDao();