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 stub
		Util 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 block
			e.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 block
				e.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 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 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 block
				e.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 block
			e.printStackTrace();
		}

	}
/*	public  static  void main (String[]args){
		Util u=new Util();
		if(u.conn!=null){
			System.out.println("连接上了");
		}

	}*/


}