Blob例

 

 public boolean addMMSCardTemplate(String title, String content, int owner,
        String addTime) throws Exception{
        Connection con = null;
        long id = 1L;
        PreparedStatement  pst = 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;
    }