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 the previous post, we had developed a JDBC program to select records from a table using Statement Interface. Now in this post, we will develop the JDBC Statement example program for insert, update, and delete queries. Here we will use Oracle database software, but you can work with any database with a suitable JDBC driver, URL, username, and password. For more see:- How to establish connection with any database in JDBC using Eclipse IDE
Pre-requisite:- JDBC Statement Interface
The executeQuery(-)
on Statement object, is used to send and execute the select query in database software from Java application. The return type of executeQuery(-)
method is ResultSet. Similarly, the executeUpdate(-)
method on Statement object is used to send and execute the non-select query in the database from Java application. The return type of executeUpdate(-) method is int.
While executing insert, create table, drop table queries we will get exception if the query execution is failed. But while dealing with delete, update queries we will get zero if the query execution is failed and no records are effected.
Basic details of Oracle JDBC driver to develop the program.
JDBC Driver Class Name:: oracle.jdbc.driver.OracleDriver
URL:: jdbc:oracle:thin:@<ip-address/host-name>:<port-no-of-oracle-db>:<sid>
Username:: <username_of_database>
Password:: <password_of_database>
- On a local machine generally, Oracle database is located at the port no => 1521
- If you are developing JDBC application on a local computer then use, host-name => localhost
Now let us begin with the insert query, but before developing any JDBC program first create a table in the database which will be used in all application given in this post.
Create a table in the Oracle database.
SQL>
create table student
(
sno number(5) primary key,
sname varchar2(15),
sadd varchar2(15),
avg float
);
JDBC Statement Example to Insert Record
Develop a JDBC program to insert record into the table using Statement object. Previously, we had created a table “student”. We will develop a JDBC program to take input for each records from end-user and insert those records into the “student” table.
The required SQL query to insert values into the table is,SQL> INSERT INTO STUDENT VALUES (1000, 'Sophia', 'London', 70);
We will take input for records from the end-user, but it will not be in the format as required for the SQL query. For example, we take input for the student name and stored in a "sname"
variable of String type. Note that while inserting character value to the table, it should be in a single quote. Therefore, to store this "sname"
value to the query we have to convert this variable as sname = "\'" + sname + "\'"
// take input
int sno = scan.nextInt();
String sname = scan.next();
String sadd = scan.next();
float avg = scan.nextFloat();
// convert sname and sadd
// as required for SQL query
sname = "\'" + sname + "\'";
sadd = "\'" + sadd + "\'";
// prepare query to insert record
String query = "INSERT INTO STUDENT VALUES (" + sno + "," + sname + ","+ sadd + "," + avg + ")";
Or, we can directly convert the string at the time of query preparation as shown below,
// prepare SQL query
query = "INSERT INTO STUDENT VALUES (" + sno + ", " + "\'" + sname + "\' ," + "\'" + sadd + "\' ," + avg + ") ";
Here semicolon required to complete the Java statement, not for SQL query. This conversion is only required while working with simple Statement interface, when we are working with PreparedStatement interface then we don’t need to do this conversion.
package com.know.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class InsertTest {
// values specific to each database
private static final String URL =
"jdbc:oracle:thin:@localhost:1521:knowprogram";
private static final String USERNAME = "scott";
private static final String PASSWORD = "tiger";
// main method
public static void main(String[] args ) {
// declare variables
Scanner scan = null;
int sno = 0;
String sname = null, sadd = null;
float avg = 0.0f;
String query = null;
Connection con = null;
Statement st = null;
int count = 0;
try {
// create Scanner class object
scan = new Scanner(System.in);
// read input
if(scan != null) {
System.out.print("Enter student number: ");
sno = scan.nextInt();
System.out.print("Enter student name: ");
sname = scan.next();
System.out.print("Enter student address: ");
sadd = scan.next();
System.out.print("Enter student avg: ");
avg = scan.nextFloat();
}
// prepare SQL query
query = "INSERT INTO STUDENT VALUES ("
+ sno + ", " + "\'" + sname +"\' ,"
+"\'"+ sadd + "\' ," + avg + ") ";
// establish the connection
con = DriverManager.getConnection(
URL, USERNAME, PASSWORD);
// create JDBC statement object
if(con != null) {
st = con.createStatement();
}
// execute the SQL query
if(st != null) {
count = st.executeUpdate(query);
}
// display result
System.out.println(count + " record inserted.");
} catch(SQLException se) {
se.printStackTrace();
} catch(Exception e) {
e.printStackTrace();
} // try-catch block
finally {
// close JDBC objects
try {
if(st != null) st.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();
}
} // finally
} //main()
} //class
Output:-
Enter student number: 1000Enter
student name: Olivia
Enter student address: Chicago
Enter student avg: 69
1 record inserted.
Now, to verify that the records are inserted or not, open the SQL plus tool or SQL developer and display the table. You can see that records are inserted into the table.
JDBC Statement Example to Update Record
Task:- Write a JDBC program to update record in a table using Statement object.
Previously, we have created a table “student” and then written a JDBC program to insert records. Now, we will develop a JDBC program to update records in the “student” table using the Statment object.
Required SQL query to update a record,SQL> UPDATE STUDENT SET SNAME = 'name', AVG = 70 WHERE SNO = 1000;
package com.know.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class InsertTest {
// values specific to each database
private static final String URL =
"jdbc:oracle:thin:@localhost:1521:knowprogram";
private static final String USERNAME = "scott";
private static final String PASSWORD = "tiger";
// main method
public static void main(String[] args ) {
// declare variables
Scanner scan = null;
int sno = 0;
String sname = null;
float avg = 0.0f;
String query = null;
Connection con = null;
Statement st = null;
int count = 0;
try {
// create Scanner class object
scan = new Scanner(System.in);
// read input
if(scan != null) {
System.out.print("Enter student number: ");
sno = scan.nextInt();
System.out.print("Enter student name to update: ");
sname = scan.next();
System.out.print("Enter student avg to update: ");
avg = scan.nextFloat();
}
// prepare SQL query
query = "UPDATE STUDENT SET SNAME = "
+ "\'" + sname + "\', AVG = "
+ avg + "WHERE SNO = " + sno;
// establish the connection
con = DriverManager.getConnection(
URL, USERNAME, PASSWORD);
// create JDBC statement object
if(con != null) {
st = con.createStatement();
}
// execute the SQL query
if(st != null) {
count = st.executeUpdate(query);
}
// display result
if(count != 0)
System.out.println(count + " record updated.");
else
System.out.println("Record updation failed");
} catch(SQLException se) {
se.printStackTrace();
} catch(Exception e) {
e.printStackTrace();
} // try-catch block
finally {
// close JDBC objects
try {
if(st != null) st.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();
}
} // finally
} // main()
} // class
Output:-
Enter student number: 1000Enter
student name to update: Emma
Enter student avg to update: 75
1 record updated.
Enter student number: 10Enter
student name to update: OliviaEnter
student avg to update: 88
Record updation failed
There student number 10 doesn’t exist in the table so, the record is not inserted. Open SQL plus tool or SQL Developer and verify that records are updated are not.
JDBC Statement Example to Delete Record
Task:- Write a JDBC program using Statement object to delete record from the table. Take one column value as input from the end-user and delete that row.
Before writing the program, first display the table. SQL> SELECT * FROM STUDENT;
The required query for this program is, SQL> DELETE FROM STUDENT WHERE SNO = 1000;
package com.know.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class DeleteTest {
// values specific to each database
private static final String URL =
"jdbc:oracle:thin:@localhost:1521:knowprogram";
private static final String USERNAME = "scott";
private static final String PASSWORD = "tiger";
// main method
public static void main(String[] args ) {
// declare variables
Scanner scan = null;
int sno = 0;
String query = null;
Connection con = null;
Statement st = null;
int count = 0;
try {
// create Scanner class object
scan = new Scanner(System.in);
// read input
if(scan != null) {
System.out.print("Enter student number: ");
sno = scan.nextInt();
}
// prepare SQL query
query = "DELETE FROM STUDENT WHERE SNO = " + sno;
// establish the connection
con = DriverManager.getConnection(
URL, USERNAME, PASSWORD);
// create JDBC statement object
if(con != null) {
st = con.createStatement();
}
// execute the SQL query
if(st != null) {
count = st.executeUpdate(query);
}
// display result
if(count != 0)
System.out.println(count + " record deleted.");
else
System.out.println("Record deletion failed");
} catch(SQLException se) {
se.printStackTrace();
} catch(Exception e) {
e.printStackTrace();
} // try-catch block
finally {
// close JDBC objects
try {
if(st != null) st.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();
}
} // finally
} //main()
} //class
Output:-
Enter student number: 1000
1 record deleted.
Enter student number: 1
Record deletion failed
Since sno = 1 is not available in the student table that’s why record deletion failed. In these three JDBC programs, we can observe that we don’t use the Resultset object. The ResultSet object is required only for Select queries i.e. DRL queries. Select queries return multiple results, so we need ResultSet object to fetch them but Non-select queries always return an integer value.
Examples on Select Queries
While establishing JDBC connection to the database we had written a simple JDBC program to select records from the table and display it to the console, for this you can refer here Oracle, MySQL, Using Eclipse. Once again In the JDBC Statement interface we developed a JDBC program to select the record but this time it was condition based and we used “WHERE” clause in the SQL query. Those two JDBC program were the great example of using Statement object while working with select queries. Now, we will develop some more JDBC program on select query using Statement object.
Count of Employees
Task:- Write a JDBC application to get the count of employees from the emp table.
The emp is a pre-defined table in Oracle database under the scott user. Required query is, SQL> SELECT COUNT(*) FROM EMP;
package com.know.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class CountEMPTest {
// values specific to each database
private static final String URL =
"jdbc:oracle:thin:@localhost:1521:knowprogram";
private static final String USERNAME = "scott";
private static final String PASSWORD = "tiger";
// main method
public static void main(String[] args ) {
// declare variables
String query = null;
Connection con = null;
Statement st = null;
ResultSet rs = null;
int count = 0;
try {
// prepare SQL query
query = "SELECT COUNT(*) FROM EMP";
// establish the connection
con = DriverManager.getConnection(
URL, USERNAME, PASSWORD);
// create JDBC statement object
if(con != null) {
st = con.createStatement();
}
// execute the SQL query
if(st != null) {
rs = st.executeQuery(query);
}
// process the resultset
if(rs != null) {
rs.next();
count = rs.getInt("COUNT(*)");
// or, getInt(1)
}
// display result
System.out.println("Count of Employees"+
" in emp table: "+ count );
} catch(SQLException se) {
se.printStackTrace();
} catch(Exception e) {
e.printStackTrace();
} // try-catch block
finally {
// close JDBC objects
try {
if(rs != null) rs.close();
} catch(SQLException se) {
se.printStackTrace();
}
try {
if(st != null) st.close();
} catch(SQLException se) {
se.printStackTrace();
}
try {
if(con != null) con.close();
} catch(SQLException se) {
se.printStackTrace();
}
} // finally
} //main()
} //class
Output:-
Count of Employees in emp table: 14
Employee Details Having Highest Salary
Task:- Write a JDBC application to get the Employee details whose has having highest salary.
Query to display all details of the emp table, SQL> SELECT * FROM EMP;
The SQL Query to display only empno, ename, job, sal information from emp table. SQL> SELECT EMPNO, ENAME, JOB, SAL FROM EMP;
Query to display the max sal from emp table, SQL> SELECT MAX(SAL) FROM EMP;
The final query to display the employee details whose have maximum sal.
SQL> SELECT EMPNO, ENAME, JOB, SAL
FROM EMP WHERE SAL =
(SELECT MAX(SAL) FROM EMP);
package com.know.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class CountEMPTest {
// values specific to each database
private static final String URL =
"jdbc:oracle:thin:@localhost:1521:knowprogram";
private static final String USERNAME = "scott";
private static final String PASSWORD = "tiger";
// main method
public static void main(String[] args ) {
// declare variables
String query = null;
Connection con = null;
Statement st = null;
ResultSet rs = null;
try {
// prepare SQL query
query = "SELECT EMPNO, ENAME, JOB, SAL "+
" FROM EMP WHERE SAL = "+
" (SELECT MAX(SAL) FROM EMP)";
// establish the connection
con = DriverManager.getConnection(
URL, USERNAME, PASSWORD);
// create JDBC statement object
if(con != null) {
st = con.createStatement();
}
// execute the SQL query
if(st != null) {
rs = st.executeQuery(query);
}
// process the resultset
if(rs != null) {
rs.next();
System.out.println(
rs.getInt("EMPNO") + " " +
rs.getString("ENAME") + " " +
rs.getString("JOB") + " " +
rs.getFloat("SAL") );
}
} catch(SQLException se) {
se.printStackTrace();
} catch(Exception e) {
e.printStackTrace();
} // try-catch block
finally {
// close JDBC objects
try {
if(rs != null) rs.close();
} catch(SQLException se) {
se.printStackTrace();
}
try {
if(st != null) st.close();
} catch(SQLException se) {
se.printStackTrace();
}
try {
if(con != null) con.close();
} catch(SQLException se) {
se.printStackTrace();
}
} // finally
} //main()
} //class
Output:-
7839 KING PRESIDENT 5000
In this application, the query can give 1 or more than one results (when more than one emp have the same max sal). So, to process the resultset, for more than one result we should use a while loop but for one result we should use if block. There is no chance that the record is not found. So, the flag is also not required.
Employee Details having Nth Highest Salary
Task:- Write a JDBC application that gives employee details who is having given nth highest salary?
Required SQL Query,
SQL> select * from(
select ename, sal, dense_rank()
over(order by sal desc)r from emp)
where r = &n;
Employees having 2nd highest salary,
SQL> /
Enter value for n: 2
old 4: WHERE R = &N
new 4: WHERE R = 2
ENAME SAL R
---------- ---------- ----------
SCOTT 3000 2
FORD 3000 2
Employees having 5th highest salary,
SQL> /
Enter value for n: 5
old 4: WHERE R = &N
new 4: WHERE R = 5
ENAME SAL R
---------- ---------- ----------
CLARK 2450 5
There can be only one employee having nth highest salary or there can be multiple employee having nth highest salary so write program accordingly.
package com.know.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class CountEMPTest {
// values specific to each database
private static final String URL =
"jdbc:oracle:thin:@localhost:1521:knowprogram";
private static final String USERNAME = "scott";
private static final String PASSWORD = "tiger";
// main method
public static void main(String[] args ) {
// declare variables
Scanner scan = null;
int n = 0;
String query = null;
Connection con = null;
Statement st = null;
ResultSet rs = null;
try {
// create Scanner class object
scan = new Scanner(System.in);
// take input
if(scan != null) {
System.out.print("Enter N value: ");
n = scan.nextInt();
}
// prepare SQL query
query = "SELECT * FROM ( " +
"SELECT ENAME, SAL, DENSE_RANK()"+
"OVER(ORDER BY SAL DESC) R FROM EMP)"+
"WHERE R = " + n;
// establish the connection
con = DriverManager.getConnection(
URL, USERNAME, PASSWORD);
// create JDBC statement object
if(con != null) {
st = con.createStatement();
}
// execute the SQL query
if(st != null) {
rs = st.executeQuery(query);
}
// process the resultset
if(rs != null) {
while(rs.next()) {
System.out.println(
rs.getString("ENAME") + " " +
rs.getFloat("SAL") + " " +
rs.getInt("R") );
}
}
} catch(SQLException se) {
se.printStackTrace();
} catch(Exception e) {
e.printStackTrace();
} // try-catch block
finally {
// close JDBC objects
try {
if(rs != null) rs.close();
} catch(SQLException se) {
se.printStackTrace();
}
try {
if(st != null) st.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();
}
} // finally
} //main()
} //class
Output for different test-cases:-
Enter N value: 2
SCOTT 3000.0 2
FORD 3000.0 2
Enter N value: 7
TURNER 1500.0 7
Enter N value: 9
WARD 1250.0 9
MARTIN 1250.0 9
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!
Also See:-
- How to Connect MySQL Database in Java Using Eclipse
- Oracle Database JDBC Connection with Example
- Different types of statements in JDBC
- JDBC PreparedStatement interface
- Statement vs PreParedStatement
- Insert record using PreparedStatement in Java
- Update record using Java PreparedStatement
- Select record using PreparedStatement in Java
- JDBC create table using statements
- Preparedstatement Example
- JDBC program to insert with Surrogate Key