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 learned about PreparedStatement object and later we developed a Java program to insert records into the table and another program to update records of a table. Now, in this post, we will post, we will develop a Java program to select and display the record using the PreparedStatement object.
To fetch the records of a table we need ResultSet object and using the getXxx(-) method of ResultSet we can get the details of table.
We are working with the Oracle database, but you can work with any database with the required information. In the Oracle database, we have a product table which has the details of product id, product name, price of the product, and the quantity.
SQL> SELECT * FROM product;
PID PNAME PRICE QUANTITY
----- -------- ------- ----------
102 Bed 2000 1
2051 Fan 999 2
111 Table 1500 1
SQL> DESC product;
Name Null? Type
--------- -------- ----------
PID NOT NULL NUMBER(10)
PNAME VARCHAR2(15)
PRICE FLOAT(126)
QUANTITY NUMBER(10)
We want to display the records of the product table. So, no need to take any input value from the end-user, and also no need to set query parameters.
The required SQL query is,
SQL> SELECT pid, pname, price,
quantity FROM product;
Note:- While developing the JDBC application it is not recommended to use * in the SQL query, so SELECT * FROM product
is not recommended.
Java program to display table records
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class DisplayProductTable {
// SQL query
private static final String SELECT_PRODUCT_QUERY =
"SELECT PID, PNAME, PRICE, QUANTITY FROM PRODUCT";
public static void main(String[] args ) {
// declare variables
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
// 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_PRODUCT_QUERY);
// execute the query
if(ps != null) {
rs = ps.executeQuery();
}
// process the result
if(rs != null) {
while(rs.next()) {
System.out.println(""
+ rs.getInt("PID") +" "
+ rs.getString("PNAME") +" "
+ rs.getFloat("PRICE") +" "
+ rs.getInt("QUANTITY"));
}
}
System.out.println("\nRecords displayed");
} 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();
}
}
} //end of main
} //end of class
Output:-
102 Bed 2000.0 1
2051 Fan 999.0 2
111 Table 1500.0 1
Records displayed
Select and display record based on given value using PreparedStatement
Now let us develop another JDBC program that will select the row based on the given value and display only those row details.
Required SQL query,
SQL> SELECT pname, price, quantity
FROM product WHERE pid = 102;
PNAME PRICE QUANTITY
------- ---------- ----------
Bed 2000 1
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_PRODUCT_QUERY =
"SELECT PNAME, PRICE, QUANTITY FROM PRODUCT"+
" WHERE PID = ?";
public static void main(String[] args ) {
// declare variables
Scanner scan = null;
int pid = 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 product ID: ");
pid = 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_PRODUCT_QUERY);
// set input value to query parameter
if(ps != null)
ps.setInt(1, pid);
// execute the query
if(ps != null)
rs = ps.executeQuery();
// process the result
if(rs != null) {
while(rs.next()) {
flag = true;
System.out.println(""
+ rs.getString("PNAME") +" "
+ rs.getFloat("PRICE") +" "
+ rs.getInt("QUANTITY"));
}
}
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(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 product ID: 102
Bed 2000.0 1
Records fetched & displayed
Enter product ID: 1000
Records not found
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:-
- 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