Menu

What steps to connect to the database

What steps to connect to the database

Connecting to a database in Java involves several key steps using the JDBC (Java Database Connectivity) API. These steps include loading the JDBC driver, establishing a connection, creating a statement, executing SQL queries, and closing the resources.

database

Steps to Connect to a Database in Java

  • 1. Load the JDBC Driver: Load the JDBC driver class dynamically at runtime using Class.forName(). This step is crucial as it registers the driver with the DriverManager.
  • 2. Establish a Connection: Use DriverManager.getConnection() method to create a Connection object. This method requires a Db URL, username, and password.
  • 3. Create a Statement: Use the Connection object to create a Statement or PreparedStatement object. These objects are used to execute SQL queries.
  • 4. Execute a Query: Execute SQL queries using the Statement or PreparedStatement object. This can be a query to retrieve data (SELECT) or modify data (INSERT, UPDATE, DELETE).
  • 5. Process the Results: If executing a query that retrieves data, process the results using a ResultSet object.
  • 6. Close the Resources: Close the ResultSet, Statement, and Connection objects to free up resources.

Example

Let’s illustrate these steps with a complete example where we connect to a MySQL DB, execute a query to retrieve data, and process the results.

Database Table Creation
Before running the Java code, create a table in your database.

sql
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50),
    password VARCHAR(50),
    email VARCHAR(100)
);


  Java Program to Connect to DB
java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DatabaseConnectionExample {
    public static void main(String[] args) {
        // JDBC URL, username, and password of MySQL server
        String jdbcURL = "jdbc:mysql://localhost:3306/your_db";
        String username = "root";
        String password = "password";

        // JDBC variables for opening and managing connection
        Connection connection = null;

        try {
            // Load the JDBC driver
            Class.forName("com.mysql.cj.jdbc.Driver");

            // Establish a connection
            connection = DriverManager.getConnection(jdbcURL, username, password);
            System.out.println("Connected to the database!");

            // Create a SQL query
            String sql = "SELECT * FROM users";

            // Create a Statement
            PreparedStatement statement = connection.prepareStatement(sql);

            // Execute the query
            ResultSet resultSet = statement.executeQuery();

            // Process the results
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String userName = resultSet.getString("username");
                String userPassword = resultSet.getString("password");
                String userEmail = resultSet.getString("email");

                System.out.println("ID: " + id);
                System.out.println("Username: " + userName);
                System.out.println("Password: " + userPassword);
                System.out.println("Email: " + userEmail);
                System.out.println("-----------");
            }

            // Close the resources
            resultSet.close();
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            try {
                if (connection != null) {
                    connection.close();
                    System.out.println("Disconnected from the database.");
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }
}

Explanation of the Example

  • Loading the JDBC Driver:
  • Class.forName(“com.mysql.cj.jdbc.Driver”); loads the MySQL JDBC driver.
  • Establishing a Connection:
  • DriverManager.getConnection(jdbcURL, username, password); creates a connection to the DB using the provided JDBC URL, username, and password.
  • Creating a Statement:
  • connection.prepareStatement(sql); creates a PreparedStatement for the SQL query.
  • Executing the Query:
  • statement.executeQuery(); executes the SQL query and returns a ResultSet object containing the results.
  • Processing the Results:
  • The ResultSet object is used to iterate over the results and print them to the console.
  • Closing the Resources:
  • The ResultSet, PreparedStatement, and Connection objects are closed to free up resources.

Homepage

Readmore