jdbc blob插入及查询操作

首先建一张表

create table picture(
    picId char(6) primary key not null,
    picName nvarchar(20) not null, 
    picfile image null
)

insert操作

//存入一张图片
    static public void testInsert() {
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = JDBCUtils.getConnection();
            String sql = "insert into picture values(?,?,?)";
            ps = con.prepareStatement(sql);
            ps.setObject(1, "000000");
            ps.setObject(2, "zsben照片");
            File f = new File("zsben.jpg");
            FileInputStream is = new FileInputStream(f);
            ps.setBinaryStream(3, is, f.length());
            
            ps.execute();
            System.out.println("执行成功");
                    
        }
        catch(Exception ex) {
            ex.printStackTrace();
        }
        finally {
            JDBCUtils.closeResource(con, ps);
        }
    }

select 操作

//查询数据表中blob类型的字段
    static public void testQuery() {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet res = null;
        try {
            con = JDBCUtils.getConnection();
            String sql = "select picId,picName,picfile from picture where picId = ?";
            ps = con.prepareStatement(sql);
            ps.setObject(1, "000000");
        
            res = ps.executeQuery();
            if(res.next()) {
                String picId = res.getString(1);
                String picName = res.getString(2);
                
                Picture pic = new Picture(picId,picName);
                System.out.println(pic);
                
                Blob photo = res.getBlob(3);
                InputStream is = photo.getBinaryStream();
                FileOutputStream fos = new FileOutputStream("zsbenn.jpg");
                byte[] buffer = new byte[1024];
                int len;
                while((len = is.read(buffer))!=-1) {
                    fos.write(buffer,0,len);
                }
            }
            System.out.println("执行成功");
        }
        catch(Exception ex) {
            ex.printStackTrace();
        }
        finally {
            JDBCUtils.closeResource(con, ps, res);
        }
    }