JDBC - Update Records

Previous
Quiz
Next

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:\>
Print Page
Previous
Next
Advertisements