ODBC，实现图片循环写入Oracle数据库

 

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
*java连接oracle
*/
public class TestImage {

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

static {
try {
// 加载Oracle驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
// 获得连接
conn = DriverManager.getConnection(
"jdbc:oracle:thin:@10.117.10.5:1521:dqjz1", "jwzh",
"jwzh");
stmt = conn.createStatement();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}

/**
* 关闭所有与数据库相关的连接
*
* @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();
}
}
}

/**
* 读取图片并入库
*/
public void insertDB() {
// 历史表与路径表关联查询
String sql = " select rownum, t2.oldfwbh, t2.olddybh, t2.oldbzdzbm, t1.tplj,t2.newfwbh from test_jgt t1, test_jgt_lsb_20130401 t2 where t1.fwbh = t2.oldfwbh and t1.sfdrcg='0' ";
String dir = "";
String[][] res;
try {
res = querySql(conn, sql);
if (res != null) {
// 循环取出照片
for (int i = 0; i < res.length; i++) {
dir = "C:\\test\\images\\images";
dir += "\\" + res[i][3] + "\\" + res[i][2] + "\\"
+ res[i][1] + "\\" + res[i][4];
// 获得fjbh(主键)
String seqId = getSequenceValue("SEQ_COMMON_SERIVAL_NUMBER",conn) ;
//String seqId = "1" ;
System.out.println(seqId+"----"+dir);
// 逐个插入
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 {
// 关闭相应数据库连接
closeAll(rs, stmt, conn);
}
}

/**
* 向数据库中插入图片
* @param args
* @param impImageDir
*/
public void inputImage(String[] args, String impImageDir) {
try {

conn.setAutoCommit(false);// 取消自动提交功能
OutputStream os = null;
// 插入一个空对象empty_blob()
//stmt.executeUpdate("insert into image_lob (t_id, t_image) values ('"+args[0]+"', empty_blob())");
stmt.executeUpdate("insert into test_attachment (fjbh, dah, filename, data, cjsj, fjlx) values ('"+args[0]+"','"+args[1]+"','"+args[2]+"', empty_blob(), sysdate, '"+args[3]+"')");

// 锁定数据行进行更新，注意"for update"语句
//rs = stmt.executeQuery("select t_image from image_lob where t_id='" + args[0] + "' for update");
rs = stmt.executeQuery("select data from test_attachment where fjbh='" + args[0] + "' for update");

if (rs.next()) {
// 得到java.sql.Blob对象后强制转换为oracle.sql.BLOB
oracle.sql.BLOB blob = (oracle.sql.BLOB) rs.getBlob("data");
// 通过getBinaryOutputStream()方法获得向数据库中插入图片的"管道"
os = blob.getBinaryOutputStream();
// 读取想要存储的图片文件
InputStream is = new FileInputStream(impImageDir);
// 依次读取流字节,并输出到已定义好的数据库字段中.
int i = 0;
while ((i = is.read()) != -1) {
os.write(i);
}
}
os.flush();
os.close();
// 图片入库成功
stmt.executeUpdate("update test_jgt set sfdrcg='1' where fwbh = '"+args[4]+"'") ;
conn.commit();
conn.setAutoCommit(true);// 恢复现场
} 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();
// 本地图片不存在
stmt.executeUpdate("update test_jgt set sfdrcg='2' where fwbh = '"+args[4]+"'") ;
conn.commit();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
}

/**
* 从数据库里检索出图片
*/
public void outputImage() {
try {
String sql = "select image from t_image where id=1";
stmt = conn.createStatement();
rs = stmt.executeQuery(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);
}
}

/**
* 执行查询语句,获得返回结果
* @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("无效的SQL语句！");
if (conn == null)
throw new Exception("获取数据库连接失败！");
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.executeQuery(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;
}

/**
* 获得序列
* @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.executeQuery("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) {
// 从硬盘提取图片插入到数据库中
// new TestImage().inputImage();
// 从数据库中检索图片到硬盘
// new TestImage().outputImage();
new TestImage().insertDB();
}
}