Jade Dungeon

JDBC

基础

加载数据库驱动

方法一（不推荐）：

DriverManager.registerDriver(Driver driver);

方法二：

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

注意：在实际开发中并不推荐采用registerDriver方法注册驱动。原因有二：

查看Driver的源代码可以看到，如果采用此种方式，会导致驱动程序注册两次，也就是在内存中会有两个Driver对象。
程序依赖mysql的api，脱离mysql的jar包，程序将无法编译，将来程序切换底层数据库将会非常麻烦。

推荐方式：Class.forName不会导致驱动对象在内存中重复出现，并且采用此种方式，程序仅仅只需要一个字符串，不需要依赖具体的驱动，使程序的灵活性更高。

建立连接

Connection conn = DriverManager.getConnection(url, user, pass);

Jdbc程序中的Connection，它用于代表数据库的链接。 Connection 是数据库编程中最重要的一个对象，客户端与数据库所有交互都是通过Connection对象完成的，这个对象的常用方法：

createStatement()：创建向数据库发送sql的statement对象。
prepareStatement(sql)：创建向数据库发送预编译sql的PrepareSatement对象。
prepareCall(sql)：创建执行存储过程的callableStatement对象。
setAutoCommit(boolean autoCommit)：设置事务是否自动提交。
commit()：在链接上提交事务。
rollback()：在此链接上回滚事务。

批量处理

只有把rewriteBatchedStatements参数置为true, 驱动才会帮你批量执行SQL：

jdbc:mysql://ip:port/db?rewriteBatchedStatements=true

这样才会在批量执行插入的时候真正执行批量操作。

如果查看PreparedStatment类的源代码，也可以看到相关逻辑：

// 确定打开rewriteBatchedStatements
if (!this.batchHasPlainStatements && 
		this.connection.getRewriteBatchedStatements()) 
{

    if (canRewriteAsMultiValueInsertAtSqlLevel()) { // instert语句有特别处理
        return executeBatchedInserts(batchTimeout);
    }

    if (this.connection.versionMeetsMinimum(4, 1, 0) && 
				!this.batchHasPlainStatements && this.batchedArgs != null
            && this.batchedArgs.size() > 3 /* 大于3条才会批处理 */) 
		{
        return executePreparedBatchAsMultiStatement(batchTimeout);
    }
}

发送SQL的Statement

Statement st = conn.createStatement();

Jdbc程序中的Statement对象用于向数据库发送SQL语句， Statement对象常用方法：

executeQuery(String sql)：用于向数据库发送查询语句。
executeUpdate(String sql)：用于向数据库发送insert、update或delete语句
execute(String sql)：用于向数据库发送任意sql语句
addBatch(String sql)：把多条sql语句放到一个批处理中。
executeBatch()：向数据库发送一批sql语句执行。
clearBatch()：清空此 Statement 对象的当前 SQL 命令列表。

PreparedStatement

PreperedStatement是Statement的子类，它的实例对象可以通过调用 Connection.preparedStatement()方法获得，相对于Statement对象而言的优势：

防止SQL注入：PreperedStatement可以避免SQL注入的问题。
预编译SQL语句：Statement会使数据库频繁编译SQL，可能造成数据库缓冲区溢出。PreparedStatement 可对SQL进行预编译，从而提高数据库的执行效率。
使用占位符简化语句：并且PreperedStatement对于sql中的参数，允许使用占位符的形式进行替换，简化sql语句的编写。

Statement st = conn.prepareStatement(sql);

从结果集中获取数据

ResultSet rs = st.excuteQuery(sql);
while(rs.next()) {
	System.out.println("id=" + rs.getObject("id"));
	System.out.println("name=" + rs.getObject("name"));
	System.out.println("password=" + rs.getObject("password"));
	System.out.println("email=" + rs.getObject("email"));
	System.out.println("birthday=" + rs.getObject("birthday"));
}

ResultSet提供了对结果集进行滚动的方法：

next()：移动到下一行
Previous()：移动到前一行
absolute(int row)：移动到指定行
beforeFirst()：移动resultSet的最前面。
afterLast()：移动到resultSet的最后面。

ResultSet既然用于封装执行结果的，所以该对象提供了用于获取数据的get方法：

SQL类型	JDBC类型	返回类型
BIT(1)	getBoolean()	Boolean byte[]
BIT(10)	getBytes()	Boolean byte[]
TINYINT	getByte()	Byte
SMALLINT	getSort()	Sort
INT	getInt()	Int
BIGINT	getLong()	Long
CHAR VARCHAR LONGVARCHAR	getString()	String
TEXT(CLOB)	getClob()	Clob
BLOG	getBlog()	Blob
DATE	getDate()	java.sql.Date
TIME	getTime()	java.sql.Time
TIMESTAMP	getTimestamp()	java.sql.Timestamp

释放资源

rs.close();
st.close();
conn.close();

CRUD操作

insert

int num = st.executeUpdate("insert into user(….) values(…..) ");
if (num>0) System.out.println("插入成功！！！");

MySQL自增主键

st = conn.prepareStatement(
		"insert into test(name) values('aaa')", 
		Statement.RETURN_GENERATED_KEYS);
st.executeUpdate();
rs = st.getGeneratedKeys();

if (rs.next()) System.out.println(rs.getInt(1));

=== update ===

{{{class="brush:java"
int num = st.executeUpdate("update user set name='' where name=''"); 
if (num>0) System.out.println("修改成功！！！");

delete

int num = st.executeUpdate("delete from user where id=1"); 
if (num>0) System.out.println("删除成功！！！");

select

ResultSet rs = st.excuteQuery("select * from user where id=1");
while (rs.next()) { /* .... */ }

分页

MySQL分页

MySQL分页的实现语句：

Select * from table limit idx, lines

idx：记录开始索引位置
lines：取多少条记录。

步骤：

先获得需分页显示的记录总数，然后在web页面中显示页码。
根据页码，从数据库中查询相应的记录显示在web页面中。

以上两项操作通常使用Page对象进行封装。

Oracle分页

Oracle分页的实现语句：

select * from (
	select rownum r_, row_.* from (
		select * from student order by id
	) row_ where rownum <= 5              -- 起始索引位置
) where r _>= 1                         -- 结束索引位置。

批量处理

Statement和PreparedStatment都可以批量执行SQL。

不同参数类型的语句：

st = conn.createStatement();

st.addBatch("insert into user(name,password,email,birthday) " + 
	"values('kkk','123','abc@sina.com','1978-08-08')");
st.addBatch("update user set password='123456' where id=3");

st.executeBatch();  // 执行批处理
st.clearBatch();    // 执行完后清空SQL语句，否则会造成内存溢出

同类型的SQL可以用PreparedStatment：

st = conn.prepareStatement("insert into " + 
		"user(name,password,email,birthday)" + 
		" values(?,?,?,?)");

for (int i=0; i < 50000; i++){
	st.setString(1, "aaa" + i);
	st.setString(2, "123" + i);
	st.setString(3, "aaa" + i + "@sina.com");
	st.setDate(4,new Date(1980, 10, 10));

	if (i % 1000 == 0) {
		st.executeBatch();  // 执行批处理
		st.clearBatch();    // 执行完后清空SQL语句，否则会造成内存溢出
	}
}
// 如果不是1000的整数还有余下来的
st.executeBatch();  // 执行批处理
st.clearBatch();    // 执行完后清空SQL语句，否则会造成内存溢出

存储过程

以MySQL为例：

CREATE PROCEDURE demoSp (
		IN inputParam VARCHAR(255), INOUT inOutParam varchar(255) )

BEGIN
	SELECT CONCAT ( 'ZYXW---- ', inputParam ) into  inOutParam ;
END

st = conn.prepareCall("{call demoSp(?,?)}");
st.setString(1, "aaaaa");
st.registerOutParameter(2, Types.VARCHAR);
st.execute();

System.out.println(st.getString(2));

BLOB和CLOB

MySQL大对象处理

保存CLOB：

File file = new File("src/1.txt");
FileReader reader = new FileReader(file);

PreparedStatement st = conn.prepareStatement(
		"insert into testclob(id,resume) values(?,?)");
st.setString(1, "1");
st.setCharacterStream(2, reader, (int) file.length()); // 长度须设置为int型
int num = st.executeUpdate();

if (num > 0) System.out.println("插入成功！！");

读取CLOB：

st = conn.prepareStatement(
		"select id,resume from testclob where id='1'");
rs = st.executeQuery();
if (rs.next()){
	Reader reader = rs.getCharacterStream("resume");
	FileWriter writer = new FileWriter("c:\\1.txt");
	try {
		int len = 0;
		char buffer[] = new char[1024];
		while ((len = reader.read(buffer)) > 0) writer.write(buffer, 0, len);
	} finally {
		if (reader != null) reader.close();
		if (writer != null) writer.close();
	}
}

保存BLOB：

File file = new File("src/1.jpg");
FileInputStream in = new FileInputStream(file);

st = conn.prepareStatement("insert into testblob(id,image) values(?,?)");
st.setString(1, "1");
st.setBinaryStream(2, in, (int) file.length());
int num = st.executeUpdate();

if (num > 0) System.out.println("插入成功！！");

读取BLOB：

st = conn.prepareStatement(
		"select id,image from testblob where id='1'");
rs = st.executeQuery();
if (rs.next()){
	try {
		InputStream in = rs.getBinaryStream("image");
		OutputStream out = new FileOutputStream("c:\\1.jpg");;
		int len = 0;
		byte buffer[] = new byte[1024];
		while ((len = in.read(buffer)) > 0) out.write(buffer, 0, len);
	} finally {
		if (in  != null) in.close();
		if (out != null) out.close();
	}
}

Oracle大对象处理

Oracle定义了一个BLOB字段用于保存二进制数据，但这个字段并不能存放真正的二进制数据，只能向这个字段存一个指针，然后把数据放到指针所指向的Oracle的LOB段中，LOB段是在数据库内部表的一部分。

因而在操作Oracle的Blob之前，必须获得指针（定位器）才能进行Blob数据的读取和写入。

如何获得表中的Blob指针呢？可以先使用insert语句向表中插入一个空的blob （调用oracle的函数empty_blob()），这将创建一个blob的指针，然后再把这个empty的blob的指针查询出来，这样就可得到BLOB对象，从而读写blob数据了。

事务

隔离级别

set   transaction isolation level  -- 设置事务隔离级别

select @@tx_isolation              -- 查询当前事务隔离级别

Read uncommitted：最低级别，以上情况均无法保证。(读未提交)
Read committed：可避免脏读情况发生（读已提交）。
Repeatable read：可避免脏读、不可重复读情况的发生。（可重复读）
Serializable：可避免脏读、不可重复读、虚读情况的发生。（串行化，近似于单线程操作。）

脏读

脏读：指一个事务读取了另外一个事务未提交的数据。【针对同一个业务操作】

这是非常危险的，假设Ａ向Ｂ转帐货款100元，对应sql语句如下所示

update account set money=money+100 while name='b';  
update account set money=money-100 while name='a';

当第1条sql执行完，第2条还没执行(A未提交时)，如果此时Ｂ查询自己的帐户，就会发现自己多了100元钱。如果A等B走后再回滚，B就会损失100元。

演示脏读发生。

a窗口

set transaction isolation level read uncommitted;

start transaction;

select * from account; 
-- 发现a帐户是1000元，转到b窗口

b窗口

start transaction;

update account set money=money+100 where name='aaa';
-- 事务在不提交的情况下，转到a窗口进行查询

a窗口

select * from account;
-- 发现a帐户是1100元，发生了脏读（这个事务读取到了别的事务未提交的数据）

不可重复读

不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同。【针对同一行数据】

不可重复读和脏读的区别：脏读是读取前一事务未提交的脏数据，不可重复读是重新读取了前一事务已提交的数据。

例如银行想查询A帐户余额，第一次查询A帐户为200元，此时A向帐户存了100元并提交了，银行接着又进行了一次查询，此时A帐户为300元了。银行两次查询不一致，可能就会很困惑，不知道哪次查询是准的。

很多人认为这种情况就对了，无须困惑，当然是后面的为准。我们可以考虑这样一种情况，比如银行程序需要将查询结果分别输出到电脑屏幕和写到文件中，结果在一个事务中针对输出的目的地，进行的两次查询不一致，导致文件和屏幕中的结果不一致，银行工作人员就不知道以哪个为准了。

避免脏读，并演示不可重复读问题的发生。

a窗口

set transaction isolation level read committed;
start transaction;

select * from account;
-- 发现a帐户是1000元，转到b窗口

b窗口

start transaction;
update account set money=money+100 where name='aaa';
-- 事务在不提交的情况下，转到a窗口进行查询

a窗口

select * from account;
-- 发现a帐户是1000元，这时我们发现read committed这种级别可以避免脏读
-- 转到b窗口

b窗口

commit;
-- 转到a窗口

a窗口

select * from account;
-- 发现a帐户是1100元，这时就发生不可重复读（指这个事务读取到了别的事务提交的数据）

虚读(幻读)

虚读(幻读) ：是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。【针对整张表】

如丙存款100元未提交，这时银行做报表统计account表中所有用户的总额为500元，然后丙提交了，这时银行再统计发现帐户为600元了，造成虚读同样会使银行不知所措，到底以哪个为准。

演示虚读问题的发生

set transaction isolation level repeatable read;
start transaction;

select * from account;
-- 发现a帐户是1000元，并且表的总纪录数是3条,这时转到b窗口

b窗口

start transaction;

update account set money=money+100 where name='aaa';
-- 转到a窗口

a窗口

select * from account
-- 发现a帐户是1000元，这说明repeatable read这种级别可避免脏读
-- 转到b窗口

b窗口

commit;
-- 转到a窗口

a窗口

select * from account
-- 发现a帐户是1000元，这说明repeatable read这种级别还可以避免不可重复读
-- 转到b窗口

b窗口

start transaction;

insert into account(name,money) values('ddd','1000');
commit;
-- 转到a窗口

a窗口

select * from account
-- 发现表中可能会多出一条ddd的记录，这就发生了虚读，
-- 也就是在这个事务内读取了别的事务插入的数据（幻影数据）

事务操作

Jdbc程序向数据库获得一个Connection对象时，默认情况下这个Connection对象会自动向数据库提交在它上面发送的SQL语句。

JDBC控制事务语句

Connection.setAutoCommit(false)
Connection.rollback()
Connection.commit()

设置事务回滚点

Savepoint sp = conn.setSavepoint()
Conn.rollback(sp)会滚到指定的位置，该位置之前的操作任然有效执行，因为需要被提交
Conn.commit()回滚后必须要提交

Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;

Savepoint sp = null;

try {
	conn = DriverManager.getConnection(url, user, pass);
	conn.setAutoCommit(false);          //start transaction;

	st = conn.prepareStatement(
			"update account set money=money-100 where name='aaa'");
	st.executeUpdate();
	sp = conn.setSavepoint();

	st = conn.prepareStatement(
			"update account set money=money+100 where name='bbb'");
	st.executeUpdate();
	int x = 1/0;

	st = conn.prepareStatement(
			"update account set money=money+100 where name='ccc'");
	st.executeUpdate();

	conn.commit();
} catch (Exception e) {
	e.printStackTrace();
	conn.rollback(sp);
	conn.commit();  //手动回滚后，一定要记得提交事务
} finally {
	rs.close();
	st.close();
	conn.close();
}

事务隔离级别例子：

Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;

Savepoint sp = null;

try {
	conn = DriverManager.getConnection(url, user, pass);
	conn.setAutoCommit(false);
	// 设置事务级别
	conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);

	st = conn.prepareStatement("select * from account");
	
	// ......

	conn.commit();
} finally {
	rs.close();
	st.close();
	conn.close();
}

常用模式

常用的事务管理模式：

跨层传递方法参数——在Service层创建开启事务的连接，并传递到Dao层，最后在Service层提交事务；
ThreadLocal 绑定连接——使用ThreadLocal进行事务管理——ThreadLocal可以实现在线程范围内实现数据共享。
使用Spring进行事务管理；（其实Spring内部也是用的ThreadLocal）

例子：使用ThreadLocal把连接和事务绑定到当前线程

	public class JdbcUtils {
		private static DataSource ds;

		// 为保证各层的类所使用的ThreadLocal是同一个，建议将其设定成静态的，
		// 但是一定要记得使用后要移出绑定在上面的对象。
		private static ThreadLocal<Connection> tl = 
			new ThreadLocal<Connection>();  // 其实就是一个Map集合

		static{
			try{
				Properties prop = new Properties();
				InputStream in = JdbcUtils.class.getClassLoader()
					.getResourceAsStream("dbcpconfig.properties");
				prop.load(in);

				BasicDataSourceFactory factory = new BasicDataSourceFactory();
				ds = factory.createDataSource(prop);
			} catch (Exception e) {
				throw new ExceptionInInitializerError(e);
			}
		}

		public static DataSource getDataSource() { return ds; } 

		// 备注：该获取连接的方法，仅当使用ThreadLocal来管理事务连接的情况，
		// 因为向静态对象ThreadLocal中绑定了对象，
		// 所以当我们不需要管理事务的普通获取连接的方法，就不要用此方法。
		// 应该用普通的获取连接的方法。
		public static Connection getConnection() throws SQLException{
			try{
				//得到当前线程上绑定的连接
				Connection conn = tl.get();
				if(conn==null){  //代表线程上没有绑定连接
					conn = ds.getConnection();
					tl.set(conn);
				}
				return conn;
			} catch (Exception e) { throw new RuntimeException(e); }
		} 

		public static void startTransaction(){
			try{
				//得到当前线程上绑定连接开启事务
				Connection conn = tl.get();
				if(conn==null){  //代表线程上没有绑定连接
					conn = ds.getConnection();
					tl.set(conn);
				}
				conn.setAutoCommit(false);
			} catch (Exception e) { throw new RuntimeException(e); }
		} 

		public static void commitTransaction(){
			try{
				Connection conn = tl.get();
				if(conn!=null) { conn.commit(); }
			} catch (Exception e) {
				throw new RuntimeException(e);
			}
		}

		public static void closeConnection(){
			try{
				Connection conn = tl.get();
				if(conn!=null){ conn.close(); }
			}catch (Exception e) { throw new RuntimeException(e);
			}finally{
				//千万注意，解除当前线程上绑定的链接
				//（从threadlocal容器中移除对应当前线程的链接）
				tl.remove();
			}
		}

	}

采用 ThreadLocal 绑定连接来管理事务的 Service层的代码。

public class BusinessService {  

	//用上ThreadLocal的事务管理
	public void transfer2(int sourceid,int targetid,double money) 
		throws SQLException
	{
		try{
			JdbcUtils.startTransaction();
			AccountDao dao = new AccountDao();
			Account a = dao.find(sourceid);   //select
			Account b = dao.find(targetid);   //select
			a.setMoney(a.getMoney()-money);  
			b.setMoney(b.getMoney()+money);   
			dao.update(a); //update
			dao.update(b);//update
			JdbcUtils.commitTransaction();
		} finally {
			JdbcUtils.closeConnection();
		}
	}
}