JavaWeb学习总结(三十五)——使用JDBC处理Oracle大数据

一、Oracle中大数据处理

在Oracle中，LOB(Large Object，大型对象)类型的字段现在用得越来越多了。因为这种类型的字段，容量大(最多能容纳4GB的数据)，且一个表中可以有多个这种类型的字段，很灵活，适用于数据 量非常大的业务领域(如图象、档案等)。

LOB类型分为BLOB和CLOB两种：BLOB即二进制大型对象(Binary Large Object)，适用于存贮非文本的字节流数据(如程序、图象、影音等)。而CLOB，即字符型大型对象(Character Large Object)，则与字符集相关，适于存贮文本型的数据(如历史档案、大部头著作等)。

二、搭建测试环境

2.1、建立两个测试用的数据库表

建表SQL语句为：

1 CREATE TABLE TEST_CLOB ( ID NUMBER(3), CLOBCOL CLOB)2 3 CREATE TABLE TEST_BLOB ( ID NUMBER(3), BLOBCOL BLOB)

2.2、搭建测试项目架构

2.3、编写db.properties配置文件

1 oracleDb_Driver=oracle.jdbc.driver.OracleDriver2 oracleDb_Url=jdbc:oracle:thin:@localhost:1521:GACL3 oracleDb_UserName=GACL_XDP4 oracleDb_Password=P

2.4、编写JdbcUtils工具类

1 package me.gacl.utils;2 3 import java.io.InputStream;4 import java.sql.Connection;5 import java.sql.DriverManager;6 import java.sql.ResultSet;7 import java.sql.SQLException;8 import java.sql.Statement;9 import java.util.Properties;10 11 public class JdbcUtils {12 13private static String oracleDb_Driver = null;14private static String oracleDb_Url = null;15private static String oracleDb_UserName = null;16private static String oracleDb_Password = null;1718static{19 try{20 //读取db.properties文件中的数据库连接信息21 InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");22 Properties prop = new Properties();23 prop.load(in);24 25 //获取数据库连接驱动26 oracleDb_Driver = prop.getProperty("oracleDb_Driver");27 //获取数据库连接URL地址28 oracleDb_Url = prop.getProperty("oracleDb_Url");29 //获取数据库连接用户名30 oracleDb_UserName = prop.getProperty("oracleDb_UserName");31 //获取数据库连接密码32 oracleDb_Password = prop.getProperty("oracleDb_Password");33 34 //加载数据库驱动35 Class.forName(oracleDb_Driver);36 37 }catch (Exception e) {38 throw new ExceptionInInitializerError(e);39 }40}4142/**43* @Method: getOracleConnection44* @Description: 获取Oracle数据库连接对象45* @Anthor:孤傲苍狼46*47* @return Connection数据库连接对象48* @throws SQLException49*/ 50public static Connection getOracleConnection() throws SQLException{51 return DriverManager.getConnection(oracleDb_Url, oracleDb_UserName,oracleDb_Password);52}5354/**55* @Method: release56* @Description: 释放资源，57*要释放的资源包括Connection数据库连接对象，负责执行SQL命令的Statement对象，存储查询结果的ResultSet对象58* @Anthor:孤傲苍狼59*60* @param conn61* @param st62* @param rs63*/ 64public static void release(Connection conn,Statement st,ResultSet rs){65 if(rs!=null){66 try{67 //关闭存储查询结果的ResultSet对象68 rs.close();69 }catch (Exception e) {70 e.printStackTrace();71 }72 rs = null;73 }74 if(st!=null){75 try{76 //关闭负责执行SQL命令的Statement对象77 st.close();78 }catch (Exception e) {79 e.printStackTrace();80 }81 }82 83 if(conn!=null){84 try{85 //关闭Connection数据库连接对象86 conn.close();87 }catch (Exception e) {88 e.printStackTrace();89 }90 }91}92 }

三、JDBC处理Oracle大数据

3.1、JDBC处理CLOB数据

1 package me.gacl.demo;2 3 import java.io.BufferedReader;4 import java.io.BufferedWriter;5 import java.io.FileReader;6 import java.io.FileWriter;7 import java.sql.Connection;8 import java.sql.PreparedStatement;9 import java.sql.ResultSet;10 import org.junit.Test;11 import me.gacl.utils.JdbcUtils;12 13 /**14 * @ClassName: JdbcOperaOracleClob15 * @Description:Oracle中字符型大型对象(Character Large Object)数据处理16 * @author: 孤傲苍狼17 * @date: -10-7 下午3:53:1918 *19 */ 20 public class JdbcOperaOracleClob {21 22/**23CREATE TABLE TEST_CLOB ( ID NUMBER(3), CLOBCOL CLOB)24*/25/**26* @Method: clobInsert27* @Description:往数据库中插入一个新的CLOB对象28* @Anthor:孤傲苍狼29*30* @throws Exception31*/ 32@Test33public void clobInsert() throws Exception {34 Connection conn = null;35 PreparedStatement stmt = null;36 ResultSet rs = null;37 conn = JdbcUtils.getOracleConnection();38 boolean defaultCommit = conn.getAutoCommit();39 /*开启事务，设定不自动提交 */40 conn.setAutoCommit(false);41 try {42 /* 插入一个空的CLOB对象 */43 String sql = "INSERT INTO TEST_CLOB VALUES (?, EMPTY_CLOB())";44 stmt = conn.prepareStatement(sql);45 stmt.setInt(1, 1);46 stmt.executeUpdate();47 /* 查询此CLOB对象并锁定 */48 sql = "SELECT CLOBCOL FROM TEST_CLOB WHERE ID=? FOR UPDATE";49 stmt = conn.prepareStatement(sql);50 stmt.setInt(1, 1);51 rs = stmt.executeQuery();52 if (rs.next()) {53 /* 取出此CLOB对象 */54 oracle.sql.CLOB clob = (oracle.sql.CLOB) rs.getClob("CLOBCOL");55 /* 向CLOB对象中写入数据 */56 BufferedWriter out = new BufferedWriter(clob.getCharacterOutputStream());57 //这种方式获取的路径，其中的空格会被使用“%20”代替58 String path = JdbcOperaClob.class.getClassLoader().getResource("data.txt").getPath();59 //将“%20”替换回空格60 path = path.replaceAll("%20", " ");61 BufferedReader in = new BufferedReader(new FileReader(path));62 int c;63 while ((c = in.read()) != -1) {64 out.write(c);65 }66 in.close();67 out.close();68 }69 /* 正式提交 */70 mit();71 System.out.println("插入成功");72 } catch (Exception ex) {73 /* 出错回滚 */74 conn.rollback();75 throw ex;76 }finally{77 /* 恢复原提交状态 */78 conn.setAutoCommit(defaultCommit);79 JdbcUtils.release(conn,stmt,rs);80 }81 82}8384/**85* @Method: clobRead86* @Description: CLOB对象读取87* @Anthor:孤傲苍狼88*89* @throws Exception90*/91@Test92public void clobRead() throws Exception {93 Connection conn = null;94 PreparedStatement stmt = null;95 ResultSet rs = null;96 conn = JdbcUtils.getOracleConnection();97 boolean defaultCommit = conn.getAutoCommit();98 conn.setAutoCommit(false);99 try {100 /* 查询CLOB对象 */101 String sql = "SELECT * FROM TEST_CLOB WHERE ID=?";102 stmt = conn.prepareStatement(sql);103 stmt.setInt(1, 1);104 rs = stmt.executeQuery();105 if (rs.next()) {106 /* 获取CLOB对象 */107 oracle.sql.CLOB clob = (oracle.sql.CLOB) rs.getClob("CLOBCOL");108 /* 以字符形式输出 */109 BufferedReader in = new BufferedReader(clob.getCharacterStream());110 BufferedWriter out = new BufferedWriter(new FileWriter("D:\\2.txt"));111 int c;112 while ((c = in.read()) != -1) {113 out.write(c);114 }115 out.close();116 in.close();117 }118 } catch (Exception ex) {119 conn.rollback();120 throw ex;121 }finally{122 /* 恢复原提交状态 */123 conn.setAutoCommit(defaultCommit);124 JdbcUtils.release(conn,stmt,rs);125 }126}127128/**129* @Method: clobModify130* @Description:修改CLOB对象(是在原CLOB对象基础上进行覆盖式的修改)131* @Anthor:孤傲苍狼132*133* @throws Exception134*/ 135@Test136public void clobModify() throws Exception {137 Connection conn = null;138 PreparedStatement stmt = null;139 ResultSet rs = null;140 conn = JdbcUtils.getOracleConnection();141 boolean defaultCommit = conn.getAutoCommit();142 // 开启事务143 conn.setAutoCommit(false);144 try {145 /* 查询CLOB对象并锁定 */146 String sql = "SELECT CLOBCOL FROM TEST_CLOB WHERE ID=? FOR UPDATE";147 stmt = conn.prepareStatement(sql);148 stmt.setInt(1, 1);149 rs = stmt.executeQuery();150 if (rs.next()) {151 /* 获取此CLOB对象 */152 oracle.sql.CLOB clob = (oracle.sql.CLOB) rs.getClob("CLOBCOL");153 /* 进行覆盖式修改 */154 BufferedWriter out = new BufferedWriter(clob.getCharacterOutputStream());155 // 这种方式获取的路径，其中的空格会被使用“%20”代替156 String path = JdbcOperaClob.class.getClassLoader().getResource("data2.txt").getPath();157 // 将“%20”替换回空格158 path = path.replaceAll("%20", " ");159 BufferedReader in = new BufferedReader(new FileReader(path));160 int c;161 while ((c = in.read()) != -1) {162 out.write(c);163 }164 in.close();165 out.close();166 }167 /*提交事务 */168 mit();169 } catch (Exception ex) {170 /*出错回滚事务 */171 conn.rollback();172 throw ex;173 }finally{174 /*恢复原提交状态 */175 conn.setAutoCommit(defaultCommit);176 JdbcUtils.release(conn,stmt,rs);177 }178}179 180/**181* @Method: clobReplace182* @Description:替换CLOB对象(将原CLOB对象清除，换成一个全新的CLOB对象)183* @Anthor:孤傲苍狼184*185* @throws Exception186*/ 187@Test188public void clobReplace() throws Exception {189 Connection conn = null;190 PreparedStatement stmt = null;191 ResultSet rs = null;192 conn = JdbcUtils.getOracleConnection();193 boolean defaultCommit = conn.getAutoCommit();194 // 开启事务195 conn.setAutoCommit(false);196 try {197 /* 清空原CLOB对象 */198 String sql = "UPDATE TEST_CLOB SET CLOBCOL=EMPTY_CLOB() WHERE ID=?";199 stmt = conn.prepareStatement(sql);200 stmt.setInt(1, 1);201 stmt.executeUpdate();202 203 /* 查询CLOB对象并锁定 */204 sql = "SELECT CLOBCOL FROM TEST_CLOB WHERE ID=? FOR UPDATE";205 stmt = conn.prepareStatement(sql);206 stmt.setInt(1, 1);207 rs = stmt.executeQuery();208 if (rs.next()) {209 /* 获取此CLOB对象 */210 oracle.sql.CLOB clob = (oracle.sql.CLOB) rs.getClob("CLOBCOL");211 /* 更新数据 */212 BufferedWriter out = new BufferedWriter(clob.getCharacterOutputStream());213 // 这种方式获取的路径，其中的空格会被使用“%20”代替214 String path = JdbcOperaClob.class.getClassLoader().getResource("db.properties").getPath();215 // 将“%20”替换回空格216 path = path.replaceAll("%20", " ");217 BufferedReader in = new BufferedReader(new FileReader(path));218 int c;219 while ((c = in.read()) != -1) {220 out.write(c);221 }222 in.close();223 out.close();224 }225 /* 正式提交 */226 mit();227 } catch (Exception ex) {228 /* 出错回滚 */229 conn.rollback();230 throw ex;231 } finally {232 /* 恢复原提交状态 */233 conn.setAutoCommit(defaultCommit);234 JdbcUtils.release(conn, stmt, rs);235 }236}237 }

3.2、JDBC处理BLOB数据

Oracle定义了一个BLOB字段用于保存二进制数据，但这个字段并不能存放真正的二进制数据，只能向这个字段存一个指针，然后把数据放到指针所指向的Oracle的LOB段中， LOB段是在数据库内部表的一部分。因而在操作Oracle的Blob之前，必须获得指针(定位器)才能进行Blob数据的读取和写入。

如何获得表中的Blob指针呢？ 可以先使用insert语句向表中插入一个空的blob(调用oracle的函数empty_blob())，这将创建一个blob的指针，然后再把这个empty的blob的指针查询出来，这样就可得到BLOB对象，从而读写blob数据了。

1、插入空blob：insert into testblob(id,image) values(?,empty_blob())

2、获得blob的cursor：

select image from testblob where id=? for update 注意: 必 须加for update锁定该行，直至该行被修改完毕，保证不产生并发冲突。

Blob b = rs.getBlob("image");

3、利用 io和获取到的cursor往数据库读写数据

注意：以上操作需开启事务。

BLOB对象的存取范例

1 package me.gacl.demo;2 3 import java.io.BufferedInputStream;4 import java.io.BufferedOutputStream;5 import java.io.FileOutputStream;6 import java.sql.Connection;7 import java.sql.PreparedStatement;8 import java.sql.ResultSet;9 import org.junit.Test;10 import me.gacl.utils.JdbcUtils;11 12 /**13 * @ClassName: JdbcOperaOracleBlob14 * @Description:Oracle中大数据处理15 * @author: 孤傲苍狼16 * @date: -10-7 下午3:53:1917 *18 */ 19 public class JdbcOperaOracleBlob {20 21/**22* @Method: blobInsert23* @Description: 向数据库中插入一个新的BLOB对象24* @Anthor:孤傲苍狼25* 26* @throws Exception27*/28@Test29public void blobInsert() throws Exception {30 Connection conn = null;31 PreparedStatement stmt = null;32 ResultSet rs = null;33 boolean defaultCommit = true;34 try {35 conn = JdbcUtils.getOracleConnection();36 //得到数据库事务处理的默认提交方式37 defaultCommit = conn.getAutoCommit();38 //1、开启事务39 conn.setAutoCommit(false);40 //2、插入一个空的BLOB对象41 String sql = "INSERT INTO TEST_BLOB VALUES (?, EMPTY_BLOB())";42 stmt = conn.prepareStatement(sql);43 stmt.setInt(1, 1);44 stmt.executeUpdate();45 //3、查询此BLOB对象并锁定。注意: 必 须加for update锁定该行，直至该行被修改完毕，保证不产生并发冲突46 sql = "SELECT BLOBCOL FROM TEST_BLOB WHERE ID=? FOR UPDATE";47 stmt = conn.prepareStatement(sql);48 stmt.setInt(1, 1);49 rs = stmt.executeQuery();50 if (rs.next()) {51 //4、取出此BLOB对象 ，并强制转换成Oracle的BLOB对象52 oracle.sql.BLOB blob = (oracle.sql.BLOB) rs.getBlob("BLOBCOL");53 //5、使用IO向BLOB对象中写入数据54 BufferedOutputStream out = new BufferedOutputStream(blob.getBinaryOutputStream());55 BufferedInputStream in = new BufferedInputStream(JdbcOperaOracleBlob.class.getClassLoader().getResourceAsStream("01.jpg"));56 int c;57 while ((c = in.read()) != -1) {58 out.write(c);59 }60 in.close();61 out.close();62 }63 //6、提交事务64 mit();65 } catch (Exception ex) {66 //7、出错回滚事务67 conn.rollback();68 throw ex;69 } finally {70 //8、恢复数据库事务处理的默认提交方式71 conn.setAutoCommit(defaultCommit);72 //释放资源73 JdbcUtils.release(conn, stmt, rs);74 }75 76}77 78/**79* @Method: blobModify80* @Description:修改BLOB对象(是在原BLOB对象基础上进行覆盖式的修改)81* @Anthor:孤傲苍狼82* 83* @throws Exception84*/85@Test86public void blobModify() throws Exception {87 Connection conn = null;88 PreparedStatement stmt = null;89 ResultSet rs = null;90 boolean defaultCommit = true;91 try {92 conn = JdbcUtils.getOracleConnection();93 //得到数据库事务处理的默认提交方式94 defaultCommit = conn.getAutoCommit();95 //1、开启事务96 conn.setAutoCommit(false);97 //2、查询此BLOB对象并锁定。注意: 必 须加for update锁定该行，直至该行被修改完毕，保证不产生并发冲突98 String sql = "SELECT BLOBCOL FROM TEST_BLOB WHERE ID=? FOR UPDATE";99 stmt = conn.prepareStatement(sql);100 stmt.setInt(1, 1);101 rs = stmt.executeQuery();102 if (rs.next()) {103 //3、取出此BLOB对象 ，并强制转换成Oracle的BLOB对象104 oracle.sql.BLOB blob = (oracle.sql.BLOB) rs.getBlob("BLOBCOL");105 //4、使用IO向BLOB对象中写入数据106 BufferedOutputStream out = new BufferedOutputStream(blob.getBinaryOutputStream());107 BufferedInputStream in = new BufferedInputStream(JdbcOperaOracleBlob.class.getClassLoader().getResourceAsStream("02.jpg"));108 int c;109 while ((c = in.read()) != -1) {110 out.write(c);111 }112 in.close();113 out.close();114 }115 //5、提交事务116 mit();117 } catch (Exception ex) {118 //6、出错回滚事务119 conn.rollback();120 throw ex;121 } finally {122 //8、恢复数据库事务处理的默认提交方式123 conn.setAutoCommit(defaultCommit);124 //释放资源125 JdbcUtils.release(conn, stmt, rs);126 }127}128 129/**130* @Method: blobReplace131* @Description:替换BLOB对象(将原BLOB对象清除，换成一个全新的BLOB对象)132* @Anthor:孤傲苍狼133* 134* @throws Exception135*/136@Test137public void blobReplace() throws Exception {138 Connection conn = null;139 PreparedStatement stmt = null;140 ResultSet rs = null;141 boolean defaultCommit = true;142 try {143 conn = JdbcUtils.getOracleConnection();144 //得到数据库事务处理的默认提交方式145 defaultCommit = conn.getAutoCommit();146 //1、开启事务147 conn.setAutoCommit(false);148 //2、清空原BLOB对象149 String sql = "UPDATE TEST_BLOB SET BLOBCOL=EMPTY_BLOB() WHERE ID=?";150 stmt = conn.prepareStatement(sql);151 stmt.setInt(1, 1);152 stmt.executeUpdate();153 //3、查询此BLOB对象并锁定。注意: 必 须加for update锁定该行，直至该行被修改完毕，保证不产生并发冲突154 sql = "SELECT BLOBCOL FROM TEST_BLOB WHERE ID=? FOR UPDATE";155 stmt = conn.prepareStatement(sql);156 stmt.setInt(1, 1);157 rs = stmt.executeQuery();158 if (rs.next()) {159 //4、取出此BLOB对象 ，并强制转换成Oracle的BLOB对象160 oracle.sql.BLOB blob = (oracle.sql.BLOB) rs.getBlob("BLOBCOL");161 //5、使用IO向BLOB对象中写入数据162 BufferedOutputStream out = new BufferedOutputStream(blob.getBinaryOutputStream());163 BufferedInputStream in = new BufferedInputStream(JdbcOperaOracleBlob.class.getClassLoader().getResourceAsStream("01.jpg"));164 int c;165 while ((c = in.read()) != -1) {166 out.write(c);167 }168 in.close();169 out.close();170 }171 //6、提交事务172 mit();173 } catch (Exception ex) {174 //7、出错回滚事务175 conn.rollback();176 throw ex;177 } finally {178 //8、恢复数据库事务处理的默认提交方式179 conn.setAutoCommit(defaultCommit);180 //释放资源181 JdbcUtils.release(conn, stmt, rs);182 }183}184 185/**186* @Method: blobRead187* @Description:BLOB对象读取188* @Anthor:孤傲苍狼189* 190* @throws Exception191*/192@Test193public void blobRead() throws Exception {194 Connection conn = null;195 PreparedStatement stmt = null;196 ResultSet rs = null;197 boolean defaultCommit = true;198 try {199 conn = JdbcUtils.getOracleConnection();200 //得到数据库事务处理的默认提交方式201 defaultCommit = conn.getAutoCommit();202 //1、开启事务203 conn.setAutoCommit(false);204 //2、查询BLOB对象205 String sql = "SELECT BLOBCOL FROM TEST_BLOB WHERE ID=?";206 stmt = conn.prepareStatement(sql);207 stmt.setInt(1, 1);208 rs = stmt.executeQuery();209 if (rs.next()) {210 //3、取出此BLOB对象 ，并强制转换成Oracle的BLOB对象211 oracle.sql.BLOB blob = (oracle.sql.BLOB) rs.getBlob("BLOBCOL");212 //4、以二进制流的形式输出213 BufferedOutputStream out = new BufferedOutputStream(new FileOutputStream("D:/1.jpg"));214 BufferedInputStream in = new BufferedInputStream(blob.getBinaryStream());215 int c;216 while ((c = in.read()) != -1) {217 out.write(c);218 }219 in.close();220 out.close();221 }222 //5、提交事务223 mit();224 } catch (Exception ex) {225 //6、出错回滚事务226 conn.rollback();227 throw ex;228 } finally {229 //8、恢复数据库事务处理的默认提交方式230 conn.setAutoCommit(defaultCommit);231 //释放资源232 JdbcUtils.release(conn, stmt, rs);233 }234 235}236 }

四、使用JDBC处理Oracle大数据总结

通过JDBC操纵Oracle数据库的LOB字段，不外乎插入、修改、替换、读取四种方式，掌握起来并不难。观察上述程序对LOB类型字段的存取，我们可以看出，较之其它类型字段，有下面几个显著不同的特点：

1、必须取消自动提交。

存取操作开始前，必须用setAutoCommit(false)取消自动提交。其它类型字段则无此特殊要求。这是因为存取LOB类型字段时，通常要进行多次操作可以完成。不这样的话，Oracle将抛出“读取违反顺序”的错误。

2、插入方式不同。

LOB数据不能象其它类型数据一样直接插入(INSERT)。插入前必须先插入一个空的LOB对象，CLOB类型 的空对象为EMPTY_CLOB()，BLOB类型的空对象为EMPTY_BLOB()。之后通过SELECT命令查询得到先前插入的记录并锁定，继而将 空对象修改为所要插入的LOB对象。

3、修改方式不同。

其它类型的字段修改时，用UPDATE … SET…命令即可。而LOB类型字段，则只能用SELECT … FOR UPDATE命令将记录查询出来并锁定，然后才能修改。且修改也有两种改法：一是在原数据基础上的修改(即覆盖式修改)，执行SELECT … FOR UPDATE后再改数据；二是替换(先将原数据清掉，再修改)，先执行UPDATE命令将LOB字段之值设为空的LOB对象，然后进行第一种改法。建议使 用替换的方法，以实现与其它字段UPDATE操作后一样的效果。

4、存取时应使用由数据库JDBC驱动程序提供的LOB操作类。

对于Oracle数据库，应使用oracle.sql.CLOB和oracle.sql.BLOB。不使用由数据库JDBC驱动程序提供的LOB类时，程序运行时易于出现“抽象方法调用”的错误，这是因为JDBC所定义的java.sql.Clob与 java.sql.Blob接口，其中的一些方法并未在数据库厂家提供的驱动程序中真正实现。

5、存取手段与文件操作相仿。

对于BLOB类型，应用InputStream/OutputStream类，此类不进行编码转换，逐个字节存取。oracle.sql.BLOB类相应提供了getBinaryStream()和getBinaryOutputStream()两个方法，前一个 方法用于读取Oracle的BLOB字段，后一个方法用于将数据写入Oracle的BLOB字段。

对于CLOB类型，应用Reader/Writer类，此类进行编码转换。oracle.sql.CLOB类相应 提供了getCharacterStream()和getCharacterOutputStream()两个方法，前一个方法用于读取Oracle的 CLOB字段，后一个方法用于将数据写入Oracle的CLOB字段。

需要说明的是，为了大幅提高程序执行效率，对BLOB/CLOB字段的读写操作，应该使用缓冲操作类(带 Buffered前缀)，即：BufferedInputStream，BufferedOutputStream，BufferedReader，BufferedWriter。 例程中全部使用了缓冲操作类。

/xdp-gacl/p/4009399.html

如果觉得《JavaWeb学习总结(三十五)——使用JDBC处理Oracle大数据》对你有帮助，请点赞、收藏，并留下你的观点哦！