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.

About the Author

author image
mkyong
Founder of Mkyong.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

Leave a Reply

avatar
newest oldest most voted
Abhijeet Ashok Muneshwar
Guest
Abhijeet Ashok Muneshwar

I think, in short example, the below line of code gives error:
ResultSet rs = preparedStatement.executeQuery(selectSQL );
Here we don’t need to pass “selectSQL” as parameter to executeQuery.

This error can be removed in following way:
ResultSet rs = preparedStatement.executeQuery();

Please correct me if I am wrong.

Tharindu
Guest
Tharindu

yes… I think you are right. when I used that way, I got the exception java.sql.SQLException: Use of the executeQuery(String) method is not supported on this type of statement. but in the example, coding is right

Beri
Guest
Beri

you’re right, it happened to me this same morning by mistake :P

DerechaHDLGP
Guest
DerechaHDLGP

No m’enterao de ná, pero te tengo como rojo y como tal te trato.
Saludos.

Beri
Guest
Beri

jajaja da gusto tener camaradas como tu de follower! Un saludo!!

user
Guest
user
Esto es un foro de programación, no un lugar para mostrar ideologías políticas con banderas no-oficiales, Beri. ¿O es que acaso programas también en Catalán? Apuesto a que Arturo ya os ha lavado el cerebro diciendo que es tan importante el Catalán (primera lengua conocida de la humanidad) que con el paso de unos meses se impondrá hasta como lenguaje de programación. Leía hoy en el periódico que ha ido a EEUU y a dar una charla a la Universidad de Columbia y tras la misma va y afirma que allí están a favor del “referendum”… En serio, ¿no tenéis… Read more »
Beri
Guest
Beri

Disculpa bonico pero, eres tú el que postea asuntos no
relacionados con la programación.

Por otro lado se ve de lejos que no conoces a muchos catalanes, ya que das de hecho que todos somos Arturistas, cuándo este señor es un mamarracho capitalista como cualquier otro de los que tenéis del Ebro para abajo.

Y lo del avatar, es el que uso en Disqus. Y como veo que hay gente a quien le escuece la vista tansolo de verlo, pues como que se va a quedar unos añitos ahí puesto :P

DerechaHDLGP
Guest
DerechaHDLGP

Meas fuera del tiesto naZionalista de pacotilla.

ramo
Guest
ramo

pls is there any help i have this code put my ResultSet which i call it rst is not work why ?
prepareStatement pst =null;
ResultSet rst=null;

Connection con=null;
this is my code

String sql = “SELECT * FROM user WHERE username007 = ‘” + txtusername007.getText() + “‘”

+ ” AND password = ‘” + txtpassword.getText() + “‘ AND specialization ”

+ ” = ‘”+cspcilization.getSelectedItem() + “‘ “;

try {

pst = con.prepareStatement(sql);

rst= pst.executeQuery();

if( rst.next()) {

if ( cspcilization.getSelectedItem().equals(“Admin”)){

this.setVisible(false);

home h =new home();

h.setVisible(true);

h.m2.setEnabled(false);

h.m3.setEnabled(false);

h.m4.setEnabled(false);

h.m5.setEnabled(false);

h.m6.setEnabled(false);

h.m7.setEnabled(false);

h.m8.setEnabled(false);

}

Ishwarya
Guest
Ishwarya

I have the same problem..

ionel condor
Guest
ionel condor

just run executeQuery() and no executeQuery(String) when you are doing a SELECT query with params.

viru
Guest
viru

i hav excute following code but it throw exception that executeQuery() cannot take arguments on a PreparedStatement

try{

String insertSQL=”select txtPassword from new where txtUserName=’?'”;

PreparedStatement preparedStatement=connection.prepareStatement(insertSQL);

rs=preparedStatement.executeQuery(insertSQL);

System.out.print(rs);

}

catch(Exception e1)

{

System.out.print(e1.getMessage());

}

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

chinga
Guest
chinga

I think MySQL does NOT accept the normal single quotes [i.e. ‘ ] but accepts the left tilted quotes which are found to the left of Number 1 on keyboard (or below the “ESC” button) [ i.e. ` ]

Amit Baxi
Guest
Amit Baxi

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

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

Tim
Guest
Tim

Seriously ?! Maybe you need your car washed, too ? :)
@mkyong thanks for this tutorial