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);
}