怎么读取orcl数据库，表中字段是clob类型的一条数据

怎么读取orcl数据库，表中字段是clob类型的一条数据

写入clob数据

import javaioWriter;

import javasqlConnection;

import javasqlDriverManager;

import javasqlResultSet;

import javasqlStatement;

public class TestClobIn {

public static void main(String args[]){

String data="this is a long passage！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！";

Writer outStream = null;

//通过JDBC获得数据库连接

try {

ClassforName("OraclejdbcdriverOracleDriver");

Connection con = DriverManagergetConnection("jdbc:oracle:thin:@localhost:1521:ewins", "scott", "tiger");

consetAutoCommit(false);

Statement st = concreateStatement();

//插入一个空对象empty_clob()，这个是必须的

stexecuteUpdate("insert into TESTCLOB(ID, NAME, CLOBATTR)values(2,'thename', empty_clob())");

//锁定数据行进行更新，注意“for update”语句,这里不用for update锁定不可以插入clob

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

if (rsnext())

{

//得到javasqlClob对象后强制转换为oraclesqlCLOB

oraclesqlCLOB clob = (oraclesqlCLOB) rsgetClob("CLOBATTR");

outStream = clobgetCharacterOutputStream();

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

char[] c = datatoCharArray();

outStreamwrite(c, 0, clength);

}

outStreamflush();

outStreamclose();

concommit();

conclose();

} catch (Exception e) {

// TODO Auto-generated catch block

eprintStackTrace();

}

}

}

读取clob数据

import javaioInputStream;

import javaioReader;

import javasqlConnection;

import javasqlResultSet;

import javasqlStatement;

public class TestClobOut {

public static void main(String args[]){

String data;

Reader inStream=null;

//获得数据库连接

Connection con = ConnectionFactorygetConnection();//ConnectionFactory类是另外定义的，不必纠结

consetAutoCommit(false);

Statement st = concreateStatement();

//不需要“for update”

ResultSet rs = stexecuteQuery("select CLOBATTR from TESTCLOB where ID=1");

if (rsnext())

{

javasqlClob clob = rsgetClob("CLOBATTR");

inStream = clobgetCharacterStream();

char[] c = new char[(int) cloblength()];

inStreamread(c);

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

data = new String(c);

inStreamclose();

}

inStreamclose();

concommit();

conclose();

}

}

　在绝大多数情况下，使用2种方法使用CLOB

1 相对比较小的，可以用String进行直接 *** 作，把CLOB看成字符串类型即可

2 如果比较大，可以用 getAsciiStream 或者 getUnicodeStream 以及对应的 setAsciiStream 和 setUnicodeStream 即可

读取数据

1 ResultSet rs = stmtexecuteQuery("SELECT TOP 1 FROM Test1");

2 rsnext();

3 Reader reader = rsgetCharacterStream(2);

给你段参考代码，读取clob数据

import javaioInputStream;

import javaioReader;

import javasqlConnection;

import javasqlResultSet;

import javasqlStatement;

public class TestClobOut {

public static void main(String args[]){

String data;

Reader inStream=null;

//获得数据库连接

Connection con = ConnectionFactorygetConnection();//ConnectionFactory类是另外定义的，不必纠结

consetAutoCommit(false);

Statement st = concreateStatement();

//不需要“for update”

ResultSet rs = stexecuteQuery("select CLOBATTR from TESTCLOB where ID=1");

if (rsnext())

{

javasqlClob clob = rsgetClob("CLOBATTR");

inStream = clobgetCharacterStream();

char[] c = new char[(int) cloblength()];

inStreamread(c);

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

data = new String(c);

inStreamclose();

}

inStreamclose();

concommit();

conclose();

}

}

在做数据库开发的时候，有时候会遇到需要读取Oracle数据库中的clob类型的数据的情况。本着代码复用的目的，写了下面的存储过程：读取数据库中clob字段的数据。

CREATE OR REPLACE PROCEDURE prc_read_clob(

table_name IN VARCHAR2,

clob_column_name IN VARCHAR2,

primary_Key_Column_names IN VARCHAR2,

primary_key_values IN VARCHAR2,

offset_i IN NUMBER,

read_length_i IN NUMBER,

RES OUT VARCHAR2,

total_length OUT NUMBER

) AS

/

Autor:Hanks_gao

Create Date:2008/12/10

Description:This procedure is to read clob value by conditions

--------------------------------------------------------------

-----------------Parameters descritption----------------------

table_name : The table that contains clob/blob columns(表名)

clob_column_name : Clob/blob column name of table_name(类型为clob的字段名)

primary_key_column_names : The columns seperated by '}' that can fix only one row data (that is primary key) (主键名，以'}'分隔的字符串)

primary_key_values : The primary keyes values that seperated by '}'(主键键值,以'}'分隔的字符串)

offset_i : The offset of reading clob data(要读取的位移量)

read_length_i : The length of reading clob data per times(要读取的长度)

res : Return value that can be referenced by application(读取的结果)

total_length : The total length of readed clob data(数据库查询到的clob数据的总长度)

-----------------End Parameters descritption------------------

/

tmpPrimaryKeys VARCHAR2(2000); --To save primary_Key_Column_names temporarily(暂存主键,主键是以'}'分隔的字符串)

tmpPrimaryKeyValues VARCHAR2(2000); --To save primary_key_values temporarily(暂存主键键值,以'}'分隔的字符串)

i NUMBER; --循环控制变量

tmpReadLength NUMBER; --暂存要读取的长度

sqlStr VARCHAR2(6000); --Query string(查询字符串)

sqlCon VARCHAR2(5000); --Query condition(查询条件)

TYPE tmparray IS TABLE OF VARCHAR2(5000) INDEX BY BINARY_INTEGER;

arrayPrimaryKeys tmparray; --To save the analyse result of primary_Key_Column_names (暂存分析后得到的主键名)

arrayPrimaryKeyValues tmparray; --To save the analyse result of primary_key_values(暂存分析后得到的主键键值)

BEGIN

total_length := 0;

RES := '';

DECLARE

clobvar CLOB := EMPTY_CLOB;

BEGIN

tmpPrimaryKeys:=primary_Key_Column_names;

tmpPrimaryKeyValues:=primary_key_values;

i:=0;

WHILE INSTR(tmpPrimaryKeys,'}')>0 LOOP --Analyse the column names of primary key(将主键分开,相当于arrayPrimaryKeys =tmpPrimaryKeyssplit("}") )

arrayPrimaryKeys(i):=subSTR(tmpPrimaryKeys,1,(INSTR(tmpPrimaryKeys,'}')-1));

tmpPrimaryKeys:=subSTR(tmpPrimaryKeys,(INSTR(tmpPrimaryKeys,'}')+1));

i:=i+1;

END LOOP;

i:=0;

WHILE INSTR(tmpPrimaryKeyValues,'}')>0 LOOP --Analyse the values of primary key

arrayPrimaryKeyValues(i):=subSTR(tmpPrimaryKeyValues,1,(INSTR(tmpPrimaryKeyValues,'}')-1));

tmpPrimaryKeyValues:=subSTR(tmpPrimaryKeyValues,(INSTR(tmpPrimaryKeyValues,'}')+1));

i:=i+1;

END LOOP;

IF arrayPrimaryKeysCOUNT()<>arrayPrimaryKeyValuesCOUNT() THEN --判断键与键值是否能匹配起来

res:='KEY-VALUE NOT MATCH';

RETURN;

END IF;

i := 0;

sqlCon := '';

WHILE i < arrayPrimaryKeysCOUNT() LOOP

sqlCon := sqlCon || ' AND ' || arrayPrimaryKeys(i) || '='''

|| replace(arrayPrimaryKeyValues(i),'''','''''') || '''';

i := i + 1;

END LOOP;

sqlStr := 'SELECT ' || clob_column_name || ' FROM ' || table_name

|| ' WHERE 1=1 ' || sqlCon || ' AND ROWNUM = 1' ; --组查询字符串

dbms_lobcreatetemporary(clobvar, TRUE);

dbms_lobOPEN(clobvar, dbms_loblob_readwrite);

EXECUTE IMMEDIATE TRIM(sqlStr) INTO clobvar; --执行查询

IF offset_i <= 1 THEN

total_length:=dbms_lobgetlength(clobvar);

END IF;

IF read_length_i <=0 THEN

tmpReadLength := 4000;

ELSE

tmpReadLength := read_length_i;

END IF;

dbms_lobREAD(clobvar,tmpReadLength,offset_i,res); --读取数据

IF dbms_lobISOPEN(clobvar)=1 THEN

dbms_lobCLOSE(clobvar);

END IF;

END;

EXCEPTION

WHEN OTHERS THEN

res:='';

total_length:=0;

END;

以上就是关于怎么读取orcl数据库，表中字段是clob类型的一条数据全部的内容，包括:怎么读取orcl数据库，表中字段是clob类型的一条数据、oracle数据库中clob类型怎么查询博客园、oracle数据库CLOB类型怎么转换为String等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！