jsp结合html编辑器FCKeditor对Oracle CLOB大字段操作范例

package com.datainfo; import java.io.PrintWriter; import java.io.Reader; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; import oracle.sql.CLOB; public class UserDAO { public List getUserList() throws ClassNotFoundException, SQLException { Connection conn = DBConnection.getConnection(); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery("select * from userinfo"); List userList = new ArrayList(); try { while (rs.next()) { User user = new User(); user.setId(rs.getInt("id")); user.setUsername(rs.getString("username")); user.setUserpass(rs.getString("userpass")); userList.add(user); } rs.close(); stmt.close(); conn.close(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } return userList; } public User getUser(int id) throws ClassNotFoundException, SQLException { Connection conn = DBConnection.getConnection(); User user = new User(); try { Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery("select * from userinfo where id ="+id); if (rs.next()) { user.setId(rs.getInt("id")); user.setUsername(rs.getString("username")); user.setUserpass(rs.getString("userpass")); java.sql.Clob clob = rs.getClob("userinfo"); Reader inStream = clob.getCharacterStream(); char[] c = new char[(int) clob.length()]; inStream.read(c); String data = new String(c); user.setUserinfo(data); inStream.close(); } rs.close(); stmt.close(); } catch (Exception e) { System.out.println(" Error："+e.getMessage()); }finally{ conn.close(); } return user; } public int addUser(User user) throws ClassNotFoundException, SQLException { Connection conn = DBConnection.getConnection(); String sql=""; int id = 0; try { PreparedStatement pstmt=null; ResultSet rst=null; conn.setAutoCommit(false); oracle.sql.CLOB clob=null; sql = "select sequenceDemo.nextval from dual"; pstmt = conn.prepareStatement(sql); rst=pstmt.executeQuery(); if(rst.next()){ id = (int)rst.getInt(1); } pstmt.close(); //添加null Clob sql = "insert into userinfo(id,username,userpass,userinfo) values (?,?,?,empty_clob())"; pstmt = conn.prepareStatement(sql); pstmt.setInt(1, id); pstmt.setString(2, user.getUsername()); pstmt.setString(3, user.getUserpass()); pstmt.executeUpdate(); pstmt.close(); //获取修改--锁 sql = "select userinfo from userinfo where id= ? for update"; pstmt = conn.prepareStatement(sql); pstmt.setInt(1, id); rst=pstmt.executeQuery(); if(rst.next()){ clob=(CLOB)rst.getClob(1); } pstmt.close(); //获得clob对象的输出流 PrintWriter pw=new PrintWriter(clob.getCharacterOutputStream()); pw.write(user.getUserinfo()); pw.flush(); //更新clob对象 sql="UPDATE userinfo set userinfo =? where id=?"; pstmt=conn.prepareStatement(sql); pstmt.setClob(1, clob); pstmt.setInt(2, id); pstmt.executeUpdate(); conn.commit(); pw.close(); pstmt.close(); }catch (Exception e) { System.out.println(" Error："+e.getMessage()); }finally{ conn.close(); } return id; } public void updataUser(User user) throws ClassNotFoundException, SQLException { Connection conn = DBConnection.getConnection(); String sql = "update userinfo set username=?,userpass=? where id=?"; try { PreparedStatement pstmt = conn.prepareStatement(sql); pstmt.setString(1, user.getUsername()); pstmt.setString(2, user.getUserpass()); pstmt.setInt(3, user.getId()); pstmt.executeUpdate(); pstmt.close(); conn.close(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } public void deleteUser(User user) throws ClassNotFoundException, SQLException { Connection conn = DBConnection.getConnection(); String sql = "delete from userinfo where id =?"; try { PreparedStatement pstmt = conn.prepareStatement(sql); pstmt.setInt(1, user.getId()); pstmt.executeUpdate(); pstmt.close(); conn.close(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } public boolean isUser(User user) throws ClassNotFoundException,SQLException { Connection conn = DBConnection.getConnection(); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery("select * from userinfo where username ='"+user.getUsername()+"' and userpass ='"+user.getUserpass()+"'"); boolean isUsers = false; try { if(rs.next()){ isUsers = true; } rs.close(); stmt.close(); conn.close(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } return isUsers; } }