Java JDBC DataSource Connection MySQL Example

In this tutorial, we will learn how to use JDBC to connect to a MySQL database using a DataSource. A DataSource is an alternative to the DriverManager for establishing a connection to a database. DataSources are typically preferred because they provide connection pooling and are more suitable for enterprise applications.

Introduction

What is JDBC?

Java Database Connectivity (JDBC) is an API that enables Java applications to interact with databases. It provides methods to query and update data in a database, as well as to retrieve metadata about the database itself.

What is DataSource?

A DataSource is a Java interface that provides a more flexible way to manage database connections. It allows for connection pooling, which can improve the performance of database operations by reusing existing connections instead of creating new ones.

Table of Contents

1. Setting Up the MySQL Database
2. Adding MySQL JDBC Driver to Your Project
3. Setting Up DataSource
4. Establishing a Database Connection
5. Closing the Connection
6. Conclusion

1. Setting Up the MySQL Database

First, create a database named jdbc_example and an employee table within it. Open your MySQL command line or any MySQL client and execute the following commands:

CREATE DATABASE jdbc_example;

USE jdbc_example;

CREATE TABLE employee (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(100),
    salary DECIMAL(10, 2)
);

INSERT INTO employee (name, department, salary) VALUES
('John Doe', 'HR', 50000.00),
('Jane Smith', 'Finance', 60000.00),
('Mike Johnson', 'IT', 75000.00),
('Emily Davis', 'Marketing', 65000.00);

2. Adding MySQL JDBC Driver to Your Project

To interact with a MySQL database, you need to add the MySQL JDBC driver to your project. If you are using Maven, add the following dependency to your pom.xml file:

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.30</version>
</dependency>

3. Setting Up DataSource

We will use the Apache DBCP (Database Connection Pooling) library to create a DataSource. Add the following dependency to your pom.xml file:

<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-dbcp2</artifactId>
    <version>2.9.0</version>
</dependency>

4. Establishing a Database Connection

We will create a DataSource and use it to establish a connection to the MySQL database.

Example: DataSource Connection

import org.apache.commons.dbcp2.BasicDataSource;

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

public class DataSourceExample {

    private static BasicDataSource dataSource;

    static {
        dataSource = new BasicDataSource();
        dataSource.setUrl("jdbc:mysql://localhost:3306/jdbc_example?useSSL=false");
        dataSource.setUsername("root");
        dataSource.setPassword("password");
        dataSource.setMinIdle(5);
        dataSource.setMaxIdle(10);
        dataSource.setMaxOpenPreparedStatements(100);
    }

    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }

    public static void main(String[] args) {
        try (Connection connection = getConnection();
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery("SELECT * FROM employee")) {

            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String department = resultSet.getString("department");
                double salary = resultSet.getDouble("salary");

                System.out.println("ID: " + id + ", Name: " + name + ", Department: " + department + ", Salary: " + salary);
            }

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

Explanation

1. Setting Up DataSource: We create a BasicDataSource object and configure it with the database connection details.
2. Getting a Connection: We define a method getConnection() that returns a connection from the DataSource.
3. Executing a Query: In the main method, we use the DataSource to get a connection, create a Statement, execute a query, and process the result set.
4. Using try-with-resources: We use try-with-resources to ensure that the connection, statement, and result set are closed automatically.

5. Closing the Connection

Using the try-with-resources statement ensures that the connection is closed automatically. This is important to free up database resources.

import java.sql.Connection;
import java.sql.SQLException;

public class CloseConnectionExample {
    public static void main(String[] args) {
        try (Connection connection = DataSourceExample.getConnection()) {
            if (connection != null) {
                System.out.println("Connected to the database!");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Conclusion

In this tutorial, we have covered the basics of using JDBC to connect to a MySQL database using a DataSource. We demonstrated how to set up a DataSource, establish a connection, execute a query, and close the connection using the try-with-resources statement. This guide should help you get started with JDBC and understand how to use DataSource for managing database connections effectively with MySQL.