JDBC Auto Increment Primary Key

In this JDBC tutorial, we will discuss how to use auto-increment value as the primary key or how to generate primary key values dynamically using surrogate key columns.

In these programs, we will use the PreparedStatement object. But before developing a JDBC program let us discuss some points on the Candidate key column.

Candidate key column:- The column in the database table whose value can be used to identify the record and to access them is called a candidate key column. It holds unique values.

Example:- We have a table “Personal_info” and it have following columns.

Personal_info
identityCardNo
voterId
passportNo
drivingLCNo
name
addrs
age
qualification

In this table, the first 4 rows (identityCardNo, voterId, passportNo, and drivingLCNo) are unique so they are the Candidate key column.

The Candidate key can be categorized into two parts.
1) Natural key column
2) Surrogate key columns

Natural key column:- The candidate key column which is having business meaning and changes its value because of outside world business changes is called a natural key column.

These column values not only useful to identify the records within the database table but also useful to identify things/people outside the database software they naturally maintain identity values. Example:- identityCardNo, panCardNo, licenseId, voterId, drivingLCNo and etc. These values are given by the government.

Surrogate key column:- The candidate key column whose values are not expected from end-user but generated by underlying database software or application without having business meaning and can be used only to identify records within database table but not outside is called Surrogate key columns.

Example of a surrogate key:- sequence generated values in Oracle, identity columns in MySQL (auto-increment), serial numbers/random numbers generated by application as ids, generators in hibernate. These values are autogenerated by database software and also used only inside the database software. It is no use of these columns outside of the database.

Limitations of natural key and advantages of surrogate key

Limitations of taking natural key columns as primary key column,

a) These values are very lengthy, so more memory is required
b) It is expected from the end-user and if end-user fails to given the registrations may fail.
c) These column values will change because of business/policies change happens in the outside worlds and these changes reflect other database tables and classes of the projects which costly affair.

Note:- It is always recommended to don’t take natural key columns as a primary key column. Prefer taking surrogate key columns as primary key columns.

Advantages of taking surrogate key cols as primary key columns,

a) The surrogate key values are small values having less length, so less memory is required.
b) Values are not expected from end-users, it is generated by application or database software dynamically, so registrations never fail.
c) No business meaning for these values, so it will not change for outside world business/policies change. Hence the code maintenance becomes easier.

How to use auto increment as primary key

In Oracle Database,

Step1) create sequence in oracle database.

SQL> CREATE SEQUENCE <sequence_name>
     START WITH 1 INCREMENT BY 1;

Step2) link sequence to query

SQL> INSERT INTO TABLE VALUES 
    (ARTISTID_SEQ.NEXTVAL, ?, ? );

Note that the first column of the table must be primary key.

In MySQL database,

If we add AUTO_INCREMENT constraint on the primary key column then it becomes a surrogate key column.

Also see:-

JDBC Program using Surrogate key in Oracle database

First we will develop JDBC program for oracle database, and later for SQL database.

Create a table.

SQL> create table product
     ( pid number(10) primary key,
       pname varchar2(15), 
       price float
     );

Create a sequence.

SQL> create sequence pid_seq
     start with 100 increment by 1;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;

public class SelectTest {

   // SQL query
   private static final String INSERT_PRODUCT_QUERY = 
     " INSERT INTO PRODUCT VALUES(PID_SEQ.NEXTVAL,?,?)";

   public static void main(String[] args ) {

      // declare variables
      Scanner scan = null;
      int item = 0;
      String pname = null;
      float price = 0.0f;
      Connection con = null;
      PreparedStatement ps = null;
      int result = 0;

      try {
         // read input
         scan = new Scanner(System.in);
         if(scan != null) {
             System.out.print("Enter the number of items: ");
             item = scan.nextInt();
         }

         // establish the connection
         con = DriverManager.getConnection(
            "jdbc:oracle:thin:@localhost:1521:knowprogram",
            "scott", "tiger");

         // compile SQL query and 
         // store it in PreparedStatemet object
         if(con != null)
         ps = con.prepareStatement(INSERT_PRODUCT_QUERY);

         // set query parameter and executed query
         if(ps != null) {
            for(int i=0; i < item; i++ ) {

               // read inputs
               System.out.println("\nEnter product-"
                                +(i+1)+" details,");
               System.out.print("Enter name: ");
               pname = scan.next();
               System.out.print("Enter price: ");
               price = scan.nextFloat();

               // set input value to query parameter
               ps.setString(1, pname);
               ps.setFloat(2, price);

               // execute the query
               result += ps.executeUpdate();
           }
         }

         // result
         if(result != 0)
         System.out.println(result + " Records inserted");
         else
         System.out.println("Records not inserted");

      } catch(SQLException se) {
         se.printStackTrace();
      } catch(Exception e) {
         e.printStackTrace();
      } // end of try-catch block 

      finally {
         // close JDBC objects
         try {
            if(ps != null) ps.close();
         } catch(SQLException se) {
            se.printStackTrace();
         }
         try {
            if(con != null) con.close();
         } catch(SQLException se) {
            se.printStackTrace();
         }
         try {
            if(scan != null) scan.close();
         } catch(Exception e) {
            e.printStackTrace();
         }
     }

   } //end of main
} //end of class

Output:-

Enter the number of items: 4

Enter product-1 details,
Enter name: Table
Enter price: 500

Enter product-2 details,
Enter name: Chair
Enter price: 200

Enter product-3 details,
Enter name: TableLamp
Enter price: 300

Enter product-4 details,
Enter name: Glass
Enter price: 250
4 Records inserted

Display table to verify it.

SQL> select * from product;

PID   PNAME     PRICE
----- --------  ----------
101   Table      500
102   Chair      200
103   TableLamp  300
104   Glass      250

JDBC program for auto increment as primary key in MySQL database

Create table in MySQL database.

CREATE TABLE knowprogram.person (
    personid int NOT NULL AUTO_INCREMENT,
    firstName varchar(15) NOT NULL,
    lastName varchar(15),
    age int,
    PRIMARY KEY (personid)
);

Note:- Table has 4 columns but we will insert only three column values so we need below syntax to insert values:-

INSERT INTO person 
(firstName, lastName, age)
VALUES 
('Olivia', 'Brown', 25);
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;

public class SelectTest {

   // SQL query
   private static final String INSERT_PERSON_QUERY = 
      " INSERT INTO PERSON " +
      "(FIRSTNAME, LASTNAME, AGE)" +
      " VALUES (?,?,?) ";

   public static void main(String[] args ) {
      // declare variables
      Scanner scan = null;
      int n = 0;
      String firstName = null, lastName = null;
      int age = 0;
      Connection con = null;
      PreparedStatement ps = null;
      int result = 0;

      try {
         // read input
         scan = new Scanner(System.in);
         if(scan != null) {
             System.out.print("Enter the number of persons:");
             n = scan.nextInt();
         }

         // establish the connection
         con = DriverManager.getConnection(
           "jdbc:mysql:///knowprogram","user","pwd");

         // compile SQL query and 
         // store it in PreparedStatemet object
         if(con != null)
         ps = con.prepareStatement(INSERT_PERSON_QUERY);

         // set query parameter and executed query
         if(ps != null) {
            for(int i=0; i < n; i++ ) {

               // read inputs
               System.out.println("\nEnter person-"
                                +(i+1)+" details,");
               System.out.print("Enter first name: ");
               firstName = scan.next();
               System.out.print("Enter last name: ");
               lastName = scan.next();
               System.out.print("Enter age: ");
               age = scan.nextInt();

               // set input value to query parameter
               ps.setString(1, firstName);
               ps.setString(2, lastName);
               ps.setInt(3, age);

               // execute the query
               result += ps.executeUpdate();
           }
         }

         // result
         if(result != 0)
         System.out.println(result + " records inserted");
         else
         System.out.println("Records not inserted");

      } catch(SQLException se) {
         se.printStackTrace();
      } catch(Exception e) {
         e.printStackTrace();
      } // end of try-catch block 

      finally {
         // close JDBC objects
         try {
            if(ps != null) ps.close();
         } catch(SQLException se) {
            se.printStackTrace();
         }
         try {
            if(con != null) con.close();
         } catch(SQLException se) {
            se.printStackTrace();
         }
         try {
            if(scan != null) scan.close();
         } catch(Exception e) {
            e.printStackTrace();
         }
      }

   } //end of main
} //end of class

Output:-

Enter the number of persons: 3

Enter person-1 details,
Enter first name: Oliver
Enter last name: Smith
Enter age: 25

Enter person-2 details,
Enter first name: Harry
Enter last name: Brown
Enter age: 37

Enter person-3 details,
Enter first name: Jacob
Enter last name: Wright
Enter age: 19
3 records inserted

Now, display the table to verify that the records are correctly inserted or not.

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or you find anything incorrect? Let us know in the comments. Thank you!

Leave a Reply