Binary storage picture

Keywords: MySQL Database

//This is a simple case - using MySQL database to store images in binary form
The type of our database should be: mediumblob type
Reason: in order to prevent the saved image from exceeding the storage capacity limit, the maximum storage capacity of mediablob is 16M.
In MySQL, blob is a binary large object, a container that can store a lot of data, and it can hold different sizes of data. Blob type is actually a type series (tinyblob, blob, mediumblob, longblob). Apart from the difference in the maximum amount of information stored, they are the same.

type Size (bytes - maximum)
tinyblob 255
blob 65K
mediumblob 16M
longblob 4G

Here is a code display of the case

public class TestSaveImager {
    public static Connection con = null;
    public static PreparedStatement ps = null;
    public static ResultSet rs = null;
    public static void testSave(){
        try {
//This is a custom data
            File file = new File("C:\\Users\\Administrator\\Downloads\\20190622063517.png");
            FileInputStream inputStream = new FileInputStream(file);
            con = DBUtil.getConnection();
            ps = con.prepareStatement("insert into student(userid,name,sex,age,imager) values(?,?,?,?,?)");
            ps.setInt(1,1);
            ps.setString(2,"Hello!");
            ps.setString(3,"female");
            ps.setString(4,"24");
//Write with stream when storing
        ps.setBinaryStream(5,inputStream,(int)file.length());
        ps.executeUpdate();
        inputStream.close();
         }catch (FileNotFoundException e){
         e.printStackTrace();
       }catch (IOException e){
         e.printStackTrace();
       }catch (SQLException e){
        e.printStackTrace();
    }
}
    public static void main(String[] args) {
        readerJpg();
    }
//Read binary image
public static void readerJpg(){
    try {
        con = DBUtil.getConnection();
        String log = "SELECT name,sex,imager FROM student WHERE studentid = 9";
        File file = new File("C:\\Users\\Administrator\\Downloads\\desktop.jpg");
        if (file.exists()){
            try {
                file.createNewFile();
            }catch (IOException e){
                e.printStackTrace();
            }
        }
        try {
            byte[] Buffer = new byte[4095*5];
            ps = con.prepareStatement(log);
            rs = ps.executeQuery();
            while (rs.next()){
                rs.getString("name");
                rs.getString("sex");
                FileOutputStream outputStream = new FileOutputStream(file);
                InputStream inputStream = rs.getBinaryStream("imager");
                int size = 0;
                while ((size = inputStream.read(Buffer))!=-1){
                    System.out.println(size);
                    outputStream.write(Buffer,0,size);
                }
            }
        }catch (IOException e){
            e.printStackTrace();
        }
    }catch (SQLException e){
        e.printStackTrace();
    }
}

Posted by gdogfunk on Wed, 23 Oct 2019 15:22:13 -0700