【JDBC】——【大文本、二进制、批处理、获取自动生成主键、事务、事务的隔离级别】

于 2014-05-13 12:31:42 发布
阅读量715 收藏
点赞数
分类专栏： web数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/wanwan_javaweb/article/details/25701767
版权

1、大本文数据类型

statement.setCharacterStream

resultSet.getCharacterStream

package com.xiaozhi.clob.and.blob;

import java.io.File;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.Reader;
import java.net.URL;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import com.xiaozhi.uitls.JdbcUtil;

//大文本数据的存取
/*
	 create database testdatabase;
	 use testdatabase;
	 create table clobtable(
	 id int primary key auto_increment,
	 content MEDIUMTEXT
	 );
 */
public class ClobTest
{
	public static void main(String[] args)
	{
		saveClob();
	}

	public static void saveDisk()
	{
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try{
			connection = JdbcUtil.getConnection();
			String sql = "select content from clobtable where id=?";
			statement = connection.prepareStatement(sql);
			statement.setInt(1, 1);
			resultSet = statement.executeQuery();
			
			if(resultSet.next()){
				Reader reader = resultSet.getCharacterStream("content");
				//写到D:盘上
				FileWriter out = new FileWriter("D:/man.txt");
				char buf[] = new char[1024];
				int len = -1;
				while((len=reader.read(buf))!=-1){
					out.write(buf, 0, len);
				}
				reader.close();
				out.close();
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JdbcUtil.release(connection, statement, resultSet);
		}
	}

	public static void saveClob()
	{
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try
		{
			connection = JdbcUtil.getConnection();
			String sql = "insert into clobtable(content) values(?)";
			statement = connection.prepareStatement(sql);
			
			URL url=ClobTest.class.getClassLoader().getResource("man.txt");
			String filePath=url.getPath();
			File file=new File(filePath); 
			
			statement.setCharacterStream(1,new FileReader(file),(int)file.length());//要使用int类型
			int num=statement.executeUpdate();
			
			if(num>0)
				System.out.println("添加成功！");
		} catch (Exception e)
		{
			throw new RuntimeException(e);
		} finally
		{
			JdbcUtil.release(connection, statement, resultSet);
		}
	}
}

2、二进制数据类型

statement.setBinaryStream

resultSet.getBinaryStream

package com.xiaozhi.clob.and.blob;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.net.URL;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import com.xiaozhi.uitls.JdbcUtil;


/*大二进制数据的存取
	use testdatabase;
	create table blobtable(
	id int primary key auto_increment,
	content MEDIUMBLOB
	);
 */

public class BlobTest
{
	public static void main(String[] args)
	{
		saveBlob();
	}

	public static void saveDisk()
	{
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try{
			connection = JdbcUtil.getConnection();
			String sql = "select content from blobtable where id=?";
			statement = connection.prepareStatement(sql);
			statement.setInt(1, 1);
			resultSet=statement.executeQuery();
			
			while(resultSet.next()){
				InputStream in = resultSet.getBinaryStream("content");
				//写到D:盘上
				FileOutputStream out = new FileOutputStream("D:/1.jpg");
				byte buf[] = new byte[1024];
				int len = -1;
				while((len=in.read(buf))!=-1){
					out.write(buf, 0, len);
				}
				in.close();
				out.close();
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JdbcUtil.release(connection, statement, resultSet);
		}
	}

	public static void saveBlob()
	{
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try
		{
			connection = JdbcUtil.getConnection();
			String sql = "insert into blobtable(content) values(?)";
			statement = connection.prepareStatement(sql);
			
			URL url=ClobTest.class.getClassLoader().getResource("1.jpg");
			String filePath=url.getPath();
			File file=new File(filePath); 
			
			statement.setBinaryStream(1,new FileInputStream(file),(int)file.length());//要使用int类型
			int num=statement.executeUpdate();
			
			if(num>0)
				System.out.println("添加成功！");
		} catch (Exception e)
		{
			throw new RuntimeException(e);
		} finally
		{
			JdbcUtil.release(connection, statement, resultSet);
		}
	}
}


3、批处理

statement.addBatch(sql1);

statement.executeBatch();

statement.clearBatch();

package com.xiaozhi.clob.and.blob;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import com.xiaozhi.uitls.JdbcUtil;


/*批处理
	use testdatabase;
	create table batchtable(
	name varchar(20)
	);
 */
public class BatchTest
{

	public static void main(String[] args)
	{
		test3();
	}
	
	private static void test3(){
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		long time = System.currentTimeMillis();
		try{
			connection = JdbcUtil.getConnection();
			String sql = "insert into batchtable (name) values(?)";
			statement = connection.prepareStatement(sql);
			
			for(int i=1;i<=1000009;i++){
				statement.setString(1, "a"+i);
				statement.addBatch();
				if(i%1000==0){
					statement.executeBatch();//list
					statement.clearBatch();//清空
				}
			}
			statement.executeBatch();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JdbcUtil.release(connection, statement, resultSet);
		}
		System.out.println("用时："+(System.currentTimeMillis()-time)/1000+"秒");
	}
	
	private static void test2(){
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try{
			connection = JdbcUtil.getConnection();
			String sql = "insert into batchtable (name) values(?)";
			statement = connection.prepareStatement(sql);

			for(int i=1;i<=1000;i++){
				statement.setString(1, "a"+i);
				statement.addBatch();
			}
			statement.executeBatch();
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JdbcUtil.release(connection, statement, resultSet);
		}
	}
	
	//利用Statement批量发送处理语句：多条
	private static void test1(){
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		try{
			connection = JdbcUtil.getConnection();
			statement = connection.createStatement();

			String sql1 = "insert into batchtable(name) values('a')";
			String sql2 = "insert into batchtable(name) values('b')";
			String sql3 = "delete from batchtable where name='a'";

			statement.addBatch(sql1);//List
			statement.addBatch(sql2);
			statement.addBatch(sql3);
			
			statement.executeBatch();//执行批处理

		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JdbcUtil.release(connection, statement, resultSet);
		}
	}
}

采用Statement.addBatch(sql)方式实现批处理：
优点：可以向数据库发送多条不同的ＳＱＬ语句。
缺点：
SQL语句没有预编译。
当向数据库发送多条语句相同，但仅参数不同的SQL语句时，需重复写上很多条SQL语句。例如：
Insert into user(name,password) values(‘aa’,’111’);
Insert into user(name,password) values(‘bb’,’222’);
Insert into user(name,password) values(‘cc’,’333’);
Insert into user(name,password) values(‘dd’,’444’);


采用PreparedStatement.addBatch()实现批处理
优点：发送的是预编译后的SQL语句，执行效率高。
缺点：只能应用在SQL语句相同，但参数不同的批处理中。因此此种形式的批处理经常用于在同一个表中批量插入数据，或批量更新表的数据。


4、获取插入新纪录的主键

statement.getGeneratedKeys();

package com.xiaozhi.clob.and.blob;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import com.xiaozhi.uitls.JdbcUtil;

/*
 获取插入的记录的主键：INSERT时才有效。要求数据库能自动生成主键
	use testdatabase;
	create table autokeytable(
	id int primary key auto_increment,
	name varchar(20)
	);
 */
public class GenAutoKeyTest {

	public static void main(String[] args) {
		ResultSet resultSet = null;
		Connection connection = null;
		PreparedStatement statement = null;
		try{
			connection = JdbcUtil.getConnection();
			String sql = "insert into autokeytable (name) values(?)";
			statement = connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);//默认行为
			statement.setString(1, "admin");
			statement.executeUpdate();
			//获得插入的新纪录的主键
			resultSet = statement.getGeneratedKeys();
			if(resultSet.next())
				System.out.println(resultSet.getObject(1));
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JdbcUtil.release(connection, statement, resultSet);
		}
	}

}

5、事务

Connection.setAutoCommit(false);  //start transaction
Connection.rollback();  // rollback
Connection.commit();   //commit

package com.xiaozhi.clob.and.blob;

/*
 *  use testdatabase;
	create table account(
	id int primary key auto_increment,
	name varchar(40),
	money float
	)character set utf8 collate utf8_general_ci;
	
	insert into account(name,money) values('aaa',1000);
	insert into account(name,money) values('bbb',1000);
	insert into account(name,money) values('ccc',1000); 

 */
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Savepoint;

import com.xiaozhi.uitls.JdbcUtil;

public class AccountDemo1 {
	public static void main(String[] args) {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try{
			connection = JdbcUtil.getConnection();
			//开启事务
			connection.setAutoCommit(false);
			
			
			String sql1 = "update account set money=money+100 where name='aaa'";
			statement = connection.prepareStatement(sql1);
			statement.executeUpdate();
			

			int i = 1/0;
			
			
			String sql2 = "update account set money=money-100 where name='bbb'";
			statement = connection.prepareStatement(sql2);
			statement.executeUpdate();
			
			
			//关闭事务
			connection.commit();
			
		}catch(Exception e){
			try {
				//回滚
				connection.rollback();
				//关闭事务
				connection.commit();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}finally{
			JdbcUtil.release(connection, statement, resultSet);
		}
	}

}
设置回滚点 

package com.xiaozhi.clob.and.blob;

/*
 *  use testdatabase;
	create table account(
	id int primary key auto_increment,
	name varchar(40),
	money float
	)character set utf8 collate utf8_general_ci;
	
	insert into account(name,money) values('aaa',1000);
	insert into account(name,money) values('bbb',1000);
	insert into account(name,money) values('ccc',1000); 

 */
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Savepoint;

import com.xiaozhi.uitls.JdbcUtil;

public class AccountDemo1 {
	public static void main(String[] args) {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		Savepoint sp = null;
		try{
			connection = JdbcUtil.getConnection();
			//开启事务
			connection.setAutoCommit(false);
			
			//设置回滚点
			sp = connection.setSavepoint();
			
			String sql1 = "update account set money=money+100 where name='aaa'";
			statement = connection.prepareStatement(sql1);
			statement.executeUpdate();
			
			int i = 1/0;
			
			
			String sql2 = "update account set money=money-100 where name='bbb'";
			statement = connection.prepareStatement(sql2);
			statement.executeUpdate();
			
			//关闭事务
			connection.commit();
			
		}catch(Exception e){
			try {
				//回滚到回滚点
				connection.rollback(sp);
				//关闭事务
				connection.commit();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}finally{
			JdbcUtil.release(connection, statement, resultSet);
		}
	}

}

6、事务的隔离级别

1）事务特性


2）出现的问题：

脏读：回滚之前看到的
重复读：事务未提交之前，有人给你打钱，你第一次查询是这个钱，第二次查询是另一个钱。
幻读:银行统计用户数，在这个过程中，有一个人注册为用户。


3）数据库共定义了四种隔离级别：

Serializable：可避免脏读、不可重复读、虚读情况的发生。（串行化）
Repeatable read：可避免脏读、不可重复读情况的发生。（可重复读）
Read committed：可避免脏读情况发生（读已提交）。
Read uncommitted：最低级别，以上情况均无法保证。(读未提交)
set   transaction isolation level 设置事务隔离级别
select @@tx_isolation 查询当前事务隔离级别


4)JDBC设置隔离级别:

//设置隔离级别
			conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);

wanwan_javaweb
关注
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值