Java mysql blob 数据读写操作

拼搏现实的明天。 2022-10-13 13:59 145阅读 0赞

package com.lw.database;
    
    import java.io.FileInputStream;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    
    /**
     * CREATE: CREATE TABLE IDCard ( id char(18),pic BLOB);
     * @author fhadmin
     * from www.fhadmin.cn
     */
    public class LOBTest {
    
        protected static final String DEFAULT_URL = "jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf8";
        protected static final String DRIVER_NAME = "com.mysql.jdbc.Driver";
        
        private Connection connection = null;
        
        public LOBTest() throws ClassNotFoundException, SQLException {
            Class.forName(DRIVER_NAME);
            connection = DriverManager.getConnection(DEFAULT_URL, "user", "password");
        }
        
        public void insert(String id,String path) throws SQLException, IOException {
            PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO IDCard VALUES (?,?)");
            preparedStatement.setString(1, id);
            FileInputStream fileInputStream = new FileInputStream(path);
            preparedStatement.setBlob(2, fileInputStream,fileInputStream.available());
            preparedStatement.execute();
        }
        
        public void get(String id) throws SQLException, IOException {
            PreparedStatement preparedStatement = connection.prepareStatement("SELECT pic FROM IDCard WHERE id = ?");
            preparedStatement.setString(1, id);
            ResultSet results = preparedStatement.executeQuery();
            while(results.next()) {
                FileOutputStream outputStream = new FileOutputStream("/Users/liuwei/temp.png");
                InputStream inputStream = results.getBinaryStream(1);
                int num = -1;
                while((num=inputStream.read())!=-1) {
                    outputStream.write(num);
                }
                outputStream.flush();
                inputStream.close();
                outputStream.close();
            }
        }
        
        public static void main(String[] args) throws ClassNotFoundException, SQLException, IOException {
            LOBTest test = new LOBTest();
            test.insert("78907656784323", "/Users/liuwei/Documents/bt_next_nor.png");
            test.get("78907656784323");
        }
    }

注意：

MySQL的四种BLOB类型

类型  大小(单位：字节)

TinyBlob  最大 255B

Blob  最大 65K

MediumBlob  最大 16M

LongBlob  最大 4G

插入图像的时候，注意下图像大小，图像超过该类型所能容纳的最大字节的时候，会报错