How to update a row using a ResultSet object - Java JDBC

Java examples for JDBC:ResultSet

  1. HOME
  2. Java
  3. JDBC
  4. ResultSet

Description

How to update a row using a ResultSet object

Demo Code

import static java.sql.ResultSet.CONCUR_UPDATABLE;
import static java.sql.ResultSet.TYPE_FORWARD_ONLY;

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Main {
  public static void main(String[] args) {
    Connection conn = null;// ww  w .ja  v  a2s.  c om
    try {
      conn = JDBCUtil.getConnection();
      giveRaise(conn, 10.0);
      JDBCUtil.commit(conn);
    } catch (SQLException e) {
      System.out.println(e.getMessage());
      JDBCUtil.rollback(conn);
      e.printStackTrace();
    } finally {
      JDBCUtil.closeConnection(conn);
    }
  }

  public static void giveRaise(Connection conn, double raise)
      throws SQLException {
    String SQL = "select person_id, first_name, last_name, "
        + "income from person";

    Statement stmt = null;

    try {
      stmt = conn.createStatement(TYPE_FORWARD_ONLY, CONCUR_UPDATABLE);
      ResultSet rs = stmt.executeQuery(SQL);
      int concurrency = rs.getConcurrency();
      if (concurrency != CONCUR_UPDATABLE) {
        System.out.println("The JDBC driver does not "
            + "support updatable result sets.");
        return;
      }
      while (rs.next()) {
        double oldIncome = rs.getDouble("income");
        double newIncome = 0.0;

        if (rs.wasNull()) {
          oldIncome = 10000.00;
          newIncome = oldIncome;
        } else {
          newIncome = oldIncome + oldIncome * (raise / 100.0);
        }
        rs.updateDouble("income", newIncome);
        int personId = rs.getInt("person_id");
        String firstName = rs.getString("first_name");
        String lastName = rs.getString("last_name");

        System.out.println(firstName + " " + lastName + " (person id="
            + personId + ") income changed from " + oldIncome + " to "
            + newIncome);
        rs.updateRow();
      }
    } finally {
      JDBCUtil.closeStatement(stmt);
    }
  }
}

class JDBCUtil {
  public static Connection getConnection() throws SQLException {
    // Register the Java DB embedded JDBC driver
    Driver derbyEmbeddedDriver = null;// new
                                      // org.apache.derby.jdbc.EmbeddedDriver();
    DriverManager.registerDriver(derbyEmbeddedDriver);

    String dbURL = "jdbc:derby:beginningJavaDB;create=true;";
    String userId = "root";
    String password = "password";

    Connection conn = DriverManager.getConnection(dbURL, userId, password);
    conn.setAutoCommit(false);
    return conn;
  }

  public static void closeConnection(Connection conn) {
    try {
      if (conn != null) {
        conn.close();
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public static void closeStatement(Statement stmt) {
    try {
      if (stmt != null) {
        stmt.close();
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public static void closeResultSet(ResultSet rs) {
    try {
      if (rs != null) {
        rs.close();
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public static void commit(Connection conn) {
    try {
      if (conn != null) {
        conn.commit();
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public static void rollback(Connection conn) {
    try {
      if (conn != null) {
        conn.rollback();
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public static void main(String[] args) {
    Connection conn = null;
    try {
      conn = getConnection();
      System.out.println("Connetced to the database.");
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      closeConnection(conn);
    }
  }
}

Related Tutorials