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

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

CLOB：用于存储大文本

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

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

TEXT  分为：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT

BLOB 分为：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB

取值范围如下图：

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

 1 package com.cream.ice.jdbc; 2  3 import java.io.File; 4 import java.io.FileNotFoundException; 5 import java.io.FileReader; 6 import java.io.FileWriter; 7 import java.io.Reader; 8 import java.io.Writer; 9 import java.sql.Connection;10 import java.sql.PreparedStatement;11 import java.sql.ResultSet;12 import java.sql.SQLException;13 14 import org.junit.Test;15 16 /**17  * 大文本数据操作18  * 19  * 假设数据库中已存在表test: 20  * create table test(21  *    id int primary key,22  *    content longtext23  * );24  * 25  * @author ice26  *27 */28 public class ClobDemo {29   30   Connection connection = null;31   PreparedStatement statement = null;32   ResultSet resultSet=null;33   34   @Test35   public void add(){36     try {37       connection=JdbcUtils.getConnection();38       statement=connection.prepareStatement("insert into test (id,content) values (?,?)");39       statement.setInt(1, 1);40       41       //大文本要使用流的形式。将d:/test.txt内容添加至该记录的content字段42       File file = new File("d:/test.txt");43       Reader reader = new FileReader(file);44       //不能使用long的参数，因为mysql根本支持不到那么大的数据，所以没有实现45       statement.setCharacterStream(2, reader, (int)file.length());46       47       int i = statement.executeUpdate();48       if(i>0)49         System.out.println("插入成功");50       51     } catch (SQLException e) {52       e.printStackTrace();53     } catch (FileNotFoundException e) {54       e.printStackTrace();55     } finally{56       JdbcUtils.releaseResources(null, statement, connection);57     }58   }59   60   @Test61   public void read(){62     try {63       connection = JdbcUtils.getConnection();64       statement = connection.prepareStatement("select * from test where id=?");65       statement.setInt(1, 1);66 67       //将读取内容保存到E盘上68       resultSet = statement.executeQuery();69       while(resultSet.next()){70         Reader reader = resultSet.getCharacterStream("content");71         Writer writer = new FileWriter("e:/test.txt");72         char buffer[] = new char[1024];73         int len = -1;74         while((len=reader.read(buffer))!=-1){75           writer.write(buffer, 0, len);76         }77         reader.close();78         writer.close();79       }80     } catch (Exception e) {81       e.printStackTrace();82     } finally{83       JdbcUtils.releaseResources(resultSet, statement, connection);84     }85   }86 }

 1 package com.cream.ice.jdbc; 2  3 import java.io.FileInputStream; 4 import java.io.FileOutputStream; 5 import java.io.InputStream; 6 import java.io.OutputStream; 7 import java.sql.Connection; 8 import java.sql.PreparedStatement; 9 import java.sql.ResultSet;10 11 import org.junit.Test;12 13 /**14  * 大二进制数据操作15  * 16  * 假设数据库中已存在表test: 17  * create table test(18  *    id int primary key,19  *    content longblob20  * );21  * 22  * @author ice23  *24 */25 public class BlobDemo {26   Connection connection = null;27   PreparedStatement statement = null;28   ResultSet resultSet=null;29   30   @Test31   public void add(){32     try {33       connection=JdbcUtils.getConnection();34       statement=connection.prepareStatement("insert into test (id,content) values (?,?)");35       statement.setInt(1, 1);36       37       InputStream in = new FileInputStream("d:/test.jpg");38       statement.setBinaryStream(2, in, in.available());39       40       int i = statement.executeUpdate();41       if(i>0)42         System.out.println("插入成功");43       44     } catch (Exception e) {45       e.printStackTrace();46     } finally{47       JdbcUtils.releaseResources(null, statement, connection);48     }49   }50   51   @Test52   public void read(){53     try {54       connection = JdbcUtils.getConnection();55       statement = connection.prepareStatement("select * from test where id=?");56       statement.setInt(1, 1);57 58       //保存到E盘上59       resultSet = statement.executeQuery();60       while(resultSet.next()){61         InputStream in = resultSet.getBinaryStream("content");62         OutputStream out = new FileOutputStream("e:/test.jpg");63         byte b[] = new byte[1024];64         int len = -1;65         while((len=in.read(b))!=-1){66           out.write(b, 0, len);67         }68         out.close();69         in.close();70       }71     } catch (Exception e) {72       e.printStackTrace();73     } finally{74       JdbcUtils.releaseResources(resultSet, statement, connection);75     }76   }77 }

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

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

关键词：