Java连接Mysql数据库存取图片

1、创建数据库

CREATE TABLE `img_table`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `photo` mediumblob NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

2、数据库连接

package chapter.img;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

/**
 * @author Wang
 * @date 2021/12/24 0024 16:27
 * description
 */
public class DBUtil {
    public static final String DRIVER_CLASS_NAME = "com.mysql.jdbc.Driver";

    public static final String URL = "jdbc:mysql://localhost:3306/test??useUnicode=true&useSSL=false&characterEncoding=utf8&serverTimezone=Asia/Shanghai";

    public static final String USERNAME = "root";

    public static final String PASSWORD = "";

    static {
        try {
            Class.forName(DRIVER_CLASS_NAME);
        }
        catch (ClassNotFoundException e){
            System.out.println("注册失败！");
            e.printStackTrace();
        }
    }

    public static Connection getConn() throws SQLException {
        return DriverManager.getConnection(URL, USERNAME, PASSWORD);
    }

    // 关闭连接
    public static void closeConn(Connection connection){
        if(null != connection){
            try {
                connection.close();
            }
            catch (SQLException e){
                System.out.println("关闭数据库连接失败！");
                e.printStackTrace();
            }
        }
    }
}

3、图片流

package chapter.img;

import java.io.*;

/**
 * @author Wang
 * @date 2021/12/24 0024 16:33
 * description
 */
public class ImageUtil {

    // 读取本地图片获取输入流
    public static FileInputStream readImage(String path) throws FileNotFoundException {
        return new FileInputStream(new File(path));
    }

    // 读取表中图片获取输入流
    public static void readBin2Image(InputStream in, String targetPath){
        File file = new File(targetPath);
        String path = targetPath.substring(0, targetPath.lastIndexOf("/"));
        if(!file.exists()){
            new File(path).mkdir();
        }
        FileOutputStream fos = null;
        try {
            fos = new FileOutputStream(file);
            int len = 0;
            byte[] buf = new byte[1024];
            while ((len = in.read(buf)) != -1){
                fos.write(buf, 0, len);
            }
            fos.flush();
        }
        catch (Exception e){
            e.printStackTrace();
        }
        finally {
            if(null != fos){
                try {
                    fos.close();
                }
                catch (IOException e){
                    e.printStackTrace();
                }
            }
        }
    }
}

4、转码存储

package chapter.img;

import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @author Wang
 * @date 2021/12/24 0024 16:41
 * description
 */
public class ImageDemo {

    // 将图片插入数据库
    public static void readImage2DB(){
        String path = "F:\\04.JavaLearning\\QianFengEdu\\src\\chapter\\images\\1.png";
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        FileInputStream in = null;
        try {
            in = ImageUtil.readImage(path);
            connection = DBUtil.getConn();
            String sql = "INSERT INTO img_table(name, type, photo) VALUES(?, ?, ?)";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, "curry");
            preparedStatement.setString(2,"nba");
            preparedStatement.setBinaryStream(3, in, in.available());
            int count = preparedStatement.executeUpdate();
            if(count > 0){
                System.out.println("插入成功！");
            }else{
                System.out.println("插入失败！");
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtil.closeConn(connection);
            if(null != preparedStatement){
                try{
                    preparedStatement.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }
        }
    }

    // 读取数据库中的图片
    public static void readDB2Image(){
        String targetPath = "C:/Users/Administrator/Desktop/svg/2.png";
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = DBUtil.getConn();
            String sql = "SELECT * FROM img_table WHERE id = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, 1);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                InputStream in = resultSet.getBinaryStream("photo");
                ImageUtil.readBin2Image(in, targetPath);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtil.closeConn(connection);
            if(resultSet != null){
                try {
                    resultSet.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }
            if(preparedStatement != null){
                try {
                    preparedStatement.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }
        }
    }

    public static void main(String[] args){
        // 插入图片
//        readImage2DB();
        // 取出图片
        readDB2Image();
    }
}

5、实现效果

插入数据库的信息

取到的图片