JDBCUtil

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * 数据库连接工具类，抽取了常量到DbConstants中
 * 
  *
 */
public class JDBCUtil {
    /**
     * 类名称，用于打印Log日志
     */
    private static final String CLASS_NAME = "JDBCUtil";

    /**
     * 数据库类型
     */
    private String db_type;

    /**
     * 数据库实例名称
     */
    private String databaseName;

    /**
     * 数据库用户名
     */
    private String db_username;

    /**
     * 数据库密码
     */
    private String db_password;

    /**
     * ip地址
     */
    private String ip;

    /**
     * 端口
     */
    private int port;

    /**
     * 数据库连接URl
     */
    private String db_url;

    /**
     * 数据库驱动
     */
    private String driver;

    public JDBCUtil(String driver, String url, String db_username, String db_password) {
        this.driver = driver;
        this.db_url = url;
        this.db_username = db_username;
        this.db_password = db_password;
    }

    public JDBCUtil(String dababase_name, String db_type, String db_username, String db_password, String ip, int port) {
        this.db_type = db_type;
        this.databaseName = dababase_name;
        this.db_username = db_username;
        this.db_password = db_password;
        this.ip = ip;
        this.port = port;
        this.db_url = getDBURL();
    }

    /**
     * 获得数据库连接
     *
     * @return
     */
    public Connection getConnection() {
        // 声明Connection连接对象
        Connection conn = null;
        // 获取对应数据库的驱动
        if (StringUtil.isNull(driver)) {
            driver = getDBDriver();
        }
        try {
            // 使用Class.forName()方法自动创建这个驱动程序的实例且自动调用DriverManager来注册它
            Class.forName(driver);
            // 通过DriverManager的getConnection()方法获取数据库连接
            conn = DriverManager.getConnection(db_url, db_username, db_password);
        } catch (Exception ex) {
            LogUtil.exception(CLASS_NAME, "getConnection", ex);
            return null;
        }
        return conn;
    }

    /**
     * 获取JDBC的URL，例如：jdbc:mysql://localhost:3306/test1
     *
     * @param type
     *            数据库类型，如：mysql，oracle
     * @param ip
     *            数据库IP地址
     * @param dbId
     *            数据库实例名称
     * @param port
     *            数据库端口
     * @return url
     * @throws Exception
     */
    public String getDBURL() {
        String methodName = "getDBURL";
        if (StringUtil.isNull(db_type)) {
            LogUtil.error(CLASS_NAME, methodName, "type=null");
            return null;
        }
        if (StringUtil.isNull(ip)) {
            LogUtil.error(CLASS_NAME, methodName, "ip=null");
            return null;
        }
        if (StringUtil.isNull(databaseName)) {
            LogUtil.error(CLASS_NAME, methodName, "dbId=null");
            return null;
        }
        if (port == 0) {
            LogUtil.error(CLASS_NAME, methodName, "port=0");
            return null;
        }
        StringBuffer url = new StringBuffer();
        if (DbConstants.ORACLE_TYPE.equals(db_type)) {
            url.append(DbConstants.ORACLE_URL_PREFIX).append(ip).append(":").append(port).append(":")
                    .append(databaseName);
        } else if (DbConstants.MYSQL_TYPE.equals(db_type)) {
            url.append(DbConstants.MYSQL_URL_PREFIX).append(ip).append(":").append(port).append("/")
                    .append(databaseName).append("?characterEncoding=utf8");
        } else if (DbConstants.DB2_TYPE.equals(db_type)) {
            url.append(DbConstants.DB2_URL_PREFIX).append(ip).append(":").append(port).append("/").append(databaseName);
        } else if (DbConstants.SQLSERVER_TYPE.equals(db_type)) {
            url.append(DbConstants.SQLSERVER_URL_PREFIX).append(ip).append(":").append(port).append(";DatabaseName=")
                    .append(databaseName);
        }
        return url.toString();
    }

    /**
     * 获取数据库驱动
     *
     * @param type
     *            数据库类型，如：mysql，oracle
     * @return
     * @throws Exception
     */
    public String getDBDriver() {
        if (DbConstants.ORACLE_TYPE.equals(db_type)) {
            return DbConstants.ORACLE_DRIVER;
        } else if (DbConstants.MYSQL_TYPE.equals(db_type)) {
            return DbConstants.MYSQL_DRIVER;
        } else if (DbConstants.DB2_TYPE.equals(db_type)) {
            return DbConstants.DB2_DRIVER;
        } else if (DbConstants.SQLSERVER_TYPE.equals(db_type)) {
            return DbConstants.SQLSERVER_DRIVER;
        }
        return null;
    }

    /**
     * 关闭数据库连接
     *
     * @param conn
     */
    public static void close(Connection conn) {
        if (conn != null) {
            try {
                if (!conn.isClosed()) {
                    conn.close();
                    conn = null;
                }
            } catch (SQLException e) {
                LogUtil.exception(CLASS_NAME, "close", e);
            }
        }
    }

    public static void main(String[] args) {
        // JDBCUtil jdbc = new JDBCUtil("idswitch", "Mysql", "root", "root",
        // "127.0.0.1", 3306);
        //
        // Connection conn = jdbc.getConnection();
        // List<String> dataList = new ArrayList<String>();
        // try {
        // Statement statement = conn.createStatement();
        // ResultSet resultSet =
        // statement.executeQuery("SELECT id,subscribe_id,catalog_id FROM data_clean_result");
        // int columns = resultSet.getMetaData().getColumnCount();
        // while (resultSet.next()) {
        // for (int i = 1; i <= columns; i++) {
        // dataList.add(resultSet.getString(i));
        // }
        // }
        // } catch (SQLException e) {
        // e.printStackTrace();
        // }
        try {
            insertData();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.print("ok");

    }

    public static void insertData() throws SQLException {
        JDBCUtil jdbc = new JDBCUtil("esbTest01", "Oracle", "idswitch", "idswitch", "10.18.224.73", 1521);
        Connection conn = jdbc.getConnection();
        conn.setAutoCommit(false);
        PreparedStatement pstat = conn
                .prepareStatement("insert into MYSQLZYML1402478075(ID,TEST_BIGINT,TEST_INT,TEST_VARCHAR,TEST_CHAR) values (?,?,?,?,?)");
        for (int i = 0; i < 2000; i++) {
            pstat.setString(1, i + 3 + "");
            pstat.setInt(2, i + 3);
            pstat.setInt(3, i + 3);
            pstat.setString(4, i + 3 + "" + i + 3);
            pstat.setString(5, i + 3 + "" + i + 3);
            pstat.addBatch();
        }
        pstat.executeBatch();
        conn.commit();
    }
}

public interface DbConstants {

    // The minimum number of connections we will keep open, regardless of
    // whether anyone needs them or not. Default is 2
    String minimum_connection_count = "2";

    // The maximum number of connections to the database. Default is 15.
    String maximum_connection_count = "15";

    // mysql数据库类型名称
    String MYSQL_TYPE = "Mysql";

    // mysql数据库URL前缀，完整URL:jdbc:mysql://127.0.0.1:3306/idep
    String MYSQL_URL_PREFIX = "jdbc:mysql://";

    // oracle数据库类型名称
    String ORACLE_TYPE = "Oracle";

    // oracle数据库URL前缀,完整URL：jdbc:oracle:thin:@127.0.0.1:1521:idep
    String ORACLE_URL_PREFIX = "jdbc:oracle:thin:@";

    String DB2_TYPE = "DB2";

    // DB2数据库URL前缀，完整URL：jdbc:db2://127.0.0.1:50000/idep
    String DB2_URL_PREFIX = "jdbc:db2://";

    String SQLSERVER_TYPE = "SqlServer";

    // sqlserver数据库URL前缀，完整URL：jdbc:sqlserver://127.0.0.1:1433;DatabaseName=idep
    String SQLSERVER_URL_PREFIX = "jdbc:sqlserver://";

    // oracle数据库连接驱动
    String ORACLE_DRIVER = "oracle.jdbc.driver.OracleDriver";

    // mysql数据库驱动
    String MYSQL_DRIVER = "com.mysql.jdbc.Driver";

    // DB2数据库驱动
    String DB2_DRIVER = "com.ibm.db2.jcc.DB2Driver";

    // sqlserver数据库驱动
    String SQLSERVER_DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";

}

 

public class LogUtil {
    private static Logger log = LoggerFactory.getLogger(LogUtil.class);

    /**
     * 入参日志打印，debug级别
     *
     * @param className
     *            类名称
     * @param methodName
     *            方法名称
     * @param logMap
     *            入参列表,key：参数名称，value：参数值
     */
    public static void debug(String className, String methodName, Map<String, String> logMap) {
        log.debug("----------log start-----------");

        // 打印类名称
        log.debug("Class Name:" + className);
        // 打印方法名称
        log.debug("Method Name:" + methodName);
        // 打印参数列表
        Set<String> keySet = logMap.keySet();
        Iterator<String> iter = keySet.iterator();

        // 参数名称作为key
        String paramKey = null;
        String paramValue = null;
        while (iter.hasNext()) {
            paramKey = iter.next();
            paramValue = logMap.get(paramKey);
            StringBuffer logBuff = new StringBuffer();
            logBuff.append("Parameter Name:").append(paramKey).append(",");
            logBuff.append("Parameter Value:").append(paramValue).append(",");
            log.debug(logBuff.toString());
        }
        log.debug("----------log end-----------");
    }

    /**
     * Java代码块日志打印，debug级别
     *
     * @param className
     *            类名称
     * @param methodName
     *            方法名称
     * @param paramMap
     *            入参列表,key：参数名称，value：参数值
     */
    public static void debug(String className, String methodName, String... desc) {
        log.debug("----------log start-----------");
        // 打印类名称
        log.debug("Class Name:" + className);
        // 打印方法名称
        log.debug("Method Name:" + methodName);
        // 打印日志信息
        for (String info : desc) {
            log.debug("Log Info:" + info);
        }
        log.debug("----------log end-----------");
    }

    /**
     * 异常错误日志打印
     *
     * @param className
     *            类名称
     * @param methodName
     *            方法名称
     * @param e
     *            异常堆栈信息
     */
    public static void exception(String className, String methodName, Exception e) {
        log.error("----------log start-----------");
        // 打印类名称
        log.error("Class Name:" + className);
        // 打印方法名称
        log.error("Method Name:" + methodName);
        // 打印异常信息
        log.error("Exception Info", e);
        log.error("----------log end-----------");
    }

    /**
     * 除异常之外的其他错误日志打印
     *
     * @param className
     *            类名称
     * @param methodName
     *            方法名称
     * @param logDescription
     *            错误描述
     */
    public static void error(String className, String methodName, String logDescription) {
        log.error("----------log start-----------");
        // 打印类名称
        log.error("Class Name:" + className);
        // 打印方法名称
        log.error("Method Name:" + methodName);
        // 打印错误描述
        log.error("Error Info:" + logDescription);
        log.error("----------log end-----------");
    }

    /**
     * 打印警告日志
     *
     * @param className
     *            类名称
     * @param methodName
     *            方法名称
     * @param logDescription
     *            错误描述
     */
    public static void warn(String className, String methodName, String logDescription) {
        log.warn("----------log start-----------");
        // 打印类名称
        log.warn("Class Name:" + className);
        // 打印方法名称
        log.warn("Method Name:" + methodName);
        // 打印错误描述
        log.warn("Warn Info:" + logDescription);
        log.warn("----------log end-----------");
    }

    /**
     * 打印info日志
     *
     * @param className
     * @param methodName
     * @param logDescription
     */
    public static void info(String className, String methodName, String logDescription) {
        log.info("----------log start-----------");
        // 打印类名称
        log.info("Class Name:" + className);
        // 打印方法名称
        log.info("Method Name:" + methodName);
        // 打印错误描述
        log.info("Info:" + logDescription);
        log.info("----------log end-----------");
    }
}

 

public class StringUtil {
    /**
     * 字符串为空
     *
     * @param text
     * @return
     */
    public static boolean isNull(String text) {
        if (text == null || "".equals(text) || "null".equals(text)) {
            return true;
        } else {
            return false;
        }
    }

    /**
     * 字符串不为空
     *
     * @param text
     * @return
     */
    public static boolean isNotNull(String text) {
        if (text != null && !"".equals(text) && !"null".equals(text) && text.length() > 0) {
            return true;
        } else {
            return false;
        }
    }

    public static String getFormatAddress(String ipAndPort) {
        return ipAndPort.replaceAll("\\.|:", "_");
    }

    public static String getCenterQueueName(String ipAndPort) {
        return "center_" + ipAndPort.replaceAll("\\.|:", "_");
    }

    public static String getCenterLogQueueName(String ipAndPort) {
        return "center_log_" + ipAndPort.replaceAll("\\.|:", "_");
    }

    public static String getSwtichQueueNamePrefix(String ipAndPort) {
        return "switch_" + ipAndPort.replaceAll("\\.|:", "_") + "_";
    }

    public static String getVsQueueName(String ipAndPort, String id) {
        return "vs_" + ipAndPort.replaceAll("\\.|:", "_") + "_" + id;
    }
}