Advertisement

---

Top

< Prev

Java Database Connectivity(JDBC) API provide a set of classes and interfaces that allow a Java application to connect to a database and execute SQL statements such as -

• Creating a table.
• Inserting the data in the database.
• Retrieving the data from the database.
• Updating the data in the database.

In this article, we are going to explain how to update records in an existing table within the Oracle database by UPDATE SQL statement using its JDBC thin driver.

Note :

The JDBC classes and interfaces are available in java.sql and javax.sql packages.

Steps to update records in a table with UPDATE SQL statement using JDBC -

• Loading and registering database driver to connect our Java application with a database.

Class.forName("oracle.jdbc.driver.OracleDriver");


In the upcoming example, we are going to create a table within the Oracle database using its JDBC driver.




• Creating a connection between the Java program and the Oracle database.

Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","scott", "tiger");


In the upcoming example, we are using Oracle's thin JDBC driver to create a table, where -
• localhost refers to our local computer machine where the Oracle database server is running.
• 1521 is the general port number to connect to the database.
• XE stands for Oracle Express Edition Database.
• scott is our username to connect to the Oracle database and tiger is the password.





• Creating a Statement object to excute SQL statements

Statement stmt = con.createStatement();

Creating a Statement object to using createStatement() method of Connection interface.




• Executing a SQL query using Statement object.

int count = stmt.executeUpdate(query);

count gives us the total number of rows updated in a database table due to the execution of a SQL query using executeUpdate() method, where query is a String object which specifies the SQL query to execute.




• Closing the database connection

con.close();

con is a Connection reference used to close the connection with the database after the updation is over.



We are going to update records in an existing table named MyTable within the Oracle database, which we created and filled with values when we learned -

• How to create a table using JDBC.
• How to insert values in table using JDBC.

Advertisement

Updating data in database table using JDBC

import java.sql.*;

class A
{
public static void main(String... ar)
{
try
{
//First SQL UPDATE Query to update record.
String query1 =  "Update MyTable2 Set FirstName='Thomas' Where FirstName = 'Tom'";


//Second SQL UPDATE Query to update record.
String query2 =  "Update MyTable2 Set FirstName='Bradly' where age = '53'";


//Third SQL SELECT Query to retrieve updated records.
String query3 =  "SELECT * FROM MyTable2";


Class.forName("oracle.jdbc.driver.OracleDriver");

Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","System", "Promila21");
Statement stmt = con.createStatement();


//Executing first SQL UPDATE query using executeUpdate()  method of Statement object.
int count = stmt.executeUpdate(query1);
System.out.println("Number of rows updated by executing query1 =  " + count);



//Executing second SQL UPDATE query using executeUpdate()  method of Statement object.
count = stmt.executeUpdate(query2);
System.out.println("Number of rows updated by executing query2 =  " + count);



//Executing SQL SELECT query using executeQuery()  method of Statement object.
ResultSet rs = stmt.executeQuery(query3);
System.out.println("Result of executing query3 to display updated records");
System.out.println("ID " + "\t" + "FirstName" +  "\t" +  "LastName" + "\t" + "Age");

//looping through the number of row/rows retrieved after executing SELECT query3
while(rs.next()) 
{
System.out.print(rs.getString("ID") + "\t");
System.out.print(rs.getString("FirstName") + "\t" + "\t");
System.out.print(rs.getString("LastName")+ "\t" +  "\t");
System.out.println(rs.getString("Age") + "\t");
}

}
catch(SQLException e)
{
System.out.println(e);
}

}//main() method ends

}//class definitin ends

Output

Number of rows updated by executing query1 =  1
Number of rows updated by executing query2 =  1
Result of executing query3 to display updated records
ID      FirstName       LastName        Age
1       Thomas          Hanks           61
2       Johnny          Depp            54
3       Bradly          Pitt            53

In the output, you may see the updated records in table MyTable, where FirstName Tom is changed to Thomas and Brad is changed to Bradley.



ID	FirstName	LastName	Age
1	Thomas	Hanks	61
2	Johnny	Depp	54
3	Bradley	Pitt	53

Table - MyTable





Please share this article -






< Prev
< JDBC SELECT from table



Advertisement