用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:[e-mail] * @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;}@Overridepublic int hashCode() {final int prime = 31;int result = 1;result = prime * result + id;return result;}@Overridepublic 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:[e-mail] * @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:[e-mail] * @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:[e-mail]* @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:[e-mail] * @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;}@Overridepublic Connection getConnection() throws SQLException {return dataSource.getConnection();}@Overridepublic 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:[e-mail] * @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:[e-mail] * @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:[e-mail] * @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:[e-mail] * @version v1.0 Date Created：Jul 30, 2010 11:07:23 PM * */public class UserDaoImpl extends SuperDao implements UserDao, RowMapper {@Overridepublic void delete(User user) {String sql = "delete from t_user where name=?";Object[] args = new Object[] {user.getName()};getJdbcTemplate().executeUpdate(sql, args);}@Overridepublic void save(User user) {String sql = "insert into t_user values(null, ?)";Object[] args = new Object[] {user.getName()};getJdbcTemplate().executeUpdate(sql, args);}@Overridepublic 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);}@Overridepublic 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);}@Overridepublic Object mapRow(ResultSet rs) throws SQLException {return new User(rs.getInt("id"), rs.getString("name"));}}

 

 

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