import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.Statement; import java.sql.ResultSet; import java.sql.SQLException; import javax.naming.Context; import javax.naming.InitialContext; import javax.naming.NamingException; import javax.sql.DataSource; /** * 数据访问公共类 * @author shengbo * */ public final class DBDriver { private Context context; private DataSource ds; //数据源 private Connection con; //数据库连接对象 private PreparedStatement ps; //执行对象 /** * 构造器,初始化数据连接池 * @throws NamingException * */ public DBDriver() throws NamingException{ context = new InitialContext(); //通过JNDI找到数据库源 ds =(DataSource) context.lookup("java:comp/env/jdbc/HouseDB"); } /** * 得到数据库连接对象 * @return * @throws SQLException */ private Connection getConnection() throws SQLException { //通过DataSource在连接池中得到数据库连接对象 con = ds.getConnection(); return con; } /** * 执行Update操作 * @param sql 要执行的sql语句 * @param param 传给PreparedStatement的参数 * @return 受影响行数 * @throws SQLException * @throws ClassNotFoundException */ public int doUpdate(String sql, Object[] param) throws Exception { int count = 0; con = getConnection(); ps = con.prepareStatement(sql); if (param != null) { this.setPsParam(ps, param); this.getPsSql(sql, param); } count = ps.executeUpdate(); closeAll(); return count; } /** * 执行查询操作 * @param sql * @param param * @param rs * @return * @throws Exception */ public ResultSet doSelect(String sql, Object[] param, ResultSet rs) throws Exception { con = getConnection(); ps = con.prepareStatement(sql); if (param != null) { this.setPsParam(ps, param); } System.out.println(this.getPsSql(sql, param)); rs = ps.executeQuery(); return rs; } /** * 执行插入数据,并返回主键ID * @param sql * @param param * @return * @throws SQLException */ public int doInsert(String sql ,Object[] param) throws SQLException{ con = getConnection(); ps = con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS); if (param != null) { this.setPsParam(ps, param); } System.out.println(this.getPsSql(sql, param)); ps.executeUpdate(); //执行 ResultSet rs = null; rs = ps.getGeneratedKeys(); //取得主键结果集 int index = 0; if(rs.next()){ index= rs.getInt(1); } rs.close(); this.closeAll(); return index; } /** * 关闭数据库连接对象和操作对象 * @throws SQLException * */ public void closeAll() throws SQLException { if (ps != null) { ps.close(); ps = null; } if (con != null) { con.close(); } } /** * 关闭ResultSet对象 * @param rs 要关闭的ResultSet * @throws SQLException */ public void closeResultSet(ResultSet rs) throws SQLException { if (rs != null) { rs.close(); rs = null; } } /** * 设置PreparedStatement参数 * @param ps PreparedStatement对象 * @param param 参数数组 * @throws SQLException */ private void setPsParam(PreparedStatement ps, Object[] param) throws SQLException{ for(int i=0;i<param.length;i++){ ps.setObject(i+1, param[i]); } } /** * 拼凑sql语句 * @param sql 预编译sql语句 * @param param 参数数组 * @return */ private String getPsSql(String sql , Object[] param){ if(param==null){ return sql; } StringBuffer str = new StringBuffer(); //使用问号分割sql语句 String[] arr = sql.split("//?"); for(int i=0;i<param.length;i++){ str.append(arr[i]+" '"+param[i]+"' "); } if(arr.length>param.length){ str.append(arr[arr.length-1]); } return str.toString(); } } 本文转自:http://hi.baidu.com/tears008/blog/item/5df3223d0fe91ecf9e3d624d.html