Java Database Connectivity

Author:
Java Database Connectivity or JDBC API provides industry-standard and database-independent connectivity between the Java applications and relational database servers(relational databases, spreadsheets, and flat files).
Using the JDBC API, we can access virtually any data source, from relational databases to spreadsheets and flat files. JDBC technology also provides a common base on which tools and alternate interfaces can be built.
JDBC helps you to write Java applications that manage these three programming activities:
  1. Connect to a data source, like a database
  2. Send queries and update statements to the database
  3. Retrieve and process the results received from the database in answer to your query
The JDBC API is comprised of two packages:
We automatically get both packages when you download the Java Platform Standard Edition (Java SE) 8.

JDBC Driver Types

To use the JDBC API with a particular database management system(MySQL, Oracle etc), we need a JDBC technology-based driver to mediate between JDBC technology and the database (oracle.jdbc.driver.OracleDriver). Depending on various factors, a driver might be written purely in the Java programming language or in a mixture of the Java programming language and Java Native Interface (JNI) native methods.
There are 4 different types of JDBC drivers:
  1. Type 1: JDBC-ODBC bridge driver
  2. Type 2: Java + Native code driver
  3. Type 3: All Java + Middleware translation driver
  4. Type 4: All Java driver.
If you are using Java DB ( Apache Derby database), it already comes with a JDBC driver. If you are using MySQL, install the latest version of Connector/J.
Let's see some examples to create a table, insert data into an existing table in the database using Java Database Connectivity.

JDBC Statement Create a Table Example

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 a complete Java JDBC program to create a users table in a database:
package com.javaguides.jdbc.statement.examples;

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

/**
 * Create Statement JDBC Example
 * @author Ramesh Fadatare
 *
 */
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) {

            // print SQL exception information
            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();
                }
            }
        }
    }
}

Executing Insert Statement - Insert a Record Example

In this example, we use a PreparedStatement interface to insert users record. As we know PreparedStatement interface improves performance like SQL statement is precompiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times.
package com.javaguides.jdbc.preparestatement.examples;

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

/**
 * Insert PrepareStatement JDBC Example
 * 
 * @author Ramesh Fadatare
 *
 */
public class InsertPStatementExample {
    private static final String INSERT_USERS_SQL = "INSERT INTO users" +
        "  (id, name, email, country, password) VALUES " +
        " (?, ?, ?, ?, ?);";

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

    public void insertRecord() throws SQLException {
        System.out.println(INSERT_USERS_SQL);
        // 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
            PreparedStatement preparedStatement = connection.prepareStatement(INSERT_USERS_SQL)) {
            preparedStatement.setInt(1, 1);
            preparedStatement.setString(2, "Tony");
            preparedStatement.setString(3, "tony@gmail.com");
            preparedStatement.setString(4, "US");
            preparedStatement.setString(5, "secret");

            System.out.println(preparedStatement);
            // Step 3: Execute the query or update query
            preparedStatement.executeUpdate();
        } catch (SQLException e) {

            // print SQL exception information
            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();
                }
            }
        }
    }
}

Java Database Connectivity Tutorial and Examples

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.
         Overview of JDBC API, java.sql and javax.sql package.
         JDBC API quick getting started tutorial- CREATE, INSERT, UPDATE, SELECT and DELETE.
          Example to create a table using 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 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 4.2 API - PreparedStatement

         Example to insert a record in a table using PreparedStatement interface.
         Example to update a record in a table using PreparedStatement interface.
          Example to retrieve records from a table using 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.

JDBC 4.2 API - CallableStatement

          Create and use Stored Procedure examples using CallableStatement interface.

JDBC 4.2 API - Transactions

          How to use JDBC transactions with examples.

JDBC 4.2 API - SQLExceptions Handling

          In this article, we will learn how to handle SQLExceptions while working with JDBC.

JDBC 4.2 API - java.sql Package

          In this article, we will learn commonly used methods of Connection interface with examples.
         In this article, we will learn commonly used methods of Statement interface with examples.
          In this article, we will learn commonly used methods of PreparedStatement interface.
          In this article, we will learn commonly used methods of CallableStatement interface.
          In this article, we will learn commonly used methods of ResultSet interface with examples.
          In this article, we will learn commonly used methods of ResultSetMetaData interface.
          In this article, we will learn commonly used methods of DatabaseMetadata interface.
          In this article, we will learn commonly used methods of DriverManager class with examples.

JDBC 4.2 API - Batch Processing

         Example to update records in a batch process using Statement and PreparedStatement interfaces.
         Example to insert records in a batch process using Statement and PreparedStatement interfaces.
          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 records in a batch process via PreparedStatement interface.
          Example to update records in a batch process via PreparedStatement interface.

Servlet + JSP + JDBC + MySQL Examples

JSP + JDBC

  • JSP + JDBC + MySQL Example -  In this article, we will build a simple Employee Registration module using JSP, JDBC, and MySQL database.

JDBC 4.2 API FAQ

         Example to dynamically insert rows using StringBuilder and PreparedStatement placeholders ?.
         This article provides how to retrieve the column names of a table using the getMetaData() method.
          Example of how to use DataSource to connect with MySQL database.

Comments

Post a Comment

Leave Comment