Full Control of Blob clob in Oracle using Java)

Source: Internet
Author: User

 

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.