这篇文章是我对李刚老师的《疯狂Java讲义》第三版第十三章：MySQL 数据库与JDBC编程的学习总结。适合有一定SQL基础的同学，如果你还不是很了解SQL，建议去: w3school 熟悉一下SQL的概念和基本语法。本文更专注于数据库在Java程序中的实践。

SQL语句基础

语句	内容
查询语句	select
DML(Data Manipulation Language)	insert, update, delete
DDL(Data Definition Language)	create, alter, drop, truncate
DCL(Data Control Language)	grant, revoke

JDBC 常用类

接口／类	解释
Driver Manager	用于管理JDBC驱动
Connection	数据库连接对象
Statement	用于执行SQL语句的工具接口
PreparedStatement	预编译的Statement对象
CallableStatement	用于调用储存过程
ResultSet	结果集对象

JDBC 编程步骤

Step1 加载数据库驱动:

1	- Class.forName("com.mysql.jdbc.Driver");

Step2 通过DriverManager获取数据库连接，获得Connection对象
1
- jdbc:mysql://hostname:port/databasename

Step3 通过Connection对象创建Statement对象

1	- 使用getStatement()、 prepareStatement()、prepareCall()等方法

Step4 使用Statement对象执行SQL语句:

1
2
3

- execute()可执行任何SQL语句。 
- executeUpdate()用于执行DML和DDL语句。在执行DML语句返回受影响行数，执行DDL语句返回0。
- executeQuery()只执行查询语句，返回ResultSet对象。

Step5 操作结果集:

1
2

- 程序可通过 next(), previous(), first(), last(), beforeFirst(), beforeLast(), afterLast(), absolute()等方法移动记录指针。 
- 通过getXXX(column index)获取记录指针指向行、列的值。

Step6 回收数据库资源:

1	- 关闭ResultSet、Statement、Connection等资源。

1. 用Properties类来加载属性文件

1.1 属性文件格式

drv = com.mysql.jdbc.Driver
url = jdbc:mysql://hostname:port/databasename
usr = username
pwd = password

例如下面的配置建立的连接会占用电脑的5555端口，生成一个people_list数据库的Connection对象，我们把这个配置文件命名为mysql.ini并将其放置在项目的根目录中：

drv = com.mysql.jdbc.Driver
url = jdbc:mydql://127.0.0.1:3306/people_list
usr = enzeM
pwd = 1234567

需要注意的是 MySQL 在计算机中的默认端口是3306，除非更改MySQL的默认端口值，否则把配置文件端口设为其他端口会出现MySQL连接异常。

1.2 在Java程序中加载属性文件

private String drv;
private String url;
private String usr;
private String pwd;
void initConnFileds(String fileName) throws Exception {
	Properties p = new Properties();
	p.load(new FileInputStream(fileName));
	drv = p.getProperties("drv");
	url = p.getProperties("url");
	usr = p.getProperties("usr");
	pwd = p.getProperties("pwd");
	//step1: load the database
	Class.forName("mysql.ini")  
}

2. 通过JDBC实现数据库的基本操作

2.1 用Statement和execute()写一个可以执行任何SQL语句的函数

使用情境:

在不清楚SQL语句类型的情况下可以使用。

使用getString()方法可以取得除Blob之外的任意类型列的值。

void executeSQL(String query) throws Exception {
	//step2l get Connection object
	try(Connection conn = DriverManager.getConnection(url, usr, pwd); 
		Statement stmt = conn.createStatement()) {//step3: get Statement object
		//step4: get ResutSet object
		boolean hasResultSet = stmt.execute(query);
		if(hasResultSet) {
			try(ResultSet rs = stmt.getResutSet()) {
				//use metadata object to visit all possible info from tables
				ResultSetMetaData rsmd = rs.getMetaData();
				int colunmCount = rsmd.getColumnCount();
				//print result after execute the query
				while(rs.next()) {
					for(int i=0; i<colunmCount; i++) {
						System.out.print(rs.getString(i+1) + "\t");
					}
					System.out.println();
				}
			}
		} else {
			System.out.println("this query effect "
					+ stmt.getUpdateCount() + " record(s) in the database");
		}
	}
}

上面的代码使用execute()来执行SQL语句返回了一个boolean值，它表明SQL是否返回了ResultSet对象。然后程序用getResutSet() 获取Statement执行查询语句后的RsultSet。使用getUpdateCount()获取Statement DML语句所影响的记录行数。

2.2 用PreparedStatement执行SQL语句

使用情境：

适用于经常需要反复执行的一条语句

使用占位符?代替需要输入的参数

预编译SQL语句，性能更好

防止SQL注入攻击，安全性高

PreparedStatement对象储存了预编译SQL语句，因此可以高效的反复执行该语句。
若清楚表格的参数类型，我们可以使用setXXX(int index, XXX value)的方法传入参数。若不清楚表格的参数类型，我们可以使用setObject()的方法传入参数。接下来，我们在数据库里建一个Employees，然后完成：0）加入新的employee record、2）根据ID找出Employee在Table里记录的讯息。

CREATE TABLE Employees (
	ID INT AUTO_INCREMENT PRIMARY KEY,
	Name VARCHAR(255) NOT NULL,
	Occupation VARCHAR(255),
	Salary INT
)

加入新的employee record:

void insertNewEmployee(String name, String occupation, int salary) throws Exception {
	try(Connection conn = DriverManager.getConnection(url, usr, pwd); 
		PreparedStatement pstmt = conn.prepareStatement("INSERT INTO Employee VALUES(null, ?, ?, ?)")) {
		//pstmt.setXXX(index, value)
		pstmt.setString(1, "Tom");
		pstmt.setString(2, "Java Enginner");
		pstmt.setInt(3, 20000);
		pstmt.executeUpdate();
	}
}

这里的index指的是占位符?的位置，跟数组不同的是，SQL中的index相关的起始位置为1而不是0，比如column也是从0开始的。上面的代码用PreparedStatement的setXXX()方法添加了新的employee record并且在最后使用executeUpdate()更新了Table, 下面的代码实现了根据ID找出Employee在数据库里面的讯息

根据ID找出Employee在Table里记录的讯息:

void findEmployeeBy(int id) throws Exception {
	try(Connection conn = DriverManager.getConnection(url, usr, pwd);
			PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM Employee WHERE ID=?")) {
		pstmt.setInt(1, id); //replace ? to id parameter
		ResultSet rs = pstmt.executeQuery(); //get result set
		//if we know number of column in the table 
		/*
		   if(rs.next()) {
			   for(int i=0; i<4; i++) {
				   System.out.print(rs.getString(i+1)+"\t");
			   }
			   System.out.println();
		   }
		*/
		//if we do not know abut the table, we use metadata
		ResultSetMetaData rsmd = rs.getMetaData();
		int columnCount = rsmd.getColumnCount();
		if(rs.next()) {
			for(int i=0; i<columnCount; i++) {
				System.out.print(rs.getString(i+1)+"\t");
			}
			System.out.println();
		}
	}
}

2.3 用CallableStatement调用储存过程（Procedure）

使用情境：需要调用数据库中定义的procedure来处理数据

例子：建立一个储存过程:

delimiter //
create procedure diff_salary(a int, b int, out diff int)
begin
set diff = abs(a - b)
end;

以上例子在数据库里定义了一个procedure diff_salary 来计算两个input的差值。我们要通过程序调用这个procedure，需要使用CallableStatement，以下是具体步骤：

使用CallableStatement调用procedure:

用Connection对象的prepareCall()方法生成CallableStatement对象
用CallableStatment的setXXX()为procedure传入参数
用rigisterOutParameter()的方法来注册获取储存过程的值
用CallStatement的execute()方法来执行储存过程
用getXXX(int index)获取指定参数的值

void callDiffSalaryProc(int a, int b) throws Exception {
	try(Connection conn = DriverManager.getConnection(url, usr, pwd); 
			CallableStatment cstmt = conn.prepareCall("{call diff_salary(?, ?, ?)}")) { //注意这里使用的格式
		cstmt.setInt(1, a);
		cstmt.setInt(2, b);
		cstmt.registerOutParameter(3, TYPE.INTEGER);
		cstmt.execute();
		System.out.println("result is:"+cstmt.getInt(3));
	}
}

管理结果集(to be continue)

使用情境：

移动ResultSet记录指针取出结果集的内容

通过ResultSet更新记录（record）

在前面的例子中只使用了next()方法移动指针，除此之外absolute()、previous() 、afterLast() 等方法也可以移动指针。
默认的ResultSet是不可更新的，可更新的ResultSet需要在创建Statement或PreparedStatement时传入额外的参数。

resultSetType控制ResultSet的类型，该参数有以下三个值：

ResultSet.TYPE_FORWORD_ONLY：控制记录指针只向前移动。
ResultSet.TYPE_SCROLL_INSENSITIVE：控制记录指针可自由移动，底层数据改变不影响ResultSet内容。
ResultSet.TYPE_SCROLLSENSITIVE：控制记录指针可自由移动，底层数据改变影响Result内容。

resultSetConcurrency：控制ResultSet的并发类型，该参数可接受以下值：

ResultSet.CONCUR_READ_ONLY：指示ResultSet是只读的并发模式（默认）
ResultSet.CONCUR_UPDATABLE：指示ResultSet是可更新的并发模式

下列的例子创建了一个可滚动、可更新的结果集（表格参考上面的Employee）：

void query(String sql) throws Exception {
	try(Connection conn = DriverManager.getConnection(url, usr, pwd); 
		//在创建PreparedStatement时传入控制结果集科滚动、可更新的参数
		PreparedStatement pstmt = conn.prepareStatement(sql
			, ResultSet.TYPE_SCROLL_INSENSITIVE
			, ResultSet.CONCUR_UPDATABLE);
		ResultSet rs = pstmt.executeQuery()) {
		rs.last(); //记录指针指向最后一条记录
		int rowCount = rs.getRow();
		for(int i=rowCount; i>=0; i--) {
			rs.absolute(i) //
			//id, name, occupation, salary
			System.out.println(rs.getString(1) + "\t"
				+ rs.getString(2) + "\t"
				+ rs.getString(3) + "\t"
				+ rs.getString(4));
			if(i == 2) {
				rs.updateString(4, 50000); //把第2条记录的salary值设为5万
				rs.updateRow(); //提交修改
			}
		}
	}
}

处理Blob类型数据

使用情境：处理大文件。

Blob（Binary Long Object）常用于储存大文件如图片或音频文件。

传入数据库：使用PreparedStatement的 setBinaryStream(int parameterIndex, InputStream x) 将制定参数传入二进制输入流，从而将Blob数据保存到数据库
访问数据库：调用ResultSet的getBlob(int column)得到Blob对象，使用Blob对象的 getBinaryStream() 或 getByte() 方法直接取出该Blob对象封装的二进制数据。

上传图片到数据库示例：

建立数据库，MySQL数据库里的Blob类型最多只能储存64KB内容，可能不够满足实际用途，使用MEDIUMBLOB可储存16MB内容。

CREATE TABLE img_table (
IMG_ID INT AUTO_INCREAMENT PRIMARY KEY,
IMG_NAME VARCHAR(255),
IMG_DATA MEDIUMBLOB)

上传图片至数据库函数：

void uploadImage(String fileName) {
	String imageName = fileName.substring(fileName.lastIndexOf('\\') + 1 , fileName.lastIndexOf('.'));
	File f = new File(fileName);
	try(Connection conn = DriverManager.getConnection(url, usr, pwd);
		/／创建执行插入操作的PreparedStatement对象，该对象执行插入操作后返回自动生成的主键
		PreparedStatement pstmt = conn.prepareStatemet("insert into img_table values(null, ?, ?)" 
						, Statement.RETURN_GENERATED_KEYS);
		InputStream is = new FileInputStream(f)) {
		pstmt.setString(1, imageName); //设置图片名参数
		pstmt.setBinaryStream(2, is, (int)f.length()); //设置二进制流参数
		pstmt.executeUpdate();
	} catch(Exception e) {
		e.printStackTrace();
	}
}

访问数据库图片：

ImageIcon getImage(int id) throws Exception {
	try(Connection conn = DriverManager.getConnection(url, usr, pwd); 
		PreparedStatement pstmt = conn.prepareStatemet("select image_id, image_name from image_table where img_id=?")) {
		pstmt.setInt(1, id);
		ResultSet rs = pstmt.executeQuery();
		if(rs.next()) {
			Blob imgBlob = rs.getBlob(1); //取出Blob列
			//取出Blob列里的数据
			ImageIcon icon = new ImageIcon(imgBlob.getBytes(1L, (int)imgBlob.length()) );
			return icon;
		} else {
			//return an empty ImageIcon object
			return new ImageIcon();
		}
	}
}

通过RowSetFactory和RowSetProvider使用RowSet

使用情境：优化数据库性能，保证数据库安全

为什么使用RowSet：

RowSet继承了ResultSet接口,是离线的数据集，毋需与数据库保持连接。
RowSet默认是可滚动、可更新、可序列化的结果集。
RowSet作为JavaBean使用，可方便的在网络上传输。
程序在创建RowSet时以把数据从底层数据库读取到了内存，降低数据服务器的负载，提高程序性能。

Java目前提供了JdbcRowSetImpl作为RowSet的实现类，但不建议使用JdbcRowSetImpl的方法使用RowSet，因为JdbcRowSetImpl是个未被公开的API，未来有被删除的可能性。在Java 7以后允许使用RowSetProvider类和RowSetFactory接口使用RowSet，这样可以：

把应用程序和RowSet实现类分离开
避免使用JdbcRowSetImpl等非公开API
有利于软件后期的升级和拓展

使用RowDetFactory创建JdbcRowSet示例：

private String drv;
private String url;
private String usr;
private String pwd;
//使用properties提取配置文件中的drv、url等讯息，此处省略
void update(String sql) throws Exception {
	Class.forName(driver);
	//使用RowSetProvider创建RowSetFactory
	RowSetFactory factory = RowSetProvider.newFactory();
	//使用RowSetFactory创建默认的JdbcRowSet实例
	try(JdbcRowSet jdbcRs = factory.createJdbcRowSet()) {
		//设置连接讯息 
		jdbcRs.setUrl(url);
		jdbcRs.setUsername(usr);
		jdbcRs.setPassword(pwd);
		//执行查询
		jdbcRs.setCommand(sql);
		jdbcRs.execute();
		//执行相应操作
	}
}

假设应用程序有数据访问层和视图显示层，如果使用ResultSet，对于访问层的查询的结果（ResultSet实例）以下两种常见方式：

使用迭代访问ResultSet里的记录，将记录转成Java Bean，然后将多个Java Bean封装成一个List集合，转换完成后可关闭Connection等资源，然后将Java Bean集合转到视图显示层。（比较安全，但是麻烦）
直接将ResultSet传到视图显示层。（要求当视图显示层显示数据时，底层Connection必须一直处于打开状态，不安全并且影响程序性能）
通过使用离线RowSet可以优雅地解决上面的问题。离线RowSet将底层数据读入内存中，封装层RowSet对象，并且完全可以当成Java Bean来使用，因此安全，并且实现过程简单。CachedRowSet是所有离线RowSet的父接口，下面是具体实现过程：

//创建类名为CachedRowSetTest的文件
//使用properties加载配置文件，此处省略
//生成CachedRowSet的方法
void CachedRowSet generateCachedRowSet(String sql) throws Exception {
	Class.forName(drv);
	Connection conn = DriverManager.getConnection(url, usr, pwd);
	Statement stmt = conn.createStatement();
	ResultSet rs = stmt.executeQuery(sql);
	//1.用RowSetProvider创建RowSetFactory
	RowSetFactory factory = RowSetProvider.newFactory();
	//2.创建CachedRowSet实例
	CachedRowSet crs = factory.createCachedRowSet();
	//3.用ResultSet装填RowSet
	crs.populate(rs);
	//4.关闭资源
	rs.close()
	stmt.close();
	conn.close();
	return crs;
}
public static void main(String[] args) {
	CachedRowSetTest c = new CachedRowSetTest();
	CachedRowSet crs = c.generateCachedRowSet("select * from Employees");//参考前面的Employees Table
	crs.afterLast();
	//向前滚动结果集
i	while(crs.previous()) {
		//id, name, occupation, salary
		System.out.println(crs.getString(1) + "\t"
			+ crs.getString(2) + "\t"
			+ crs.getString(3) + "\t"
			+ crs.getString(4));
		//把ID为10的record Salary更新为50000
		if(crs.getInt("ID") == 10) {
			crs.updateString("Salary", 50000);
			crs.updateRow();
		}
	}
	//重新连接数据库并把对RowSet所做的修改同步到底层数据库
	Connection conn = DriverManager.getConnection(url, usr, pwd);
	conn.setAutoCommit(false);
	crs.accaptChanges(conn);
}

在上面的代码中RowSet调用了populate(ResultSet rs)方法来包装给定的ResultSet再关闭资源。如果程序直接返回ResultSet，这个ResultSet是无法使用的因为底层的Connection关闭后ResultSet将无法使用。所以要把ResultSet封装进CachedRowSet里面再返回CachedRowSet实例。

离线RowSet的查询分页（to be continue)

JDBC 编程学习笔记