Blob例
public boolean addMMSCardTemplate(String title, String content, int owner, String addTime) throws Exception{ Connection con = null; long id = 1L; PreparedStatementpst = null; boolean ret = false; String date = CommonFun.getDateTimeString(); ResultSet rs = null; try { con = this.dbPool.getConnection(); con.setAutoCommit(false); pst = con.prepareStatement("SELECT T_MMSCADETEMPLATE_SEQ.NEXTVAL from dual"); rs = pst.executeQuery(); //获取数据库中创建的序列 if(rs.next()){ id = rs.getLong("nextval"); } String sql = "insert into t_mmscardtemplate (id, title,content,owner,addtime) values (?,?,?,?,?)"; pst = con.prepareStatement (sql); pst.setLong(1, id); pst.setString(2, title); pst.setBlob(3, BLOB.empty_lob()); pst.setInt(4, owner); pst.setString(5, date); pst.executeUpdate(); pst = con.prepareStatement("select content from t_mmscardtemplate where id = ? for update"); pst.setLong(1, id); rs = pst.executeQuery(); BLOB blob = null; byte[] c = content.getBytes(); while(rs.next()){ blob = (BLOB) rs.getBlob("content"); final java.io.BufferedOutputStream out = new java.io.BufferedOutputStream( blob.getBinaryOutputStream()); out.write(c, 0, c.length); out.close(); } pst = con.prepareStatement("update t_mmscardtemplate set content = ? where id = ?"); pst.setBlob(1, blob); pst.setLong(2, id); pst.executeUpdate(); con.commit(); ret = true; } catch (SQLException e) { con.rollback(); throw e; } finally { con.setAutoCommit(true); DBTools.close(con, pst, rs); } return ret; } 以流的方式写入Blob或Clob，前者二进制方式，后者以字符方式，先插空blob后update。其中涉及到sql的长度问题等问题。
public MMSCardTemplatePO getMMSCardTemplateById(int id) throws Exception { PreparedStatement pst = null; Connection con = null; ResultSet rs = null; MMSCardTemplatePO mcPO = null; String sql = "select * from t_mmscardtemplate where id = ?"; try{ con = this.dbPool.getConnection(); pst = con.prepareStatement(sql); pst.setInt(1, id); rs = pst.executeQuery(); mcPO = new MMSCardTemplatePO(); while(rs.next()){ mcPO.setId(rs.getInt("id")); BLOB blob = (BLOB) rs.getBlob("content"); mcPO.setContent(new String(blob.getBytes(1, (int)blob.length()))); mcPO.setAddTime(rs.getString("addtime")); mcPO.setOwner(rs.getString("owner")); mcPO.setTitle(rs.getString("title")); } } catch (SQLException e) { throw e; } finally { DBTools.close(con, pst, rs); } return mcPO; }
页:
[1]