CLOB字段的插入与更新

由于项目需求，表里字段有个CLOB字段，以前没用过这个字段类型，参考了网上一些内容后，记录一下CLOB字段的插入与更新。

插入时把CLOB字段插入一个empty_clob()，然后再取出更新clob字段。代码如下

                   public String[] getWebFileContent(String name) throws DataServiceSQLException {
                       Connection connection = null;
                       PreparedStatement pstatement = null;
                       ResultSet rs = null;
                       String content = null;       
                      try{
                         String sql = "insert into bam_webfileinfo(name,groupid,content) values(?,?,empty_clob())";
			 connection = ConnectionSource.getConnection();
			 boolean defaultCommit=connection.getAutoCommit();
			 connection.setAutoCommit(false);
			 pstatement = connection.prepareStatement(sql);
			 pstatement.setString(1, filename);
			 pstatement.setInt(2, obj.getGroupId());
			 pstatement.executeUpdate();
			 pstatement=null;
			 String sqlString = "select contentfrom bam_webfileinfo where name = ? for update"; 
			 pstatement = connection.prepareStatement(sqlString);
			 pstatement.setString(1, filename);
			 rs=pstatement.executeQuery();
			 java.sql.CLOB clobContent = null;
			 if(rs.next()){     
				 clobContent = (java.sql.CLOB.CLOB)rs.getClob("content");   
			 }
			 Writer wr = clobContent.getCharacterOutputStream();        
			 wr.write(obj.getContent());    
			 wr.flush(); 
			 wr.close();
			 connection.commit();
			 connection.setAutoCommit(defaultCommit);
                       
                       }catch (SQLException e) {
                          throw new DataServiceSQLException(e);
                    }catch(Exception e){
                          e.printStackTrace();
                    } finally {
                          ConnectionSource.closeStatement(pstatement);
                          ConnectionSource.closeConnection(connection);
                    }

               }



更新时先把CLOB字段置空，然后再更新CLOB字段。代码如下

		  public void updateContent(WebFileInfo obj) throws DataServiceSQLException {   
                     Connection connection = null;
                     PreparedStatement pstatement = null;
                     ResultSet rs = null;
                     try{
                         String sql = "update bam_webfileinfo set content=empty_clob() where name = ?";
			 connection = ConnectionSource.getConnection();
			 pstatement = connection.prepareStatement(sql);
			 pstatement.setString(1, obj.getFileName());
			 pstatement.executeUpdate();
			 connection.commit();
			 pstatement = null;
			 String sqlString = "select content from bam_webfileinfo where name = ? for update ";
			 boolean defaultCommit=connection.getAutoCommit();
			 connection.setAutoCommit(false);
			 pstatement = connection.prepareStatement(sqlString);
			 pstatement.setString(1, obj.getFileName());
			 rs=pstatement.executeQuery();
			 java.sql.CLOB clobContent = null;
			 if(rs.next()){     
				 clobContent = (java.sql.CLOB)rs.getClob("content");    
			 }
			 Writer wr = clob.getCharacterOutputStream();   
			 wr.write(obj.getContent());    
			 wr.flush();    
			 wr.close();
			 connection.commit();
			 connection.setAutoCommit(defaultCommit);
                    }catch (SQLException e) {
                          throw new DataServiceSQLException(e);
                    }catch(Exception e){
                          e.printStackTrace();
                    } finally {
                          ConnectionSource.closeStatement(pstatement);
                          ConnectionSource.closeConnection(connection);
                    }

               }