Java Programming Techniques and APIs

Understanding JDBC in JAVA

Board Infinity
Board Infinity
Understanding JDBC in JAVA

    Understand Serialization and Deserialization in Java

    A quick guide to Stack Class in Java

    A Quick Guide to String Builder in Java

    Understanding Garbage Collection in Java

    What are Functional Interfaces in Java?

    How to Handle Exceptions in Java?

    A practical guide on Synchronization in Java

    A Guide to Association, Aggregation, and Composition in Java

    How to Read files in Java?

    A Quick Guide to Linked Hashmap in Java

    Understanding JDBC in JAVA

    What is Static Class in JAVA?

    How to Read a File in JAVA?

    Checked vs Unchecked Exceptions in Java

    Priority Queue In Java

    Java Server Page: Introduction

    Volatile Keyword in Java

    Java Array and ArrayList: Differences

    Regex in Java

    Packages in Java

    Thread Priority in Java: What is it & How to set?

    Java Bytecode

    Hashtable in Java

    Lambda Expression in Java

    Java Stream

    LinkedList in Java

    Sorting A Hashmap In Java

    Finally Block In Java

    Trim in Java

Introduction

Java code may interface to relational databases like Postgres, Oracle, MySQL, H2 Database, and others thanks to the Java JDBC API (Java Database Connectivity). Relational databases may be accessed and updated using the JDBC API. Stored procedures can also be called, and metadata about the database can be obtained. All Java programs that want to utilize JDBC have access to it thanks to the Java JDBC API, which is a component of the fundamental Java SE SDK.

JDBC is not DB Specific

The Java JDBC API formalizes how to access a database, run queries against it, explore the results of those searches, perform modifications in the database, invoke stored procedures, and retrieve metadata from the database. It is "standardized," in the sense that the code is consistent across various database products. Therefore, switching to a different database will be much simpler if your project ever requires it.

JDBC Is Not Independent of SQL

The SQL that is supplied to the database is not standardized by JDBC. As a JDBC API user, you are the one that writes the SQL. Because each database's SQL dialect will differ slightly from the others, your SQL must be completely independent of all databases.

JDBC is Not For Non-Relational Databases

Relational databases, or databases you connect with using normal SQL, are the focus of the Java JDBC API. MongoDB, Cassandra, Dynamo, and other non-relational databases are not intended uses of the JDBC API. Such databases are accessible from Java programs, but you should check the drivers they offer for Java.

Here is a list of well-known relational databases that are free source and for sale that includes JDBC drivers so you may use them from Java:

  1. H2Database
  2. MariaDB
  3. PostgreSQL
  4. Derby
  5. Oracle
  6. Microsoft
  7. SQL Server

JDBC Core Concepts

The Java JDBC API's fundamental ideas are as follows:

  1. Connection
  2. Statement for the JDBC Driver
  3. PreparedStatement
  4. Callable Statement
  5. Batch Updates for the ResultSet
  6. Transactions DatabaseMetaData

These ideas are each given a more thorough explanation in their respective lessons. However, I will just provide a brief outline of how these essential elements are employed in the sections that follow. Once you have a foundational knowledge of JDBC, you can next dig into the additional lessons.

JDBC Example

The following JDBC code demonstrates how to load the JDBC driver, establish a database connection, generate a Statement, run a query, and iterate the returning ResultSet:

import java.sql.*;

public class JdbcExample {

    public static void main(String[] args) throws ClassNotFoundException {
        Class.forName("com.mysql.Driver");

        String url      = "jdbc:mysql:~/test"; 

        String user     = "sa";
        String password = "";

        try(Connection connection = DriverManager.getConnection(url, user, password)) {
            try(Statement statement = connection.createStatement()){
                String sql = "select * from people";
                try(ResultSet result = statement.executeQuery(sql)){
                    while(result.next()) {
                        String name = result.getString("name");
                        long   age  = result.getLong  ("age");
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
}

write your code here: Coding Playground

If you don't understand everything, don't worry. The sections below provide explanations for each component of this example.

Load JDBC Driver

Class.forName("org.h2.Driver");

The JDBC driver class name for the specified JDBC driver is contained in the Java String parameter passed to the Class forName() method. You must identify the appropriate class name for your JDBC driver. Since each database often has its own JDBC driver, you will need to seek up the name of the JDBC driver class (if you are using pre-Java 6). There are several types of JDBC drivers. Each variety has distinct benefits and drawbacks. You must check the types of drivers that your database provider provides.

Open Database Connection

You must first establish a DB connection before you can interact with it. Here is a brief illustration of how to open a JDBC connection, which is covered in more detail in my lesson on JDBC connections:

String url = "jdbc:h2:~/test";   //database specific url.
String user = "sa";
String password = "";

Connection connection = DriverManager.getConnection(url, user, password);

When you open a database connection, you usually either update the database by adding new records or updating those that already exist, or you query the database, which means you read records from it. In the parts that follow, both activities will be briefly discussed.

Create Statement

You must build a JDBC Statement or JDBC PreparedStatement in order to update or query the database, depending on your needs. An illustration of generating a JDBC Statement instance is provided here:

Statement statement = connection.createStatement();

Update the Database

Updates to the database can be made after creating a JDBC Statement or JDBC PreparedStatement instance. Here is an illustration of how to use a Statement instance to update the database:

Statement statement = connection.createStatement();
String sql = "update people set name='John' where id=123";
int rowsAffected = statement.executeUpdate(sql);

Query the Database

Additionally, a JDBC Statement or PreparedStatement object can be used to query the database. When you query a database, you receive a JDBC ResultSet in return, which you can use to access the query's outcome. Here is an illustration of how to use JDBC to run a query against a database:

Statement statement = connection.createStatement();
String sql = "select * from people";
ResultSet result = statement.executeQuery(sql);
while(result.next()) {
    String name = result.getString("name");
    long   age  = result.getLong  ("age");
}

Close Database Connection

You must once again end the connection after using the JDBC database connection. Both inside your application and the database server, a JDBC connection might use a significant amount of resources. It is crucial to again shut off the database connection after use. A JDBC connection's close() function is used to terminate it. Using the following code, a JDBC connection may be closed:

connection.close();