Java MySQL BLOB data read / write operation

Time:2021-7-30

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

be careful:

Four blob types of MySQL

Type size in bytes

Tinyblob Max 255b

Blob Max 65K

Mediumblob 16m Max

Longblob Max 4G

When inserting an image, pay attention to the image size. An error will be reported when the image exceeds the maximum bytes that can be accommodated by this type

This work adoptsCC agreement, reprint must indicate the author and the link to this article

Recommended Today

Implementation example of go operation etcd

etcdIt is an open-source, distributed key value pair data storage system, which provides shared configuration, service registration and discovery. This paper mainly introduces the installation and use of etcd. Etcdetcd introduction etcdIt is an open source and highly available distributed key value storage system developed with go language, which can be used to configure sharing […]