(一)、原生JDBC处理CLOB类型
增加,一般会插入一个空的clob到数据库对应的字段,然后锁定该列,用Write将待插入字符串写入进去。
重点:这两步操作要放在同一个事务里面。具体增加的方法如下:
public boolean save(Article article){ boolean result = true; Connection conn = ConnectionUntils.getInstance(); String sql = "insert into temp values(?,?,empty_clob())"; //锁住该列,防止并发写入时候该字段同时被多次写入造成错误 String sqlClob = "select temp_clob from temp t where t.name=? for update"; PreparedStatement pst =null; ResultSet rs = null; Writer writer = null; try { conn.setAutoCommit(false);//设置不自动提交,开启事务 pst = conn.prepareStatement(sql); pst.setString(1,article.getName()); pst.setString(2,article.getAge()); pst.executeUpdate(); pst= conn.prepareStatement(sqlClob); pst.setInt(1, article.getId()); rs = pst.executeQuery(); CLOB clob = null; if(rs.next()){ try { clob = (CLOB) rs.getClob(1); writer = clob.getCharacterOutputStream(); //拿到clob的字符输入流 writer.write(article.getContent()); writer.flush(); writer.close(); } catch (IOException e) { e.printStackTrace(); } } conn.commit(); } catch (SQLException e) { result = false; try { conn.rollback();//当commit或者rollback后会自动释放该列的锁定 } catch (SQLException e1) { e1.printStackTrace(); } e.printStackTrace(); } finally { conn.setAutoCommit(true);//还原 ConnectionUntils.close(rs, pst, conn); } return result; }
update操作,update时候主要利用PreparedStatement的setClob方法:
public boolean update(String name,String content){ int result = 0; Connection conn = ConnectionUntils.getInstance(); String sql = "update temp set temp_clob=? where name=?"; PreparedStatement pst =null; try { CLOB clob = oracle.sql.CLOB.createTemporary(conn, false,oracle.sql.CLOB.DURATION_SESSION); clob.setString(1L, content); pst = conn.prepareStatement(sql); pst.setClob(1, clob); pst.setString(2,name); result = pst.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); }finally{ ConnectionUntils.close(null, pst, conn); } if(result==0) return false; return true; }
查询就主要是从结果集ResultSet中定位到对应的字段后,往外读:
public Article select(String name){ Article article = new Article(); Connection conn = ConnectionUntils.getInstance(); String sql = "select name,age,temp_clog from temp where name = ?"; PreparedStatement pst =null; ResultSet rs = null; try { pst = conn.prepareStatement(sql); pst.setInt(1,id); rs = pst.executeQuery(); StringBuilder builder = new StringBuilder(); if(rs.next()){ Clob clob = rs.getClob("temp_clog"); Reader rd = clob.getCharacterStream(); char [] str = new char[12]; while(rd.read(str) != -1) { builder.append(new String(str)); } article.setContent(builder.toString()); article.setName(rs.getString("name")); article.setAge(rs.getInt("age")); } } catch (SQLException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); }finally{ ConnectionUntils.close(rs, pst, conn); } return article; }
(二)、Hibernate、Mybatis框架操作
这个因为框架都封装集成好了,所以我们使用的时候直接配置变量的类型为CLOB就可以。
譬如:Mybatis
<result column="temp_clob" property="content" jdbcType="Clob" typeHandler="org.apache.ibatis.type.ClobTypeHandler"/>
貌似Hibernate5内部做了对应的处理,可以直接当string类型一样处理即可。
总结
以上所述是小编给大家介绍的Oracle的CLOB大数据字段类型操作方法,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对网站的支持!