JDBC操作LOB字段详解
oracle dbms_lob.substr用法
Oracle数据库系统中有许多内置的函数和过程，用于处理LOB （Large Object）数据类型，其中dbms_lob.substr是其中之一。
LOB数据类型通常用于存储大量的文本、图像或音频数据。
在处理这些数据类型时，需要使用专门的方法来提取和操作其中的部分数据。
dbms_lob.substr就是用于从LOB字段中提取指定长度的数据的函数。
二、使用方法1. 参数说明在使用dbms_lob.substr函数时，需要传入以下参数：- lob_loc：表示LOB字段或者变量的值。
- amount：表示要提取的数据的长度。
- offset：表示从LOB字段中的哪个位置开始提取数据。
如果不指定offset，则默认从LOB字段的第一个字节开始提取数据。
2. 函数语法dbms_lob.substr函数的语法如下所示：dbms_lob.substr(lob_loc IN BLOB,amount IN INTEGER,offset IN INTEGER := 1)RETURN VARCHAR2;下面是一个使用dbms_lob.substr函数的示例：```sqlDECLAREl CLOB;offset NUMBER := 1;amount NUMBER := 100;l_substr VARCHAR2(xxx);BEGINSELECT clob_column INTO l FROM table_name WHERE ...;l_substr := dbms_lob.substr(l, amount, offset);END;```在这个示例中，我们首先声明了一个CLOB类型的变量l，接着指定了要提取的数据的长度和偏移量。
然后使用SELECT语句从表中获取LOB字段的值，并将数据存储到变量l中。
调用dbms_lob.substr函数提取指定长度的数据，并将结果存储到l_substr变量中。
三、注意事项1. 数据长度在使用dbms_lob.substr函数时，需要注意指定要提取的数据的长度和偏移量。
blob用法
}
catch(IOException e) {
e.printStackTrace();
int length=(int) file.length();
InputStream fin=new FileInputStream(file);
PreparedStatement pstmt=new PreparedStratement("INSERT INTO files VALUSE(?,?)");
pstmt.setString(1,"LOGO");
pstmt.setBinaryStream(2,fin,length); //将文件流写入
pstmt.executeUpdate();
pstmt.clearParameters();
}
}
}
Statement stmt=conn.createStatement();
ResultSet result=stmt.executeQuery("SELECT * FROM files");
Blob拥有getBinaryStream(),getBytes()等方法,可以取得二进制串流或byte等资料,同样,Clob可以通过getCharacterStream(),getSubString()等方法,可以取得字元串流或子字串等数据(详细查看API文件)
下面举例说明操作Blob,Clob数据
String url="jdbc:mysql://localhost:3306/upload?";
String username="myname";
java 存lob字段数据到sybase数据库(三种情况)
</id>
<property name="postText" column="post_text"
type="org.springframework.orm.hibernate3.support.ClobStringType" />
public LobHandler getLobHandler() {
return lobHandler;
}
public void setLobHandler(LobHandler lobHandler) {
this.lobHandler = lobHandler;
new AbstractLobCreatingPreparedStatementCallback(this.lobHandler) { //②
protected void setValues(PreparedStatement ps,LobCreator lobCreator)
//④ 设置 BLOB 字段
lobCreator.setBlobAsBytes(ps,3,post.getPostAttach());
}
});
}
private JdbcDaoAccess execFeeDeductInfoJDAO;
class="org.springframework.jdbc.support.lob.DefaultLobHandler"
lazy-init="true" />
<bean id="postDao" class="my.PostJdbcDao">
详解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是读出并需要返回的数据，类型是Stringdata = new String(c);inStream.close();}inStream.close();mit();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是读出并需要返回的数据，类型是byte[]data = new byte[input.available()];inStream.read(data);inStream.close();}inStream.close();mit();con.close();2、写⼊操作CLOB//获得数据库连接Connection con = ConnectionFactory.getConnection();con.setAutoCommit(false);Statement st = con.createStatement();//插⼊⼀个空对象empty_clob()st.executeUpdate("insert into TESTCLOB (ID, NAME, CLOBATTR) 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.CLOBoracle.sql.CLOB clob = (oracle.sql.CLOB) rs.getClob("CLOBATTR");Writer outStream = clob.getCharacterOutputStream();//data是传⼊的字符串，定义：String datachar[] c = data.toCharArray();outStream.write(c, 0, c.length);}outStream.flush();outStream.close();mit();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.BLOBoracle.sql.BLOB blob = (oracle.sql.BLOB) rs.getBlob("BLOBATTR");OutputStream outStream = blob.getBinaryOutputStream();//data是传⼊的byte数组，定义：byte[] dataoutStream.write(data, 0, data.length);}outStream.flush();outStream.close();mit();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(ID VARCHAR2(20),TSBLOB BLOB not null,TSCLOB CLOB not null)测试代码：package mon;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();感谢阅读，希望能帮助到⼤家，谢谢⼤家对本站的⽀持，如有疑问请留⾔或者到本站社区交流讨论，感谢阅读，希望能帮助到⼤家，谢谢⼤家对本站的⽀持！。
mysql和Oracle在对clob和blob字段的处理
mysql和Oracle在对clob和blob字段的处理⼀、与库如何处理Clob，Blob数据类型(1)不通数据库中对应clob,blob的类型如下：MySQL中：clob对应text，blob对应blobDB2/Oracle中：clob对应clob，blob对应blob(2)domain中对应类型：clob对应String，blob对应byte[]clob对应.sql.Clob，blob对应java.sql.Blob(3)hibernate配置中对应类型：clob-->clob ，blob-->binary也可以直接使⽤数据库提供类型，例如：oracle.sql.Clob，oracle.sql.Blob⼆、jdbc操作clob(以oracle为例)⾸先操作clob/blob不像操作varchar类型那样简单，插⼊步骤⼀般分为两步：第⼀步插⼊⼀个空值，第⼆步锁住此⾏，更新clob/blob字段。
//插⼊空值conn.setAutoCommit(false);String sql = "INSERT INTO T_FILE(NAME, FILE_CONTENT) VALUES ('Jambhala', EMPTY_CLOB())";PreparedStatement pstmt = conn.prepareStatement(sql);pstmt.executeUpdate();//锁住此⾏String sql_lockstr = "SELECT FILE_CONTENT FROM T_FILE WHERE NAME='Jambhala' FOR UPDATE";pstmt = conn.prepareStatement(sql_lockstr);ResultSet rs = pstmt.executeQuery();oracle.sql.Clob clob = (oracle.sql.Clob)rs.getClob(1);java.io.OutputStream writer = clob.getAsciiOutputStream();byte[] temp = newFileContent.getBytes();writer.write(temp);writer.flush();writer.close();pstmt.close();读取内容：oracle.sql.Clob clob = rs.getClob("FILE_CONTENT");if(clob != null){Reader is = clob.getCharacterStream();BufferedReader br = new BufferedReader(is);String s = br.readLine();while(s != null){content += s+"<br>";s = br.readLine();}}三、jdbc操作blobconn.setAutoCommit(false);String sql = "INSERT INTO T_PHOTO(NAME, PHOTO) VALUES ('Jambhala', EMPTY_BLOB())";pstmt = conn.prepareStatement(sql);pstmt = conn.executeUpdate();sql = "SELECT PHOTO FROM T_PHOTO WHERE NAME='Jambhala'";pstmt = conn.prepareStatement(sql);rs = pstmt.executeQuery(sql);if(rs.next()){oracle.sql.Blob blob = (oracle.sql.Blob)rs.getBlob(1);}//write to a fileFile file=new File("C:\\test.rar");FileInputStream fin = new FileInputStream(file);OutputStream out = blob.getBinaryOutputStream();int count=-1,total=0;byte[] data = new byte[blob.getBufferSize()];while((count=fin.read(data)) != -1){total += count;out.write(data, 0, count);}四、hibernate处理clobMyFile file = new MyFile();file.setName("Jambhala");file.setContent(Hibernate.createClob(""));session.save(file);session.flush();session.refresh(file, LockMode.UPGRADE);oracle.sql.Clob clob = (oracle.sql.Clob)file.getContent();Writer pw = clob.getCharacterOutputStream();pw.write(longText); //写⼊长⽂本pw.close();session.close();五、使⽤hibernate处理blob原理基本相同：Photo photo = new Photo();photo.setName("Jambhala");photo.setPhoto(Hibernate.createBlob(""));session.save(photo);session.flush();session.refresh(photo, LockMode.UPGRADE); //锁住此对象oracle.sql.Blob blob = photo.getPhoto(); //取得此blob的指针OutputStream out = blob.getBinaryOutputStream();//写⼊⼀个⽂件File f = new File("C:\\test.rar");FileInputStream fin = new FileInputStream(f);int count=-1,total=0;byte[] data = new byte[(int)fin.available()];out.write(data);fin.close();out.close();session.flush();String DRIVER = "oracle.jdbc.driver.OracleDriver";//Oracle连接⽤URLprivate static final String URL = "jdbc:oracle:thin:@testora:1521:orac"; //⽤户名private static final String USER = "scott";//密码private static final String PASSWORD = "pswd";//数据库连接private static Connection conn = null;//SQL语句对象private static Statement stmt = null;//@roseuid 3EDA089E02BCpublic LobPros(){}//往数据库中插⼊⼀个新的Clob对象//@param infile 数据⽂件//@throws ng.Exception//@roseuid 3EDA089E02BCpublic static void clobInsert(String infile) throws Exception {//设定不⾃动提交boolean defaultCommit = conn.getAutoCommit();conn.setAutoCommit(false);try{//插⼊⼀个空的Clob对象stmt.executeUpdate("INSERT INTO TEST_CLOB VALUES ('111', EMPTY_CLOB())");//查询此Clob对象并锁定ResultSet rs = stmt.executeQuery("SELECT CLOBCOL FROM TEST_CLOB WHERE ID='111' FOR UPDATE"); while(rs.next()){//取出此Clob对象oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob("CLOBCOL");//向Clob对象中写⼊数据BufferedWriter out = new BufferedWriter(clob.getCharacterOutputStream());BufferedReader in = new BufferedReader(new FileReader(infile));int c;while((c=in.read()) != -1){out.write(c);}in.close();out.close();}//正式提交mit();}catch(Exception e){//出错回滚conn.rollback();throw e;}//恢复原提交状态conn.setAutoCommit(defaultCommit);}//修改Clob对象(是在原Clob对象基础上进⾏覆盖式的修改)//@param infile 数据⽂件//@throws ng.Exception//@roseuid 3EDA089E02BCpublic static void clobModify(String infile) throws Exception {//设定不⾃动提交boolean defaultCommit = conn.getAutoCommit();conn.setAutoCommit(false);try{//查询Clob对象并锁定ResultSet rs = stmt.executeQuery("SELECT CLOBCOL FROM TEST_CLOB WHERE ID='111' FOR UPDATE"); while(rs.next()){//获取此Clob对象oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob("CLOBCOL");//进⾏覆盖式修改BufferedWriter out = new BufferedWriter(clob.getCharacterOutputStream());BufferedReader in = new BufferedReader(new FileReader(infile));int c;while ((c=in.read())!=-1) {out.write(c);}in.close();out.close();}//正式提交mit();}catch(Exception e){//出错回滚conn.rollback();throw e;}//恢复原提交状态conn.setAutoCommit(defaultCommit);}//替换CLOB对象（将原CLOB对象清除，换成⼀个全新的CLOB对象//@param infile 数据⽂件//@throws ng.Exception//@roseuid 3EDA04BF01E1public static void clobReplace(String infile) throws Exception {//设定不⾃动提交boolean defaultCommit = conn.getAutoCommit();conn.setAutoCommit(false);try{//清空原CLOB对象stmt.executeUpdate("UPDATE TEST_CLOB SET CLOBCOL=EMPTY_CLOB() WHERE ID='111'");//查询CLOB对象并锁定ResultSet rs = stmt.executeQuery("SELECT CLOBCOL FROM TEST_CLOB WHERE ID='111' FOR UPDATE"); while (rs.next()) {//获取此CLOB对象oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob("CLOBCOL");//更新数据BufferedWriter out = new BufferedWriter(clob.getCharacterOutputStream());BufferedReader in = new BufferedReader(new FileReader(infile));int c;while ((c=in.read())!=-1) {out.write(c);}in.close();out.close();}//正式提交mit();}catch(Exception e){//出错回滚conn.rollback();throw e;}//恢复原提交状态conn.setAutoCommit(defaultCommit);}//CLOB对象读取//@param outfile 输出⽂件名//@throws ng.Exception//@roseuid 3EDA04D80116public static void clobRead(String outfile) throws Exception {//设定不⾃动提交boolean defaultCommit = conn.getAutoCommit();conn.setAutoCommit(false);try{//查询CLOB对象ResultSet rs = stmt.executeQuery("SELECT * FROM TEST_CLOB WHERE ID='111'");while (rs.next()) {//获取CLOB对象oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob("CLOBCOL");//以字符形式输出BufferedReader in = new BufferedReader(clob.getCharacterStream());BufferedWriter out = new BufferedWriter(new FileWriter(outfile));int c;while ((c=in.read())!=-1) {out.write(c);}out.close();in.close();}}catch(Exception e){conn.rollback();throw e;}//恢复原提交状态conn.setAutoCommit(defaultCommit);}//向数据库中插⼊⼀个新的BLOB对象//@param infile 数据⽂件//@throws ng.Exception//@roseuid 3EDA04E300F6public static void blobInsert(String infile) throws Exception {//设定不⾃动提交boolean defaultCommit = conn.getAutoCommit();conn.setAutoCommit(false);try {//插⼊⼀个空的BLOB对象stmt.executeUpdate("INSERT INTO TEST_BLOB VALUES ('222', EMPTY_BLOB())");//查询此BLOB对象并锁定ResultSet rs = stmt.executeQuery("SELECT BLOBCOL FROM TEST_BLOB WHERE ID='222' FOR UPDATE"); while (rs.next()) {//取出此BLOB对象oracle.sql.BLOB blob = (oracle.sql.BLOB)rs.getBlob("BLOBCOL");//向BLOB对象中写⼊数据BufferedOutputStream out = new BufferedOutputStream(blob.getBinaryOutputStream());BufferedInputStream in = new BufferedInputStream(new FileInputStream(infile));int c;while ((c=in.read())!=-1) {out.write(c);}in.close();out.close();}//正式提交mit();} catch (Exception e) {//出错回滚conn.rollback();throw e;}//恢复原提交状态conn.setAutoCommit(defaultCommit);}//修改BLOB对象（是在原BLOB对象基础上进⾏覆盖式的修改）//@param infile 数据⽂件//@throws ng.Exception//@roseuid 3EDA04E90106public static void blobModify(String infile) throws Exception {//设定不⾃动提交boolean defaultCommit = conn.getAutoCommit();conn.setAutoCommit(false);try {//查询BLOB对象并锁定ResultSet rs = stmt.executeQuery("SELECT BLOBCOL FROM TEST_BLOB WHERE ID='222' FOR UPDATE"); while (rs.next()) {//取出此BLOB对象oracle.sql.BLOB blob = (oracle.sql.BLOB)rs.getBlob("BLOBCOL");//向BLOB对象中写⼊数据BufferedOutputStream out = new BufferedOutputStream(blob.getBinaryOutputStream());BufferedInputStream in = new BufferedInputStream(new FileInputStream(infile));int c;while ((c=in.read())!=-1) {out.write(c);}in.close();out.close();}//正式提交mit();} catch (Exception e) {//出错回滚conn.rollback();throw e;}//恢复原提交状态conn.setAutoCommit(defaultCommit);}//替换BLOB对象（将原BLOB对象清除，换成⼀个全新的BLOB对象）//@param infile 数据⽂件//@throws ng.Exception//@roseuid 3EDA0505000Cpublic static void blobReplace(String infile) throws Exception {//设定不⾃动提交boolean defaultCommit = conn.getAutoCommit();conn.setAutoCommit(false);try {//清空原BLOB对象stmt.executeUpdate("UPDATE TEST_BLOB SET BLOBCOL=EMPTY_BLOB() WHERE ID='222'");//查询此BLOB对象并锁定ResultSet rs = stmt.executeQuery("SELECT BLOBCOL FROM TEST_BLOB WHERE ID='222' FOR UPDATE");while (rs.next()) {//取出此BLOB对象oracle.sql.BLOB blob = (oracle.sql.BLOB)rs.getBlob("BLOBCOL");//向BLOB对象中写⼊数据BufferedOutputStream out = new BufferedOutputStream(blob.getBinaryOutputStream());BufferedInputStream in = new BufferedInputStream(new FileInputStream(infile));int c;while ((c=in.read())!=-1) {out.write(c);}in.close();out.close();}//正式提交mit();} catch (Exception e) {//出错回滚conn.rollback();throw e;}//恢复原提交状态conn.setAutoCommit(defaultCommit);}//BLOB对象读取//@param outfile 输出⽂件名//@throws ng.Exception//@roseuid 3EDA050B003Bpublic static void blobRead(String outfile) throws Exception {//设定不⾃动提交boolean defaultCommit = conn.getAutoCommit();conn.setAutoCommit(false);try {//查询BLOB对象ResultSet rs = stmt.executeQuery("SELECT BLOBCOL FROM TEST_BLOB WHERE ID='222'"); while (rs.next()) {//取出此BLOB对象oracle.sql.BLOB blob = (oracle.sql.BLOB)rs.getBlob("BLOBCOL");//以⼆进制形式输出BufferedOutputStream out = new BufferedOutputStream(new FileOutputStream(outfile));BufferedInputStream in = new BufferedInputStream(blob.getBinaryStream());int c;while ((c=in.read())!=-1) {out.write(c);}in.close();out.close();}//正式提交mit();} catch (Exception e) {//出错回滚conn.rollback();throw e;}//恢复原提交状态conn.setAutoCommit(defaultCommit);}//建⽴测试⽤表格//@throws Exceptionpublic static void createTables() throws Exception {try {stmt.executeUpdate("CREATE TABLE TEST_CLOB (ID NUMBER(3), CLOBCOL CLOB)"); stmt.executeUpdate("CREATE TABLE TEST_BLOB (ID NUMBER(3), BLOBCOL BLOB)"); } catch (Exception e) { }}//@param args - 命令⾏参数//@throws ng.Exception//@roseuid 3EDA052002ACpublic static void main(String[] args) throws Exception {//装载驱动,建⽴数据库连接Class.forName(DRIVER);conn = DriverManager.getConnection(URL,USER,PASSWORD);stmt = conn.createStatement();//建⽴测试表格createTables();//CLOB对象插⼊测试clobInsert("c:/clobInsert.txt");clobRead("c:/clobInsert.out");//CLOB对象修改测试clobModify("c:/clobModify.txt");clobRead("c:/clobModify.out");//CLOB对象替换测试clobReplace("c:/clobReplace.txt");clobRead("c:/clobReplace.out");//BLOB对象插⼊测试blobInsert("c:/blobInsert.doc");blobRead("c:/blobInsert.out");//BLOB对象修改测试blobModify("c:/blobModify.doc");blobRead("c:/blobModify.out");//BLOB对象替换测试blobReplace("c:/blobReplace.doc");blobRead("c:/bolbReplace.out");//关闭资源退出conn.close();System.exit(0);}。
第六章 JDBC - Blob与Clob字段的处理
配套源码
请参照配套源码中的项目JDBC_demo中的 包blob与clob.
�
Blob与Clob字段的处 理
主编:安坤
Clob字段的处理
为什么要特殊处理Clob字段? 如ቤተ መጻሕፍቲ ባይዱ特殊处理Clob字段?
同理处理Blob字段
如何处理
代码
插入数据: // 动态导入数据库的驱动 Class.forName("com.mysql.jdbc.Driver"); // 获取数据库链接 conn = DriverManager.getConnection( "jdbc:mysql://localhost:3306/jdbc_teaching", "root", ""); // 创造SQL语句 String sql = "INSERT INTO pic_list ( pic_id, pic_data ) " + "VALUES ( 'pic1', ? )"; // 将SQL语句绑定到PreparedStatement中 stmt = conn.prepareStatement(sql); // 准备数据 File file = new File("./resources/test.jpg"); FileInputStream fis = new FileInputStream( file ); // 设定数据 stmt.setBinaryStream( 1, fis, ( int )file.length() ); // 执行插入操作 stmt.executeUpdate(); fis.close();
代码
查询数据: // 动态导入数据库的驱动 Class.forName("com.mysql.jdbc.Driver"); // 获取数据库链接 conn = DriverManager.getConnection( "jdbc:mysql://localhost:3306/jdbc_teaching", "root", ""); // 创造SQL语句 String sql = "SELECT * FROM pic_list WHERE pic_id = 'pic1'"; stmt = conn.prepareStatement(sql); rs = stmt.executeQuery(); rs.next(); // 将读到的数据写到硬盘上的管道 File file = new File("f:\\test.jpg"); FileOutputStream fos = new FileOutputStream(file); // 从数据库读数据的管道 InputStream is = rs.getBinaryStream(2); int len = 0; byte b[] = new byte[1024]; // 一边读一边写 while (-1 != (len = is.read(b))) { fos.write(b, 0, len); } fos.close(); is.close();
LOB字段相关概念(自动创建LOB索引段和重建索引方法)
LOBs,或Large Objects字段，是Oracle中用于处理存储非字符数据推荐的一种字段类型，例如mp3,video,图片，和long字符串数据。
二进制大对象，或BLOBs,字符大对象，或CLOBs,能够存储TB的数据。
LOB列有许多相关的属性，每个LOB列属性可以使用"LOB （lobcolname）STORE AS …"这种语法来描述。
一个包含LOBs字段类型的表（CLOB,NCLOB和BLOB）会为每个LOB列创建两个额外的磁盘段segment,LOBINDEX和LOBSEGMENT.可以通过DBA_LOBS,ALL_LOBS 或USER_LOBS数据字典视图表来查看他们以及LOB属性。
按照MOS（1490228.1）的示例，可以指定LOBINDEX和LOBSEGMENT的表空间（8i以前的版本允许LOBINDEX和LOBSEGMENT使用的表空间不同）：Create table DemoLob （ A number, B clob ）LOB（b）STORE AS lobsegname （TABLESPACE lobsegtsSTORAGE （lobsegment storage clause）INDEX lobindexname （TABLESPACE lobidxtsSTORAGE （ lobindex storage clause ）））TABLESPACE tables_tsSTORAGE（ tables storage clause ）;下面再做个简单的实验，证明LOB列的表会自动创建LOB索引：SQL> create table ml_test1（a clob）；Table createdSQL> create index idx_ml_test1 on ml_test1 （a）；create index idx_ml_test1 on ml_test1 （a）*ERROR at line 1:ORA-02327: cannot create index on expression with datatype LOBORA-02327: 无法以数据类型 LOB 的表达式创建索引表明不能用CREATE INDEX为LOB列创建索引。
JDBC操纵Oracle数据库中的BLOB字段
数据库执行脚本：create table bxxx(id int primary key ,image blob);查询lob字段是否写入的sql语句：SELECT DBMS_LOB.GETLENGTH(image) FROM bxxx;Blob字段的写入方法一：（JDBC2.0规范）代码：import java.io.File;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.IOException;import java.io.InputStream;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.SQLException;public class BlobDemo01 {public static final String DBDRIVER= "oracle.jdbc.driver.OracleDriver";public static final String DBURL= "jdbc:oracle:thin:@localhost:1521:ORCL";public static final String DBUSER = "scott";public static final String DBPASSWORD = "tiger";public static void main(String[] args) {Connection conn = null;PreparedStatement pstmt = null;String sql = "insert into bxxx values(?,?)";File f = new File("d:"+File.separator+"5586.jpg");InputStream in = null;try {in = new FileInputStream(f);} catch (FileNotFoundException e1) {e1.printStackTrace();}try {Class.forName(DBDRIVER);} catch (ClassNotFoundException e) {e.printStackTrace();}try {conn = DriverManager.getConnection(DBURL,DBUSER,DBPASSWORD);conn.setAutoCommit(false);pstmt = conn.prepareStatement(sql);pstmt.setInt(1,1);pstmt.setBinaryStream(2,in,(int)f.length());if(pstmt.executeUpdate()>0){System.out.println("OK");mit();conn.setAutoCommit(true);}} catch (Exception e) {try {conn.rollback();} catch (SQLException e1) {e1.printStackTrace();}e.printStackTrace();} finally{try {in.close();pstmt.close();conn.close();} catch (IOException e) {e.printStackTrace();} catch (SQLException e) {e.printStackTrace();}}}}方法二：（古老）1：设置不自动提交。
JDBC中操作Blob、Clob等对象
String line = null ;
while(null != (line = br.readLine())){
System.out.println(line);
//将其输出至屏幕，实际你可以按照需要处理
}
is.close();
java.sql.Blob ablob = rs.getBlob(2);
//和提取一般对象一样 //特殊的，对于与得到 Clob 的流 //这是得到 Clob、Blob 流的第一种方法
2
//以下是对流进行处理的过程。Clob 本身是包含大字符的对象 //顺其自然，以下是使用 java IO 中读取字符流的方法读取它
BufferedReader br = new BufferedReader(new InputStreamReader(is));
s.executeUpdate("CREATE TABLE documents(id INT, text CLOB(64 K) , photo Blob(1440 K))");
//以上就是创建包含 Clob 和 Blob 对象的表的过程 //和将一般的字段差不多，后面括号中代表该对象的大小，其语法为：
简单吧！关键是一定要对基本流程了解！对其原来了解！对什么 IO、net、Collections….基础知识必须 精通！
运行的结果是本程序的源代码会存储在数据库中，同时会在屏幕上显式，源代码目录的子目录“11” 下的 1.jpg 会存储到数据库中，同时也会在当前目录下有一个该文件的副本。（图片的相对位置一定要搞 对哦！）
利用spring的jdbcTemplate处理blob、clob
所谓CLOB 可以看成是文本文，所谓BLOB可以看成是图片文件假设在mysql数据库上有以下表：create table test(id int primary key,txt TEXT,image BLOB);//写入假设现在分别读取一个文字文件和二进制文件，并想将之存储到数据库中，则可以使用JdbcTemplate 如：final File binaryFile=new File(";wish.jpg";);final File txtFile=new File(";test.txt";);final InputStream is=new FileInputStream(binaryFile);final Reader reader=new FileReader(txtFile);JdbcTemplate jdbcTemplate=new JdbcTemplate(dataSource);final LobHandler lobHandler=new DefaultLobHandler();jdbcTemplate.execute(";insert into test (txt,image) values (?,?)";,new AbstractLobCreatingPreparedStatementCallBack(lobHandler)...{protected void setValues(PreoparedStatement pstmt,LobCreator lobCreator)...{lobCreator.setClobAsCharactoerStream(pstmt,1,reader,(int)textFile.length());lobCreator.setBlobAsBinaryStream(pstmt,2,is,(int)binaryFile.length());}});reader.close();is.close();//读取在建立AbstractLobCreatingPreparedStatementCallBack对象时候，需要一个lobHandler 实例，对于一般的数据库，采用DefaultLobHandler足以,对于Oracle特定的lob处理，可以使用OracleLobHandler如果是讲数据从数据库中读取出来并另存在未见，可以使用下面的程序final Writer writer=new FileWriter(";test_back.txt";);final OutputStream os=new FileOutputStream(new File(";wish_bak.jpg";)); jdbcTemplate.query(";select txt,image from test where id=?,new AbstractLobStreamingResultSetExtractor(){protected void streamData(ResultSet rs) throws SQLException,IOException,DataAccessException...{FileCopyUtils.copy(lobHandler.getClobAsCharacterStream(rs,1),writer);FileCopyUtils.copy(lobHandler.getBlobAsBinaryStream(rs,2),os);}});writer.close();os.close();这里使用FileCopyUtils的copy方法，将lobHandler取得的串流直接转接给文件输出FileWriter，FileOutputStream对象。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
JDBC操作LOB字段详解在Oracle中，lob类型主要是指：CLOB和BLOB，这两个类型都是用来存储大量数据而设计的。
Blob：是指二进制大对象也就是英文Binary Large Object的所写，是用来存储大量二进制数据。
Clob：是指大字符对象也就是英文Character Large Object的所写，用来存储大量文本数据。
一：操作CLOB(1)数据库表结构如下：create table CLOB_TEST(ID V ARCHAR2(5) not null,CONTENT CLOB)（2）插入CLOB方法一：第一步插入一个空值，第二步锁住此行，更新clob字段public static void insertClob(Connection conn,String data) throws Exception{//这句话如没有，9i的驱动下会报java.sql.SQLException: ORA-01002: 读取违反顺序的异常。
conn.setAutoCommit(false);//插入一个空CLOBString insertSql = "insert into clob_test(id,content) values('1',empty_clob())";//查询插入的空CLOBString selectSql = "select content from clob_test where id = '1' for update"; PreparedStatement stmt = conn.prepareStatement(insertSql);stmt.executeUpdate();stmt.close();// lock this linePreparedStatement pstmt = conn.prepareStatement(selectSql);ResultSet rs = pstmt.executeQuery();if(rs.next()){oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob(1);//为CLOB写信息BufferedWriter out = new BufferedWriter(clob.getCharacterOutputStream());BufferedReader in = new BufferedReader(new FileReader(data));int c;while ((c=in.read())!=-1) {out.write(c);}in.close();out.close();}mit();pstmt.close();}注：此方法在jdk1.4、jdk50、jdk6.0和Oracle9i、Oracle10g、Oracle11g驱动下测试通过！方法二：通过setString方法public static void insertClob(Connection conn,String data) throws Exception{String insertSql = "insert into clob_test(id,content) values('1',?)";PreparedStatement stmt = conn.prepareStatement(insertSql);stmt.setString(1, data);stmt.executeUpdate();stmt.close();conn.close();}注：由于在Oracle9i的驱动下，setString 有2000字符长度的限制，故这个方法只适合Oracle10g以上的驱动（Oracle11g驱动+JDK6.0也测试通过）。
方法三：通过setClob方法public static void insertClob(Connection conn,String filePath) throws Exception{String insertSql = "insert into clob_test(id,content) values('1',?)";PreparedStatement stmt = conn.prepareStatement(insertSql);stmt.setClob(1, new FileReader(filePath));stmt.executeUpdate();stmt.close();mit();}注：由于setClob(int parameterIndex, Reader reader)这个方法是JDBC4.0规范刚加的内容，是以流的方式为CLOB赋值的。
并且Oracle9i驱动、Oracle10g驱动、JDK1.4、JDK1.5是基于JDBC3.0规范的，只有Oracle11g驱动+JDK6.0才是基于JDBC4.0规范的，所以目前这个方法只适合Oracle11g驱动（ojdbc6.jar）+JDK6.0！(3)读取CLOB方法一：public static String readClob(Connection conn) throws Exception{PreparedStatement stmt = conn.prepareStatement("select * from clob_test where id = '1'"); ResultSet rs = stmt.executeQuery();String str="";StringBuffer sb = new StringBuffer("");while(rs.next()){Clob clob = rs.getClob("content");Reader is = clob.getCharacterStream();BufferedReader br = new BufferedReader(is);str = br.readLine();while (str != null){sb.append(str);str = br.readLine();}}return sb.toString();}方法二：public static String readClob(Connection conn) throws Exception{PreparedStatement stmt = conn.prepareStatement("select * from clob_test where id = '1'"); ResultSet rs = stmt.executeQuery();String str="";while(rs.next()){str = rs.getString("content");}return str;}注：由于在Oracle9i的驱动下，rs.getString 返回为null，所以此方法只适合Oracle10g及其以上驱动。
二：操作BLOB(1)数据库表结构如下：create table BLOB_TEST(ID V ARCHAR2(5) not null,CONTENT BLOB)（2）插入BLOB方法一：第一步插入一个空值，第二步锁住此行，更新blob字段public static void writeBlob(Connection con,String filePath) throws Exception{ FileInputStream fis = null;PreparedStatement psm = null;File file = new File(filePath);psm = con.prepareStatement("insert into blob_test(id,content) values('2',empty_blob())"); psm.executeUpdate();psm = con.prepareStatement("select content from blob_test where id ='2' for update"); ResultSet rs = psm.executeQuery();if(rs.next()){oracle.sql.BLOB blob = (oracle.sql.BLOB)rs.getBlob(1);FileInputStream fin = new FileInputStream(file);OutputStream out = blob.getBinaryOutputStream();int count = -1, total = 0;byte[] data = new byte[blob.getBufferSize()];while ((count = fin.read(data)) != -1){out.write(data, 0, count);}out.flush();out.close();}}方法二：通过setBinaryStream方法public static void writeBlob(Connection con,String filePath) throws Exception{ FileInputStream fis = null;PreparedStatement psm = null;File file = new File(filePath);try {fis = new FileInputStream(file);psm = con.prepareStatement("insert into blob_test(id,content) values('2',?)");psm.setBinaryStream(1, fis, fis.available());psm.executeUpdate();}finally{if(fis != null) fis.close();psm.close();con.close();}}方法三：通过setBlob(int parameterIndex, InputStream inputStream)方法public static void writeBlob(Connection con,String filePath) throws Exception{ FileInputStream fis = null;PreparedStatement psm = null;File file = new File(filePath);try {fis = new FileInputStream(file);psm = con.prepareStatement("insert into blob_test(id,content) values('2',?)");psm.setBlob(1, fis);psm.executeUpdate();}finally{if(fis != null) fis.close();psm.close();con.close();}注：由于setBlob(int parameterIndex, InputStream inputStream)这个方法是JDBC4.0规范刚加的内容，是以流的方式为BLOB赋值的。