基于SpringJdbc的泛型Dao
使用的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 void batchSave(List list) {
}
/**
- 未完成
*/
@Override
public void batchUpdate(List list) {
}
/**
- 未完成
*/
@Override
public void batchDelete(List list) {
}
@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的倒是可以,但是性能...
这里只是自己一点学习心得仅供参考。
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。