用JDBC封装CRUD操作（个人总结）

先定义好实体类，我简化了属性：

package org.lmw.crud.po; /** * * <br/>Copyright (C), 2010, Mingwei Liu * <br/>This program is protected by copyright laws. * @author Mingwei Liu E-mail:liumingweiweiwei@126.com * @version v1.0 Date Created：Jul 30, 2010 10:48:36 PM * */ public class User { private int id; private String name; 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; } @Override public int hashCode() { final int prime = 31; int result = 1; result = prime * result + id; return result; } @Override public boolean equals(Object obj) { if (this == obj) return true; if (obj == null) return false; if (getClass() != obj.getClass()) return false; User other = (User) obj; if (id != other.id) return false; return true; } }

然后定义接口：

package org.lmw.crud.po.dao; import org.lmw.crud.po.User; /** * * <br/>Copyright (C), 2010, Mingwei Liu * <br/>This program is protected by copyright laws. * @author Mingwei Liu E-mail:liumingweiweiwei@126.com * @version v1.0 Date Created：Jul 30, 2010 10:52:54 PM * */ public interface UserDao { public void save(User user); public boolean delete(User user); public void update(User user); public User selectByName(String name); }

初学JDBC时，都没个dao自己获得数据库连接，自己执行SQL语句，自己关闭资源，这样有两个缺陷。

第一SQL异常无法处理，第二大量的代码是重复的。

第一个问题我们可以定义一个DaoException继承RuntimeException，这样上层就不用捕获SQL异常了。即使换成文件存储数据，也不用更改Service层的代码，真正做到了各层之间的解耦。

第二个问题可以使用Template模式解决。

定义DaoException的代码：

package com.hp.trainermanage.dao.impl; /** *Root of the hierarchy of data access exceptions. * <p> * As this class is a runtime exception, there is no need for user code to catch * it or subclasses if any error is to be considered fatal (the usual case). <br/> * Copyright (C), 2010, Mingwei Liu <br/> * This program is protected by copyright laws. * * @author Mingwei Liu E-mail:liumingweiweiwei@126.com * @version v1.0 Date Created：Jul 9, 2010 4:26:19 PM */ public class DaoException extends RuntimeException { private static final long serialVersionUID = 6260282384874832029L; /** * Constructor for DaoException. * */ public DaoException() { } /** * Constructor for DaoException. * * @param message * the detail exception message */ public DaoException(String message) { super(message); } /** * Constructor for DaoException. * * @param cause * the root cause */ public DaoException(Throwable cause) { super(cause); } /** * Constructor for DaoException. * * @param message * the detail exception message * @param cause * the root cause */ public DaoException(String message, Throwable cause) { super(message, cause); } }

定义与数据库打交道的接口：

package com.hp.trainermanage.dao.impl; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; /** * * <br/>Copyright (C), 2010, Mingwei Liu * <br/>This program is protected by copyright laws. * @author Mingwei Liu E-mail:liumingweiweiwei@126.com * @version v1.0 Date Created：Jul 28, 2010 10:42:47 AM */ public interface JdbcUtil { /** * * @return * @throws SQLException */ public Connection getConnection() throws SQLException; /** * * @param rs * @param st * @param conn */ public void free(ResultSet rs, Statement st, Connection conn); }

MySQL实现：

package com.hp.trainermanage.dao.impl; import java.io.InputStream; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Properties; import javax.sql.DataSource; import org.apache.commons.dbcp.BasicDataSourceFactory; /** * * <br/> * Copyright (C), 2010, Mingwei Liu <br/> * This program is protected by copyright laws. * * @author Mingwei Liu E-mail:liumingweiweiwei@126.com * @version v1.0 Date Created：Jul 9, 2010 7:33:40 PM */ public final class JdbcUtilMySQLImpl implements JdbcUtil { private static DataSource dataSource = null; static { try { Properties properties = new Properties(); InputStream is = JdbcUtilMySQLImpl.class.getClassLoader() .getResourceAsStream("jdbc.properties"); properties.load(is); dataSource = BasicDataSourceFactory.createDataSource(properties); } catch (Exception e) { throw new ExceptionInInitializerError(e); } } public DataSource getDataSource() { return dataSource; } @Override public Connection getConnection() throws SQLException { return dataSource.getConnection(); } @Override 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 (Exception e) { e.printStackTrace(); } } } } }

定义JdbcTemplate类：

package com.hp.trainermanage.dao.impl; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.LinkedList; import java.util.List; import org.apache.log4j.Logger; /** * * <br/> * Copyright (C), 2010, Mingwei Liu <br/> * This program is protected by copyright laws. * * @author Mingwei Liu E-mail:liumingweiweiwei@126.com * @version v1.0 Date Created：Jul 10, 2010 8:21:34 AM * */ public class JdbcTemplate { JdbcUtil jdbcUtil = new JdbcUtilMySQLImpl(); private static Logger logger = Logger.getLogger(JdbcTemplate.class); public Object query(String sql, Object[] args, RowMapper rowMapper) { Connection connection = null; PreparedStatement pstmt = null; ResultSet rs = null; try { connection = jdbcUtil.getConnection(); pstmt = connection.prepareStatement(sql); if (args != null) { for (int i = 0; i < args.length; i++) { pstmt.setObject(i + 1, args[i]); } } rs = pstmt.executeQuery(); Object obj = null; if (rs.next()) { obj = rowMapper.mapRow(rs); } return obj; } catch (SQLException e) { logger.error(e); throw new DaoException(e.getMessage(), e); } finally { jdbcUtil.free(rs, pstmt, connection); } } public List<Object> queryForList(String sql, Object[] args, RowMapper rowMapper) { Connection connection = null; PreparedStatement pstmt = null; ResultSet rs = null; try { connection = jdbcUtil.getConnection(); pstmt = connection.prepareStatement(sql); if (args != null) { for (int i = 0; i < args.length; i++) { pstmt.setObject(i + 1, args[i]); } } rs = pstmt.executeQuery(); List<Object> objects = new LinkedList<Object>(); while (rs.next()) { Object obj = rowMapper.mapRow(rs); objects.add(obj); } return objects; } catch (SQLException e) { logger.error(e); throw new DaoException(e.getMessage(), e); } finally { jdbcUtil.free(rs, pstmt, connection); } } public int executeUpdate(String sql, Object[] args) { Connection connection = null; PreparedStatement pstmt = null; ResultSet rs = null; try { connection = jdbcUtil.getConnection(); pstmt = connection.prepareStatement(sql); for (int i = 0; i < args.length; i++) { pstmt.setObject(i + 1, args[i]); } return pstmt.executeUpdate(); } catch (SQLException e) { logger.error(e); throw new DaoException(e.getMessage(), e); } finally { jdbcUtil.free(rs, pstmt, connection); } } }

定义SuperDao所有dao的父类：

package com.hp.trainermanage.dao.impl; /** * * <br/>Copyright (C), 2010, Mingwei Liu * <br/>This program is protected by copyright laws. * @author Mingwei Liu E-mail:liumingweiweiwei@126.com * @version v1.0 Date Created：Jul 10, 2010 6:14:34 PM * */ public class SuperDao { private JdbcTemplate jdbcTemplate = new JdbcTemplate(); public JdbcTemplate getJdbcTemplate() { return jdbcTemplate; } }

定义代表对象和记录的映射的接口RowMapper：

package com.hp.trainermanage.dao.impl; import java.sql.ResultSet; import java.sql.SQLException; /** * An interface used by {@link JdbcTemplate} for mapping rows of a * {@link java.sql.ResultSet} on a per-row basis. * <br/>Copyright (C), 2010, Mingwei Liu * <br/>This program is protected by copyright laws. * @author Mingwei Liu E-mail:liumingweiweiwei@126.com * @version v1.0 Date Created：Jul 10, 2010 8:17:21 AM */ public interface RowMapper { /** * Implementations must implement this method to map each row of data * in the ResultSet. * @param rs the ResultSet to map (pre-initialized for the current row) * @return the result object for the current row * @throws SQLException if a SQLException is encountered getting */ public Object mapRow(ResultSet rs) throws SQLException; }

最后实现UserDao：

package org.lmw.crud.po.dao.impl; import java.sql.ResultSet; import java.sql.SQLException; import org.lmw.crud.po.User; import org.lmw.crud.po.dao.UserDao; /** * * <br/>Copyright (C), 2010, Mingwei Liu * <br/>This program is protected by copyright laws. * @author Mingwei Liu E-mail:liumingweiweiwei@126.com * @version v1.0 Date Created：Jul 30, 2010 11:07:23 PM * */ public class UserDaoImpl extends SuperDao implements UserDao, RowMapper { @Override public void delete(User user) { String sql = "delete from t_user where name=?"; Object[] args = new Object[] {user.getName()}; getJdbcTemplate().executeUpdate(sql, args); } @Override public void save(User user) { String sql = "insert into t_user values(null, ?)"; Object[] args = new Object[] {user.getName()}; getJdbcTemplate().executeUpdate(sql, args); } @Override public User selectByName(String name) { String sql = "select id, name from t_user where name=?"; Object[] args = new Object[] {name}; return (User) getJdbcTemplate().query(sql, args, this); } @Override public void update(User user) { String sql = "update t_user set name=? where id=?"; Object[] args = new Object[] {user.getName(),user.getId()}; getJdbcTemplate().executeUpdate(sql, args); } @Override public Object mapRow(ResultSet rs) throws SQLException { return new User(rs.getInt("id"), rs.getString("name")); } }

最后上个整体架构的类图：