Java mysql blob 数据读写操作

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

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

本作品采用《CC 协议》,转载必须注明作者和本文链接
讨论数量: 0
(= ̄ω ̄=)··· 暂无内容!

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!
未填写
文章
234
粉丝
11
喜欢
34
收藏
35
排名:793
访问:8674
私信
所有博文
社区赞助商