基于SpringJdbc的泛型Dao

首页 > 代码库 > 基于SpringJdbc的泛型Dao

2024-07-13 00:10:21 221人阅读

使用的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 = http://www.mamicode.com/me.getValue();  > }
 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 = http://www.mamicode.com/me.getValue();  > }

}

其他具体的接口只要继承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=http://www.mamicode.com/"${jdbc.driverClassName}"/>  > 
 
测试： 
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的倒是可以，但是性能... 
这里只是自己一点学习心得仅供参考。

声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉：投诉/举报工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。

联系
我们

首页 > 代码库 > 基于SpringJdbc的泛型Dao

基于SpringJdbc的泛型Dao

看完仍有疑问？有类似问题直接问程序猿