Main Tutorials

JDBC PreparedStatement – Select list of rows

A JDBC PreparedStatement example to select a list of rows from the database.

RowSelect.java

package com.mkyong.jdbc.preparestatement.row;

import com.mkyong.jdbc.model.Employee;

import java.math.BigDecimal;
import java.sql.*;

public class RowSelect {

    private static final String SQL_SELECT = "SELECT * FROM EMPLOYEE";

    public static void main(String[] args) {

        try (Connection conn = DriverManager.getConnection(
                "jdbc:postgresql://127.0.0.1:5432/test", "postgres", "password");
             PreparedStatement preparedStatement = conn.prepareStatement(SQL_SELECT)) {

            ResultSet resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {

                long id = resultSet.getLong("ID");
                String name = resultSet.getString("NAME");
                BigDecimal salary = resultSet.getBigDecimal("SALARY");
                Timestamp createdDate = resultSet.getTimestamp("CREATED_DATE");

                Employee obj = new Employee();
                obj.setId(id);
                obj.setName(name);
                obj.setSalary(salary);
                // Timestamp -> LocalDateTime
                obj.setCreatedDate(createdDate.toLocalDateTime());

                System.out.println(obj);
            }

        } catch (SQLException e) {
            System.err.format("SQL State: %s\n%s", e.getSQLState(), e.getMessage());
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

}
Employee.java

package com.mkyong.jdbc.model;

import java.math.BigDecimal;
import java.time.LocalDateTime;

public class Employee {

    private Long id;
    private String name;
    private BigDecimal salary;
    private LocalDateTime createdDate;

    //...
}

Table definition.


CREATE TABLE EMPLOYEE
(
    ID serial,
    NAME varchar(100) NOT NULL,
    SALARY numeric(15, 2) NOT NULL,
    CREATED_DATE timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
    PRIMARY KEY (ID)
);

P.S Tested with PostgreSQL 11 and Java 8

pom.xml

	<dependency>
		<groupId>org.postgresql</groupId>
		<artifactId>postgresql</artifactId>
		<version>42.2.5</version>
	</dependency>

Download Source Code

References

About Author

author image
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter. If you like my tutorials, consider make a donation to these charities.

Comments

Subscribe
Notify of
21 Comments
Most Voted
Newest Oldest
Inline Feedbacks
View all comments
Abhijeet Ashok Muneshwar
10 years ago

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.

Beri
9 years ago

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

user
8 years ago
Reply to  Beri

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 ninguno en esa comunidad autónoma el más mínimo sentido del ridículo?

DerechaHDLGP
8 years ago
Reply to  user

Meas fuera del tiesto naZionalista de pacotilla.

Beri
8 years ago
Reply to  user

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 😛

DerechaHDLGP
9 years ago
Reply to  Beri

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

Beri
9 years ago
Reply to  DerechaHDLGP

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

Tharindu
10 years ago

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

ramo
8 years ago

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
6 years ago
Reply to  ramo

I have the same problem..

ionel condor
6 years ago

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

viru
8 years ago

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());

}

Pratik Ranjan
4 years ago
Reply to  viru

rs=preparedStatement.executeQuery();

This is the correct syntax

Raj
10 years ago

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
10 years ago

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 😀

chinga
10 years ago
Reply to  Juan Carlos

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
11 years ago

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

Eric
11 years ago

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
12 years ago

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
10 years ago
Reply to  sri

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