Java Connect to MySQL Database

In this post, we will see how to connect Java application with MySQL database.

JDBC stands for Java Database Connectivity, which is a standard Java API for database-independent connectivity between the Java programming language and a wide range of databases.

Technologies used

We use below technologies in this post:

JDK - 1.8 or later
MySQL - 5.7.12
IDE - Eclipse Neon
JDBC - 4.2

Fundamental Steps in JDBC

The fundamental steps involved in the process of connecting to a database and executing a query consist of the following:

Import JDBC packages
Load and register the JDBC driver // This step is not required in Java 6 and in JDBC 4.0
Open a connection to the database.
Create a statement object to perform a query.
Execute the statement object and return a query resultset.
Process the resultset.
Close the resultset and statement objects. // This step is not required because we use a try-with-resource statement to auto-close the resources
Close the connection. // This step is not required because we use a try-with-resource statement to auto-close the resources

From the above steps, we actually require below five steps to connect a Java application to the database (example: MySQL):

Import JDBC packages
Open a connection to the database.
Create a statement object to perform a query.
Execute the statement object and return a query resultset.
Process the resultset.

Key points

From JDBC 4.0, we don't need to include 'Class.forName()' in our code to load JDBC driver. JDBC 4.0 drivers that are found in your classpath are automatically loaded.
We have used try-with-resources statements to automatically close JDBC resources.

Download MySQL JDBC Driver

Download and add MySQL JDBC driver to your project classpath. Here is the link: https://www.javaguides.net/2019/11/mysql-connector-java-maven-dependency.html

Database Setup

Create a database with the following command:

create database mysql_demo;

In this example, we will create a users table using SQL script:

create table users(
     id  int(3) primary key,
     name varchar(20),
     email varchar(20),
     country varchar(20),
     password varchar(20)
  );

Here is the insert SQL script:

INSERT INTO Users VALUES (1, 'Pramod', 'pramod@gmail.com', 'India', '123'),
(2, 'Deepa', 'deepa@gmail.com', 'India', '123'),
(3, 'Tom', 'top@gmail.com', 'India', '123');

Java program to connect to MySQL database and retrieve records from database using JDBC API

Let's put all the steps together and here is the complete example with output:

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

/**
 * Select Statement JDBC Example
 * @author Ramesh Fadatare
 *
 */
public class SelectStatementExample {
    private static final String QUERY = "select id,name,email,country,password from Users";

    public static void main(String[] args) {

        // using try-with-resources to avoid closing resources (boilerplate code)

        // Step 1: Establishing a Connection
        try (Connection connection = DriverManager
            .getConnection("jdbc:mysql://localhost:3306/mysql_database?useSSL=false", "root", "root");

            // Step 2:Create a statement using connection object
            Statement stmt = connection.createStatement();

            // Step 3: Execute the query or update query
            ResultSet rs = stmt.executeQuery(QUERY)) {

            // Step 4: Process the ResultSet object.
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String email = rs.getString("email");
                String country = rs.getString("country");
                String password = rs.getString("password");
                System.out.println(id + "," + name + "," + email + "," + country + "," + password);
            }
        } catch (SQLException e) {
            printSQLException(e);
        }
        // Step 4: try-with-resource statement will auto close the connection.
    }

    public static void printSQLException(SQLException ex) {
        for (Throwable e: ex) {
            if (e instanceof SQLException) {
                e.printStackTrace(System.err);
                System.err.println("SQLState: " + ((SQLException) e).getSQLState());
                System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
                System.err.println("Message: " + e.getMessage());
                Throwable t = ex.getCause();
                while (t != null) {
                    System.out.println("Cause: " + t);
                    t = t.getCause();
                }
            }
        }
    }
}

Output:

1,Ram,tony@gmail.com,US,secret
3,Pramod,pramod@gmail.com,India,123
4,Deepa,deepa@gmail.com,India,123
5,Tom,top@gmail.com,India,123

JDBC Statement Create a Table Example

Example to create a table using a Statement interface.

JDBC Statement - Insert Multiple Records Example

Example to insert multiple records in a table using Statement interface.

JDBC Statement - Update a Record Example

Example to update a record in a table using Statement interface.

JDBC Statement Select Records Example

Example to retrieve records from a table using Statement interface.

JDBC Statement - Delete a Record Example

Example to delete a record from a table using a Statement interface.

JDBC Statement - Batch Insert Example

Example to insert records in a batch process via Statement interface.

JDBC Statement - Batch Update Example

Example to update records in a batch process via Statement interface.

JDBC PreparedStatement - Insert a Record Example

Example to insert a record in a table using the PreparedStatement interface.

JDBC PreparedStatement - Update a Record Example

Example to update a record in a table using the PreparedStatement interface.

JDBC PreparedStatement - Select Records Example

Example to retrieve records from a table using the PreparedStatement interface.

JDBC PreparedStatement with a list of values in an IN clause

Example to pass a list of values to IN clause using PreparedStatement interface.

JDBC PreparedStatement - Batch Insert Example

Example to insert records in a batch process via PreparedStatement interface.

JDBC PreparedStatement - Batch Update Example

Example to update records in a batch process via PreparedStatement interface.

Java Guides

Search This Blog