博客信息

手写的仿hibernate封装传参

0
发布时间:『 2019-08-30 14:48』  博客类别:util类  阅读(709) 评论(0)


已正常发布线上,暂无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);
    }
}





关键字:   无
博主信息

勿扰
简介:对自己狠一点,社会才会对你好一点!
4年9月
QQ
热门文章
Powered by 勿扰 V2.0 湘ICP备18002237号-2     Copyright © 2016-2023 勿扰个人博客 版权所有