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

« 李开复：谷歌07份额将戏剧性增长欲开成功先例 | 首页 | 网站登陆、网站提交入口 »

想起来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.springframework.org/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("tester@gmail.com");

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("tester33@gmail.com");

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;

}

}