package practice2;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.NumberFormat;
import java.util.Locale;
import javax.swing.JOptionPane;

public class PreparedStatementTest {
    public static void main(String[] args) {

        String url = "jdbc:derby://localhost:1527/EmployeeDB";
        String user = "test";
        String pass = "tiger";

        // SQL Injection (隱碼攻擊)
        // 輸入 1' OR '1'='1
        String inputFirstName = JOptionPane.showInputDialog("請輸入要查詢的員工 first name");

        String query = "select * from employee where firstname=?"; // 使用問號代表參數，之後設定
        System.out.println("query = " + query);

        // try-with-resource (自動關閉資源)
        try (
            Connection con = DriverManager.getConnection(url, user, pass);
            PreparedStatement pstmt = con.prepareStatement(query);  // PreparedStatement
            ) {

            pstmt.setString(1, inputFirstName);     // 設定第一個 ? 參數值，因為 firstname欄位是字串型別，所以使用 setString()
            ResultSet rs = pstmt.executeQuery();    // 執行查詢
            int count = 0; // 記錄找到幾筆資料
            while (rs.next()) {
                count++;
                int id = rs.getInt("id");
                String firstName = rs.getString("firstname");
                String lastName = rs.getString("lastname");
                java.util.Date birthdate = rs.getDate("birthdate");
                float salary = rs.getFloat("salary");
                // 格式化字串
                String s = String.format("%d \t %-20s %s %15s",
                        id,
                        firstName + " " + lastName,
                        birthdate,
                        NumberFormat.getCurrencyInstance(Locale.US).format(salary));
                // 輸出目前所讀到的員工資料
                System.out.println(s);
            }
            if(count == 0) {
                System.out.println("查無此人");
            }

        } catch (SQLException ex) {
            System.out.println(ex);
        } // 無須寫 finally 來 close() 資源
    }
}