Java by API/java.sql/Clob

Clob: getCharacterStream()

   <source lang="java">

import java.io.ByteArrayOutputStream; import java.io.File; import java.io.FileInputStream; import java.io.InputStream; import java.io.Reader; import java.io.StringWriter; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class Main {

 public static void main(String[] args) throws Exception {
   Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
   Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL", "yourName", "mypwd");
   Statement stmt = conn.createStatement();
   createBlobClobTables(stmt);
   PreparedStatement pstmt = conn.prepareStatement("INSERT INTO BlobClob VALUES(40,?,?)");
   File file = new File("blob.txt");
   FileInputStream fis = new FileInputStream(file);
   pstmt.setBinaryStream(1, fis, (int) file.length());
   file = new File("clob.txt");
   fis = new FileInputStream(file);
   pstmt.setAsciiStream(2, fis, (int) file.length());
   fis.close();
   pstmt.execute();
   ResultSet rs = stmt.executeQuery("SELECT * FROM BlobClob WHERE id = 40");
   rs.next();
   java.sql.Blob blob = rs.getBlob(2);
   java.sql.Clob clob = rs.getClob(3);
   byte blobVal[] = new byte[(int) blob.length()];
   InputStream blobIs = blob.getBinaryStream();
   blobIs.read(blobVal);
   ByteArrayOutputStream bos = new ByteArrayOutputStream();
   bos.write(blobVal);
   blobIs.close();
   char clobVal[] = new char[(int) clob.length()];
   Reader r = clob.getCharacterStream();
   r.read(clobVal);
   StringWriter sw = new StringWriter();
   sw.write(clobVal);
   r.close();
   conn.close();
 }
 public static void createBlobClobTables(Statement stmt) throws Exception {
   String Sql = "CREATE TABLE BlobClob(Id NUMBER(3), b BLOB, c CLOB)";
   try {
     stmt.executeUpdate("DROP TABLE BlobClob");
   } catch (SQLException se) {
     if (se.getErrorCode() == 942)
       System.out.println("Error dropping BlobClob table:" + se.getMessage());
   }
   if (stmt.executeUpdate(Sql) == 0)
     System.out.println("BlobClob table created...");
 }

}

 </source>
   
  
 
  

Clob: getSubString(long pos, int length)

   <source lang="java">

import java.sql.Clob; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; public class Main{

 public static Connection getConnection() throws Exception {
   String driver = "org.gjt.mm.mysql.Driver";
   String url = "jdbc:mysql://localhost/databaseName";
   String username = "root";
   String password = "root";
   Class.forName(driver);
   Connection conn = DriverManager.getConnection(url, username, password);
   return conn;
 }
 public static String getCLOB(int id) throws Exception {
   Connection conn = null;
   ResultSet rs = null;
   PreparedStatement pstmt = null;
   String query = "SELECT clobData FROM tableName WHERE id = ?";
   try {
     conn = getConnection();
     pstmt = conn.prepareStatement(query);
     pstmt.setInt(1, id);
     rs = pstmt.executeQuery();
     rs.next();
     Clob clob = rs.getClob(1);
     // materialize CLOB onto client
     String wholeClob = clob.getSubString(1, (int) clob.length());
     return wholeClob;
   } finally {
     rs.close();
     pstmt.close();
     conn.close();
   }
 }
 public static void main(String args[]) throws Exception {
   System.out.println(getCLOB(01));
 }

}

 </source>