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 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") ;