【Java入門】データベースの情報を取得する

今回は、この生徒一覧表テーブルから、情報を取得します。

SQL文にすると

CREATE TABLE `students` (
    `id`    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    `name`  TEXT NOT NULL,
    `grade` INTEGER NOT NULL
);

Studentクラス

public class Student {

    private int id;
    private String name;
    private int grade;

//コンストラクタ
    public Student(int id, String name, int grade) {
        this.id = id;
        this.name = name;
        this.grade = grade;
    }

    public int getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public int getGrade() {
        return grade;
    }

//データベースに接続するメソッド
    private static String getSqlite3Path(){
        return System.getProperty("user.dir") + File.separator + "sql" + File.separator + "juku.sqlite3";
    }

    
//テーブル全体を取得するメソッド
    public static List<Student> fetchStudents() {

        List<Student> rows = new ArrayList<Student>();

        try {
            Connection connection = DriverManager.getConnection("jdbc:sqlite:" + getSqlite3Path());
            Statement createStatement = connection.createStatement();
            ResultSet resultSet = createStatement.executeQuery("SELECT * FROM students;");

            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                int grade = resultSet.getInt("grade");
                Student row = new Student(id, name, grade);
                rows.add(row);

            }

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

        return rows;
    }

//生徒IDを指定して、該当する生徒IDのレコードのみ取得するメソッド
    public static Student getById(int studentId) {

        try {
            Connection connection = DriverManager.getConnection("jdbc:sqlite:" + getSqlite3Path());
            PreparedStatement prepareStatement = connection.prepareStatement("SELECT * FROM students WHERE id = ?;");
            prepareStatement.setInt(1, studentId);
            ResultSet executeQuery = prepareStatement.executeQuery();

            if (executeQuery.next()) {
                int id = executeQuery.getInt("id");
                String name = executeQuery.getString("name");
                int grade = executeQuery.getInt("grade");
                Student row = new Student(id, name, schoolId, grade);
                return row;
            } else {
                return null;
            }

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

        return null;
    }

}