Days26: JDBC -- 優化篇

2022 iThome 鐵人賽

DAY 26

Software Development

大學耍廢的我要學Java翻身系列第 26 篇

14th鐵人賽

jhps9501

2022-10-10 23:49:10

859 瀏覽

分享至

補充

昨天有說到preparedment他的功能主要是防止駭客來竄改我們的code，還有if(rs.next())就是指rs有輸入資料的話就...

優化和實作我們資料庫

因為我們昨天的程式看起來亂遭糟的，而且功能看起來也讓人感覺模糊，所以今天就來把他優化一下，還有多做一個功能出來，如果還有不懂得歡迎留言發問喔

模組化我們的功能

因為避免我們程式寫得太過混亂或是不清楚功能所以我們就先把他們的特定功能標示出來

import java.sql.*;
import java.util.ArrayList;

public class Main {

    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        // 初始化我們的資料庫
        String sqlStatement = "select * from Person";
        Connection conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/maxdb", "Max", "12345678");
        if(conn != null){
            System.out.println("success for connecting to the database");
        } else {
            System.out.println("Fail to connect the database");
        }
        // 防止駭客入侵我們資料庫 + 優化性能
        PreparedStatement ps = conn.prepareStatement(sqlStatement);
        System.out.println(ps);
        ResultSet rs = ps.executeQuery();
        
        // 功能測試
        ArrayList<Person> result = new ArrayList<>();
        while (rs.next()){
            Person p = new Person(Integer.parseInt(rs.getString("personId")), rs.getString("personName"), Integer.parseInt(rs.getString("age")));
            result.add(p);
        }
        for (Person p:result) {
            System.out.println(p.toString());
        }
        // 關閉資料庫
        conn.close();
    }
}

挑出幾乎都會用到的去宣告成全域變數

基本上conn是驅動資料庫的連接，所以幾乎要做什麼事情都和他有關，我們就把它

import java.sql.*;
import java.util.ArrayList;

public class Main {
    private static Connection conn;
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        
    }
}

把裡面1.只會用到全域變數的2.只有不影響主要功能變數的功能抓出來

我們這裡功能主要有

初始化資料庫
防止駭客入侵的preparedment
主要功能 => 不去動它
關閉資料庫
這裡比較好抓出來的功能大致上就是初始化和關閉資料庫

// 初始化資料庫
public static void initializeDB() throws SQLException {
    conn = DriverManager.getConnection(
            "jdbc:mysql://localhost:3306/maxdb", "Max", "12345678");
    if(conn != null){
        System.out.println("success for connecting to the database");
    } else {
        System.out.println("Fail to connect the database");
    }
}
// 關閉資料庫
private static void closeDB() throws SQLException {
    conn.close();
}

模組化結果

import java.sql.*;
import java.util.ArrayList;

public class Main {
    private static Connection conn;
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        initializeDB()
        String sqlStatement = "select * from Person";
        
        // 防止駭客入侵我們資料庫 + 優化性能
        PreparedStatement ps = conn.prepareStatement(sqlStatement);
        System.out.println(ps);
        ResultSet rs = ps.executeQuery();
        
        // 功能測試
        ArrayList<Person> result = new ArrayList<>();
        while (rs.next()){
            Person p = new Person(Integer.parseInt(rs.getString("personId")), rs.getString("personName"), Integer.parseInt(rs.getString("age")));
            result.add(p);
        }
        for (Person p:result) {
            System.out.println(p.toString());
        }
        closeDB();
    }
    // 初始化資料庫
    public static void initializeDB() throws SQLException {
        conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/maxdb", "Max", "12345678");
        if(conn != null){
            System.out.println("success for connecting to the database");
        } else {
            System.out.println("Fail to connect the database");
        }
    }
    // 關閉資料庫
    private static void closeDB() throws SQLException {
        conn.close();
    }
}

實作查詢功能

其實沒有甚麼特別複雜的操作，就是輸入員工名字，輸出員工資訊

先寫出資料庫模板出來

import javax.swing.*;
import java.sql.*;
import java.util.ArrayList;

public class Main {

    private static Connection conn;
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        initializeDB();
        
        closeDB();
    }

    public static void initializeDB() throws SQLException {
        conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/maxdb", "Max", "12345678");
        if(conn != null){
            System.out.println("success for connecting to the database");
        } else {
            System.out.println("Fail to connect the database");
        }
    }
    private static void closeDB() throws SQLException {
        conn.close();
    }

}

sql_statement放進SQL的搜尋語法，在我們要搜尋的參數部分放上"?"，接著再用setString(參數索引值, data)去賦予"?"參數值

import javax.swing.*;
import java.sql.*;
import java.util.ArrayList;

public class Main {

    private static Connection conn;
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        initializeDB();
        String name = JOptionPane.showInputDialog("Input the employee name");
        String sql_statement = "select * from person where personName = ?;";
        PreparedStatement prepst = conn.prepareStatement(sql_statement);    // prevent SQL injection
        prepst.setString(1, name);  // 把我們輸入的name輸入到?
        ResultSet rs = prepst.executeQuery();

        closeDB();
    }

    public static void initializeDB() throws SQLException {
        conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/maxdb", "Max", "12345678");
        if(conn != null){
            System.out.println("success for connecting to the database");
        } else {
            System.out.println("Fail to connect the database");
        }
    }
    private static void closeDB() throws SQLException {
        conn.close();
    }

}

設定對應的變數去抓取rs中的對應的資料，還有設定資料顯示出來的樣式

import javax.swing.*;
import java.sql.*;
import java.util.ArrayList;

public class Main {

    private static Connection conn;
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        initializeDB();
        String name = JOptionPane.showInputDialog("Input the employee name");
        String sql_statement = "select * from person where personName = ?;";
        PreparedStatement prepst = conn.prepareStatement(sql_statement);    // prevent SQL injection
        prepst.setString(1, name);  // 把我們輸入的name輸入到?
        ResultSet rs = prepst.executeQuery();

        if (rs.next()) {
            int pId = Integer.parseInt(rs.getString("personId"));
            String pName = rs.getString("personName");
            int pAge = Integer.parseInt(rs.getString("age"));
            JOptionPane.showMessageDialog(null, new Person(pId, pName, pAge));
        }
        closeDB();
    }

    public static void initializeDB() throws SQLException {
        conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/maxdb", "Max", "12345678");
        if(conn != null){
            System.out.println("success for connecting to the database");
        } else {
            System.out.println("Fail to connect the database");
        }
    }
    private static void closeDB() throws SQLException {
        conn.close();
    }

}

因為會發現如果沒輸入資料庫中的資料就會直接結束程式，所以我們設定else來告訴我們輸入錯誤

import javax.swing.*;
import java.sql.*;
import java.util.ArrayList;

public class Main {

    private static Connection conn;
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        initializeDB();
        String name = JOptionPane.showInputDialog("Input the employee name");
        String sql_statement = "select * from person where personName = ?;";
        PreparedStatement prepst = conn.prepareStatement(sql_statement);    // prevent SQL injection
        prepst.setString(1, name);  // 把我們輸入的name輸入到?
        ResultSet rs = prepst.executeQuery();

        if (rs.next()){
            int pId = Integer.parseInt(rs.getString("personId"));
            String pName = rs.getString("personName");
            int pAge = Integer.parseInt(rs.getString("age"));
            JOptionPane.showMessageDialog(null, new Person(pId, pName, pAge));
        } else {
            JOptionPane.showMessageDialog(null, "employee not found");
        }
        closeDB();
    }

    public static void initializeDB() throws SQLException {
        conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/maxdb", "Max", "12345678");
        if(conn != null){
            System.out.println("success for connecting to the database");
        } else {
            System.out.println("Fail to connect the database");
        }
    }
    private static void closeDB() throws SQLException {
        conn.close();
    }

}