JDBC讀寫MySQL的大欄位資料
 
不管你是新手還是老手，大欄位資料的操作常常令你感到很頭痛。因為大欄位有些特殊，不同資料庫處理的方式不一樣，大欄位的操作常常是以流的方式來處理的。而非一般的欄位，一次即可讀出資料。本人以前用到Spring+iBatis架構來操作大欄位，結果以慘烈失敗而告終，在網上尋求解決方案，也沒找到答案。最終以JDBC來實現了大欄位操作部分。
 
本文以MySQL為例，通過最基本的JDBC技術來處理大欄位的插入、讀取操作。
 
環境：
MySQL5.1
JDK1.5
 
一、認識MySQL的大欄位型別
 
BLOB是一個二進位制大物件，可以容納可變數量的資料。有4種BLOB型別：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它們只是可容納值的最大長度不同。
 
有4種TEXT型別：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。這些對應4種BLOB型別，有相同的最大長度和儲存需求。
 
BLOB 列被視為二進位制字串(位元組字串)。TEXT列被視為非二進位制字串(字元字串)。BLOB列沒有字符集，並且排序和比較基於列值位元組的數值值。TEXT列有一個字符集，並且根據字符集的 校對規則對值進行排序和比較。
 
在TEXT或BLOB列的儲存或檢索過程中，不存在大小寫轉換。
 
當未執行在嚴格模式時，如果你為BLOB或TEXT列分配一個超過該列型別的最大長度的值值，值被擷取以保證適合。
 
幾種型別的大欄位最大長度說明：
TINYBLOB最大長度為255(2^[8]–1)位元組的BLOB列。
TINYTEXT最大長度為255(2^[8]–1)字元的TEXT列。
BLOB[(M)]最大長度為65,535(2^[16]–1)位元組的BLOB列。可以給出該型別的可選長度M。如果給出，則MySQL將列建立為最小的但足以容納M位元組長的值的BLOB型別。
TEXT[(M)]最大長度為65,535(2^[16]–1)字元的TEXT列。可以給出可選長度M。則MySQL將列建立為最小的但足以容納M字元長的值的TEXT型別。
MEDIUMBLOB最大長度為16,777,215(2^[24]–1)位元組的BLOB列。
MEDIUMTEXT最大長度為16,777,215(2^[24]–1)字元的TEXT列。
LONGBLOB最大長度為4,294,967,295或4GB(2^[32]–1)位元組的BLOB列。LONGBLOB列的最大有效(允許的)長度取決於客戶端/伺服器協議中配置最大包大小和可用的記憶體。
LONGTEXT最大長度為4,294,967,295或4GB(2^[32]–1)字元的TEXT列。LONGTEXT列的最大有效(允許的)長度取決於客戶端/伺服器協議中配置最大包大小和可用的記憶體。
 
二、建立測試環境
 
create table user (
    id int(11) not null auto_increment,
    name varchar(50) not null,
    pswd varchar(50) default null,
    pic longblob,
    remark longtext,
    primary key (id)
);
 
三、插入讀取blob
 
import lavasoft.common.DBToolkit;

import java.io.*;
import java.sql.*;

/**
* 操作MySQL5的blob欄位
*
* @author leizhimin 2009-12-3 11:34:50
*/
public class BlobTest {
        public static void main(String[] args) {
                insertBlob();
                queryBlob();
        }

        public static void insertBlob() {
                Connection conn = DBToolkit.getConnection();
                PreparedStatement ps = null;
                try {
                        String sql = “insert into testdb.user (name, pswd, pic) values (?, ?, ?)”;
                        ps = conn.prepareStatement(sql);
                        ps.setString(1, “zhangsan”);
                        ps.setString(2, “111”);
                        //設定二進位制引數
                        File file = new File(“D:\new\dbtools\src\res\PIC.PNG”);
                        InputStream in = new BufferedInputStream(new FileInputStream(file));
                        ps.setBinaryStream(3, in, (int) file.length());
                        ps.executeUpdate();
                        in.close();
                } catch (IOException e) {
                        e.printStackTrace();
                } catch (SQLException e) {
                        e.printStackTrace();
                } finally {
                        DBToolkit.closeConnection(conn);
                }
        }

        public static void queryBlob() {
                Connection conn = DBToolkit.getConnection();
                PreparedStatement ps = null;
                Statement stmt = null;
                ResultSet rs = null;
                try {
                        String sql = “select pic from user where id = 24”;
                        stmt = conn.createStatement();
                        rs = stmt.executeQuery(sql);
                        if (rs.next()) {
                                InputStream in = rs.getBinaryStream(1);
                                File file = new File(“D:\new\dbtools\src\res\PIC_COPY.PNG”);
                                OutputStream out = new BufferedOutputStream(new FileOutputStream(file));
                                byte[] buff = new byte[1024];
                                for (int i = 0; (i = in.read(buff)) > 0;) {
                                        out.write(buff, 0, i);
                                }
                                out.flush();
                                out.close();
                                in.close();
                        }
                        rs.close();
                        stmt.close();
                } catch (IOException e) {
                        e.printStackTrace();
                } catch (SQLException e) {
                        e.printStackTrace();
                } finally {
                        DBToolkit.closeConnection(conn);
                }
        }
}

 
注意，要確保二進位制資料長度足夠大，否則可能導致資料寫入不完整的問題。
 
三、插入讀取clob欄位
 
clob在MySQL5中對應的就是text欄位，可以根據實際需要選擇合適的長度。
 
package lavasoft.jdbctest;

import lavasoft.common.DBToolkit;

import java.io.*;
import java.sql.*;

/**
* 操作MySQL5的Clob欄位
*
* @author leizhimin 2009-12-3 13:56:16
*/
public class ClobTest {
        public static void main(String[] args) {
                insertClob();
                queryClob();
        }

        public static void insertClob() {
                Connection conn = DBToolkit.getConnection();
                PreparedStatement ps = null;
                try {
                        String sql = “insert into testdb.user (name, pswd, remark) values (?, ?, ?)”;
                        ps = conn.prepareStatement(sql);
                        ps.setString(1, “zhangsan”);
                        ps.setString(2, “111”);
                        //設定二進位制引數
                        File file = new File(“D:\new\dbtools\src\res\PIC.PNG”);
//                        InputStreamReader reader = new InputStreamReader(new FileInputStream(“D:\new\dbtools\src\res\TEXT.txt”),”GB18030″);
                        InputStreamReader reader = new InputStreamReader(new FileInputStream(“D:\new\dbtools\src\res\TEXT.txt”));
                        ps.setCharacterStream(3, reader, (int) file.length());
                        ps.executeUpdate();
                        reader.close();
                } catch (IOException e) {
                        e.printStackTrace();
                } catch (SQLException e) {
                        e.printStackTrace();
                } finally {
                        DBToolkit.closeConnection(conn);
                }
        }

        public static void queryClob() {
                Connection conn = DBToolkit.getConnection();
                PreparedStatement ps = null;
                Statement stmt = null;
                ResultSet rs = null;
                try {
                        String sql = “select remark from user where id = 1”;
                        stmt = conn.createStatement();
                        rs = stmt.executeQuery(sql);
                        if (rs.next()) {
                                Reader reader = rs.getCharacterStream(1);
                                File file = new File(“D:\new\dbtools\src\res\TEXT_COPY.txt”);
                                OutputStreamWriter writer = new OutputStreamWriter(new FileOutputStream(file));
//                                OutputStreamWriter writer = new OutputStreamWriter(new FileOutputStream(file),”ISO-8859-1″);
//                                OutputStreamWriter writer = new OutputStreamWriter(new FileOutputStream(file),”GB18030″);
                                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();
                        }
                        rs.close();
                        stmt.close();
                } catch (IOException e) {
                        e.printStackTrace();
                } catch (SQLException e) {
                        e.printStackTrace();
                } finally {
                        DBToolkit.closeConnection(conn);
                }
        }
}

 
在處理blob欄位時候，由於直接處理的是二進位制流，所以沒啥問題。在處理clob欄位的時候，由於資料庫對clob是以字元的形式進行儲存，這就有一個編碼問題。本文雖然成功的插入讀取了clob欄位，但是還沒有解決亂碼問題，因為JDBC在獲取到clob的時候，已經對其進行了編碼，Reader reader = rs.getCharacterStream(1); 這就導致了編碼的混亂，如果要徹底解決，還需要看看MySQL驅動的實現。通過非常規手段來解決。為了繞開此問題，可以將clob的資料儲存為blog來操作，可以避免此問題。