/* * @Copyright (c) 2018 缪聪([email protected]) * * 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.mcg.plugin.dbconn; import java.sql.Connection; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import java.util.Map; import javax.sql.DataSource; import org.apache.commons.dbutils.BasicRowProcessor; import org.apache.commons.dbutils.BeanProcessor; import org.apache.commons.dbutils.DbUtils; import org.apache.commons.dbutils.GenerousBeanProcessor; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.RowProcessor; import org.apache.commons.dbutils.handlers.BeanListHandler; import org.apache.commons.dbutils.handlers.MapListHandler; import org.apache.commons.dbutils.handlers.ScalarHandler; import org.apache.commons.lang.builder.ToStringBuilder; import org.slf4j.Logger; import org.slf4j.LoggerFactory; /** * * 带事务支持的数据库底层操作类 */ @SuppressWarnings({ "rawtypes", "unchecked" }) public class DbConnect { private static Logger logger = LoggerFactory.getLogger(DbConnect.class); private DataSource dataSource; private ThreadLocal<Connection> threadLocal = new ThreadLocal<Connection>(); public DbConnect(DataSource dataSource) { this.dataSource = dataSource; } /** * 创建数据库连接 * * @return * @see [类、类#方法、类#成员] */ public Connection getConnection() { Connection connection = threadLocal.get(); try { if (connection == null) { connection = dataSource.getConnection(); threadLocal.set(connection); } } catch (SQLException e) { logger.error("获取数据库连接失败,异常信息:", e.getMessage()); throw new RuntimeException("获取数据库连接失败....."); } return connection; } /** * 带可变参数查询,返回执行结果 * * @param sql 查询sql * @param para 可变参数 * @return */ public List<Map<String, Object>> querySql(String sql, Object... para) throws SQLException { logger.debug("查询Sql: {}, 查询参数: {}", sql, ToStringBuilder.reflectionToString(para)); QueryRunner runner = new QueryRunner(); Connection conn = null; List<Map<String, Object>> result = new ArrayList<Map<String, Object>>(); try { conn = getConnection(); result = runner.query(conn, sql, new MapListHandler(), para); } catch (SQLException e) { logger.error("查询出错,异常信息: {}", e.getMessage()); throw e; } finally { if (conn != null && conn.getAutoCommit() == true) { freeConnection(); } } return result; } /** * * @Title: querySql * @Description: TODO(带可变参数查询,返回执行结果) * @param: @param clazz 转换的对象实例 * @param: @param sql 查询sql语句 * @param: @param para 查询参数 * @param: @return * @param: @throws SQLException * @return: List * @throws */ public <T> List querySql(T clazz, String sql, Object... para) throws SQLException { logger.debug("查询Sql: {}, 查询参数: {}", sql, ToStringBuilder.reflectionToString(para)); QueryRunner runner = new QueryRunner(); Connection conn = null; List<T> result = new ArrayList<T>(); try { conn = getConnection(); // 下划线分隔的表字段名转换为实体bean驼峰命名属性 BeanProcessor bean = new GenerousBeanProcessor(); RowProcessor processor = new BasicRowProcessor(bean); result = (List<T>) runner.query(conn, sql, new BeanListHandler((Class) clazz, processor), para); } catch (SQLException e) { logger.error("查询出错,异常信息: {}------", e.getMessage()); throw e; } finally { if (conn != null && conn.getAutoCommit() == true) { freeConnection(); } } return result; } /** * * @Title: queryForLong * @Description: TODO(带可变参数查询,返回long类型数据) * @param: @param countSql 查询记录总条数 * @param: @param para 参数 * @param: @return * @param: @throws SQLException * @return: Long * @throws */ public Long queryForLong(String countSql, Object... para) throws SQLException { logger.debug("queryForLong: {}, para: {}", countSql, ToStringBuilder.reflectionToString(para)); QueryRunner runner = new QueryRunner(); Long number = null; Connection conn = null; try { conn = getConnection(); number = runner.query(conn, countSql, new ScalarHandler<Long>(), para); } catch (SQLException e) { logger.error("------queryForLong error: {}------", e.getMessage()); throw e; } finally { if (conn != null && conn.getAutoCommit() == true) { freeConnection(); } } return number; } /** * * @Title: executeUpdate * @Description: TODO(带可变参数, 执行sql,返回执行影响的记录条数) * @param: @param sql 执行的sql语句 * @param: @param para 参数 * @param: @return * @param: @throws SQLException * @return: int * @throws */ public int executeUpdate(String sql, Object... para) throws SQLException { logger.debug("executeUpdate: {}, para: {}", sql, ToStringBuilder.reflectionToString(para)); QueryRunner runner = new QueryRunner(); Connection conn = null; int count = 0; try { conn = getConnection(); count = runner.update(conn, sql, para); } catch (SQLException e) { logger.error("------executeUpdate error: {}------", e.getMessage()); throw e; } finally { if (conn != null && conn.getAutoCommit() == true) { freeConnection(); } } return count; } /** * * @Title: insertSql * @Description: TODO(带可变参数, 执行sql插入,返回新增记录的自增主键。注意: 若插入的表无自增主键则返回 0,异常的话则返回 null) * @param: @param sql 执行的sql语句 * @param: @param para 参数 * @param: @return * @param: @throws SQLException * @return: Long * @throws */ public Long insertSql(String sql, Object... para) throws SQLException { logger.debug("InsertSql: {}, para: {}", sql, ToStringBuilder.reflectionToString(para)); QueryRunner runner = new QueryRunner(); Connection conn = null; Long id = null; try { conn = getConnection(); id = (Long) runner.insert(conn, sql, new ScalarHandler<Object>(), para); } catch (SQLException e) { logger.error("------insertSql error: {}------", e.getMessage()); throw e; } finally { if (conn != null && conn.getAutoCommit() == true) { freeConnection(); } } return id; } /** * * @Title: executeBatch * @Description: TODO(批量更新) * @param: @param sql 执行的sql语句 * @param: @param params 二维数组参数 * @param: @throws SQLException * @return: void * @throws */ public void executeBatch(String sql, Object[][] params) throws SQLException { logger.debug("executeBatch: {}, params:{}", sql, ToStringBuilder.reflectionToString(params)); QueryRunner runner = new QueryRunner(); Connection conn = null; try { conn = getConnection(); runner.batch(conn, sql, params); } catch (SQLException e) { logger.error("------executeBatch Error:{}------", e.getMessage()); throw e; } finally { if (conn != null && conn.getAutoCommit() == true) { freeConnection(); } } } /** * * @Title: executeBatch * @Description: TODO(批量更新) * @param: @param sql 执行的sql语句 * @param: @param params 参数 * @param: @throws SQLException * @return: void * @throws */ public void executeBatch(String sql, List<Object[]> params) throws SQLException { Object[][] paramArr = params.toArray(new Object[0][]); executeBatch(sql, paramArr); } /** * * @Title: freeConnection * @Description: TODO(释放数据库连接) * @param: * @return: void * @throws */ public void freeConnection() { logger.debug("------释放数据库连接------"); Connection conn = threadLocal.get(); if (conn != null) { DbUtils.closeQuietly(conn); threadLocal.remove(); // 解除当前线程上绑定conn } } // ************** 事务操作 ************** /** * * @Title: startTransaction * @Description: TODO(开启事务) * @param: * @return: void * @throws */ public void startTransaction() { logger.debug("------开启事务-------"); try { Connection conn = threadLocal.get(); if (conn == null) { conn = getConnection(); threadLocal.set(conn); } conn.setAutoCommit(false); } catch (SQLException e) { logger.error("开启事务失败,异常信息:{}", e.getMessage()); } } /** * * @Title: commit * @Description: TODO(提交事务) * @param: * @return: void * @throws */ public void commit() { logger.debug("------提交事务-------"); try { Connection conn = threadLocal.get(); if (conn != null) { conn.commit(); } } catch (SQLException e) { logger.error("提交事务失败,异常信息:{}", e.getMessage()); } } /** * * @Title: rollback * @Description: TODO(回滚事务) * @param: * @return: void */ public void rollback() { logger.debug("------ 系统异常,回滚事务------"); try { Connection conn = threadLocal.get(); if (conn != null) { conn.rollback(); } } catch (SQLException e) { logger.error("回滚事务失败,异常信息:{}", e.getMessage()); } } public DataSource getDataSource() { return dataSource; } public void setDataSource(DataSource dataSource) { this.dataSource = dataSource; } }