(7)用大对象(Blob、Clob和NClob)

数据库支持的大对象的类型有：Blob、Clob和NClob

参考文档：https://docs.oracle.com/javase/tutorial/jdbc/basics/blob.html

1.增加一个大对象到数据库

下面展示clod的设值情况，如果想了解更多，请参考官方文档，上面给了链接

public void addRowToCoffeeDescriptions(
    String coffeeName, String fileName)
    throws SQLException {

    PreparedStatement pstmt = null;
    try {
        //创建clob对象
        Clob myClob = this.con.createClob();
        //设置流从第一行读起
        Writer clobWriter = myClob.setCharacterStream(1);
        //把一个文件读到流中，返回的文件的字符串内容
        String str = this.readFile(fileName, clobWriter);
        System.out.println("Wrote the following: " +
            clobWriter.toString());
        //如果是数据是mysql，还需给myClob设置字符串
        if (this.settings.dbms.equals("mysql")) {
            System.out.println(
                "MySQL, setting String in Clob " +
                "object with setString method");
            myClob.setString(1, str);
        }
        System.out.println("Length of Clob: " + myClob.length());

        String sql = "INSERT INTO COFFEE_DESCRIPTIONS " +
                     "VALUES(?,?)";

        pstmt = this.con.prepareStatement(sql);
        pstmt.setString(1, coffeeName);
        //设置出入的clob的值
        pstmt.setClob(2, myClob);
        pstmt.executeUpdate();
    } catch (SQLException sqlex) {
        JDBCTutorialUtilities.printSQLException(sqlex);
    } catch (Exception ex) {
      System.out.println("Unexpected exception: " + ex.toString());
    } finally {
        if (pstmt != null)pstmt.close();
    }
}

private String readFile(String fileName, Writer writerArg)
        throws FileNotFoundException, IOException {

    BufferedReader br = new BufferedReader(new FileReader(fileName));
    String nextLine = "";
    StringBuffer sb = new StringBuffer();
    while ((nextLine = br.readLine()) != null) {
        System.out.println("Writing: " + nextLine);
        writerArg.write(nextLine);
        sb.append(nextLine);
    }
    // Convert the content into to a string
    String clobData = sb.toString();

    // Return the data.
    return clobData;
}

2.增加Clob的值

public String retrieveExcerpt(String coffeeName, int numChar)
    throws SQLException {

    String description = null;
    Clob myClob = null;
    PreparedStatement pstmt = null;

    try {
        String sql =
            "select COF_DESC " +
            "from COFFEE_DESCRIPTIONS " +
            "where COF_NAME = ?";

        pstmt = this.con.prepareStatement(sql);
        pstmt.setString(1, coffeeName);
        ResultSet rs = pstmt.executeQuery();

        if (rs.next()) {
            //从第一列中获取clob的值
            myClob = rs.getClob(1);
            System.out.println("Length of retrieved Clob: " +
                myClob.length());
        }
        //获取clob的字符串的内容
        description = myClob.getSubString(1, numChar);
    } catch (SQLException sqlex) {
        JDBCTutorialUtilities.printSQLException(sqlex);
    } catch (Exception ex) {
        System.out.println("Unexpected exception: " + ex.toString());
    } finally {
        if (pstmt != null) pstmt.close();
    }
    return description;
}

3.增加和获取 BLOB对象

blob的增加和获取跟clob类似，可以使用 Blob.setBinaryStream为blob设置流对象。

4.释放大对象锁所持有的资源

Blob、Clob和NClob Java对象至少在创建它们的事务期间有效。这可能导致应用程序在长时间运行的事务中耗尽资源。应用程序可以通过调用Blob、Clob和NClob资源的免费方法来释放它们。

在下面的例子，方法Clob.free被调用，用于释放先前创建的Clob对象的资源:

Clob aClob = con.createClob();
int numWritten = aClob.setString(1, val);
aClob.free();