java-oracle-blob对excel进行下传和上载的一些处理(转)
java-oracle-blob对excel进行上传和下载的一些处理(转)
文章出自：http://blog.****.net/shuinianshui/article/details/6223871
1：首先是创建表
package Excel;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class CreatTable {
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
Util util=new Util();
String sql1="drop table maexcel";
String sql2="create table maexcel(" +
"filename varchar2(50),filesize number(20),filebody blob,primary key(filename))";
try {
Statement stat=util.conn.createStatement();
// stat.executeUpdate(sql1);
System.out.println(sql2);
stat.execute(sql1);System.out.println("删除成功");
stat.execute(sql2);System.out.println("创建成功");
util.conn.commit();
/* ResultSet rs=stat.executeQuery("select * from maexcel");
while(rs.next()){
System.out.println(rs.toString());
}*/
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
2：Excel插入数据库
package Excel;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import oracle.sql.BLOB;
public class InsertExcel {
public static void main(String[]args){
Util u=new Util();
byte[] data=null;//**将测试文件out.xls读入此字节数组
FileInputStream fis=null;
FileOutputStream fos=null;
OutputStream os=null;
ResultSet rs=null;//**这里rs一定要用Oracle提供的
PreparedStatement ps=null;//**PreparedStatement用Oracle提供的
try{
File file=new File("D://out.xls");
fis=new FileInputStream(file);
int flength=(int)file.length();
System.out.println("file length is:"+flength+"===========");
data=new byte[flength];
int itotal=0;int i=0;
for(;itotal<flength;itotal++){
i=fis.read(data, itotal,flength-itotal);
}
fis.close();
System.out.println("read itotal :"+itotal+"===========");
String mysql="insert into maexcel(filename,filesize,filebody) values(?,?,EMPTY_BLOB())";
ps=u.conn.prepareStatement(mysql);
ps.setString(1,"excel1");
ps.setInt(2, flength);
ps.executeUpdate();
ps.clearParameters();
//插入其它数据后，定位BLOB字段===================
u.conn.setAutoCommit(false);// 如果不关闭会报-->"错误:读取违反顺序"
mysql="select filebody from maexcel where filename=? for update";
ps=u.conn.prepareStatement(mysql);
ps.setString(1, "excel1");
rs=ps.executeQuery();
if(rs.next()){
BLOB blob= (BLOB) rs.getBlob(1);//得到BLOB字段
os=blob.getBinaryOutputStream();
// data是传入的byte数组，定义：byte[] data
os.write(data, 0, data.length);
os.flush();
os.close();
u.conn.commit();
rs.close();
}
System.out.println("insert into ok=====================");
}catch(Exception e){
e.printStackTrace();
}finally{
try {
fis.close();
fos.close();
rs.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
3：Excel从数据库下载并生成文件
package Excel;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import oracle.sql.BLOB;
public class DownExcel {
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
Util u=new Util();
byte[] data=null;//**将测试文件test.doc读入此字节数组
FileInputStream fis=null;
FileOutputStream fos=null;
OutputStream os=null;
ResultSet rs=null;//**这里rs一定要用Oracle提供的
PreparedStatement ps=null;//**PreparedStatement用Oracle提供的
try{
String mysql="select filebody,filesize from maexcel where filename=?";
ps=u.conn.prepareStatement(mysql);
ps.setString(1,"excel1");
rs=ps.executeQuery();
int flength=0;
if(rs.next()){
BLOB blob=(BLOB) rs.getBlob(1);
System.out.println("blob length is "+blob.getLength());
data=blob.getBytes(1,rs.getInt(2));//从BLOB取出字节流数据
flength=data.length;
System.out.println("data length is "+flength);
u.conn.commit();
}
rs.close();
//将blob取出的数据写入文件
fos=new FileOutputStream(new File("D:/testout.xls"));
fos.write(data, 0,data.length );
System.out.println("文件下载成功---------》》");
}catch(Exception e){
e.printStackTrace();
}finally{
try {
fos.close();
rs.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
4：数据库连接
package Excel;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class Util {
static Connection conn=null;
public Util() {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn=DriverManager.getConnection("jdbc:oracle:thin:@192.168.90.204:1521:power","gdtmis","gdtmis");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/* public static void main (String[]args){
Util u=new Util();
if(u.conn!=null){
System.out.println("连接上了");
}
}*/
}
el : 自已做练习的小例子
Connection conn = DBUtil.getConnection();
conn.setAutoCommit(false);
Statement statement = conn.createStatement();
String s = " select t.ws_template_id,t.ws_template_name,t.src_template from WS_TEMPLATE t where t.ws_kind = '30' and t.ws_template_id != '529' " ;
ResultSet resultSet = statement.executeQuery(s) ;
byte[] data = null ;
FileOutputStream fos = null ;
int i=1;
while(resultSet.next()){
//resultSet.next();
String wsid = resultSet.getString(1) ;
System.out.println("wsid="+wsid) ;
Blob blob = (Blob) resultSet.getBlob(3);
data = blob.getBytes(1,99999999);
String filename = "D:/xml/"+wsid+".xls" ;
//System.out.println(filename);
fos = new FileOutputStream(new File(filename));
fos.write(data, 0,data.length );
fos.flush();
fos.close();
System.out.println(i++);
//System.out.println(data.length) ;
}
System.out.println("success") ;