jdbc方式读取oracle的clob字段实例

织女

可能大家也都习惯了spring和hibernate对CLOB字段的处理，在spring中配置clob的oracle处理句柄，在hibernate中配置映射类型，然后就可以很轻松的以String 的形式处理大字段。 今天我做了个需求，需要以jdbc的方式从mysql导一些备份数据到oracle正式库，就查了一些资料，最后写了个例子： 1：首先：写个连接数据库的类，里面有返回mysq， oracle连接的方法 public Connection getConn(String flag){ Connection con=null; try { if(flag.equals("1")) { Class.forName(“oracle.jdbc.driver.OracleDriver”); con = DriverManager.getConnection(“jdbcracle:thinIP:1521:数据库名字”,"name","password"); } if(flag.equals("2")) { Class.forName("org.gjt.mm.mysql.Driver"); con = DriverManager.getConnection("jdbc:mysql://localhost/数据库名?user=用户名&password=密码&useUnicode=true&characterEncoding=GBK"); } } catch(Exception e) { e.printStackTrace(); } return con; } public Connection getConn(String flag){ Connection con=null; try { if(flag.equals("1")) { Class.forName(“oracle.jdbc.driver.OracleDriver”); con = DriverManager.getConnection(“jdbcracle:thinIP:1521:数据库名字”,"name","password"); } if(flag.equals("2")) { Class.forName("org.gjt.mm.mysql.Driver"); con = DriverManager.getConnection("jdbc:mysql://localhost/数据库名?user=用户名&password=密码&useUnicode=true&characterEncoding=GBK"); } } catch(Exception e) { e.printStackTrace(); } return con; } view plaincopy to clipboardprint? <RE class=csharp name="code"> public void setData() { conn = new Conn(); try { String sqlfrom = "select p.id,p.content from <A title=table href="http://www.alimama.com/membersvc/buyadzone/buy_ad_zone.htm?adzoneid=892989 " target=_blank>table</A> p order by p.id "; String sqlinsert = "insert into <A title=table href="http://www.alimama.com/membersvc/buyadzone/buy_ad_zone.htm?adzoneid=892989 " target=_blank>table</A> values(?,?)"; con = conn.getConn("2"); stmt = con.createStatement(); //从mysql取出大字段 rs = stmt.executeQuery(sqlfrom); con = conn.getConn("1"); PreparedStatement pstmt = con.prepareStatement(sqlinsert); //向oracle中插入大字段 int i = 0; while (rs.next()) { [1] [2] [3] pstmt.setInt(1, rs.getInt(1)); pstmt.setClob(2, <A title=oracle href="http://www.google.cn/search?sbi=股票&amp;q=股票&amp;sbb=搜索&amp;sa=搜索&amp;client=pub-6430022987645146&amp;forid=1&amp;prog=aff&amp;ie=GB2312&amp;oe=GB2312&amp;hl=zh-CN" target=_blank>oracle</A>.sql.CLOB.empty_lob()); pstmt.executeUpdate(); //插入时将大字段设为空 this.updateOne(con,rs.getInt(1),rs.getString(2)); // 这里调用然后更新这个大字段 } rs.close(); //关闭相关连接 pstmt.close(); stmt.close(); con.close(); } catch (Exception e) { e.printStackTrace(); try { con.rollback(); } catch (Exception e1) { <A title=system href="http://www.alimama.com/membersvc/buyadzone/buy_ad_zone.htm?adzoneid=892989 " target=_blank>system</A>.out.println("回滚出现异常！"); e1.printStackTrace(); } } } 3:该方法实现对应大字段记录的更新 <RE class=java name="code">public void updateOne(Connection con,int id, String content) { String str = "select t.content from <A title=table href="http://www.alimama.com/membersvc/buyadzone/buy_ad_zone.htm?adzoneid=892989 " target=_blank>table</A> t where t.id=" id " for update"; try { // 注意：存取操作开始前，必须用setAutoCommit(false)取消自动提交，否则Oracle将抛出“读取违反顺序”的错误。 con.setAutoCommit(false); stmt = con.createStatement(); ResultSet rs_clob = stmt.executeQuery(str); while ( rs_clob .next()) { /* 取出clob数据*/ <A title=oracle href="http://www.google.cn/search?sbi=股票&amp;q=股票&amp;sbb=搜索&amp;sa=搜索&amp;client=pub-6430022987645146&amp;forid=1&amp;prog=aff&amp;ie=GB2312&amp;oe=GB2312&amp;hl=zh-CN" target=_blank>oracle</A>.sql.CLOB clob = (oracle.sql.CLOB) rs_clob .getClob(1); /* 向clob中写入数据*/ clob.putString(1, content); } stmt.close(); con.commit(); con.setAutoCommit(true); con.close(); } catch (Exception e) { e.printStackTrace(); try { con.rollback(); } catch (Exception e1) { <A title=system href="http://www.alimama.com/membersvc/buyadzone/buy_ad_zone.htm?adzoneid=892989 " target=_blank>system</A>.out.println("回滚出现异常！"); e1.printStackTrace(); } } } </PRE> 现在就完成了一行记录的更新。 上一页 [1] [2] [3] 4：读clob字段以String 的形式返回（当然也可以将读到的内容写入文件，大家改一下就可以了） <RE class=java name="code">/** * 读clob字段 * @param con * @param id * @return */ public String readClob(Connection con,int id) { String content=""; try { con.setAutoCommit(false); stmt=con.createStatement(); ResultSet rs_clob=stmt.executeQuery("select t.content from <A title=table href="http://www.alimama.com/membersvc/buyadzone/buy_ad_zone.htm?adzoneid=892989 " target=_blank>table</A> t where t.id=" id); <A title=oracle href="http://www.google.cn/search?sbi=股票&amp;q=股票&amp;sbb=搜索&amp;sa=搜索&amp;client=pub-6430022987645146&amp;forid=1&amp;prog=aff&amp;ie=GB2312&amp;oe=GB2312&amp;hl=zh-CN" target=_blank>oracle</A>.sql.CLOB contents=null; while (rs_clob.next()) { // 取出CLOB对象 contents= (oracle.sql.CLOB) rs_clob.getClob("1"); } BufferedReader a = new BufferedReader(contents.getCharacterStream()); //以字符流的方式读入BufferedReader String str = ""; while ((str = a.readLine()) != null) { content = content.concat(str); //最后以String的形式得到 } con.commit(); /* BufferedWriter out = new BufferedWriter(new FileWriter("e:/test.txt")); out.write(content);//写入文件 out.close();*/ con.setAutoCommit(true); con.close(); }catch(Exception e) { <A title=system href="http://www.alimama.com/membersvc/buyadzone/buy_ad_zone.htm?adzoneid=892989 " target=_blank>system</A>.out.println("出现异常"); e.printStackTrace(); try { con.rollback(); } catch (Exception e1) { <A title=system href="http://www.alimama.com/membersvc/buyadzone/buy_ad_zone.htm?adzoneid=892989 " target=_blank>system</A>.out.println("回滚出现异常！"); e1.printStackTrace(); } } return content; } </PRE> </PRE> 上一页 [1] [2] [3]