JDBC学习总结(四)
JDBC对LOB的读写
在JDBC中提供了java.sql.Blob和java.sql.Clob,两个类分别代表BLOB和CLOB数据。
· BLOB(Binary Large Object):用于存储大量的二进制数据;
· CLOB(Character Large Object):用于存储大量的文本数据。
MySQL中的大容量字段类型:
BLOB、TEXT最大64KB。BLOB是大小敏感的,而TEXT不是大写敏感的。MEDIUMBLOB、MEDIUMTEXT最大16MB。MEBIUMBLOB是大小写敏感的,而MEDIUMTEXT不是大小写敏感的。LONGBLOB、LONGTEXT最大4GB。LONGBLOB是大小写敏感的。
1)创建数据库表stuinfo,有id字段,姓名字段,简介字段,头像字段。头像字段设置为LONGBLOB类型。
create table stuinfo(
id int(11) not null auto_increment,
name varchar(20) default null,
content longText,
image longBlob,
primary key(id)
)ENGINE=InnoDB default charset=utf8 row_format=redundant;
2)LOB写入文本文件和图片文件
注意:过低的mysql.jar版本会导致本案例不能正常运行,因此将mysql.jar包换成/mysql-connector-java-5.1.22-bin.jar
package com.yyq.blob; import com.yyq.factory.CloseFactory; import com.yyq.factory.ConnectionFactory; import java.io.BufferedReader; import java.io.IOException; import java.io.InputStream; import java.io.InputStreamReader; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; /** * Created by gao on 16-4-13. */ public class BlobInsertTest { public static void insert() throws SQLException{ Connection conn = ConnectionFactory.getConnection(); PreparedStatement pstmt = null; String sql = "insert into stuinfo(name,content,image) values(?,?,?)"; BufferedReader brtxt = null; InputStream isimg = null; try { pstmt = conn.prepareStatement(sql); pstmt.setString(1,"test"); // 从文件中获取输入流--读取文本 InputStream istxt = Thread.currentThread().getContextClassLoader() .getResourceAsStream("test.txt"); brtxt = new BufferedReader(new InputStreamReader(istxt)); //设置Blob pstmt.setCharacterStream(2,brtxt); //从文件中获取输入流--读取图片 isimg = Thread.currentThread().getContextClassLoader() .getResourceAsStream("panda.png"); pstmt.setBinaryStream(3,isimg); if (pstmt.executeUpdate() == 1){ System.out.println("恭喜成功添加一条记录!"); }else{ System.out.println("添加记录失败!"); } }catch (SQLException e){ e.printStackTrace(); }finally { try { brtxt.close(); }catch (IOException e){ e.printStackTrace(); } try{ isimg.close(); }catch (IOException e){ e.printStackTrace(); } CloseFactory.close(pstmt,conn); } } public static void main(String[] args) throws SQLException { BlobInsertTest.insert(); } }
3)LOB的读取
package com.yyq.blob; import com.yyq.factory.CloseFactory; import com.yyq.factory.ConnectionFactory; import java.io.*; import java.net.ConnectException; import java.sql.*; /** * Created by gao on 16-4-13. */ public class BlobSelectTest { public static void select() { Connection conn = ConnectionFactory.getConnection(); PreparedStatement pstmt = null; String sql = "select id,name,content,image from stuinfo where name=?"; ResultSet rs = null; BufferedReader br = null; try { pstmt = conn.prepareStatement(sql); pstmt.setString(1, "test"); rs = pstmt.executeQuery(); while (rs.next()) { Reader rd = rs.getCharacterStream(3); br = new BufferedReader(rd); String str = null; while ((str = br.readLine()) != null) { System.out.println(str); } Blob blob = rs.getBlob(4); BufferedInputStream bis = new BufferedInputStream(blob.getBinaryStream()); BufferedOutputStream bos = new BufferedOutputStream(new FileOutputStream("newpanda.png")); byte[] bys = new byte[1024]; int len = 0; while ((len = bis.read(bys, 0, 1024)) != -1) { bos.write(bys, 0, len); } bos.flush(); bos.close(); System.out.println("\n-------------图片写好了!"); } }catch (SQLException e){ e.printStackTrace(); }catch (IOException e){ e.printStackTrace(); }finally { CloseFactory.close(pstmt,conn); } } public static void main(String[] args) { BlobSelectTest.select(); } }