使用JDK自带的JDBC-ODBC驱动对Access进行BLOB字段读写

 

MS Access在测试阶段比较利于携带，Web开发初期我经常使用它。后期再移植到SQLServer或Oracle上。但最近在需要对数据库中插入图片文件时，发现了一个问题，即JDK自带的JDBC-ODBC不支持java.sql.Blob里的方法，经过查阅Java API和程序调试，我找到了个变通的方法，即：

1，在写入BLOB类型字段时，使用java.sql.PreparedStatement的setBinaryStream方法，

2，读出BLOB类型字段时，因为返回的是字节数组byte[]类型，可以把它转换成ByteArrayInputStream然后读出内容写到文件里去。

这样即使用JDK自带的JDBC-ODBC驱动, 也能自如的在数据库里读写上传下载的文件了，哈哈。

代码如下：

import java.sql.*;

import java.io.*;

//对BLOB字段先写入（要求被写入的文件存在），再读出来

//要求先建立一个item表,有三个字段，id(int),file_name(char),file_blob(blob)

//对Access, blob字段应该设置成为“OLE对象”类型

 

public class blobtest{

 

    public static void main(String[] args){

        Connection conn = null;

        try{

   

            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

            /*这里的数据库的url一定要写正确,这是关键,其中DBQ可以绝对路径,也可以是相对路径,为了体现数据存储路径的/独立性,你可以将数据库copy到不同的位试一下*/

            String dbUrl = "jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=blob.mdb";

            conn = DriverManager.getConnection(dbUrl,"","");

           

            File file1=new File("fileToWrite.doc");   

            File file2=new File("fileRead.doc");

   

            //BlobWriteForOracle( conn, file1);

            //BlobReadForOracle( conn, file2);

            BlobWriteForAccess( conn, file1);

            BlobReadForAccess( conn, file2);

 

            conn.close();

           

        }catch(Exception ex){

            System.err.println(ex.getMessage());

        }             

       

    }

 

    public static void BlobWriteForAccess( Connection conn, File file){

   

        try{

             conn.setAutoCommit(false); // 取消Connection对象的auto commit属性

            String file_name=file.getName();

           

            // get maxid ( to avoid insert id repeatly )

            Statement stmt = conn.createStatement();

            ResultSet rs = stmt.executeQuery("select max(id) from item");

            rs.next();

            int maxid = rs.getInt(1);

            //maxid = (maxid==null)?0:maxid;

           

            int id = maxid+1 ;

            //System.out.println("write_id="+id);

           

            PreparedStatement pstmt = conn.prepareStatement( "insert into item ( id, file_name, file_blob ) values ( " + id + ", ? , ? )" );

            FileInputStream in = new FileInputStream(file );

            int length = in.available();

           

            pstmt.setString( 1, file_name );

            pstmt.setBinaryStream( 2, in , in.available() );

           

            System.out.println( "插入了 "+ pstmt.executeUpdate ()+ " 行数据, "

                                 + "id =" + id

                                 + ", 文件名是" + file.toString() +" , 共 "+ length +" bytes" );

 

            conn.commit();

            pstmt.close();

 

        }catch(Exception ex){

            ex.printStackTrace();

            System.out.print("["+ex.getMessage()+"]");

            try{

                conn.rollback();

            }catch(SQLException sqle){

                System.err.println(sqle.getMessage());

            }

        }   

   

    }

   

    public static void BlobReadForAccess( Connection conn, File file){

   

        try{

            conn.setAutoCommit(false); // 取消Connection对象的auto commit属性

            String file_name=file.getName();

           

            // get maxid ( to avoid insert id repeatly )

            Statement stmt1 = conn.createStatement();

            ResultSet rs1 = stmt1.executeQuery("select max(id) from item");

            rs1.next();

            int maxid = rs1.getInt(1);

            //maxid = (maxid==null)?0:maxid;

                       

            int id = maxid;

            //System.out.println("read_id="+id);

           

            String sql="SELECT file_blob FROM item WHERE id=" + id + ""; //

            Statement stmt=conn.createStatement();

            ResultSet rs=stmt.executeQuery(sql);

            rs.next();

            Object obj1 = rs.getObject("file_blob"); // 得到BLOB对象

            //System.out.println("type is :"+obj1.getClass().getName());

            byte[] blob=(byte[])obj1;

           

            FileOutputStream out=new FileOutputStream(file); // 建立输出流

            ByteArrayInputStream in=new ByteArrayInputStream(blob); // 建立输入流

           

            int size=1024;

            byte[] buffer=new byte[size]; // 建立缓冲区

            int len;

            while((len=in.read(buffer)) != -1)

                out.write(buffer,0,len);

            in.close();

            out.close();

           

            conn.commit();

        }catch(Exception ex){

            ex.printStackTrace();

            System.out.print("["+ex.getMessage()+"]");

            try{

                conn.rollback();

            }catch(SQLException sqle){

                 System.err.println(sqle.getMessage());

            }

        }   

   

    }   

 

}

(本文为yanqlv原创,转载请注明出处,谢谢)