In this post we will learn How to retrieve data\row from JDBC resultset in java.
Project structure in eclipse:
SQL Query to create employee_table and insert few rows in MySQL (jdbcdb schema)
(Note:Make sure you have created employee_table and inserted few records in jdbcdb database schema)
DB.sql
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE TABLE `employee_table` ( `employee_id` int(11) NOT NULL auto_increment, `employee_name` varchar(60) NOT NULL, `email` varchar(45) NOT NULL, `salary` double default NULL, `date_of_joining` datetime default NULL, `bonus` decimal(10,0) default NULL, PRIMARY KEY (`employee_id`) ) ; INSERT INTO `employee_table` (`employee_name`,`email`,`salary`,`date_of_joining`,`bonus`) VALUES ('Martin','[email protected]',71000,'2017-05-17 00:00:00',400); INSERT INTO `employee_table` (`employee_name`,`email`,`salary`,`date_of_joining`,`bonus`) VALUES ('Paul','[email protected]',91000,'2017-05-18 00:00:00',800); INSERT INTO `employee_table` (`employee_name`,`email`,`salary`,`date_of_joining`,`bonus`) VALUES ('Sam','[email protected]',76000,'2017-05-16 00:00:00',300); INSERT INTO `employee_table` (`employee_name`,`email`,`salary`,`date_of_joining`,`bonus`) VALUES ('Paul','[email protected]',60000,'2017-05-17 00:00:00',400); |
After running above db script in your MySQL jdbcdb schema employee_table will have following content.
DBUtil.java class which is responsible to connect with MySQL database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
package com.infotech.util; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; public class DBUtil { private static final String DB_DRIVER_CLASS="com.mysql.jdbc.Driver"; private static final String DB_USERNAME="root"; private static final String DB_PASSWORD="root"; private static final String DB_URL ="jdbc:mysql://localhost:3306/jdbcdb"; private static Connection connection = null; static{ try { Class.forName(DB_DRIVER_CLASS); connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD); } catch (ClassNotFoundException | SQLException e) { e.printStackTrace(); } } public static Connection getConnection(){ return connection; } } |
Model class Employee .java
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
package com.infotech.model; import java.math.BigDecimal; import java.util.Date; public class Employee { private int employee_id; private String employeeName; private String email; private Double salary; private Date doj; private BigDecimal bonus; public int getEmployee_id() { return employee_id; } public void setEmployee_id(int employee_id) { this.employee_id = employee_id; } public String getEmployeeName() { return employeeName; } public void setEmployeeName(String employeeName) { this.employeeName = employeeName; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } public Double getSalary() { return salary; } public void setSalary(Double salary) { this.salary = salary; } public Date getDoj() { return doj; } public void setDoj(Date doj) { this.doj = doj; } public BigDecimal getBonus() { return bonus; } public void setBonus(BigDecimal bonus) { this.bonus = bonus; } @Override public String toString() { return "Employee [employee_id=" + employee_id + ", employeeName=" + employeeName + ", email=" + email + ", salary=" + salary + ", doj=" + doj + ", bonus=" + bonus + "]"; } } |
Now let’s run our ClientTest.java class to read records from database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 |
package com.infotech.client; import java.math.BigDecimal; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.Date; import java.util.List; import com.infotech.model.Employee; import com.infotech.util.DBUtil; /** * @author KK JavaTutorials *JDBC program to retrieve data from database using JDBC resultset */ public class ClientTest { public static void main(String[] args) throws SQLException { List<Employee> empList = fetchAllEmployeesInfo(); for (Employee employee : empList) { System.out.println(employee); } } //Reading all rows from database using JDBC ResultSet private static List<Employee> fetchAllEmployeesInfo() { List<Employee> empList = new ArrayList<>(); String SQL = "SELECT *FROM employee_table"; try (Connection connection = DBUtil.getConnection(); PreparedStatement ps = connection.prepareStatement(SQL)) { ResultSet rs = ps.executeQuery(); while (rs.next()) { //Here we are retrieving column value by column index(column index starts from 1) but you can read by column name as well int employeeId = rs.getInt(1); String empName = rs.getString(2); String email = rs.getString(3); Double salary = rs.getDouble(4); Date doj = rs.getDate(5); BigDecimal bonus = rs.getBigDecimal(6); Employee employee = new Employee(); employee.setEmployee_id(employeeId); employee.setEmployeeName(empName); employee.setEmail(email); employee.setSalary(salary); employee.setDoj(doj); employee.setBonus(bonus); empList.add(employee); } } catch (Exception e) { e.printStackTrace(); } return empList; } } |
After running ClientTest.java program you will look below output on eclipse console:
|
1 2 3 4 |
Employee [employee_id=1, employeeName=Martin, email=martin.cs2017@yahoo.com, salary=71000.0, doj=2017-05-17, bonus=400] Employee [employee_id=2, employeeName=Paul, email=paul.cs2017@gmail.com, salary=91000.0, doj=2017-05-18, bonus=800] Employee [employee_id=3, employeeName=Sam, email=sam.cs2014@gmail.com, salary=76000.0, doj=2017-05-16, bonus=300] Employee [employee_id=4, employeeName=Paul, email=paul.cs2009@yahoo.com, salary=60000.0, doj=2017-05-17, bonus=400] |
That’s all about How to retrieve data from JDBC resultset in java
If you have any feedback or suggestion please feel free to drop in blow comment box.