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

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. Read all published posts by

Comments

avatar
3000
newest oldest most voted
Usama Ahmed Khan
Guest
Usama Ahmed Khan

i want to make it for android.. How?

JohnDoes2017
Guest
JohnDoes2017

Just use sqlite

Felipe Windmoller
Guest
Felipe Windmoller

Congratulations!
Very simple to understand.

Thanks for sharing

bhavani
Guest
bhavani

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

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

iam getting values two times as while retrieving from database

JJagadeesh
Guest
JJagadeesh

please help

Guest
Guest
Guest

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

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