/**
* 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; }}
DB Connection
发布日期:2022-02-09 20:39:06
浏览次数:6
分类:技术文章
本文共 9698 字,大约阅读时间需要 32 分钟。
转载地址:https://blog.csdn.net/iteye_18599/article/details/82638144 如侵犯您的版权,请留言回复原文章的地址,我们会给您删除此文章,给您带来不便请您谅解!
发表评论
最新留言
表示我来过!
[***.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
spss主成分综合得分_因子分析spss教程
2019-04-21
小程序源码 租房管理系统_冲击啵租房!好用的租房小程序!
2019-04-21
二分法查找是基于有序_重温二分查找Binary Search(面试刷题导向)
2019-04-21
滴滴行程单用的什么字体_官方解答“滴滴”等交通出行的抵扣政策
2019-04-21