JdbcTemplate简易封装

在本篇博文中，我们将对SpringJDBC提供的JdbcTemplate进行简易封装，使其更加的易用，更加贴近上篇博文中对于SQL管理的设计。

package?com.kiiwow.framework.database.jdbc;

import?java.sql.ResultSet;
import?java.sql.ResultSetMetaData;
import?java.sql.SQLException;
import?java.util.Date;
import?java.util.HashMap;
import?java.util.Map;

import?org.springframework.jdbc.core.simple.ParameterizedRowMapper;

/**
?*?
?*?RowMapper
?*?@author?leon.gan
?*?@param?<T>
?*/
public?abstract?class?RowMapper<T>?implements?ParameterizedRowMapper<T>?{
????
????private?Map<String,?Integer>?columnIndexes;

????/**
?????*?每次实际取值之前先判断列名是否在结果集中存在
?????*/
????protected?int?findColumn(ResultSet?resultSet,?String?columnName)?throws?SQLException?{
????????if?(columnIndexes?==?null)?{
????????????buildIndexes(resultSet);
????????}
????????Integer?index?=?columnIndexes.get(columnName);
????????if?(index?==?null)?return?-1;
????????return?index;
????}

????/**
?????*?将结果集中的列名和一个迭代数字放入map中映射
?????*/
????private?void?buildIndexes(ResultSet?resultSet)?throws?SQLException?{
????????columnIndexes?=?new?HashMap<String,?Integer>();
????????ResultSetMetaData?meta?=?resultSet.getMetaData();
????????int?count?=?meta.getColumnCount();
????????for?(int?i?=?1;?i?<?count?+?1;?i++)?{
????????????String?column?=?meta.getColumnName(i);
????????????columnIndexes.put(column.toLowerCase(),?i);
????????}
????}

????protected?String?getString(ResultSet?resultSet,?String?column)?throws?SQLException?{
????????return?getString(resultSet,?column,?null);
????}

????protected?String?getString(ResultSet?resultSet,?String?column,?String?defaultValue)?throws?SQLException?{
????????int?columnIndex?=?findColumn(resultSet,?column);
????????if?(columnIndex?>?0)?return?resultSet.getString(columnIndex);
????????return?defaultValue;
????}

????protected?Date?getDate(ResultSet?resultSet,?String?column)?throws?SQLException?{
????????return?getDate(resultSet,?null);
????}

????protected?Date?getDate(ResultSet?resultSet,?Date?defaultValue)?throws?SQLException?{
????????int?columnIndex?=?findColumn(resultSet,?column);
????????if?(columnIndex?>?0)?return?resultSet.getTimestamp(columnIndex);
????????return?defaultValue;
????}

????protected?int?getInt(ResultSet?resultSet,?String?column)?throws?SQLException?{
????????return?getInt(resultSet,?0);
????}

????protected?int?getInt(ResultSet?resultSet,?int?defaultValue)?throws?SQLException?{
????????int?columnIndex?=?findColumn(resultSet,?column);
????????if?(columnIndex?>?0)?return?resultSet.getInt(columnIndex);
????????return?defaultValue;
????}

????protected?double?getDouble(ResultSet?resultSet,?String?column)?throws?SQLException?{
????????return?getDouble(resultSet,?0);
????}

????protected?double?getDouble(ResultSet?resultSet,?double?defaultValue)?throws?SQLException?{
????????int?columnIndex?=?findColumn(resultSet,?column);
????????if?(columnIndex?>?0)?return?resultSet.getDouble(columnIndex);
????????return?defaultValue;
????}

????public?abstract?T?mapRow(ResultSet?resultSet,?int?rowNum)?throws?SQLException;
}

package?com.kiiwow.framework.database.jdbc;

import?java.sql.ResultSet;
import?java.sql.SQLException;
import?java.util.List;

import?org.slf4j.Logger;
import?org.slf4j.LoggerFactory;
import?org.springframework.jdbc.core.JdbcTemplate;

import?com.kiiwow.framework.log.LogNameConstants;
import?com.kiiwow.framework.util.StopWatch;

/**
?*?
?*?JDBCAccess
?*
?*?JDBC执行工具类，仅对原生方法添加了日志记录，并调整了形参列表的顺序，同时使用自定义的RowMapper来替换原生RowMapper
?*?
?*?@author?leon.gan
?*/
public?class?JDBCAccess?{

????private?final?Logger?logger?=?LoggerFactory.getLogger(LogNameConstants.SQL_LOGGER);
????
????private?JdbcTemplate?jdbcTemplate;

????public?<T>?List<T>?find(String?sql,?RowMapper<T>?rowMapper,?Object...?params)?{
????????StopWatch?watch?=?new?StopWatch();
????????try?{
????????????return?jdbcTemplate.query(sql,?params,?rowMapper);
????????}?finally?{
????????????logger.debug("find,?sql={},?params={},?elapsedTime={}",?new?Object[]{sql,?watch.elapsedTime()});
????????}
????}

????public?<T>?T?findUniqueResult(String?sql,?Object...?params)?{
????????StopWatch?watch?=?new?StopWatch();
????????try?{
????????????return?jdbcTemplate.queryForObject(sql,?rowMapper);
????????}?finally?{
????????????logger.debug("findUniqueResult,?watch.elapsedTime()});
????????}
????}

????public?int?findInteger(String?sql,?Object...?params)?{
????????StopWatch?watch?=?new?StopWatch();
????????try?{
????????????return?jdbcTemplate.queryForInt(sql,?params);
????????}?finally?{
????????????logger.debug("findInteger,?watch.elapsedTime()});
????????}
????}

????public?String?findString(String?sql,?new?RowMapper<String>()?{
????????????????@Override
????????????????public?String?mapRow(ResultSet?resultSet,?int?rowNum)?throws?SQLException?{
????????????????????return?resultSet.getString(1);
????????????????}
????????????});
????????}?finally?{
????????????logger.debug("findString,?watch.elapsedTime()});
????????}
????}

????public?int?insert(String?sql,?Object...?params)?{
????????StopWatch?watch?=?new?StopWatch();
????????try?{
????????????return?jdbcTemplate.update(sql,?params);
????????}?finally?{
????????????logger.debug("insert,?watch.elapsedTime()});
????????}
????}
????
????public?int?update(String?sql,?params);
????????}?finally?{
????????????logger.debug("update,?watch.elapsedTime()});
????????}
????}
????
????public?int?delete(String?sql,?params);
????????}?finally?{
????????????logger.debug("delete,?watch.elapsedTime()});
????????}
????}

????public?int[]?batchExecute(String?sql,?List<Object[]>?params)?{
????????StopWatch?watch?=?new?StopWatch();
????????try?{
????????????return?jdbcTemplate.batchUpdate(sql,?params);
????????}?finally?{
????????????logger.debug("batchExecute,?watch.elapsedTime()});
????????}
????}

????public?JdbcTemplate?getJdbcTemplate()?{
????????return?jdbcTemplate;
????}

????public?void?setJdbcTemplate(JdbcTemplate?jdbcTemplate)?{
????????this.jdbcTemplate?=?jdbcTemplate;
????}
???
}

package?com.kiiwow.framework.database.jdbc;

import?java.util.List;

import?com.kiiwow.framework.platform.sqlmapping.SqlMappingAnalyzer;

/**
?*?对JDBCAccess的封装,对每一个操作分别提供两种形式：
?*?1)?自定义SQL模式,比如在条件查询时,在SQL映射文件中是无法提供动态查询SQL的,这个时候就采用自行编写
?*?2)?指定映射SQL模式,通过指定了SQL别名从SQL映射文件中找到指定的SQL
?*
?*?@author?leon.gan
?*
?*/
public?final?class?JDBCAccessContext?{

????private?JDBCAccess?jdbcAccess;
????
????public?<T>?List<T>?findWithOutSqlMapping(String?sql,?Object...?params)?{
????????return?jdbcAccess.find(sql,?rowMapper,?params);
????}
????
????public?<T>?List<T>?find(String?alias,?Object...?params)?{
????????String?sql?=?SqlMappingAnalyzer.getSpecificSql(alias);
????????return?jdbcAccess.find(sql,?params);
????}
????
????public?<T>?T?findUniqueResultWithOutSqlMapping(String?sql,?Object...?params)?{
????????return?jdbcAccess.findUniqueResult(sql,?params);
????}

????public?<T>?T?findUniqueResult(String?alias,?Object...?params)?{
????????String?sql?=?SqlMappingAnalyzer.getSpecificSql(alias);
????????return?jdbcAccess.findUniqueResult(sql,?params);
????}

????public?int?findIntegerWithOutSqlMapping(String?sql,?Object...?params)?{
????????return?jdbcAccess.findInteger(sql,?params);
????}

????public?int?findInteger(String?alias,?Object...?params)?{
????????String?sql?=?SqlMappingAnalyzer.getSpecificSql(alias);
????????return?jdbcAccess.findInteger(sql,?params);
????}

????public?String?findStringWithOutSqlMapping(String?sql,?Object...?params)?{
????????return?jdbcAccess.findString(sql,?params);
????}

????public?String?findString(String?alias,?Object...?params)?{
????????String?sql?=?SqlMappingAnalyzer.getSpecificSql(alias);
????????return?jdbcAccess.findString(sql,?params);
????}
????
????public?int?insertWithOutSqlMapping(String?sql,?Object...?params)?{
????????return?jdbcAccess.insert(sql,?params);
????}

????public?int?insert(String?alias,?Object...?params)?{
????????String?sql?=?SqlMappingAnalyzer.getSpecificSql(alias);
????????return?jdbcAccess.insert(sql,?params);
????}

????public?int?updateWithOutSqlMapping(String?sql,?Object...?params)?{
????????return?jdbcAccess.update(sql,?params);
????}

????public?int?update(String?alias,?Object...?params)?{
????????String?sql?=?SqlMappingAnalyzer.getSpecificSql(alias);
????????return?jdbcAccess.update(sql,?params);
????}

????public?int?deleteWithOutSqlMapping(String?sql,?Object...?params)?{
????????return?jdbcAccess.delete(sql,?params);
????}
????
????public?int?delete(String?alias,?Object...?params)?{
????????String?sql?=?SqlMappingAnalyzer.getSpecificSql(alias);
????????return?jdbcAccess.delete(sql,?params);
????}
????
????public?int[]?batchExecuteWithOutSqlMapping(String?sql,?List<Object[]>?params)?{
????????return?jdbcAccess.batchExecute(sql,?params);
????}
????
????public?int[]?batchExecute(String?alias,?List<Object[]>?params)?{
????????String?sql?=?SqlMappingAnalyzer.getSpecificSql(alias);
????????return?jdbcAccess.batchExecute(sql,?params);
????}

????public?void?setJdbcAccess(JDBCAccess?jdbcAccess)?{
????????this.jdbcAccess?=?jdbcAccess;
????}

????public?JDBCAccess?getJdbcAccess()?{
????????return?jdbcAccess;
????}
????
}