测试使用jdbc工具类简化JDBC-新增开发

方法说明

此jdbc测试工具类是为了简化使用jdbc进行新增的方法，使用此方法可以避免使用uuid生成的主键，此举主要是为了如果在有一个同样数据库，里面两条数据的主键一致性问题。因为使用hibernate可以自动生成32位的uuid

代码如下：

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
import java.util.Set;

import org.apache.commons.lang3.StringUtils;

import com.cloud.icenter.common.utils.DESUtil;
import com.cloud.icenter.common.utils.JDBCUtil;
import com.cloud.icenter.common.utils.JDBCUtil.DataBaseType;

/**
 * 测试使用JDBCUtil工具类来简化JDBC开发
 * @author ynxiea
 */
public class JdbcOperationUtil {
    static Properties pros = null; // 可以帮助读取和处理资源文件中的信息
    static { // 加载JDBCUtil类的时候调用。注意这里的话，只会被加载一次。
        pros = new Properties();
        try{
            //关键的代码 
            pros.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("jdbc.properties"));

            // 对jdbc.properties 文件进行解密
            String userName = pros.getProperty("jdbc.username");  
            if (userName != null) {  
                pros.setProperty("jdbc.username",DESUtil.decrypt(userName, DESUtil.key)); 
            }  

            String passWord = pros.getProperty("jdbc.password");  
            if (passWord != null) {  
                pros.setProperty("jdbc.password", DESUtil.decrypt(passWord, DESUtil.key));  
            }  

            String url = pros.getProperty("jdbc.url");  
            if (url != null) {  
                pros.setProperty("jdbc.url", DESUtil.decrypt(url, DESUtil.key));  
            }  

            String driver = pros.getProperty("jdbc.driver");  
            if(driver != null){  
                pros.setProperty("jdbc.driver", DESUtil.decrypt(driver, DESUtil.key));  
            }

            String dialect = pros.getProperty("hibernate.dialect");  
            if (dialect != null) {  
                pros.setProperty("hibernate.dialect", DESUtil.decrypt(dialect, DESUtil.key));  
            }
        } catch(Exception e){
            e.printStackTrace();
        }
    }

    /**
     * 获取JDBC数据库连接信息
     * @return
     * @throws Exception
     */
    public static Connection getConnection() throws Exception {
        return JDBCUtil.getConnection(pros.getProperty("jdbc.url"),
                    pros.getProperty("jdbc.username"),
                    pros.getProperty("jdbc.password"),
                    DataBaseType.MYSQL);
    }

    /**
     * 保存数据
     * @param tableName 表名称
     * @param data 数据
     * @throws Exception
     */
    public static int saveData(String tableName, Map<String, Object> data) {
        Connection conn = null;
        PreparedStatement pst = null;
        try {
            if (!StringUtils.isEmpty(tableName) && data != null && data.size()>0) {
                conn =  getConnection();
                StringBuilder sb = new StringBuilder();
                sb.append(" insert into ");
                sb.append(tableName);
                sb.append("(");
                Set<String> sets =  data.keySet();
                int size = sets.size();
                int count = 1;
                for (String key : sets) {
                    if (count == size) {
                        sb.append(key);
                    } else {
                        sb.append(key+",");
                    }
                    count++;
                }
                sb.append(")");
                sb.append(" VALUES (");

                int count1 = 1;
                for (String key : sets) {
                    if (count1 == size) {
                        sb.append("?");
                    } else {
                        sb.append("?,");
                    }
                    count1++;
                }
                sb.append(")");

                //System.out.println("sql="+sb.toString());
                pst = conn.prepareStatement(sb.toString());

                int start = 1;
                for (String key : sets) {
                    pst.setObject(start, data.get(key));
                    start++;
                }
                int res = pst.executeUpdate();
                return res;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                pst.close();
                conn.close();
                pst = null;
                conn = null;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return 0;
    }

    public static void main(String[] args) throws Exception {
        //System.out.println(getConnection());
        String tableName = "irsp_coding_org";
        Map<String, Object> data = new HashMap<String, Object>();
        data.put("ID", "8a90fa955d888e2f015d88bcd66f0008");
        data.put("ORG_ID", "8a90fa955d888e2f015d88bcd66f9998");
        data.put("CODE_ID", "8a90fa955d888e2f015d88bcd66f6668");
        saveData(tableName, data);
    }

}

上面方法所用到的工具类如下：

第一个为DESUtil,代码如下：

import java.io.IOException;
import java.security.SecureRandom;

import javax.crypto.Cipher;
import javax.crypto.SecretKey;
import javax.crypto.SecretKeyFactory;
import javax.crypto.spec.DESKeySpec;

import sun.misc.BASE64Decoder;
import sun.misc.BASE64Encoder;

public class DESUtil {

private final static String DES = "DES";
public static String key="cc6b473b7ea2f23546d0361573b98b30";

public static void main(String[] args) throws Exception {
String data = "jdbc:mysql://10.16.123.149:3306/irsp_xm?useUnicode=true&characterEncoding=utf8";
//  String key = "wang!@#$%";
System.err.println(encrypt(data, key));
System.err.println(decrypt(encrypt(data, key), key));

}

/**
 * Description 根据键值进行加密
 * @param data 
 * @param key  加密键byte数组
 * @return
 * @throws Exception
 */
public static String encrypt(String data, String key) throws Exception {
byte[] bt = encrypt(data.getBytes("UTF-8"), key.getBytes("UTF-8"));
String strs = new BASE64Encoder().encode(bt);
return strs;
}

/**
 * Description 根据键值进行解密
 * @param data
 * @param key  加密键byte数组
 * @return
 * @throws IOException
 * @throws Exception
 */
public static String decrypt(String data, String key){
    try {
        if (data == null)
            return null;
        BASE64Decoder decoder = new BASE64Decoder();
        byte[] buf = decoder.decodeBuffer(data);
        byte[] bt = decrypt(buf,key.getBytes("UTF-8"));
        return new String(bt,"UTF-8");
        } catch (Exception e) {
            return data;
        }
}

/**
 * Description 根据键值进行加密
 * @param data
 * @param key  加密键byte数组
 * @return
 * @throws Exception
 */
private static byte[] encrypt(byte[] data, byte[] key) throws Exception {
// 生成一个可信任的随机数源
SecureRandom sr = new SecureRandom();

// 从原始密钥数据创建DESKeySpec对象
DESKeySpec dks = new DESKeySpec(key);

// 创建一个密钥工厂，然后用它把DESKeySpec转换成SecretKey对象
SecretKeyFactory keyFactory = SecretKeyFactory.getInstance(DES);
SecretKey securekey = keyFactory.generateSecret(dks);

// Cipher对象实际完成加密操作
Cipher cipher = Cipher.getInstance(DES);

// 用密钥初始化Cipher对象
cipher.init(Cipher.ENCRYPT_MODE, securekey, sr);

return cipher.doFinal(data);
}


/**
 * Description 根据键值进行解密
 * @param data
 * @param key  加密键byte数组
 * @return
 * @throws Exception
 */
private static byte[] decrypt(byte[] data, byte[] key) throws Exception {
// 生成一个可信任的随机数源
SecureRandom sr = new SecureRandom();

// 从原始密钥数据创建DESKeySpec对象
DESKeySpec dks = new DESKeySpec(key);

// 创建一个密钥工厂，然后用它把DESKeySpec转换成SecretKey对象
SecretKeyFactory keyFactory = SecretKeyFactory.getInstance(DES);
SecretKey securekey = keyFactory.generateSecret(dks);

// Cipher对象实际完成解密操作
Cipher cipher = Cipher.getInstance(DES);

// 用密钥初始化Cipher对象
cipher.init(Cipher.DECRYPT_MODE, securekey, sr);

return cipher.doFinal(data);
}
}

第二个为JDBCUtils,代码如下：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.springframework.util.StringUtils;

import com.cloud.icenter.system.data.utils.DictConstans;


/**
 * JDBC工具类
 * @author byl
 *
 */
public class JDBCUtil {

    public JDBCUtil() {  
super();  
}  

public static Connection getConnection(String url,String username,String password , DataBaseType dbType) throws SQLException, ClassNotFoundException, InstantiationException, IllegalAccessException {
    Class.forName(dbType.getDriverName());
    //设置超时时间，暂时解决错误密码等待时间
    DriverManager.setLoginTimeout(1000);
return DriverManager.getConnection(url, username, password);
}  

public static Connection getConnection(String dbIp , String port , String dbName , String userName , String password , String instanceName , DataBaseType dbType) throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException{
    if(!StringUtil.isEmpty(dbIp) && !StringUtil.isEmpty(userName) && !StringUtil.isEmpty(password) && dbType != null){
        return getConnection(getJdbcConnectionUrl(dbIp, dbName, port, instanceName, dbType), userName, password, dbType);
    }
    return null;
}

public static void close(ResultSet rs, Statement st, Connection conn) throws SQLException {  
try {  
if (rs != null) {  
rs.close();  

}  
}finally{  
try {  
if(st!=null){  
st.close();  
}  
}finally{  
if(conn!=null){  
conn.close();  
}  
}  
}  

}  

    /**
     * jdbc url解析
     * @param url
     * @return
     */
    public static DataBaseObj parse(String url){
        if(!StringUtil.isEmpty(url)){
            DataBaseObj dbo = new DataBaseObj();

            url = url.replace("jdbc:oracle:thin:@", "");
            String[] params = url.split(":");
            dbo.setHost(params[0]);
            dbo.setPort(params[1]);
            dbo.setDbName(params[2]);

            return dbo;
        }
        return null;
    }

    /**
     * 将IP地址和databaseName根据数据库类型，转换成标准的连接URL
     * @param dbIp
     * @param dbName
     * @param dbType
     * @param 
     * @return
     */
    public static String getJdbcConnectionUrl(String dbIp , String dbName , String port , String instanceName , DataBaseType dbType){
        if(StringUtils.hasText(dbName) && StringUtils.hasText(port) && StringUtils.hasText(dbIp) && dbType != null){
            if(DataBaseType.MYSQL.equals(dbType) || DictConstans.DB_TYPE_MYSQL.equals(dbType)){
                //MYSQL
                return String.format(dbType.getDriverUrl(), dbIp , port , dbName);
            }else if(DataBaseType.ORACLE.equals(dbType) || DictConstans.DB_TYPE_ORACLE.equals(dbType)){
                //Oracle
                return String.format(dbType.getDriverUrl(), dbIp , port , dbName);
            }else if(DataBaseType.SQLSERVER.equals(dbType) || DictConstans.DB_TYPE_SQLSERVER.equals(dbType)){
                //Sql Server
                if(StringUtils.hasText(instanceName)){
                    return String.format(dbType.getDriverUrl(), dbIp , port , instanceName , dbName);
                }
            }else if(DataBaseType.DM.equals(dbType) || DictConstans.DB_TYPE_DM.equals(dbType)){
                //达梦数据库
                return String.format(dbType.getDriverUrl(), dbIp , port , dbName);
            }
        }
        return null;
    }
    /**
     * 根据dbtype获取相应数据库的驱动类型
     * @param dbType
     * @return
     */
    public static DataBaseType getDataBaseTypeByDbType(String dbType) {
        DataBaseType dataType = null;
        if(DictConstans.DB_TYPE_MYSQL.equals(dbType)){
        dataType = DataBaseType.MYSQL;
    }else if(DictConstans.DB_TYPE_ORACLE.equals(dbType)){
        dataType = DataBaseType.ORACLE;
    }else if(DictConstans.DB_TYPE_SQLSERVER.equals(dbType)){
        dataType = DataBaseType.SQLSERVER;
    }else if(DictConstans.DB_TYPE_DM.equals(dbType)){
        dataType = DataBaseType.DM;
    }
        return dataType;
    }

    public enum DataBaseType
    {
        MYSQL("com.mysql.jdbc.Driver", "jdbc:mysql://%s:%s/%s?useUnicode=true&characterEncoding=utf8" , "jdbc:mysql://%s:%s/") , 
        ORACLE("oracle.jdbc.driver.OracleDriver", "jdbc:oracle:thin:@//%s:%s/%s" , ""),
        DM("dm.jdbc.driver.DmDriver", "jdbc:dm://%s:%s/%s" , ""),
        SQLSERVER("com.microsoft.sqlserver.jdbc.SQLServerDriver" , "jdbc:sqlserver://%s:%s;instanceName=%s;DatabaseName=%s" , "");
        private String driverName;
        private String driverUrl;
        private String noDbNameUrl;

        DataBaseType(String driverName , String driverUrl , String noDbNameUrl)
        {
            this.driverName = driverName;
            this.driverUrl = driverUrl;
            this.noDbNameUrl = noDbNameUrl;
        }

        public String getDriverName()
        {
            return driverName;
        }

        public String getDriverUrl()
        {
            return driverUrl;
        }

        public String getNoDbNameUrl()
        {
            return noDbNameUrl;
        }
    }
}

需要注意的是：

public static int saveData(String tableName, Map data) {

tableName为数据库表名 Map 中key为数据库表字段名，value为对应的值。