Problem resolution for read-write BLOB fields in Oracle _oracle

LOB types are divided into BLOBs and CLOB two: BLOBs-binary large pair images (Binary Large Object), which are used to store text-throttling data (such as programs, images, audio, video, and so on). CLOB, a character-type large pair (Character Large Object), is associated with a character set and is suitable for storing text-type data (such as historical archives, voluminous writings, etc.).
The following is an example of a program that manipulates Oracle database LOB type fields through JDBC.

Set up the following two test database tables, the Power Designer PD model is as follows:

The Build table SQL statement is:
CREATE TABLE Test_clob (ID number (3), Clobcol CLOB)
CREATE TABLE Test_blob (ID number (3), Blobcol BLOB)

I. Access to CLOB objects

1, into the database to insert a new CLOB image

Copy Code code as follows:

public static void Clobinsert (String infile) throws Exception
{
/* Set does not automatically submit * *
Boolean defaultcommit = Conn.getautocommit ();
Conn.setautocommit (FALSE);

try {
/* Insert an empty CLOB to like * *
Stmt.executeupdate ("INSERT into Test_clob VALUES ('", Empty_clob ()) ");
* * Query this CLOB object and lock/*
ResultSet rs = stmt.executequery ("Select Clobcol from Test_clob WHERE id= ' for UPDATE");
while (Rs.next ()) {
/* Remove this CLOB to like * *
Oracle.sql.CLOB CLOB = (Oracle.sql.CLOB) rs.getclob ("Clobcol");
/* Write data to Clob to the image * *
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 submitted * * *
Conn.commit ();
catch (Exception ex) {
/* ERROR Roll back/*
Conn.rollback ();
Throw ex;
}

/* Restore original Submission Status * * *
Conn.setautocommit (Defaultcommit);
}

2, modify the CLOB (is based on the original CLOB on the basis of the revision of the cover type)

Copy Code code as follows:

public static void Clobmodify (String infile) throws Exception
{
/* Set does not automatically submit * *
Boolean defaultcommit = Conn.getautocommit ();
Conn.setautocommit (FALSE);

try {
* * Query Clob object and lock/*
ResultSet rs = stmt.executequery ("Select Clobcol from Test_clob WHERE id= ' for UPDATE");
while (Rs.next ()) {
/* Get this CLOB to like * *
Oracle.sql.CLOB CLOB = (Oracle.sql.CLOB) rs.getclob ("Clobcol");
/* Make Overlay modification * *
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 submitted * * *
Conn.commit ();
catch (Exception ex) {
/* ERROR Roll back/*
Conn.rollback ();
Throw ex;
}

/* Restore original Submission Status * * *
Conn.setautocommit (Defaultcommit);
}

3, replace the CLOB (the original clob to like clear, replaced by a new CLOB to the image)

Copy Code code as follows:

public static void Clobreplace (String infile) throws Exception
{
/* Set does not automatically submit * *
Boolean defaultcommit = Conn.getautocommit ();
Conn.setautocommit (FALSE);

try {
* * Empty the original CLOB to like * *
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= ' for UPDATE");
while (Rs.next ()) {
/* Get this CLOB to like * *
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 submitted * * *
Conn.commit ();
catch (Exception ex) {
/* ERROR Roll back/*
Conn.rollback ();
Throw ex;
}

/* Restore original Submission Status * * *
Conn.setautocommit (Defaultcommit);
}

4, CLOB to the image read

Copy Code code as follows:

public static void Clobread (String outfile) throws Exception
{
/* Set does not automatically submit * *
Boolean defaultcommit = Conn.getautocommit ();
Conn.setautocommit (FALSE);

try {
/* Query CLOB to like * *
ResultSet rs = stmt.executequery ("select * from Test_clob WHERE id= ' 111");
while (Rs.next ()) {
/* Get CLOB to like * *
Oracle.sql.CLOB CLOB = (Oracle.sql.CLOB) rs.getclob ("Clobcol");
/* Output in character form * *
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 ex) {
Conn.rollback ();
Throw ex;
}

/* Restore original Submission Status * * *
Conn.setautocommit (Defaultcommit);
}

Second, the access of BLOB objects

1. Insert a new BLOB image into the database

Copy Code code as follows:

public static void Blobinsert (String infile) throws Exception
{
/* Set does not automatically submit * *
Boolean defaultcommit = Conn.getautocommit ();
Conn.setautocommit (FALSE);

try {
/* Insert an empty blob to like * *
Stmt.executeupdate ("INSERT into Test_blob VALUES (' 222 ', Empty_blob ())");
* * Query this BLOB object and lock/*
ResultSet rs = stmt.executequery ("Select Blobcol from Test_blob WHERE id= ' for UPDATE");
while (Rs.next ()) {
/* Remove this blob to like * *
Oracle.sql.BLOB BLOB = (Oracle.sql.BLOB) rs.getblob ("Blobcol");
/* Writes data to a BLOB to the image * *
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 submitted * * *
Conn.commit ();
catch (Exception ex) {
/* ERROR Roll back/*
Conn.rollback ();
Throw ex;
}
/* Restore original Submission Status * * *
Conn.setautocommit (Defaultcommit);
}

2, modify the Blob image (is based on the original BLOB on the basis of the modified overlay)

Copy Code code as follows:

public static void Blobmodify (String infile) throws Exception
{
/* Set does not automatically submit * *
Boolean defaultcommit = Conn.getautocommit ();
Conn.setautocommit (FALSE);

try {
* * Query Blob object and lock/*
ResultSet rs = stmt.executequery ("Select Blobcol from Test_blob WHERE id= ' for UPDATE");
while (Rs.next ()) {
/* Remove this blob to like * *
Oracle.sql.BLOB BLOB = (Oracle.sql.BLOB) rs.getblob ("Blobcol");
/* Writes data to a BLOB to the image * *
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 submitted * * *
Conn.commit ();
catch (Exception ex) {
/* ERROR Roll back/*
Conn.rollback ();
Throw ex;
}

/* Restore original Submission Status * * *
Conn.setautocommit (Defaultcommit);
}

3, replace the Blob to the image (the original blob to the image clear, replaced by a new blob to the image)

Copy Code code as follows:

public static void Blobreplace (String infile) throws Exception
{
/* Set does not automatically submit * *
Boolean defaultcommit = Conn.getautocommit ();
Conn.setautocommit (FALSE);

try {
/* Empty the original blob to like * *
Stmt.executeupdate ("UPDATE test_blob SET blobcol=empty_blob () WHERE id= ' 222");
* * Query this BLOB object and lock/*
ResultSet rs = stmt.executequery ("Select Blobcol from Test_blob WHERE id= ' for UPDATE");
while (Rs.next ()) {
/* Remove this blob to like * *
Oracle.sql.BLOB BLOB = (Oracle.sql.BLOB) rs.getblob ("Blobcol");
/* Writes data to a BLOB to the image * *
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 submitted * * *
Conn.commit ();
catch (Exception ex) {
/* ERROR Roll back/*
Conn.rollback ();
Throw ex;
}

/* Restore original Submission Status * * *
Conn.setautocommit (Defaultcommit);
}

4, blob to image read

Copy Code code as follows:

public static void Blobread (String outfile) throws Exception
{
/* Set does not automatically submit * *
Boolean defaultcommit = Conn.getautocommit ();
Conn.setautocommit (FALSE);

try {
/* Query BLOB pair like * *
ResultSet rs = stmt.executequery ("Select Blobcol from Test_blob WHERE id= ' 222");
while (Rs.next ()) {
/* Remove this blob to like * *
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 submitted * * *
Conn.commit ();
catch (Exception ex) {
/* ERROR Roll back/*
Conn.rollback ();
Throw ex;
}

/* Restore original Submission Status * * *
Conn.setautocommit (Defaultcommit);
}

Looking at the access of the above program to LOB type fields, we can see that there are a few notable differences compared to other types of fields:

The first is that automatic submission must be canceled.