How can we Connect to a Database in Java?

Connect to a Database in Java

To connect to a database in Java, the Java Database Connectivity (JDBC) API is the standard framework. JDBC provides a database-independent way for Java programs to interact with various databases. The underlying architecture typically consists of two layers: the JDBC API, which handles the application-to-JDBC Manager connection, and the JDBC Driver API, which manages the JDBC Manager-to-Driver connection.

Introduction to JDBC

Database-independent communication between the Java programming language and a wide range of databases is made possible via JDBC, or Java Database communication, a standard Java API. As an essential part of Sun Microsystems’ Java platform, it was created by the company and made available in 1995. A crucial technology for creating online apps, enterprise solutions, and other software applications that need database connectivity is JDBC.

APIs for all of the common database usage operations, including connecting to a database, generating SQL statements, running queries, and viewing or editing the records that are returned, are included in the JDBC library. In essence, JDBC is a specification that offers a whole suite of interfaces that enable portable access to a database underneath. This implies that Java applications can have code that is independent of databases, providing features that are comparable to those of ODBC (Open Database Connectivity).

Applications written in the Java programming language operate on the Java platform, which is made up of an Application Programming Interface (API) and a Java Virtual Machine (VM). High-level classes for networking, security, database access, and GUI development are among the essential features of the Java programming language that are made available by the Java SE (Standard Edition) API.

JDBC API Components

In general, the JDBC API architecture consists of two layers: the JDBC Driver API, which facilitates the JDBC Manager-to-Driver connection, and the JDBC API itself, which offers the application-to-JDBC Manager connection. A driver manager and database-specific drivers are used by the JDBC API to enable transparent connectivity to a variety of databases. In addition to supporting several drivers running concurrently connected to different databases, the driver manager makes sure the right driver is utilised.

The JDBC API offers several important classes and interfaces, such as:

DriverManager: A list of database drivers is managed by the DriverManager class. Using a communication subprotocol, it provides the appropriate database driver in response to connection requests from the Java application. Establishing a database connection will be done by the first driver to recognise a certain subprotocol.
Driver: The database server connectivity is managed by this interface. Rarely do developers work directly with Driver objects; instead, they are managed by DriverManager objects, which abstract away the specifics of dealing with Driver objects.
Connection: This interface covers every way to get in touch with a database. The communication context is represented by a Connection object, which is where all database communication takes place.
Statement: SQL statements are submitted to the database by objects built via this interface. Additionally, derived interfaces can run stored procedures and take parameters.
ResultSet: Data obtained from a database following the execution of a SQL query using Statement objects is stored in ResultSet objects. It facilitates navigation across the recovered data by acting as an iterator.
SQLException: Any mistakes that arise within a database application are handled by the SQLException class. It offers ways to access more error information, such as getErrorCode(), getMessage(), and getSQLState().

Types of JDBC Drivers

Java runs on a wide range of hardware platforms and operating systems, which causes significant variation in JDBC driver implementations. Sun, which is now Oracle, divided these into four categories:

Type 1: JDBC-ODBC Bridge Driver
- This kind uses a JDBC bridge to connect to the ODBC drivers that are set up on every client computer.
- The target database is represented by a Data Source Name (DSN), which must be configured on your system.
- Although helpful when Java initially came out when the majority of databases only supported ODBC, this driver type is now only advised for experimental use or in situations where no other option is available. One example is JDK 1.2’s JDBC-ODBC bridge.
Type 2: JDBC-Native API Driver
- Native C/C++ API calls unique to the database are created from JDBC API calls.
- Like Type 1, these drivers are usually supplied by database suppliers and the vendor-specific driver must be installed on every client computer.
- Because the native API is database-specific, it must be modified when the database is changed. Despite being largely out of date, type 2 drivers can boost speed by removing ODBC overhead. An illustration would be the Oracle Call Interface (OCI) driver.
- When a particular database does not have Type 3 or Type 4 drivers accessible, Type 2 drivers can be helpful.
Type 3: JDBC-Net pure Java Driver
- This driver employs a three-tier approach to database access.
- JDBC clients use standard network sockets to communicate with a middleware application server.
- The application server then translates the socket information into the call format required by the DBMS and forwards it to the database server.
- It is highly flexible, requiring no code installation on the client, and a single driver can access multiple databases. The application server acts as a JDBC “proxy,” possibly using a Type 1, 2, or 4 driver to communicate with the database.
- This is the preferred driver if your Java application needs to access multiple types of databases simultaneously.
Type 4: 100% pure Java Driver
- This driver connects directly to the vendor’s database and is entirely Java based.
- Because it provides direct connectivity without the need for middleware or native libraries, it is very effective and portable.

Connecting to a Database and Executing Queries

There are typically six essential phases involved in creating a JDBC application:

Import the packages: In order to import the required JDBC classes, import java.sql.*.
Register the JDBC driver: Set up the driver to establish a connection with the database.
Open a connection: Use DriverManager.getConnection() to create a Connection object, representing the physical database connection.
Execute a query: Use a Statement or PreparedStatement object to construct and submit SQL statements.
- For SELECT queries:
- For UPDATE, INSERT, or DELETE statements:
Extract data from result set: utilise the ResultSet.getXXX() methods to extract data by column name if you are fetching data (from SELECT queries).
Clean up the environment: Prevent resource leaks by explicitly closing all database resources (ResultSet, Statement, and Connection). Usually done in a finally block, this makes sure that resources are closed even in the event of an exception.

Here’s a consolidated sample code demonstrating these steps:

import java.sql.*;
public class FirstExample {
    // JDBC driver name and database URL
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost/EMP";
    // Database credentials
    static final String USER = "username";
    static final String PASS = "password";
    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        try {
            // STEP 2: Register JDBC driver
            Class.forName("com.mysql.jdbc.Driver");
            // STEP 3: Open a connection
            System.out.println("Connecting to database...");
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
            // STEP 4: Execute a query
            System.out.println("Creating statement...");
            stmt = conn.createStatement();
            String sql;
            sql = "SELECT id, first, last, age FROM Employees";
            ResultSet rs = stmt.executeQuery(sql);
            // STEP 5: Extract data from result set
            while (rs.next()) {
                // Retrieve by column name
                int id = rs.getInt("id");
                int age = rs.getInt("age");
                String first = rs.getString("first");
                String last = rs.getString("last");
                // Display values
                System.out.print("ID: " + id);
                System.out.print(", Age: " + age);
                System.out.print(", First: " + first);
                System.out.println(", Last: " + last);
            }
            // STEP 6: Clean-up environment
            rs.close();
            stmt.close();
            conn.close();
        } catch (SQLException se) {
            // Handle errors for JDBC
            se.printStackTrace();
        } catch (Exception e) {
            // Handle errors for Class.forName
            e.printStackTrace();
        } finally {
            // finally block used to close resources
            try {
                if (stmt != null)
                    stmt.close();
            } catch (SQLException se2) {
                // nothing we can do
            }
            try {
                if (conn != null)
                    conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            } // end finally try
        } // end try
        System.out.println("Goodbye!");
    } // end main
} // end FirstExample

To compile this example, you would use: C:\>javac FirstExample.java

Then, to run it: C:\>java FirstExample

The expected output would be similar to:

Connecting to database...
Creating statement...
ID: 100, Age: 18, First: Zara, Last: Ali
ID: 101, Age: 25, First: Mahnaz, Last: Fatma
ID: 102, Age: 30, First: Zaid, Last: Khan
ID: 103, Age: 28, First: Sumit, Last: Mittal
Goodbye!

For Java applications, JDBC essentially serves as a universal adaptor that enables communication with a variety of databases, independent of the databases’ particular manufacture or model. Similar to how a universal travel adapter allows your electronics to be plugged into any power outlet, JDBC offers a standardised set of guidelines and resources that let your Java code connect to many databases.

Page Content

Tutorials