JDBC操作LOB字段详解

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函数时，需要注意指定要提取的数据的长度和偏移量。

详解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字段的处理⼀、与库如何处理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);}。

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列创建索引。

数据库执行脚本：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：设置不自动提交。

所谓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对象。