У меня есть программа на JAVA школьный журнал. Там есть студенты, их классы, оценки и тд взятые из БД SQL. Теперь же надо через INSERT добавить через интерфейс новый класс (допустим "2Б").
Вот мой интерфейс через него нужно добавить новый класс в БД
Вот интерфейс мое проги
Безымянный.png
Вот классы в моем БД
class.png
а вот код связаны с БД
Код:
/*
* To change this license header, choose License Headers in Project Properties.
* To change this template file, choose Tools | Templates
* and open the template in the editor.
*/
package school.database;
import school.database.Grade;
import school.database.SchoolClass;
import school.database.Student;
import java.sql.*;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
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.fullname\n" +
"FROM grade g JOIN subject s\n" +
"ON g.subject_id = s.id\n" +
"JOIN student st\n" +
"ON g.student_id = st.id\n" +
"JOIN grade_value gv\n" +
"ON g.grade_id = gv.id\n" +
"JOIN teacher t\n" +
"ON g.teacher_id = t.id\n" +
"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 class) {
boolean result = false;
PreparedStatement stmt;
try {
stmt = conn.prepareStatement(PUT_CLASS_IN_QUERY);
} catch (SQLException ex) {
Logger.getLogger(DatabaseDAO.class.getName()).log(Level.SEVERE, null, ex);
}
try {
stmt.setString(1, class.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);
}
}