Java Connect to MySQL Database

Author:
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:
  1. Import JDBC packages 
  2. Load and register the JDBC driver // This step is not required in Java 6 and in JDBC 4.0
  3. Open a connection to the database.
  4. Create a statement object to perform a query.
  5. Execute the statement object and return a query resultset.
  6. Process the resultset.
  7. Close the resultset and statement objects. // This step is not required because we use a try-with-resource statement to auto-close the resources
  8. 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):
  1. Import JDBC packages 
  2. Open a connection to the database.
  3. Create a statement object to perform a query.
  4. Execute the statement object and return a query resultset.
  5. Process the resultset.

Key points

  1. 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.
  2. 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', '[email protected]', 'India', '123'),
(2, 'Deepa', '[email protected]', 'India', '123'),
(3, 'Tom', '[email protected]', '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,[email protected],US,secret
3,Pramod,[email protected],India,123
4,Deepa,[email protected],India,123
5,Tom,[email protected],India,123

Related posts

          Example to create a table using a Statement interface.
          Example to insert multiple records in a table using Statement interface.
          Example to update a record in a table using Statement interface.
          Example to retrieve records from a table using Statement interface.
          Example to delete a record from a table using a Statement interface.
          Example to insert records in a batch process via Statement interface.
          Example to update records in a batch process via Statement interface.
         Example to insert a record in a table using the PreparedStatement interface.
         Example to update a record in a table using the PreparedStatement interface.
          Example to retrieve records from a table using the PreparedStatement interface.
          Example to pass a list of values to IN clause using PreparedStatement interface.
          Example to insert records in a batch process via PreparedStatement interface.
          Example to update records in a batch process via PreparedStatement interface.

Comments

Post a Comment

Leave Comment