MySQL存取大文本及二进制文件(图片)

package cn.com.songjy.test.db;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileReader;
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;
import java.util.Properties;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

public class MySQLText {

	private static final Log log = LogFactory.getLog(MySQLText.class);

	private static final String id = "2";

	private static final BufferedInputStream bufferedInputStream = new BufferedInputStream(
			MySQLText.class.getClassLoader().getResourceAsStream(
					"cn/com/songjy/test/db/mysql.properties"));

	private static final Properties props = new Properties();

	static {
		try {
			props.load(bufferedInputStream);
			Class.forName(props.getProperty("driver"));
		} catch (IOException e) {
			log.error(e.getMessage(), e);
		} catch (ClassNotFoundException e) {
			log.error(e.getMessage(), e);
		} finally {
			try {
				if (null != bufferedInputStream)
					bufferedInputStream.close();
			} catch (IOException e) {
				log.error(e.getMessage(), e);
			}
		}
	}

	public Connection getConnection() throws SQLException {
		log.info(props.getProperty("url"));
		return DriverManager.getConnection(props.getProperty("url"),
				props.getProperty("user"), props.getProperty("password"));
	}

	// 使用JDBC保存大文本
	public void save_text() throws SQLException, IOException {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		BufferedReader bufferedReader = null;
		try {
			connection = getConnection();

			preparedStatement = connection
					.prepareStatement("INSERT INTO testclob(id,resume) VALUES(?,?)");

			preparedStatement.setString(1, id);

			File txt = new File(
					"src/main/java/cn/com/songjy/test/db/MySQLText.java");

			bufferedReader = new BufferedReader(new FileReader(txt));

			// preparedStatement.setCharacterStream(2, reader, txt.length());
			preparedStatement.setCharacterStream(2, bufferedReader);

			log.info(preparedStatement.executeUpdate() > 0 ? "保存文本成功"
					: "保存文本失败");
		} finally {
			if (null != bufferedReader)
				bufferedReader.close();
			if (null != preparedStatement)
				preparedStatement.close();
			if (null != connection)
				connection.close();
		}
	}

	// 使用JDBC查询大文本
	public void query_text() throws SQLException, IOException {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			connection = getConnection();

			preparedStatement = connection
					.prepareStatement("SELECT id,resume FROM testclob WHERE id = ?");

			preparedStatement.setString(1, id);

			resultSet = preparedStatement.executeQuery();

			while (true == resultSet.next()) {
				BufferedReader bufferedReader = null;
				BufferedWriter bufferedWriter = null;
				try {
					bufferedReader = new BufferedReader(
							resultSet.getCharacterStream("resume"));
					bufferedWriter = new BufferedWriter(
							new FileWriter(
									"src/main/java/cn/com/songjy/test/db/MySQLText.txt"));
					char[] buffer = new char[512];// 每次读取512个字符

					for (int len = 0; (len = bufferedReader.read(buffer)) > 0;) {
						bufferedWriter.write(buffer, 0, len);
					}
				} finally {
					if (null != bufferedWriter)
						bufferedWriter.close();
					if (null != bufferedReader)
						bufferedReader.close();
				}
			}

		} finally {

			if (null != resultSet)
				resultSet.close();

			if (null != preparedStatement)
				preparedStatement.close();

			if (null != connection)
				connection.close();
		}
	}

	// JDBC保存二进制数据
	public void save_binary_stream() throws SQLException, IOException {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		BufferedInputStream bufferedInputStream = null;

		try {

			File jpg = new File(
					"src/main/java/cn/com/songjy/test/db/Jellyfish.jpg");

			bufferedInputStream = new BufferedInputStream(new FileInputStream(
					jpg));

			connection = getConnection();

			preparedStatement = connection
					.prepareStatement("INSERT INTO testblob(id, image) VALUES (?,?)");

			preparedStatement.setString(1, id);
			// preparedStatement.setBinaryStream(2, inputStream, jpg.length());
			preparedStatement.setBinaryStream(2, bufferedInputStream);

			log.info(preparedStatement.executeUpdate() > 0 ? "图片保存成功"
					: "图片保存失败");

		} finally {
			if (null != bufferedInputStream)
				bufferedInputStream.close();// 记得关闭，否则可能导致该文件占用而影响其它操纵（删除等）,可参阅//http://www.coder.xxx/archives/32.html

			if (null != preparedStatement)
				preparedStatement.close();

			if (null != connection)
				connection.close();
		}
	}

	// JDBC查询二进制数据
	public void query_binary_stream() throws SQLException, IOException {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;

		try {
			connection = getConnection();
			preparedStatement = connection
					.prepareStatement("SELECT id, image FROM testblob WHERE id = ?");

			preparedStatement.setString(1, id);

			resultSet = preparedStatement.executeQuery();

			while (true == resultSet.next()) {
				BufferedOutputStream bufferedOutputStream = null;
				BufferedInputStream bufferedInputStream = null;
				try {
					bufferedInputStream = new BufferedInputStream(
							resultSet.getBinaryStream("image"));

					bufferedOutputStream = new BufferedOutputStream(
							new FileOutputStream(
									"src/main/java/cn/com/songjy/test/db/1.jpg"));

					byte[] buffer = new byte[1024];// 每次读取1k

					for (int len = 0; (len = bufferedInputStream.read(buffer)) > 0;) {
						bufferedOutputStream.write(buffer, 0, len);
						// bufferedOutputStream.flush();//关闭的时候会自动将缓存中的数据写入文件
					}
				} finally {
					if (null != bufferedOutputStream)
						bufferedOutputStream.close();
					if (null != bufferedInputStream)
						bufferedInputStream.close();

				}
			}

		} finally {

			if (null != resultSet)
				resultSet.close();

			if (null != preparedStatement)
				preparedStatement.close();

			if (null != connection)
				connection.close();
		}
	}

	public static void main(String[] args) throws SQLException, IOException {
		// new MySQLText().save_text();
		// new MySQLText().query_text();
		// new MySQLText().save_binary_stream();
		new MySQLText().query_binary_stream();
	}
}

/*
 * 
 * create table testclob
 * 
 * (
 * 
 * id varchar(40) primary key,
 * 
 * resume text
 * 
 * );
 * 
 * create table testblob
 * 
 * (
 * 
 * id varchar(40) primary key,
 * 
 * image MediumBlob
 * 
 * );
 */

备注：保存二进制文件时注意选择类型，详见mysql blob大小配置介绍



oracle存取二进制文件（图片）

JDBC 学习笔记（二）—— 大数据+存储过程+批处理+事务管理

可能会遇到的问题：com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException

Java读文件的一些归纳总结

InputStream未关闭导致文件被占用从而引发的问题

BufferedInputStream与BufferedOutputStream的使用

	public void resource() throws IOException {

		Resource resource = new UrlResource(
				"http://127.0.0.1:8008/monitor/file/navicat8_mysql_cs.exe");

		System.out.println(resource.getFilename());

		File f = new File(resource.getFilename());

		System.out.println(f.length());

		InputStream src_in = resource.getInputStream();

		BufferedInputStream in = new BufferedInputStream(src_in);

		byte[] temp = new byte[1024];

		BufferedOutputStream out = new BufferedOutputStream(
				new FileOutputStream(f));

		while (-1 != (in.read(temp))) {
			out.write(temp);
		}

		out.flush();

		out.close();
		in.close();
		src_in.close();

		System.out.println(f.length());
	}