Table of Content

---

---

In the previous series of the tutorials, we have learnt about insertion of records in the table and fetching the records using select query.

In this example, we are looking into the JDBC database tutorial to update and fetch records from the selected MySQL Database table

JDBC Update Records from Database :

package com.c4learn.jdbc;
import java.sql.*;
public class JDBCSelectRecord {
  // JDBC driver name and database URL
  static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
  static final String DB = "jdbc:mysql://localhost/student";
  // 1. Database User name & Password
  static final String USER = "admin";
  static final String PWD = "password";
  public static void main(String[] args) {
    Connection con = null;
    Statement stmt = null;
    String sql = "";
    try {
      // 2. Register JDBC driver
      Class.forName("com.mysql.jdbc.Driver");
      // 3. Open a connection
      System.out.println("Connecting to database...");
      con = DriverManager.getConnection(DB, USER, PWD);
      // 4. Execute a query
      System.out.println("Records :");
      stmt = con.createStatement();
      sql = "update user set age = 10 where roll = 1";
          stmt.executeUpdate(sql);
      sql = "update user set age = 20 where roll = 2";
          stmt.executeUpdate(sql);
      sql = "update user set age = 30 where roll = 3";
          stmt.executeUpdate(sql);
      sql = "select * from user";
      ResultSet rs = stmt.executeQuery(sql);
      // 5. Fetch Details
      while (rs.next()) {
        // Retrieve by column name
        int id = rs.getInt("roll");
        int age = rs.getInt("age");
        String first = rs.getString("first");
        String last = rs.getString("last");
        // Display values
        System.out.print("Roll : " + id);
        System.out.print(", Age : " + age);
        System.out.print(", First : " + first);
        System.out.println(", Last : " + last);
      }
      rs.close();
    } catch (SQLException e) {
      System.out.println(e.getMessage());
    } catch (Exception e) {
      System.out.println(e.getMessage());
    } finally {
      try {
        System.out.println("Closing Connection");
        if (stmt != null)
          stmt.close();
      } catch (SQLException e) {
      }
      try {
        if (con != null)
          con.close();
      } catch (SQLException sqlEx) {
        System.out.println(sqlEx.getMessage());
      }
    }
  }
}

Output :

Connecting to database...
Records :
Roll : 1, Age : 10, First : Ram, Last : Naik
Roll : 2, Age : 20, First : Sam, Last : George
Roll : 3, Age : 30, First : Ani, Last : Pate
Closing Connection

Explanation : JDBC Update Record

We have already inserted the records in the database, Now we want to update certain fields of the database using the update query, If we want to update the records then we can carry out following steps.

Step 1 : Importing the packages :

When we are dealing with the database connectivity then we need to include the required jar file which is necessary for the database programming.

import java.sql.*;

Step 2 : Registering JDBC driver :

After importing the packages we need to initialize a driver to open a communications channel with the database.

Class.forName("com.mysql.jdbc.Driver");

Step 3 : Open a connection .

DriverManager.getConnection() method is required to create a Connection object.

It represents a physical connection with database server. Below statement is used to create the DB URL -

static final String DB = "jdbc:mysql://localhost/student";

Using below statement we can connect to database physically.

System.out.println("Connecting to database...");
con = DriverManager.getConnection(DB_URL, USER,PWD);

Step 4 : Execute a query .

Below statement will create statement object for executing the query.

stmt = con.createStatement();

We have created the following query -

sql = "update user set age = 10 where roll = 1";

To execute the query using the statement object for updating record, we can write following line of code -

stmt.executeUpdate(sql);

Step 5 : Clean up the environment .

After the execution of the query we need to clean up the resources. It requires explicitly closing all database resources

if (statement != null)
       statement.close();

and

if (con != null)
          con.close();

---

---