java-oracle-blob对excle进行上传和下载的一些处理

1：首先是创建表

package Excle;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 stubUtil util=new Util();String sql1="drop table maexcle";String sql2="create table maexcle(" +"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 maexcle");while(rs.next()){System.out.println(rs.toString());}*/} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}

2：Excle插入数据库

package Excle;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 InsertExcle {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 maexcle(filename,filesize,filebody) values(?,?,EMPTY_BLOB())";ps=u.conn.prepareStatement(mysql);ps.setString(1,"excle1");ps.setInt(2, flength);ps.executeUpdate();ps.clearParameters();//插入其它数据后，定位BLOB字段===================u.conn.setAutoCommit(false);// 如果不关闭会报-->"错误:读取违反顺序"mysql="select filebody from maexcle where filename=? for update";ps=u.conn.prepareStatement(mysql);ps.setString(1, "excle1");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 blocke.printStackTrace();}}}}

3：Excle从数据库下载并生成文件

package Excle;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 DownExcle {/** * @param args */public static void main(String[] args) {// TODO Auto-generated method stubUtil 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 maexcle where filename=?";ps=u.conn.prepareStatement(mysql);ps.setString(1,"excle1");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 blocke.printStackTrace();}}}}

4：数据库连接

package Excle;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 blocke.printStackTrace();}}/*public static void main (String[]args){Util u=new Util();if(u.conn!=null){System.out.println("连接上了");}}*/}