ODBC to write images to the Oracle database cyclically

Source: Internet
Author: User

 

Import java. io. FileInputStream;
Import java. io. FileOutputStream;
Import java. io. IOException;
Import java. io. InputStream;
Import java. io. OutputStream;
Import java. SQL. Connection;
Import java. SQL. DriverManager;
Import java. SQL. ResultSet;
Import java. SQL. ResultSetMetaData;
Import java. SQL. SQLException;
Import java. SQL. Statement;
Import java. util. ArrayList;

/**
* @ Author SunRain
* Connecting oracle using java
*/
Public class TestImage {

Private static Connection conn = null;
Private static Statement stmt = null;
Private ResultSet rs = null;

Static {
Try {
// Load the Oracle driver
Class. forName ("oracle. jdbc. driver. OracleDriver ");
// Obtain the connection
Conn = DriverManager. getConnection (
"Jdbc: oracle: thin: @ 10.117.10.5: 1521: dqjz1", "ZH ",
"ZH ");
Stmt = conn. createStatement ();
} Catch (ClassNotFoundException e ){
E. printStackTrace ();
} Catch (SQLException e ){
E. printStackTrace ();
}
}

/**
* Close all database-related connections
*
* @ Param conn
* @ Param stmt
* @ Param rs
*/
Public void closeAll (ResultSet rs, Statement stmt, Connection conn ){
If (rs! = Null ){
Try {
Rs. close ();
} Catch (SQLException e ){
E. printStackTrace ();
}
}
If (stmt! = Null ){
Try {
Stmt. close ();
} Catch (SQLException e ){
E. printStackTrace ();
}
}
If (conn! = Null ){
Try {
Conn. close ();
} Catch (SQLException e ){
E. printStackTrace ();
}
}
}

/**
* Read images and store them into the database
*/
Public void insertDB (){
// Associate query between history tables and path tables
String SQL = "select rownum, t2.oldfwbh, t2.olddybh, limit, t1.tplj, t2.newfwbh from test_jgt t1, limit t2 where t1.fwbh = t2.oldfwbh and t1.sfdrcg = '0 '";
String dir = "";
String [] [] res;
Try {
Res = querySql (conn, SQL );
If (res! = Null ){
// Cyclically retrieve the photo
For (int I = 0; I <res. length; I ++ ){
Dir = "C: \ test \ images ";
Dir + = "\" + res [I] [3] + "\" + res [I] [2] + "\\"
+ Res [I] [1] + "\" + res [I] [4];
// Obtain the fjbh (primary key)
String seqId = getSequenceValue ("SEQ_COMMON_SERIVAL_NUMBER", conn );
// String seqId = "1 ";
System. out. println (seqId + "----" + dir );
// Insert one by one
String [] args = {seqId, res [I] [5], res [I] [4], "image/pjpeg", res [I] [1]};
InputImage (args, dir );
}
}
} Catch (SQLException e ){
E. printStackTrace ();
} Catch (Exception e ){
E. printStackTrace ();
} Finally {
// Close the connection to the database
CloseAll (rs, stmt, conn );
}
}

/**
* Insert images into the database
* @ Param args
* @ Param impImageDir
*/
Public void inputImage (String [] args, String impImageDir ){
Try {

Conn. setAutoCommit (false); // cancel the automatic submission Function
OutputStream OS = null;
// Insert an empty object empty_blob ()
// Stmt.exe cuteUpdate ("insert into image_lob (t_id, t_image) values ('" + args [0] + "', empty_blob ())");
Stmt.exe cuteUpdate ("insert into test_attachment (fjbh, dah, filename, data, cjsj, fjlx) values ('" + args [0] + "', '"+ args [1] +"', '"+ args [2] +"', empty_blob (), sysdate, '"+ args [3] + "') ");

// Lock the data row for update. Note the "for update" statement.
// Rs = stmt.exe cuteQuery ("select t_image from image_lob where t_id = '" + args [0] + "' for update ");
Rs = stmt.exe cuteQuery ("select data from test_attachment where fjbh = '" + args [0] + "' 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 ("data ");
// Use the getBinaryOutputStream () method to obtain the "Pipeline" for inserting images into the database"
OS = blob. getBinaryOutputStream ();
// Read the image file you want to store
InputStream is = new FileInputStream (impImageDir );
// Read the stream bytes in sequence and output them to the predefined database fields.
Int I = 0;
While (I = is. read ())! =-1 ){
OS. write (I );
}
}
OS. flush ();
OS. close ();
// Image warehouse receiving successful
Stmt.exe cuteUpdate ("update test_jgt set sfdrcg = '1' where fwbh = '" + args [4] + "'");
Conn. commit ();
Conn. setAutoCommit (true); // restore the site
} Catch (SQLException e ){
E. printStackTrace ();
Try {
Conn. rollback ();
} Catch (SQLException e1 ){
// TODO Auto-generated catch block
E1.printStackTrace ();
}
} Catch (IOException e ){
// E. printStackTrace ();
Try {
Conn. rollback ();
// The local image does not exist.
Stmt.exe cuteUpdate ("update test_jgt set sfdrcg = '2' where fwbh = '" + args [4] + "'");
Conn. commit ();
} Catch (SQLException e1 ){
E1.printStackTrace ();
}
}
}

/**
* Retrieve images from the database
*/
Public void outputImage (){
Try {
String SQL = "select image from t_image where id = 1 ";
Stmt = conn. createStatement ();
Rs = stmt.exe cuteQuery (SQL );
If (rs. next ()){
Oracle. SQL. BLOB B = (oracle. SQL. BLOB) rs. getBlob (1 );
InputStream is = B. getBinaryStream ();
FileOutputStream fos = new FileOutputStream (
"E: \ outputImage.jpg ");
Int I = 0;
While (I = is. read ())! =-1 ){
Fos. write (I );
}
Fos. flush ();
Fos. close ();
Is. close ();
}
} Catch (SQLException e ){
E. printStackTrace ();
} Catch (IOException e ){
E. printStackTrace ();
} Finally {
CloseAll (rs, stmt, conn );
}
}

/**
* Execute the query statement to obtain the returned results.
* @ Param conn
* @ Param SQL
* @ Return
* @ Throws SQLException
* @ Throws Exception
*/
Public String [] [] querySql (Connection conn, String SQL)
Throws SQLException, Exception {
If (SQL = null)
Throw new Exception ("invalid SQL statement! ");
If (conn = null)
Throw new Exception ("An error occurred while obtaining the database connection! ");
Conn. setAutoCommit (false );
ResultSet rs = null;
Statement stmt = null;
ResultSetMetaData md = null;

ArrayList aList = new ArrayList ();
Int rows = 0, cols;
Try {
Stmt = conn. createStatement ();
Rs = stmt.exe cuteQuery (SQL );
Md = rs. getMetaData ();
Cols = md. getColumnCount ();
While (rs. next ()){
String [] row = new String [md. getColumnCount () + 1];
For (int I = 0; I <md. getColumnCount (); I ++ ){
Row [I] = rs. getString (I + 1 );
}
AList. add (row );
}
Rs. close ();
Rs = null;
Stmt. close ();
Stmt = null;
} Catch (SQLException e ){
E. printStackTrace (System. out );
Throw new SQLException ("#71:" + e. toString ());
} Catch (Exception e ){
E. printStackTrace (System. out );
Throw e;
} Finally {
If (stmt! = Null)
Stmt. close ();
Stmt = null;
}

Rows = aList. size ();
If (rows = 0 | cols = 0 ){
AList. clear ();
AList = null;
Return null;
}
String [] [] res = new String [rows] [cols];
For (int I = 0; I <rows; I ++ ){
Object [] row = (Object []) aList. toArray () [I];
For (int j = 0; j <cols; j ++ ){
If (row [j] = null)
Res [I] [j] = new String ("");
Else
Res [I] [j] = new String (row [j]. toString ());
}
}
AList. clear ();
AList = null;
Return res;
}

/**
* Obtain the sequence
* @ Param seqname
* @ Param conn
* @ Return
* @ Throws SQLException
*/
Public static String getSequenceValue (String seqname, Connection conn)
Throws SQLException {
ResultSet rs = null;
Statement stmt = null;
String res = null;
Try {
Stmt = conn. createStatement ();
Rs = stmt.exe cuteQuery ("select" + seqname + ". nextval from dual ");
If (rs. next ()){
Res = rs. getString (1 );
}
Rs. close ();
Rs = null;
Stmt. close ();
Stmt = null;
} Catch (SQLException e ){
E. printStackTrace (System. out );
Throw e;
}
Return res;
}

Public static void main (String [] args ){
// Extract image from hard disk and insert it into database
// New TestImage (). inputImage ();
// Retrieve images from the database to the hard disk
// New TestImage (). outputImage ();
New TestImage (). insertDB ();
}
}

 

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.