jdbc大数据 CLOB BLOB

package cn.itcast.lob;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.Reader;
import java.io.Writer;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import org.junit.Test;

import cn.itcast.jdbc.JDBCUtils;

public class LobTest {
	@Test
	public void demo4() {
		// 读取blob数据
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;

		try {
			conn = JDBCUtils.getConnection();
			String sql = "select * from blobdata where id = 1";
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();

			if (rs.next()) {
				InputStream in = new BufferedInputStream(rs
						.getBinaryStream("myblob"));
				OutputStream out = new BufferedOutputStream(
						new FileOutputStream("boy_bak.mp3"));
				int b;
				while ((b = in.read()) != -1) {
					out.write(b);
				}
				in.close();
				out.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	@Test
	public void demo3() {
		// 将 mp3 写入数据库
		// 将文本文件保存到数据库
		Connection conn = null;
		PreparedStatement stmt = null;

		try {
			conn = JDBCUtils.getConnection();
			String sql = "insert into blobdata values(null,?)";
			stmt = conn.prepareStatement(sql);// 预编译
			// 因为sql存在 ? 设置参数
			File file = new File("oldboy.mp3");
			stmt.setBinaryStream(1, new FileInputStream("oldboy.mp3"),
					(int) file.length());

			// 插入
			stmt.executeUpdate();// 预编译执行 不需要传入sql
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.release(stmt, conn);
		}
	}

	@Test
	public void demo2() {
		// 读取text大文本字段
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;

		try {
			conn = JDBCUtils.getConnection();
			String sql = "select * from textdata where id = ?";
			stmt = conn.prepareStatement(sql);
			// 设置参数
			stmt.setInt(1, 2);
			rs = stmt.executeQuery();// 这里不要写sql
			while (rs.next()) {
				Reader reader = rs.getCharacterStream("mytext");
				Writer writer = new FileWriter("dulala_bak.txt");
				int c;
				while ((c = reader.read()) != -1) {
					writer.write(c);
				}
				reader.close();
				writer.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.release(rs, stmt, conn);
		}
	}

	@Test
	public void demo1() {
		// 将文本文件保存到数据库
		Connection conn = null;
		PreparedStatement stmt = null;

		try {
			conn = JDBCUtils.getConnection();
			String sql = "insert into textdata values(null,?)";
			stmt = conn.prepareStatement(sql);// 预编译
			// 因为sql存在 ? 设置参数
			File file = new File("dulala.txt");
			stmt.setCharacterStream(1, new FileReader("dulala.txt"), (int) file
					.length());

			// 插入
			stmt.executeUpdate();// 预编译执行 不需要传入sql
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.release(stmt, conn);
		}
	}
}