[转载]MYSQ数据库CLOB、BLOB存取

转自 http://xdnh.iteye.com/blog/939858

文本大字段类型（CLOB）

------------------------------------------------------------------------------------------------------------------------

>>>>>>>>>> 数据库脚本 <<<<<<<<<<

CREATE TABLE `clob_test` (
`id`  integer NOT NULL AUTO_INCREMENT ,
`txt`  text NOT NULL ,
PRIMARY KEY (`id`)
)
;

>>>>>>>>>> ClobTest.java <<<<<<<<<<

package com.test;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.io.Reader;
import java.io.Writer;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class ClobTest {

	public static void main(String[] args) throws SQLException, IOException {
		read();

		System.out.println("--- End ---");
	}

	// 增加
	static void create() throws SQLException, IOException {

		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = "insert into clob_test (txt) values (?)";

		try {
			conn = JdbcUtils.getConnection();
			pstmt = conn.prepareStatement(sql);

			File file = new File("src/com/test/JdbcUtils.java");
			Reader reader = new BufferedReader(new FileReader(file));

			pstmt.setCharacterStream(1, reader, file.length());
			pstmt.executeUpdate();

			reader.close();
		} finally {
			JdbcUtils.free(null, pstmt, conn);
		}
	}

	// 查询
	static void read() throws SQLException, IOException {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		String sql = "select txt from clob_test";
		try {
			conn = JdbcUtils.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Clob clob = rs.getClob(1);

				Reader reader = clob.getCharacterStream();

				File file = new File("JdbcUtils.java");
				Writer writer = new BufferedWriter(new FileWriter(file));

				char[] buff = new char[1024];

				for (int i = 0; (i = reader.read(buff)) > 0;) {
					writer.write(buff, 0, i);
				}

				writer.flush();
				writer.close();
				reader.close();

			}
		} finally {
			JdbcUtils.free(rs, stmt, conn);
		}
	}

}

二进制大字段类型（BLOB）

------------------------------------------------------------------------------------------------------------------------

 

>>>>>>>>>> 数据库脚本 <<<<<<<<<<

CREATE TABLE `blob_test` (
`id`  integer NOT NULL AUTO_INCREMENT ,
`pic`  blob NOT NULL ,
PRIMARY KEY (`id`)
)
;

package com.test;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class BlobTest {

	public static void main(String[] args) throws SQLException, IOException {
		read();

		System.out.println("--- End ---");
	}

	// 增加
	static void create() throws SQLException, IOException {

		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = "insert into blob_test (pic) values (?)";

		try {
			conn = JdbcUtils.getConnection();
			pstmt = conn.prepareStatement(sql);

			File file = new File("level.gif");
			InputStream is = new BufferedInputStream(new FileInputStream(file));

			pstmt.setBinaryStream(1, is, file.length());
			pstmt.executeUpdate();

			is.close();
		} finally {
			JdbcUtils.free(null, pstmt, conn);
		}
	}

	// 查询
	static void read() throws SQLException, IOException {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		String sql = "select pic from blob_test";
		try {
			conn = JdbcUtils.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Blob blob = rs.getBlob(1);

				InputStream is = blob.getBinaryStream();

				File file = new File("logo.gif");
				OutputStream os = new BufferedOutputStream(
						new FileOutputStream(file));

				byte[] buff = new byte[1024];

				for (int i = 0; (i = is.read(buff)) > 0;) {
					os.write(buff, 0, i);
				}

				os.flush();
				os.close();
				is.close();

			}
		} finally {
			JdbcUtils.free(rs, stmt, conn);
		}
	}

}

数据库工具类：

------------------------------------------------------------------------------------------------------------------------

 

>>>>>>>>>> JdbcUtils.java <<<<<<<<<<

package com.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 数据库工具类
 * 
 * @author MY
 * @version 1.0, 2011-03-03
 * 
 */
public final class JdbcUtils {

	private static String url = "jdbc:mysql:///jdbc";
	private static String username = "root";
	private static String password = "";

	private JdbcUtils() {

	}

	static {
		try {
			// 1、注册驱动
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			throw new ExceptionInInitializerError(e);
		}
	}

	/**
	 * 得到数据库连接
	 * 
	 * @return
	 * @throws SQLException
	 */
	public static Connection getConnection() throws SQLException {
		return DriverManager.getConnection(url, username, password);
	}

	/**
	 * 关闭数据库资源
	 * 
	 * @param rs
	 * @param stmt
	 * @param conn
	 */
	public static void free(ResultSet rs, Statement stmt, Connection conn) {
		try {
			if (rs != null) {
				rs.close();
				rs = null;
			}
		} catch (SQLException e) {
			System.out.println("ResultSet关闭失败：" + e.getMessage());
		} finally {
			try {
				if (stmt != null) {
					stmt.close();
					stmt = null;
				}
			} catch (SQLException e) {
				System.out.println("Statement关闭失败：" + e.getMessage());
			} finally {
				try {
					if (conn != null) {
						conn.close();
						conn = null;
					}
				} catch (SQLException e) {
					System.out.println("Connection关闭失败：" + e.getMessage());
				}
			}
		}
	}

}

>>>>>>>>>> （单例模式）JdbcUtilsSing.java <<<<<<<<<<

package com.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 数据库工具类<br />
 * 单例模式
 * 
 * @author MY
 * @version 1.0, 2011-03-03
 * 
 */
public final class JdbcUtilsSing {

	private String url = "jdbc:mysql:///jdbc";
	private String username = "root";
	private String password = "";

	private static JdbcUtilsSing instance = null;

	private JdbcUtilsSing() {

	}

	public static JdbcUtilsSing getInstance() {
		if (instance == null) {
			synchronized (JdbcUtilsSing.class) {
				if (instance == null) {
					instance = new JdbcUtilsSing();
				}
			}
		}
		return instance;
	}

	static {
		try {
			// 1、注册驱动
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			throw new ExceptionInInitializerError(e);
		}
	}

	/**
	 * 得到数据库连接
	 * 
	 * @return
	 * @throws SQLException
	 */
	public Connection getConnection() throws SQLException {
		return DriverManager.getConnection(url, username, password);
	}

	/**
	 * 关闭数据库资源
	 * 
	 * @param rs
	 * @param stmt
	 * @param conn
	 */
	public void free(ResultSet rs, Statement stmt, Connection conn) {
		try {
			if (rs != null) {
				rs.close();
				rs = null;
			}
		} catch (SQLException e) {
			System.out.println("ResultSet关闭失败：" + e.getMessage());
		} finally {
			try {
				if (stmt != null) {
					stmt.close();
					stmt = null;
				}
			} catch (SQLException e) {
				System.out.println("Statement关闭失败：" + e.getMessage());
			} finally {
				try {
					if (conn != null) {
						conn.close();
						conn = null;
					}
				} catch (SQLException e) {
					System.out.println("Connection关闭失败：" + e.getMessage());
				}
			}
		}
	}

}