如何将图片插入数据库

如何将图片,Mp3 ,或是一些二进制类型的数据插入到sqlserver,或是 Oracle 数据库 . 方法是通过流进行操作.

创建一张测试表(sqlserver2000)

create table [pictable] (
    [id] [int] identity (1, 1) not null ,
    [img] [image] not null 
) on [primary] textimage_on [primary]
go

1,插入数据库的方法(sqlserver2000)

this.getConnection() 为获得连接的方法.

public void insertPic(String path){
        Connection con = this.getConnection();
        String sql = "insert into picTable values(?)" ;
        try {
            PreparedStatement pstm = con.prepareStatement(sql);
            InputStream is = new FileInputStream(path);
            
            pstm.setBinaryStream(1, is, is.available());
            int count = pstm.executeUpdate();
            if(count>0){
                System.out.println("插入成功");
            }else{
                System.out.println("插入失败");
            }
            is.close();
            pstm.close();
            con.close();
            
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

2,从数据库中读出来的方法.(sqlserver2000)

public void readPic(int id){
        Connection con = this.getConnection();
        String sql = "select  * from  picTable where id=?" ;
        try {
            PreparedStatement pstm = con.prepareStatement(sql);
            pstm.setInt(1, id);
            ResultSet rs = pstm.executeQuery();
            rs.next();
            InputStream is = rs.getBinaryStream(2);
            
            OutputStream os = new FileOutputStream("f:/temp.jpg");
            byte[] buff = new byte[1024];
            int len = is.read(buff);
            while( len !=-1 ){
                os.write(buff);
                len = is.read(buff);
            }
            System.out.println("写入成功");
            is.close();
            os.close();
            pstm.close();
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

3,插入数据库的方法(Oracle)

public void insertBinary() {
        Connection con = MyConnection.getORACLEConnection();
        String sql = "insert into testBinary values(?,?)";
        try {
            con.setAutoCommit(false);
            PreparedStatement pstm = con.prepareStatement(sql);
            pstm.setString(1, "a1");
            pstm.setBlob(2, oracle.sql.BLOB.empty_lob());
            int count = pstm.executeUpdate();
            pstm.close();
            pstm = con.prepareStatement("select  * from testBinary where id=?");
            pstm.setString(1, "a1");
            ResultSet rs = pstm.executeQuery();
            rs.next();
            oracle.sql.BLOB blob = (BLOB) rs.getBlob(2);
            OutputStream os = blob.getBinaryOutputStream();
            FileInputStream fi = new FileInputStream("E:/test.mp3");
            byte[] buff = new byte[1024];

            int len = fi.read(buff);

            while (len != -1) {
                os.write(buff);
                len = fi.read(buff);
            }
            pstm = con.prepareStatement(sql);
            pstm.setString(1, "a1");
            pstm.setBlob(2, blob);
            int res = pstm.executeUpdate();
            con.commit();
            pstm.close();
            con.close();

            if (res > 0) {
                System.out.println("success");
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }

    }

4,从数据库中读出来的方法.(Oracle)

public void readerBinaryStream() {
        Connection con = MyConnection.getORACLEConnection();
        try {
            java.sql.PreparedStatement pstm = con.prepareStatement(
                    "select * from testBinary where id='a1'");
            ResultSet rs = pstm.executeQuery();
            rs.next();
            oracle.sql.BLOB blob = (BLOB) rs.getBlob(2);
            InputStream is = blob.getBinaryStream();
            FileOutputStream fi = new FileOutputStream("f:/aaaa.mp3");
            byte[] buff = new byte[1024];
            int len = is.read(buff);
            while (len != -1) {
                fi.write(buff);
                len = is.read(buff);

            }
            fi.close();

        } catch (SQLException ex) {
        } catch (FileNotFoundException ex) {
          ex.printStackTrace();
        } catch (IOException ex) {
            ex.printStackTrace();
        }

    }