首先是建一张名为LOBTEST的测试表 字段 ID(NUMBER)、UNAME(VARCHAR2)、JIANJIE(CLOB)、TOUXIANG(BLOB)
1、JDBC的操作方法
(1)往数据库存储CLOB和BLOB型字段数据。数据来源文本文件和图片文件,演示代码如下:
- import java.io.*;
- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.PreparedStatement;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- public class InsertLob {
- /**
- * @param args
- * @throws IOException
- */
- public static void main(String[] args) throws IOException {
- try {
- long startTime=System.currentTimeMillis();
- Class.forName("oracle.jdbc.driver.OracleDriver");
- String url="jdbc:oracle:thin:@192.168.130.131:1521:ICSS";
- String user="lixiang";
- String password="19870924";
- Connection conn=DriverManager.getConnection(url,user,password);
- conn.setAutoCommit(false);//这句很重要
- //1、插入一条LOB字段都是空值的记录
- String inSql="insert into LIXIANG.LOBTEST(ID,UNAME,JIANJIE,TOUXIANG)values(?,?,?,?)";
- PreparedStatement preStm=conn.prepareStatement(inSql);
- preStm.setInt(1, 1);
- preStm.setString(2, "奶茶妹妹");
- preStm.setClob(3, oracle.sql.CLOB.getEmptyCLOB());//引入oracle的ojdbc6.jar驱动包后可用
- preStm.setBlob(4, oracle.sql.BLOB.getEmptyBLOB());//先往表里LOB字段插入空值,关于这样的做法许多别的文章有讲解原因。
- preStm.executeUpdate();
- preStm.close();
- //2、将该条记录再取出,然后对LOB字段传入你要保存的数据
- String outSql="select JIANJIE,TOUXIANG from LIXIANG.LOBTEST where ID=? for update";
- preStm=conn.prepareStatement(outSql);
- preStm.setInt(1, 1);
- ResultSet rst=preStm.executeQuery();
- rst.next();
- oracle.sql.CLOB clob=(oracle.sql.CLOB)rst.getClob(1);
- oracle.sql.BLOB blob=(oracle.sql.BLOB)rst.getBlob(2);
- BufferedInputStream bis=new BufferedInputStream(new FileInputStream("D:\\naicha.jpeg"));//D盘根目录下有个名为naicha.jpeg的图片
- BufferedOutputStream bos=new BufferedOutputStream(blob.setBinaryStream(0));
- byte[] buf=new byte[10240];//10K字节的缓存 以字节流的方式传输
- int len;
- while((len=bis.read(buf))!=-1){
- bos.write(buf);
- }//关于JAVA输入输出流的东西 http://muzixiang.iteye.com/blog/912451
- bis.close();
- bos.close();
- BufferedReader br=new BufferedReader(new FileReader("D:\\test.txt"));//D盘根目录下有个名为test.txt的文本文件
- BufferedWriter bw=new BufferedWriter(clob.setCharacterStream(0));
- char[] cbuf=new char[1024];//1K的字符缓存 以字符流的方式传输
- while((len=br.read(cbuf))!=-1){
- bw.write(cbuf);
- }//关于JAVA输入输出流的东西 http://muzixiang.iteye.com/blog/912451
- br.close();
- bw.close();
- rst.close();
- preStm.close();
- //3、将Blob Clob字段更新到数据库 这步必不可少!
- String upSql="update LIXIANG.LOBTEST set JIANJIE=?,TOUXIANG=? where ID=?";
- preStm=conn.prepareStatement(upSql);
- preStm.setClob(1, clob);
- preStm.setBlob(2, blob);
- preStm.setInt(3, 1);
- preStm.executeUpdate();
- preStm.close();
- conn.commit();//前面设置conn.setAutoCommit(false)所以这里勿忘手动提交!
- conn.close();
- long endTime=System.currentTimeMillis();
- long totalTime=endTime-startTime;
- System.out.println("程序总共运行了"+totalTime+"毫秒");
- } catch (ClassNotFoundException e) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- } catch (SQLException e) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- }
- }
- }
D:\test.txt的内容如下 保存到数据库后打开表我们发现CLOB字段保存文本中的格式,也是按照两行来存储的!
(2)从数据库中读出CLOB和BLOB型数据。将数据读取到文本文件和图片文件,演示代码如下:
- import java.io.BufferedInputStream;
- import java.io.BufferedOutputStream;
- import java.io.BufferedReader;
- import java.io.BufferedWriter;
- import java.io.File;
- import java.io.FileOutputStream;
- import java.io.FileWriter;
- import java.io.IOException;
- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.PreparedStatement;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- public class getLob {
- /**
- * @param args
- * @throws IOException
- */
- public static void main(String[] args) throws IOException {
- try {
- long startTime=System.currentTimeMillis();
- Class.forName("oracle.jdbc.driver.OracleDriver");
- String url="jdbc:oracle:thin:@192.168.130.131:1521:ICSS";
- String user="lixiang";
- String password="19870924";
- Connection conn=DriverManager.getConnection(url,user,password);
- conn.setAutoCommit(false);//这句很重要
- String sql="select JIANJIE,TOUXIANG from LIXIANG.LOBTEST where id=?";
- PreparedStatement preStm=conn.prepareStatement(sql);
- preStm.setInt(1, 1);
- ResultSet rst=preStm.executeQuery();
- rst.next();
- oracle.sql.CLOB clob=(oracle.sql.CLOB)rst.getClob("JIANJIE");//有的程序里用的是java.sql.Clob类和java.sql.Blob类不需要强制转换也实现目的了。。
- oracle.sql.BLOB blob=(oracle.sql.BLOB)rst.getBlob("TOUXIANG");
- BufferedReader br=new BufferedReader(clob.getCharacterStream());
- BufferedWriter bw=new BufferedWriter(new FileWriter(new File("D:\\Clob.txt")));
- BufferedInputStream bis=new BufferedInputStream(blob.getBinaryStream());
- BufferedOutputStream bos=new BufferedOutputStream(new FileOutputStream(new File("D:\\Blob.jpeg")));
- char[] cbuf=new char[1024];
- int len;
- while((len=br.read(cbuf))!=-1){
- bw.write(cbuf);
- bw.flush();//这句非常重要,一开始就是因为没有加这句导致没有成功将数据库内容输出到文本文件里
- }
- byte[] buff=new byte[10240];
- while((len=bis.read(buff))!=-1){
- bos.write(buff);
- bos.flush();
- }
- rst.close();
- preStm.close();
- conn.commit();
- conn.close();
- long endTime=System.currentTimeMillis();
- long totalTime=endTime-startTime;
- System.out.println("程序总共运行了"+totalTime+"毫秒");
- } catch (ClassNotFoundException e) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- } catch (SQLException e) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- }
- }
- }