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());
   
  }
 };