Java JDBC PreparedStatement - Select Records Example

---

< Previous Next >

JDBC Tutorial

---

In this article, we will learn how to select records from a database table using JDBC PreparedStatement interface. 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.

PreparedStatement provides executeQuery() method which executes the SQL query in this PreparedStatement object and returns the ResultSet object generated by the query.

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

Technologies used

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

Steps to process Select 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

From JDBC 4.0, we don't need to include 'Class.forName()' in our code, to load JDBC driver. When the method 'getConnection' is called, the 'DriverManager' will automatically load the suitable driver among the JDBC drivers that were loaded at initialization and those loaded explicitly using the same class loader as the current application.

Any JDBC 4.0 drivers that are found in your classpath are automatically loaded. (However, you must manually load any drivers prior to JDBC 4.0 with the method Class.forName.)

JDBC PreparedStatement Select Records Example

Here we have a users table in a database and we will query a list of users from database table by id.

package com.javaguides.jdbc.preparestatement.examples;

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

/**
 * Select PreparedStatement JDBC Example
 * 
 * @author Ramesh Fadatare
 *
 */
public class SelectPStatementExample {
    private static final String QUERY = "select id,name,email,country,password from Users where id =?";

    public static void main(String[] args) {

        // using try-with-resources to avoid closing resources (boiler plate 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
            PreparedStatement preparedStatement = connection.prepareStatement(QUERY);) {
            preparedStatement.setInt(1, 1);
            System.out.println(preparedStatement);
            // Step 3: Execute the query or update query
            ResultSet rs = preparedStatement.executeQuery();

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

---

< Previous Next >

JDBC Tutorial

---