DB Connection
发布日期:2022-02-09 20:39:06 浏览次数:6 分类:技术文章

本文共 9698 字,大约阅读时间需要 32 分钟。

/**

 * Copyright 2014 Liang Yuan
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *     http://www.apache.org/licenses/LICENSE-2.0
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package com;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
public class DBConnection {
   
    /** Hold the class name to be used in logging. */
    private static final String CLASS_NAME = DBConnection.class.getName();
   
    private  static  Log log  =  LogFactory.getLog(DBConnection.class);
   
    private static javax.naming.Context ctx = null;
   
    private static String jndi = "java:/comp/env/jdbc/subsidy";
   
    private static final String BATCH_SIZE = ApplicationConfig
            .getInstance().getConfigValue("batch_size");
   
   
    private static void createInitialCxt(){
        try{
            ctx = new InitialContext();
           
        } catch(NamingException ne){
           
        }
    }
   
    public static synchronized Connection getConnection(){
        Connection conn = null;
        if (ctx == null) {
            createInitialCxt();
        }
        try{
            DataSource ds = (DataSource)ctx.lookup(jndi);
            conn = ds.getConnection();
           
        } catch(NamingException ne){
            ne.printStackTrace();
        } catch(SQLException se){
            se.printStackTrace();;
        }
        return conn;
       
    }
   
    /**
     * return a connection.
     *
     * @return database connection.
     * @throws SQLException
     *             if the database connection cannot be fetched.
     */
    public Connection getConnection(String databaseDriver, String connectString, String databaseUser, String databasePwd) throws SQLException {
        log.debug("Enter method getConnection");
        Connection conn = null;
        try {
            Class.forName(databaseDriver);
            conn = DriverManager.getConnection(connectString, databaseUser, databasePwd);
        } catch (Throwable ex) {
            String errmsg = MessageFormatHelper.getMessage(LogMessage.CLASS_NOTFOUND_ERROR, new Object[] { CLASS_NAME,
                    "getConnection", databaseDriver });
            log.error(errmsg, ex);
            throw new InvalidConfigurationException(errmsg, ex);
        }
        log.debug(MessageFormatHelper.getMessage(LogMessage.METHOD_EXIT, new Object[] { CLASS_NAME, "getConnection",
                " <" + conn + ">" }));
        return conn;
    }
   
    /**
     * Executes a JDBC update using a prepared statement.
     *
     * @param sql
     *            the sql statement to be executed.
     * @param params
     *            The parameters for the SQL, must be in the order specified in
     *            the SQL.
     * @throws SQLException
     *             if database connection cannot be fetched or sql statement
     *             cannot be executed.
     */
    public void executeUpdate(String sql, Object[] params) throws SQLException {
        log.debug("Enter method executeUpdate, sql <" + sql + ">," + MessageFormatHelper.getArrayAsString("params", params));
        Connection conn = null;
        PreparedStatement stmt = null;
        conn = getConnection();
        try {
            stmt = conn.prepareStatement(sql);
            setParam(stmt, params);
            stmt.executeUpdate();
        } catch (SQLException e) {
            String errmsg = "SQLException happened when executing <sql>: " + sql + "," + MessageFormatHelper.getArrayAsString("params", params) + "please see log file for more details.";
            log.error(errmsg, e);
            throw e;
        } finally {
            // Close the prepared statement and the connection, even if an
            // exception is thrown.
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    String warnmsg = "Error happened during closing statement";
                    log.warn(warnmsg, e);
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    String warnmsg = "Error happened when closing the database connection";
                    log.warn(warnmsg, e);
                }
            }
        }
        log.debug("Exit method executeUpdate");
    }
   
    public void executeBatchUpdate(String sql, List<Object[]> paramList) throws SQLException {
        log.debug("Enter method executeBatchUpdate, sql <" + sql + ">");
        Connection conn = null;
        PreparedStatement stmt = null;
        conn = getConnection();
        boolean autoCommit = conn.getAutoCommit();
        conn.setAutoCommit(false);
        int errRowNum = 0;
        try {
            stmt = conn.prepareStatement(sql);
            for (int loop = 0, batchCount = 0; loop < paramList.size(); loop++, batchCount++) {
                Object[] params = paramList.get(loop);
                setParam(stmt, params);
                stmt.addBatch();
                errRowNum = loop;
                if (batchCount == Integer.parseInt(BATCH_SIZE)) {
                    stmt.executeBatch();
                    stmt.clearBatch();
                    batchCount = 0;
                    conn.commit();
                }
            }
            stmt.executeBatch();
            conn.commit();
        } catch (SQLException e) {
            String errmsg = "SQLException happened when batch executing <sql>: " + sql + ", error occurs around records[" + errRowNum + "], please check whether these records have error";
            log.error(errmsg, e);
            throw new FatalException(errmsg, e);
        } finally {
            // Close the prepared statement and the connection, even if an
            // exception is thrown.
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    String warnmsg = "Error happened during closing statement";
                    log.warn(warnmsg, e);
                }
            }
            if (conn != null) {
                try {
                    conn.setAutoCommit(autoCommit);
                    conn.close();
                } catch (SQLException e) {
                    String warnmsg = "Error happened when closing the database connection";
                    log.warn(warnmsg, e);
                }
            }
        }
        log.debug("Exit method executeBatchUpdate");
    }
    /**
     * Executes a JDBC query using a prepared statement.
     *
     * @param sql
     *            The SQL to be executed.
     * @param params
     *            The parameters for the SQL, must be in the order specified in
     *            the SQL.
     * @param returnTypes
     *            The type of the data to be returned, in the order in which the
     *            SQL returns them.
     * @return An array of objects of the type specified in returnTypes.
     * @throws SQLException
     *             if database connection cannot be fetched or sql statement
     *             cannot be executed.
     */
    public Object[][] executeQuery(String sql, Object[] params, int[] returnTypes) throws SQLException {
        log.debug("Enter method executeQuery, sql <" + sql + ">," + MessageFormatHelper.getArrayAsString("params", params) + ">, returnTypes <" + returnTypes  + ">");
        Object[][] returnValues = null;
        Connection conn = null;
        PreparedStatement stmt = null;
        conn = getConnection();
        try {
            stmt = conn.prepareStatement(sql);
            setParam(stmt, params);
            ResultSet rs = stmt.executeQuery();
            returnValues = getReturnValues(rs, returnTypes);
        } catch (SQLException e) {
            String errmsg = "SQLException happened when executing <sql>: " + sql + "," + MessageFormatHelper.getArrayAsString("params", params) + "please see log file for more details.";
            log.error(errmsg, e);
            throw e;
        } finally {
            // Close the prepared statement and the connection, even if an
            // exception is thrown.
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    String warnmsg = "Error happened during closing statement";
                    log.warn(warnmsg, e);
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    String warnmsg = "Error happened when closing the database connection";
                    log.warn(warnmsg, e);
                }
            }
        }
        log.debug(" Exit method executeQuery");
        return returnValues;
    }
   
    /**
     * Sets the supplied parameters in the prepared statement.
     *
     * @param stmt
     *            the sql statement
     * @param params
     *            the needed parameters.
     * @throws SQLException
     *             if database error happen.
     * 
     */
    private void setParam(PreparedStatement stmt, Object[] params) throws SQLException {
        for (int i = 0; i < params.length; i++) {
            if (params[i] == null) {
                stmt.setString(i + 1, null);
            } else if (params[i] instanceof String) {
                stmt.setString(i + 1, (String) params[i]);
            } else if (params[i] instanceof Double) {
                stmt.setDouble(i + 1, ((Double) params[i]).doubleValue());
            } else if (params[i] instanceof java.sql.Date) {
                stmt.setDate(i + 1, (Date) params[i]);
            } else if (params[i] instanceof Timestamp) {
                stmt.setTimestamp(i + 1, (Timestamp) params[i]);
            } else if (params[i] instanceof java.util.Date) {
                java.util.Date date = (java.util.Date) params[i];
                stmt.setDate(i + 1, new java.sql.Date(date.getTime()));
            } else if (params[i] instanceof Integer) {
                stmt.setInt(i + 1, ((Integer) params[i]).intValue());
            } else if (params[i] instanceof Long) {
                stmt.setLong(i + 1, ((Long) params[i]).longValue());
            } else if (params[i] instanceof InputStream) {
                stmt.setBinaryStream(i + 1, (InputStream) params[i]);
            } else {
                stmt.setString(i + 1, params[i].toString());
            }
        }
    }
    /**
     * Gets the return data fromt he result set, using the returnTypes.
     *
     * @param rs
     *            result set from database.
     * @param returnTypes
     *            type list.
     * @throws SQLException
     *             if the database errors happen.
     * @return object arrays.
     */
    private Object[][] getReturnValues(ResultSet rs, int[] returnTypes) throws SQLException {
        ArrayList returnValues = new ArrayList();
        while (rs.next()) {
            Object[] rowValues = new Object[returnTypes.length];
            for (int i = 0; i < returnTypes.length; i++) {
                if (returnTypes[i] == DataConstant.STRING) {
                    rowValues[i] = rs.getString(i + 1);
                } else if (returnTypes[i] == DataConstant.INT) {
                    rowValues[i] = new Integer(rs.getInt(i + 1));
                } else if (returnTypes[i] == DataConstant.DOUBLE) {
                    rowValues[i] = new Double(rs.getDouble(i + 1));
                } else if (returnTypes[i] == DataConstant.DATE) {
                    rowValues[i] = rs.getDate(i + 1);
                } else if (returnTypes[i] == DataConstant.TIMESTAMP) {
                    rowValues[i] = rs.getTimestamp(i + 1);
                } else if (returnTypes[i] == DataConstant.LONG) {
                    rowValues[i] = new Long(rs.getLong(i + 1));
                }
            }
            returnValues.add(rowValues);
        }
        Object[][] returnObject = (Object[][]) returnValues.toArray(new Object[0][0]);
        return returnObject;
    }
}

转载地址:https://blog.csdn.net/iteye_18599/article/details/82638144 如侵犯您的版权,请留言回复原文章的地址,我们会给您删除此文章,给您带来不便请您谅解!

上一篇:httpclient4.5调用接口
下一篇:DataConstant

发表评论

最新留言

表示我来过!
[***.240.166.169]2024年03月13日 01时54分58秒

关于作者

    喝酒易醉,品茶养心,人生如梦,品茶悟道,何以解忧?唯有杜康!
-- 愿君每日到此一游!

推荐文章

3t硬盘 xp_怎么让xp支持3T硬盘 2019-04-21
python的web抓取_带有请求的Python Web抓取-登录后 2019-04-21
kswapd0 挖矿_Linux kswapd0 进程CPU占用过高 2019-04-21
opengl绘制长方体线框_OpenGL 绘制应用纹理的矩形 2019-04-21
c-free显示运行程序错误怎么办_电脑蓝屏了怎么办?小白看这里! 2019-04-21
aop的实现原理_你必须要懂的 Spring:Aop 2019-04-21
4位先行进位电路 logisim_数字电路学习笔记(八):计算电路 2019-04-21
spyder怎么看已有的包_怎么样做一份好吃的羊肉手抓饭?看了包学会 2019-04-21
freebuds3怎么查激活日期_怎么查股票的月平均价格 中泰证券股票上市日期 2019-04-21
八类网线和七类网线的区别_七类网线的知识你知道多少? 2019-04-21
链接orientdb的图形数据库_血清标志物数据库推荐 2019-04-21
centos7建站php_有想法不会行动?想建站无从下手?看过来 2019-04-21
云联惠认证时间_2020.10.24号最新币圈动态资讯:piKYC延长时间,趣步,好玩吧,AOT慈善,蚂蚁短视频,猫爪,秀刻,BTD,GEC... 2019-04-21
spss主成分综合得分_因子分析spss教程 2019-04-21
如何判断笔记本蓝牙硬件坏了_如何判断一个浴室柜的好与坏?这里有完整的浴室柜选购攻略!... 2019-04-21
小程序源码 租房管理系统_冲击啵租房!好用的租房小程序! 2019-04-21
二分法查找是基于有序_重温二分查找Binary Search(面试刷题导向) 2019-04-21
iphone编辑过的录音怎么还原_讯飞智能耳机评测:职场人的最佳耳机外设,iPhone得力助手... 2019-04-21
uibot中级认证试题_【软考认证培训】现代卓越2020年5、11月软考认证网络班全国招生中,欲报从速!... 2019-04-21
滴滴行程单用的什么字体_官方解答“滴滴”等交通出行的抵扣政策 2019-04-21