Edunge Edunge - 3 months ago 18
MySQL Question

Insert auto_increment value of the test table to the score table table using a single "update" JButton

Java application to insert student scores and type of test taken

enter image description here

MySql tables tests table and scores tables

enter image description here

I need to update both tables with a single click of the "Update" button. How do I get the tests.test_id value to to inserted on the scores.test_id.

Here is what I have tried so far, however only the test table gets updated.

String subjectCode = SubjectCombo.getSelectedItem().toString(); //gets value selected from subject code JCombobox
String testType = AssesmentCombo.getSelectedItem().toString();//gets value selected from assesment code JCombobox
ResultSet rst = null;
try {
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/resultchecker_db","edunge","Ihu18om@031988");
st = con.createStatement();
String query4 = "INSERT INTO tests (subject_id, type, test_id) VALUES (?,?,NULL)"; //query to update tests table
ps = con.prepareStatement(query4);
ps.setString(1, subjectCode);
ps.setString(2, testType);
ps.execute();
} catch (SQLException e1) {
JOptionPane.showMessageDialog(null, e1);
}
try {
if (rst.next()){
JOptionPane.showMessageDialog(null, "Student record updated");
}
} catch (HeadlessException e1) {
JOptionPane.showMessageDialog(null, e1);
} catch (SQLException e1) {
JOptionPane.showMessageDialog(null, e1);
}
try {
con.close();
st.close();
rst.close();
} catch (SQLException e1) {
JOptionPane.showMessageDialog(null, e1);
}


//This successfully updates the test table

I also tried to create another mysql connection on the actionlistener that will take the value of test.test_id and insert it to scores table, with below code.

try {
Connection con2 = DriverManager.getConnection("jdbc:mysql://localhost:3306/resultchecker_db","edunge","Ihu18om@031988");
Statement st2 = con2.createStatement();
String query5 = "SELECT test_id FROM tests ORDER BY test_id DESC LIMIT 1;";
rst2 = st2.executeQuery(query5);
} catch (SQLException e1) {
JOptionPane.showMessageDialog(null, e1);
}
try {
while(rst2.next()){
label.setText(rst2.getString(1)); //used a label to see if the auto_increment values is received.
}
} catch (SQLException e1) {
JOptionPane.showMessageDialog(null, e1);
}


Both connection codes to MySQL DB are all in the
"update" actionlistener.

The aim of this is to build a simple student result checker application, for different subjects (with continuous Assessments and Exam) and scores. I would also welcome any advice on building a better MySQL database

Answer

The approach to query the generated ID might not always work in case of concurrency, e.g. multiple users using the application at the same time. It depends on the configured concurrency isolation level. If two transactions first both insert the test and then both query the ID one transaction will get the ID of the test that the other transaction inserted.

How to get a generated ID is answered in this post. These are the main things to do according to the post:

PreparedStatement statement = connection.prepareStatement(SQL_INSERT,  Statement.RETURN_GENERATED_KEYS);
... 
ResultSet generatedKeys = statement.getGeneratedKeys()

I don't recognize any problems concerning your database schema, but you wouldn't have to think about how to get the ID if you used an ORM-framework like Hibernate.