用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;
	}
	
	@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:[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;
	}
	@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:[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 {
	@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"));
	}
	
	
}

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