Connecting Java with a Database (MySQL/PostgreSQL)

Introduction

Java provides excellent support for database connectivity through JDBC (Java Database Connectivity). With JDBC, Java can interact with a variety of databases, including MySQL and PostgreSQL. JDBC allows Java applications to connect to a database, execute SQL queries, and retrieve results.

In this tutorial, we will cover how to:

  • Set up a connection between Java and MySQL/PostgreSQL.
  • Execute SQL queries.
  • Retrieve and process data from the database.

Step 1: Set Up the Database

Before connecting Java to a database, you need to have a MySQL or PostgreSQL database set up. Here's how to do it:

  • Install MySQL/PostgreSQL if not already installed.
  • Create a new database, for example, testdb.
  • Create a table, for example, employees, with some sample data: 
         CREATE TABLE employees (
      id INT PRIMARY KEY,
      name VARCHAR(50),
      department VARCHAR(50)
     );
     INSERT INTO employees VALUES (1, 'John Doe', 'HR');
     INSERT INTO employees VALUES (2, 'Jane Smith', 'Finance');

Step 2: Add JDBC Driver

Java communicates with databases via JDBC, and you need to include the JDBC driver for your database. For MySQL, you need the mysql-connector-java library, and for PostgreSQL, you need the postgresql

Step 3: Establish a Connection to the Database

To connect Java to a MySQL or PostgreSQL database, you use the DriverManager.getConnection() method. Below is an example of how to do this.

Example 1: Connecting to MySQL Database

  import java.sql.*;
      
  public class MySQLConnection {
       public static void main(String[] args) {
          try {
               // Load the MySQL JDBC driver
              Class.forName("com.mysql.cj.jdbc.Driver");
      
              // Establish a connection to the MySQL database
               Connection connection = DriverManager.getConnection(
                  "jdbc:mysql://localhost:3306/testdb", "root", "password");
      
              System.out.println("Connected to MySQL database!");
      
               // Close the connection
               connection.close();
          } catch (ClassNotFoundException | SQLException e) {
               e.printStackTrace();
           }
       }
   }

In this example:

  • The Class.forName("com.mysql.cj.jdbc.Driver") method loads the MySQL JDBC driver.
  • The DriverManager.getConnection() method is used to establish the connection to the MySQL database by specifying the database URL, username, and password.
  • Once the connection is successful, a message is printed.

Example 2: Connecting to PostgreSQL Database

  import java.sql.*;
      
  public class PostgreSQLConnection {
      public static void main(String[] args) {
          try {
              // Load the PostgreSQL JDBC driver
              Class.forName("org.postgresql.Driver");
      
              // Establish a connection to the PostgreSQL database
              Connection connection = DriverManager.getConnection(
                  "jdbc:postgresql://localhost:5432/testdb", "postgres", "password");
      
               System.out.println("Connected to PostgreSQL database!");
      
              // Close the connection
              connection.close();
          } catch (ClassNotFoundException | SQLException e) {
              e.printStackTrace();
           }
       }
   }

In this example:

  • The Class.forName("org.postgresql.Driver") method loads the PostgreSQL JDBC driver.
  • The DriverManager.getConnection() method establishes the connection to the PostgreSQL database.

Step 4: Executing SQL Queries

Once the connection is established, you can execute SQL queries using the Statement or PreparedStatement objects. Below is an example that demonstrates how to execute a SELECT query to retrieve data from the database.

Example 3: Executing a SELECT Query

  import java.sql.*;
      
   public class DatabaseQueryExample {
      public static void main(String[] args) {
          try {
              // Load the MySQL JDBC driver
              Class.forName("com.mysql.cj.jdbc.Driver");
      
               // Establish a connection
               Connection connection = DriverManager.getConnection(
                  "jdbc:mysql://localhost:3306/testdb", "root", "password");
      
              // Create a statement
               Statement statement = connection.createStatement();
      
              // Execute a SELECT query
              String query = "SELECT * FROM employees";
              ResultSet resultSet = statement.executeQuery(query);
      
              // Process the result
              while (resultSet.next()) {
                   int id = resultSet.getInt("id");
                   String name = resultSet.getString("name");
                   String department = resultSet.getString("department");
      
                   System.out.println("ID: " + id + ", Name: " + name + ", Department: " + department);
               }
      
               // Close the result set, statement, and connection
               resultSet.close();
               statement.close();
               connection.close();
          } catch (ClassNotFoundException | SQLException e) {
               e.printStackTrace();
           }
       }
   }

In this example:

  • We use the createStatement() method to create a Statement object.
  • We execute the SELECT query with the executeQuery() method.
  • The ResultSet object is used to iterate over the results and retrieve data.

Step 5: Handling Prepared Statements

A PreparedStatement is used when executing SQL queries that include user input, as it helps prevent SQL injection attacks.

Example 4: Using PreparedStatement to Insert Data

  import java.sql.*;
      
  public class PreparedStatementExample {
      public static void main(String[] args) {
          try {
              // Load the MySQL JDBC driver
              Class.forName("com.mysql.cj.jdbc.Driver");
      
              // Establish a connection
               Connection connection = DriverManager.getConnection(
                   "jdbc:mysql://localhost:3306/testdb", "root", "password");
      
              // Create a PreparedStatement for inserting data
               String insertQuery = "INSERT INTO employees (id, name, department) VALUES (?, ?, ?)";
              PreparedStatement preparedStatement = connection.prepareStatement(insertQuery);
      
              // Set parameters for the PreparedStatement
              preparedStatement.setInt(1, 3);
              preparedStatement.setString(2, "Mark Johnson");
              preparedStatement.setString(3, "Engineering");
      
              // Execute the query
              int rowsAffected = preparedStatement.executeUpdate();
              System.out.println("Rows affected: " + rowsAffected);
      
              // Close the prepared statement and connection
              preparedStatement.close();
              connection.close();
          } catch (ClassNotFoundException | SQLException e) {
              e.printStackTrace();
           }
      }
   }

In this example:

  • We use a PreparedStatement to insert data into the employees table.
  • We use the setXXX() methods to set parameters for the query.
  • We execute the query using the executeUpdate() method.

Step 6: Closing the Connection

After executing your queries and processing the results, always close the Connection, Statement, and ResultSet objects to release database resources.

Conclusion

In this tutorial, we covered the steps required to connect Java to a MySQL/PostgreSQL database using JDBC. We discussed how to:

  • Establish a connection with the database.
  • Execute SQL queries to retrieve and manipulate data.
  • Use prepared statements to insert data securely.
JDBC provides a powerful and flexible way to interact with relational databases in Java applications. By following this tutorial, you can build database-driven applications that can store, retrieve, and process data efficiently.




Advertisement