Java JDBC Connection Interface Example with MySQL Database

In this article, we will discuss how to create a Connection and how to use the Connection interface to connect to a specific database.

In this example, we are going to use the MySQL database.

Technologies used

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

Connection interface Overview

A Connection is a session between a Java application and a specific database. SQL statements are executed and results are returned within the context of a connection.
A Connection object's database is able to provide information describing its tables, its supported SQL grammar, its stored procedures, the capabilities of this connection, and so on. This information is obtained with the getMetaData method.

Connection interface Class Diagram

The below class diagram shows a list of methods/APIs the Connection interface provides.

Connection Interface Commonly used Methods

  • public Statement createStatement() - creates a statement object that can be used to execute SQL queries.
  • public Statement createStatement(int resultSetType,int resultSetConcurrency) - Creates a Statement object that will generate ResultSet objects with the given type and concurrency.
  • public void setAutoCommit(boolean status) - is used to set the commit s status. By default it is true.
  • public void commit() - saves the changes made since the previous commit/rollback permanent.
  • public void rollback()  - Drops all changes made since the previous commit/rollback.
  • public void close() - closes the connection and releases JDBC resources immediately.

Connection Interface Example - Establish Database Connection and Create Table

In below example, we first create a connection object using DriverManager.getConnction() API. We will use Connection.createStatement() method to create Statement object:
Here is the complete example to create a table in MySQL database using connection object:
public class CreateStatementExample {

    private static final String createTableSQL = "create table Users1(\r\n" + "  id  int(3) primary key,\r\n" +
        "  name varchar(20),\r\n" + "  email varchar(20),\r\n" + "  country varchar(20),\r\n" +
        "  password varchar(20)\r\n" + "  );";

    public static void main(String[] argv) throws SQLException {
        CreateStatementExample createTableExample = new CreateStatementExample();
        createTableExample.createTable();
    }

    public void createTable() throws SQLException {

        System.out.println(createTableSQL);
        // 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 statement = connection.createStatement();) {

            // Step 3: Execute the query or update query
            statement.execute(createTableSQL);
        } catch (SQLException e) {

        }

        // Step 4: try-with-resource statement will auto close the connection.
    }
}

Java Transaction Management Example using Connection Methods

In JDBC API, the Connection interface provides the setAutoCommit() , commit() and rollback() methods to perform transaction management.
Below Java JDBC program demonstrates the usage of Connection interface provided setAutoCommit() , commit() and rollback() methods to perform transaction management:
package com.javaguides.jdbc.batch;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * JDBC Transaction Management Example
 * @author Ramesh Fadatare
 *
 */
public class JDBCTransactionExample {
    private static final String INSERT_USERS_SQL = "INSERT INTO users " +
        "  (id, name, email, country, password) VALUES " + " (?, ?, ?, ?, ?);";
    private static final String UPDATE_USERS_SQL = "update users set name = ? where id = ?;";

    private static final String jdbcUrl = "jdbc:mysql://localhost:3306/mysql_database?useSSL=false";
    private static final String username = "root";
    private static final String password = "root";

    public static void main(String[] args) {

        try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password);) {

            // STEP 1 - Disable auto commit mode
            conn.setAutoCommit(false);

            try (PreparedStatement insertStmt = conn.prepareStatement(INSERT_USERS_SQL); PreparedStatement updateStmt = conn.prepareStatement(UPDATE_USERS_SQL);) {

                // Create insert statement
                insertStmt.setInt(1, 200);
                insertStmt.setString(2, "Tony");
                insertStmt.setString(3, "tony123@gmail.com");
                insertStmt.setString(4, "US");
                insertStmt.setString(5, "secret");
                insertStmt.executeUpdate();

                // Create update statement
                updateStmt.setString(1, "Ram");
                updateStmt.setInt(2, 200);
                updateStmt.executeUpdate();

                // STEP 2 - Commit insert and update statement
                conn.commit();
                System.out.println("Transaction is commited successfully.");
            } catch (SQLException e) {
                printSQLException(e);
                if (conn != null) {
                    try {
                        // STEP 3 - Roll back transaction
                        System.out.println("Transaction is being rolled back.");
                        conn.rollback();
                    } catch (Exception ex) {
                        ex.printStackTrace();
                    }
                }
            }
        } catch (SQLException e) {
            printSQLException(e);
        }
    }

    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:
Transaction is commited successfully.

JDBC Statement Interface Examples

          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.

JDBC PreparedStatement Interface Examples

         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