import java.io.*;
import java.sql.*;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;


public class Database {


  //JDBC驅動名
  String JDBC_DRIVER = "com.mysql.jdbc.Driver";

  //數據庫URL：這里的tt是數據庫名稱
  String JDBC_URL = "jdbc:mysql://localhost:3306/daImage?useSSL=false&serverTimezone=UTC";

  //    數據庫的用戶名與密碼
  String USER = "root";
  String PASS = "admin123";

  //通過DriverManager類獲得該連接對象才能訪問數據庫
  Connection connection = null;

  //    通過Connection獲得該結果對象用于執行靜態的SQL語句
//  Statement statement = null;
  PreparedStatement preparedStatement = null;

  String path;
  FileInputStream fileInputStream;


  
  Database() {


//      注冊JDBC驅動
    try {
      Class.forName(JDBC_DRIVER);

//      數據庫的連接：通過DriverManager類的getConnection方法，傳入三個參數：數據庫URL、用戶名、用戶密碼，實例化connection對象
      connection = DriverManager.getConnection(JDBC_URL, USER, PASS);


    } catch (ClassNotFoundException e) {
      e.printStackTrace();
    } catch (SQLException e) {
      e.printStackTrace();
    }


  }



  public void add() {
    //      定義數據庫查詢語句：查詢aa表中的name、sex兩列數據
    String sql = "insert into taImage values(?,?,?) ";
//    讀取圖片，圖片放在電腦本地，所以我這里手動復制了路徑
    File file = new File("/Users/liuliu/Desktop/vv.jpeg");


    try {
      FileInputStream fi = new FileInputStream(file);
      preparedStatement = connection.prepareStatement(sql);
      preparedStatement.setInt(1, 2);
      preparedStatement.setString(2, "圖片一");
      preparedStatement.setBlob(3, fi);

//      執行查詢語句
      int f = preparedStatement.executeUpdate();

      if (f > 0) {
        System.out.println("插入成功");
      } else {
        System.out.println("插入失敗");
      }

      preparedStatement.close();
      connection.close();

    } catch (SQLException e) {
      e.printStackTrace();
    } catch (FileNotFoundException e) {
      e.printStackTrace();
    }


  }

  public void select() {

    Blob get_image;

    String sql = "select* from taImage";
    try {
//      將讀取到的圖片存放到指定的路徑中
      FileOutputStream fileOutputStream = new FileOutputStream("/Users/liuliu/Desktop/bb.jpg");

      preparedStatement = connection.prepareStatement(sql);

      ResultSet resultSet = preparedStatement.executeQuery();

      while (resultSet.next()) {

        get_image = resultSet.getBlob("photo");
//        將讀取到的Blob對象轉成字節流
        inputStream = get_image.getBinaryStream();
        int a;
        byte b[] = new byte[1014];
        while ((a = inputStream.read(b)) != -1) {
          fileOutputStream.write(b, 0, a);
        }


      }


    } catch (SQLException e) {
      e.printStackTrace();
    } catch (FileNotFoundException e) {
      e.printStackTrace();
    } catch (IOException e) {
      e.printStackTrace();
    }


  }


}