Java JDBC

Table Of Contents
  1. Java Database Connectivity (JDBC)
  2. Key Concepts in JDBC
  3. Setting Up JDBC
  4. Establishing a Connection
  5. Executing SQL Queries
  6. Updating Data
  7. Handling Transactions
  8. Best Practices for Using JDBC
  9. Summary

Java Database Connectivity (JDBC)

Java Database Connectivity (JDBC) is an API that enables Java applications to interact with a wide range of databases. It provides methods for querying and updating data in a database. JDBC is part of the Java Standard Edition platform and is included in the java.sql package.

Key Concepts in JDBC

  1. DriverManager: Manages a list of database drivers and establishes a connection to the database.
  2. Connection: Represents a connection to a specific database.
  3. Statement: Used to execute SQL queries.
  4. PreparedStatement: Used for executing precompiled SQL queries with parameters.
  5. ResultSet: Represents the result of a query and provides methods to access the data.
  6. SQLException: Handles SQL errors.

Setting Up JDBC

1. Add the Database Driver

To connect to a database, you need the appropriate JDBC driver. For example, to connect to a MySQL database, you need the MySQL JDBC driver. Add the driver dependency to your project.

  • Maven Example:
Java
  <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.25</version>
  </dependency>

2. Load the Driver Class

Although this step is often not needed with modern JDBC drivers that use the service provider mechanism, it’s a good practice to ensure the driver class is loaded.

Java
try {
    Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
    e.printStackTrace();
}

Establishing a Connection

Use DriverManager to establish a connection to the database.

Java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class JDBCExample {
    public static void main(String[] args) {
        String jdbcURL = "jdbc:mysql://localhost:3306/mydatabase";
        String username = "root";
        String password = "password";

        try (Connection connection = DriverManager.getConnection(jdbcURL, username, password)) {
            System.out.println("Connected to the database");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Executing SQL Queries

Using Statement

Statement is used for executing simple SQL queries without parameters.

  • Example: Executing a Query:
Java
  import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.sql.Statement;

  public class JDBCStatementExample {
      public static void main(String[] args) {
          String jdbcURL = "jdbc:mysql://localhost:3306/mydatabase";
          String username = "root";
          String password = "password";

          try (Connection connection = DriverManager.getConnection(jdbcURL, username, password);
               Statement statement = connection.createStatement()) {

              String sql = "SELECT * FROM users";
              ResultSet resultSet = statement.executeQuery(sql);

              while (resultSet.next()) {
                  int id = resultSet.getInt("id");
                  String name = resultSet.getString("name");
                  String email = resultSet.getString("email");
                  System.out.println("ID: " + id + ", Name: " + name + ", Email: " + email);
              }
          } catch (SQLException e) {
              e.printStackTrace();
          }
      }
  }

Using PreparedStatement

PreparedStatement is used for executing precompiled SQL queries with parameters. It helps prevent SQL injection attacks.

  • Example: Executing a Query with Parameters:
Java
  import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.PreparedStatement;
  import java.sql.ResultSet;
  import java.sql.SQLException;

  public class JDBCPreparedStatementExample {
      public static void main(String[] args) {
          String jdbcURL = "jdbc:mysql://localhost:3306/mydatabase";
          String username = "root";
          String password = "password";

          try (Connection connection = DriverManager.getConnection(jdbcURL, username, password)) {
              String sql = "SELECT * FROM users WHERE id = ?";
              PreparedStatement preparedStatement = connection.prepareStatement(sql);
              preparedStatement.setInt(1, 1);
              ResultSet resultSet = preparedStatement.executeQuery();

              while (resultSet.next()) {
                  int id = resultSet.getInt("id");
                  String name = resultSet.getString("name");
                  String email = resultSet.getString("email");
                  System.out.println("ID: " + id + ", Name: " + name + ", Email: " + email);
              }
          } catch (SQLException e) {
              e.printStackTrace();
          }
      }
  }

Updating Data

Using Statement

  • Example: Inserting Data:
Java
  import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.SQLException;
  import java.sql.Statement;

  public class JDBCInsertExample {
      public static void main(String[] args) {
          String jdbcURL = "jdbc:mysql://localhost:3306/mydatabase";
          String username = "root";
          String password = "password";

          try (Connection connection = DriverManager.getConnection(jdbcURL, username, password);
               Statement statement = connection.createStatement()) {

              String sql = "INSERT INTO users (name, email) VALUES ('John Doe', 'john.doe@example.com')";
              int rowsInserted = statement.executeUpdate(sql);

              if (rowsInserted > 0) {
                  System.out.println("A new user was inserted successfully!");
              }
          } catch (SQLException e) {
              e.printStackTrace();
          }
      }
  }

Using PreparedStatement

  • Example: Updating Data:
Java
  import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.PreparedStatement;
  import java.sql.SQLException;

  public class JDBCUpdateExample {
      public static void main(String[] args) {
          String jdbcURL = "jdbc:mysql://localhost:3306/mydatabase";
          String username = "root";
          String password = "password";

          try (Connection connection = DriverManager.getConnection(jdbcURL, username, password)) {
              String sql = "UPDATE users SET email = ? WHERE id = ?";
              PreparedStatement preparedStatement = connection.prepareStatement(sql);
              preparedStatement.setString(1, "new.email@example.com");
              preparedStatement.setInt(2, 1);

              int rowsUpdated = preparedStatement.executeUpdate();
              if (rowsUpdated > 0) {
                  System.out.println("An existing user was updated successfully!");
              }
          } catch (SQLException e) {
              e.printStackTrace();
          }
      }
  }

Handling Transactions

Transactions ensure that a series of operations are executed as a single unit of work. JDBC provides methods to manage transactions.

  • Example: Managing Transactions:
Java
  import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.PreparedStatement;
  import java.sql.SQLException;

  public class JDBCTransactionExample {
      public static void main(String[] args) {
          String jdbcURL = "jdbc:mysql://localhost:3306/mydatabase";
          String username = "root";
          String password = "password";

          try (Connection connection = DriverManager.getConnection(jdbcURL, username, password)) {
              connection.setAutoCommit(false);

              String sqlInsert = "INSERT INTO users (name, email) VALUES (?, ?)";
              String sqlUpdate = "UPDATE accounts SET balance = balance - ? WHERE user_id = ?";

              try (PreparedStatement insertStmt = connection.prepareStatement(sqlInsert);
                   PreparedStatement updateStmt = connection.prepareStatement(sqlUpdate)) {

                  insertStmt.setString(1, "Alice");
                  insertStmt.setString(2, "alice@example.com");
                  insertStmt.executeUpdate();

                  updateStmt.setDouble(1, 100.00);
                  updateStmt.setInt(2, 1);
                  updateStmt.executeUpdate();

                  connection.commit();
                  System.out.println("Transaction committed successfully!");
              } catch (SQLException e) {
                  connection.rollback();
                  e.printStackTrace();
              }
          } catch (SQLException e) {
              e.printStackTrace();
          }
      }
  }

Best Practices for Using JDBC

  1. Close Resources: Always close Connection, Statement, and ResultSet objects to prevent resource leaks.
  2. Use Prepared Statements: Use PreparedStatement to prevent SQL injection and improve performance.
  3. Manage Transactions: Use transactions to ensure data consistency and handle rollback scenarios.
  4. Connection Pooling: Use connection pooling to manage database connections efficiently and improve performance.
  5. Exception Handling: Properly handle SQL exceptions to ensure your application can recover from errors gracefully.
  6. Batch Processing: Use batch processing to execute multiple SQL statements efficiently.

Summary

JDBC provides a powerful and flexible way to interact with databases from Java applications. By understanding the core concepts, setting up connections, executing queries, and managing transactions, you can build robust database-driven applications. Following best practices ensures that your application is efficient, secure, and maintainable.

Scroll to Top