使用的Spring是3.1版本,不是3.0版本。两者还是有区别的，其中一点就是：SimpleJdbcTemplate在3.1版本被标记为过时了，而SimpleJdbcTemplate的一些方法，被JdbcTemplate吸收了。所以，个人推荐使用3.1版本.
需要的JAR文件：
org.springframework.aop-3.1.0.RELEASE.jar
org.springframework.asm-3.1.0.RELEASE.jar
org.springframework.beans-3.1.0.RELEASE.jar
org.springframework.context-3.1.0.RELEASE.jar
org.springframework.core-3.1.0.RELEASE.jar
org.springframework.expression-3.1.0.RELEASE.jar
org.springframework.jdbc-3.1.0.RELEASE.jar
org.springframework.test-3.1.0.RELEASE.jar
org.springframework.transaction-3.1.0.RELEASE.jar
com.springsource.net.sf.cglib-2.2.0.jar
com.springsource.org.aopalliance-1.0.0.jar
com.springsource.org.aspectj.weaver-1.6.8.RELEASE.jar
com.springsource.org.apache.commons.logging-1.1.1.jar
com.springsource.org.junit-4.7.0.jar
ojdbc14.jar
数据库脚本：
Sql代码 收藏代码
–创建商品表
create table product(
id varchar2(255) primary key, name varchar2(255), author varchar2(255), price number(6,2), quantity number, description varchar2(255)
);
实体类：
Java代码 收藏代码
package org.monday.springjdbc.domain;
public class Product /implements Serializable/{ //暂不序列化
private String id;
private String name;
private String author;
private double price;
private int quantity;
private String description;
public Product() {
}
// getter and setter
@Override
public String toString() {
return "Product [id=" + id + ", name=" + name + ", author=" + author + ", price=" + price + ", quantity="
+ quantity + ", description=" + description + "]\n";
}
}
正题：
BaseDao接口不提供了，附件中会有，直接给出BaseDaoImpl
Java代码 收藏代码
package org.monday.springjdbc.dao.impl;
import java.io.Serializable;
import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.sql.Types;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import org.monday.springjdbc.dao.BaseDao;
import org.monday.springjdbc.util.QueryResult;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
/**
使用场景
数据库是Oracle
主键列名为id
主键由程序提供（这里用的是UUID）
实体类名和数据库表名一致 比如：类名Product 表名product、PRODUCT、Produc 都可以 t_product 不可以
*/
public class BaseDaoImpl implements BaseDao {
/* 设置一些操作的常量 /
public static final String SQL_INSERT = “insert”;
public static final String SQL_UPDATE = “update”;
public static final String SQL_DELETE = “delete”;
private JdbcTemplate jdbcTemplate;
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
private Class entityClass;
@SuppressWarnings(“unchecked”)
public BaseDaoImpl() {
ParameterizedType type = (ParameterizedType) getClass().getGenericSuperclass();
entityClass = (Class<T>) type.getActualTypeArguments()[0];
System.out.println("Dao实现类是：" + entityClass.getName()); }
@Override
public void save(T entity) {
String sql = this.makeSql(SQL_INSERT); Object[] args = this.setArgs(entity, SQL_INSERT); int[] argTypes = this.setArgTypes(entity, SQL_INSERT); jdbcTemplate.update(sql.toString(), args, argTypes);
}
@Override
public void update(T entity) {
String sql = this.makeSql(SQL_UPDATE); Object[] args = this.setArgs(entity, SQL_UPDATE); int[] argTypes = this.setArgTypes(entity, SQL_UPDATE); jdbcTemplate.update(sql, args, argTypes);
}
@Override
public void delete(T entity) {
String sql = this.makeSql(SQL_DELETE); Object[] args = this.setArgs(entity, SQL_DELETE); int[] argTypes = this.setArgTypes(entity, SQL_DELETE); jdbcTemplate.update(sql, args, argTypes);
}
@Override
public void delete(Serializable id) {
String sql = " DELETE FROM " + entityClass.getSimpleName() + " WHERE id=?"; jdbcTemplate.update(sql, id);
}
@Override
public void deleteAll() {
String sql = " TRUNCATE TABLE " + entityClass.getSimpleName(); jdbcTemplate.execute(sql);
}
/**
}
/**
}
/**
}
@Override
public T findById(Serializable id) {
String sql = "SELECT * FROM " + entityClass.getSimpleName() + " WHERE id=?"; RowMapper<T> rowMapper = BeanPropertyRowMapper.newInstance(entityClass); return jdbcTemplate.query(sql, rowMapper, id).get(0);
}
@Override
public List findAll() {
String sql = "SELECT * FROM " + entityClass.getSimpleName(); RowMapper<T> rowMapper = BeanPropertyRowMapper.newInstance(entityClass); return jdbcTemplate.query(sql, rowMapper);
}
@Override
public QueryResult findByPage(int pageNo, int pageSize) {
List<T> list = this.find(pageNo, pageSize, null, null); int totalRow = this.count(null); return new QueryResult<T>(list, totalRow);
}
@Override
public QueryResult findByPage(int pageNo, int pageSize, Map where) {
List<T> list = this.find(pageNo, pageSize, where, null); int totalRow = this.count(where); return new QueryResult<T>(list, totalRow);
}
@Override
public QueryResult findByPage(int pageNo, int pageSize, LinkedHashMap orderby) {
List<T> list = this.find(pageNo, pageSize, null, orderby); int totalRow = this.count(null); return new QueryResult<T>(list, totalRow);
}
@Override
public QueryResult findByPage(int pageNo, int pageSize, Map where,
LinkedHashMap<String, String> orderby) {
List<T> list = this.find(pageNo, pageSize, where, orderby);
int totalRow = this.count(where);
return new QueryResult<T>(list, totalRow); }
// 组装SQL
private String makeSql(String sqlFlag) {
StringBuffer sql = new StringBuffer();
Field[] fields = entityClass.getDeclaredFields();
if (sqlFlag.equals(SQL_INSERT)) {
sql.append(" INSERT INTO " + entityClass.getSimpleName());
sql.append("(");
for (int i = 0; fields != null && i < fields.length; i++) {
fields[i].setAccessible(true); // 暴力反射
String column = fields[i].getName();
sql.append(column).append(",");
}
sql = sql.deleteCharAt(sql.length() - 1);
sql.append(") VALUES (");
for (int i = 0; fields != null && i < fields.length; i++) {
sql.append("?,");
}
sql = sql.deleteCharAt(sql.length() - 1);
sql.append(")");
} else if (sqlFlag.equals(SQL_UPDATE)) {
sql.append(" UPDATE " + entityClass.getSimpleName() + " SET ");
for (int i = 0; fields != null && i < fields.length; i++) {
fields[i].setAccessible(true); // 暴力反射
String column = fields[i].getName();
if (column.equals("id")) { // id 代表主键
continue;
}
sql.append(column).append("=").append("?,");
}
sql = sql.deleteCharAt(sql.length() - 1);
sql.append(" WHERE id=?");
} else if (sqlFlag.equals(SQL_DELETE)) {
sql.append(" DELETE FROM " + entityClass.getSimpleName() + " WHERE id=?");
}
System.out.println("SQL=" + sql);
return sql.toString(); }
// 设置参数
private Object[] setArgs(T entity, String sqlFlag) {
Field[] fields = entityClass.getDeclaredFields();
if (sqlFlag.equals(SQL_INSERT)) {
Object[] args = new Object[fields.length];
for (int i = 0; args != null && i < args.length; i++) {
try {
fields[i].setAccessible(true); // 暴力反射
args[i] = fields[i].get(entity);
} catch (Exception e) {
e.printStackTrace();
}
}
return args;
} else if (sqlFlag.equals(SQL_UPDATE)) {
Object[] tempArr = new Object[fields.length];
for (int i = 0; tempArr != null && i < tempArr.length; i++) {
try {
fields[i].setAccessible(true); // 暴力反射
tempArr[i] = fields[i].get(entity);
} catch (Exception e) {
e.printStackTrace();
}
}
Object[] args = new Object[fields.length];
System.arraycopy(tempArr, 1, args, 0, tempArr.length - 1); // 数组拷贝
args[args.length - 1] = tempArr[0];
return args;
} else if (sqlFlag.equals(SQL_DELETE)) {
Object[] args = new Object[1]; // 长度是1
fields[0].setAccessible(true); // 暴力反射
try {
args[0] = fields[0].get(entity);
} catch (Exception e) {
e.printStackTrace();
}
return args;
}
return null; }
// 设置参数类型（写的不全，只是一些常用的）
private int[] setArgTypes(T entity, String sqlFlag) {
if (fields[i].get(entity).getClass().getName().equals("java.lang.String")) {
if (sqlFlag.equals(SQL_INSERT)) {
int[] argTypes = new int[fields.length];
try {
for (int i = 0; argTypes != null && i < argTypes.length; i++) {
fields[i].setAccessible(true); // 暴力反射
if (fields[i].get(entity).getClass().getName().equals("java.lang.String")) {
argTypes[i] = Types.VARCHAR;
} else if (fields[i].get(entity).getClass().getName().equals("java.lang.Double")) {
argTypes[i] = Types.DECIMAL;
} else if (fields[i].get(entity).getClass().getName().equals("java.lang.Integer")) {
argTypes[i] = Types.INTEGER;
} else if (fields[i].get(entity).getClass().getName().equals("java.util.Date")) {
argTypes[i] = Types.DATE;
}
}
} catch (Exception e) {
e.printStackTrace();
}
return argTypes;
} else if (sqlFlag.equals(SQL_UPDATE)) {
int[] tempArgTypes = new int[fields.length];
int[] argTypes = new int[fields.length];
try {
for (int i = 0; tempArgTypes != null && i < tempArgTypes.length; i++) {
fields[i].setAccessible(true); // 暴力反射
if (fields[i].get(entity).getClass().getName().equals("java.lang.String")) {
tempArgTypes[i] = Types.VARCHAR;
} else if (fields[i].get(entity).getClass().getName().equals("java.lang.Double")) {
tempArgTypes[i] = Types.DECIMAL;
} else if (fields[i].get(entity).getClass().getName().equals("java.lang.Integer")) {
tempArgTypes[i] = Types.INTEGER;
} else if (fields[i].get(entity).getClass().getName().equals("java.util.Date")) {
tempArgTypes[i] = Types.DATE;
}
}
System.arraycopy(tempArgTypes, 1, argTypes, 0, tempArgTypes.length - 1); // 数组拷贝
argTypes[argTypes.length - 1] = tempArgTypes[0];
} catch (Exception e) {
e.printStackTrace();
}
return argTypes;
} else if (sqlFlag.equals(SQL_DELETE)) {
int[] argTypes = new int[1]; // 长度是1
try {
fields[0].setAccessible(true); // 暴力反射
if (fields[0].get(entity).getClass().getName().equals("java.lang.String")) {
argTypes[0] = Types.VARCHAR;
} else if (fields[0].get(entity).getClass().getName().equals("java.lang.Integer")) {
argTypes[0] = Types.INTEGER;
}
} catch (Exception e) {
e.printStackTrace();
}
return argTypes;
}
return null; }
private List find(int pageNo, int pageSize, Map where, LinkedHashMap orderby) {
// where 与 order by 要写在select * from table 的后面，而不是where rownum<=? ) where rn>=?的后面
StringBuffer sql = new StringBuffer(" SELECT * FROM (SELECT t.*,ROWNUM rn FROM (SELECT * FROM "
+ entityClass.getSimpleName());
if (where != null && where.size() > 0) {
sql.append(" WHERE "); // 注意不是where
for (Map.Entry<String, String> me : where.entrySet()) {
String columnName = me.getKey();
String columnValue = me.getValue();
sql.append(columnName).append(" ").append(columnValue).append(" AND "); // 没有考虑or的情况
}
int endIndex = sql.lastIndexOf("AND");
if (endIndex > 0) {
sql = new StringBuffer(sql.substring(0, endIndex));
}
}
if (orderby != null && orderby.size() > 0) {
sql.append(" ORDER BY ");
for (Map.Entry<String, String> me : orderby.entrySet()) {
String columnName = me.getKey();
String columnValue = me.getValue();
sql.append(columnName).append(" ").append(columnValue).append(",");
}
sql = sql.deleteCharAt(sql.length() - 1);
}
sql.append(" ) t WHERE ROWNUM<=? ) WHERE rn>=? ");
System.out.println("SQL=" + sql);
Object[] args = { pageNo * pageSize, (pageNo - 1) * pageSize + 1 };
RowMapper<T> rowMapper = BeanPropertyRowMapper.newInstance(entityClass);
return jdbcTemplate.query(sql.toString(), args, rowMapper); }
private int count(Map where) {
StringBuffer sql = new StringBuffer(" SELECT COUNT(*) FROM " + entityClass.getSimpleName());
if (where != null && where.size() > 0) {
sql.append(" WHERE ");
for (Map.Entry<String, String> me : where.entrySet()) {
String columnName = me.getKey();
String columnValue = me.getValue();
sql.append(columnName).append(" ").append(columnValue).append(" AND "); // 没有考虑or的情况
}
int endIndex = sql.lastIndexOf("AND");
if (endIndex > 0) {
sql = new StringBuffer(sql.substring(0, endIndex));
}
}
System.out.println("SQL=" + sql);
return jdbcTemplate.queryForInt(sql.toString()); }
}
其他具体的接口只要继承BaseDao就可以了
例如：
Java代码 收藏代码
package org.chendl.springjdbc.dao;
import org.chendl.springjdbc.domain.Product;
public interface ProductDao extends BaseDao {
}
而具体的实现类只要集成BaseDaoImpl，并实现自己的接口就可以了。
例如：
Java代码 收藏代码
package org.monday.springjdbc.dao.impl;
import org.monday.springjdbc.dao.ProductDao;
import org.monday.springjdbc.domain.Product;
public class ProductDaoImpl extends BaseDaoImpl implements ProductDao{
}
补上分页的工具类：
Java代码 收藏代码
package org.monday.springjdbc.util;
import java.util.List;
public class QueryResult {
private List<T> list; // 结果集
private int totalRow; // 总记录数
public QueryResult() {
}
public QueryResult(List<T> list, int totalRow) {
this.list = list;
this.totalRow = totalRow;
}
//getter and setter
}
补上生成主键的工具类：
Java代码 收藏代码
package org.monday.springjdbc.util;
import java.util.UUID;
public final class ToolUtil {
/**
* 生成32位UUID 并去掉"-"
*/
public static String getUUID() {
return UUID.randomUUID().toString().replaceAll("-", "");
}
public static void main(String[] args) {
System.out.println(ToolUtil.getUUID());
System.out.println(ToolUtil.getUUID().length());// 32
}
}
Spring的配置文件Beans.xml
Xml代码 收藏代码
<beans xmlns=“http://www.springframework.org/schema/beans”
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"
xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.1.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.1.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop-3.1.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx-3.1.xsd">
<!-- 加载属性文件 -->
<context:property-placeholder location="classpath:jdbc.properties"/>
<!-- 配置数据源 -->
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="${jdbc.driverClassName}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</bean>
<!-- 配置jdbcTemplate -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"/>
</bean>
<!-- 配置Dao -->
<bean id="baseDao" class="org.monday.springjdbc.dao.impl.BaseDaoImpl" abstract="true">
<property name="jdbcTemplate" ref="jdbcTemplate"/>
</bean>
<bean id="productDao" class="org.monday.springjdbc.dao.impl.ProductDaoImpl" parent="baseDao"/>
<!-- 配置事务 -->
<bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource"/>
</bean>
<tx:advice id="txAdvice" transaction-manager="txManager">
<tx:attributes>
<tx:method name="*"/>
<tx:method name="find*" read-only="true"/>
</tx:attributes>
</tx:advice>
<aop:config>
<aop:pointcut id="daoMethod" expression="execution(* org.monday.springjdbc.dao.impl.*Impl*.*(..))" />
<aop:advisor advice-ref="txAdvice" pointcut-ref="daoMethod"/>
</aop:config>
测试：
1.测试Spring的数据源
Java代码 收藏代码
package junit.test;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = { “/beans.xml” })
public class SpringTest {
// 测试是否取得数据库连接
@Test
public void testDataSource() throws SQLException {
ApplicationContext ctx = new ClassPathXmlApplicationContext("beans.xml");
DataSource dataSource = ctx.getBean(DataSource.class);
System.out.println(dataSource.getConnection());
}
}
2.测试Dao
Java代码 收藏代码
package junit.test;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.Map;
import org.monday.springjdbc.dao.ProductDao;
import org.monday.springjdbc.domain.Product;
import org.monday.springjdbc.util.QueryResult;
import org.monday.springjdbc.util.ToolUtil;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = { “/beans.xml” })
public class BaseDaoTest {
@Autowired
private ProductDao dao;
@Test
public void testSave() throws Exception {
Product product = new Product();
product.setId(ToolUtil.getUUID());
product.setName("aaa");
product.setAuthor("bbb");
product.setPrice(111);
product.setQuantity(9);
product.setDescription("ccc");
dao.save(product);
}
@Test
public void testUpdate() throws Exception {
Product product = new Product();
product.setId("79934cfd71b84cc6819d3c06b2984f80");
product.setName("a");
product.setAuthor("b");
product.setPrice(444);
product.setQuantity(44);
product.setDescription("c");
dao.update(product);
}
@Test
public void testDelete1() {
Product product = new Product();
product.setId("79934cfd71b84cc6819d3c06b2984f80");
dao.delete(product);
}
@Test
public void testDelete2() {
dao.delete("7030aaf8a19d4d30b26e6e2588c43c30");
}
@Test
public void testDeleteAll() {
dao.deleteAll();
}
// 插入一些测试数据
@Test
public void insertTestData() {
for (int i = 1; i <= 100; i++) {
Product product = new Product();
product.setId(ToolUtil.getUUID());
product.setName("springJdbc" + i);
product.setAuthor("monday" + i);
product.setPrice((double) Math.random() * 100);
product.setQuantity((int) (Math.random() * 100));
product.setDescription("介绍SpringJdbc" + i);
dao.save(product);
}
}
// 未完成
@Test
public void testBatchSave() {
}
// 未完成
@Test
public void testBatchUpdate() {
}
// 未完成
@Test
public void testBatchDelete() {
}
@Test
public void testFindById() {
System.out.println(dao.findById("07b5999dcb9346b3b353df18de345c31"));
}
@Test
public void testFindAll() {
System.out.println(dao.findAll());
}
// 分页
@Test
public void testFindByPage1() {
int pageNo = 1;
int pageSize = 10;
QueryResult<Product> queryResult = dao.findByPage(pageNo, pageSize);
for (Product p : queryResult.getList()) {
System.out.println(p.getAuthor());
}
}
// 分页+条件
@Test
public void testFindByPage2() {
int pageNo = 1;
int pageSize = 10;
Map<String, String> where = new HashMap<String, String>();
where.put("author", "like ‘%monday1%‘");
where.put("price", "<90");
QueryResult<Product> queryResult = dao.findByPage(pageNo, pageSize, where);
for (Product p : queryResult.getList()) {
System.out.println(p.getAuthor());
}
}
// 分页+排序
@Test
public void testFindByPage3() {
int pageNo = 1;
int pageSize = 10;
LinkedHashMap<String, String> orderby = new LinkedHashMap<String, String>();
orderby.put("price", "desc");
orderby.put("author", "asc");
QueryResult<Product> queryResult = dao.findByPage(pageNo, pageSize, orderby);
for (Product p : queryResult.getList()) {
System.out.println(p.getAuthor());
}
}
// 分页+条件+排序
@Test
public void testFindByPage4() {
int pageNo = 1;
int pageSize = 10;
Map<String, String> where = new HashMap<String, String>();
where.put("author", "like ‘%monday1%‘");
where.put("price", "<90");
LinkedHashMap<String, String> orderby = new LinkedHashMap<String, String>();
orderby.put("price", "desc");
orderby.put("author", "asc");
QueryResult<Product> queryResult = dao.findByPage(pageNo, pageSize, where, orderby);
for (Product p : queryResult.getList()) {
System.out.println(p.getAuthor());
}
}
}
一些说明：
这些代码的应用有些局限性，没有使用Hibernate方便。 可能还有些Bug自己没有测试出来。这里只是提供一些思路或者只是为了学习而用。之所以有这么想法是觉得单纯的使用JDBC确实有点那个（你懂的...），而使HibernateTemplate可以实现类似的Dao，那用SpringJdbc能吗？自己就试了试。就个人觉得SpringJdbc 是十分好用的。有一次写批量的时候，SpringJdbc提供的batchUpdate()方法比Hibernate的快好多。所以，就自己看来，持久层可以Hibernate+SpringJdbc搭配使用，其中批量建议使用SpringJdbc处理。（可能自己还没想到Hiberante批量性能提高的方法）。而这里基于SpringJdbc的泛型的批量方法，还没想出来。基于HibernateTemplate的倒是可以，但是性能...
这里只是自己一点学习心得仅供参考。
基于SpringJdbc的泛型Dao,布布扣,bubuko.com
原文：http://my.oschina.net/moziqi/blog/293016