clob-blob.java

完整通过JAVA读写ORACLE中CLOB、BLOB字段的方法

 /**
  * 写入CLOB字段到oracle,应用于使用连接池时，该方法有大小限制，不推荐使用
  * 使用前必须先连接
  * @param field String        CLOB字段名
  * @param tableName String    表名
  * @param where String        where 条件，例：where seq=1
  * @param content String     内容
  * @throws Exception
  */
 public void writeClob(String field,String tableName,String where,String content) throws Exception
  {
    try
    {
      String sqll="select "+field+" from "+tableName+" "+where+" for update";

      exec_stmt.executeUpdate("update "+tableName+" set "+field+"=empty_clob() "+where);

      OracleResultSet rss=(OracleResultSet)query_stmt.executeQuery(sqll);

      if(rss.next())
      {
       OracleThinClob clob=(OracleThinClob)rss.getClob(1);

        clob.putString(1,content);

        sqll="update "+tableName+" set "+field+"=? "+where;
        OraclePreparedStatement pstmt=(OraclePreparedStatement)con.prepareStatement(sqll);

        pstmt.setClob(1,(Clob)clob);
        pstmt.executeUpdate();
        pstmt.close();
     }
       con.commit();
    }
    catch (Exception e)
    {
      con.rollback();
      throw e;
    }
  }

  /**
   * 写入CLOB字段到oracle,应用于使用连接池时，使用完毕必须调用commit方法
   * 使用前必须先连接
   * @param field String        CLOB字段名
   * @param tableName String    表名
   * @param where String        where 条件，例：where seq=1
   * @return BufferedWriter
   * @throws Exception
   */
  public BufferedWriter writeClob(String field,String tableName,String where) throws Exception
   {
     try
     {
       String sqll="select "+field+" from "+tableName+" "+where+" for update";

       exec_stmt.executeUpdate("update "+tableName+" set "+field+"=empty_clob() "+where);

       OracleResultSet rss=(OracleResultSet)query_stmt.executeQuery(sqll);

       if(rss.next())
       {
        OracleThinClob clob=(OracleThinClob)rss.getClob(1);
        if(clob!=null)
        {
          BufferedWriter  out = new BufferedWriter(clob.getCharacterOutputStream());
          return out;
        }
       }

     }
     catch (Exception e)
     {
       throw e;
     }
     return null;
  }

 /**
   * 写入CLOB字段到oracle,使用于非连接池场合，该方法有大小限制，不推荐使用
   * 使用前必须先连接
   * @param field String        CLOB字段名
   * @param tableName String    表名
   * @param where String        where 条件，例：where seq=1
   * @param content String     内容
   * @throws Exception
   */
  public void writeClob2(String field,String tableName,String where,String content) throws Exception
   {
     try
     {
       String sqll="select "+field+" from "+tableName+" "+where+" for update";

       exec_stmt.executeUpdate("update "+tableName+" set "+field+"=empty_clob() "+where);
       OracleResultSet rss=(OracleResultSet)query_stmt.executeQuery(sqll);

       if(rss.next())
       {
        oracle.sql.CLOB clob=(oracle.sql.CLOB)rss.getClob(1);

         clob.putString(1,content);

         sqll="update "+tableName+" set "+field+"=? "+where;
         OraclePreparedStatement pstmt=(OraclePreparedStatement)con.prepareStatement(sqll);

         pstmt.setClob(1,(Clob)clob);
         pstmt.executeUpdate();

         pstmt.close();
      }
        con.commit();
     }
     catch (Exception e)
     {
       con.rollback();
       throw e;
     }
  }

  /**
    * 写入CLOB字段到oracle,使用于非连接池场合，使用后必须提交
    * 使用前必须先连接
    * @param field String        CLOB字段名
    * @param tableName String    表名
    * @param where String        where 条件，例：where seq=1
    * @return BufferedWriter
    * @throws Exception
    */
   public BufferedWriter writeClob2(String field,String tableName,String where) throws Exception
    {
      try
      {
        String sqll="select "+field+" from "+tableName+" "+where+" for update";

        exec_stmt.executeUpdate("update "+tableName+" set "+field+"=empty_clob() "+where);
        OracleResultSet rss=(OracleResultSet)query_stmt.executeQuery(sqll);

        if(rss.next())
        {
         oracle.sql.CLOB clob=(oracle.sql.CLOB)rss.getClob(1);
         if(clob!=null)
         {
             BufferedWriter  out = new BufferedWriter(clob.getCharacterOutputStream());
             return out;
         }

       }

      }
      catch (Exception e)
      {
        throw e;
      }
      return null;
  }

  /**
    * 写入BLOB字段到oracle,使用于非连接池场合
    * 使用前必须先连接
    * @param field String        BLOB字段名
    * @param tableName String    表名
    * @param where String        where 条件，例：where seq=1
    * @param content byte[]      内容
    * @throws Exception
    */
   public void writeBlob2(String field,String tableName,String where,byte[] content) throws Exception
    {
      try
      {
        String sqll="select "+field+" from "+tableName+" "+where+" for update";

        exec_stmt.executeUpdate("update "+tableName+" set "+field+"=empty_blob() "+where);
        OracleResultSet rss=(OracleResultSet)query_stmt.executeQuery(sqll);

        if(rss.next())
        {
         oracle.sql.BLOB blob=(oracle.sql.BLOB)rss.getBlob(1);
         BufferedOutputStream out = new BufferedOutputStream(blob.getBinaryOutputStream());
         out.write(content);
         out.close();
       }
         con.commit();
      }
      catch (Exception e)
      {
        con.rollback();
        throw e;
      }
  }

  /**
    * 写入BLOB字段到oracle,使用于非连接池场合
    * 使用前必须先连接
    * @param field String        BLOB字段名
    * @param tableName String    表名
    * @param where String        where 条件，例：where seq=1
    * @return BufferedOutputStream
    * @throws Exception
    */
   public BufferedOutputStream writeBlob2(String field,String tableName,String where) throws Exception
    {
      try
      {
        String sqll="select "+field+" from "+tableName+" "+where+" for update";

        exec_stmt.executeUpdate("update "+tableName+" set "+field+"=empty_blob() "+where);
        OracleResultSet rss=(OracleResultSet)query_stmt.executeQuery(sqll);

        if(rss.next())
        {
         oracle.sql.BLOB blob=(oracle.sql.BLOB)rss.getBlob(1);
         BufferedOutputStream out = new BufferedOutputStream(blob.getBinaryOutputStream());
         return out;
       }
      }
      catch (Exception e)
      {
        throw e;
      }
      return null;
  }

  /**
  * 写BLOB字段到oracle,应用于使用连接池时，该方法有大小限制，不推荐使用
  * 使用前必须先连接
  * @param field String        BLOB字段名
  * @param tableName String    表名
  * @param where String        where 条件，例：where seq=1
  * @param content byte[]     内容
  * @throws Exception
  */
 public void writeBlob(String field,String tableName,String where,byte[] content) throws Exception
  {
    try
    {
      String sqll="select "+field+" from "+tableName+" "+where+" for update";

      exec_stmt.executeUpdate("update "+tableName+" set "+field+"=empty_blob() "+where);

      OracleResultSet rss=(OracleResultSet)query_stmt.executeQuery(sqll);

      if(rss.next())
      {
       OracleThinBlob blob=(OracleThinBlob)rss.getBlob(1);

       BufferedOutputStream out = new BufferedOutputStream(blob.getBinaryOutputStream());

       out.write(content);
       out.close();


     }
       con.commit();
    }
    catch (Exception e)
    {
      con.rollback();
      throw e;
    }
  }

  /**
   * 写BCLOB字段到oracle,应用于使用连接池时,使用完毕后必须调用commit方法
   * 使用前必须先连接
   * @param field String        BLOB字段名
   * @param tableName String    表名
   * @param where String        where 条件，例：where seq=1
   * @throws Exception
   * @return BufferedOutputStream
   */
  public BufferedOutputStream writeBlob(String field,String tableName,String where) throws Exception
   {
     try
     {
       String sqll="select "+field+" from "+tableName+" "+where+" for update";

       exec_stmt.executeUpdate("update "+tableName+" set "+field+"=empty_blob() "+where);

       OracleResultSet rss=(OracleResultSet)query_stmt.executeQuery(sqll);

       if(rss.next())
       {
        OracleThinBlob blob=(OracleThinBlob)rss.getBlob(1);

        BufferedOutputStream out = new BufferedOutputStream(blob.getBinaryOutputStream());

        return out;

      }

     }
     catch (Exception e)
     {
       throw e;
     }
     return null;
  }

  /**
   * 读出CLOB字段,使用前先连接,使用完毕后必须调用commit方法
   * @param field String   CLOB字段名
   * @param tableName String 表名
   * @param where String where条件，例：where seq=1
   * @throws Exception
   * @return BufferedReader
   */
  public BufferedReader newReadClob(String field,String tableName,String where) throws Exception
    {
      CLOB clob;
      String str;
      BufferedReader in=null;

        str="select "+field+" from "+tableName+" "+where+" for update";

        OracleResultSet rss=(OracleResultSet)query_stmt.executeQuery(str);
        if(rss.next())
        {
          clob = (CLOB)rss.getClob(1);
          if(clob!=null)
             in = new BufferedReader(clob.getCharacterStream());
         }

        return in;

    }


    /**
     * 读出CLOB字段,使用前必须先连接，该方法有大小限制，不推荐使用，可用newReadClob代替
     * @param field String   CLOB字段名
     * @param tableName String 表名
     * @param where String where条件，例：where seq=1
     * @throws Exception
     * @return String
     */
    public String readClob(String field,String tableName,String where) throws Exception
      {
        Clob clob;
        String str;
        try
        {
          str="select "+field+" from "+tableName+" "+where+" for update";

          OracleResultSet rss=(OracleResultSet)query_stmt.executeQuery(str);
          if(rss.next())
          {
            clob = rss.getClob(1);
            if(clob!=null)
            str=clob.getSubString( (long) 1, (int) clob.length());
         }
           return str;
        }
        catch (Exception e)
        {
           throw e;
        }
        finally
        {
            con.commit();
        }
    }
    /**
      * 读出BLOB字段,使用前必须先连接，该方法有大小限制，不推荐使用，可用newReadBlob代替
      * @param field String   BLOB字段名
      * @param tableName String 表名
      * @param where String where条件，例：where seq=1
      * @throws Exception
      * @return byte[]
      */
     public byte[] readBlob(String field,String tableName,String where) throws Exception
       {
         Blob blob;
         byte[] b=null;
         try
         {
           String sqll="select "+field+" from "+tableName+" "+where+" for update";

           OracleResultSet rss=(OracleResultSet)query_stmt.executeQuery(sqll);
           if(rss.next())
           {
             blob = rss.getBlob(1);
             if(blob!=null)
             {
                 ByteArrayOutputStream b2= new ByteArrayOutputStream();
                 BufferedInputStream in = new BufferedInputStream(blob.getBinaryStream());

                 int c;
                 while ((c=in.read())!=-1)
                 {
                     b2.write(c);
                 }
                 in.close();
                 b=b2.toByteArray();
                 b2.close();
                 return b;
             }
          }
         }
         catch (Exception e)
         {
           throw e;
         }
         finally
         {
           con.commit();
           return b;
         }
    }


    /**
      * 读出BLOB字段,使用前先连接,使用完毕后必须调用commit方法
      * @param field String   BLOB字段名
      * @param tableName String 表名
      * @param where String where条件，例：where seq=1
      * @throws Exception
      * @return BufferedInputStream
      */
     public BufferedInputStream newReadBlob(String field,String tableName,String where) throws Exception
       {
         BufferedInputStream in=null;
         Blob blob;
         try
         {
           String sqll="select "+field+" from "+tableName+" "+where+" for update";

           OracleResultSet rss=(OracleResultSet)query_stmt.executeQuery(sqll);
           if(rss.next())
           {
             blob = rss.getBlob(1);
             if(blob!=null)
             {
                 in = new BufferedInputStream(blob.getBinaryStream());
              }
          }

         }
         catch (Exception e)
         {
           throw e;
         }
         finally
         {
           return in;
         }
    }