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

Question

Java application to insert student scores and type of test taken

MySql tables tests table and scores tables

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

Please note that your post includes credentials. You might want to change them because the current ones are public. — mm759, Aug 22 '16 at 12:04

score 0 · Accepted Answer · edited May 23 '17 at 12:00

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.

Thank you this helped so much – Edunge Aug 21 '16 at 14:39 — Edunge, Aug 21 '16 at 14:39

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

1 Answers1