我和java操作数据库那些事儿（5）

引用

半自动化武器来了：Spring JdbcTemplate

    经过前面几篇的介绍，对纯JDBC的编程应用应该已经介绍的差不多了，那个感觉就是小米加步枪，虽然实用，但总感觉不爽，现在对现代化的要求越来越高，我们也应该与时俱进了，别急别急，一口吃不了胖子，我们也不要一下子完全自动化了，像Spring提供的JdbcTemplate是个不错的选择。

    先来看看Spring对JdbcTemplate的介绍吧：

引用

The JdbcTemplate class is the central class in the JDBC core package. It simplifies the use of JDBC since it
handles the creation and release of resources. This helps to avoid common errors such as forgetting to always
close the connection. It executes the core JDBC workflow like statement creation and execution, leaving
application code to provide SQL and extract results. This class executes SQL queries, update statements or
stored procedure calls, imitating iteration over ResultSets and extraction of returned parameter values. It also
catches JDBC exceptions and translates them to the generic, more informative, exception hierarchy defined in
the org.springframework.dao package.

    闲话不多说了，直接上代码吧：
1. 先定义相应的bean

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
		destroy-method="close">
		<property name="driverClassName" value="org.sqlite.JDBC" />
		<property name="url" value="jdbc:sqlite:test.db" />
	</bean>
	
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
	    <property name="dataSource" ref="dataSource" />
	</bean>
	
	<bean id="employeeDao" class="cn.lettoo.spring.jdbc.EmployeeDao">
	    <property name="jdbcTemplate" ref="jdbcTemplate" />
	</bean>

2. 写一个IDao的接口，这个接口定义了5个方法：
insert：增加一条记录
delete：删除一条记录，通过记录的id来删除
update：更新一条记录，通过记录的id来更新
select：根据id返回一条记录
selectList：根据condition字符串来返回一批记录

package cn.lettoo.spring.jdbc;

import java.util.List;

public interface IDao<T> {

    int insert(T object);
    
    int delete(T object);
    
    int update(T object);    
       
    T select(Object id);
    
    List<T> selectList(String condition);
    
}

3. 定义一个AbstractDao类，把一些通用的方法，比如set/get JdbcTemplate放进去，这样不需要每个dao子类都去写这些重复的代码了：

package cn.lettoo.spring.jdbc;

import org.springframework.jdbc.core.JdbcTemplate;

public abstract class AbstractDao<T> implements IDao<T> {

    protected JdbcTemplate jdbcTemplate;

    public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

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

}

4. 来看看EmployeeDao的实现吧：

package cn.lettoo.spring.jdbc;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.RowMapper;

import cn.lettoo.jdbc.Employee;
import cn.lettoo.jdbc.Department;
import cn.lettoo.jdbc.SqlParser;

public class EmployeeDao extends AbstractDao<Employee> implements
        IDao<Employee> {

    public int insert(Employee employee) {
        String sql = SqlParser.getInstance().getSql("Employee.insert");

        Object[] args;
        if (employee.getDepartment() != null) {
            args = new Object[] { 
                    employee.getId(),
                    employee.getName(),
                    employee.getDepartment().getId(),
                    employee.getDescription() };
        } else {
            args = new Object[] { 
                    employee.getId(),
                    employee.getName(),
                    java.sql.Types.NULL,
                    employee.getDescription() };
        }
        
        return jdbcTemplate.update(sql, args);
    }

    public int delete(Employee employee) {
        String sql = SqlParser.getInstance().getSql("Employee.delete");

        return jdbcTemplate.update(sql, new Object[] { employee.getId() });
    }

    public int update(Employee employee) {
        String sql = SqlParser.getInstance().getSql("Employee.update");
        Object[] args;
        if (employee.getDepartment() != null) {
            args = new Object[] { 
                    employee.getName(),
                    employee.getDepartment().getId(),
                    employee.getDescription(), 
                    employee.getId() };
        } else {
            args = new Object[] { 
                    employee.getName(), 
                    java.sql.Types.NULL,
                    employee.getDescription(), 
                    employee.getId() };
        }
        
        return jdbcTemplate.update(sql, args);
    }

    public Employee select(Object id) {
        String sql = SqlParser.getInstance().getSql("Employee.select.id");
        return jdbcTemplate.queryForObject(sql, new Object[]{id}, new EmployeeRowMapper());
    }

    public List<Employee> selectList(String condition) {
        String sql = SqlParser.getInstance().getSql("Employee.select.condition") + condition;
        return jdbcTemplate.query(sql, new EmployeeRowMapper());        
    }
    
    private static final class EmployeeRowMapper implements RowMapper {

        public Employee mapRow(ResultSet rs, int rowNum) throws SQLException {
            Employee employee = new Employee();
            
            employee.setId(rs.getInt("ID"));
            employee.setName(rs.getString("NAME"));
            employee.setDescription(rs.getString("DESCRIPTION"));
            
            if (rs.getString("DEPARTMENTID") != null) {
                Department department = new Department();
                department.setId(rs.getInt("DEPARTMENTID"));
                employee.setDepartment(department);
            }
            
            return employee;
        }
        
    }

}

    使用JdbcTemplate.query方法来查询，使用JdbcTemplate.update来做增删改，使用JdbcTemplate.execute的方法来执行sql，这些方法的具体的应用，可以参考Spring的官方文档。

    使用查询的时候，有一个RowMapper接口的实现，这个接口的mapRow()方法负责将ResultSet查询的数据转到Object上去，毕竟我们还是半自化嘛。对于不同的查询sql和对应的Object，我们只要实现一个RowMapper就可以了。

    这里我还是使用老的方法，将sql放在一个文件中，由SqlParser来读取，关于Employee的sql定义如下：

	<sql name="Employee.insert">
		<content><![CDATA[
		INSERT INTO EMPLOYEE(ID, NAME, DEPARTMENTID, DESCRIPTION) VALUES (?, ?, ?, ?)
		]]></content>
	</sql>
	
	<sql name="Employee.delete">
		<content><![CDATA[
		DELETE FROM EMPLOYEE WHERE ID = ?
		]]></content>
	</sql>
	
	<sql name="Employee.update">
		<content><![CDATA[
		UPDATE EMPLOYEE SET NAME=?, DEPARTMENTID=?, DESCRIPTION=? WHERE ID=?
		]]></content>
	</sql>
	
	<sql name="Employee.select.id">
		<content><![CDATA[
		SELECT ID, NAME, DEPARTMENTID, DESCRIPTION FROM EMPLOYEE WHERE ID=?
		]]></content>
	</sql>
	
	<sql name="Employee.select.condition">
		<content><![CDATA[
		SELECT ID, NAME, DEPARTMENTID, DESCRIPTION FROM EMPLOYEE WHERE 1=1 
		]]></content>
	</sql>