Mysql学习-----大对象Lob，操作方法补充

最新推荐文章于 2023-03-14 16:45:40 发布

大佬的幼生期

最新推荐文章于 2023-03-14 16:45:40 发布

阅读量478

点赞数

文章标签：学习 mysql 数据库

本文链接：https://blog.csdn.net/weixin_64771061/article/details/127291428

版权

大对象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 ();

}

操作方法补充

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 ){

                  nt [] 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行

跨数据库平台分页显示

真正在数据显示时，不能一次性加载所有数据，建议采用的是按需加载

public class App2 {

      public static void main ( String [] args ) {

            // 显示第 5 页数据，每页 10 条数据

            int pageNum = 5 ;

            int rowsPerPage = 10 ;

            List < Student > res = new ArrayList <> ();

            try ( Connection conn = DriverManager . getConnection ( "jdbc:mysql:///test?

serverTimezone=UTC" , "root" , "123456" )) {

                  // 如果考虑跨数据库平台，这里的 sql 应该使用标准 sql

                  String sql = "select * from tb_student" ;

                  PreparedStatement ps = conn . prepareStatement ( sql ,

                  ResultSet . TYPE_SCROLL_INSENSITIVE , ResultSet . CONCUR_READ_ONLY );

                  ResultSet rs = ps . executeQuery ();

                  // 需要判断页码值的合理性 , 页码值需要大于 0 ，小于等于最大页码值

                  // 如果页码值小于 1 ，则为 1 ；如果大于最大页码值则为最大页码值

                  if ( rs . last ()) { // 将行指针移动到最后一行

                        int rowNum = rs . getRow (); // 获取总行数

                        int maxPage = rowNum / rowsPerPage ;

                        if ( rowNum % rowsPerPage != 0 )

                                maxPage ++ ;

                        if ( pageNum < 1 )

                                pageNum = 1 ;

                        if ( pageNum > maxPage )

                                pageNum = maxPage ;

                        int begin = ( pageNum - 1 ) * rowsPerPage + 1 ; // 起始行号

                        rs . absolute ( begin ); // 将行指针移动到指定的行上

                        int count = 0 ;

                        do {

                             Student s = new Student ();

                             s . setId ( rs . getLong ( "id" ));

                             s . setName ( rs . getString ( "name" ));

                             res . add ( s );

                             count ++ ;

                             if ( count >= rowsPerPage )

                                      break ;

                        } while ( rs . next ());

                 }

            } catch ( Exception e ) {

                  e . printStackTrace ();

            }

          for ( Student tmp : res )

                   System . out . println ( tmp );

            }

}

class Student implements Serializable {

       private Long id ;

       private String name ;

       public Long getId () {

         return id ;

       }

       public void setId ( Long id ) {

           this . id = id ;

        }

       public String getName () {

           return name ;

       }

       public void setName ( String name ) {

            this . name = name ;

      }

      @Override

      public String toString () {

              return "Student [id=" + id + ", name=" + name + "]" ;

       }

}

可修改的结果集

由于执行性能的问题，一般不建议使用

// MySQL 驱动支持 JDBC4

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_UPDATABLE );

        ResultSet rs = ps . executeQuery ();

        // 获取第 3 行数据

        rs . absolute ( 3 );

        // 在当前行上执行修改指定列数据的操作

         rs . updateString ( "name" , " 东方星雨 " );

         rs . updateRow (); // 修改修改

         // 插入数据

         rs . moveToInsertRow (); // 将结果集指针移动到插入行上

         rs . updateString ( "name" , " 张三 " );

         rs . insertRow (); // 插入数据

         // 删除当前行数据 , 删除第 5 行数据

         rs . absolute ( 5 );

         rs . deleteRow (); // 删除当前行

         rs . close ();

         ps . close ();

}

特殊占位符

模糊查询， select * from t_user where username like '%a%';

使用 concat('%a%')

select * from t_user where username like concat('%',?,'%')

批量删除 String ids = "1,2,3"; delete from t_user where id in('1,2,3');

方法一：用 in 并且字符串拼接。 String ids = "1,2,3"; ，执行sql String

sql="delete from t_user where id in(" + ids + ")" ;

方法二：用 or ，并且用 1 = 1 或 1 != 1

在 where 的条件判断中，可以使用 1 = 1 更好的拼接 and 条件，使用 1 != 1 更好的拼接

or 条件

呈现结果如： delete from t_user where 1!=1 or id = 1 or id = 2 or id = 3

获取数据表的元数据

ResultSetMetaData 对象保存了所有 ResultSet 对象中关于字段的信息，提供了对应的方法获取字段相关的信息

int getColumnCount()获取ResultSet对象中的字段个数
String getColumnName(int index)获取ResultSet对象中指定序号对应字段的名称

获取表的元数据信息

ResultSet rs = ps . executeQuery ();

ResultSetMetaData rsmd = rs . getMetaData ();

int len = rsmd . getColumnCount (); // 獲取列數

for ( int i = 1 ; i <= len ; i ++ ){

        String columnName = rsmd . getColumnName ( i ); // 獲取列名稱

        String typeName = rsmd . getColumnTypeName ( i ); // 獲取列的類型名稱

        System . out . println ( columnName + "\t" + typeName );

}