已正常发布线上,暂无bug
之前由于工作需要,连接多台远程数据库,需要jdbc转查询切换,为了通用性,少改动不必要的复杂sql代码,就写了通用转换的类。 参数说明: 1.传返回结果集对应的对象实体类, 2.sql, 3.map参数 根据3个条件来一步调用,返回list<map>或list<Object>对象的结果集封装。这样就不需要改动业务逻辑与前端展示。 package com.ceair.util.db; import com.ceair.pojo.cdi.PnrIts; import com.ceair.pojo.cdi.PnrItsOts; import com.ceair.pojo.cdi.PnrOts; import com.ceair.util.DESEncryptTools; import com.ceair.util.DbPropertiesUtil; import com.mchange.v2.c3p0.ComboPooledDataSource; import org.apache.commons.lang3.StringUtils; import java.io.IOException; import java.lang.reflect.Field; import java.math.BigDecimal; import java.sql.*; import java.util.*; import java.util.Date; public class DataPool { private static Connection conn = null; private static PreparedStatement st = null; private static ResultSet rs =null; public static void main(String[] args) { HashMap hashMap=new HashMap(); hashMap.put("airport","PVG"); hashMap.put("airport1","PVG"); hashMap.put("airport2","PVG"); String sql =" SELECT TZNAME,INTNL_FLAG as INtL_FLAG FROM blp.AIRPORT WHERE CODE = :airport "; PnrItsOts list = (PnrItsOts)DataPool.jdbcObject("ODS", PnrItsOts.class, sql, hashMap); System.out.println(list); } /** * 查询list<map> * @param dataSql * @param map * @return */ public static List jdbcMapObject(String dataSource,String dataSql,Map map){ try{ // 打开链接 // 注册 JDBC 驱动 Class.forName(getValue("gui.dbDriver", "oracle.jdbc.driver.OracleDriver")); if(dataSource.equals("GUI")) { conn = DriverManager.getConnection( new String(DESEncryptTools.decrypt(DESEncryptTools.hexStringToBytes(getValue("gui.url", "270db52930929233db5b963e293dc8468d2de4891420a1b04418ac20ce238378550257b40f9f37fdfe93a6f140429989")))), new String(DESEncryptTools.decrypt(DESEncryptTools.hexStringToBytes(getValue("gui.username", "0c45c95567a19c23")))), new String(DESEncryptTools.decrypt(DESEncryptTools.hexStringToBytes(getValue("gui.password", "56d6f272ba897dfb"))))); }else if(dataSource.equals("CDI")) { conn = DriverManager.getConnection( new String(DESEncryptTools.decrypt(DESEncryptTools.hexStringToBytes(getValue("cdi.url", "270db52930929233db5b963e293dc8468d2de4891420a1b04418ac20ce238378550257b40f9f37fdfe93a6f140429989")))), new String(DESEncryptTools.decrypt(DESEncryptTools.hexStringToBytes(getValue("cdi.username", "0c45c95567a19c23")))), new String(DESEncryptTools.decrypt(DESEncryptTools.hexStringToBytes(getValue("cdi.password", "56d6f272ba897dfb"))))); }else if(dataSource.equals("ods")) { conn = DriverManager.getConnection( new String(DESEncryptTools.decrypt(DESEncryptTools.hexStringToBytes(getValue("ods.url", "270db52930929233db5b963e293dc8468d2de4891420a1b04418ac20ce238378550257b40f9f37fdfe93a6f140429989")))), new String(DESEncryptTools.decrypt(DESEncryptTools.hexStringToBytes(getValue("ods.username", "0c45c95567a19c23")))), new String(DESEncryptTools.decrypt(DESEncryptTools.hexStringToBytes(getValue("ods.password", "56d6f272ba897dfb"))))); } // 执行查询 st = conn.prepareStatement(setQueryParam(dataSql,map)); rs=st.executeQuery(); return selectAll(rs); }catch(Exception e){ e.printStackTrace(); } finally{ close(st,conn, rs); } return null; } /** * 查询list对象<Object> * @param dataSql * @param map * @param clazz * @return */ public static List jdbcListObject(String dataSource,Class clazz,String dataSql,Map map){ try{ // 打开链接 // 注册 JDBC 驱动 Class.forName(getValue("gui.dbDriver", "oracle.jdbc.driver.OracleDriver")); if(dataSource.equals("GUI")) { conn = DriverManager.getConnection( new String(DESEncryptTools.decrypt(DESEncryptTools.hexStringToBytes(getValue("gui.url", "270db52930929233db5b963e293dc8468d2de4891420a1b04418ac20ce238378550257b40f9f37fdfe93a6f140429989")))), new String(DESEncryptTools.decrypt(DESEncryptTools.hexStringToBytes(getValue("gui.username", "0c45c95567a19c23")))), new String(DESEncryptTools.decrypt(DESEncryptTools.hexStringToBytes(getValue("gui.password", "56d6f272ba897dfb"))))); }else if(dataSource.equals("CDI")) { conn = DriverManager.getConnection( new String(DESEncryptTools.decrypt(DESEncryptTools.hexStringToBytes(getValue("cdi.url", "270db52930929233db5b963e293dc8468d2de4891420a1b04418ac20ce238378550257b40f9f37fdfe93a6f140429989")))), new String(DESEncryptTools.decrypt(DESEncryptTools.hexStringToBytes(getValue("cdi.username", "0c45c95567a19c23")))), new String(DESEncryptTools.decrypt(DESEncryptTools.hexStringToBytes(getValue("cdi.password", "56d6f272ba897dfb"))))); }else if(dataSource.equals("ODS")) { conn = DriverManager.getConnection( new String(DESEncryptTools.decrypt(DESEncryptTools.hexStringToBytes(getValue("ods.url", "270db52930929233db5b963e293dc8468d2de4891420a1b04418ac20ce238378550257b40f9f37fdfe93a6f140429989")))), new String(DESEncryptTools.decrypt(DESEncryptTools.hexStringToBytes(getValue("ods.username", "0c45c95567a19c23")))), new String(DESEncryptTools.decrypt(DESEncryptTools.hexStringToBytes(getValue("ods.password", "56d6f272ba897dfb"))))); } // 执行查询 st = conn.prepareStatement(setQueryParam(dataSql,map)); rs=st.executeQuery(); return selectAll(rs,clazz); }catch(Exception e){ e.printStackTrace(); } finally{ close(st,conn, rs); } return null; } /** * 查询list对象<Object> * @param dataSql * @param map * @param clazz * @return */ public static Object jdbcObject(String dataSource,Class clazz,String dataSql,Map map){ try{ // 打开链接 // 注册 JDBC 驱动 Class.forName(getValue("gui.dbDriver", "oracle.jdbc.driver.OracleDriver")); if(dataSource.equals("GUI")) { conn = DriverManager.getConnection( new String(DESEncryptTools.decrypt(DESEncryptTools.hexStringToBytes(getValue("gui.url", "270db52930929233db5b963e293dc8468d2de4891420a1b04418ac20ce238378550257b40f9f37fdfe93a6f140429989")))), new String(DESEncryptTools.decrypt(DESEncryptTools.hexStringToBytes(getValue("gui.username", "0c45c95567a19c23")))), new String(DESEncryptTools.decrypt(DESEncryptTools.hexStringToBytes(getValue("gui.password", "56d6f272ba897dfb"))))); }else if(dataSource.equals("CDI")) { conn = DriverManager.getConnection( new String(DESEncryptTools.decrypt(DESEncryptTools.hexStringToBytes(getValue("cdi.url", "270db52930929233db5b963e293dc8468d2de4891420a1b04418ac20ce238378550257b40f9f37fdfe93a6f140429989")))), new String(DESEncryptTools.decrypt(DESEncryptTools.hexStringToBytes(getValue("cdi.username", "0c45c95567a19c23")))), new String(DESEncryptTools.decrypt(DESEncryptTools.hexStringToBytes(getValue("cdi.password", "56d6f272ba897dfb"))))); }else if(dataSource.equals("ODS")) { conn = DriverManager.getConnection( new String(DESEncryptTools.decrypt(DESEncryptTools.hexStringToBytes(getValue("ods.url", "270db52930929233db5b963e293dc8468d2de4891420a1b04418ac20ce238378550257b40f9f37fdfe93a6f140429989")))), new String(DESEncryptTools.decrypt(DESEncryptTools.hexStringToBytes(getValue("ods.username", "0c45c95567a19c23")))), new String(DESEncryptTools.decrypt(DESEncryptTools.hexStringToBytes(getValue("ods.password", "56d6f272ba897dfb"))))); } // 执行查询 st = conn.prepareStatement(setQueryParam(dataSql,map)); rs=st.executeQuery(); return selectObject(rs,clazz); }catch(Exception e){ e.printStackTrace(); } finally{ close(st,conn, rs); } return null; } public static int updateObject(String dataSource,String dataSql,Map map) { int count=0; try{ // 打开链接 // 注册 JDBC 驱动 Class.forName(getValue("gui.dbDriver", "oracle.jdbc.driver.OracleDriver")); if(dataSource.equals("GUI")) { conn = DriverManager.getConnection( new String(DESEncryptTools.decrypt(DESEncryptTools.hexStringToBytes(getValue("gui.url", "270db52930929233db5b963e293dc8468d2de4891420a1b04418ac20ce238378550257b40f9f37fdfe93a6f140429989")))), new String(DESEncryptTools.decrypt(DESEncryptTools.hexStringToBytes(getValue("gui.username", "0c45c95567a19c23")))), new String(DESEncryptTools.decrypt(DESEncryptTools.hexStringToBytes(getValue("gui.password", "56d6f272ba897dfb"))))); }else if(dataSource.equals("CDI")) { conn = DriverManager.getConnection( new String(DESEncryptTools.decrypt(DESEncryptTools.hexStringToBytes(getValue("cdi.url", "270db52930929233db5b963e293dc8468d2de4891420a1b04418ac20ce238378550257b40f9f37fdfe93a6f140429989")))), new String(DESEncryptTools.decrypt(DESEncryptTools.hexStringToBytes(getValue("cdi.username", "0c45c95567a19c23")))), new String(DESEncryptTools.decrypt(DESEncryptTools.hexStringToBytes(getValue("cdi.password", "56d6f272ba897dfb"))))); }else if(dataSource.equals("ods")) { conn = DriverManager.getConnection( new String(DESEncryptTools.decrypt(DESEncryptTools.hexStringToBytes(getValue("ods.url", "270db52930929233db5b963e293dc8468d2de4891420a1b04418ac20ce238378550257b40f9f37fdfe93a6f140429989")))), new String(DESEncryptTools.decrypt(DESEncryptTools.hexStringToBytes(getValue("ods.username", "0c45c95567a19c23")))), new String(DESEncryptTools.decrypt(DESEncryptTools.hexStringToBytes(getValue("ods.password", "56d6f272ba897dfb"))))); } // 执行查询 st = conn.prepareStatement(setQueryParam(dataSql,map)); count= st.executeUpdate(); }catch(Exception e){ e.printStackTrace(); } finally{ close(st,conn, rs); } return count; } /** * rs结果集转list<Map> * @param rs * @return */ private static List<Map<String, Object>> selectAll(ResultSet rs) { List<Map<String, Object>> list = new ArrayList<Map<String, Object>>(); try { // 获取结果集结构(元素据) ResultSetMetaData rmd = rs.getMetaData(); int columnCount = rmd.getColumnCount(); while (rs.next()) { // 保存记录中的每个<字段名-字段值> Map<String, Object> rowData = new HashMap<String, Object>(); for (int i = 1; i <= columnCount; ++i) { // <字段名-字段值> rowData.put(rmd.getColumnName(i), rs.getObject(i)); //System.out.println(rmd.getColumnName(i)+":"+ rs.getObject(i)); } list.add(rowData); } } catch (Exception ex) { ex.printStackTrace(); } return list; } /** * rs结果集转集合对象 * @param rs * @return */ private static List selectAll(ResultSet rs, Class clazz) throws SQLException, IllegalAccessException, InstantiationException { //结果集的元素对象 ResultSetMetaData rsmd = rs.getMetaData(); //获取结果集的元素个数 int colCount = rsmd.getColumnCount(); //返回结果的列表集合 List list = new ArrayList(); //业务对象的属性数组 Field[] fields = clazz.getDeclaredFields(); while(rs.next()){//对每一条记录进行操作 Object obj = clazz.newInstance();//构造业务对象实体 //将每一个字段取出进行赋值 for(int i = 1;i<=colCount;i++){ Object value = rs.getObject(i); //寻找该列对应的对象属性 for(int j=0;j<fields.length;j++){ Field f = fields[j]; //如果匹配进行赋值,并替换预编译占位符 if(f.getName().equalsIgnoreCase(getName(rsmd.getColumnName(i),rsmd.getColumnName(i)))){ boolean flag = f.isAccessible(); f.setAccessible(true); try{ f.set(obj,value); }catch (Exception e){ try{ BigDecimal b= (BigDecimal) value; f.set(obj,b.longValue()); }catch (Exception ex){ BigDecimal b= (BigDecimal) value; f.set(obj,b.intValue()); } } f.setAccessible(flag); } } } list.add(obj); } return list; } /** * rs结果集转单对象 * @param rs * @return */ private static Object selectObject(ResultSet rs, Class clazz) throws SQLException, IllegalAccessException, InstantiationException { //结果集的元素对象 ResultSetMetaData rsmd = rs.getMetaData(); //获取结果集的元素个数 int colCount = rsmd.getColumnCount(); //返回结果的列表 Object obj = clazz.newInstance();//构造业务对象实体 //业务对象的属性数组 Field[] fields = clazz.getDeclaredFields(); while(rs.next()){//对每一条记录进行操作 //将每一个字段取出进行赋值 for(int i = 1;i<=colCount;i++){ Object value = rs.getObject(i); //寻找该列对应的对象属性 for(int j=0;j<fields.length;j++){ Field f = fields[j]; //如果匹配进行赋值,并替换预编译占位符 if(f.getName().equalsIgnoreCase(getName(rsmd.getColumnName(i),rsmd.getColumnName(i)))){ boolean flag = f.isAccessible(); f.setAccessible(true); try{ f.set(obj,value); }catch (Exception e){ try{ BigDecimal b= (BigDecimal) value; f.set(obj,b.longValue()); }catch (Exception ex){ BigDecimal b= (BigDecimal) value; f.set(obj,b.intValue()); } } f.setAccessible(flag); } } } } return obj; } /** * 去下划线后字母大写 * @param name * @param anotherName * @return 例子:getName("of_terminal","of_terminal") */ private static String getName(String name,String anotherName) { name=anotherName; //如果最后一个是_ 不做转换 if(name.indexOf("_")>0&&name.length()!=name.indexOf("_")+1){ int lengthPlace=name.indexOf("_"); name=name.replaceFirst("_", ""); String s=name.substring(lengthPlace, lengthPlace+1); s=s.toUpperCase(); anotherName=name.substring(0,lengthPlace)+s+name.substring(lengthPlace+1); }else{ return anotherName; } return getName(name,anotherName); } /** * 预编译 * @param sql * @param params */ private static String setQueryParam( String sql,Map<String, ?> params) { if (null != params) { Iterator i$ = params.keySet().iterator(); while(i$.hasNext()) { String key = (String)i$.next(); if(params.get(key) instanceof Integer ||params.get(key) instanceof Long){ sql=sql.replaceAll(":"+key, params.get(key).toString()+" "); }else if((params.get(key) instanceof String)){ sql=sql.replaceAll(":"+key, "'"+params.get(key).toString()+"'"); }else if((params.get(key) instanceof Double)){ sql=sql.replaceAll(":"+key, "'"+params.get(key).toString()+"'"); }else if((params.get(key) instanceof Date)){ sql=sql.replaceAll(":"+key, "'"+params.get(key).toString()+"'"); }else{ if(params.get(key)==null){ sql=sql.replaceAll(":"+key, "''"); } } } } return sql; } /** * 关闭资源 * @param st * @param conn * @param rs */ public static void close(PreparedStatement st,Connection conn,ResultSet rs){ // 关闭资源 try{ if(st!=null) { st.close(); } }catch(SQLException se2){ }// 什么都不做 try{ if(conn!=null) { conn.close(); } }catch(SQLException se){ se.printStackTrace(); } try{ if(rs!=null) { conn.close(); } }catch(SQLException se){ se.printStackTrace(); } } private static String getValue(String s, String defaultValue) throws IOException { return new DbPropertiesUtil().getValue(s, defaultValue); } }