jdbc 小封装
菜鸟写的JDBC封装，望前辈指出不足地方！
先介绍一下功能： 1.数据库连接地址从配置文件中获取
??????????????????????????2.支持命名参数设置（如： select? * from t_user where f_userName = :username）
??????????????????????????3.批量插入、更新?使用同一个connncetion
使用：
?
public class StockSend { Log LOG = LogFactory.getLog(StockSend.class); WarningEnvironment env = new WarningEnvironment(); SaveRemind send = new SaveRemind(); MySqlUtil mySqlUtil = new MySqlUtil(env.db_stock_warning, env.DB_STOCK_USER, env.DB_STOCK_PASSWORD); public List<StockSendUser> sendAllUser(){ List<StockSendUser> stockSends = new ArrayList<StockSendUser>(); String sql = "select * from db_stock_warning.t_warning_task where f_send_state = 0"; stockSends = mySqlUtil.queryForList(sql, callBack); return stockSends; } MysqlQueryCallBack callBack = new MysqlQueryCallBack() {//查询对象封装回调接口 @Override public Object next(ResultSet rs) throws Exception { StockSendUser stock = new StockSendUser(); stock.setUin(rs.getString("f_uin")); stock.setStock_code(rs.getString("f_stock_code")); stock.setStock_name(rs.getString("f_stock_name")); stock.setPrice(rs.getDouble("f_price")); stock.setStock_higher(rs.getDouble("f_stock_higher")); stock.setStock_less(rs.getDouble("f_stock_less")); return stock; } }; public void updateStockSend(StockSendUser stock){ String sql="update from db_stock_warning.t_warning_task set f_send_state = 1 where f_uin= ? and f_date= ? and f_stock_code=? and f_price = ? "; mySqlUtil.insertOrUpdate(sql, parameter, stock); } MysqlParameterCallBack parameter = new MysqlParameterCallBack() {//参数设置回调接口 @Override public void setParameter(PreparedStatement ps, Object o) throws Exception { StockSendUser stock = (StockSendUser)o; int i=1; ps.setString(i++, stock.getUin()); ps.setString(i++, stock.getDate()); ps.setString(i++, stock.getStock_code()); ps.setDouble(i++, stock.getPrice()); } }; 核心类：package com.tenpay.fund.util.pub.JDBC;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import org.apache.commons.logging.Log;import org.apache.commons.logging.LogFactory;/** * jdbc连接 处理类 * @author W_xieqi * */public class MySqlUtil { private final String mysql_url; private final String user ; private final String password ; private Log log = LogFactory.getLog(MySqlUtil.class); public MySqlUtil(String mysql_url,String user,String password ) { this.mysql_url= mysql_url; this.user = user; this.password = password; } //获得连接 public Connection getConnection() { // 建立连接 Connection con = null; try { if (con == null || con.isClosed()) { Class.forName("com.mysql.jdbc.Driver");//这里应该也应该放到配置文件中 con = DriverManager.getConnection(mysql_url, user, password); } } catch (Exception e) { log.error("re create connection error", e); } return con; } /** * 关闭连接方法 */ public void closeConnection(Connection con){ try { //连接不为空，并且连接未关闭 if(con !=null && !con.isClosed()){ con.close(); } } catch (SQLException e) { log.error("close connection error", e); } } /** * 关闭PreparedStatement */ public void closePreparedStatement(PreparedStatement ps){ try { if(ps != null && !ps.isClosed()){ ps.close(); } } catch (SQLException e) { log.error("close PreparedStatement excption",e); } } /** * 关闭PreparedStatement */ public void closeNamedParameterStatement(NamedParameterStatement ps){ try { if(ps != null ){ ps.close(); } } catch (SQLException e) { log.error("closeNamedParameterStatement excption",e); } } /** * 关闭PreparedStatement */ public void closeResultSet(ResultSet rs){ try { if(rs != null && !rs.isClosed()){ rs.close(); } } catch (SQLException e) { log.error("close ResultSet excption",e); } } /** * 插入和更新操作 * @param sql 带参数 * @param callBack 回调设参数 * @return 影响的行数 */ public int insertOrUpdate(String sql,MysqlParameterCallBack parameter,Object o){ return update(sql,parameter,o,null); } /** * 插入和更新(批量操作 connncetion 关闭由调用者关闭) * @param sql 执行的sql语句 * @param parameter 参数中？ * @param o * @param noClose 封装代码中不 关闭连接(一次连接多次操作) * @return */ public int insertOrUpdate(String sql,MysqlParameterCallBack parameter,Object o,Connection noCloseCon){ return update(sql,parameter,o,noCloseCon); } /** * 插入和更新(批量操作 connncetion 关闭由调用者关闭) * @param sql 执行的sql语句 * @param parameter 命名参数设置 * @param o 参数值获取对象 * @param noCloseCon 调用者自己获取的连接 * @return */ public int insertOrUpdate(String sql,MysqlNameParameterCallBack parameter,Object o,Connection noCloseCon){ return updateByNameParameter(sql, parameter, o, noCloseCon); } /** * 插入和更新 * @param sql 执行的sql语句 * @param parameter 命名参数设置 * @param o 参数值获取对象 * @return */ public int insertOrUpdate(String sql,MysqlNameParameterCallBack parameter,Object o){ return updateByNameParameter(sql, parameter, o, null); } /** * 执行更新和插入操作 * @param sql 执行的sql语句 * @param parameter 参数 * @param o 参数值获取对象 * @param noCloseCon 不关闭连接(这里主要用在批量操作，一次连接，多次update/insert) * @return */ private int updateByNameParameter(String sql,MysqlNameParameterCallBack parameter,Object o,Connection noCloseCon){ int count = 0 ; Connection con = null; if(noCloseCon == null)//如果调用者传了connncetion con = getConnection(); else con = noCloseCon; NamedParameterStatement nps = null; try { nps = new NamedParameterStatement(con, sql); parameter.setParameter(nps, o); count = nps.executeUpdate(); } catch (Exception e) { log.error("命名参数错误",e); }finally{ closeNamedParameterStatement(nps); if(noCloseCon == null){ closeConnection(con); } } return count; } private int update(String sql,MysqlParameterCallBack parameter,Object o,Connection noCloseCon){ int count = 0 ; Connection con = null; if(noCloseCon == null)//如果调用者传了connncetion con = getConnection(); else con = noCloseCon; PreparedStatement ps = null; try { ps = con.prepareStatement(sql); if(parameter != null && o != null){ parameter.setParameter(ps,o); } count = ps.executeUpdate(); } catch (Exception e) { log.error("close PreparedStatement excption",e); }finally{ closePreparedStatement(ps); if(noCloseCon == null){ closeConnection(con); } } return count; } /** * 插入和更新操作 * @param sql 不带参数 * @return 影响的行数 */ public int insertOrUpdate(String sql){ return update(sql,null,null,null); } private List query(String sql,MysqlParameterCallBack parameter,MysqlQueryCallBack query,Object o){ List list = new ArrayList(); Connection con = getConnection(); PreparedStatement ps = null; ResultSet rs = null; try { ps = con.prepareStatement(sql); if(parameter != null && o != null){ parameter.setParameter(ps,o); } rs = ps.executeQuery(); while(rs.next()){ list.add(query.next(rs)); } } catch (Exception e) { log.error("select exception ",e); }finally{ closeResultSet(rs); closePreparedStatement(ps); closeConnection(con); } return list; } /** * 查询 带参数 * @param sql * @param parameter * @param query * @return 结果集 */ public List queryForList(String sql,MysqlParameterCallBack parameter,MysqlQueryCallBack query,Object o){ return query(sql, parameter, query,o); } /** * 查询不 带参数 * @param sql * @param parameter * @param query * @return 结果集 */ public List queryForList(String sql,MysqlQueryCallBack query){ return query(sql, null, query,null); }} ?
附件中有详细代码，多多指点.!
?下面是批量插入代码，通过命名参数赋值。
?
public int update(StockInfoEntity stockInfo,Connection con){ String sql ="update db_stock.db_stock_info set " + "f_stock_name=:stockName,f_pinyin=:pinyin,f_stock_type_id=:stockTypeId " + "where f_stock_code=:stockCode and f_exchange=:exchange"; return mySqlUtil.insertOrUpdate(sql,parameter,stockInfo,con); } public void insert(StockInfoEntity stockInfo,Connection con){ String sql = "insert db_stock.db_stock_info(f_exchange,f_stock_code,f_stock_name,f_pinyin,f_stock_type_id)" + "value(:exchange,:stockCode,:stockName,:pinyin,:stockTypeId)"; mySqlUtil.insertOrUpdate(sql,parameter,stockInfo,con); } MysqlNameParameterCallBack parameter = new MysqlNameParameterCallBack() { public void setParameter(NamedParameterStatement ps, Object o) throws Exception { StockInfoEntity stockInfo = (StockInfoEntity)o; ps.setString("exchange", stockInfo.getExchange()); ps.setString("stockCode", stockInfo.getStock_code()); ps.setString("stockName", stockInfo.getStock_name()); ps.setString("pinyin", stockInfo.getPinyin()); ps.setInt("stockTypeId", stockInfo.getStock_type_id()); } };??
?????????????????????????? ?????