CLOB是内置类型,将字符大对象存储为数据库表某一行中的一个列值。默认情况下,驱动程序使用SQL locator实现Clob对象,这意味着CLOB对象包含一个指向SQL CLOB数据的逻辑指针而不是数据本身。
在JAVA如何使用CLOB进行操作?
在绝大多数情况下,有2种方法使用CLOB。
1、相对比较小的,可以用String进行直接操作,把CLOB看成字符串类型即可。
2、如果比较大,可以用 getAsciiStream 或者 getUnicodeStream 以及对应的 setAsciiStream 和 setUnicodeStream 即可。
(1)读取数据:
ResultSet rs = stmt.executeQuery("SELECT TOP 1 * FROM Test1") rs.next() Reader reader = rs.getCharacterStream(2)
(2)插入数据:
PreparedStatement pstmt = con.prepareStatement("INSERT INTO test1 (c1_id, c2_vcmax) VALUES (?, ?)") pstmt.setInt(1, 1) pstmt.setString(2, htmlStr) pstmt.executeUpdate()
(3)更新数据:
Statement stmt = con.createStatement() ResultSet rs = stmt.executeQuery("SELECT * FROM test1") rs.next() Clob clob = rs.getClob(2) long pos = clob.position("dog", 1) clob.setString(1, "cat", len, 3) rs.updateClob(2, clob) rs.updateRow()
那么java是如何操作数据库clob字段的?
示例代码如下: package com.test.db.clob import java.io.BufferedReader import java.io.IOException import java.io.Writer import java.sql.Clob import java.sql.Connection import java.sql.DriverManager import java.sql.PreparedStatement import java.sql.ResultSet import java.sql.SQLException import java.sql.Statement public class ClobTest {undefined private static Connection conn static {undefined try {undefined Class.forName("oracle.jdbc.driver.OracleDriver") conn = DriverManager.getConnection( "jdbc:oracle:thin:@localhost:1521:orcl","scott","tiger") } catch (ClassNotFoundException e) {undefined e.printStackTrace() } catch (SQLException e) {undefined e.printStackTrace() } } public static void main(String[] args) throws SQLException, IOException {undefined testInsert() testUpdate() testRead() } private static void testInsert() throws SQLException {undefined String sql = "insert into test_clob values(1, empty_clob())" Statement stm = conn.createStatement() stm.execute(sql) } private static void testUpdate() throws SQLException, IOException {undefined String sql = "select content from test_clob where id = 1 for update" Statement stm = conn.createStatement() ResultSet rs = stm.executeQuery(sql) while (rs.next()) {undefined Clob c = rs.getClob(1) c.truncate(0)// clear Writer w = c.setCharacterStream(1)//The first position is 1 w.write("abc") w.close() c.setString(c.length() + 1, "abc") conn.commit() } } private static void testRead() throws SQLException, IOException {undefined String sql = "select content from test_clob where id = 1" PreparedStatement pstm = conn.prepareStatement(sql) ResultSet rs = pstm.executeQuery() while (rs.next()) {undefined Clob clob = rs.getClob("content") System.out.println("clob.getSubString(1, 2) --> " + clob.getSubString(1, 2)) System.out.println("clob.getSubString(1, (int)clob.length()) --> " + clob.getSubString(1, (int)clob.length())) BufferedReader r = new BufferedReader(clob.getCharacterStream()) String s while ((s = r.readLine()) != null) {undefined System.out.println(s) } r.close() } } }
数据库查询出来CLOB clob = (oracle.sql.CLOB)rs.getClob(i)
String sBlob = ClobToString(clob)
row.put(strFieldName, sBlob)
//---------------
/**
*
* 功能:(将clob类型的数据转换为string)
*
* @Title: ClobToString
* @Date: 0521, 2012 17:10:52 PM
* @param clob
* @return string
*/
public String ClobToString(CLOB clob) throws SQLException, IOException {
String reString = ""
Reader is = clob.getCharacterStream()// 得到流
BufferedReader br = new BufferedReader(is)
String s = br.readLine()
StringBuffer sb = new StringBuffer()
while (s != null) {// 执行循环将字符串全部取出付值给StringBuffer由StringBuffer转成STRING
sb.append(s)
s = br.readLine()
}
reString = sb.toString()
return reString
}