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 JDBC tutorial, we had developed the JDBC program to insert, update, select, and display the records using the PreparedStatement object. Now in this tutorial, we will develop some more JDBC or Java program example using the PreparedStatement object.
- Insert record into a table using PreparedStatement
- Update record using PreparedStatement
- JDBC PreparedStatement example to select and display records.
We are using the Oracle database, but you can work with any database with required information like Jar file, URL syntax, username, password. In the Oracle database, we have an emp table under the Scott user. We will use this table.
JDBC PreparedStatement Example to Select Record
Program description:- Write a JDBC application that gives employee details based on the given department number.
Prepare the SQL query
SQL> SELECT * FROM emp;
It will display all the details of emp table.
SQL> SELECT empno, ename, job, sal, deptno FROM emp;
Above query will display only empno, ename, job, sal and deptno columns from the emp table.
SQL> SELECT empno, ename, job, sal,
deptno FROM emp
WHERE deptno IN (10, 20);
It displays empno, ename, job, sal, and deptno columns from the emp table, only for whose department number is 10 or 20.
SQL> SELECT empno, ename, job, sal,
deptno FROM emp
WHERE deptno IN (10, 20)
ORDER BY deptno;
It displays empno, ename, job, sal, and deptno columns from the emp table, only for whose department number is 10 or 20, in the order of deptno.
Now, our query is ready. Let us develop the application.
Develop the application
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
public class SelectTest {
// SQL query
private static final String SELECT_EMPLOYEE_QUERY =
"SELECT EMPNO, ENAME, JOB, SAL, DEPTNO FROM EMP "
+ " WHERE DEPTNO IN (?,?) ORDER BY DEPTNO";
public static void main(String[] args ) {
// declare variables
Scanner scan = null;
int deptno1 = 0, deptno2 = 0;
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
boolean flag = false;
try {
// read input
scan = new Scanner(System.in);
if(scan != null) {
System.out.print("Enter deptno1: ");
deptno1 = scan.nextInt();
System.out.print("Enter deptno2: ");
deptno2 = 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(SELECT_EMPLOYEE_QUERY);
// set input value to query parameter
if(ps != null) {
ps.setInt(1, deptno1);
ps.setInt(2, deptno2);
}
// execute the query
if(ps != null)
rs = ps.executeQuery();
// process the result
if(rs != null) {
while(rs.next()) {
flag = true;
// SELECT EMPNO, ENAME, JOB
// SAL, DEPTNO FROM EMP
System.out.println(""
+ rs.getInt("EMPNO") +" "
+ rs.getString("ENAME") +" "
+ rs.getString("JOB") +" "
+ rs.getInt("SAL") +" "
+ rs.getInt("DEPTNO"));
}
}
if(flag)
System.out.println("Records fetched & displayed");
else
System.out.println("Records not found");
} catch(SQLException se) {
se.printStackTrace();
} catch(Exception e) {
e.printStackTrace();
} // end of try-catch block
finally {
// close JDBC objects
try {
if(rs != null) rs.close();
} catch(SQLException se) {
se.printStackTrace();
}
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 deptno1: 10
Enter deptno2: 20
7934 MILLER CLERK 1300 10
7782 CLARK MANAGER 2450 10
………
7902 FORD ANALYST 2850 20
7876 ADAMS CLERK 1100 20
………
Records fetched & displayed
Enter deptno1: 10
Enter deptno2: 30
7782 CLARK MANAGER 2450 10
7839 KING PRESIDENT 5000 10
………
7698 BLAKE MANAGER 2850 30
7844 TURNER SALESMAN 1500 30
………
Records fetched & displayed
Enter deptno1: 40
Enter deptno2: 80
Records not found
JDBC program to display Nth highest salary
Program description:- Write a JDBC application example using PreparedStatement that gives employees details from emp table who has having nth highest sal.
From the emp table we have to fetch the employee having nth highest salary and then display those records.
Prepare the SQL query,
SQL> SELECT * FROM
(SELECT ename, sal, dense_rank()
over(ORDER BY sal desc) r
FROM emp) WHERE r=&n;
Enter value for n: 9
ENAME SAL R
-------- ------- ---
WARD 1250 9
MARTIN 1250 9
Develop the application,
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
public class SelectTest {
// SQL query
private static final String SELECT_EMPLOYEE_QUERY =
" SELECT * FROM " +
" (SELECT ENAME, SAL, DENSE_RANK() OVER " +
" (ORDER BY SAL DESC) R FROM EMP) WHERE R = ? ";
public static void main(String[] args ) {
// declare variables
Scanner scan = null;
int n = 0;
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
boolean flag = false;
try {
// read input
scan = new Scanner(System.in);
if(scan != null) {
System.out.print("Enter n value: ");
n = 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(SELECT_EMPLOYEE_QUERY);
// set input value to query parameter
if(ps != null) {
ps.setInt(1, n);
}
// execute the query
if(ps != null)
rs = ps.executeQuery();
// process the result
if(rs != null) {
while(rs.next()) {
flag = true;
// SELECT ENAME, SAL, DENSE_RANK()
System.out.println(""
+ rs.getString("ENAME") +" "
+ rs.getInt("SAL") +" "
+ rs.getInt("R"));
}
}
if(flag)
System.out.println("Records fetched & displayed");
else
System.out.println("Records not found");
} catch(SQLException se) {
se.printStackTrace();
} catch(Exception e) {
e.printStackTrace();
} // end of try-catch block
finally {
// close JDBC objects
try {
if(rs != null) rs.close();
} catch(SQLException se) {
se.printStackTrace();
}
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 n value: 1
KING 5000 1
Records fetched & displayed
Enter n value: 9
WARD 1250 9
MARTIN 1250 9
Records fetched & displayed
Enter n value: 20
Records not found
JDBC Program Example for Non-select SQL Queries using PreparedStatement
To send and execute non-select SQL query in database software use executeUpdate(-) or executeLargeUpdate(-). The executeLargeUpdate(-) method is given from java8 version.
public int executableUpdate(String query) throws SQLException
public long executableLargeUpdate(String query) throws SQLException
Program description:- Write a JDBC application to delete student details based on given student city/address using PreparedStatement object.
In Oracle database create table with some records,
SQL> select * from student;
SNO SNAME SADD AVG
---- ------- ----------- ----------
2000 Sophia Manchester 89
3000 William London 75
1000 Alex Boise 80
1005 Ameila Washington 59
In SQL plus tool, by default autocommit mode is off. So, whenever we delete any record from the table then it doesn’t deleted permanently. We can get those delete records by using “rollback” command. To delete them permanently we should use “commit” command.
But in the JDBC application, by default the auto-commit mode is on. So, whenever we delete records from JDBC application then it deletes permanently. We can’t get back those deleted records.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;
public class DeleteStudent {
// SQL query
private static final String DELETE_STUDENT_QUERY =
"DELETE FROM STUDENT WHERE SADD = ?";
public static void main(String[] args ) {
// declare variables
Scanner scan = null;
String address = null;
Connection con = null;
PreparedStatement ps = null;
int result = 0;
try {
// read input
scan = new Scanner(System.in);
if(scan != null) {
System.out.print("Enter address: ");
address = scan.next();
}
// 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(DELETE_STUDENT_QUERY);
// set input values and execute query
if(ps != null) {
// set input values to query parameters
ps.setString(1, address);
// execute the query
result = ps.executeUpdate();
}
// process the result
if(result == 0)
System.out.println("No records found for deletion");
else
System.out.println(result + " records deleted");
} 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 address: London
1 records deleted
Enter address: London
No records found for deletion
In SQL plus tool by default auto-commit mode is off, and in JDBC application auto-commit mode is on. Whenever we delete records using the SQL* Plus tool and didn’t commit it, then the JDBC application can’t access those data, and the deadlock situation cames. JDBC application hangs until we didn’t either commit or rollback the data in the SQL plus tool, then only JDBC application takes the further decision.
To demonstrate it, delete sadd = Washington
in SQL plus tool, and then without commit
or rollback
, execute the above JDBC application and from JDBC application try to delete sadd = Washington
. What happens?
Deletion from SQL Plus tool,
SQL> delete from student
where sadd = ‘Washington’;
1 row deleted.
SQL> select * from student;
Now, executing JDBC program
Enter address: Washington
The JDBC program reached into the hang mode. Currently the records are in stream so, it can’t say that records not found, but it can’t delete because the records is not in the table.
Now, from SQL tool if we execute “commit” command then the jdbc program will display “No records found for deletation”. Or, from SQL tool if we execute “rollback” command then JDBC program will display “1 record deleted”.
Important Points From this Application
- While dealing with the non-select query, there is no need of working with ResultSet object.
- All operations in the JDBC app takes place on database software through JDBC driver.
Points on Auto-commit mode
- The non-select SQL queries executed from SQL prompt can be
committed
orrolled back
explicitly because they will be executed in database software by disabling auto-commit mode. - The non-select SQL queries that are executing in database software by coming from front-end like java application can’t be
committed
orrolled back
because they will execute in database software by enabling auto-commit mode. The query execution result will be committed immediately so that we can’t rollback it later. - JDBC applications can’t read and manipulate uncommitted records. If such records are coming for manipulation then the application will hang up until the records become stable in the database.
Delete Records based on Start and End Range
Program description:- Write a JDBC application to delete the student details based on the given start, end range of the student number.
Prepare the SQL query,
SQL> DELETE FROM student WHERE
sno>=100 AND sno<=1000;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;
public class DeleteStudent {
// SQL query
private static final String DELETE_STUDENT_QUERY =
"DELETE FROM STUDENT WHERE" +
" SNO >= ? AND SNO <= ?";
public static void main(String[] args ) {
// declare variables
Scanner scan = null;
int startSno = 0, endSno = 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 start sno: ");
startSno = scan.nextInt();
System.out.print("Enter end sno: ");
endSno = 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(DELETE_STUDENT_QUERY);
// set input values and execute query
if(ps != null) {
// set input values to query parameters
ps.setInt(1, startSno);
ps.setInt(2, endSno);
// execute the query
result = ps.executeUpdate();
}
// process the result
if(result == 0)
System.out.println("No records found"+
" for deletion");
else
System.out.println(result + " records deleted");
} 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 start sno: 1000
Enter end sno: 2000
3 records deleted
Enter start sno: 100
Enter end sno: 1000
No records found for deletion
Execute both select and non-select query using single method
Can we execute both the Select and non-select query by using a single method?
Yes, we can execute both select and non-select queries by using a single method execute(-)
.
Prototype/signature of execute(-) method:-public boolean execute(String qry) throws Exception
- This method returns true when a select query is executed and we should use getResultSet() to get the records given by select query.
- It returns false when a non-select query is executed and we should use st.getUpdateCount(-) to get the numeric value that represents the number of records that are affected.
- This method is not industry standard because it makes the programmer execute query separately or to gather results of the query separately so use executeQuery(-) for select queries and use executeUpdate(-) for non-select queries.
- While executing the insert, create the table, drop table queries we will get an exception if the query execution is failed.
- While dealing with delete, update queries we will get zero if the query execution is effected.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
public class DeleteStudent {
public static void main(String[] args ) {
// declare variables
Scanner scan = null;
String query = null;
Connection con = null;
PreparedStatement ps = null;
boolean flag = false;
ResultSet rs = null;
int count = 0;
try {
// read input
scan = new Scanner(System.in);
if(scan != null) {
System.out.print("Enter query: ");
query = scan.nextLine();
}
// 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(query);
// execute the query
if(ps != null)
flag = ps.execute();
// process the result
if(flag) {
System.out.println("Select query is executed");
rs = ps.getResultSet();
// process the resultSet
while(rs.next()) {
System.out.println(rs.getString(1)+" "
+rs.getString(2)+" "
+rs.getString(3) );
}
} else {
System.out.println("The non-select query"
" is executed");
count = ps.getUpdateCount();
System.out.println("Number of records that"+
" are affected = "+ count);
}
} catch(SQLException se) {
se.printStackTrace();
} catch(Exception e) {
e.printStackTrace();
} // end of try-catch block
finally {
// close JDBC objects
try {
if(rs != null) rs.close();
} catch(SQLException se) {
se.printStackTrace();
}
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 query: select sno, sname, avg from student
Select query is executed
2000 Sophia 89
3000 William 75
1000 Alex 80
1005 Amelia 65
Enter query: update student set avg = 95 where sno = 2000
The non-select query is executed
Number of records that are affected = 1
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!