JDBC Basic
➤ Intro to JDBC
➤ JDBC vs ODBC
➤ JDBC API
➤ JDBC Driver
Connect to Database
➤ Oracle Connection
➤ MySQL Connection
➤ Using Eclipse IDE
➤ Coding Standards
➤ Close JDBC Object
➤ Java.sql Package
➤ DriverManager Class
➤ Get Connection
JDBC Statement
➤ Statement Interface
➤ Statement Example
➤ Create a database
PreparedStatement
➤ Types of statements
➤ PreparedStatement
➤ Statement vs PreParedStatement
PreparedStatement Example
➤ Insert record
➤ Update record
➤ Select record
➤ Create a table
➤ More Examples
➤ Surrogate Key
Others
➤ DatabaseMetaData
➤ ResultSetMetaData & ParameterMetaData
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:-
- Insert record into a table using PreparedStatement
- Update record using PreparedStatement
- JDBC PreparedStatement example to select and display records.
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 do you find anything incorrect? Let us know in the comments. Thank you!