Java Database Connectivity (JDBC) in 5 Steps

Java Database Connectivity (JDBC) is a Java API that enables Java programs to interact with databases. Using JDBC, you can execute SQL queries, update records, and retrieve data from a database.

Here are the 5 basic steps to establish JDBC connectivity and interact with a database:

Step 1: Load the JDBC Driver

The first step is to load the database-specific JDBC driver. For most modern Java versions, the driver automatically loads when you include the JDBC library in your project.

Code Example:

try {
    Class.forName("com.mysql.cj.jdbc.Driver"); // For MySQL
    System.out.println("Driver Loaded Successfully!");
} catch (ClassNotFoundException e) {
    e.printStackTrace();
}

• Explanation:
  • Replace com.mysql.cj.jdbc.Driver with the driver class name for your database (e.g., Oracle, PostgreSQL).
  • For JDBC 4.0 and above, this step is optional because the driver auto-registers.

Step 2: Establish a Connection

Use the DriverManager class to establish a connection to your database by providing the database URL, username, and password.

Code Example:

import java.sql.Connection;
import java.sql.DriverManager;

Connection connection = null;

try {
    String url = "jdbc:mysql://localhost:3306/mydatabase"; // Replace "mydatabase" with your database name
    String user = "root"; // Replace with your database username
    String password = "password"; // Replace with your database password

    connection = DriverManager.getConnection(url, user, password);
    System.out.println("Connection Established Successfully!");
} catch (Exception e) {
    e.printStackTrace();
}

• Database URL Format:
  • MySQL: jdbc:mysql://hostname:port/database_name
  • Oracle: jdbc:oracle:thin:@hostname:port:database_name
  • PostgreSQL: jdbc:postgresql://hostname:port/database_name

Step 3: Create a Statement

After establishing a connection, you need a Statement object to execute SQL queries.

Code Example:

import java.sql.Statement;

Statement statement = null;

try {
    statement = connection.createStatement();
    System.out.println("Statement Created Successfully!");
} catch (Exception e) {
    e.printStackTrace();
}

• Alternative: You can also use PreparedStatement or CallableStatement for parameterized or stored procedure queries.

Step 4: Execute SQL Queries

Use the Statement object to execute SQL queries. There are three main methods:

1. executeQuery(String sql): For SELECT queries (returns a ResultSet).
2. executeUpdate(String sql): For INSERT, UPDATE, DELETE queries (returns an integer for rows affected).
3. execute(String sql): For both types of queries (returns a boolean).

Code Example (SELECT Query):

import java.sql.ResultSet;

try {
    String query = "SELECT * FROM users"; // Replace "users" with your table name
    ResultSet resultSet = statement.executeQuery(query);

    while (resultSet.next()) {
        System.out.println("User ID: " + resultSet.getInt("id"));
        System.out.println("User Name: " + resultSet.getString("name"));
    }
} catch (Exception e) {
    e.printStackTrace();
}

Code Example (INSERT Query):

try {
    String query = "INSERT INTO users (id, name) VALUES (1, 'John Doe')";
    int rowsAffected = statement.executeUpdate(query);
    System.out.println("Rows Inserted: " + rowsAffected);
} catch (Exception e) {
    e.printStackTrace();
}

Step 5: Close the Connection

Once the operations are complete, close the Connection, Statement, and ResultSet objects to release resources.

Code Example:

try {
    if (statement != null) statement.close();
    if (connection != null) connection.close();
    System.out.println("Connection Closed Successfully!");
} catch (Exception e) {
    e.printStackTrace();
}

Complete Example

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

public class JDBCExample {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;

        try {
            // Step 1: Load the JDBC Driver
            Class.forName("com.mysql.cj.jdbc.Driver");
            System.out.println("Driver Loaded Successfully!");

            // Step 2: Establish a Connection
            String url = "jdbc:mysql://localhost:3306/mydatabase";
            String user = "root";
            String password = "password";
            connection = DriverManager.getConnection(url, user, password);
            System.out.println("Connection Established Successfully!");

            // Step 3: Create a Statement
            statement = connection.createStatement();

            // Step 4: Execute SQL Query
            String query = "SELECT * FROM users";
            ResultSet resultSet = statement.executeQuery(query);

            while (resultSet.next()) {
                System.out.println("User ID: " + resultSet.getInt("id"));
                System.out.println("User Name: " + resultSet.getString("name"));
            }

            // Step 5: Close the Connection
            resultSet.close();
            statement.close();
            connection.close();
            System.out.println("Connection Closed Successfully!");

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Additional Notes

• JDBC Driver Dependency: Add the corresponding JDBC driver to your project. For Maven:

  <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.33</version> <!-- Adjust version as needed -->
  </dependency>
  

• Error Handling: Always handle SQL exceptions properly to debug errors.