JDBC Connection Interface

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

Connection interface Overview

A Connection is a session between 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 Examples

Let's demonstrate a few important Connection interface methods with examples.

Connect to Database and Create a Table Example

In below example, we have used Connection.createStatement() method to create Statement object.
Statement statement = connection.createStatement()
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.
    }
}
public void updateCoffeeSales(HashMap<String, Integer> salesForWeek)
    throws SQLException {

    PreparedStatement updateSales = null;
    PreparedStatement updateTotal = null;

    String updateString =
        "update " + dbName + ".COFFEES " +
        "set SALES = ? where COF_NAME = ?";
    try {
        con.setAutoCommit(false);
        updateSales = con.prepareStatement(updateString);

        for (Map.Entry<String, Integer> e : salesForWeek.entrySet()) {
            updateSales.setInt(1, e.getValue().intValue());
            updateSales.setString(2, e.getKey());
            updateSales.executeUpdate();
           con.commit();
        }
    } catch (SQLException e ) {
        if (con != null) {
            try {
                System.err.print("Transaction is being rolled back");
                con.rollback();
            } catch(SQLException excep) {
            }
        }
    } finally {
        if (updateSales != null) {
            updateSales.close();
        }
        if (updateTotal != null) {
            updateTotal.close();
        }
        con.setAutoCommit(true);
    }
}








Comments