To select records using Prepared Statement in java we use
|
1 |
ResultSet executeQuery(String sql) throws SQLException |
“A ResultSet object maintains a cursor pointing to its current row of data. Initially the cursor is positioned before the first row. The next method moves the cursor to the next row, and because it returns false when there are no more rows in the ResultSet object, it can be used in a while loop to iterate through the result set.”
Steps to Execute Select SQL Query using Prepared Statement
1. Get a connection Object
|
1 |
Connection connection = getConnection(); |
2. Create a prepareStatement object
|
1 2 |
String selectSql = "SELECT * FROM employee_details WHERE id = ?"; PreparedStatement preparedStatement = connection.prepareStatement(selectSql) |
3. Add parameters (if required)
As you can see we have ? in the query. So thats a parameter and we need to pass values for that parameter.
|
1 |
preparedStatement.setInt(1, 1); |
The first parameter in the setInt method is the parameterIndex and the 2nd param is the parameter value for the query.
Please note: ParameterIndex starts from 1.
4. execute the query using executeQuery.
|
1 |
ResultSet rs = preparedStatement.executeQuery(); |
Full Example : Select records using Prepared Statement in Java
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 |
package com.kscodes.sampleproject.jdbc; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class PreparedSelectExample { public static String DRIVER_CLASS_NAME = "com.mysql.jdbc.Driver"; public static String CONNECTION_URL = "jdbc:mysql://localhost:3306/mysql"; public static String CONNECTION_USER = "kscodes"; public static String CONNECTION_PASSWORD = "kscodes"; public static void main(String[] args) { String selectSql = "SELECT * FROM employee_details WHERE id = ?"; try (Connection connection = getConnection(); PreparedStatement preparedStatement = connection.prepareStatement(selectSql)) { preparedStatement.setInt(1, 1); ResultSet rs = preparedStatement.executeQuery(); while (rs.next()) { int id = rs.getInt("id"); String firstName = rs.getString("first_name"); String lastName = rs.getString("last_name"); System.out.println("Employee Id :: " + id); System.out.println("Employee Name :: " + firstName + " " + lastName); } } catch (SQLException e) { System.out.println("An exception occured while Selecting records from Table. Exception is :: " + e); } } public static Connection getConnection() { try { Class.forName(DRIVER_CLASS_NAME); } catch (ClassNotFoundException e) { System.out.println("Error while registering JDBC driver"); return null; } Connection connection = null; try { connection = DriverManager.getConnection(CONNECTION_URL, CONNECTION_USER, CONNECTION_PASSWORD); } catch (SQLException e) { System.out.println("Failed to create Connection"); return null; } System.out.println("Connection created Successfully."); return connection; } } |
Output
|
1 2 3 |
Connection created Successfully. Employee Id :: 1 Employee Name :: Smith Yo |