Main Tutorials

JDBC Statement – Select list of rows

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

RowSelect.java

package com.mkyong.jdbc.statement.row;

import com.mkyong.jdbc.model.Employee;

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

public class RowSelect {

    public static void main(String[] args) {

        String sql = "SELECT * FROM EMPLOYEE";

        try (Connection conn = DriverManager.getConnection(
                "jdbc:postgresql://127.0.0.1:5432/test", "postgres", "password");
             Statement statement = conn.createStatement()) {

            ResultSet resultSet = statement.executeQuery(sql);
            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
9 Comments
Most Voted
Newest Oldest
Inline Feedbacks
View all comments
Usama Ahmed Khan
8 years ago

i want to make it for android.. How?

JohnDoes2017
6 years ago

Just use sqlite

Felipe Windmoller
6 years ago

Congratulations!
Very simple to understand.

Thanks for sharing

bhavani
9 years ago

this is my code

package bhanuJsf;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.faces.bean.ManagedBean;
import javax.faces.bean.RequestScoped;
@ManagedBean(name=”customerAction” , eager=true)
@RequestScoped
public class SelectCustomer{
private Connection con=null;
private Statement statement = null;
private ResultSet resultSet = null;
private List custInfoAll = new ArrayList();
//connect to DB and get customer list
public List getCustInfoAll() throws SQLException, ClassNotFoundException
{
Class.forName(“com.mysql.jdbc.Driver”);
con = DriverManager.getConnection(“jdbc:mysql://localhost:3306/jsfdb”,”root”,”root”);
statement = con.createStatement();
String sql=”select distinct * from customer”;
resultSet = statement.executeQuery(sql);
CusomerInfo x = new CusomerInfo();
while(resultSet.next())
{
x.setCustomerID(resultSet.getString(1));
x.setName(resultSet.getString(2));
x.setAddress(resultSet.getString(3));
x.setPhoneno(resultSet.getString(4));
System.out.println(“CUSTOMER ID —>”+x.getCustomerID());
custInfoAll.add(x);

System.out.println(“TOTAL SIZE —–>”+custInfoAll);
x = new CusomerInfo();
}

return custInfoAll;

}

}

bhavani
9 years ago

hello sir.i am getting duplicate values when we are retriving the values from database and we dont have duplicate values in data base. how to reslove it plz give me quick rply thanks in advance.

JJagadeesh
9 years ago

iam getting values two times as while retrieving from database

JJagadeesh
9 years ago
Reply to  JJagadeesh

please help

Guest
10 years ago

can you help me please ….. I need to write the following statement in jdbc

function subjectFormula return Char is

v_emp_no number;

v_emp_name varchar2(100);

begin

begin

select emp_no

into v_emp_no

from emp

where id_emp = :id_emp;

exception

when no_data_found then

v_emp_no := null;

end;

—-

select emp_name

into v_emp_name

from emp22

where emp_no = v_emp_no;

return v_emp_name;

exception

when no_data_found then

return null;

end;

how write equivalent this in jdbc ……..thanks

felix
10 years ago

This is one of the most complete and comprehensive Java tutorials I have encountered so far.