MySQL 中Blob類型資料的插入和讀取

​ 我们在操作数据存入blob数据的类型,常用来存储头像图片等流数据,blob类型如果想要存储比较大的流文件的数据,建议选用longBlob的数据类型,Demo中的数据就简单的示范了一下,sql文件如下:

DROP TABLE IF EXISTS `image_save`;
CREATE TABLE `image_save` (
  `image_name` varchar(255) DEFAULT NULL,
  `image_in` longblob
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;           

复制

​ 插入图片和读取图片到本机的操作如下:

public class BlobTest {

    public static void main(String[] args) throws IOException, SQLException {
        //把图片存为blob的格式到数据库
        // storePicBlog();

        //从数据库读取blob的格式的图片数据
        getPicBlog();
    }

    public static void storePicBlog() throws FileNotFoundException, SQLException, IOException {

        String m_dbDriver    ="com.mysql.jdbc.Driver";
        String m_dbUrl   ="jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC";
        Connection conn = DriverManager.getConnection(m_dbUrl, "root", "root");

        File f = new File("C:\\Users\\admin\\Desktop\\timg.jpg");
        FileInputStream fis = new FileInputStream(f);

        String sql = "insert into image_save(image_name,image_in) values(?,?)";

        PreparedStatement ps = conn.prepareStatement(sql);

        ps.setString(1, "临时图片");
        ps.setBinaryStream(2, fis, (int)f.length());

        ps.executeUpdate();

        conn.close();
        ps.close();
    }
    public static void getPicBlog() throws FileNotFoundException, SQLException, IOException {

        String m_dbDriver    ="com.mysql.jdbc.Driver";
        String m_dbUrl   ="jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC";
        Connection conn = DriverManager.getConnection(m_dbUrl, "root", "root");

        File f = new File("C:\\Users\\admin\\Desktop\\timg.jpg");
        FileInputStream fis = new FileInputStream(f);

        String sql = "select image_in from image_save where image_name='临时图片'";

        PreparedStatement ps = conn.prepareStatement(sql);
        ResultSet rs = ps.executeQuery(sql);
        Blob imageIn = null;
        while (rs.next()){
             imageIn = rs.getBlob("image_in");
        }
        InputStream in = imageIn.getBinaryStream();

        OutputStream out = new FileOutputStream("C:\\Users\\admin\\Desktop\\cat.jpg");
        byte[] buffer = new byte[1024];
        int len = 0;
        while((len = in.read(buffer)) != -1){
            out.write(buffer, 0, len);
        }
        in.close();
        out.close();

        conn.close();
        ps.close();
    }
}           

复制