MYSQ数据库CLOB、BLOB存取

虚度年华

文本大字段类型（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`)); 
 
>>>>>>>>>> BlobTest.java <<<<<<<<<<
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());}}}}}