vlambda博客
学习文章列表

import java.io.File;import java.io.IOException;import java.nio.charset.Charset;import java.util.Map;import java.util.concurrent.BlockingQueue;import java.util.concurrent.LinkedBlockingQueue;
import org.apache.commons.lang3.StringUtils;import org.springframework.core.io.DefaultResourceLoader;
import com.google.common.collect.Maps;public class Global { /** * 当前对象实例 */    private static Global global = new Global(); /** * 保存全局属性值 */    private static Map<String, String> map = Maps.newHashMap(); /** * 属性文件加载对象 */    private static PropertiesLoader loader = new PropertiesLoader("db.properties");  /** * 获取当前对象实例 */ public static Global getInstance() { return global;    } /** * 获取配置 */ public static String getConfig(String key) { String value = map.get(key); if (value == null) { value = loader.getProperty(key); map.put(key, value != null ? value : StringUtils.EMPTY); } return value; }    // 数据源及相关参数设置 public static final String DB_URL =getConfig("productionJdbc.url"); public static final String DB_USERNAME =getConfig("productionJdbc.username"); public static final String DB_PASSWORD =getConfig("productionJdbc.password"); public static final String DB_DRIVER =getConfig("productionJdbc.driver"); public static final String DB_INITIALSIZE =getConfig("ds.initialSize"); public static final String DB_MAXACTIVE =getConfig("ds.maxActive"); public static final String DB_MINIDLE =getConfig("ds.minIdle"); public static final String DB_MAXWAIT =getConfig("ds.maxWait"); public static final String DB_TIMEBETWEENEVICTIONRUNSMILLIS =getConfig("ds.timeBetweenEvictionRunsMillis"); public static final String DB_MINEVICTABLEIDLETIMEMILLIS =getConfig("ds.minEvictableIdleTimeMillis");
 public static final String TRANSFERJDBC_DB_URL =getConfig("transferJdbc.url"); public static final String TRANSFERJDBC_DB_USERNAME =getConfig("transferJdbc.username");    public static final String  TRANSFERJDBC_DB_PASSWORD =getConfig("transferJdbc.password"); }

public enum DbEnum { PROD("1") //生产库 ,TRANSF("2"); //中转库   ,HISTORY("3"); //历史库  private String value;  private DbEnum(String value){ this.value = value; }
 public String getValue() { return value; }
 @Override public String toString() {  return this.value; } }

import java.sql.Connection;import java.sql.SQLException;
import com.alibaba.druid.pool.xa.DruidXADataSource;import com.byd.mes.base.utils.Global;import com.byd.mes.base.utils.JdbcUtil;import com.byd.mes.base.utils.SpringBeanUtill;import com.byd.mes3.exception.BizException;
public class JdbcPoolTool {
 private static DruidXADataSource dataSourcePro = null;  private static DruidXADataSource dataSourceTrans = null;  /** * 获取数据库连接 * @param dbEnum * @return */ public static Connection getConnection(DbEnum dbEnum){ Connection conn = getConectionOfPool(dbEnum); return conn; }  /** * 根据不同的数据库从框架中获取Druid连接池 * @param dbEnum * @return */ private static Connection getConectionOfPool(DbEnum dbEnum){ DruidXADataSource ds = null; Connection poolConn = null; switch(dbEnum.getValue()){ case "1": ds = (DruidXADataSource) SpringBeanUtill.getBean("productionJdbcXADataSource"); break; case "2": ds = (DruidXADataSource) SpringBeanUtill.getBean("transferJdbcXADataSource"); break; default: ds = (DruidXADataSource) SpringBeanUtill.getBean("productionJdbcXADataSource");  break; } if(ds.isClosed() || ds == null){ ds = setDruidXADataSource(dbEnum); } try { poolConn = ds.getConnection(); } catch (SQLException e) { e.printStackTrace(); throw new BizException("获取数据库连接出错！"); } if(poolConn == null ){ // 如果实在不行，手动建立JDBC连接 switch(dbEnum.getValue()){ case "1": poolConn = JdbcUtil.getConnection(Global.DB_URL,Global.DB_USERNAME,Global.DB_PASSWORD); break; case "2": poolConn = JdbcUtil.getConnection(Global.TRANSFERJDBC_DB_URL,Global.TRANSFERJDBC_DB_USERNAME,Global.TRANSFERJDBC_DB_PASSWORD); break; default: poolConn = JdbcUtil.getConnection(Global.DB_URL,Global.DB_USERNAME,Global.DB_PASSWORD); break; } } return poolConn; }
 /** * 获取配置文件，并赋值 * @param dbEnum * @param poolConn  * @return */ private static DruidXADataSource setDruidXADataSource(DbEnum dbEnum) { switch(dbEnum.getValue()){ case "1": if(dataSourcePro != null){ return dataSourcePro; } dataSourcePro = getDataSourcePro(); return dataSourcePro; case "2": if(dataSourceTrans != null){ return dataSourceTrans; } dataSourceTrans = getDataSourceTrans(); return dataSourceTrans; default: dataSourcePro = getDataSourcePro(); return dataSourcePro; }  }  /** * 获取生产库连接池对象 * @return */ private static DruidXADataSource getDataSourcePro() { DruidXADataSource ds = new DruidXADataSource(); setCommJdbc(ds); ds.setUrl(Global.DB_URL); ds.setUsername(Global.DB_USERNAME); ds.setPassword(Global.DB_PASSWORD);   return ds; }  /** * 获取中转库连接池对象 * @return */ private static DruidXADataSource getDataSourceTrans() { DruidXADataSource ds = new DruidXADataSource(); setCommJdbc(ds); ds.setUrl(Global.TRANSFERJDBC_DB_URL); ds.setUsername(Global.TRANSFERJDBC_DB_USERNAME); ds.setPassword(Global.TRANSFERJDBC_DB_PASSWORD); return ds; } 
 /** * 设置JDBC通用属性(请参考 applicationContext-db.xml 和 application.properties文件的配置) * @param dbEnum * @return */ private static void setCommJdbc(DruidXADataSource ds){ //配置初始化大小、最小、最大 ds.setInitialSize(Integer.parseInt(Global.DB_INITIALSIZE)); ds.setMaxActive(Integer.parseInt(Global.DB_MAXACTIVE)); ds.setMinIdle(Integer.parseInt(Global.DB_MINIDLE)); //配置获取连接等待超时的时间  ds.setMaxWait(Integer.parseInt(Global.DB_MAXWAIT)); //配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 ds.setTimeBetweenEvictionRunsMillis(Long.parseLong(Global.DB_TIMEBETWEENEVICTIONRUNSMILLIS)); //配置一个连接在池中最小生存的时间，单位是毫秒 ds.setMinEvictableIdleTimeMillis(Long.parseLong(Global.DB_MINEVICTABLEIDLETIMEMILLIS)); //配置连接驱动 ds.setDriverClassName(Global.DB_DRIVER); //设置驱动 //连接泄漏监测 超过removeAbandonedTimeout时间后，是否进行没用连接（废弃）的回收 ds.setRemoveAbandoned(false); ds.setRemoveAbandonedTimeout(60); //防止过期 ds.setValidationQuery("SELECT * FROM DUAL"); ds.setTestWhileIdle(true); ds.setTestOnBorrow(false);  ds.setTestOnReturn(false); //关闭abanded连接时输出错误日志，将会在回收事件后，在log中打印出回收Connection的错误信息，包括在哪个地方用了Connection却忘记关闭了，方便调试 ds.setLogAbandoned(true); //打开PSCache，并且指定每个连接上PSCache的大小  ds.setPoolPreparedStatements(true); ds.setMaxPoolPreparedStatementPerConnectionSize(20); } }

import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;
public class JdbcUtil {  /** *  * 释放资源方法 */ public static void close(PreparedStatement ps , Connection conn){ if (ps != null){  try {  ps.close();  } catch (SQLException e) {  e.printStackTrace();  }  } if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); } } } /** *  * 释放资源方法 */ public static void close(CallableStatement ps , Connection conn){ if (ps != null){  try {  ps.close();  } catch (SQLException e) {  e.printStackTrace();  }  } if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); } } } /** * 释放资源方法 */ public static void close(ResultSet rs, Statement stmt, Connection conn) { if (rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); } } if (stmt != null) { try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); } } if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); } } } /** * 释放资源方法 */ public static void close(ResultSet rs, CallableStatement stmt, Connection conn) { if (rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); } } if (stmt != null) { try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); } } if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); } } }
 /** * 释放资源方法 */ public static void close(Statement stmt, Connection conn) { if (stmt != null) { try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); } } if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); } } }}

import java.math.BigDecimal;import java.sql.Array;import java.sql.Blob;import java.sql.CallableStatement;import java.sql.Clob;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Types;import java.util.ArrayList;import java.util.Date;import java.util.HashMap;import java.util.List;import java.util.Map;
public class JdbcTool {
 public static Connection getConnectionByDb(Connection conn ,DbEnum dbEnum){ switch(dbEnum.getValue()){ case "1": conn = JdbcPoolTool.getConnection(DbEnum.PROD); break; case "2": conn = JdbcPoolTool.getConnection(DbEnum.TRANSF); break; default: conn = JdbcPoolTool.getConnection(DbEnum.PROD); break; } return conn; }  public static List<Map<String,Object>> queryForMap(DbEnum dbEnum,String sql){ Connection conn = null; PreparedStatement psmt = null; ResultSet rs = null; List<Map<String,Object>> list = new ArrayList<Map<String,Object>>(); try { conn = getConnectionByDb(conn,dbEnum); psmt = conn.prepareStatement(sql); rs = psmt.executeQuery(); list = ResultSetTool.resultSetToListMap(rs); } catch (Exception e) { JdbcUtil.close(rs, psmt, conn); throw new BizException(e.getMessage()); }finally{ JdbcUtil.close(rs, psmt, conn); } return list; }  public static Map<String,Object> queryForMapSingle(DbEnum dbEnum,String sql){ List<Map<String,Object>> list = queryForMap(dbEnum,sql); Map<String,Object> map = new HashMap<String,Object>(); if(list != null && list.size() >0){ map = list.get(0); } return map; }  public static List<Map<String,Object>> queryForMap(DbEnum dbEnum,String sql,List<Object> paramsList){ Connection conn = null; PreparedStatement psmt = null; ResultSet rs = null; List<Map<String,Object>> list = new ArrayList<Map<String,Object>>(); if(paramsList != null && paramsList.size() >0){ if(countInterrogation(sql) != paramsList.size()){ throw new BizException("传入的参数个数不符合！执行语句"+sql+"传入的参数个数为"+paramsList.size()); } } try { conn = getConnectionByDb(conn,dbEnum); psmt = conn.prepareStatement(sql); psmt = ResultSetTool.listObjectSetToPs(paramsList, psmt); rs = psmt.executeQuery(); list = ResultSetTool.resultSetToListMap(rs); } catch (Exception e) { JdbcUtil.close(rs, psmt, conn); throw new BizException(e.getMessage()); }finally{ JdbcUtil.close(rs, psmt, conn); } return list; }  public static Map<String,Object> queryForMapSingle(DbEnum dbEnum,String sql,List<Object> paramsList){ List<Map<String,Object>> list = queryForMap(dbEnum,sql,paramsList); Map<String,Object> map = new HashMap<String,Object>(); if(list != null && list.size() >0){ map = list.get(0); } return map; }  public static <T> List<T> queryForList(DbEnum dbEnum,String sql,Class<T> clazz){ Connection conn = null; PreparedStatement psmt = null; ResultSet rs = null; List<T> list = new ArrayList<>(); try { conn = getConnectionByDb(conn,dbEnum); psmt = conn.prepareStatement(sql); rs = psmt.executeQuery(); list =ResultSetTool.resultSetToListBean(rs, clazz); } catch (Exception e) { JdbcUtil.close(rs, psmt, conn); throw new BizException(e.getMessage()); }finally{ JdbcUtil.close(rs, psmt, conn); } return list; }   public static <T> T queryForBean(DbEnum dbEnum,String sql,Class<T> clazz){ List<T> list = queryForList(dbEnum,sql, clazz); if(list != null && list.size() >0){ return list.get(0); } return null; }  public static <T> List<T> queryForList(DbEnum dbEnum,String sql,List<Object> paramsList,Class<T> clazz){ Connection conn = null; PreparedStatement psmt = null; ResultSet rs = null; List<T> list = new ArrayList<>(); if(paramsList != null && paramsList.size() >0){ if(countInterrogation(sql) != paramsList.size()){ throw new BizException("传入的参数个数不符合！执行语句"+sql+"传入的参数个数为"+paramsList.size()); } } try { conn = getConnectionByDb(conn,dbEnum); psmt = conn.prepareStatement(sql); psmt = ResultSetTool.listObjectSetToPs(paramsList, psmt); rs = psmt.executeQuery(); list =ResultSetTool.resultSetToListBean(rs, clazz); } catch (Exception e) { JdbcUtil.close(rs, psmt, conn); throw new BizException(e.getMessage()); }finally{ JdbcUtil.close(rs, psmt, conn); } return list; }  /**   * 更新   **/ public static void update(DbEnum dbEnum,String sql){ Connection conn = null; PreparedStatement psmt = null; ResultSet rs = null; try { conn = getConnectionByDb(conn,dbEnum); //设置事务属性  conn.setAutoCommit(false);  psmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY); psmt.executeUpdate();  //提交，设置事务初始值  conn.commit();  conn.setAutoCommit(true);  } catch (Exception e) { try{  //提交失败，执行回滚操作  conn.rollback();  }catch (SQLException sqlE) {  sqlE.printStackTrace();  throw new BizException("执行出错，错误原因："+e.getMessage()+"，回滚执行失败!!!"); }  JdbcUtil.close(rs, psmt, conn); throw new BizException(e.getMessage()); }finally{ JdbcUtil.close(rs, psmt, conn); } }  public static void update(DbEnum dbEnum,String sql,List<Object> paramsList){ Connection conn = null; PreparedStatement psmt = null; ResultSet rs = null; try { conn = getConnectionByDb(conn,dbEnum); //设置事务属性  conn.setAutoCommit(false);  psmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY); psmt = ResultSetTool.listObjectSetToPs(paramsList, psmt); psmt.executeUpdate();  //提交，设置事务初始值  conn.commit();  conn.setAutoCommit(true);  } catch (Exception e) { try{  //提交失败，执行回滚操作  conn.rollback();  }catch (SQLException sqlE) {  sqlE.printStackTrace();  throw new BizException("执行出错，错误原因："+e.getMessage()+"，回滚执行失败!!!"); }  JdbcUtil.close(rs, psmt, conn); throw new BizException(e.getMessage()); }finally{ JdbcUtil.close(rs, psmt, conn); } }  public static void batchUpdate(DbEnum dbEnum,String sql,List<List<Object>> paramsList){ Connection conn = null; PreparedStatement psmt = null; ResultSet rs = null; //sql包含问号个数 int count = countInterrogation(sql); for(List<Object> list:paramsList){ if(list.size() != count){ throw new BizException("传入的参数个数不符合！执行语句"+sql+"传入的参数个数为"+ list.size()); } } try { conn = getConnectionByDb(conn,dbEnum); //设置事务属性  conn.setAutoCommit(false);  psmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY); for(int i=0; i<paramsList.size(); i++){ psmt = ResultSetTool.listObjectSetToPs(paramsList.get(i), psmt); psmt.addBatch(); } psmt.executeBatch();  //提交，设置事务初始值  conn.commit();  conn.setAutoCommit(true);  } catch (Exception e) { try{  //提交失败，执行回滚操作  conn.rollback();  }catch (SQLException sqlE) {  sqlE.printStackTrace();  throw new BizException("执行出错，错误原因："+e.getMessage()+"，回滚执行失败!!!"); }  JdbcUtil.close(rs, psmt, conn); throw new BizException(e.getMessage()); }finally{ JdbcUtil.close(rs, psmt, conn); } }  /** * 调用无参无返回值存储过程 * @param dbEnum * @param procedureName */ public static void callProcedureNon(DbEnum dbEnum,String procedureName){ Connection conn = null; CallableStatement cstmt = null; try { conn = getConnectionByDb(conn,dbEnum); StringBuilder strSb = new StringBuilder(); strSb.append(" {call "); strSb.append(procedureName); strSb.append("()}"); cstmt = conn.prepareCall(strSb.toString()); cstmt.execute(); } catch (Exception e) { throw new BizException(e.getMessage()); }finally{ JdbcUtil.close(cstmt, conn); } }  /** * 调用无参有返回值的存储过程 * @param dbEnum * @param procedureName * @return */ public static List<Object> callProcedure(DbEnum dbEnum,String procedureName){ Connection conn = null; CallableStatement cstmt = null; ResultSet rs = null; List<Object> list = new ArrayList<Object>(); try { conn = getConnectionByDb(conn,dbEnum); StringBuilder strSb = new StringBuilder(); strSb.append(" {call "); strSb.append(procedureName); strSb.append("()}"); cstmt = conn.prepareCall(strSb.toString()); rs = cstmt.executeQuery(); while(rs.next()){ int rowNum = rs.getRow(); for(int i=0;i<rowNum;i++){ list.add(rs.getObject(i)); } } } catch (Exception e) { throw new BizException(e.getMessage()); }finally{ JdbcUtil.close(rs, cstmt, conn); } return list; }   /** * 调用有参无返回值的存储过程 * @param dbEnum * @param procedureName * @param ins */ public static void callProcedure(DbEnum dbEnum,String procedureName,List<Object> ins){ Connection conn = null; CallableStatement cstmt = null; try { conn = getConnectionByDb(conn,dbEnum); StringBuilder strSb = new StringBuilder(); strSb.append(" {call "); strSb.append(procedureName); strSb.append("("); if(ins != null && ins.size() > 0){ for(int i=0;i<ins.size(); i++){ strSb.append("?,"); } strSb.setLength(strSb.length()-1); } strSb.append(")}"); cstmt = conn.prepareCall(strSb.toString()); cstmt = listOject2CallableStatement(cstmt,ins); cstmt.execute(); } catch (Exception e) { throw new BizException(e.getMessage()); }finally{ JdbcUtil.close(cstmt, conn); } }  /** * 调用有出入参的存储过程 * @param dbEnum * @param procedureName * @param ins */ public static List<Object> callProcedure(DbEnum dbEnum,String procedureName,List<Object> ins,List<ProcedureOutParameter> outs){ Connection conn = null; CallableStatement cstmt = null; List<Object> result = new ArrayList<Object>(); try { conn = getConnectionByDb(conn,dbEnum); StringBuilder strSb = new StringBuilder(); strSb.append(" {call "); strSb.append(procedureName); strSb.append("("); if(ins != null && ins.size() > 0){ for(int i=0; i<ins.size(); i++){ strSb.append("?,"); } if(outs != null && outs.size() >0){ for(int j=0; j<outs.size(); j++){ strSb.append("?,"); } } strSb.setLength(strSb.length()-1); } strSb.append(")}"); cstmt = conn.prepareCall(strSb.toString()); cstmt = listOject2CallableStatement(cstmt,ins); if(outs != null && outs.size() >0){ for(int j=0; j<outs.size(); j++){ cstmt.registerOutParameter(outs.get(j).getLeft(), outs.get(j).getRight()); } }  cstmt.execute(); if(outs != null && outs.size() >0){ for(int j=0; j<outs.size(); j++){ int type = outs.get(j).getRight(); int left = outs.get(j).getLeft(); switch(type){ case Types.CHAR: result.add(cstmt.getString(left)); break; case Types.DATE: result.add(cstmt.getDate(left)); break; case Types.INTEGER: result.add(cstmt.getBigDecimal(left)); break; case Types.FLOAT: result.add(cstmt.getBigDecimal(left)); break; case Types.DOUBLE: result.add(cstmt.getBigDecimal(left)); break; case Types.BOOLEAN: result.add(cstmt.getBoolean(left)); break; case Types.BLOB: result.add(cstmt.getBlob(left)); break; case Types.CLOB: result.add(cstmt.getClob(left)); break; case Types.NVARCHAR: result.add(cstmt.getString(left)); break; case Types.VARCHAR: result.add(cstmt.getString(left)); break; case Types.TIME: result.add(cstmt.getTime(left)); break; case Types.TIMESTAMP: result.add(cstmt.getTimestamp(left)); break; default: result.add(cstmt.getObject(left)); break; } } } } catch (Exception e) { throw new BizException(e.getMessage()); }finally{ JdbcUtil.close(cstmt, conn); } return result; }    public static CallableStatement listOject2CallableStatement(CallableStatement cstmt,List<Object> params){ if(cstmt != null && params != null && params.size() >0){ try{ for(int i=0; i<params.size(); i++){ Object p = params.get(i); if(p instanceof Integer){ cstmt.setInt(i+1, (Integer)p); }else if(p instanceof String){ cstmt.setString(i+1, (String)p); }else if(p instanceof Boolean){ cstmt.setBoolean(i+1, (Boolean)p); }else if(p instanceof Date){ // psmt.setDate(i+1, getDate((Date)p)); cstmt.setTimestamp(i+1, ResultSetTool.getTimestamp((Date)p)); }else if(p instanceof Double){ cstmt.setDouble(i+1, (Double)p); }else if(p instanceof Float){ cstmt.setFloat(i+1, (Float)p); }else if(p instanceof Long){ cstmt.setLong(i+1, (Long)p); }else if(p instanceof Blob){ cstmt.setBlob(i+1, (Blob)p); }else if(p instanceof Array){ cstmt.setArray(i+1, (Array)p); }else if(p instanceof BigDecimal){ cstmt.setBigDecimal(i+1, (BigDecimal)p); }else if(p instanceof Byte){ cstmt.setByte(i+1, (Byte)p); }else if(p instanceof Clob){ cstmt.setClob(i+1, (Clob)p); }else{ cstmt.setString(i+1, (String)p); } } }catch(Exception e){ throw new BizException("调用存储过程入参转换错误："+e.getMessage()); } } return cstmt; }   /** * 计算字符串包含的问号数量 * @param str * @return */ private static int countInterrogation(String str){ int count = 0; for(int i=0;i<str.length();i++){ if(str.charAt(i) == '?'){ count++; } } return count; } }

import java.lang.reflect.InvocationTargetException;import java.lang.reflect.Method;import java.math.BigDecimal;import java.sql.Array;import java.sql.Blob;import java.sql.Clob;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;import java.sql.Timestamp;import java.util.ArrayList;import java.util.Date;import java.util.HashMap;import java.util.List;import java.util.Map;
import net.sf.json.JSONArray;import net.sf.json.JSONException;import net.sf.json.JSONObject;
/** * JDBC的ResultSet工具类 * @author jianbin.lei * @date 2019-09-03 */public class ResultSetTool {
 /** * 转换数据 JDBC结果集转实体 * * @param resultSet 结果集 * @param clazz 实体字节码文件对象 * @param <T> 实体类型 * @throws Exception sql异常 NoClass异常等等 */ public static <T> List<T> resultSetToListBean(ResultSet resultSet, Class<T> clazz) throws Exception { //获取全部类方法 包括父类的 Method[] declaredMethods = clazz.getMethods(); List<T> list = new ArrayList<>(); ResultSetMetaData metaData = resultSet.getMetaData(); while (resultSet.next()) { //反射实例化对象 T obj = clazz.newInstance(); //遍历类调用方法 for (Method method : declaredMethods) { //获取方法名 String name = method.getName(); if (!name.startsWith("set")){ //只要setter continue; } //获取数据库名 驼峰命名法转数据库字段命名法 String dbName = getDbName(name); //遍历数据库所有列 for (int i = 1; i <= metaData.getColumnCount(); i++) { //抓取指定列赋值 if (metaData.getColumnName(i).equalsIgnoreCase(dbName)) { if (resultSet.getObject(i) != null) { //赋值 setValue(obj,method,resultSet,i); } break; } } } list.add(obj); } return list; }  /** * 将resultSet转换为List<Map<String,Object>> * @param rs * @return * @throws Exception */ public static List<Map<String,Object>> resultSetToListMap(ResultSet rs) throws Exception { //获取全部类方法 包括父类的 List<Map<String,Object>> list = new ArrayList<Map<String,Object>>(); ResultSetMetaData metaData = rs.getMetaData(); int columnCount = metaData.getColumnCount();  // 遍历ResultSet中的每条数据  while (rs.next()) {  Map<String,Object> map = new HashMap<String,Object>(); // 遍历每一列  for (int i = 1; i <= columnCount; i++) {  String columnName =metaData.getColumnLabel(i);  String value = rs.getString(columnName);  map.put(columnName, value);  }  list.add(map);  }  return list; }   /** * 将resultSet转化为JSON数组 * @param rs * @return * @throws SQLException * @throws JSONException */ public static JSONArray resultSetToJsonArry(ResultSet rs) throws SQLException,JSONException {  // json数组  JSONArray array = new JSONArray();  // 获取列数  ResultSetMetaData metaData = rs.getMetaData();  int columnCount = metaData.getColumnCount();  // 遍历ResultSet中的每条数据  while (rs.next()) {  JSONObject jsonObj = new JSONObject();  // 遍历每一列  for (int i = 1; i <= columnCount; i++) {  String columnName =metaData.getColumnLabel(i);  String value = rs.getString(columnName);  jsonObj.put(columnName, value);  }  array.add(jsonObj);  }  return array;  }
 /** * 将resultSet转化为JSONObject * @param rs * @return * @throws SQLException * @throws JSONException */ public static JSONObject resultSetToJsonObject(ResultSet rs) throws SQLException,JSONException {  // json对象 JSONObject jsonObj = new JSONObject(); 
 // 获取列数  ResultSetMetaData metaData = rs.getMetaData();  int columnCount = metaData.getColumnCount();  // 遍历ResultSet中的每条数据  if (rs.next()) {  // 遍历每一列  for (int i = 1; i <= columnCount; i++) {  String columnName = metaData.getColumnLabel(i);  String value = rs.getString(columnName);  jsonObj.put(columnName, value);  }  } return jsonObj;  }
 /** * 将list参数设置到PreparedStatement中 * @param list * @param psmt * @return * @throws SQLException  */ public static PreparedStatement listObjectSetToPs(List<Object> params,PreparedStatement psmt) throws SQLException{ if(params != null && params.size() >0){ for(int i=0;i<params.size();i++){ Object p = params.get(i); if(p instanceof Integer){ psmt.setInt(i+1, (Integer)p); }else if(p instanceof String){ psmt.setString(i+1, (String)p); }else if(p instanceof Boolean){ psmt.setBoolean(i+1, (Boolean)p); }else if(p instanceof Date){// psmt.setDate(i+1, getDate((Date)p)); psmt.setTimestamp(i+1, getTimestamp((Date)p)); }else if(p instanceof Double){ psmt.setDouble(i+1, (Double)p); }else if(p instanceof Float){ psmt.setFloat(i+1, (Float)p); }else if(p instanceof Long){ psmt.setLong(i+1, (Long)p); }else if(p instanceof Blob){ psmt.setBlob(i+1, (Blob)p); }else if(p instanceof Array){ psmt.setArray(i+1, (Array)p); }else if(p instanceof BigDecimal){ psmt.setBigDecimal(i+1, (BigDecimal)p); }else if(p instanceof Byte){ psmt.setByte(i+1, (Byte)p); }else if(p instanceof Clob){ psmt.setClob(i+1, (Clob)p); }else{ psmt.setString(i+1, (String)p); } } } return psmt;  }  public static Timestamp getTimestamp(Date date){ return new Timestamp(date.getTime()); }  public static java.sql.Date getDate(long time){ return new java.sql.Date(time); }
 public static java.sql.Date getDate(Date date){ return new java.sql.Date(date.getTime()); }
  /** * 赋值操作， * 主要是处理数据类型 * 此处只简单处理下基本数据类型和Date类型 * @param obj 泛型对象 * @param method 方法 * @param resultSet 结果集 * @param i 脚标 * @param <T> 泛型 */ private static <T> void setValue(T obj, Method method, ResultSet resultSet, int i) throws SQLException, InvocationTargetException, IllegalAccessException { //Setter方法只有一个参数，获取参数类型名称 String name = method.getParameterTypes()[0].getName().toLowerCase(); if (name.contains("string")){ method.invoke(obj, resultSet.getString(i)); }else if (name.contains("short")){ method.invoke(obj,resultSet.getShort(i)); }else if (name.contains("int") || name.contains("integer")){ method.invoke(obj,resultSet.getInt(i)); }else if (name.contains("bigdecimal")){ method.invoke(obj,resultSet.getBigDecimal(i)); }else if(name.contains("long")){  method.invoke(obj,resultSet.getLong(i)); }else if (name.contains("float")){ method.invoke(obj,resultSet.getFloat(i)); }else if (name.contains("double")){ method.invoke(obj,resultSet.getDouble(i)); }else if (name.contains("boolean")){ method.invoke(obj,resultSet.getBoolean(i)); }else if (name.contains("date")){ method.invoke(obj,resultSet.getDate(i)); }else { method.invoke(obj, resultSet.getObject(i)); } }
 /** * 实体setter名称转对应数据库列的列名 * 需要遵守命名规范，java（驼峰命名法），数据库（全小写，单词间用'_'隔开） * @param name setter名称 * @return 数据库列名 */ private static String getDbName(String name) { //根据setter命名规则获取对应的属性名 name = name.substring(3,4).toLowerCase()+name.substring(4); //获取数据库对应列名 StringBuffer buffer = new StringBuffer(); char[] nameChars = name.toCharArray(); for (char nameChar : nameChars) { if (nameChar >= 'A' && nameChar <= 'Z') { //将大写字母转换为下划线和对应的小写字母组合 buffer.append("_").append(String.valueOf(nameChar).toLowerCase()); } else { buffer.append(String.valueOf(nameChar)); } } return buffer.toString(); }}

 StringBuilder sql = new StringBuilder(); sql.append(" UPDATE MES3_BASKET SET BASKET_NO =?,BASKET_NAME=?"); sql.append(" ,BASKET_DESC =?,BASKET_QTY=?,MODIFY_USER=? "); sql.append(" ,MODIFY_DATE=sysdate,BASKET_KZLS=?,BASKET_TYPE=?"); sql.append(" WHERE PID= ?");  List<Object> paramsList = new ArrayList<>(); paramsList.add(map.get("BASKET_NO")); paramsList.add(map.get("BASKET_NAME")); paramsList.add(map.get("BASKET_DESC")); paramsList.add(map.get("BASKET_QTY")); paramsList.add(map.get("MODIFY_USER")); paramsList.add(map.get("BASKET_KZLS")); paramsList.add(map.get("BASKET_TYPE")); paramsList.add(map.get("PID")); JdbcTool.update(DbEnum.PROD, sql.toString(), paramsList);

String sfc = "1000020001";StringBuilder sqlSb = new StringBuilder();sqlSb.append(" SELECT DATA03 FROM Z_FORMES_SFC WHERE SFC ='").append(sfc).append("'");Map<String,Object> sfcMap = JdbcTool.queryForMapSingle(DbEnum.PROD, sqlSb.toString());

StringBuilder sqlSb = new StringBuilder();sqlSb.append("SELECT * FROM STUDENT WHERE NAME='张三'");return JdbcTool.queryForBean(DbEnum.PROD, sqlSb.toString(), Student.class);

StringBuilder sql = new StringBuilder(""); sql.append(" select PID, SEQ, NAME, OPERATION_ID, RESOURCE_ID, STATUS, STEP_MAIN_ID, SIMPLE");    sql.append(" from STEP_DETAIL "); sql.append(" where RESOURCE_ID = ? and OPERATION_ID = ? order by SEQ ");List<Object> paramsList = new ArrayList<>();paramsList.add("123123");paramsList.add("2222"); List<StepDetail> param =JdbcTool.queryForList(DbEnum.PROD, sql.toString(), paramsList,StepDetail.class);

StringBuilder sqlSb = new StringBuilder();sqlSb.append("SELECT * FROM STUDENT");List<Map<String,Object>> cellList = JdbcTool.queryForMap(DbEnum.PROD, sqlSb.toString());

StringBuilder sql = new StringBuilder(); sql.append("SELECT a.RESRCE AS resourceNo,b.OPERATION AS opeationNo,a.HANDLE AS R_HANDLE,b.HANDLE AS O_HANDLE"); sql.append(" FROM RESRCE a"); sql.append(" INNER JOIN OPERATION b ON SUBSTR (a.OPERATION_BO, 0,INSTR (a.OPERATION_BO, ',', -2, 1)-1) = SUBSTR (b.HANDLE, 0,INSTR (b.HANDLE, ',', -2, 1)-1) "); sql.append(" WHERE 1=1 ");    sql.append(" AND a.RESRCE = ? ");//问号为占位符 sql.append(" AND a.OPERATION_BO IS NOT NULL "); sql.append(" ORDER BY RESRCE "); List<Object> paramsList = new ArrayList<>(); paramsList.add(resourceNo);//根据顺序，查询时候替换占位符 List<Map<String, Object>> param =JdbcTool.queryForMap(DbEnum.TRANSF, sql.toString(), paramsList);

 List<List<Object>> paramsList = new ArrayList<>(); List<Object> params = new ArrayList<>();  params.add("112312");  params.add(112312);  params.add("3213212");  paramsList.add(params);  String sql = " DELETE FROM STEP_MAIN WHERE PID=? AND MM=? AND CC=?";  JdbcTool.batchUpdate(DbEnum.PROD, sql, paramsList);

List<Object> ins = new ArrayList<>(); ins.add(mResource.get("OPEATIONNO"));    ins.add(map.get("SCAN_VALUE"));List<ProcedureOutParameter> outs = new ArrayList<>(); ProcedureOutParameter p = new ProcedureOutParameter(); p.setLeft(3); p.setRight(1); outs.add(p);List<Object> list = JdbcTool.callProcedure(DbEnum.PROD, "P_POD_GETFZREWORK", ins, outs);