package school;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.swing.JTable;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class SQLtest {
    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_STUDENTS_QUERY = "SELECT pupil.id, pupil.name, pupil.surname from pupil";
    private static final String GET_ALL_MARKS_QUERY = "SELECT pupil.name, pupil.surname, mark_value.valuesn"
            + "    FROM pupil, mark_value, markn"
            + "    WHERE pupil.id = mark.pupil_id AND mark.mark_id = mark_value.id ";

    public static void main(String args[]) {
        SQLtest sqltest = new SQLtest();
        sqltest.printAllStudents();
        sqltest.printAllMarks();
    }

    private void printAllStudents() {
        try (Connection con = DriverManager.getConnection(URL, USER, PASSWORD)) {
            PreparedStatement stmtGetStudents = con.prepareStatement(GET_ALL_STUDENTS_QUERY);
            ResultSet rs = stmtGetStudents.executeQuery();
            while (rs.next()) {
                int id = rs.getInt(1);
                String firstName = rs.getString(2);
                String lastName = rs.getString(3);
                System.out.println("Students :  " + firstName + " " + lastName);
            }
        } catch (Exception e) {
            System.out.println("Ошибка при выборке студентов. " + e.getMessage());
        }
    }

    private void printAllMarks() {
        try (Connection con = DriverManager.getConnection(URL, USER, PASSWORD)) {
            PreparedStatement stmtGetMarks = con.prepareStatement(GET_ALL_MARKS_QUERY);
            ResultSet rs = stmtGetMarks.executeQuery();
            while (rs.next()) {
                String name = rs.getString(1);
                String surName = rs.getString(2);
                String value = rs.getString(3);
                System.out.println(name + "  " + surName + " " + value);
            }
        } catch (Exception e) {
            System.out.println("Ошибка при выборке оценок. " + e.getMessage());
        }
    }
}

SELECT pupil.name ,pupil.surname,mark_value.values FROM pupil ,mark_value ,mark  WHERE pupil.id=mark.pupil_id AND mark.mark_id=mark_value.id;

package school.frames;


import javax.swing.JFrame;
import javax.swing.JList;
import javax.swing.JScrollPane;
import javax.swing.ScrollPaneConstants;
import school.Main;

public class AllStudentFrame extends MyFrame {

  public AllStudentFrame(JFrame parent){
    super("Все студенты", parent, 250, 200);

  }
  @Override
        public void showFrame() {
        JList<Object> list = new JList<>(Main.getClassesAsArray());
        JScrollPane listScroller = new JScrollPane(list);
        listScroller.setVerticalScrollBarPolicy(ScrollPaneConstants.VERTICAL_SCROLLBAR_ALWAYS);
        listScroller.setBounds(0, 0, 250, 300);
        add(listScroller);



        setVisible(true);


  }

  }

package school;

import school.frames.DeleteStudentFrame;
import school.frames.DeleteClassFrame;
import school.frames.CreateClassFrame;
import school.frames.AddGradeFrame;
import school.frames.AddStudentFrame;
import school.frames.FindStudentFrame;
import school.database.SchoolClass;
import school.database.Student;

import javax.management.openmbean.KeyAlreadyExistsException;
import javax.swing.*;
import javax.swing.border.TitledBorder;
import java.awt.*;
import java.awt.event.ComponentAdapter;
import java.awt.event.ComponentEvent;
import java.util.ArrayList;
import java.util.Comparator;
import school.frames.AllStudentFrame;

public class Main {

    private static ArrayList<SchoolClass> classes = new ArrayList<>();

    private static JFrame mainFrame;
    private static JList<SchoolClass> classList;
    private static JList<Student> studentList;
    private static JTable table;
    private static JScrollPane classListPane;
    private static JScrollPane studentListPane;
    private static JScrollPane tablePane;

    public static ArrayList<SchoolClass> getClasses() {
        return classes;
    }

    public static SchoolClass[] getClassesAsArray() {
        return classes.toArray(new SchoolClass[0]);
    }


    public static void addClass(SchoolClass schoolClass) throws KeyAlreadyExistsException {
        for (SchoolClass c : classes) {
            if (c.getName().equals(schoolClass.getName()))
                throw new KeyAlreadyExistsException(schoolClass.getName());
        }
        classes.add(schoolClass);
        classes.sort(Comparator.comparing(SchoolClass::toString));
        classList.setListData(getClassesAsArray());
    }


    public static void deleteClass(SchoolClass schoolClass) {
        classes.remove(schoolClass);
        classList.setListData(getClassesAsArray());
        updateStudentsList();
        updateTable();
    }


    public static void selectStudent(SchoolClass schoolClass, Student student) {
        try {
            classList.setSelectedIndex(classes.indexOf(schoolClass));
            updateStudentsList();
            studentList.setSelectedIndex(schoolClass.getStudents().indexOf(student));
            updateTable();
        } catch (Exception ignored) {


        }
    }

    public static void updateStudentsList() {
        if (classList.getSelectedValue() != null) {
            studentList.setListData(classList.getSelectedValue().getStudentsAsArray());
            updateTable();
        }
    }


    public static void updateTable() {
        if (tablePane != null) mainFrame.remove(tablePane);
        if (studentList.getSelectedValue() != null) {
            Student select = studentList.getSelectedValue();
            table = select.createTable();
            tablePane = new JScrollPane(table);
            tablePane.setBounds(300, 0, mainFrame.getWidth() - 300, mainFrame.getHeight());
            mainFrame.add(tablePane);
        } else {
            tablePane = null;
        }
        mainFrame.validate();
        mainFrame.repaint();
    }

    private static JMenuBar createMenuBar() {
        JMenuBar menuBar = new JMenuBar();

        JMenu classMenu = new JMenu("Классы");
        JMenuItem createClassItem = new JMenuItem("Создать");
        createClassItem.addActionListener(actionEvent -> new CreateClassFrame(mainFrame));
        JMenuItem deleteClassItem = new JMenuItem("Удалить");
        deleteClassItem.addActionListener(actionEvent -> new DeleteClassFrame(mainFrame));
        classMenu.add(createClassItem);
        classMenu.add(deleteClassItem);

        JMenu studentMenu = new JMenu("Ученики");
        JMenuItem findStudentItem = new JMenuItem("Найти");
        findStudentItem.addActionListener(actionEvent -> new FindStudentFrame(mainFrame));
        JMenuItem addStudentItem = new JMenuItem("Добавить");
        addStudentItem.addActionListener(actionEvent -> new AddStudentFrame(mainFrame));
        JMenuItem showAllStudentItem = new JMenuItem("Вывести всех учеников");
        showAllStudentItem.addActionListener(actionEvent -> new AllStudentFrame(mainFrame));
        JMenuItem deleteStudentItem = new JMenuItem("Удалить");
        deleteStudentItem.addActionListener(actionEvent -> new DeleteStudentFrame(mainFrame));

        studentMenu.add(findStudentItem);
        studentMenu.add(addStudentItem);
        studentMenu.add(showAllStudentItem);
        studentMenu.add(deleteStudentItem);

        JMenu gradeMenu = new JMenu("Оценки");
        JMenuItem addGradeItem = new JMenuItem("Добавить");
        addGradeItem.addActionListener(actionEvent -> new AddGradeFrame(mainFrame));
        gradeMenu.add(addGradeItem);

        menuBar.add(classMenu);
        menuBar.add(studentMenu);
        menuBar.add(gradeMenu);

        return menuBar;
    }

    public static void main(String[] args) {
        mainFrame = new JFrame("Журнал");
        mainFrame.setMinimumSize(new Dimension(960, 720));
        mainFrame.setSize(mainFrame.getMinimumSize());

        mainFrame.setLocationRelativeTo(null);
        mainFrame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        mainFrame.setLayout(null);

        classList = new JList<>(getClassesAsArray());
        classList.setBorder(new TitledBorder("Классы"));
        classList.addListSelectionListener(e -> updateStudentsList());
        classListPane = new JScrollPane(classList);
        classListPane.setBounds(0, 0, 100, mainFrame.getHeight());

        studentList = new JList<>();
        studentList.setBorder(new TitledBorder("Ученики"));
        studentList.addListSelectionListener(e -> updateTable());
        studentListPane = new JScrollPane(studentList);
        studentListPane.setBounds(100, 0, 200, mainFrame.getHeight());

        mainFrame.add(classListPane);
        mainFrame.add(studentListPane);
        mainFrame.setJMenuBar(createMenuBar());


        mainFrame.addComponentListener(new ComponentAdapter() {
            @Override
            public void componentResized(ComponentEvent e) {
                super.componentResized(e);
                classListPane.setBounds(0, 0, 100, mainFrame.getHeight());
                studentListPane.setBounds(100, 0, 200, mainFrame.getHeight());
                if (tablePane != null) tablePane.setBounds(300, 0, mainFrame.getWidth() - 300, mainFrame.getHeight());
            }
        });

        mainFrame.setVisible(true);
    }
}

package school.frames;

import school.Main;
import school.database.SchoolClass;
import school.database.Student;

import javax.swing.*;
import java.awt.*;
import java.awt.event.WindowAdapter;
import java.awt.event.WindowEvent;

abstract class MyFrame extends JFrame {

    private JFrame parent;

    protected JComboBox<Student> studentBox;

    public abstract void showFrame();

    MyFrame(String title, JFrame parent, int width, int height) {
        super(title);
        this.parent = parent;

        setSize(width, height);
        setResizable(false);
        setLocationRelativeTo(null);
        setLayout(null);
        setAlwaysOnTop(true);
        getParent().setEnabled(false);

        addWindowListener(new WindowAdapter() {
            @Override
            public void windowClosing(WindowEvent e) {
                dispose();
            }
        });

        showFrame();
        repaint();
    }

    protected JComboBox<SchoolClass> createClassBox() {
        JComboBox<SchoolClass> classBox = new JComboBox<>(Main.getClassesAsArray());
        classBox.setBounds(150, 10, 90, 25);
        classBox.addItemListener(e -> {
            SchoolClass selectedClass = (SchoolClass) classBox.getSelectedItem();
            remove(studentBox);
            studentBox = new JComboBox<>(selectedClass.getStudentsAsArray());
            studentBox.setBounds(90, 45, 150, 25);
            add(studentBox);
            validate();
            repaint();
        });
        return classBox;
    }

    @Override
    public Container getParent() {
        return parent;
    }

    @Override
    public void dispose() {
        getParent().setEnabled(true);
        super.dispose();
    }

}