ODBC to write images to the Oracle database cyclically

 

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