使用Spring JDBC包装基本的CRUD操作

使用Spring JDBC包装基本的CRUD操作

想起来Spring的JDBC还不错，就拿起来研究了一下，顺便也写了个小例子，希望对初学者有点用处

主要注意以下几点：
1. 对于一些特定的小项目中，很多的工作量都在增删改上，而这些代码当然是惊人地相似，所以对5个简单的
方法抽象了一下，以达到共用的目的
2. 主要文件：applicationContext.xml, BaseDAO.java, BaseDAOImp.java, DAORowMapper.java
3. 在BaseDAOImp.java里有一个Main方法可以做测试
4. DB中只要一个表，表名：users, 三个列：id(int,主键)，name(varchar), email(varchar)

这只是一个初稿，后面再做修改再与大家分享

applicationContext.xml

代码

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www./dtd/spring-beans.dtd">

<beans>
    <bean id="userDAO" class="com.xxxxx.dao.BaseDAOImp" singleton="false">
        <property name="jdbcTemplate">
            <ref bean="jdbcTemplate" />
        </property>
        <property name="tableName" value="users"></property>
        <property name="objectClass" value="com.xxxxx.admin.vo.UserVO"></property>
        <property name="keyColumn" value="id"></property>
    </bean>

    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <constructor-arg>
            <ref bean="datasource" />
        </constructor-arg>
    </bean>
    <bean id="datasource"

class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName"
value="com.microsoft.sqlserver.jdbc.SQLServerDriver" />
        <property name="url" value="jdbc:sqlserver://localhost;databaseName=testdb" />
        <property name="username" value="sa" />
        <property name="password" value="admin" />
    </bean>
</beans>

BaseDAO.java

代码

package com.xxxxx.dao;

import java.util.List;

public interface BaseDAO {
    public int insert(Object obj);
    public int update(Object obj);
    public int delete(Object id);
    public Object findByID(Object id);
    public List findAll();
}

BaseDAOImp.java

代码

package com.xxxxx.dao;

import java.util.List;

import org.springframework***ans.factory.xml.XmlBeanFactory;
import org.springframework.core.io.ClassPathResource;
import org.springframework.jdbc.core.JdbcTemplate;

import com.xxxxx.admin.vo.UserVO;
import com.xxxxx.common.utils.SQLUtils;

public class BaseDAOImp implements BaseDAO {
    private JdbcTemplate jdbcTemplate;
    private String tableName;
    private Class objectClass;
    private String keyColumn;

    public void setKeyColumn(String keyColumn) {
        this.keyColumn = keyColumn;
    }

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public void setObjectClass(Class objectClass) {
        this.objectClass = objectClass;
    }

    public void setTableName(String tableName) {
        this.tableName = tableName;
    }

    public int delete(Object id) {
        String sql = "delete from " + tableName + " where "+keyColumn+"=?";
        return jdbcTemplate.update(sql, new Object[]{id});
    }

    public List findAll() {
        String sql = "select * from " + tableName;
        return jdbcTemplate.query(sql, new DAORowMapper(objectClass));
    }

    public Object findByID(Object id) {
        String sql = "select * from " + tableName + " where " + keyColumn + "=?";
        return jdbcTemplate.queryForObject(sql, new Object[]{id}, new DAORowMapper

(objectClass));
    }

    /*
     * 这里用的Database的主键自增策略，如果想自己实现可以去看看Hibernate的主键生成方法
     * 其实简单一点可以在server起来的时候定义一个存放主键的Map,然后让这些主键值递增就可以了

，
     * 只是要注意一下并发问题
     */
    public int insert(Object obj) {
        Object[] sqlAndParams = SQLUtils.generateInsert(obj, tableName);
        return jdbcTemplate.update((String)sqlAndParams[0], (Object[])sqlAndParams[1]);
    }

    public int update(Object obj) {
        Object[] sqlAndParams = SQLUtils.generateUpdate(obj, tableName, keyColumn);
        return jdbcTemplate.update((String)sqlAndParams[0], (Object[])sqlAndParams[1]);
    }

    public static void main(String[] args){
        // init factory:
        XmlBeanFactory factory = new XmlBeanFactory(new ClassPathResource

("applicationContext.xml"));

        BaseDAO userDAO = (BaseDAO)factory.getBean("userDAO");
        // find all
        List list = userDAO.findAll();
        System.out.println("user count: "+list.size());
        // find by id
        UserVO userVO = (UserVO)userDAO.findByID(new Integer(2));
        System.out.println("Ginger‘s Email: "+userVO.getEmail());
        // insert
        userVO = new UserVO();
        userVO.setName("tester1");
        userVO.setEmail("[e-mail]");
        Object insertResult = userDAO.insert(userVO);
        System.out.println("insert returned: "+insertResult);
        //update
        userVO = new UserVO();
        userVO.setId(new Integer(3));
        userVO.setName("tester33");
        userVO.setEmail("[e-mail]");
        Object updateResult = userDAO.update(userVO);
        System.out.println("update returned: "+updateResult);
        //delete
        Object deleteResult = userDAO.delete(new Integer(4));
        System.out.println("delete returned: "+deleteResult);
    }
}

DAORowMapper.java

代码

package com.xxxxx.dao;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.rowset.ResultSetWrappingSqlRowSetMetaData;

import com.xxxxx.common.utils.ReflectionUtils;

public class DAORowMapper implements RowMapper {
    private Class rowObjectClass;

    public DAORowMapper(Class rowObjectClass){
        this.rowObjectClass = rowObjectClass;
    }

    public Object mapRow(ResultSet rs, int index) throws SQLException {
        Object object;
        try {
            object = rowObjectClass.newInstance();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

        ResultSetWrappingSqlRowSetMetaData wapping = new

ResultSetWrappingSqlRowSetMetaData(rs.getMetaData());
        for(int i=1;i<=wapping.getColumnCount();i++){
            String name = wapping.getColumnName(i);
            Object value = rs.getObject(i);
            ReflectionUtils.setFieldVlaue(object, name, value);
        }

        return object;
    }

}