Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- package school.database;
- import java.sql.*;
- import java.util.ArrayList;
- public class DatabaseDAO {
- private static final String URL = "jdbc:mysql://localhost:3306/school_db?useTimezone=true&serverTimezone=GMT";
- private static final String USER = "root";
- private static final String PASSWORD = "123456";
- private static final String GET_ALL_CLASSES_QUERY = "SELECT id, name FROM class ORDER BY name";
- private static final String GET_STUDENTS_IN_CLASS_QUERY =
- "SELECT s.id, s.name, s.surname, s.birthday, s.phonen" +
- "FROM student s JOIN class_student_connection cscn" +
- "ON csc.student_id = s.idn" +
- "JOIN class cn" +
- "ON csc.class_id = c.idn" +
- "WHERE c.id = ?";
- private static final String GET_STUDENT_GRADES_QUERY =
- "SELECT s.name, gv.values, g.grade_daten" +
- "FROM grade g JOIN subject sn" +
- "ON g.subject_id = s.idn" +
- "JOIN student stn" +
- "ON g.student_id = st.idn" +
- "JOIN grade_value gvn" +
- "ON g.mark_id = gv.idn" +
- "WHERE st.id = ?";
- private Connection conn;
- public DatabaseDAO() {
- try {
- conn = DriverManager.getConnection(URL, USER, PASSWORD);
- } catch (SQLException e) {
- throw new RuntimeException(e);
- }
- }
- public ArrayList<SchoolClass> readAllClassesFromDB() {
- ArrayList<SchoolClass> result = new ArrayList<>();
- try (PreparedStatement stmt = conn.prepareStatement(GET_ALL_CLASSES_QUERY)) {
- ResultSet rs = stmt.executeQuery();
- while (rs.next()) {
- int id = rs.getInt("id");
- String name = rs.getString("name");
- SchoolClass schoolClass = new SchoolClass(id, name);
- result.add(schoolClass);
- }
- } catch (SQLException e) {
- throw new RuntimeException(e);
- }
- return result;
- }
- public ArrayList<Student> readStudentsInClassFromDB(int classID) {
- ArrayList<Student> result = new ArrayList<>();
- try (PreparedStatement stmt = conn.prepareStatement(GET_STUDENTS_IN_CLASS_QUERY)) {
- stmt.setInt(1, classID);
- ResultSet rs = stmt.executeQuery();
- while (rs.next()) {
- int studentID = rs.getInt("id");
- String firstName = rs.getString("name");
- String lastName = rs.getString("surname");
- Date birthday = rs.getDate("birthday");
- Student student = new Student(studentID, firstName, lastName, "PATRON", birthday);
- result.add(student);
- }
- } catch (SQLException e) {
- throw new RuntimeException(e);
- }
- return result;
- }
- public ArrayList<Grade> readGradesFromDB(int studentID) {
- ArrayList<Grade> result = new ArrayList<>();
- try (PreparedStatement stmt = conn.prepareStatement(GET_STUDENT_GRADES_QUERY)) {
- stmt.setInt(1, studentID);
- ResultSet rs = stmt.executeQuery();
- while (rs.next()) {
- String subject = rs.getString("name");
- int value = rs.getInt("values");
- java.util.Date date = rs.getDate("grade_date");
- Grade grade = new Grade(subject, value, date.toString());
- result.add(grade);
- }
- } catch (SQLException e) {
- throw new RuntimeException(e);
- }
- return result;
- }
- }
Add Comment
Please, Sign In to add comment