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 longtext
23 * );
24 *
25 * @author ice
26 *
27 */
28 public class ClobDemo {
29
30 Connection connection = null;
31 PreparedStatement statement = null;
32 ResultSet resultSet=null;
33
34 @Test
35 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 @Test
61 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 longblob
20 * );
21 *
22 * @author ice
23 *
24 */
25 public class BlobDemo {
26 Connection connection = null;
27 PreparedStatement statement = null;
28 ResultSet resultSet=null;
29
30 @Test
31 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 @Test
52 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工具类,同时也使用了单元测试来测试两个成员方法,代码已亲测可运行。