jdbc基础 (三) 大文本、二进制数据处理

LOB (Large Objects)   分为：CLOB和BLOB，即大文本和大二进制数据

CLOB：用于存储大文本

BLOB：用于存储二进制数据，例如图像、声音、二进制文件

在mysql中,只有BLOB,没有CLOB，mysql存储大文本用TEXT

TEXT  分为：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT

BLOB 分为：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB

取值范围如下图：

下面来看具体的代码实现：

 package com.cream.ice.jdbc;

 import java.io.File;

 import java.io.FileNotFoundException;

 import java.io.FileReader;

 import java.io.FileWriter;

 import java.io.Reader;

 import java.io.Writer;

 import java.sql.Connection;

 import java.sql.PreparedStatement;

 import java.sql.ResultSet;

 import java.sql.SQLException;

 import org.junit.Test;

 /**

  * 大文本数据操作

  *

  * 假设数据库中已存在表test:

  * create table test(

  *        id int primary key,

  *        content longtext

  * );

  *

  * @author ice

  *

  */

 public class ClobDemo {

     Connection connection = null;

     PreparedStatement statement = null;

     ResultSet resultSet=null;

     @Test

     public void add(){

         try {

             connection=JdbcUtils.getConnection();

             statement=connection.prepareStatement("insert into test (id,content) values (?,?)");

             statement.setInt(1, 1);

             //大文本要使用流的形式。将d:/test.txt内容添加至该记录的content字段

             File file = new File("d:/test.txt");

             Reader reader = new FileReader(file);

             //不能使用long的参数，因为mysql根本支持不到那么大的数据，所以没有实现

             statement.setCharacterStream(2, reader, (int)file.length());

             int i = statement.executeUpdate();

             if(i>0)

                 System.out.println("插入成功");

         } catch (SQLException e) {

             e.printStackTrace();

         } catch (FileNotFoundException e) {

             e.printStackTrace();

         } finally{

             JdbcUtils.releaseResources(null, statement, connection);

         }

     }

     @Test

     public void read(){

         try {

             connection = JdbcUtils.getConnection();

             statement = connection.prepareStatement("select * from test where id=?");

             statement.setInt(1, 1);

             //将读取内容保存到E盘上

             resultSet = statement.executeQuery();

             while(resultSet.next()){

                 Reader reader = resultSet.getCharacterStream("content");

                 Writer writer = new FileWriter("e:/test.txt");

                 char buffer[] = new char[1024];

                 int len = -1;

                 while((len=reader.read(buffer))!=-1){

                     writer.write(buffer, 0, len);

                 }

                 reader.close();

                 writer.close();

             }

         } catch (Exception e) {

             e.printStackTrace();

         } finally{

             JdbcUtils.releaseResources(resultSet, statement, connection);

         }

     }

 }

 package com.cream.ice.jdbc;

 import java.io.FileInputStream;

 import java.io.FileOutputStream;

 import java.io.InputStream;

 import java.io.OutputStream;

 import java.sql.Connection;

 import java.sql.PreparedStatement;

 import java.sql.ResultSet;

 import org.junit.Test;

 /**

  * 大二进制数据操作

  *

  * 假设数据库中已存在表test:

  * create table test(

  *        id int primary key,

  *        content longblob

  * );

  *

  * @author ice

  *

  */

 public class BlobDemo {

     Connection connection = null;

     PreparedStatement statement = null;

     ResultSet resultSet=null;

     @Test

     public void add(){

         try {

             connection=JdbcUtils.getConnection();

             statement=connection.prepareStatement("insert into test (id,content) values (?,?)");

             statement.setInt(1, 1);

             InputStream in = new FileInputStream("d:/test.jpg");

             statement.setBinaryStream(2, in, in.available());

             int i = statement.executeUpdate();

             if(i>0)

                 System.out.println("插入成功");

         } catch (Exception e) {

             e.printStackTrace();

         } finally{

             JdbcUtils.releaseResources(null, statement, connection);

         }

     }

     @Test

     public void read(){

         try {

             connection = JdbcUtils.getConnection();

             statement = connection.prepareStatement("select * from test where id=?");

             statement.setInt(1, 1);

             //保存到E盘上

             resultSet = statement.executeQuery();

             while(resultSet.next()){

                 InputStream in = resultSet.getBinaryStream("content");

                 OutputStream out = new FileOutputStream("e:/test.jpg");

                 byte b[] = new byte[1024];

                 int len = -1;

                 while((len=in.read(b))!=-1){

                     out.write(b, 0, len);

                 }

                 out.close();

                 in.close();

             }

         } catch (Exception e) {

             e.printStackTrace();

         } finally{

             JdbcUtils.releaseResources(resultSet, statement, connection);

         }

     }

 }

这里使用了我上一篇jdbc基础中的JdbcUtils工具类，同时也使用了单元测试来测试两个成员方法，代码已亲测可运行。

csdn博文地址：jdbc基础 (三)  大文本、二进制数据处理