基于spring jdbc的上层封装，底层jdbc操作基于JdbcTemplate

package com.mini.jdbc; import java.sql.SQLException; import java.util.ArrayList; import java.util.Collection; import java.util.List; import java.util.Map; import org.springframework.dao.DataAccessException; import org.springframework.jdbc.core.BatchPreparedStatementSetter; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.ParameterizedPreparedStatementSetter; import org.springframework.jdbc.core.RowMapper; import com.mini.jdbc.dynamic.DynamicDialect; import com.mini.jdbc.ehcache.EhCache; import com.mini.jdbc.translate.TranslateFactory; import com.mini.jdbc.utils.DbKit; import com.mini.jdbc.utils.RecordKit; import com.mini.jdbc.utils.StrKit; /** * Db. Powerful database query and update tool box. */ @SuppressWarnings("rawtypes") public class DbHelper { final Object[] NULL_PARA_ARRAY = new Object[0]; private JdbcTemplate jdbcTemplate; private DynamicDialect dialect; private EhCache cache; private boolean exchange = false;//是否转义数据源方言 /** * 设置是否转义数据源方言 * @param exchange */ public void setExchange(boolean exchange) { this.exchange = exchange; } /** * 数据库帮助类 */ public DbHelper(){ this.cache = new EhCache(); } /** * 数据库帮助类 */ public DbHelper(EhCache cache){ this.cache = cache; } /** * 数据库帮助类 */ public DbHelper(JdbcTemplate jdbcTemplate,DynamicDialect dialect){ this.jdbcTemplate = jdbcTemplate; this.dialect = dialect; } /** * 获取jdbcTemplate * @return */ public JdbcTemplate getJdbcTemplate() { return jdbcTemplate; } /** * 设置jdbcTemplate * @param jdbcTemplate */ public void setJdbcTemplate(JdbcTemplate jdbcTemplate) { this.jdbcTemplate = jdbcTemplate; } /** * 获取转义 * @return */ public DynamicDialect getDialect() { return dialect; } /** * 设置转义 * @param dialect */ public void setDialect(DynamicDialect dialect) { this.dialect = dialect; } /** * 查询 * @param sql * @param paras * @param rowMapper * @return */ public <T> List<T> query(String sql, Object[] paras,RowMapper<T> rowMapper) { if(exchange) TranslateFactory.translate(sql, dialect.getDialect()); return jdbcTemplate.query(sql, paras, rowMapper); } /** * 查询 * @param sql * @param rowMapper * @return */ public <T> List<T> query(String sql, RowMapper<T> rowMapper) { return query(sql, NULL_PARA_ARRAY,rowMapper); } /** * 查询list * @param sql * @param elementType * @return */ public <T> List<T> queryForList(String sql,Class<T> elementType) { if(exchange) TranslateFactory.translate(sql, dialect.getDialect()); return jdbcTemplate.queryForList(sql,elementType); } /** * 查询list * @param sql * @return */ public List<Map<String, Object>> queryForList(String sql) { if(exchange) TranslateFactory.translate(sql, dialect.getDialect()); return jdbcTemplate.queryForList(sql); } /** * 查询对象 * @param sql * @param requiredType * @param rowMapper * @return */ public <T> T queryForObject(String sql,Class<T> requiredType,RowMapper<T> rowMapper){ if(exchange) TranslateFactory.translate(sql, dialect.getDialect()); return jdbcTemplate.queryForObject(sql, rowMapper); } /** * 查询对象 * @param sql * @param requiredType * @param args * @param rowMapper * @return */ public <T> T queryForObject(String sql,Class<T> requiredType,Object[] args,RowMapper<T> rowMapper){ if(exchange) TranslateFactory.translate(sql, dialect.getDialect()); return jdbcTemplate.queryForObject(sql, args, rowMapper); } /** * 查询对象 * @param sql * @param requiredType * @return */ public <T> T queryForObject(String sql,Class<T> requiredType){ if(exchange) TranslateFactory.translate(sql, dialect.getDialect()); return jdbcTemplate.queryForObject(sql, requiredType); } /** * 查询对象放入缓存 * @param cacheName * @param key * @param sql * @param requiredType * @param args * @return */ public <T> T queryForObjectByCache(String cacheName, Object key,String sql,Class<T> requiredType,Object[] args){ T result = cache.get(cacheName, key); if (result == null) { result = queryForObject(sql, requiredType,args); cache.put(cacheName, key, result); } return result; } /** * 查询对象放入缓存 * @param sql * @param requiredType * @param args * @return */ public <T> T queryForObject(String sql,Class<T> requiredType,Object[] args){ if(exchange) TranslateFactory.translate(sql, dialect.getDialect()); return jdbcTemplate.queryForObject(sql, requiredType,args); } /** * 查询并返回Map * @param sql * @return */ public Map<String,Object> queryForMap(String sql){ if(exchange) TranslateFactory.translate(sql, dialect.getDialect()); return jdbcTemplate.queryForMap(sql); } /** * 查询并返回Map * @param sql * @param args * @return */ public Map<String,Object> queryForMap(String sql, Object[] args){ if(exchange) TranslateFactory.translate(sql, dialect.getDialect()); return jdbcTemplate.queryForMap(sql,args); } /** * 查询返回List * @param sql * @param elementType * @param args * @return */ public <T> List<T> queryForList(String sql,Class<T> elementType, Object[] args){ if(exchange) TranslateFactory.translate(sql, dialect.getDialect()); return jdbcTemplate.queryForList(sql, elementType,args); } /** * 查询返回缓存List * @param cacheName * @param key * @param sql * @param elementType * @param args * @return */ public <T> List<T> queryForListByCache(String cacheName, Object key, String sql,Class<T> elementType, Object[] args){ List<T> result = cache.get(cacheName, key); if (result == null) { result = queryForList(sql, elementType, args); cache.put(cacheName, key, result); } return result; } /** * 插入或更新 * @param record * @return */ public <T extends Model> int merge(T record) { Record _record = RecordKit.convert2Record(record); String[] primaryKeys = _record.getPrimaryKeys(); Object[] ids = new Object[primaryKeys.length]; for(int i = 0;i < primaryKeys.length;i++){ ids[i] = ((Record)_record).get(primaryKeys[i]); if(ids[i]!=null&&StrKit.notBlank(ids[i].toString())){ return update(record); } } return save(record); } /** * 更新 * @param record * @return */ public <T extends Model> int update(T record) { StringBuilder sql = new StringBuilder(); List<Object> paras = new ArrayList<Object>(); Map<String,Object> ingore = ModelDeal.dealUpdate(record); Record _record = RecordKit.convert2Record(record); String[] primaryKeys = _record.getPrimaryKeys(); Object[] ids = new Object[primaryKeys.length]; for(int i = 0;i < primaryKeys.length;i++){ ids[i] = ((Record)_record).get(primaryKeys[i]); } dialect.getDialect().forDbUpdate(record.getTableName(), primaryKeys, ids, _record, sql, paras); ModelDeal.completeRecord(ingore, record); return jdbcTemplate.update(sql.toString(), paras.toArray()); } /** * 更新 * @param tableName * @param primaryKeys * @param record * @return */ public int update(String tableName,String[] primaryKeys,Record record){ Object[] ids = new Object[primaryKeys.length]; for(int i = 0;i < primaryKeys.length;i++){ ids[i] = record.get(primaryKeys[i]); } StringBuilder sql = new StringBuilder(); List<Object> paras = new ArrayList<Object>(); Map<String,Object> ingore = ModelDeal.dealUpdate(record); dialect.getDialect().forDbUpdate(tableName, primaryKeys, ids, record, sql, paras); ModelDeal.completeRecord(ingore, record); return jdbcTemplate.update(sql.toString(), paras.toArray()); } /** * 执行更新插入或删除 * @param sql * @return */ public int update(String sql){ if(exchange) TranslateFactory.translate(sql, dialect.getDialect()); return jdbcTemplate.update(