1 CREATE TABLE TEST_CLOB ( ID NUMBER(3), CLOBCOL CLOB)

2

3 CREATE TABLE TEST_BLOB ( ID NUMBER(3), BLOBCOL BLOB)

1 oracleDb_Driver=oracle.jdbc.driver.OracleDriver

2 oracleDb_Url=jdbc:oracle:thin:@localhost:1521:GACL

3 oracleDb_UserName=GACL_XDP

4 oracleDb_Password=P

 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

13     private static String oracleDb_Driver = null;

14     private static String oracleDb_Url = null;

15     private static String oracleDb_UserName = null;

16     private static String oracleDb_Password = null;

17

18     static{

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     }

41

42     /**

43     * @Method: getOracleConnection

44     * @Description: 获取Oracle数据库连接对象

45     * @Anthor:孤傲苍狼

46     *

47     * @return Connection数据库连接对象

48     * @throws SQLException

49     */

50     public static Connection getOracleConnection() throws SQLException{

51         return DriverManager.getConnection(oracleDb_Url, oracleDb_UserName,oracleDb_Password);

52     }

53

54     /**

55     * @Method: release

56     * @Description: 释放资源，

57     *     要释放的资源包括Connection数据库连接对象，负责执行SQL命令的Statement对象，存储查询结果的ResultSet对象

58     * @Anthor:孤傲苍狼

59     *

60     * @param conn

61     * @param st

62     * @param rs

63     */

64     public 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 }

  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: JdbcOperaOracleClob

 15 * @Description:Oracle中字符型大型对象（Character Large Object）数据处理

 16 * @author: 孤傲苍狼

 17 * @date: 2014-10-7 下午3:53:19

 18 *

 19 */

 20 public class JdbcOperaOracleClob {

 21

 22     /**

 23     CREATE TABLE TEST_CLOB ( ID NUMBER(3), CLOBCOL CLOB)

 24      */

 25     /**

 26     * @Method: clobInsert

 27     * @Description:往数据库中插入一个新的CLOB对象

 28     * @Anthor:孤傲苍狼

 29     *

 30     * @throws Exception

 31     */

 32     @Test

 33     public 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             conn.commit();

 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     }

 83

 84     /**

 85     * @Method: clobRead

 86     * @Description: CLOB对象读取

 87     * @Anthor:孤傲苍狼

 88     *

 89     * @throws Exception

 90     */

 91     @Test

 92     public 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     }

127

128     /**

129     * @Method: clobModify

130     * @Description:修改CLOB对象（是在原CLOB对象基础上进行覆盖式的修改）

131     * @Anthor:孤傲苍狼

132     *

133     * @throws Exception

134     */

135     @Test

136     public 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             conn.commit();

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: clobReplace

182     * @Description:替换CLOB对象（将原CLOB对象清除，换成一个全新的CLOB对象）

183     * @Anthor:孤傲苍狼

184     *

185     * @throws Exception

186     */

187     @Test

188     public 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             conn.commit();

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 }

  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: JdbcOperaOracleBlob

 14 * @Description:Oracle中大数据处理

 15 * @author: 孤傲苍狼

 16 * @date: 2014-10-7 下午3:53:19

 17 *

 18 */

 19 public class JdbcOperaOracleBlob {

 20

 21     /**

 22      * @Method: blobInsert

 23      * @Description: 向数据库中插入一个新的BLOB对象

 24      * @Anthor:孤傲苍狼

 25      *

 26      * @throws Exception

 27      */

 28     @Test

 29     public 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             conn.commit();

 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: blobModify

 80      * @Description:修改BLOB对象（是在原BLOB对象基础上进行覆盖式的修改）

 81      * @Anthor:孤傲苍狼

 82      *

 83      * @throws Exception

 84      */

 85     @Test

 86     public 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             conn.commit();

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: blobReplace

131      * @Description:替换BLOB对象（将原BLOB对象清除，换成一个全新的BLOB对象）

132      * @Anthor:孤傲苍狼

133      *

134      * @throws Exception

135      */

136     @Test

137     public 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             conn.commit();

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: blobRead

187      * @Description:BLOB对象读取

188      * @Anthor:孤傲苍狼

189      *

190      * @throws Exception

191      */

192     @Test

193     public 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             conn.commit();

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 }