StackStalk
  • Home
  • Java
    • Java Collection
    • Spring Boot Collection
  • Python
    • Python Collection
  • C++
    • C++ Collection
    • Progamming Problems
    • Algorithms
    • Data Structures
    • Design Patterns
  • General
    • Tips and Tricks

Monday, July 28, 2014

JDBC Database Access Examples

 July 28, 2014     Java     No comments   

This article provides a list of key examples which we would commonly encounter when implementing a Java application requiring database access. For purpose of these examples the assumption is that PostgreSQL database is already setup and necessary database driver is downloaded and added to the CLASSPATH. Refer to this article JDBC Quick Start for a quick introduction to get started.

Create Table using JDBC

Let us consider a simple car loan EMI table with 3 columns EMI Number, EMI Amount and Interest Amount. To create this table on a PostgreSQL database we would do the following. The Statement object is used for executing a static SQL statement.

public static void createTable(Connection connection) {
  try ( Statement stmt = connection.createStatement(); ) {
    String sql = "CREATE TABLE car_loan_emi (\r\n" + 
          "    emi_number      numeric(2) PRIMARY KEY,\r\n" + 
          "    emi_amount      integer,\r\n" + 
          "    interest_amount integer\r\n" + 
          ");";
    stmt.execute(sql);   
  } catch (SQLException e) {
    e.printStackTrace();
  } 
}

Insert records using JDBC

Let us consider the case where we need to populate our car loan EMI table with the values for 12 months. First let us consider the simple case of inserting a record in the table.
public static void insertSingle(Connection connection) {
  try ( Statement stmt = connection.createStatement(); ) {
    String sql = "INSERT INTO car_loan_emi values(1, '10000', '990');";
    stmt.execute(sql);   
  } catch (SQLException e) {
    e.printStackTrace();
  } 
}
Let us say we want to insert EMI values for 12 months into the car loan EMI table. In this case we can leverage PreparedStatement object for sending SQL queries to the database. The advantage of using PreparedStatement is that we use the same object and we supply it with different values when it is executed.
public static void insertMultiple(Connection connection) {
  String sql = "INSERT INTO car_loan_emi values(?, ?, ?);";
  try {
    connection.setAutoCommit(false);
  } catch (SQLException e2) {
    e2.printStackTrace();
  }
  try ( PreparedStatement stmt = connection.prepareStatement(sql); ) {
    for ( int month = 1; month <= 12; month++ ) {
      stmt.setInt(1, month);
      stmt.setInt(2, 10000);
      // ignore this formula
      int interest = (int)((100000 - ((month - 1) * 10000)) * 0.12);
      stmt.setInt(3, interest);
      stmt.executeUpdate();
    }
    connection.commit();
  } catch (SQLException e) {
    e.printStackTrace();
  } finally {
    try {
      connection.setAutoCommit(true);
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
}

Select records using JDBC

To select records from the car loan EMI table we use a ResultSet which is usually generated by querying the database with a Statement. The ResultSet object provides getter methods for retrieving the values.
public static void selectRecords(Connection connection) {
  try ( Statement stmt = connection.createStatement(); ) {
    ResultSet rs = stmt.executeQuery("SELECT * from car_loan_emi");
    while ( rs.next() ) {
      System.out.println(rs.getInt(1)); 
      System.out.println(rs.getInt(2));
      System.out.println(rs.getInt(3));
    }
  } catch (SQLException e) {
    e.printStackTrace();
  } 
}

Update records using JDBC

To update records in a table we can either execute an SQL statement (or) update using ResultSet. There are 3 types of ResultSet types.

  1. TYPE_FORWARD_ONLY: The result set cannot be scrolled and the cursor can be moved forward only.
  2. TYPE_SCROLL_INSENSITIVE: The result set can be scrolled and the cursor can be moved both forward and backward. The result set is insensitive to changes made in the underlying data source.
  3. TYPE_SCROLL_SENSITIVE: The result set can be scrolled and the cursor can be moved both forward and backward. The result set reflects the changes made in the underlying data source.
We update the car loan EMI table for a specific month using SQL query.
public static void updateRows(Connection connection) {
  try ( Statement stmt = connection.createStatement(); ) {
    String sql = "UPDATE car_loan_emi SET emi_amount = 9000 WHERE emi_number = 6;"; 
    stmt.execute(sql);
  } catch (SQLException e) {
    e.printStackTrace();
  } 
}
We update the car loan EMI table for a specific month using the result set.
public static void updateRows(Connection connection) {
  try ( Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
              ResultSet.CONCUR_UPDATABLE); ) {
    String sql = "SELECT emi_number, emi_amount from car_loan_emi WHERE emi_number = 6;"; 
    ResultSet rs = stmt.executeQuery(sql);
    while ( rs.next() ) {
      int value = rs.getInt("emi_amount");    
      rs.updateInt("emi_amount", value - 1000);
      rs.updateRow();
    }
  } catch (SQLException e) {
    e.printStackTrace();
  } 
}
  • Share This:  
Newer Post Older Post Home

0 comments:

Post a Comment

Follow @StackStalk
Get new posts by email:
Powered by follow.it

Popular Posts

  • Avro Producer and Consumer with Python using Confluent Kafka
    In this article, we will understand Avro a popular data serialization format in streaming data applications and develop a simple Avro Produc...
  • Monitor Spring Boot App with Micrometer and Prometheus
    Modern distributed applications typically have multiple microservices working together. Ability to monitor and manage aspects like health, m...
  • Server-Sent Events with Spring WebFlux
    In this article we will review the concepts of server-sent events and work on an example using WebFlux. Before getting into this article it ...
  • Implement caching in a Spring Boot microservice using Redis
    In this article we will explore how to use Redis as a data cache for a Spring Boot microservice using PostgreSQL as the database. Idea is to...
  • Python FastAPI microservice with Okta and OPA
    Authentication (AuthN) and Authorization (AuthZ) is a common challenge when developing microservices. In this article, we will explore how t...
  • Spring Boot with Okta and OPA
    Authentication (AuthN) and Authorization (AuthZ) is a common challenge when developing microservices. In this article, we will explore how t...
  • Getting started with Kafka in Python
    This article will provide an overview of Kafka and how to get started with Kafka in Python with a simple example. What is Kafka? ...
  • Getting started in GraphQL with Spring Boot
    In this article we will explore basic concepts on GraphQL and look at how to develop a microservice in Spring Boot with GraphQL support. ...

Copyright © StackStalk