详解jdbc实现对CLOB和BLOB数据类型的操作

详解jdbc实现对CLOB和BLOB数据类型的操作

详解jdbc实现对CLOB和BLOB数据类型的操作

1、 读取操作

CLOB

//获得数据库连接

Connection con = ConnectionFactory.getConnection();

con.setAutoCommit(false);

Statement st = con.createStatement();

//不需要“for update”

ResultSet rs = st.executeQuery("select CLOBATTR from TESTCLOB where ID=1");

if (rs.next())

{

java.sql.Clob clob = rs.getClob("CLOBATTR");

Reader inStream = clob.getCharacterStream();

char[] c = new char[(int) clob.length()];

inStream.read(c);

//data是读出并需要返回的数据，类型是String

data = new String(c);

inStream.close();

}

inStream.close();

con.commit();

con.close();

BLOB

//获得数据库连接

Connection con = ConnectionFactory.getConnection();

con.setAutoCommit(false);

Statement st = con.createStatement();

//不需要“for update”

ResultSet rs = st.executeQuery("select BLOBATTR from TESTBLOB where ID=1");

if (rs.next())

{

java.sql.Blob blob = rs.getBlob("BLOBATTR");

InputStream inStream = blob.getBinaryStream();

//data是读出并需要返回的数据，类型是bhttp://yte[]

data = new byte[input.available()];

inStream.read(data);

inStream.close();

}

inStream.close();

con.commit();

con.close();

2、写入操作

CLOB

//获得数据库连接

Connection con = ConnectionFactory.getConnection();

con.setAutoCommit(false);

Statement st = con.createStatement();

//插入一个空对象empty_clob()

st.executeUpdate("insert into TESTCLOB (ID, NAME, CLOJNMvvhBATTR) values (1, "thename", empty_clob())");

//锁定数据行进行更新，注意“for update”语句

ResultSet rs = st.executeQuery("select CLOBATTR from TESTCLOB where ID=1 for update");

if (rs.next())

{

//得到java.sql.Clob对象后强制转换为oracle.sql.CLOB

oracle.sql.CLOB clob = (oracle.sql.CLOB) rs.getClob("CLOBATTR");

Writer outStream = clob.getCharacterOutputStream();

//data是传入的字符串，定义：String data

char[] c = data.toCharArray();

outStream.write(c, 0, c.length);

}

outStream.flush();

outStream.close();

con.commit();

con.close();

BLOB

//获得数据库连接

Connection con = ConnectionFactory.getConnection();

con.setAutoCommit(false);

Statement st = con.createStatement();

//插入一个空对象empty_blob()

st.executeUpdate("insert into TESTBLOB (ID, NAME, BLOBATTR) values (1, "thename", empty_blob())");

//锁定数据行进行更新，注意“for update”语句

ResultSet rs = st.executeQuery("select BLOBATTR from TESTBLOB where ID=1 for update");

if (rs.next())

{

//得到java.sql.Blob对象后强制转换为oracle.sql.BLOB

oracle.sql.BLOB blob = (oracle.sql.BLOB) rs.getBlob("BLOBATTR");

OutputStream outStream = blob.getBinaryOutputStream();

//data是传入的byte数组，定义：byte[] data

outStream.write(data, 0, data.length);

} http://

outStream.flush();

outStream.close();

con.commit();

con.close();

3、读写CLOB/BLOB数据到文件

TNS：

# tnsnames.ora Network Configuration File: d:\oracle\product\10.2.0\client_1\NETWORK\ADMIN\tnsnames.ora

# Generated by Oracle configuration tools.

ORADB =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.100)(PORT = 1521))

)

(CONNECT_DATA =

(SID = ORCL)

)

)

MYORCL =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.100)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = myorcl)

)

)

Table：

create table TEST_ORALOB

( JNMvvh

ID VARCHAR2(20),

TSBLOB BLOB not null,

TSCLOB CLOB not null

)

测试代码：

package lavasoft.oralob.common;

import oracle.sql.BLOB;

import java.io.*;

import java.sql.*;

/**

* JDBC读写Oracle10g的CLOB、BLOB

*

*/

public class TestOraLob {

public static void main(String[] args) {

insertBlob();

queryBlob();

}

public static void insertBlob() {

Connection conn = DBToolkit.getConnection();

PreparedStatement ps = null;

try {

String sql = "insert into test_oralob (ID, TSBLOB, TSCLOB) values (?, ?, ?)";

ps = conn.prepareStatement(sql);

ps.setString(1, "100");

//设置二进制BLOB参数

File file_blob = new File("C:\\a.jpg");

InputStream in = new BufferedInputStream(new FileInputStream(file_blob));

ps.setBinaryStream(2, in, (int) file_blob.length());

//设置二进制CLOB参数

File file_clob = new File("c:\\a.txt");

InputStreamReader reader = new InputStreamReader(new FileInputStream(file_clob));

ps.setCharacterStream(3, reader, (int) file_clob.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 TSBLOB from TEST_ORALOB where id ='100'";

stmt = conn.createStatement();

rs = stmt.executeQuery(sql);

if (rs.next()) {

//读取Oracle的BLOB字段

InputStream in = rs.getBinaryStream(1);

File file = new File("c:\\a1.jpg");

OutputStream out = new BufferedOutputStream(new FileOutputStream(file));

byte[] buff1 = new byte[1024];

for (int i = 0; (i = in.read(buff1)) > 0;) {

out.write(buff1, 0, i);

}

out.flush();

out.close();

in.close();

//读取Oracle的CLOB字段

char[] buff2 = new char[1024];

File file_clob = new File("c:\\a1.txt");

OutputStreamWriter writer = new OutputStreamWriter(new FileOutputStream(file_clob));

Reader reader = rs.getCharacterStream(1);

for (int i = 0; (i = reader.read(buff2)) > 0;) {

writer.write(buff2, 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);

}

}

}

注：如果是具体的字符串写入CLOB字段，简化写法：

//设置二进制CLOB参数

String xxx = "abcdefg";

ps.setCharacterStream(3, new StringReader(xxx), xxx.getBytes("GBK").length);

ps.executeUpdate();

in.close();

感谢阅读，希望能帮助到大家，谢谢大家对本站的支持，如有疑问请留言或者到本站社区交流讨论，感谢阅读，希望能帮助到大家，谢谢大家对本站的支持！

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。