Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- package school.database;
- import school.database.Grade;
- import school.database.SchoolClass;
- import school.database.Student;
- 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 * FROM student WHERE class_id = ?";
- private static final String GET_STUDENT_GRADES_QUERY =
- "SELECT s.name, gv.values, g.grade_date, t.fullnamen" +
- "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.grade_id = gv.idn" +
- "JOIN teacher tn" +
- "ON g.teacher_id = t.idn" +
- "WHERE st.id = ?";
- private static final String PUT_CLASS_IN_QUERY = "INSERT INTO class (name) VALUES (?)";
- 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");
- String teacher = rs.getString("fullname");
- Grade grade = new Grade(subject, value, date);
- grade.setTeacher(teacher);
- result.add(grade);
- }
- } catch (SQLException e) {
- throw new RuntimeException(e);
- }
- return result;
- }
- public boolean writeClassinDB(Class clazz) {
- boolean result = false;
- try {
- PreparedStatement stmt = conn.prepareStatement(PUT_CLASS_IN_QUERY);
- stmt.setString(1, clazz.getName());
- if(stmt.executeUpdate() == 1){
- result = true;
- }
- } catch (SQLException e) {
- throw new RuntimeException(e);
- }
- return result;
- }
- }
- package school.frames;
- import school.Main;
- import school.database.SchoolClass;
- import javax.management.openmbean.KeyAlreadyExistsException;
- import javax.swing.*;
- public class CreateClassFrame extends MyFrame {
- private static Integer[] nums = {1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11};
- private static Character[] letters = {'А', 'Б', 'В', 'Г'};
- public CreateClassFrame(JFrame parent) {
- super("Создать класс", parent, 250, 150);
- }
- public void showFrame() {
- JLabel numLabel = new JLabel("Номер класса: ");
- numLabel.setBounds(10, 10, 150, 25);
- JComboBox<Integer> numsBox = new JComboBox<>(nums);
- numsBox.setBounds(150, 10, 90, 25);
- JLabel letterLabel = new JLabel("Буква класса: ");
- letterLabel.setBounds(10, 45, 150, 25);
- JComboBox<Character> lettersBox = new JComboBox<>(letters);
- lettersBox.setBounds(150, 45, 90, 25);
- JButton cancel = new JButton("Отмена");
- cancel.setBounds(10, 90, 110, 25);
- cancel.addActionListener(actionEvent -> dispose());
- JButton done = new JButton("Готово");
- done.setBounds(130, 90, 110, 25);
- done.addActionListener(actionEvent -> {
- try {
- if (numsBox.getSelectedItem() != null && lettersBox.getSelectedItem() != null) {
- Main.addClass(new SchoolClass(0, "" + (Integer) numsBox.getSelectedItem() + (Character) lettersBox.getSelectedItem()));
- dispose();
- }
- } catch (KeyAlreadyExistsException e) {
- JOptionPane.showMessageDialog(this, "Класс " + e.getMessage() + " уже существует");
- }
- });
- add(numLabel);
- add(numsBox);
- add(letterLabel);
- add(lettersBox);
- add(cancel);
- add(done);
- setVisible(true);
- }
- }
- package school.database;
- import school.Main;
- import javax.swing.*;
- import java.util.ArrayList;
- import java.util.Comparator;
- import java.util.regex.Matcher;
- import java.util.regex.Pattern;
- public class SchoolClass {
- private int id;
- private String name;
- private ArrayList<Student> students;
- public SchoolClass(int id, String name, ArrayList<Student> students) {
- this.id = id;
- this.name = name;
- this.students = students;
- }
- public SchoolClass(int id, String name) {
- this.id = id;
- this.name = name;
- students = new ArrayList<>();
- }
- public String getName() {
- return name;
- }
- public ArrayList<Student> getStudents() {
- return students;
- }
- public Student[] getStudentsAsArray() {
- students = Main.sqlTest.readStudentsInClassFromDB(id);
- return this.students.toArray(new Student[0]);
- }
- public void addStudent(Student student) {
- students.add(student);
- students.sort(Comparator.comparing(Student::toString));
- }
- public void deleteStudent(Student student) {
- students.remove(student);
- }
- @Override
- public String toString() {
- return getName();
- }
- }
- done.addActionListener(actionEvent -> {
- try {
- if (numsBox.getSelectedItem() != null && lettersBox.getSelectedItem() != null) {
- databaseDAO.createSchoolClass(new SchoolClass(0, "" + (Integer) numsBox.getSelectedItem() + (Character) lettersBox.getSelectedItem()));
- dispose();
- }
- } catch (KeyAlreadyExistsException e) {
- JOptionPane.showMessageDialog(this, "Класс " + e.getMessage() + " уже существует");
- }
- });
Add Comment
Please, Sign In to add comment