Apr 52016

ADM

How to select records using JDBC PreparedStatement

  • April 5 2016
  • ADM

 

 

How to select records using JDBC PreparedStatement - jdbc

 

In this tutorial will try to get some data from a database table using JDBC PreparedStatement. For more resources about JDBC please follow the links from references section.

Example

To be easier for this tutorial I will copy all necessary code from the tutorials listed on reference section.

package com.admfactory.db;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

public class JDBCSelectRecord {

    private static Connection connect() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            return DriverManager.getConnection("jdbc:mysql://localhost:3306/admfactory.com", "root", "");
        } catch (Exception e) {
            System.out.println("Failed to connect to DB.");
            e.printStackTrace();
            return null;
        }
    }

    private static void selectDataPreparedStatement(String id) throws Exception {
        PreparedStatement statement = null;
        try {
            String query = "SELECT * FROM `user` WHERE id = ?";
            Connection connection = connect();
            statement = connection.prepareStatement(query);

            statement.setString(1, id);

            ResultSet rs = statement.executeQuery();
            while (rs.next()) {

                long userId = rs.getLong("id");
                String name = rs.getString("name");
                String email = rs.getString("email");
                String password = rs.getString("password");
                String type = rs.getString("type");
                Date dob = rs.getDate("dob");

                System.out.println(
                        "[" + userId + ", " + name + ", " + email + ", " + password + ", " + type + ", " + dob + "]");

            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (statement != null) {
                statement.close();
            }
        }
    }

    public static void main(String[] args) {
        try {
            System.out.println("Select records.");
            /** Get the connection. */
            Connection connection = connect();

            if (connection != null) {
                /** select some data. */
                selectDataPreparedStatement("1");
                
                System.out.println("Records selected successfully.");

                /** Close the connection. */
                connection.close();
            }
        } catch (Exception e) {
            System.out.println("Something went wrong.");
        }
    }
}

Note that the index for the statement object starts from 1.

Output

Select records.
[1, John Doe, [email protected], pass, REGULAR, 2016-03-24]
Records selected successfully.

References