Processing clob and BLOB fields in MySQL and Oracle

1. How to Deal with clob in MySQL and Oracle databases. BLOB data type (1) cannot be connected to clob in the database. The Blob type is as follows: MySQL: clob corresponds to text and blob  In DB2/Oracle: clob corresponds to clob and blob(2) types in domain: Clob corresponds to string, blob corresponds to byte []  Clob corresponds to Java. SQL. clob, blob corresponds to Java. SQL. blob(3) corresponding types in the hibernate configuration file: Clob --> clob, blob --> binary  You can also directly use the database to provide types, such as Oracle. SQL. clob and Oracle. SQL. Blob.2. JDBC operations clob (Taking ORACLE as an example) First, clob/BLOB operations are not as simple as varchar operations. The insertion steps are generally divided into two steps: Step 1 inserts a null value and Step 2 locks this row, update the clob/BLOB field.

// Insert null value
conn.setAutoCommit (false);
String sql = "INSERT INTO T_FILE (NAME, FILE_CONTENT) VALUES ('Jambhala', EMPTY_CLOB ())";
PreparedStatement pstmt = conn.prepareStatement (sql);
pstmt.executeUpdate ();
// Lock this line
String sql_lockstr = "SELECT FILE_CONTENT FROM T_FILE WHERE NAME = 'Jambhala' FOR UPDATE";
pstmt = conn.prepareStatement (sql_lockstr);
ResultSet rs = pstmt.executeQuery ();
oracle.sql.Clob clob = (oracle.sql.Clob) rs.getClob (1);
java.io.OutputStream writer = clob.getAsciiOutputStream ();
byte [] temp = newFileContent.getBytes ();
writer.write (temp);
writer.flush ();
writer.close ();
pstmt.close ();

Read content:

oracle.sql.Clob clob = rs.getClob("FILE_CONTENT");
if(clob != null){
  Reader is = clob.getCharacterStream();
  BufferedReader br = new BufferedReader(is);
  String s = br.readLine();
  while(s != null){
  content += s+"<br>";
    s = br.readLine();
  }
}

Iii. JDBC operations blob

conn.setAutoCommit(false);
String sql = "INSERT INTO T_PHOTO(NAME, PHOTO) VALUES ('Jambhala', EMPTY_BLOB())";
pstmt = conn.prepareStatement(sql); 
pstmt = conn.executeUpdate();
sql = "SELECT PHOTO FROM T_PHOTO WHERE NAME='Jambhala'";
pstmt = conn.prepareStatement(sql); 
rs = pstmt.executeQuery(sql);
if(rs.next()){
  oracle.sql.Blob blob = (oracle.sql.Blob)rs.getBlob(1);
}
//write to a file
File file=new File("C:\\test.rar");
FileInputStream fin = new FileInputStream(file);
OutputStream out = blob.getBinaryOutputStream();
int count=-1,total=0;
byte[] data = new byte[blob.getBufferSize()];
while((count=fin.read(data)) != -1){
  total += count;
  out.write(data, 0, count);
} 

4. hibernate handles clob

MyFile file = new MyFile ();
file.setName ("Jambhala");
file.setContent (Hibernate.createClob (""));
session.save (file);
session.flush ();
session.refresh (file, LockMode.UPGRADE);
oracle.sql.Clob clob = (oracle.sql.Clob) file.getContent ();
Writer pw = clob.getCharacterOutputStream ();
pw.write (longText); // Write long text
pw.close ();
session.close ();

5. Use hibernate to process blob

The principles are basically the same:

Photo photo = new Photo ();
photo.setName ("Jambhala");
photo.setPhoto (Hibernate.createBlob (""));
session.save (photo);
session.flush ();

session.refresh (photo, LockMode.UPGRADE); // Lock this object
oracle.sql.Blob blob = photo.getPhoto (); // Get a pointer to this blob
OutputStream out = blob.getBinaryOutputStream ();
// Write to a file
File f = new File ("C: \\ test.rar");
FileInputStream fin = new FileInputStream (f);
int count = -1, total = 0;
byte [] data = new byte [(int) fin.available ()];
out.write (data);
fin.close ();
out.close ();
session.flush ();


String DRIVER = "oracle.jdbc.driver.OracleDriver";
// Oracle connection URL
private static final String URL = "jdbc: oracle: thin: @testora: 1521: orac";
//username
private static final String USER = "scott";
//password
private static final String PASSWORD = "pswd";
//Database Connectivity
private static Connection conn = null;
// SQL statement object
private static Statement stmt = null;
// @ roseuid 3EDA089E02BC
public LobPros () {}

// Insert a new Clob object into the database
// @ param infile data file
// @ throws java.lang.Exception
// @ roseuid 3EDA089E02BC
public static void clobInsert (String infile) throws Exception {
  // Set not to submit automatically
  boolean defaultCommit = conn.getAutoCommit ();
  conn.setAutoCommit (false);
  try {
// Insert an empty Clob object
    stmt.executeUpdate ("INSERT INTO TEST_CLOB VALUES ('111', EMPTY_CLOB ())");
    // Query this Clob object and lock it
    ResultSet rs = stmt.executeQuery ("SELECT CLOBCOL FROM TEST_CLOB WHERE ID = '111' FOR UPDATE");
    while (rs.next ()) {
 // Remove this Clob object
      oracle.sql.CLOB clob = (oracle.sql.CLOB) rs.getClob ("CLOBCOL");
      // Write data to Clob object
      BufferedWriter out = new BufferedWriter (clob.getCharacterOutputStream ());
      BufferedReader in = new BufferedReader (new FileReader (infile));
      int c;
      while ((c = in.read ())! = -1) {
   out.write (c);
      }
      in.close ();
      out.close ();
    }
    // formally submit
    conn.commit ();
  } catch (Exception e) {
   // Error rollback
   conn.rollback ();
   throw e;
  }

  // Restore the original commit status
  conn.setAutoCommit (defaultCommit);
}

// Modify the Clob object (is an overriding modification based on the original Clob object)
// @ param infile data file
// @ throws java.lang.Exception
// @ roseuid 3EDA089E02BC
public static void clobModify (String infile) throws Exception {
  // Set not to submit automatically
  boolean defaultCommit = conn.getAutoCommit ();
  conn.setAutoCommit (false);
  try {
// Query Clob object and lock
    ResultSet rs = stmt.executeQuery ("SELECT CLOBCOL FROM TEST_CLOB WHERE ID = '111' FOR UPDATE");
    while (rs.next ()) {
 // Get this Clob object
      oracle.sql.CLOB clob = (oracle.sql.CLOB) rs.getClob ("CLOBCOL");
      // make overriding changes
      BufferedWriter out = new BufferedWriter (clob.getCharacterOutputStream ());
      BufferedReader in = new BufferedReader (new FileReader (infile));
      int c;
      while ((c = in.read ())! =-1) {
       out.write (c);
      }
      in.close ();
      out.close ();
    }
    // formally submit
    conn.commit ();
  } catch (Exception e) {
   // Error rollback
   conn.rollback ();
   throw e;
  }
  // Restore the original commit status
  conn.setAutoCommit (defaultCommit);
}

// Replace CLOB object (Clear the original CLOB object and replace it with a new CLOB object
// @ param infile data file
// @ throws java.lang.Exception
// @ roseuid 3EDA04BF01E1
public static void clobReplace (String infile) throws Exception {
  // Set not to submit automatically
  boolean defaultCommit = conn.getAutoCommit ();
  conn.setAutoCommit (false);
  try {
// Empty the original CLOB object
    stmt.executeUpdate ("UPDATE TEST_CLOB SET CLOBCOL = EMPTY_CLOB () WHERE ID = '111'");
    // Query CLOB object and lock
    ResultSet rs = stmt.executeQuery ("SELECT CLOBCOL FROM TEST_CLOB WHERE ID = '111' FOR UPDATE");
    while (rs.next ()) {
 // Get this CLOB object
      oracle.sql.CLOB clob = (oracle.sql.CLOB) rs.getClob ("CLOBCOL");
      //update data
      BufferedWriter out = new BufferedWriter (clob.getCharacterOutputStream ());
      BufferedReader in = new BufferedReader (new FileReader (infile));
      int c;
      while ((c = in.read ())! =-1) {
   out.write (c);
 }
 in.close ();
 out.close ();
    }
// formally submit
conn.commit ();
  } catch (Exception e) {
// Error rollback
conn.rollback ();
throw e;
  }
  // Restore the original commit status
  conn.setAutoCommit (defaultCommit);
}

// CLOB object read
// @ param outfile output file name
// @ throws java.lang.Exception
// @ roseuid 3EDA04D80116
public static void clobRead (String outfile) throws Exception {
  // Set not to submit automatically
  boolean defaultCommit = conn.getAutoCommit ();
  conn.setAutoCommit (false);
  try {
    // Query CLOB object
    ResultSet rs = stmt.executeQuery ("SELECT * FROM TEST_CLOB WHERE ID = '111'");
    while (rs.next ()) {
 // Get CLOB object
      oracle.sql.CLOB clob = (oracle.sql.CLOB) rs.getClob ("CLOBCOL");
      // Output as characters
      BufferedReader in = new BufferedReader (clob.getCharacterStream ());
      BufferedWriter out = new BufferedWriter (new FileWriter (outfile));
      int c;
      while ((c = in.read ())! =-1) {
       out.write (c);
      }
 out.close ();
 in.close ();
    }
  } catch (Exception e) {
    conn.rollback ();
    throw e;
  }
  // Restore the original commit status
  conn.setAutoCommit (defaultCommit);
}

// Insert a new BLOB object into the database
// @ param infile data file
// @ throws java.lang.Exception
// @ roseuid 3EDA04E300F6
public static void blobInsert (String infile) throws Exception {
  // Set not to submit automatically
  boolean defaultCommit = conn.getAutoCommit ();
  conn.setAutoCommit (false);
  try {
// Insert an empty BLOB object
stmt.executeUpdate ("INSERT INTO TEST_BLOB VALUES ('222', EMPTY_BLOB ()) ");
// Query this BLOB object and lock it
ResultSet rs = stmt.executeQuery ("SELECT BLOBCOL FROM TEST_BLOB WHERE ID = '222' FOR UPDATE");
while (rs.next ()) {
 // Remove this BLOB object
 oracle.sql.BLOB blob = (oracle.sql.BLOB) rs.getBlob ("BLOBCOL");
 // Write data to BLOB object
 BufferedOutputStream out = new BufferedOutputStream (blob.getBinaryOutputStream ());
 BufferedInputStream in = new BufferedInputStream (new FileInputStream (infile));
 int c;
 while ((c = in.read ())! =-1) {
out.write (c);
 }
 in.close ();
 out.close ();
}
// formally submit
conn.commit ();
  } catch (Exception e) {
// Error rollback
conn.rollback ();
throw e;
  }
  // Restore the original commit status
  conn.setAutoCommit (defaultCommit);
}

// Modify the BLOB object (overwrite modification based on the original BLOB object)
// @ param infile data file
// @ throws java.lang.Exception
// @ roseuid 3EDA04E90106
public static void blobModify (String infile) throws Exception {
  // Set not to submit automatically
  boolean defaultCommit = conn.getAutoCommit ();
  conn.setAutoCommit (false);
  try {
// Query BLOB object and lock
ResultSet rs = stmt.executeQuery ("SELECT BLOBCOL FROM TEST_BLOB WHERE ID = '222' FOR UPDATE");
while (rs.next ()) {
 // Remove this BLOB object
 oracle.sql.BLOB blob = (oracle.sql.BLOB) rs.getBlob ("BLOBCOL");
 // Write data to BLOB object
 BufferedOutputStream out = new BufferedOutputStream (blob.getBinaryOutputStream ());
 BufferedInputStream in = new BufferedInputStream (new FileInputStream (infile));
 int c;
 while ((c = in.read ())! =-1) {
   out.write (c);
 }
 in.close ();
 out.close ();
}
// formally submit
conn.commit ();
  } catch (Exception e) {
// Error rollback
conn.rollback ();
throw e;
  }
  // Restore the original commit status
  conn.setAutoCommit (defaultCommit);
}

// Replace the BLOB object (clear the original BLOB object and replace it with a brand new BLOB object)
// @ param infile data file
// @ throws java.lang.Exception
// @ roseuid 3EDA0505000C
public static void blobReplace (String infile) throws Exception {
  // Set not to submit automatically
  boolean defaultCommit = conn.getAutoCommit ();
  conn.setAutoCommit (false);
  try {
// Empty the original BLOB object
stmt.executeUpdate ("UPDATE TEST_BLOB SET BLOBCOL = EMPTY_BLOB () WHERE ID = '222'");
// Query this BLOB object and lock it
ResultSet rs = stmt.executeQuery ("SELECT BLOBCOL FROM TEST_BLOB WHERE ID = '222' FOR UPDATE");
while (rs.next ()) {
 // Remove this BLOB object
 oracle.sql.BLOB blob = (oracle.sql.BLOB) rs.getBlob ("BLOBCOL");
 // Write data to BLOB object
 BufferedOutputStream out = new BufferedOutputStream (blob.getBinaryOutputStream ());
 BufferedInputStream in = new BufferedInputStream (new FileInputStream (infile));
 int c;
 while ((c = in.read ())! =-1) {
   out.write (c);
 }
 in.close ();
 out.close ();
    }
// formally submit
conn.commit ();
  } catch (Exception e) {
// Error rollback
conn.rollback ();
throw e;
  }
  // Restore the original commit status
  conn.setAutoCommit (defaultCommit);
}

// BLOB object read
// @ param outfile output file name
// @ throws java.lang.Exception
// @ roseuid 3EDA050B003B
public static void blobRead (String outfile) throws Exception {
  // Set not to submit automatically
  boolean defaultCommit = conn.getAutoCommit ();
  conn.setAutoCommit (false);
  try {
     // Query BLOB object
ResultSet rs = stmt.executeQuery ("SELECT BLOBCOL FROM TEST_BLOB WHERE ID = '222'");
while (rs.next ()) {
  // Remove this BLOB object
  oracle.sql.BLOB blob = (oracle.sql.BLOB) rs.getBlob ("BLOBCOL");
  // Output in binary form
  BufferedOutputStream out = new BufferedOutputStream (new FileOutputStream (outfile));
  BufferedInputStream in = new BufferedInputStream (blob.getBinaryStream ());
  int c;
  while ((c = in.read ())! =-1) {
   out.write (c);
      }
  in.close ();
  out.close ();
     }
// formally submit
     conn.commit ();
  } catch (Exception e) {
// Error rollback
conn.rollback ();
throw e;
  }
  // Restore the original commit status
  conn.setAutoCommit (defaultCommit);
}

// Create a test form
// @ throws Exception
public static void createTables () throws Exception {
  try {
stmt.executeUpdate ("CREATE TABLE TEST_CLOB (ID NUMBER (3), CLOBCOL CLOB)");
stmt.executeUpdate ("CREATE TABLE TEST_BLOB (ID NUMBER (3), BLOBCOL BLOB)");
  } catch (Exception e) {}
}

// @ param args-command line parameters
// @ throws java.lang.Exception
// @ roseuid 3EDA052002AC
public static void main (String [] args) throws Exception {
  // Load the driver and establish a database connection
  Class.forName (DRIVER);
  conn = DriverManager.getConnection (URL, USER, PASSWORD);
  stmt = conn.createStatement ();
  // Create a test form
  createTables ();
  // CLOB object insertion test
  clobInsert ("c: /clobInsert.txt");
  clobRead ("c: /clobInsert.out");
  // CLOB object modification test
  clobModify ("c: /clobModify.txt");
  clobRead ("c: /clobModify.out");
  // CLOB object replacement test
  clobReplace ("c: /clobReplace.txt");
  clobRead ("c: /clobReplace.out");
  // BLOB object insertion test
  blobInsert ("c: /blobInsert.doc");
  blobRead ("c: /blobInsert.out");
  // BLOB object modification test
  blobModify ("c: /blobModify.doc");
  blobRead ("c: /blobModify.out");
  // BLOB object replacement test
  blobReplace ("c: /blobReplace.doc");
  blobRead ("c: /bolbReplace.out");
  // Close the resource and exit
  conn.close ();
  System.exit (0);
}