Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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;
- 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();
- }
- }
Add Comment
Please, Sign In to add comment