??? 在上一篇《我和JAVA数据库操作的那些事儿（1） 》中，采用纯JDBC编程我经历过的一些问题，主要有：

• 连接重复创建
• 资源需要注意释放
• sql和代码在一起
• 重复性的创建Statement, ResultSet代码多

?

??? 根据以前的做法，基本上都有一些解决方法

• 把连接放在一个对象里，并不每次都重复创建
• 用try{}finally{}调用写的工具类方法来关闭资源并释放
• 把sql移到配置文件中，并通过PreparedStatement来setString, setInt等方法来给sql传参
• 写一些函数来封装重复的代码

??? 上次已经写过了部门表的增删除改查并进行了对上面几个问题的解决，如下：

package cn.lettoo.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class DepartmentDB {

	// 增
	public void addDepartment(Connection conn, Department dept)
	        throws SQLException {
		PreparedStatement stmt = null;
		try {
			// SqlParser读取sql.xml文件
			String sql = SqlParser.getInstance().getSql("Department.insert");
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, dept.getId());
			stmt.setString(2, dept.getName());
			stmt.setString(3, dept.getDescription());
			stmt.execute();
		} finally {
			DBUtil.close(stmt);
			DBUtil.close(conn);
		}
	}

	// 删
	public void deleteDepartment(Connection conn, Department dept)
	        throws SQLException {
		PreparedStatement stmt = null;
		try {
			String sql = SqlParser.getInstance().getSql("Department.delete");
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, dept.getId());
			stmt.execute();
		} finally {
			DBUtil.close(stmt);
			DBUtil.close(conn);
		}
	}

	// 改
	public void updateDepartment(Connection conn, Department dept)
	        throws SQLException {
		PreparedStatement stmt = null;
		try {
			String sql = SqlParser.getInstance().getSql("Department.update");
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, dept.getName());
			stmt.setString(2, dept.getDescription());
			stmt.setInt(3, dept.getId());
			stmt.execute();
		} finally {
			DBUtil.close(stmt);
			DBUtil.close(conn);
		}
	}

	// 查
	public List<Department> selectDepartment(Connection conn, int id)
	        throws SQLException {
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String sql = SqlParser.getInstance().getSql("Department.select");
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, id);
			rs = stmt.executeQuery();
			List<Department> deptList = new ArrayList<Department>();

			while (rs.next()) {
				Department dept = new Department();
				dept.setId(rs.getInt("ID"));
				dept.setName(rs.getString("NAME"));
				dept.setDescription(rs.getString("DESCRIPTION"));
				deptList.add(dept);
			}

			return deptList;
		} finally {
			DBUtil.close(rs);
			DBUtil.close(stmt);
			DBUtil.close(conn);
		}
	}
}

? ? sql语句放在sql.xml里，定义如下：

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<!DOCTYPE sql_template>
<sqltemplate xmlns="cn.lettoo">
	<sql name="Department.insert">
		<content><![CDATA[
		INSERT INTO DEPARTMENT(ID, NAME, DESCRIPTION) VALUES (?, ?, ?)
		]]></content>
	</sql>

	<sql name="Department.delete">
		<content><![CDATA[
		DELETE FROM DEPARTMENT WHERE ID = ?
		]]></content>
	</sql>

	<sql name="Department.update">
		<content><![CDATA[
		UPDATE DEPARTMENT SET NAME = ?, DESCRIPTION = ? WHERE ID = ?
		]]></content>
	</sql>

	<sql name="Department.select">
		<content><![CDATA[
		SELECT ID, NAME, DESCRIPTION FROM DEPARTMENT WHERE ID = ?
		]]></content>
	</sql>

</sqltemplate>

?

?

??? 时间一长，基本上就顺手了，也不觉得有什么。接下来，我们还需要实现

• 批处理提交

? ? 接下来，我们看看批处理。假如我有很多的Employee需要提交到数据库，如果一条条的提交，性能肯定是比较差的，这时就需要批处理。