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 a Java program to insert records into the table using PreparedStatement object. In this post, we will develop a Java program to update the record using the PreparedStatement object.
In this tutorial we are working with the Oracle database, you can work with any database with the required information. In our Oracle database, we have a “Product” table.
SQL> desc product;
Name Null? Type
---------- -------- -------------
PID NOT NULL NUMBER(10)
PNAME VARCHAR2(15)
PRICE FLOAT(126)
QUANTITY NUMBER(10)
In the product table we have some records,
SQL> select * from product;
PID PNAME PRICE QUANTITY
---- -------- ------ ------
102 Chair 500 5
2051 Fan 999 2
111 Table 1500 1
Program description:- Develop a JDBC program to update records of product table using PreparedStatement object.
To update the record of a row we need at least one column value to select that row and later update the required values. Required SQL query,
SQL> update product set pname='Book',
price = 20, quantity = 5
where pid = 111;
The SQL query that goes to database software without input values and becomes parsed or compiled SQL query in database software irrespective of whether it will be executed or not is called pre-compiled SQL query. PreparedStatement object of the Java application represents this pre-compiled SQL query of database software. We can use that object to set input values to SQL query, to execute SQL query and to fetch the output of SQL query for one or multiple times. PreparedStatement object is good to execute the same SQL query for multiple times either with input values or with output values.
Java program to update record using PreparedStatement object
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 UPDATE_PRODUCT_QUERY =
"UPDATE PRODUCT SET PNAME = ?, PRICE = ?, QUANTITY = ?"
+ "WHERE PID = ? ";
public static void main(String[] args ) {
// declare variables
Scanner scan = null;
int pid = 0, qty = 0;
String name = 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.println("Enter the existing product ID"
+ " to update: ");
pid = scan.nextInt();
System.out.println("Enter new details,");
System.out.print("Name of the product: ");
name = scan.next();
System.out.print("Price: ");
price = scan.nextFloat();
System.out.print("Quantity: ");
qty = 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(UPDATE_PRODUCT_QUERY);
// set input values and execute query
if(ps != null) {
// set input values to query parameters
ps.setString(1, name);
ps.setFloat(2, price);
ps.setInt(3, qty);
ps.setInt(4, pid);
// execute the query
result = ps.executeUpdate();
}
// process the result
if(result == 0)
System.out.println("Records not updated");
else
System.out.println("Records updated"+
" successfully");
} 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 existing product ID to update:
102
Enter new details,
Name of the product: Bed
Price: 2000
Quantity: 1
Records updated successfully
Enter the existing product ID to update:
1000
Enter new details,
Name of the product: Book
Price: 50
Quantity: 2
Records not updated
The product id 1000 is not available in the product table so, the records are not updated.
Now, let us verify that records are updated or not. Execute SQL query to display the table details,
SQL> select * from product;
PID PNAME PRICE QUANTITY
---- ------ ------ --------
102 Bed 2000 1
2051 Fan 999 2
111 Table 1500 1
By default the JDBC program are executed in autocommit mode. So, no need to explicitly commit the records.
In the next JDBC example, we will develop a program to display the records of the table. Also see:-
- Java Program to Insert using PreparedStatement
- JDBC PreparedStatement
- Difference between Statement and PreparedStatement
- Different Types of Statements in JDBC
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!