Full Control of Blob clob in Oracle using Java)

 

Many Java operations on blob and clob fields in Oracle on the network are not comprehensive, some are inaccurate, and some are even nonsense. The recent project uses this knowledge and summarizes it here.
Environment:
Database: Oracle 9i
APP server: BEA WebLogic 8.14
Table Structure:
Create Table testblob (ID int, name varchar2 (20), blobattr BLOB)
Create Table testblob (ID int, name varchar2 (20), clobattr clob)
Java can access and operate databases through JDBC or JNDI. There are some differences between the two methods, java provided by the database connection obtained through the database connection pool JNDI of the app server. SQL. blob and Java. SQL. the clob implementation class is different from that provided by the JDBC Method. Therefore, you need to treat the warehouse receiving operations separately. There is no such difference in warehouse picking operations, so you do not need to treat them separately.

1. Blob operations
1. warehouse receiving
(1) JDBC Method
// Obtain the database connection through JDBC
Class. forname ("oracle. JDBC. Driver. oracledriver ");
Connection con = drivermanager. getconnection ("JDBC: oracle: thin: @ localhost: 1521: testdb", "test", "test ");
Con. setautocommit (false );
Statement ST = con. createstatement ();
// Insert an empty object empty_blob ()
St.exe cuteupdate ("insert into testblob (ID, name, blobattr) values (1," thename ", empty_blob ())");
// Lock the data row for update. Pay attention to the "for update" statement.
Resultset rs = st.exe cutequery ("select blobattr from testblob where id = 1 for update ");
If (Rs. Next ())
{
// Obtain the java. SQL. BLOB Object and convert it to Oracle. SQL. Blob.
Oracle. SQL. Blob blob = (Oracle. SQL. Blob) Rs. getblob ("blobattr ");
Outputstream outstream = blob. getbinaryoutputstream ();
// Data is the input byte array, defined as byte [] Data
Outstream. Write (data, 0, Data. Length );
}
Outstream. Flush ();
Outstream. Close ();
Con. Commit ();
Con. Close ();
(2) JNDI Mode
// Obtain the database connection through JNDI
Context context = new initialcontext ();
DS = (datasource) Context. Lookup ("ora_jndi ");
Connection con = Ds. getconnection ();
Con. setautocommit (false );
Statement ST = con. createstatement ();
// Insert an empty object empty_blob ()
St.exe cuteupdate ("insert into testblob (ID, name, blobattr) values (1," thename ", empty_blob ())");
// Lock the data row for update. Pay attention to the "for update" statement.
Resultset rs = st.exe cutequery ("select blobattr from testblob where id = 1 for update ");
If (Rs. Next ())
{
// Obtain the java. SQL. BLOB Object and convert it to weblogic. JDBC. Vendor. Oracle. oraclethinblob (the corresponding app server may be different)
Weblogic. JDBC. Vendor. Oracle. oraclethinblob blob = (weblogic. JDBC. Vendor. Oracle. oraclethinblob) Rs. getblob ("blobattr ");
Outputstream outstream = blob. getbinaryoutputstream ();
// Data is the input byte array, defined as byte [] Data
Outstream. Write (data, 0, Data. Length );
}
Outstream. Flush ();
Outstream. Close ();
Con. Commit ();
Con. Close ();
2. Warehouse picking
// Obtain the database connection
Connection con = connectionfactory. getconnection ();
Con. setautocommit (false );
Statement ST = con. createstatement ();
// "For update" is not required"
Resultset rs = st.exe cutequery ("select blobattr from testblob where id = 1 ");
If (Rs. Next ())
{
Java. SQL. Blob blob = Rs. getblob ("blobattr ");
Inputstream instream = blob. getbinarystream ();
// Data is the data that is read and needs to be returned. The type is byte []
Data = new byte [input. Available ()];
Instream. Read (data );
Instream. Close ();
}
Instream. Close ();
Con. Commit ();
Con. Close ();
Ii. clob operations
1. warehouse receiving
(1) JDBC Method
// Obtain the database connection through JDBC
Class. forname ("oracle. JDBC. Driver. oracledriver ");
Connection con = drivermanager. getconnection ("JDBC: oracle: thin: @ localhost: 1521: testdb", "test", "test ");
Con. setautocommit (false );
Statement ST = con. createstatement ();
// Insert an empty object empty_clob ()
St.exe cuteupdate ("insert into testclob (ID, name, clobattr) values (1," thename ", empty_clob ())");
// Lock the data row for update. Pay attention to the "for update" statement.
Resultset rs = st.exe cutequery ("select clobattr from testclob where id = 1 for update ");
If (Rs. Next ())
{
// Obtain the java. SQL. clob object and convert it to Oracle. SQL. clob.
Oracle. SQL. clob = (Oracle. SQL. clob) Rs. getclob ("clobattr ");
Writer outstream = clob. getcharacteroutputstream ();
// Data is the input string, defined as string data
Char [] C = data. tochararray ();
Outstream. Write (C, 0, C. Length );
}
Outstream. Flush ();
Outstream. Close ();
Con. Commit ();
Con. Close ();
(2) JNDI Mode
// Obtain the database connection through JNDI
Context context = new initialcontext ();
DS = (datasource) Context. Lookup ("ora_jndi ");
Connection con = Ds. getconnection ();
Con. setautocommit (false );
Statement ST = con. createstatement ();
// Insert an empty object empty_clob ()
St.exe cuteupdate ("insert into testclob (ID, name, clobattr) values (1," thename ", empty_clob ())");
// Lock the data row for update. Pay attention to the "for update" statement.
Resultset rs = st.exe cutequery ("select clobattr from testclob where id = 1 for update ");
If (Rs. Next ())
{
// Obtain the java. SQL. clob object and convert it to weblogic. JDBC. Vendor. Oracle. oraclethinclob (the corresponding app server may be different)
Weblogic. JDBC. Vendor. Oracle. oraclethinclob clob = (weblogic. JDBC. Vendor. Oracle. oraclethinclob) Rs. getclob ("clobattr ");
Writer outstream = clob. getcharacteroutputstream ();
// Data is the input string, defined as string data
Char [] C = data. tochararray ();
Outstream. Write (C, 0, C. Length );
}
Outstream. Flush ();
Outstream. Close ();
Con. Commit ();
Con. Close ();
2. Warehouse picking
// Obtain the database connection
Connection con = connectionfactory. getconnection ();
Con. setautocommit (false );
Statement ST = con. createstatement ();
// "For update" is not required"
Resultset rs = st.exe cutequery ("select clobattr from testclob where id = 1 ");
If (Rs. Next ())
{
Java. SQL. clob = Rs. getclob ("clobattr ");
Reader instream = clob. getcharacterstream ();
Char [] C = new char [(INT) clob. Length ()];
Instream. Read (C );
// Data is read and the data to be returned is of the string type.
Data = new string (C );
Instream. Close ();
}
Instream. Close ();
Con. Commit ();
Con. Close ();
Note:
1. Differences between Java. SQL. Blob, Oracle. SQL. Blob, weblogic. JDBC. Vendor. Oracle. oraclethinblob
2. Differences between Java. SQL. clob, Oracle. SQL. clob, weblogic. JDBC. Vendor. Oracle. oraclethinclob

Trackback: http://tb.blog.csdn.net/TrackBack.aspx? Postid = 839235