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

来源：互联网发布：氟硝西泮片淘宝店编辑：程序博客网时间：2024/05/01 09:49

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工具类，同时也使用了单元测试来测试两个成员方法，代码已亲测可运行。

鄙人博客园原博地址：jdbc基础 (三) 大文本、二进制数据处理

1 0