CODEWITHSUNDEEP
javamysqljdbcnetbeans
lakshi
lakshi

Reputation: 47

How to generate next value in mysql table using java?

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.enter image description here

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);
    }
       }
}

Upvotes: 1

Views: 97

Answers (1)

ujulu
ujulu

Reputation: 3309

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.

Upvotes: 1

Related Questions