Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2494
  • Last Modified:

JDBC: mySQL: How do I autoincrement the primary key?

JDBC: mySQL: How do I autoincrement the primary key?

Here is my table:

CREATE TABLE rtpm_user (
  rtpm_user_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  firstname VARCHAR(20) NULL,
  surname VARCHAR(20) NULL,
  username VARCHAR(20) NULL,
  password1 VARCHAR(15) NULL,
  password2 VARCHAR(15) NULL,
  email VARCHAR(15) NULL,
  sdp_code VARCHAR(5) NULL,
  last_access DATE NULL,
  PRIMARY KEY(rtpm_user_id)
);

Here is my database handler (it does not contain any statement for autoincrementing) as most of what I've tried has failed.

public class DBHandler extends HttpServlet {

    public void doPost (HttpServletRequest request,
      HttpServletResponse response) {
      try { // OBTAIN A DATABASE CONNECTION
         
          // Step 1: Load the JDBC driver          
          Class.forName("com.mysql.jdbc.Driver");
             
          // Step 2: Establish a connection to the database          
       
          Connection conn = DriverManager.getConnection(
          "jdbc:mysql://localhost/rtpmKenyaDB","rrugemalira", "Pacifier7");
 
          String sql = "INSERT INTO rtpm_user(firstname, surname, " +
          "username, password1, password2, email, " +
          "sdp_code) VALUES (?, ?, ?, ?, ?, ?, ?)";
          PreparedStatement ps = conn.prepareStatement(sql);
         
          // Retreave the bean properties to be inserted into table rtpm_user
          FormBean f = (FormBean) request.getAttribute("formHandler");
         
          // Set the parameters in the prepared statement
          ps.setString(1, f.getFirstName());
          ps.setString(2, f.getLastName());
          ps.setString(3, f.getUserName());
          ps.setString(4, f.getPassword1());
          ps.setString(5, f.getPassword2());
          ps.setString(6, f.getEmail());
          ps.setString(7, f.getSdp());
         
          // Insert the row in the table
          ps.executeUpdate();


          // Close the connection
          ps.close();
          conn.close();    
      }
      catch (SQLException e) {
          // handle any errors
          System.out.println("SQLException: " + e.getMessage());
          System.out.println("SQLState: " + e.getSQLState());
          System.out.println("VendorError: " + e.getErrorCode());
          }
      catch (Exception e) {
      e.printStackTrace();
      }
     
      // Send the request back to success.jsp  
      try {
          getServletConfig().getServletContext().
            getRequestDispatcher("/success.jsp").
             forward(request, response);
      }
      catch (Exception e){}
     
   }
}

Handler works fine for the first data entry into the database, where rtpm_user_id is automatically given the value 0.

A second data entry fails because rtpm_user_id is assigned the same value of 0. How do I autoincrement? Kindly instruct.
0
Rrugemalira
Asked:
Rrugemalira
1 Solution
 
ldbkuttyCommented:
autoincrement should increment automatically... ;)
what is the exact error you got ? what DB do you use ?
0
 
ldbkuttyCommented:
>> Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/rtpmKenyaDB","rrugemalira", "Pacifier7");
oops... Ok..MySQL... :-D
0
 
ronan_40060Commented:
hello Rrugemalira
and yes ldbkutty nice to hear from :) and good to have u here in JSP :)
well yes in ur SQL create table
>>>> rtpm_user_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
should increment
Like ldbkutty said what kinda error messages you are getting , pls post
waiting for ur reply
regards
ronan
 
0
 
RrugemaliraAuthor Commented:
Here's the error message:

SQLException: Duplicate key or integrity constraint violation,  message from server: "Duplicate entry '0' for key 1"
SQLState: 23000
VendorError: 1062
0
 
sompol_kiatkamolchaiCommented:
Try to change INTEGER datatype to bigint or mediumint
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now