Jdbc操作Blob数据

清疚 2023-07-01 03:24 14阅读 0赞

mysql数据库中Blob数据包括：TinyBlob、Blob、MediumBlob、LongBlob他们之间存储的数据大小不一致

类型 大小(单位：字节)  
TinyBlob 最大 255  
Blob 最大 65K  
MediumBlob 最大 16M  
LongBlob 最大 4G

Jdbc操作添加Blob数据

@Test
        public void blobTest(){
            Connection connection = null;
            PreparedStatement ps = null;
            FileInputStream fis = null;
            try
            {
                //  1.获取数据库连接
                 connection = JdbcUtil.getConnection();
                String sql = "insert into dome2(img) values(?)";
                //  2.得到PreparedStatement
                 ps = connection.prepareStatement(sql);
                //  3.填充占位符
                 fis = new FileInputStream(new File("img1.png"));
                ps.setBlob(1,fis);
                //  4.执行
                ps.execute();
    
                System.out.println("添加成功");
            }
            catch (Exception e)
            {
                e.printStackTrace();
            }
            finally
            {
                //  5. 关闭流
                JdbcUtil.closes(connection,ps);
    
                try
                {
                    if(fis !=null)
                    fis.close();
                }
                catch (IOException e)
                {
                    e.printStackTrace();
                }
            }
    
    
        }

查询并下载图片操作

@Test
        public void blobTest2(){
            Connection connection = null;
            PreparedStatement ps = null;
            FileInputStream fis = null;
            InputStream binaryStream = null;
            FileOutputStream fos = null;
            ResultSet rs = null;
            try
            {
                //  1.获取数据库连接
                connection = JdbcUtil.getConnection();
                String sql = "select img,id from dome2 where id=?";
                //  2.得到PreparedStatement
                ps = connection.prepareStatement(sql);
                //  3.填充占位符
               ps.setInt(1,2);
                //  4.执行
                 rs = ps.executeQuery();
                //  遍历
                if(rs.next()){
                    Blob blob = rs.getBlob("img");
                    int id = rs.getInt("id");
    
                    //  将Blob文件下载下来
                     binaryStream = blob.getBinaryStream();
    
                     fos =new FileOutputStream(new File("img2.png"));
                    byte [] bytes= new byte[1024];
                    int len= 0;
                    while ((len=binaryStream.read()) != -1){
                        fos.write(bytes,0,len);
                    }
    
    
                }
    
            }
            catch (Exception e)
            {
                e.printStackTrace();
            }
            finally
            {
                //  5. 关闭资源
                JdbcUtil.closes(connection,ps,rs);
                try
                {
                    if(binaryStream !=null)
                    binaryStream.close();
                }
                catch (IOException e)
                {
                    e.printStackTrace();
                }
                try
                {
                    if(fos !=null)
                    fos.close();
                }
                catch (IOException e)
                {
                    e.printStackTrace();
                }
                try
                {
                    if(fis !=null)
                        fis.close();
                }
                catch (IOException e)
                {
                    e.printStackTrace();
                }
            }
        }