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
$ git clone https://github.com/mkyong/java-jdbc.git
Congratulations!
Very simple to understand.
Thanks for sharing
i want to make it for android.. How?
Just use sqlite
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;
}
}
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.
iam getting values two times as while retrieving from database
please help
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
This is one of the most complete and comprehensive Java tutorials I have encountered so far.