集成spring jdbc,及jdbc泛型封装
创建表SQL
-- 表 test.t_role 结构
CREATE TABLE IF NOT EXISTS `t_role` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(50) COLLATE utf8_unicode_ci NOT NULL,
`remark` char(250) COLLATE utf8_unicode_ci DEFAULT NULL,
`create_time` date NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- 表 test.t_user 结构
CREATE TABLE IF NOT EXISTS `t_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(50) CHARACTER SET utf8 NOT NULL,
`age` int(11) NOT NULL DEFAULT '0',
`email` varchar(150) CHARACTER SET utf8 DEFAULT NULL,
`phone` char(15) CHARACTER SET utf8 NOT NULL,
`create_time` date NOT NULL,
`address` varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- 表 test.t_user_role 结构
CREATE TABLE IF NOT EXISTS `t_user_role` (
`role_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
PRIMARY KEY (`role_id`,`user_id`),
KEY `FK_t_user_role_t_user` (`user_id`),
CONSTRAINT `FK_t_user_role_t_role` FOREIGN KEY (`role_id`) REFERENCES `t_role` (`id`),
CONSTRAINT `FK_t_user_role_t_user` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
基于Maven搭建项目
1.配置pom.xml
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<spring.version>4.1.9.RELEASE</spring.version>
<aspectj.version>1.8.6</aspectj.version>
<jackson.version>2.6.4</jackson.version>
</properties>
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.10</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.apache.tomcat</groupId>
<artifactId>tomcat-servlet-api</artifactId>
<version>8.0.30</version>
<scope>provided</scope><!--打包时不包含进去-->
</dependency>
<dependency>
<groupId>javax.servlet.jsp</groupId>
<artifactId>jsp-api</artifactId>
<version>2.2</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.16</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.34</version>
</dependency>
<dependency>
<groupId>com.google.code.gson</groupId>
<artifactId>gson</artifactId>
<version>2.5</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.1</version>
</dependency>
<dependency><!-- 带反射机制 -->
<groupId>commons-beanutils</groupId>
<artifactId>commons-beanutils-core</artifactId>
<version>1.8.3</version>
</dependency>
<!-- 以下是Spring所需包-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-beans</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-web</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-orm</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context-support</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjrt</artifactId>
<version>${aspectj.version}</version>
</dependency>
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjweaver</artifactId>
<version>${aspectj.version}</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<version>1.7.13</version>
</dependency>
<dependency>
<groupId>org.glassfish</groupId>
<artifactId>javax.annotation</artifactId>
<version>3.1</version>
</dependency>
<dependency>
<groupId>cglib</groupId>
<artifactId>cglib-nodep</artifactId>
<version>3.1</version>
</dependency>
<dependency>
<groupId>commons-dbcp</groupId>
<artifactId>commons-dbcp</artifactId>
<version>1.4</version>
</dependency>
<dependency>
<groupId>org.freemarker</groupId>
<artifactId>freemarker</artifactId>
<version>2.3.22</version>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.4</version>
</dependency>
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.3.1</version>
</dependency>
<!-- Bonecp连接池 -->
<dependency>
<groupId>com.jolbox</groupId>
<artifactId>bonecp</artifactId>
<version>0.8.0.RELEASE</version>
</dependency>
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-core</artifactId>
<version>${jackson.version}</version>
</dependency>
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-databind</artifactId>
<version>${jackson.version}</version>
</dependency>
<dependency>
<groupId>org.json</groupId>
<artifactId>org.json</artifactId>
<version>chargebee-1.0</version>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>jstl</artifactId>
<version>1.2</version>
</dependency>
</dependencies>
<build>
<finalName>sssj-web</finalName>
<plugins>
<!-- <plugin>
<groupId>org.mortbay.jetty</groupId>
<artifactId>maven-jetty-plugin</artifactId>
<version>6.1.26</version>
<configuration>
设置扫描target/classes内部文件变化时间间隔
<scanIntervalSeconds>10</scanIntervalSeconds>
<connectors>
<connector implementation="org.mortbay.jetty.nio.SelectChannelConnector">
<port>8888</port>
</connector>
</connectors>
<webApp>
<contextPath>/</contextPath>
</webApp>
</configuration>
</plugin> -->
<plugin>
<groupId>org.apache.tomcat.maven</groupId>
<artifactId>tomcat7-maven-plugin</artifactId>
<version>2.1</version>
<configuration>
<port>8888</port>
<path>/</path>
<uriEncoding>UTF-8</uriEncoding>
<finalName>sssj</finalName>
<server>tomcat7</server>
</configuration>
</plugin>
</plugins>
</build>
有些jar是多余的,可根据项目需求进行修改。
2.配置bean.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:p="http://www.springframework.org/schema/p"
xmlns:cache="http://www.springframework.org/schema/cache"
xsi:schemaLocation="
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-4.1.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx-4.1.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop-4.1.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-4.1.xsd
http://www.springframework.org/schema/cache
http://www.springframework.org/schema/cache/spring-cache-4.1.xsd">
<!-- 注解支持 -->
<context:annotation-config />
<context:component-scan base-package="com.huhui">
<context:exclude-filter type="regex"
expression="com.huhui.web*" />
</context:component-scan>
<!-- 属性文件位置 -->
<bean id="propertiesConfig" class="com.huhui.common.EncryptPropertyPlaceholderConfigurer">
<property name="locations">
<list>
<value>classpath:jdbc.properties</value>
</list>
</property>
</bean>
<!-- 国际化支持 -->
<bean id="messageSource"
class="org.springframework.context.support.ResourceBundleMessageSource">
<property name="basenames">
<list>
<value>messages</value>
</list>
</property>
<property name="defaultEncoding" value="utf8" />
</bean>
<!-- 数据源 -->
<bean id="dataSource" class="com.jolbox.bonecp.BoneCPDataSource" destroy-method="close">
<property name="driverClass" value="${jdbc.driverClassName}" />
<property name="jdbcUrl" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
<property name="idleConnectionTestPeriod" value="${BoneCP.idleConnectionTestPeriod}" />
<property name="idleMaxAge" value="${BoneCP.idleMaxAge}" />
<property name="maxConnectionsPerPartition" value="${BoneCP.maxConnectionsPerPartition}" />
<property name="minConnectionsPerPartition" value="${BoneCP.minConnectionsPerPartition}" />
<property name="partitionCount" value="${BoneCP.partitionCount}" />
<property name="acquireIncrement" value="${BoneCP.acquireIncrement}" />
<property name="statementsCacheSize" value="${BoneCP.statementsCacheSize}" />
<property name="releaseHelperThreads" value="${BoneCP.releaseHelperThreads}" />
</bean>
<!-- 配置Jdbc模板 -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
<!-- 配置事务管理器 -->
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"
p:dataSource-ref="dataSource" />
<!-- 采用注解的方式配置事务 -->
<tx:annotation-driven transaction-manager="transactionManager"/>
</beans>
jdbc.properties
jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8
jdbc.username=8WN5qsYFsGc=
jdbc.password=DyyHAFCE34NOmJs5+WzviA==
##===============BoneCP配置==============##
#检查数据库连接池中空闲连接的间隔时间,单位是分,默认值:240,如果要取消则设置为0#
BoneCP.idleConnectionTestPeriod=30
#连接池中未使用的链接最大存活时间,单位是分,默认值:60,如果要永远存活设置为0#
BoneCP.idleMaxAge=10
#每个分区最大的连接数#
BoneCP.maxConnectionsPerPartition=3
#每个分区最小的连接数#
BoneCP.minConnectionsPerPartition=1
#分区数 ,默认值2,最小1,推荐3-4,视应用而定#
BoneCP.partitionCount=2
#每次去拿数据库连接的时候一次性要拿几个,默认值:2#
BoneCP.acquireIncrement=1
#缓存prepared statements的大小,默认值:0#
BoneCP.statementsCacheSize=0
#每个分区释放链接助理进程的数量,默认值:3,除非你的一个数据库连接的时间内做了很多工作,不然过多的助理进程会影响你的性能#
BoneCP.releaseHelperThreads=3
EncryptPropertyPlaceholderConfigurer.java
package com.huhui.common;
import org.springframework.beans.factory.config.PropertyPlaceholderConfigurer;
public class EncryptPropertyPlaceholderConfigurer extends PropertyPlaceholderConfigurer {
private String[] encryptPropNames = { "jdbc.username", "jdbc.password" };
@Override
protected String convertProperty(String propertyName, String propertyValue) {
// 如果在加密属性名单中发现该属性
if (isEncryptProp(propertyName)) {
String decryptValue ="";
try {
decryptValue = Des3Util.decryptMode(propertyValue);
} catch (Exception e) {
e.printStackTrace();
}
return decryptValue;
} else {
return propertyValue;
}
}
private boolean isEncryptProp(String propertyName) {
for (String encryptName : encryptPropNames) {
if (encryptName.equals(propertyName)) {
return true;
}
}
return false;
}
}
package com.huhui.common;
import javax.crypto.Cipher;
import javax.crypto.SecretKey;
import javax.crypto.spec.SecretKeySpec;
import sun.misc.BASE64Decoder;
import sun.misc.BASE64Encoder;
public class Des3Util {
private static final String Algorithm = "DESede"; // 定义 加密算法,可用DES,DESede,Blowfish
private static final byte[] key = ***;//24字节key
/**
* 加密
* @param keybyte 加密密钥,长度为24字节
* @param src 数据缓冲区(源)
* @return
*/
public static byte[] encryptMode(byte[] keybyte, byte[] src) {
try {
// 生成密钥
SecretKey deskey = new SecretKeySpec(keybyte, Algorithm);
// 加密
Cipher c1 = Cipher.getInstance(Algorithm);
c1.init(Cipher.ENCRYPT_MODE, deskey);
return c1.doFinal(src);
} catch (java.security.NoSuchAlgorithmException e1) {
e1.printStackTrace();
} catch (javax.crypto.NoSuchPaddingException e2) {
e2.printStackTrace();
} catch (java.lang.Exception e3) {
e3.printStackTrace();
}
return null;
}
/**
* 加密
* @param src
* @return
*/
public static String encryptMode(String src){
try {
// 生成密钥
SecretKey deskey = new SecretKeySpec(key, Algorithm);
// 加密
Cipher c1 = Cipher.getInstance(Algorithm);
c1.init(Cipher.ENCRYPT_MODE, deskey);
BASE64Encoder enc=new BASE64Encoder();
return enc.encodeBuffer(c1.doFinal(src.getBytes()));
} catch (java.security.NoSuchAlgorithmException e1) {
e1.printStackTrace();
} catch (javax.crypto.NoSuchPaddingException e2) {
e2.printStackTrace();
} catch (java.lang.Exception e3) {
e3.printStackTrace();
}
return null;
}
/**
* 解密
* @param keybyte 加密密钥,长度为24字节
* @param src 数据缓冲区(源)
* @return
*/
public static byte[] decryptMode(byte[] keybyte, byte[] src) {
try {
// 生成密钥
SecretKey deskey = new SecretKeySpec(keybyte, Algorithm);
// 解密
Cipher c1 = Cipher.getInstance(Algorithm);
c1.init(Cipher.DECRYPT_MODE, deskey);
return c1.doFinal(src);
} catch (java.security.NoSuchAlgorithmException e1) {
e1.printStackTrace();
} catch (javax.crypto.NoSuchPaddingException e2) {
e2.printStackTrace();
} catch (java.lang.Exception e3) {
e3.printStackTrace();
}
return null;
}
/**
* 解密
* @param src
* @return
*/
public static String decryptMode(String src){
try {
// 生成密钥
SecretKey deskey = new SecretKeySpec(key, Algorithm);
// 解密
Cipher c1 = Cipher.getInstance(Algorithm);
c1.init(Cipher.DECRYPT_MODE, deskey);
BASE64Decoder dec = new BASE64Decoder();
return new String(c1.doFinal(dec.decodeBuffer(src)));
} catch (java.security.NoSuchAlgorithmException e1) {
e1.printStackTrace();
} catch (javax.crypto.NoSuchPaddingException e2) {
e2.printStackTrace();
} catch (java.lang.Exception e3) {
e3.printStackTrace();
}
return null;
}
}
3.配置web.xml
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" id="WebApp_ID" version="3.0">
<context-param>
<param-name>contextConfigLocation</param-name>
<param-value>classpath*:beans*.xml</param-value>
</context-param>
<!-- 通过listener对Spring容器进行实例化 -->
<listener>
<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
</listener>
<!-- Freemarker引用其它的标签库 -->
<listener>
<listener-class>freemarker.ext.jsp.EventForwarding</listener-class>
</listener>
<!-- 处理字符编码,解决中文乱码问题 -->
<filter>
<filter-name>CharacterEncodingFilter</filter-name>
<filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
<init-param>
<param-name>encoding</param-name>
<param-value>UTF-8</param-value>
</init-param>
</filter>
<filter-mapping>
<filter-name>CharacterEncodingFilter</filter-name>
<url-pattern>/*</url-pattern>
</filter-mapping>
<session-config>
<session-timeout>30</session-timeout>
</session-config>
</web-app>
4.DAO封装代码实现
package com.huhui.dao;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import org.springframework.jdbc.core.RowMapper;
public interface BaseDao {
/**
* 插入/更新/删除数据
* @param sql
* @param params
*/
public void operate(String sql, Object[] params);
/**
* 增加表数据
* @param tableName 表名
* @param map key为表字段名,value为该字段值,例:map.put("field1", "test");
*/
public void save(String tableName, Map<String, Object> map);
/**
* 增加表数据
* @param tableName 表名
* @param map key为表字段名,value为该字段值,例:map.put("field1", "test");
* @return 返回自增ID值
*/
public int saveReAutoId(String tableName, Map<String, Object> map);
/**
* 批量增加表数据
* @param tableName 表名
* @param fields 要增加的字段集,例:{field1,field2,...}
* @param list 数据集,map key为表字段名,value为该字段值,例:map.put("field1", "test");
*/
public void saveBatch(String tableName, final String[] fields, final List<Map<String, Object>> list);
/**
* 修改表数据
* @param tableName 表名
* @param map 要修改的数据集,key为表字段名,value为该字段值,例:map.put("field1", "test");
* @param whereMap 过滤条件集,key为表字段名和判断符,value为该字段值,例:map.put("field1 = ", "test");
*/
public void update(String tableName, Map<String, Object> map, LinkedHashMap<String, Object> whereMap);
/**
* 批量修改表数据
* @param tableName 表名
* @param fields 要修改的字段集,例:{field1,field2,...}
* @param whereFields 过滤条件字段集,key为表字段名,value为判断符,例:map.put("field1", "=");
* @param list 数据集,map key为表字段名,value为该字段值,例:map.put("field1", "test");
*/
public void updateBatch(String tableName, String[] fields, LinkedHashMap<String, String> whereFields, final List<Map<String, Object>> list);
/**
* 删除表数据
* @param tableName 表名
* @param id 参数id值
*/
public void delete(String tableName, int id);
/**
* 批量删除表数据
* @param tableName 表名
* @param ids
*/
public void deleteBatch(String tableName, final Integer[] ids);
/**
* 根据id查询表数据
* @param tableName 表名
* @param fields 查询结果字段集,例:{field1,field2,...}
* @param id 查询条件id值
* @param rowMapper 结果封装对象
* @return
*/
public <T> T findById(String tableName, String[] fields, int id, RowMapper<T> rowMapper);
/**
* 根据id查询表数据
* @param tableName 表名
* @param field 查询结果字段(仅一个字段)
* @param id 查询条件id值
* @param clazz 结果对象类
* @return
*/
public <T> T findById(String tableName, String field, int id, Class<T> clazz);
/**
* 查询数据(不分页)
* @param fields 查询结果字段集,例:{field1,field2,...}
* @param fromSql
* @param maxresult 每页显示数(maxresult<=0时查询所有)
* @param where
* @param params
* @param groupby
* @param orderby
* @param rowMapper 结果封装对象
* @return
*/
public <T> List<T> findByList(String[] fields, String fromSql, int maxresult, String where,
List<Object> params,String groupby,LinkedHashMap<String, String> orderby, RowMapper<T> rowMapper);
}
package com.huhui.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
@Transactional(rollbackFor=Exception.class)
public class BaseDaoImpl implements BaseDao {
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public void operate(String sql, Object[] params) {
jdbcTemplate.update(sql, params);
}
@Override
public void save(String tableName, Map<String, Object> map) {
List<Object> params = new ArrayList<Object>();
StringBuffer sql = new StringBuffer("insert into ");
sql.append(tableName).append("(");
StringBuffer field = new StringBuffer();
for(Entry<String, Object> entry:map.entrySet()){
field.append(entry.getKey()).append(",");
params.add(entry.getValue());
}
sql.append(field.deleteCharAt(field.length()-1));
sql.append(") values (");
StringBuffer fieldVal = new StringBuffer();
for(int i=0,len=map.size();i<len;i++){
fieldVal.append("?,");
}
sql.append(fieldVal.deleteCharAt(fieldVal.length()-1));
sql.append(");");
jdbcTemplate.update(sql.toString(), params.toArray());
}
@Override
public int saveReAutoId(String tableName, Map<String, Object> map) {
List<Object> params = new ArrayList<Object>();
StringBuffer sql = new StringBuffer("insert into ");
sql.append(tableName).append("(");
StringBuffer field = new StringBuffer();
for(Entry<String, Object> entry:map.entrySet()){
field.append(entry.getKey()).append(",");
params.add(entry.getValue());
}
sql.append(field.deleteCharAt(field.length()-1));
sql.append(") values (");
StringBuffer fieldVal = new StringBuffer();
for(int i=0,len=map.size();i<len;i++){
fieldVal.append("?,");
}
sql.append(fieldVal.deleteCharAt(fieldVal.length()-1)).append(");");
final String sqlTemp = sql.toString();
final List<Object> paramsTemp = params;
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
PreparedStatement ps = con.prepareStatement(sqlTemp, new String[]{"id"});
for(int j=0,len=paramsTemp.size();j<len;j++){
ps.setObject(j+1, paramsTemp.get(j));
}
return ps;
}
}, keyHolder);
return keyHolder.getKey().intValue();
}
@Override
public void saveBatch(String tableName, final String[] fields, final List<Map<String, Object>> list) {
StringBuffer sql = new StringBuffer("insert into ");
sql.append(tableName).append("(").append(buildFields(fields)).append(") values (");
StringBuffer fieldVal = new StringBuffer();
for(int i=0,len=fields.length;i<len;i++){
fieldVal.append("?,");
}
sql.append(fieldVal.deleteCharAt(fieldVal.length()-1)).append(");");
jdbcTemplate.batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
for(int j=0,len=fields.length;j<len;j++){
ps.setObject(j+1, list.get(i).get(fields[j]));
}
}
@Override
public int getBatchSize() {
return list.size();
}
});
}
@Override
public void update(String tableName, Map<String, Object> map, LinkedHashMap<String, Object> whereMap) {
List<Object> params = new ArrayList<Object>();
StringBuffer sql = new StringBuffer("update ");
sql.append(tableName).append(" set ");
StringBuffer temp = new StringBuffer();
for(Entry<String, Object> entry:map.entrySet()){
temp.append(entry.getKey()).append("=").append("?,");
params.add(entry.getValue());
}
sql.append(temp.deleteCharAt(temp.length()-1));
if(whereMap!=null){
sql.append(" where 1=1 ");
for(Entry<String, Object> entry:whereMap.entrySet()){
sql.append(" and ").append(entry.getKey()).append("?");
params.add(entry.getValue());
}
}
sql.append(";");
jdbcTemplate.update(sql.toString(), params.toArray());
}
@Override
public void updateBatch(String tableName, String[] fields, LinkedHashMap<String, String> whereFields, final List<Map<String, Object>> list) {
StringBuffer sql = new StringBuffer("update ");
sql.append(tableName).append(" set ");
final List<String> paramKeys = new ArrayList<String>();
StringBuffer temp = new StringBuffer();
for(String ft:fields){
temp.append(ft).append("=").append("?,");
paramKeys.add(ft);
}
sql.append(temp.deleteCharAt(temp.length()-1));
if(whereFields!=null){
sql.append(" where 1=1 ");
for(Entry<String, String> entry:whereFields.entrySet()){
sql.append(" and ").append(entry.getKey()).append(" ").append(entry.getValue()).append(" ").append("?");
paramKeys.add(entry.getKey());
}
}
sql.append(";");
jdbcTemplate.batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
for(int j=0,len=paramKeys.size();j<len;j++){
ps.setObject(j+1, list.get(i).get(paramKeys.get(j)));
}
}
@Override
public int getBatchSize() {
return list.size();
}
});
}
@Override
public void delete(String tableName, int id) {
StringBuffer sql = new StringBuffer("delete from ");
sql.append(tableName).append(" where id=?;");
jdbcTemplate.update(sql.toString(), new Object[]{id});
}
@Override
public void deleteBatch(String tableName, final Integer[] ids) {
StringBuffer sql = new StringBuffer("delete from ");
sql.append(tableName).append(" where id=?;");
jdbcTemplate.batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
ps.setInt(1, ids[i]);
}
@Override
public int getBatchSize() {
return ids.length;
}
});
}
@Transactional(readOnly = true, propagation = Propagation.NOT_SUPPORTED)
@Override
public <T> T findById(String tableName, String[] fields, int id, RowMapper<T> rowMapper) {
StringBuffer sql = new StringBuffer("select ");
sql.append(buildFields(fields)).append(" ").append("from ").append(tableName).append(" where id=? limit 1;");
return jdbcTemplate.queryForObject(sql.toString(), new Object[]{id}, rowMapper);
}
@Transactional(readOnly = true, propagation = Propagation.NOT_SUPPORTED)
@Override
public <T> T findById(String tableName, String field, int id, Class<T> clazz) {
StringBuffer sql = new StringBuffer("select ");
sql.append(field).append(" ").append("from ").append(tableName).append(" where id=? limit 1;");
return jdbcTemplate.queryForObject(sql.toString(), new Object[]{id}, clazz);
}
@Transactional(readOnly = true, propagation = Propagation.NOT_SUPPORTED)
@Override
public <T> List<T> findByList(String[] fields, String fromSql, int maxresult, String where,
List<Object> params, String groupby, LinkedHashMap<String, String> orderby, RowMapper<T> rowMapper) {
StringBuffer sql = new StringBuffer("select ");
sql.append(buildFields(fields)).append(" from ").append(fromSql)
.append(StringUtils.isEmpty(where)?"":" where "+where).append(" ")
.append(StringUtils.isEmpty(groupby)?"":groupby).append(buildOrderby(orderby));
if(params==null) params = new ArrayList<Object>();
if(maxresult>0){
sql.append(" limit 1,?;");
params.add(maxresult);
}
return jdbcTemplate.query(sql.toString(), params.toArray(), rowMapper);
}
/**
* 组装字段集
* @param fields
* @return field1,field2,...
*/
private StringBuffer buildFields(String[] fields) {
StringBuffer fieldTemp = new StringBuffer();
if(fields!=null && fields.length>0){
for(String field:fields){
fieldTemp.append(field).append(",");
}
fieldTemp.deleteCharAt(fieldTemp.length()-1);
}
return fieldTemp;
}
/**
* 组装order by语句
* @param orderby
* @return order by t1.field1 desc,t2.field2 asc
*/
private StringBuffer buildOrderby(LinkedHashMap<String, String> orderby){
StringBuffer orderbyql=new StringBuffer();
if(orderby!=null&&orderby.size()>0){
orderbyql.append(" order by ");
for(String key:orderby.keySet()){
orderbyql.append(key).append(" ").append(orderby.get(key)).append(",");
}
orderbyql.deleteCharAt(orderbyql.length()-1);
}
return orderbyql;
}
}
5.相应Service、Entity和Model代码实现
public interface UserService extends BaseDao {
}
@Service
public class UserServiceImpl extends BaseDaoImpl implements UserService {
}
public class UserEntity {
private static final String t_tableNickname = "user";
public static final String t_tableName = "t_user "+t_tableNickname;
public static final String t_id = t_tableNickname+".id";
public static final String t_name = t_tableNickname+".name";
public static final String t_age = t_tableNickname+".age";
public static final String t_email = t_tableNickname+".email";
public static final String t_phone = t_tableNickname+".phone";
public static final String t_createTime = t_tableNickname+".create_time";
public static final String t_address = t_tableNickname+".address";
}
@SuppressWarnings("serial")
public class UserModel implements RowMapper<UserModel>, Serializable {
private int id;
private String name;
private int age;
private String email;
private String phone;
private Date createTime;
private String address;
private String[] fields;//查询结果字段集
public UserModel() { }
/**
* 查询结果集封装成对象
* @param fields 查询结果字段集
*/
public UserModel(String[] fields) {
this.fields = fields;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public void setFields(String[] fields) {
this.fields = fields;
}
@Override
public UserModel mapRow(ResultSet rs, int rowNum) throws SQLException {
UserModel user = new UserModel();
if(fields!=null && fields.length>0){
Map<String, String> map = new HashMap<String, String>();
for(String f:fields){
map.put(f, f);
}
if(StringUtils.isNotEmpty(map.get(UserEntity.t_id)))
user.setId(rs.getInt(UserEntity.t_id));
if(StringUtils.isNotEmpty(map.get(UserEntity.t_name)))
user.setName(rs.getString(UserEntity.t_name));
if(StringUtils.isNotEmpty(map.get(UserEntity.t_age)))
user.setAge(rs.getInt(UserEntity.t_age));
if(StringUtils.isNotEmpty(map.get(UserEntity.t_email)))
user.setEmail(rs.getString(UserEntity.t_email));
if(StringUtils.isNotEmpty(map.get(UserEntity.t_phone)))
user.setPhone(rs.getString(UserEntity.t_phone));
if(StringUtils.isNotEmpty(map.get(UserEntity.t_createTime)))
user.setCreateTime(rs.getDate(UserEntity.t_createTime));
if(StringUtils.isNotEmpty(map.get(UserEntity.t_address)))
user.setAddress(rs.getString(UserEntity.t_address));
}
return user;
}
}
分页代码的封装,可根据自己的分页模块,按照此思路进行封装 。
6.测试
public class TestJdbc {
private static ApplicationContext cxt;
private static UserService userService;
@BeforeClass
public static void setUpBeforeClass() throws Exception {
try {
cxt=new ClassPathXmlApplicationContext("beans.xml");
userService=(UserService) cxt.getBean("userServiceImpl");
} catch (Exception e) {
e.printStackTrace();
}
}
@Test
public void testSave() {
Map<String, Object> map = new HashMap<String, Object>();
map.put(UserEntity.t_age, 14);
map.put(UserEntity.t_name, "新增字段测试2");
map.put(UserEntity.t_createTime, new Date());
map.put(UserEntity.t_phone, "1339482322");
map.put(UserEntity.t_email, "newmail2@sina.cn");
map.put(UserEntity.t_address, "深圳某区");
userService.save(UserEntity.t_tableName, map);
}
@Test
public void testSaveBatch() {
List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
Map<String, Object> map = new HashMap<String, Object>();
map.put(UserEntity.t_age, 14);
map.put(UserEntity.t_name, "新增字段测试2");
map.put(UserEntity.t_createTime, new Date());
map.put(UserEntity.t_phone, "1339482322");
map.put(UserEntity.t_email, "newmail2@sina.cn");
map.put(UserEntity.t_address, "深圳某区");
list.add(map);
map.put(UserEntity.t_age, 14);
map.put(UserEntity.t_name, "新增字段测试3");
map.put(UserEntity.t_createTime, new Date());
map.put(UserEntity.t_phone, "1339482333");
map.put(UserEntity.t_email, "newmail3@sina.cn");
map.put(UserEntity.t_address, "深圳某区");
list.add(map);
String[] fields = new String[]{UserEntity.t_name,UserEntity.t_age,UserEntity.t_email,UserEntity.t_phone,UserEntity.t_address};
roleService.saveBatch(UserEntity.t_tableName, fields, list);
}
@Test
public void testUpdate() {
Map<String, Object> map = new HashMap<String, Object>();
map.put(UserEntity.t_name, "抽象测试-111");
map.put(UserEntity.t_phone, "47439482-333");
LinkedHashMap<String, Object> whereMap = new LinkedHashMap<String, Object>();
whereMap.put(UserEntity.t_id+" = ", 111);
whereMap.put(UserEntity.t_email+" like ", "eo%");
whereMap.put(UserEntity.t_age+" > ", 15);
userService.update(UserEntity.t_tableName, map, whereMap);
}
@Test
public void testUpdateBatch() {
LinkedHashMap<String, String> whereFields = new LinkedHashMap<String, String>();
whereFields.put(UserEntity.t_id, "=");
List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
Map<String, Object> map = new HashMap<String, Object>();
map.put(UserEntity.t_name, "批量修改测试5");
map.put(UserEntity.t_email, "updatebatch5@qq.com");
map.put(UserEntity.t_phone, "55439482577");
map.put(UserEntity.t_address, null);
map.put(UserEntity.t_age, 11);
map.put(UserEntity.t_id, 110);
list.add(map);
map = new HashMap<String, Object>();
map.put(UserEntity.t_name, "批量修改测试6");
map.put(UserEntity.t_email, "updatebatch6@qq.com");
map.put(UserEntity.t_phone, "44439482577");
map.put(UserEntity.t_address, "业余爱好者区域");
map.put(UserEntity.t_age, 11);
map.put(UserEntity.t_id, 111);
list.add(map);
String[] fields = new String[]{UserEntity.t_name,UserEntity.t_age,UserEntity.t_email,UserEntity.t_phone,UserEntity.t_address};
userService.updateBatch(UserEntity.t_tableName, fields, whereFields, list);
}
@Test
public void testFindById() {
String[] fields = new String[]{UserEntity.t_id,UserEntity.t_name,UserEntity.t_age,UserEntity.t_email,UserEntity.t_phone,UserEntity.t_createTime,UserEntity.t_address};
UserModel user = userService.findById(UserEntity.t_tableName, fields, 133, new UserModel(fields));
System.out.println(user.getId()+"---"+user.getName()+"---"+user.getAge()+"---"+user.getEmail()
+"---"+user.getPhone()+"---"+user.getCreateTime()+"---"+user.getAddress());
Integer age = userService.findById(UserEntity.t_tableName, UserEntity.t_age, 133, Integer.class);
System.out.println(age);
}
@Test
public void testDelete() {
userService.delete(UserEntity.t_tableName, 106);
}
@Test
public void testDeleteBatch() {
userService.deleteBatch(UserEntity.t_tableName, new Integer[]{105,104,103});
}
@Test
public void testFindByList() {
String[] fields = new String[]{UserEntity.t_id,UserEntity.t_name,UserEntity.t_age,UserEntity.t_email,UserEntity.t_phone,UserEntity.t_createTime,UserEntity.t_address};
StringBuffer fromSql = new StringBuffer(UserEntity.t_tableName);
List<Object> params = new ArrayList<Object>();
StringBuffer where = new StringBuffer("1=1");
where.append(" and ").append(UserEntity.t_name).append(" like ?");
params.add("抽象测试%");
where.append(" and ").append(UserEntity.t_phone).append(" like ?");
params.add("47439482%");
LinkedHashMap<String, String> orderby = new LinkedHashMap<String, String>();
orderby.put(UserEntity.t_id, "desc");
List<UserModel> list = userService.findByList(fields, fromSql.toString(), 0, where.toString(), params, null, orderby, new UserModel(fields));
System.out.println(list.size());
for(UserModel user:list){
System.out.println(user.getId()+"---"+user.getName()+"---"+user.getAge()+"---"+user.getEmail()
+"---"+user.getPhone()+"---"+user.getCreateTime()+"---"+user.getAddress());
}
}
@Test
public void testFindByList2() {
String[] fields = new String[]{RoleEntity.t_id,RoleEntity.t_name,UserEntity.t_id,UserEntity.t_name};
StringBuffer fromSql = new StringBuffer(RoleEntity.t_tableName);
fromSql.append(" left join ").append(UserRoleEntity.t_tableName).append(" on ").append(UserRoleEntity.t_roleId).append("=").append(RoleEntity.t_id);
fromSql.append(" left join ").append(UserEntity.t_tableName).append(" on ").append(UserEntity.t_id).append("=").append(UserRoleEntity.t_userId);
List<Object> params = new ArrayList<Object>();
StringBuffer where = new StringBuffer("1=1");
where.append(" and ").append(RoleEntity.t_id).append(" = ?");
params.add(1);
LinkedHashMap<String, String> orderby = new LinkedHashMap<String, String>();
orderby.put(RoleEntity.t_id, "desc");
List<UserModel> list = userService.findByList(fields, fromSql.toString(), 0, where.toString(), params, null, orderby, new UserModel(fields));
for(UserModel user:list){
System.out.println(user.getRoleId()+"---"+user.getRoleName()+"---"+user.getId()+"---"+user.getName());
}
}
}
最后一个测试方法是多表关联查询,在此也给出相应的Entity、Model
public class RoleEntity {
private static final String t_tableNickname = "role";
public static final String t_tableName = "t_role "+t_tableNickname;
public static final String t_id = t_tableNickname+".id";
public static final String t_name = t_tableNickname+".name";
public static final String t_remark = t_tableNickname+".remark";
public static final String t_createTime = t_tableNickname+".create_time";
}
public class UserRoleEntity {
private static final String t_tableNickname = "user_role";
public static final String t_tableName = "t_user_role "+t_tableNickname;
public static final String t_userId = t_tableNickname+".user_id";
public static final String t_roleId = t_tableNickname+".role_id";
}
@SuppressWarnings("serial")
public class RoleModel implements RowMapper<RoleModel>, Serializable {
private int id;
private String name;
private String remark;
private Date createTime;
private String[] fields;//查询结果字段集
public RoleModel() { }
/**
* 查询结果集封装成对象
* @param fields 查询结果字段集
*/
public RoleModel(String[] fields) {
this.fields = fields;
}
//get/set略
public void setFields(String[] fields) {
this.fields = fields;
}
@Override
public RoleModel mapRow(ResultSet rs, int rowNum) throws SQLException {
RoleModel role = new RoleModel();
if(fields!=null && fields.length>0){
Map<String, String> map = new HashMap<String, String>();
for(String f:fields){
map.put(f, f);
}
if(StringUtils.isNotEmpty(map.get(RoleEntity.t_id)))
role.setId(rs.getInt(RoleEntity.t_id));
if(StringUtils.isNotEmpty(map.get(RoleEntity.t_name)))
role.setName(rs.getString(RoleEntity.t_name));
if(StringUtils.isNotEmpty(map.get(RoleEntity.t_remark)))
role.setRemark(rs.getString(RoleEntity.t_remark));
if(StringUtils.isNotEmpty(map.get(RoleEntity.t_createTime)))
role.setCreateTime(rs.getDate(RoleEntity.t_createTime));
}
return role;
}
}
在多表关联查询时,在UserModel类里加了两个属性:
@SuppressWarnings("serial")
public class UserModel implements RowMapper<UserModel>, Serializable {
//其它属性略
private int roleId;
private String roleName;
//get/set略
@Override
public UserModel mapRow(ResultSet rs, int rowNum) throws SQLException {
UserModel user = new UserModel();
if(fields!=null && fields.length>0){
Map<String, String> map = new HashMap<String, String>();
for(String f:fields){
map.put(f, f);
}
//其它属性封装略
if(StringUtils.isNotEmpty(map.get(RoleEntity.t_id)))
user.setRoleId(rs.getInt(RoleEntity.t_id));
if(StringUtils.isNotEmpty(map.get(RoleEntity.t_name)))
user.setRoleName(rs.getString(RoleEntity.t_name));
}
return user;
}
}
若有需要,还可以将Model转Map进行封装