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("连接上了"); } }*/ }