接著我們就來進行JDBC的測試~

首先回到我們JDBC project裡面的Main.java

1. 因為我們知道一定會出現SQLException, ClassNotFoundException這兩個Exception，所以我們就在pvsm 後面加上throws SQLException, ClassNotFoundException

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

public class Main {

    public static void main(String[] args) throws SQLException, ClassNotFoundException {
    }
}

2. 我們再加上資料庫狀態的變數sqlStatement, 還有連接到資料庫的Connection物件，使用getConnection()方法，裡面放進參數(1)資料庫url(2)使用者帳號(3)密碼
   資料庫url的位置
   
   

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

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");
        
    }
}

3. 確認資料庫連接成功，用if-else設定conn

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

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");
        }
    }
}

4. 建立Preparedment提升性能，我們用conn.preparedment(sqlStatement)，在使用ResultSet回傳查詢結果

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);
        ResultSet rs = ps.executeQuery();
    }
}

5. 接著我們建立Person.java並放入建構子(table的各欄項目)，因為要建立物件

// Person.java
public class Person {
    private int personId;
    private String personName;
    private int age;

    public Person(int personId, String personName, int age){
        this.personId = personId;
        this.personName = personName;
        this.age = age;
    }

    @Override
    public String toString(){
        return "Id:" + personId + ", name:" + personName + ", age:" + age;
    }
}

接著就是用ArrayList去new出我們的result，再去建立出我們的Person物件，依序放入各欄的值

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();
    }
}

最後再加上close去關閉以免資源被占走

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();
    }
}