MySQL大对象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 向表中插入数据

//JDK1.8 支持 JDBC4.2 ，所以只要驱动支持，加载驱动的操作可以省略

/*

目前可以使用的驱动有 2 种，一个 com.mysql.jdbc.Driver 是针对 MySQL8 以前的老版本的驱动，官方已经不再建议使用；如果使用 MySQL5.7+ 的数据库则建议使用新版本的驱动 com.mysql.cj.jdbc.Driver

*/

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();
        }