[FIXED] How to generate next value in mysql table using java?

Issue

In course table my primary key is course_code and it has a pattern like "SLIOP-P-001" (SLIOP-SUBJECT INITIAL-NUMBER)

I need to generate SLIOP-P-002 when click the generate button.

Can someone guide me a way to code this?

I have attached my project screen shot.I typed the below records.

I have tried below code. But it doesn't work.

private void generate_course_codeActionPerformed(java.awt.event.ActionEvent evt) {                                                     
   String c_code=course_catergory.getSelectedItem().toString();

   if(c_code=="Printing"){ 
       try {

    pst.executeUpdate("INSERT INTO course (course_code)VALUE(?)",Statement.RETURN_GENERATED_KEYS);

        rs = pst.getGeneratedKeys();

        if (rs.next()) {
            cour_code.setText(rs.getString(1)); 
        }

        }   


   catch(Exception e){
        //JOptionPane.showMessageDialog(null,"Error occured","Error",JOptionPane.ERROR_MESSAGE);
        JOptionPane.showMessageDialog(null,e);
    }
       }
}        

Solution

I am not aware of an automatic way of creating such a pattern. But one tedious way to generate such a key is as follow:

1. Create a table

   CREATE TABLE course (
      id int NOT NULL AUTO_INCREMENT,
      course_code CHAR(7) NOT NULL,
      PRIMARY KEY (id)
   );
   

2. Insert an entry for a course_code:

   PreparedStatement pst = connection.prepareStatement("INSERT INTO course (course_code) VALUES (?)");
   pst.setString(1, "SLIOP-P");
   pst.executeUpdate();
   // close pst
   

   This will insert the course_code and generates a new integer value for id column what you can use as a suffix (see below).

3. Execute a select query for your desired course_code:

   PreparedStatement pst2 = connection.prepareStatement("select * from course where course_code = ?");
   pst2.setString(1, "SLIOP-P");  // this is an example; pass anything
   ResultSet rs = pst2.executeQuery();
   String courseCode = null;
   if (rs.next()) {
        courseCode = rs.getString("course_code") + "-" + String.format("%03d", rs.getInt("id"));
   }
   

4. Return this course code from your method:

   return courseCode;
   

But if you ask me, the better way is to store all possible course codes in a map; unless there is no a requirement.

Answered By - ujulu
Answer Checked By - Mary Flores (JavaFixing Volunteer)