Reading data from database using JDBC Statement

In previous post CRUD(Create,Read,Update and Delete) Operation using JDBC Statement we learnt how to perform insert,read,update and delete operation using JDBC statement.In this post is about Reading data from database using JDBC Statement

Project structure in eclipse:

SQL Query to create employee_table in MySQL (jdbcdb schema)
(Note:Make sure you created employee_table in jdbcdb database schema)

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`)
) ;

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`)

) ;

Suppose we have records in our database as below:

DBUtil.java class which is responsible to connect with MySQL database.
this util class uses getConnection() method of java.sql.DriverManager class
public static Connection getConnection(String url,String user, String password) throws SQLException
This method takes three parameters database URL,username and password.
Here database username =”root”,password = “root” what password had supplied during MYSQL database installation time and finally database URL =“jdbc:mysql://localhost:3306/jdbcdb” Where jdbc is the API, mysql is the database, localhost is the server name on which mysql database server is running, we may also use IP address instead machine name, 3306 is the port number and jdbcdb(Make you you have created this schema in MySQL database) is the database name. You may use any database name, in that case, You need to replace the jdbcdb with your database name.

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;
	}
}

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;

}

Now let’s run our ClientTest.java class to read an employee record or all employee records from database.

package com.infotech.client;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import com.infotech.util.DBUtil;

public class ClientTest {

	public static void main(String[] args) {
		 getEmployeeById();
		//getAllEmployeesInfo();
	}

	private static void getAllEmployeesInfo() {

		try (Connection connection = DBUtil.getConnection(); Statement st = connection.createStatement();) {

			String SQL = "SELECT *FROM employee_table";
			ResultSet rs = st.executeQuery(SQL);
			while (rs.next()) {
				int empId = rs.getInt("employee_id");
				String eName = rs.getString("employee_name");
				String email = rs.getString("email");
				Double salary = rs.getDouble("salary");
				BigDecimal bonus = rs.getBigDecimal("bonus");

				System.out.println(empId + "\t" + eName + "\t" + salary + "\t" + email + "\t" + bonus);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private static void getEmployeeById() {

		try (Connection connection = DBUtil.getConnection(); Statement st = connection.createStatement();) {

			String SQL = "SELECT *FROM employee_table WHERE employee_id=3";
			ResultSet rs = st.executeQuery(SQL);
			if (rs.next()) {
				int empId = rs.getInt("employee_id");
				String eName = rs.getString("employee_name");
				String email = rs.getString("email");
				Double salary = rs.getDouble("salary");
				BigDecimal bonus = rs.getBigDecimal("bonus");

				System.out.println(empId + "\t" + eName + "\t" + salary + "\t" + email + "\t" + bonus);

			} else {
				System.out.println("Employee doesn't exit with provided ID..");
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

package com.infotech.client;

import java.math.BigDecimal;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.Statement;

import com.infotech.util.DBUtil;

public class ClientTest {

public static void main(String[] args) {

getEmployeeById();

//getAllEmployeesInfo();

}

private static void getAllEmployeesInfo() {

try (Connection connection = DBUtil.getConnection(); Statement st = connection.createStatement();) {

String SQL = "SELECT *FROM employee_table";

ResultSet rs = st.executeQuery(SQL);

while (rs.next()) {

int empId = rs.getInt("employee_id");

String eName = rs.getString("employee_name");

String email = rs.getString("email");

Double salary = rs.getDouble("salary");

BigDecimal bonus = rs.getBigDecimal("bonus");

System.out.println(empId + "\t" + eName + "\t" + salary + "\t" + email + "\t" + bonus);

}

} catch (Exception e) {

e.printStackTrace();

}

private static void getEmployeeById() {

try (Connection connection = DBUtil.getConnection(); Statement st = connection.createStatement();) {

String SQL = "SELECT *FROM employee_table WHERE employee_id=3";

ResultSet rs = st.executeQuery(SQL);

if (rs.next()) {

int empId = rs.getInt("employee_id");

String eName = rs.getString("employee_name");

String email = rs.getString("email");

Double salary = rs.getDouble("salary");

BigDecimal bonus = rs.getBigDecimal("bonus");

System.out.println(empId + "\t" + eName + "\t" + salary + "\t" + email + "\t" + bonus);

} else {

System.out.println("Employee doesn't exit with provided ID..");

}

} catch (Exception e) {

e.printStackTrace();

}

If you run method getEmployeeById() to get an employee record from database you will get below output on eclipse console:

Reading an employee record from database whose employee id is:3
3	Rocky	95000.0	rocky.cs015@gmail.com	600

1 2	Reading an employee record from database whose employee id is:3 3 Rocky 95000.0 rocky.cs015@gmail.com 600

Again If you run method getAllEmployeesInfo() to get all employees record from database you will get below output on eclipse console:

Reading all employee records from database..
1	Paul	60000.0	paul.cs2009@yahoo.com	400
2	Martin	90000.0	martin.cs2018@gmail.com	500
3	Rocky	95000.0	rocky.cs015@gmail.com	600

Reading all employee records from database..

1 Paul 60000.0 paul.cs2009@yahoo.com 400

2 Martin 90000.0 martin.cs2018@gmail.com 500

3 Rocky 95000.0 rocky.cs015@gmail.com 600

That’s all about reading data from a database using the JDBC statement.

You May Also Like:

What is Java Database Connectivity (JDBC)?
Types of JDBC drivers in java?
JDBC MySQL database Connection example
JDBC database connection using property resource(property file)
CRUD(Create,Read,Update and Delete) Operation using JDBC Statement
CRUD(Create,Read,Update and Delete) Operation using JDBC PreparedStatement

If you have any feedback or suggestion please feel free to drop in blow comment box.

Leave a Reply Cancel reply