JDBC之处理大数据类型

这次以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);
		}
	}
}

 

 

之前，看过网上的一些列子，感觉写的虎头蛇尾的。。。自己整理了一下。

其中Oralce的插入Blob类型，很特殊。

 

说点特别的：

一般来说，网站的大数据类型的文件，不会直接存到数据库，会存放在一定的服务器目录下。

所以，貌似这样的代码用的机会很少。但是，与中情况还是可以用用滴。

 

比如：你有心爱女孩的照片，但却不是你的女友或者老婆的，这个时候，你就可以把她的照片存到数据库中。

凡此种种，你懂的...