集成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进行封装
相关推荐
### 泛型Hibernate的实现与Spring集成 #### 一、泛型Hibernate概念及其实现 在Java开发领域，尤其在企业级应用开发中，Hibernate框架因其强大的对象关系映射（ORM）能力而受到广泛欢迎。泛型Hibernate是利用Java...
本程序整合了struts-2.2.3.1+spring-framework-3.1.0+hibernate-distribution-3.6.8+JSON+MySQL+Annotation，并且对Dao和Service进行了封装，内含.jar包，并且解决了一对多双向关联的could not initialize proxy - ...
JdbcTemplate是Spring JDBC的核心组件，它提供了一种模板方法设计模式，将常见的JDBC操作进行了封装，如打开和关闭连接、处理异常、管理事务等，让开发者只需关注SQL语句和结果集的处理。JdbcTemplate支持JDBC 2.0...
4. **数据访问/集成**：Spring提供了对各种持久层技术的支持，包括JDBC、ORM（Object-Relational Mapping）框架如Hibernate和MyBatis，以及对NoSQL数据库的集成。 5. ** Transactions（事务管理）**：Spring提供了...
在Java开发中，JDBC...总的来说，基于JDBC封装的`BaseDao`是一个基础的数据库操作模板，它通过泛型和反射实现了对不同实体类的通用操作。然而，实际项目中往往需要更全面和完善的解决方案，以满足复杂的业务需求。
3. `org.springframework.core`：这个基础包提供了Spring框架的基本工具类，包括泛型操作、反射、类型转换和资源访问等。 4. `org.springframework.aop`：AOP（面向切面编程）包，提供了面向切面的编程实现，可以...
Spring框架是Java开发中最常用的轻量级开源框架之一，它为构建企业级应用程序提供了全面的编程和配置模型。Spring 3.1版本是该框架的一个重要里程碑，引入了许多改进和新特性，旨在提升性能和开发效率。这个"spring...
5. `org.springframework.context.support-3.0.0.RELEASE`：提供了对Spring-context的扩展支持，主要用于MVC场景，包含了对邮件服务、任务调度、JNDI定位、EJB集成、远程访问、缓存和视图层框架封装等功能。...
- **使用 JDBC 进行数据访问：** Spring 对 JDBC 进行了封装，简化了 JDBC 编程的复杂性。 - **ORM 数据访问：** Spring 支持多种 ORM 框架的集成，如 Hibernate、MyBatis 等。 - **O/XMAPPERS：** Spring 提供了对 ...
Mybatis是一款轻量级的持久层框架，它避免了几乎所有的JDBC代码和手动设置参数以及获取结果集。通过提供自定义SQL、存储过程以及高级映射，Mybatis使得开发者能够专注于编写高质量的代码，而无需关心底层数据库交互...
Spring Data项目提供了一套统一的访问各种数据存储的API，包括JDBC、ORM（Object-Relational Mapping）框架如Hibernate和MyBatis，以及NoSQL数据库支持。5.28版本强化了对最新数据库驱动的支持，并引入了更多简化...
4. **数据访问集成**：Spring支持多种数据访问技术，包括JDBC、Hibernate、MyBatis等ORM框架，提供了一致的编程模型和异常处理机制，简化了数据库操作。 5. **MVC（Model-View-Controller）框架**：Spring MVC是...
4. **数据访问**：Spring 2.0提供了对JDBC的简化封装，以及对ORM（对象关系映射）框架如Hibernate、JPA的支持。这使得数据库操作变得更加简洁和灵活，同时避免了直接操作JDBC的繁琐。 5. **Web MVC框架**：Spring ...
3. 数据访问集成：Spring支持多种数据访问技术，包括JDBC、Hibernate、JPA等，提供了统一的API，简化了数据访问操作。 4. Web应用支持：Spring MVC是Spring提供的Web框架，它简化了创建基于模型-视图-控制器（MVC）...
6. **集成测试**：Spring Test和Hibernate的TestNG或JUnit结合，可以方便地进行单元测试和集成测试。通过`@ContextConfiguration`和`@Transactional`注解，可以在测试环境中轻松地启动Spring容器并管理事务。 7. **...
此外，还可以使用Spring的JdbcTemplate或NamedParameterJdbcTemplate进行更灵活的SQL操作，它们提供了一种简便的方式来执行SQL查询和更新，而无需直接与JDBC API打交道。 在实际项目中，SSH框架的数据库操作封装还...
3. **数据访问集成**：Spring 2.0 提供了对各种数据访问技术的集成，如 JPA（Java Persistence API）、Hibernate 和 JDBC，使得数据库操作更加简便。 4. **Web 模块强化**：Spring MVC 框架在 2.0 版本中得到进一步...
Spring 2.5提供了丰富的测试支持，包括`@Test`注解的`SpringJUnit4ClassRunner`，使得单元测试和集成测试变得更加便捷。 总之，Spring 2.5作为Spring框架的一个重要版本，不仅增强了核心功能，还引入了众多新特性，...
* 使用工厂模式有什么好处：我们可以将一些对象创建的过程封装在工厂类中， 使用者只需要从工厂中获取就可以，变得更加灵活 三、开发第一个Spring程序 * 搭建开发环境 1. 引入依赖：参考课堂案例 2. 引入配置...
3. **数据访问/集成**：Spring提供了JDBC、ORM（Object-Relational Mapping）和OXM（Object-XML Mapping）抽象层，支持Hibernate、JPA、MyBatis等持久化技术，简化了数据库操作。 4. **Web模块**：Spring MVC是...