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

我和java操作数据库那些事儿（5）
<bean id="dataSource" value="org.sqlite.JDBC" /><property name="url" value="jdbc:sqlite:test.db" /></bean><bean id="jdbcTemplate" ref="dataSource" /></bean><bean id="employeeDao" 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>

查看更多 下一篇