Java JDBC Statement Interface Example with MySQL Database

Author:
In this article, we will discuss commonly used JDBC Statement interface methods with examples.

Statement interface Overview

Statement interface object used for executing a static SQL statement and returning the results it produces.
Statement interface extends Wrapper, AutoCloseable interfaces.
By default, only one ResultSet object per Statement object can be open at the same time. Therefore, if the reading of one ResultSet object is interleaved with the reading of another, each must have been generated by different Statement objects. All execution methods in the Statement interface implicitly close a current ResultSet object of the statement if an open one exists.

Statement interface Class Diagram

Statement interface Commonly used methods

The important methods of Statement interface are as follows:
  • boolean execute(String sql) - Executes the given SQL statement, which may return multiple results.
  • int[] executeBatch() - Submits a batch of commands to the database for execution and if all commands execute successfully, returns an array of update counts.
  • ResultSet executeQuery(String sql) - Executes the given SQL statement, which returns a single ResultSet object.
  • int executeUpdate(String sql) - Executes the given SQL statement, which may be an INSERT, UPDATE, or DELETE statement or an SQL statement that returns nothing, such as an SQL DDL statement.

Statement interface Examples

Let's see a few examples of using Statement interface methods.

JDBC Statement Create a Table Example

In this example, we will use the execute(SQL) method to create a users table in a MySQL database.
Steps to process create SQL statement with JDBC:
  1. Establishing a connection
  2. Create a statement
  3. Execute the query
  4. Using try-with-resources Statements to Automatically Close JDBC Resources
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();
                }
            }
        }
    }
}

JDBC Statement Select Records Example

In this example, we will use the executeQuery() method to select records from MySQL database table using the JDBC Statement interface.
package com.javaguides.jdbc.statement.examples;

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 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

Post a Comment

Leave Comment