转：java操纵主要数据库的lob类型数据

2010-04-11 
转：java操纵主要数据库的lob类型数据 
文章分类:Java编程 
Clob和blob的操作主要分为3种：插入，更新和读取显示。 

对于插入，可以分为两类。一类是可以直接按照正常的字段处理，一类为必须先插入空clob/blob再更新为真正的内容。 

插入： 
对于大部分的数据库，在插入lob时都可以通过PreparedStatement.setAsciiStream或PreparedStatement.setBinaryStream直接写入，查到的资料包括： 
H2数据库：http://code.google.com/p/h2database/issues/detail?id=100#c3 
Mysql数据库：http://lavasoft.blog.51cto.com/62575/64963 
DB2：http://lavasoft.blog.51cto.com/62575/64683 

而对于Oracle数据库则必须先插入空lob，重新查询再进行更新（查询时需要加for update）。方法如下： 
Oracle数据库：http://www.iteye.com/topic/254 
插入空Lob的方式很多，如可以插入长度为1的byte[1到blob中]或1个字符的字符串到clob中。也可以在创建表时通过default empty_clob()由数据库自动创建。 

对于上面帖子的补充：robbin同志介绍说需要加for update行锁，是为了防止被其他连接并发更新，这种提法并不完整。oracle数据库要求进行lob更新时，必须加for update，否则报错，这是数据库一个强制要求。就算你知道不会有并发修改，也必须加。 

对于Lob更新，方法都比较类似，查询到Lob字段然后直接更新（H2不支持Lob字段的更新）。需要注意的是，获取到Lob的Stream（如：Blob.setBinaryStream()）写入完毕后，必须调用flush()，否则可能会出现数据丢失（在Oracle10g + odbc14.jar下测试发现）。 

对于Lob读取：可以使用传统的java.sql.Clob/java.sql.Blob的api，某些驱动也可以使用rs.getString()或rs.getBytes()（没有测试过，据说大部分都行）当成普通的varchar和字节字段处理，很是方便。 

对于Clob/Blob的使用，大部分都纠结在Oracle上，Oracle有各种各样的限制，如经典的长度限制，最新的odbc14.jar驱动据说已经解决了所有问题，也就是增加一个连接参数SetBigStringTryClob的事情。相关的资料如下： 
Oracle官方文档： 
http://www.oracle.com/technology/sample_code/tech/java/codesnippet/jdbc/clob10g/handlingclobsinoraclejdbc10g.html
也可以看看一大堆跟贴的讨论： 
http://www.iteye.com/topic/254 

我没有测试过rs.getString()的方法，我测试过Oracle和H2的Clob api。通过clob api的方法，可以正常的写入和读取100万字符的大字符串。最理想，最节省内存，也最通用的应该还是JDBC为Lob设计的api。 





JDBC操作MySQL Lob字段记实 

虽然Java的持久化框架多如牛毛，但都离不开JDBC技术，JDBC在某些时候是其他框架难以取代的。也是java操作数据库最根本的技术。 

上文写了JDBC操作DB2 Lob字段bug问题，为此，我还特意写了MySQL平台下的Lob字段操作，以便能得出更为准确的结论。 

本文通过一个简单的Java类，就能增删改查MySQL的Lob字段。google一下，JDBC操作数据库Lob字段的完整代码一个也没找到。因此把这个测试代码也放在blog上，希望给正在用JDBC做MySQL开发的朋友们一点参考。 

环境： 
MySQL-5.0.45 
mysql-connector-java-5.1.5.zip 

测试的SQL脚本： 
CREATE TABLE t_lob ( 
  NAME varchar(24) DEFAULT NULL, 
  TXT text, 
  IMG blob 
) ENGINE=InnoDB DEFAULT CHARSET=gbk; 

测试代码： 
package lob; 

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

/** 
* JDBC 读取MySQL lob字段测试 
* File: TestLob4MySQL.java 
* User: leizhimin 
* Date: 2008-3-3 14:44:30 
*/ 
public class TestLob4MySQL { 
    public static final String url = "jdbc:mysql://localhost/testdb"; 
    public static final String username = "root"; 
    public static final String password = "leizhimin"; 
    public static final String driverClassName = "com.mysql.jdbc.Driver"; 


    /** 
     * 数据库连接获取器 
     * 
     * @return 数据库连接 
     */ 
    public static Connection makeConnection() { 
        Connection conn = null; 
        try { 
            Class.forName(driverClassName); 
        } catch (ClassNotFoundException e) { 
            e.printStackTrace(); 
        } 
        try { 
            conn = DriverManager.getConnection(url, username, password); 
        } catch (SQLException e) { 
            e.printStackTrace(); 
        } 
        return conn; 
    } 

    /** 
     * 测试数据库连接 
     */ 
    public static void testConnection() { 
        Connection conn = makeConnection(); 
        try { 
            Statement stmt = conn.createStatement(); 
            ResultSet rs = stmt.executeQuery("SELECT * FROM mysql.user"); 
            while (rs.next()) { 
                String s1 = rs.getString(1); 
                System.out.println(s1); 
            } 
            rs.close(); 
            stmt.close(); 
        } catch (SQLException e) { 
            e.printStackTrace(); 
        } finally { 
            try { 
                conn.close(); 
            } catch (SQLException e) { 
                e.printStackTrace(); 
            } 
        } 
    } 

    /** 
     * 插入Lob字段 
     */ 
    public static void testInsertlob() { 
        Connection conn = makeConnection(); 
        try { 
            conn.setAutoCommit(false); 
            File txtFile = new File("C:\\txt.txt"); 
            File imgFile = new File("C:\\img.png"); 
            int txt_len = (int) txtFile.length(); 
            int img_len = (int) imgFile.length(); 
            try { 
                InputStream fis1 = new FileInputStream(txtFile); 
                InputStream fis2 = new FileInputStream(imgFile); 
                PreparedStatement pstmt = conn.prepareStatement("INSERT INTO T_LOB(NAME,TXT,IMG) VALUES('G',?,?)"); 
                pstmt.setAsciiStream(1, fis1, txt_len); 
                pstmt.setBinaryStream(2, fis2, img_len); 
                pstmt.executeUpdate(); 
                conn.commit(); 
            } catch (FileNotFoundException e) { 
                e.printStackTrace(); 
            } 
        } catch (SQLException e) { 
            e.printStackTrace(); 
        } finally { 
            try { 
                conn.close(); 
            } catch (SQLException e) { 
                e.printStackTrace(); 
            } 
        } 
    } 

    /** 
     * 读取lob字段 
     */ 
    public static void testQueryLob() { 
        Connection conn = makeConnection(); 
        try { 
            conn.setAutoCommit(false); 
            Statement stmt = conn.createStatement(); 
            ResultSet rs = stmt.executeQuery("SELECT TXT,IMG FROM T_LOB"); 
            int i = 1; 
            while (rs.next()) { 
                Clob clob = rs.getClob("TXT"); 
                Blob blob = rs.getBlob("IMG"); 
                InputStream txtIs = rs.getAsciiStream("TXT"); 
                InputStream imgIs = rs.getBinaryStream("IMG"); 

                InputStreamReader txtIsr = new InputStreamReader(txtIs); 
                InputStreamReader imgIsr = new InputStreamReader(imgIs); 

                BufferedReader buff_txtIsr = new BufferedReader(txtIsr); 
                BufferedReader buff_imgIsr = new BufferedReader(imgIsr); 

                String line = null; 
                while (null != (line = buff_txtIsr.readLine())) { 
                    System.out.println(line); //将其输出至屏幕，实际你可以按照需要处理 
                } 

                File fileOutput = new File("c:\\img_x" + i + ".png"); 
                FileOutputStream fo = new FileOutputStream(fileOutput); 
                int c; 
                while ((c = imgIs.read()) != -1) 
                    fo.write(c); 
                fo.close(); 
                System.out.println("img " + i + " retrieved!"); 
                i++; 
            } 
            conn.commit(); 
        } catch (SQLException e) { 
            e.printStackTrace(); 
        } catch (Exception e) { 
            e.printStackTrace(); 
        } finally { 
            try { 
                conn.close(); 
            } catch (SQLException e) { 
                e.printStackTrace(); 
            } 
        } 

    } 

    /** 
     * 读取lob字段 
     */ 
    public static void testQueryLob1() { 
        Connection conn = makeConnection(); 
        try { 
            conn.setAutoCommit(false); 
            Statement stmt = conn.createStatement(); 
            ResultSet rs = stmt.executeQuery("SELECT TXT,IMG FROM T_LOB"); 
            while (rs.next()) { 
                Clob clob = rs.getClob("TXT"); 
                Blob blob = rs.getBlob("IMG"); 
                InputStream txtIs = clob.getAsciiStream(); 
                InputStream imgIs = blob.getBinaryStream(); 

                InputStreamReader txtIsr = new InputStreamReader(txtIs); 
                InputStreamReader imgIsr = new InputStreamReader(imgIs); 

                BufferedReader buff_txtIsr = new BufferedReader(txtIsr); 
                BufferedReader buff_imgIsr = new BufferedReader(imgIsr); 

                String line = null; 
                while (null != (line = buff_txtIsr.readLine())) { 
                    System.out.println(line); //将其输出至屏幕，实际你可以按照需要处理 
                } 
            } 
            conn.commit(); 
        } catch (SQLException e) { 
            e.printStackTrace(); 
        } catch (Exception e) { 
            e.printStackTrace(); 
        } finally { 
            try { 
                conn.close(); 
            } catch (SQLException e) { 
                e.printStackTrace(); 
            } 
        } 
    } 

    /** 
     * 删除lob字段 
     */ 
    public static void testDeleteLob() { 
        Connection conn = makeConnection(); 
        try { 
            conn.setAutoCommit(false); 
            Statement stmt = conn.createStatement(); 
            int row = stmt.executeUpdate("DELETE FROM T_LOB"); 
            conn.commit(); 
            System.out.println("删除 " + row + " 行数据！"); 
        } catch (SQLException e) { 
            e.printStackTrace(); 
        } finally { 
            try { 
                conn.close(); 
            } catch (SQLException e) { 
                e.printStackTrace(); 
            } 
        } 
    } 

    /** 
     * 读取lob字段 
     */ 
    public static void testUpdateLob() { 
        Connection conn = makeConnection(); 

        try { 
            String in_str="HAHAHAHAHAHA!!!"; 
            File in_file=new File("c:\\img_haha.png"); 
            InputStream txt_is = string2InputStream(in_str); 
            InputStream img_is =new FileInputStream(in_file); 

            conn.setAutoCommit(false); 
            PreparedStatement pstmt = conn.prepareStatement("UPDATE T_LOB SET TXT=?, IMG=? WHERE NAME='G'"); 
            pstmt.setAsciiStream(1,txt_is,in_str.getBytes().length); 
            pstmt.setBinaryStream(2,img_is,(int)in_file.length()); 

            int row = pstmt.executeUpdate(); 

            conn.commit(); 
            txt_is.close(); 
            img_is.close(); 

//            System.out.println("更新 " + row + " 行数据！"); 
        } catch (SQLException e) { 
            e.printStackTrace(); 
        } catch (FileNotFoundException e) { 
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates. 
        } catch (IOException e) { 
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates. 
        } finally { 
            try { 
                conn.close(); 
            } catch (SQLException e) { 
                e.printStackTrace(); 
            } 
        } 
    } 

    public static void main(String args[]) { 
//        testInsertlob(); 
//        testQueryLob(); 
//        testQueryLob1(); 
//        testDeleteLob(); 
        testUpdateLob(); 
    } 

    public static InputStream string2InputStream(String str) { 
        if (str == null) return null; 
        return new ByteArrayInputStream(str.getBytes()); 
    } 

    public static String inputStream2String(InputStream is) { 
        StringBuffer sb = new StringBuffer(); 
        BufferedReader br = new BufferedReader(new InputStreamReader(is)); 
        String inputLine; 
        try { 
            while ((inputLine = br.readLine()) != null) { 
                sb.append(inputLine).append("\n"); 
            } 
        } catch (IOException e) { 
            e.printStackTrace(); 
        } 
        return sb.toString(); 
    } 
}