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 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
7 Comment threads
2 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
7 Comment authors
JohnDoes2017Felipe WindmollerUsama Ahmed KhanbhavaniJJagadeesh Recent comment authors
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 =… Read more »

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.