Jdbc操作Blob数据

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();
            }
        }
    }