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 the Author

author image
mkyong
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

avatar
9 Comment threads
11 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
17 Comment authors
Ishwaryaionel condorramoviruuser Recent comment authors
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