这次以MySQL与Oralce为例。
先看表结构把:
- ------------------------处理大数对象-----------------
- ------------------------MySQL----------------------
- --有4种text类型:tinytext、text、mediumtext和longtext
- create table t_clob
- (
- id integer ,
- resume longtext,
- primary key(id)
- );
- --有4种blob类型:tinyblob、blob、mediumblob和longblob
- create table t_blob
- (
- id integer ,
- photo longblob,
- primary key(id)
- );
- -----------------------Oracle------------------------
- --clob
- create table t_clob
- (
- id integer ,
- resume clob,
- primary key(id)
- );
- --blob
- create table t_blob
- (
- id integer ,
- photo blob,
- primary key(id)
- );
先考虑Clob类型的,下面的代码适用于MySQL与Oralce
- package org.monday.demo;
- import java.io.File;
- import java.io.FileReader;
- import java.io.PrintWriter;
- import java.io.Reader;
- import java.sql.Connection;
- import java.sql.PreparedStatement;
- import java.sql.ResultSet;
- import org.junit.Test;
- import org.monday.util.JdbcUtil;
- /**
- * Clob大文本数据操作
- */
- public class ClobMain {
- private static String INSERT_CLOB = "insert into t_clob(id,resume)values(?,?)";
- private static String SELECT_CLOB = "select * from t_clob where id=?";
- @Test
- public void insert() {
- Connection conn = null;
- PreparedStatement pstmt = null;
- try {
- File file = new File("src/电子商务.txt");
- Reader reader = new FileReader(file);
- conn = JdbcUtil.getConnection();
- pstmt = conn.prepareStatement(INSERT_CLOB);
- pstmt.setInt(1, 1);
- pstmt.setCharacterStream(2, reader, (int) file.length());
- pstmt.executeUpdate();
- } catch (Exception e) {
- throw new RuntimeException(e);
- } finally {
- JdbcUtil.release(conn, pstmt, null);
- }
- }
- @Test
- public void read() {
- Connection conn = null;
- PreparedStatement pstmt = null;
- ResultSet rs = null;
- try {
- conn = JdbcUtil.getConnection();
- pstmt = conn.prepareStatement(SELECT_CLOB);
- pstmt.setInt(1, 1);
- rs = pstmt.executeQuery();
- if (rs.next()) {
- Reader reader = rs.getCharacterStream("resume");
- PrintWriter out = new PrintWriter(System.out); // 打印到控制台
- // PrintWriter out= new PrintWriter("src/out.txt"); // 打印到文本
- try {
- int len = 0;
- char[] buffer = new char[1024];
- while ((len = reader.read(buffer)) > 0) {
- out.write(buffer, 0, len);
- }
- } finally {
- if (reader != null) {
- reader.close();
- }
- out.close();
- }
- }
- } catch (Exception e) {
- throw new RuntimeException(e);
- } finally {
- JdbcUtil.release(conn, pstmt, rs);
- }
- }
- }
接下来是Blob类型,这个MySQL与Oracle还是有些不一样的。看仔细了。。
- package org.monday.demo;
- import java.io.File;
- 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;
- import org.monday.util.JdbcUtil;
- /**
- * Blob图片数据操作
- * MySQL与Oralce有所不同
- */
- public class BlobMain {
- private static String INSERT_BLOB = "insert into t_blob(id,photo)values(?,?)";
- private static String SELECT_BLOB = "select * from t_blob where id=?";
- @Test
- public void insert() {
- Connection conn = null;
- PreparedStatement pstmt = null;
- try {
- File file = new File("src/王若琳.jpg");
- InputStream in = new FileInputStream(file);
- conn = JdbcUtil.getConnection();
- pstmt = conn.prepareStatement(INSERT_BLOB);
- pstmt.setInt(1, 1);
- pstmt.setAsciiStream(2, in, (int) file.length());
- pstmt.executeUpdate();
- } catch (Exception e) {
- throw new RuntimeException(e);
- } finally {
- JdbcUtil.release(conn, pstmt, null);
- }
- }
- @Test
- public void read() {
- Connection conn = null;
- PreparedStatement pstmt = null;
- ResultSet rs = null;
- try {
- conn = JdbcUtil.getConnection();
- pstmt = conn.prepareStatement(SELECT_BLOB);
- pstmt.setInt(1, 1);
- rs = pstmt.executeQuery();
- if (rs.next()) {
- InputStream in = rs.getBlob("photo").getBinaryStream();
- // 或者InputStream in = rs.getAsciiStream("photo"); 但是这个却Oralce不好用
- OutputStream out = new FileOutputStream("src/out.jpg");
- try {
- int len = 0;
- byte[] b = new byte[1024];
- while ((len = in.read(b)) > 0) {
- out.write(b, 0, len);
- }
- } finally {
- if (in != null) {
- in.close();
- }
- if (out != null) {
- out.close();
- }
- }
- }
- } catch (Exception e) {
- throw new RuntimeException(e);
- } finally {
- JdbcUtil.release(conn, pstmt, rs);
- }
- }
- /**
- * Oralce插入Blob类型很特殊
- */
- @Test
- public void insert_oralce() {
- // 插入Oralce用的
- String INSERT_BLOB_ORACLE = "insert into t_blob(id,photo)values(?,empty_blob())"; // empty_blob()固定的
- String SELECT_BLOB_ORACLE = "select * from t_blob where id=? for update"; // for update 固定的,不可去
- Connection conn = null;
- PreparedStatement pstmt = null;
- ResultSet rs = null;
- try {
- // 不用开启事务,网上传说要开启事务
- // conn.setAutoCommit(false);
- conn = JdbcUtil.getConnection();
- // 插入一个指针
- pstmt = conn.prepareStatement(INSERT_BLOB_ORACLE);
- pstmt.setInt(1, 1);
- pstmt.executeUpdate();
- // 读取插入的那个指针
- pstmt = conn.prepareStatement(SELECT_BLOB_ORACLE);
- pstmt.setInt(1, 1);
- rs = pstmt.executeQuery();
- if (rs.next()) {
- InputStream in = new FileInputStream(new File("src/王若琳.jpg"));
- // 得到java.sql.Blob对象后强制转换为oracle.sql.BLOB
- oracle.sql.BLOB blob = (oracle.sql.BLOB) rs.getBlob("photo");
- OutputStream out = blob.getBinaryOutputStream();
- try {
- byte[] b = new byte[blob.getBufferSize()];
- int len;
- while ((len = in.read(b)) > 0) {
- out.write(b, 0, len);
- }
- } finally {
- if (in != null) {
- in.close();
- }
- if (out != null) {
- out.close();
- }
- }
- }
- } catch (Exception e) {
- throw new RuntimeException(e);
- } finally {
- JdbcUtil.release(conn, pstmt, rs);
- }
- }
- }