
- JDBC - Home
- JDBC - Introduction
- JDBC - SQL Syntax
- JDBC - Environment
- JDBC - Sample Code
- JDBC - Driver Types
- JDBC - Connections
- JDBC - Statements
- JDBC - Result Sets
- JDBC - Data Types
- JDBC - Transactions
- JDBC - Exceptions
- JDBC - Batch Processing
- JDBC - Stored Procedure
- JDBC - Streaming Data
- JDBC - RowSet
- JDBC - Copy Database
- JDBC - ACID Properties
- JDBC - Connection Pooling
- JDBC Examples
- JDBC - Create Database
- JDBC - Select Database
- JDBC - Drop Database
- JDBC - Create Tables
- JDBC - Drop Tables
- JDBC - Insert Records
- JDBC - Select Records
- JDBC - Update Records
- JDBC - Delete Records
- JDBC - WHERE Clause
- JDBC - Like Clause
- JDBC - Sorting Data
- JDBC Useful Resources
- JDBC - Questions and Answers
- JDBC - Quick Guide
- JDBC - Useful Resources
- JDBC - Discussion
- Useful - Java Tutorials
JDBC - Update Records
This chapter provides examples on how to update records in a table using JDBC application. Before executing the following example, make sure you have the following in place −
To execute the following example you can replace the username and password with your actual user name and password.
Your MySQL or whatever database you are using is up and running.
Required Steps
The following steps are required to create a new Database using JDBC application −
Import the packages − Requires that you include the packages containing the JDBC classes needed for database programming. Most often, using import java.sql.* will suffice.
Open a connection − Requires using the DriverManager.getConnection() method to create a Connection object, which represents a physical connection with a database server.
Execute a query − Requires using an object of type Statement for building and submitting an SQL statement to update records in a table. This Query makes use of IN and WHERE clause to update conditional records.
-
Clean up the environment − try with resources automatically closes the resources.
Example: Updating Record in a Table Using Statement Object
In this example, we've four static strings containing a dababase connection url, username, password and a SELECT query. Now using DriverManager.getConnection() method, we've prepared a database connection. Once connection is prepared, we've prepared a Statement object using createStatement() method. As next step, We've prepared and executed an update query on table REGISTRATION by calling statement.executeUpdate() method where we've updated age as 30 where registration id are 100 and 101. Then using executeQuery(), all records are fetched and stored in a ResultSet object. ResultSet is iterated and all records are printed.
In case of any exception while connecting to the database, a catch block handled SQLException and printed the stack trace.
Copy and paste the following example in JDBCExample.java, compile and run as follows −
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class JDBCExample { static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT"; static final String USER = "guest"; static final String PASS = "guest123"; static final String QUERY = "SELECT id, first, last, age FROM Registration"; public static void main(String[] args) { // Open a connection try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS); Statement stmt = conn.createStatement(); ) { String sql = "UPDATE Registration " + "SET age = 30 WHERE id in (100, 101)"; stmt.executeUpdate(sql); ResultSet rs = stmt.executeQuery(QUERY); while(rs.next()){ //Display values System.out.print("ID: " + rs.getInt("id")); System.out.print(", Age: " + rs.getInt("age")); System.out.print(", First: " + rs.getString("first")); System.out.println(", Last: " + rs.getString("last")); } rs.close(); } catch (SQLException e) { e.printStackTrace(); } } }
Output
Now let us compile the above example as follows −
C:\>javac JDBCExample.java C:\>
When you run JDBCExample, it produces the following result −
C:\>java JDBCExample ID: 100, Age: 30, First: Zara, Last: Ali ID: 101, Age: 30, First: Mahnaz, Last: Fatma ID: 102, Age: 30, First: Zaid, Last: Khan ID: 103, Age: 28, First: Sumit, Last: Mittal C:\>
Example: Updating Record in a Table Using PreparedStatement
In this example, we've three static strings containing a dababase connection url, username, password. Now using DriverManager.getConnection() method, we've prepared a database connection. Once connection is prepared, we've prepared a PreparedStatement object using prepareStatement() method. As next step, We've prepared and executed an update query on table employees by calling statement.executeUpdate() method where we've updated age as 51 where employee id is 35. Then using executeQuery(), updated record is fetched and stored in a ResultSet object. ResultSet is iterated and the record is printed.
In case of any exception while connecting to the database, a catch block handled SQLException and printed the stack trace.
Copy and paste the following example in JDBCExample.java, compile and run as follows −
import java.sql.*; // This class demonstrates use of UPDATE using Java PreparedStatement public class JDBCExample { static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT"; static final String USER = "root"; static final String PASS = "guest123"; public static void main(String args[]) { try{ Connection conn = DriverManager.getConnection(DB_URL, USER, PASS); String upd_qry = "update employees set age = ? where id =? "; PreparedStatement pstmt = conn.prepareStatement( upd_qry); pstmt.setInt(1, 51); pstmt.setInt(2, 35); pstmt.executeUpdate(); String sel_qry = "select * from employees where id = 35"; ResultSet rs = pstmt.executeQuery(sel_qry); System.out.println("Displaying updated record.."); while(rs.next()){ System.out.print(" ID: " + rs.getInt(1)); System.out.print(" AGE: " + rs.getInt(2)); System.out.print(" FirstName: " + rs.getString(3)); System.out.println(" LastName: " + rs.getString(4)); } rs.close(); pstmt.close(); conn.close(); }catch(SQLException e){ e.printStackTrace(); } } }
Output
Now let us compile the above example as follows −
C:\>javac JDBCExample.java C:\>
When you run JDBCExample, it produces the following result −
C:\>java JDBCExample Displaying updated record.. ID: 35 AGE: 51 FirstName: Raksha LastName: Agarwal C:\>
Example: Updating Multiple Columns in single SQL
We can update multiple columns easily as well.
In this example, we've three static strings containing a dababase connection url, username, password. Now using DriverManager.getConnection() method, we've prepared a database connection. Once connection is prepared, we've prepared a Statement object using createStatement() method. As next step, We've prepared and executed an update query on table employees by calling statement.executeUpdate() method where we've updated age as 50 and first name as Shahbaz where employee id is 1. Then using executeQuery(), updated record is fetched and stored in a ResultSet object. ResultSet is iterated and the record is printed.
In case of any exception while connecting to the database, a catch block handled SQLException and printed the stack trace.
Copy and paste the following example in JDBCExample.java, compile and run as follows −
import java.sql.*; // This class demonstrates use of updating multiple columns with a single SQL command public class JDBCExample { static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT"; static final String USER = "root"; static final String PASS = "guest123"; public static void main(String args[]) { try{ Connection conn = DriverManager.getConnection(DB_URL, USER, PASS); String upd_qry = "update employees set age = 50, first='Shahbaz' where id =1 "; Statement stmt = conn.createStatement(); stmt.executeUpdate(upd_qry); String sel_qry = "select * from employees where id = 1"; ResultSet rs = stmt.executeQuery(sel_qry); System.out.println("Displaying updated record.."); while(rs.next()){ System.out.print(" ID: " + rs.getInt(1)); System.out.print(", AGE: " + rs.getInt(2)); System.out.print(", FirstName: " + rs.getString(3)); System.out.println(", LastName: " + rs.getString(4)); } rs.close(); stmt.close(); conn.close(); }catch(SQLException e){ e.printStackTrace(); } } }
Output
Now let us compile the above example as follows −
C:\>javac JDBCExample.java C:\>
When you run JDBCExample, it produces the following result −
C:\>java JDBCExample Displaying updated record.. ID: 1, AGE: 50, FirstName: Shahbaz, LastName: Ali C:\>