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.

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

}

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!

Amit Baxi
Guest
Amit Baxi

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

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 😀

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.

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

}

ionel condor
Guest
ionel condor

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

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