day-3-4-2

大对象Lob字段的处理

Lob实际上有2种类型，分别是用于存储大文本的字段Clob和存储二进制大量数据的字段Blob。不同的数 据库处理方式略有区别，大字段一般都是采用流的方式进行处理

由于大字段类型存储的数据较多，一般针对lob字段会采用分表存储的方式实现

需求：存储学生信息，要求存储学生的照片

• 将学生照片存储在文件系统中，实际数据中只存储照片的路径
• 如果图片要求加密，存储在文件系统中则不合理
• 可以利用DBMS提供的压缩加密功能将数据存储在数据表

如果具体存储?

• 一个表中存储相关的基本信息，非大字段数据
• 一个表中存储大字段数据

Clob

Clob在MySQL种的数据类型

• tinytext最大存放255的字符
• text最大存放65535个字符
• mediumtext最大存放16M的字符
• longtext最大存放4G的字符

表的创建

-- 学生的基本信息
create table tb_student(
id bigint primary key auto_increment,
name varchar(20)
);
-- 学生表对应的大字段数据
create table tb_memo(
id bigint -- 具体实现中将其定义为外键，并且支持级联删除
memo longtext
);
-- 为了测试方便，所以将两个表合在一起
create table tb_student(
id bigint primary key auto_increment,
name varchar(20),
memo longtext
);

使用JDBC向表中插入数据

try (
	Connection conn = DriverManager.getConnection("jdbc:mysql:///test?
serverTimezone=UTC", "root", "123456");
	Reader r = new BufferedReader(new FileReader("memo.txt"));) {
	PreparedStatement ps = conn.prepareStatement("insert into tb_student
values(null,?,?)");
	ps.setString(1, "张毅");
	ps.setClob(2, r);
	int len = ps.executeUpdate();
	if (len > 0)
		System.out.println("插入成功!");
	else
		System.out.println("插入失败!");
}

使用JDBC从表中读取大对象类型字段

PreparedStatement ps = conn.prepareStatement("select * from tb_student where
id=?");
ps.setLong(1, 1);
ResultSet rs = ps.executeQuery();
	if (rs.next()) {
	Long id = rs.getLong("id");
	String name = rs.getString("name");
	Clob clob = rs.getClob("memo");
	Reader r = clob.getCharacterStream();
	System.out.println(id+"\t"+name);
	char[] buffer=new char[8192];
	int len=0;
	while((len=r.read(buffer))>0){
		System.out.print(new String(buffer,0,len));
	}
	r.close();
}

Blob

Blob是二进制大字段类型，一般用于存储图形、声音、视频等二进制序列数据。MySQL针对二进制大对 象提供了4种数据类型

• tinyblob上限255
• blob上限65535
• mediumblob上限16M
• longblob上限4G

表定义：针对大对象类型的字段必须分表存储

alter table tb_student add image longblob

使用JDBC插入二进制大对象数据

InputStream is = new BufferedInputStream(new
FileInputStream("pic/xiaopang.jpg"));
PreparedStatement ps=conn.prepareStatement("update tb_student set image=?
where id=?");
ps.setBlob(1, is);
ps.setLong(2, 1);
int len=ps.executeUpdate()

使用JDBC获取二进制大对象数据

PreparedStatement ps = conn.prepareStatement("select * from tb_student where
id=?");
ps.setLong(1, 1);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
	Long id = rs.getLong("id");
	String name = rs.getString("name");
	String memo = rs.getString("memo");
	System.out.println(id + "\t" + name + "\t" + memo);
	Blob image = rs.getBlob("image");
	InputStream is = image.getBinaryStream();
	OutputStream os = new FileOutputStream("c:\\" + name + ".jpg");
	byte[] buffer = new byte[8192];
	int len = 0;
	while((len=is.read(buffer))>0)
		os.write(buffer,0,len);
	os.close();
	is.close();
}

操作方法补充

JDBC 指 Java 数据库连接，是一种标准Java应用编程接口JAVA API，用来连接 Java 编程语言和广泛的数 据库

create table tb_student(
	id bigint primary key auto_increment comment '学生编号',
	name varchar(32) not null
)engine=innodb default charset utf8mb4;

获取自增长键值

通过JDBC往数据库的表格中添加一条记录，其中有一个字段是自增的 id bigint primary key auto_increment ，那么在JDBC这边怎么在添加之后直接获取到这个自增的值。

PreparedStatement是Statement的子接口。Statement接口中有一些常量值，如 Statement.RETURN_GENERATED_KEYS

要先添加后获取到自增的key值：

（1）PreparedStatement pst = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);

（2）添加sql执行完成后,通过PreparedStatement的对象调用getGeneratedKeys()方法来获取自增长键 值，遍历结果集 ResultSet rs = pst.getGeneratedKeys();

try (Connection conn = DriverManager.getConnection("jdbc:mysql:///test?
serverTimezone=UTC", "root",
"123456");) {
String sql = "insert into tb_student(name) values(?)";
// 默认不返回生成的key值，如果需要返回自增长的主键值，则需要添加一个常量值作为参数。默认值
NO_GENERATED_KEYS
PreparedStatement ps = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
ps.setString(1, "赵小胖2");
int pos = ps.executeUpdate();
	if (pos > 0) {
	System.out.println("插入成功!");
	//如果插入成功则会返回插入数据对应的主键值
	//从ps中获取到服务器端返回的键值
	ResultSet rs = ps.getGeneratedKeys();//因为这里的key值可能多个，因为insert语句可以同时添加多行，所以用ResultSet封装
//这里因为只添加一条，所以用if判断
	if(rs.next()){
		Object key = rs.getObject(1);
		System.out.println("自增的key值id =" + key);
	}
	rs.close();
} else
	System.out.println("插入失败!");
ps.close();
}

批处理

批处理：批量处理sql。例如1）订单明细表的多条记录的添加。2）批量添加模拟数据 等

不用批处理，和用批处理有什么不同？

• 批处理的效率会好很多

如何进行批处理操作？

ps.addBatch();
int[] all = ps.executeBatch();

注意：如果批量添加时，insert使用values，不要使用value

try (Connection conn = DriverManager.getConnection("jdbc:mysql:///test?
serverTimezone=UTC", "root", "123456");) {
	String sql = "insert into tb_student(name) values(?)";
	PreparedStatement ps = conn.prepareStatement(sql);
	// 一次性插入100条数据
	for (int i = 0; i < 100; i++) {
		ps.setString(1, "张_" + i);
		ps.addBatch(); //添加到批处理一组操作中，攒一块处理
		//如果一次性提交的数据量过大，允许部分提交
		if(i%10==0){
			int[] res=ps.executeBatch();
			System.out.println(i+":"+Arrays.toString(res));
			ps.clearBatch();
		}
	}
	int[] pos = ps.executeBatch(); //执行批处理，返回各个对应处理的结果
	System.out.println(Arrays.toString(pos));
	ps.close();
}

如果批处理优势不明显，则没有必要使用批处理了，因为限制太多了

可滚动结果集

默认情况下ResultSet结果集是单向的，也就是只能通过rs.next方法单向的读取数据；如果在读取数据库 中的记录时不用逐个逐个的读取，而可以根据具体需要跳过几条记录再进行读取，或者从后向前读取。

PreparedStatement prepareStatement(String sql, int resultSetType,int
resultSetConcurrency) throws SQLException;

• 参数1为要指定的sql语句

• 参数2为结果集的类型，可以使用的ResultSet接口中的3个常量值

  • TYPE_SCROLL_INSENSITIVE 可滚动不敏感
  • TYPE_SCROLL_SENSITIVE 可滚动敏感的
  • TYPE_FORWARD_ONLY 单向滚动，只能调用next

• 参数3用于设置结果集的并发性支持

  • CONCUR_READ_ONLY 并发访问时只读。其中concur译作同时发生，即并发，即当并发访问 结果集的时候只能够只读访问，也就是只能读取里面的内容，不能做更改
  • CONCUR_UPDATABLE 访问的同时允许修改

要做到这点就是在创建Statement对象时使用两个属性做参数 Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)

try (Connection conn = DriverManager.getConnection("jdbc:mysql:///test?
serverTimezone=UTC", "root", "123456")) {
	PreparedStatement ps = conn.prepareStatement("select * from tb_student",
ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
	ResultSet rs = ps.executeQuery();
	rs.last();// 将行指针指向到最后一行
	do {
		long id = rs.getLong("id");
		String name = rs.getString("name");
		System.out.println(id + "\t" + name);
	} while (rs.previous());
	rs.close();
	ps.close();
}

获取一个可滚动结果集ResultSet引用rs之后，就可以使用结果集的方法使行指针任意滚动的方法

• rs.last() //使游标指向最后一行记录
• rs.isLast() //判断是否指向最后一行记录
• rs.isAfterLast() //判断是否指向最行一行记录的上面一行，即倒数第二行
• rs.getRow() //返回当前游标所指向的是第几行
• rs.previous() //使游标指向当前所指向的行的上一行
• rs.absolute(int x) // 使游标直接指向第x行