JDBC PreparedStatement example – Select list of the records

Here’s an example to show you how to select records from table via JDBC PreparedStatement, and display the records via a ResultSet object. To issue a select query, calls the PreparedStatement.executeQuery() method like this :

String selectSQL = "SELECT USER_ID, USERNAME FROM DBUSER WHERE USER_ID = ?";
PreparedStatement preparedStatement = dbConnection.prepareStatement(selectSQL);
preparedStatement.setInt(1, 1001);
ResultSet rs = preparedStatement.executeQuery(selectSQL );
while (rs.next()) {
	String userid = rs.getString("USER_ID");
	String username = rs.getString("USERNAME");	
}

Full example…

package com.mkyong.jdbc;
 
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
 
public class JDBCPreparedStatementSelectExample {
 
	private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
	private static final String DB_CONNECTION = "jdbc:oracle:thin:@localhost:1521:MKYONG";
	private static final String DB_USER = "user";
	private static final String DB_PASSWORD = "password";
 
	public static void main(String[] argv) {
 
		try {
 
			selectRecordsFromTable();
 
		} catch (SQLException e) {
 
			System.out.println(e.getMessage());
 
		}
 
	}
 
	private static void selectRecordsFromTable() throws SQLException {
 
		Connection dbConnection = null;
		PreparedStatement preparedStatement = null;
 
		String selectSQL = "SELECT USER_ID, USERNAME FROM DBUSER WHERE USER_ID = ?";
 
		try {
			dbConnection = getDBConnection();
			preparedStatement = dbConnection.prepareStatement(selectSQL);
			preparedStatement.setInt(1, 1001);
 
			// execute select SQL stetement
			ResultSet rs = preparedStatement.executeQuery();
 
			while (rs.next()) {
 
				String userid = rs.getString("USER_ID");
				String username = rs.getString("USERNAME");
 
				System.out.println("userid : " + userid);
				System.out.println("username : " + username);
 
			}
 
		} catch (SQLException e) {
 
			System.out.println(e.getMessage());
 
		} finally {
 
			if (preparedStatement != null) {
				preparedStatement.close();
			}
 
			if (dbConnection != null) {
				dbConnection.close();
			}
 
		}
 
	}
 
	private static Connection getDBConnection() {
 
		Connection dbConnection = null;
 
		try {
 
			Class.forName(DB_DRIVER);
 
		} catch (ClassNotFoundException e) {
 
			System.out.println(e.getMessage());
 
		}
 
		try {
 
			dbConnection = DriverManager.getConnection(
                             DB_CONNECTION, DB_USER,DB_PASSWORD);
			return dbConnection;
 
		} catch (SQLException e) {
 
			System.out.println(e.getMessage());
 
		}
 
		return dbConnection;
 
	}
 
}

Result

List of the records with “user_id = 1001″ are retrieved from table “DBUSER” and displayed.

Tags :

About the Author

mkyong
Founder of Mkyong.com and HostingCompass.com, love Java and open source stuff. Follow him on Twitter, or befriend him on Facebook or Google Plus. If you like my tutorials, consider make a donation to these charities.

Comments

  • Raj

    Create a java program that inserts numbers in table3 (numeral) starting with 100 and ending with 300 and also randomly inserts letters into table3(letter).
    Use a prepared statement for the insert.

  • Juan Carlos

    But what if we want to set a string? PreparedStatement.setString(1, Variable)?

    I’m having this issue, the problem is that MySQL takes the query with the variable around apostrophes. I mean: “SELECT * FROM Table Where TableVarChar = ‘VariableValue'”…
    MySQL won’t accept those apostrophes. Anyone else having this issue? Hope it helps someone in the future, as well to me :D

  • Amit Baxi

    In the example shown above don’t we need to close resultSet in finally block.

  • http://www.outfastsource.com Eric

    Your executive summary incorrectly uses executeQuery passing the SQL in again, whereas the long version correctly uses executeQuery with no parameters.

    – Appreciate this service!

  • sri

    Pls send me code for read data from excel sheet from a folder, after words inserted into specified table. send me full project code using jdbc

    waiting reply