数据库|Java连接Mysql数据库存取图片 mysql|java

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、实现效果插入数据库的信息