Oracle处理CLOB超过4000入库问题 （2）

         接上一篇转载的Oracle处理CLOB超过4000入库问题，同事用我的写的代码，说仍出现问题，后面写了另一种方法，在这里做一个记录，以便以后学习。

先把需求简要提出来：

public String toInsertSql(){String sql="insert into TB_NEWS ("+"title,"+"publictime,"+"source,"+"abstract,"+"content,"+"url,"+"infodate,"+"author,"+"hits,"+"expand,"+"createtime,"+"modifytime"+") "+"values (";if(this.getTitle() == null){sql += "'',";}else{sql += "'"+this.getTitle().replaceAll("'", "")+"'"+",";}if(this.getPublictime() == null){sql += "'',";}else{sql += "'"+this.getPublictime().replaceAll("'", "")+"'"+",";}if(this.getSource() == null){sql += "'',";}else{sql += "'"+this.getSource().replaceAll("'", "")+"'"+",";}if(this.getAbstracts() == null){sql += "'',";}else{sql += "'"+this.getAbstracts().replaceAll("'", "")+"'"+",";}if(this.getContent() == null){sql += "'',";}else{//sql += "'"+this.getContent().replaceAll("'", "")+"'"+",";sql += "?,";}if(this.getUrl() == null){sql += "'',";}else{sql += "'"+this.getUrl().replaceAll("'", "")+"'"+",";}if(this.getInfodate() == null){sql += "'',";}else{sql += "'"+this.getInfodate().replaceAll("'", "")+"'"+",";}if(this.getAuthor() == null){sql += "'',";}else{sql += "'"+this.getAuthor().replaceAll("'", "")+"'"+",";}if(this.getHits() == null){sql += "'',";}else{sql += "'"+this.getHits().replaceAll("'", "")+"'"+",";}if(this.getExpand() == null){sql += "'',";}else{sql += "'"+this.getExpand().replaceAll("'", "")+"'"+",";}if(this.getCreatetime() == null){sql += "'',";}else{sql += "to_date('"+this.getCreatetime()+"','yyyymmdd HH24:mi:ss'),"; //to_date('20140214','yyyymmdd')}if(this.getModifytime() == null){sql += "''";}else{sql += "to_date('"+this.getCreatetime()+",'yyyymmdd HH24:mi:ss'),";}sql += ")";return sql;}

这是一个sql语句，content为大数据字段，content中注释的为方法一种使用的sql，没有注释的是方法二中使用的sql

方法一：参考上一篇转载的文章，代码如下：

public static void insert(String sql,String url,String content) {conn = getConnection();try {st = conn.createStatement(); // 创建用于执行静态sql语句的Statement对象 //st.executeUpdate(sql); // 执行插入操作的sql语句，并返回插入数据的个数 /** * clob数据太长超过4000，如下处理 * 1、插入一个空对象empty_clob() * 2、锁定数据行进行更新，注意“for update”语句 *///1、插入一个空对象empty_clob()sql = sql.replace("'"+content+"'", "empty_clob()");st.executeUpdate(sql);//2、锁定数据行进行更新，注意“for update”语句String sql2 = "select CONTENT from TB_NEWS where url='"+url+"' for update";//System.out.println(sql2);ResultSet rs = st.executeQuery(sql2);if(rs.next()){//得到java.sql.Clob对象后强制转换为oracle.sql.CLOBoracle.sql.CLOB clob = (CLOB) rs.getClob("content");outStream = clob.getCharacterOutputStream();//data是传入的字符串，定义：String datachar[] c = content.toCharArray();outStream.write(c, 0, c.length);}outStream.flush();conn.commit();} catch (Exception e) {if(e.getMessage().contains("唯一键约束"));else{logger.error("插入数据库出错，sql语句："+sql);e.printStackTrace();}} finally{try {if(outStream!=null){outStream.close();}if(rs!=null){rs.close();}if(st!=null){st.close();}if(conn != null){conn.close();}} catch (Exception e) {e.printStackTrace();}}}

方法二：使用setCharacterStream来处理大数据插入问题，代码如下：

public static void insert(String sql,String content) {conn = getConnection();PreparedStatement statement = null;try {st = conn.createStatement(); // 创建用于执行静态sql语句的Statement对象 statement=conn.prepareStatement(sql);Reader reader=new StringReader(content);statement.setCharacterStream(1, reader,content.length());statement.executeUpdate();conn.commit();} catch (Exception e) {logger.error("插入数据库出错，sql语句："+sql);e.printStackTrace();} finally{try {if(statement!=null){conn.close();}if(conn != null){conn.close();}} catch (Exception e) {e.printStackTrace();}}}