java-oracle-blob对excel进行上传和下载的一些处理(转)
文章出自：http://blog.csdn.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 stubUtil 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 blocke.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 blocke.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 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 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 blocke.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 blocke.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") ;?
?
?