我和JAVA数据库操作的那些事儿（1）

    不管你的程序员生涯是不是从做数据库应用开始的，反正我是的。

    刚刚使用java那时，除了会写个hello world以外，参加的面试都会问你，会不会java数据库操作？答曰：会！再问：请详细描述。答曰：JDBC。

    后来的工作中写代码，写的最多的就是JDBC的应用，创建connection，创建Statement，创建ResultSet，遍历......周而复始，好像深山中樵夫砍柴，一刀一个印，单调乏味，却乐此不彼。

    言归正传，本文就从自己使用JDBC讲起，对自己在java数据库操作方面做一个小结，温故知新，回忆往昔。

    接下来，考虑到例子的简易性，数据库就使用sqlite 。例子中所有的操作基于以下几个表：

• 部门表
• 员工表
• 技能表
• 员工技能关系表

图1： 表结构定义

建表SQL如下

CREATE TABLE Department (id NUMERIC, name TEXT, description TEXT);

CREATE TABLE Employee (id NUMERIC, name TEXT, departmentid NUMERIC, description TEXT);

CREATE TABLE Skill (id NUMERIC, name TEXT);

CREATE TABLE EmpSkill (empid NUMERIC, skillid NUMERIC);

    其中，Department和employee是一对多的关系，employee和skill表是多对多关系。

    项目需要：

• 部门表、员工表、技能表可以CRUD
• 删除部门，员工表的对应的部门ID设置为空
• 删除员工，员工技能关系表要对应删除
• 删除技能，要检查这项技能有没有被员工使用，如果有，则不允许删除，要先把员工和技能对应关系删除后，再进行删除

    好吧，我承认，一开始我就是直接写代码开始做了，大概是这样子的：

package cn.lettoo.jdbc;

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

public class DepartmentDB {

	public void addDepartment(Connection conn, Department dept)
			throws SQLException {
		Statement stmt = conn.createStatement();
		String sql = "INSERT INTO DEPARTMENT(ID, NAME, DESCRIPTION) VALUES ("
				+ dept.getId() + ", \"" + dept.getName() + "\", \""
				+ dept.getDescription() + "\")";
		stmt.execute(sql);
	}
	
	public void deleteDepartment(Connection conn, Department dept)
			throws SQLException {
		Statement stmt = conn.createStatement();
		String sql = "DELETE FROM DEPARTMENT WHERE ID = " + dept.getId();
		stmt.execute(sql);
	}
	
	public void updateDepartment(Connection conn, Department dept) throws SQLException {
		Statement stmt = conn.createStatement();
		String sql = "UPDATE DEPARTMENT SET NAME = \"" + dept.getName() + "\", DESCRIPTION =\""
				+ dept.getDescription() + "\" WHERE ID = "+ dept.getId();
		stmt.execute(sql);
	}
	
	public List<Department> selectDepartment(Connection conn, String condition) throws SQLException {
		Statement stmt = conn.createStatement();
		String sql = "SELECT ID, NAME, DESCRIPTION FROM DEPARTMENT WHERE " + condition;
		ResultSet rs = stmt.executeQuery(sql);
		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;
	}
}

测试类大概是这样：

package cn.lettoo.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;

public class JdbcTest {

    /**
     * @param args
     * @throws ClassNotFoundException 
     * @throws SQLException 
     */
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        String url = "jdbc:sqlite:test.db";
        Class.forName("org.sqlite.JDBC");
        Connection conn=DriverManager.getConnection(url);
        
        DepartmentDB deptDB = new DepartmentDB();
        Department dept1 = new Department();
        dept1.setId(1);
        dept1.setName("研发部");
        dept1.setDescription("研发部很不错！");
        deptDB.addDepartment(conn, dept1);
        
        Department dept2 = new Department();
        dept2.setId(2);
        dept2.setName("商务部");
        dept2.setDescription("商务部也很不错！");
        deptDB.addDepartment(conn, dept2);
        
        deptDB.deleteDepartment(conn, dept1);
        
        dept2.setName("新商务部");
        dept2.setDescription("新商务更好！");
        deptDB.updateDepartment(conn, dept2);
        
        String condition = "id=2";
        List<Department> deptList = deptDB.selectDepartment(conn, condition);
        for (Department dept : deptList) {
        	System.out.println(dept.getId() + dept.getName() + dept.getDescription());
        }
        
    }

}

    测试OK，功能正常，一切都好，回家睡觉了。过了没有几天，就发现很多很多的问题，主要有：

1. Connection基本上每次都是创建新的，导致速度很慢

2. Connection, Statement, ResultSet用完都没有释放，导致资源泄露，内存溢出

3. 重复的代码很多，创建Statement，创建ResultSet，遍历ResultSet，把ResultSet查询出来的数据set到对象，等等等等

4. sql语句在代码里，修改了表结构就要更新sql语句，就要修改源代码，里面双引号、单引号看多了都晕了。

5. 此处可以继续写上更多的问题......

    对于第一个问题，记得当时对连接池没有了解，就使用一个对象里持有一个连接，这个连接在用之前先检查是不是关闭的，如果不是，直接使用，如果是，创建再使用。写了N多检查维护这个连接的代码，包括什么创建这个连接不成功重试的功能等，汗一下~

    第二个问题，解决方案就是在一个util类写上close方法，如：

    public static void close(Statement stmt) {
    	if (stmt != null) {
    		try {
				stmt.close();
			} catch (SQLException e) {
				// Do nothing
			}
    	}
    }

    然后在使用的地方，通过try{}finally{}在finally里调用关闭之类。这一类的代码，目前在某些项目里仍然可见。

    第三个问题，当时解决无非就是用函数包装下重复的代码。此处略。

    第四个问题，解决方法是把所有的sql语句放到一个文本文件里，然后有一个configuration模块预先把文本文件读到内存，在代码里通过参数从configuration提供的接口里获取sql语句。sql语句采用传参的形式，比如这样：

SELECT * FROM DEPARTMENT WHERE ID = ？

    然后使用PreparedStatement.setString(1, xx)这样的方式。当然，这样的代码目前仍然大量使用ing...

    还有很多问题，当时，就是这样忙的不亦乐乎吧。

    今天就写到这里，后面的内容将会包括以下：

1. 连接池的引用
2. 批处理
   
3. 事务的加入
4. 使用apache dbutils来减少代码工作量