CRUD JDBC Project in Java is a good and basic project for beginners this project will help you to understand the basic concepts of JDBC operation. Here we have covered more than 11 JDBC operations.
Database connection with java is one of the essential features of java, In java to connect with the database we use java database connectivity(JDBC) programs.
JDBC is an Application Program Interface(API) that is used to interact with database and java programs in database independent way.
So JDBC (complete form Java Database Connectivity) code to interact with MySQL, oracle database, and other databases.
Here we used JDBC with MySQL to connect it we need a MySQL JDBC driver to connect MySQL with java.
Here we will see a java JDBC project to perform all basic operations involved in Database interaction.
Using Employee details program in java using JDBC operations.
Setting Up CRUD JDBC Project in Java
JDBC project setup is in two part
- create a database
- Write Java, JDBC codes
Creating database
- Create a database username with a password.
- given all privileges to the user.
- designed database and table for employees.
Above all details are set in the following script.
Run this script before running the java JDBC program
create table employee( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, fname varchar(100), lname varchar(30), address varchar(80), mobile_no varchar(12), email_id varchar(100), city varchar(50), designation varchar(20), dob date, doj date, salary decimal(10,2), add_date timestamp DEFAULT CURRENT_TIMESTAMP, primary key(id), unique key(mobile_no), unique key(email_id) )ENGINE=InnoDB DEFAULT CHARSET=utf8;
CRUD JDBC Project in Java Creating Classes
Writing Java Jdbc Code
- Create Java Bean
- Creating Main class (Execution starts from here)
- Create Action class
- Create DAO class
Java Bean
A Java Bean is a simple java class, that holds all properties, getter-setter methods, and constructors of the class.
Java Bean for Employee class is given below
/* This class contains getter setter and constructor of class
*/
package pojo;
import java.sql.Timestamp;
/**
*
* @author Manish Sahu
*/
public class Employee {
private long id;
private String fname;
private String lname;
private String address;
private String mobileNo;
private String mailId;
private String city;
private Timestamp addDate;
public Employee() {
}
public Employee(String fname, String lname, String address, String mobileNo, String mailId, String city) {
this.fname = fname;
this.lname = lname;
this.address = address;
this.mobileNo = mobileNo;
this.mailId = mailId;
this.city = city;
}
public Employee(long id, String fname, String lname, String address, String mobileNo, String mailId, String city) {
this.id = id;
this.fname = fname;
this.lname = lname;
this.address = address;
this.mobileNo = mobileNo;
this.mailId = mailId;
this.city = city;
}
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
public String getFname() {
return fname;
}
public void setFname(String fname) {
this.fname = fname;
}
public String getLname() {
return lname;
}
public void setLname(String lname) {
this.lname = lname;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public String getMobileNo() {
return mobileNo;
}
public void setMobileNo(String mobileNo) {
this.mobileNo = mobileNo;
}
public String getMailId() {
return mailId;
}
public void setMailId(String mailId) {
this.mailId = mailId;
}
public String getCity() {
return city;
}
public void setCity(String city) {
this.city = city;
}
public Timestamp getAddDate() {
return addDate;
}
public void setAddDate(Timestamp addDate) {
this.addDate = addDate;
}
@Override
public String toString() {
return "Employee{" + "id=" + id + ", fname=" + fname + ", lname=" + lname + ", address=" + address + ", mobileNo=" + mobileNo + ", mailId=" + mailId + ", city=" + city + ", addDate=" + addDate + '}';
}
}
Create Main Class
Below is the main class for running all java JDBC examples
Here you find various examples like inserting in the table, updating the table, deleting a record from the MySQL table, and selecting a specific or all record from the table.
To run the below code see all actions and operations in the below table
| Sr No | Operation and code | Action |
|---|---|---|
| 1 | ADD (1) | Add Employee records in database should be initialize only once. First time after creating database |
| 2 | UPDATE (2) | Update a record in table, based on primary key id |
| 3 | DELETE (3) | Insert a new record to table and delete it from database |
| 4 | FETCH BY ID (4) | Fetch a record from table based on id |
| 5 | FETCH BY EMAIL (5) | Fetch a record from table based on email |
| 6 | FETCH BY MOBILE NO (6) | Fetch a record from table based on mobile number |
| 7 | SEARCH BY NAME (7) | Fetch records from table based on Name |
| 8 | FETCH BY CITY (8) | Fetch records from table based on city name |
| 9 | FETCH BY SALARY RANGE (9) | Fetch records from table based on Salary Range |
| 10 | FETCH BY DOB (10) | Fetch records from table based on date of birth |
| 11 | FETCH BY DOJ RANGE (11) | Fetch records from table based on Date of Joining Range |
| 12 | FETCH ALL (12) | Fetch all records from table |
| 13 | EXIT (0) | EXIT FROM ALL OPERATION |
package ebhor.start;
import ebhor.action.EmployeeAction;
import ebhor.model.Employee;
import java.math.BigDecimal;
import java.sql.Date;
import java.util.Scanner;
public class MainClass {
EmployeeAction action = new EmployeeAction();
public static void main(String[] args) {
String DbOperation = "EXIT(0), ADD(1), UPDATE(2), DELETE(3), FETCHBYID(4), FETCHBYEMAIL(5), FETCHBYMOBNO(6), FETCHBYNAME(7),"
+ "FETCHBYCITY(8), FETCHBYSALRANGE(9), FETCHBYDOB(10), FETCHBYDOJRANGE(11), FETCHALL(12)";
MainClass mainclass = new MainClass();
int value = 0;
do {
System.out.println("valid operations are");
System.out.println(DbOperation);
System.out.println("Enter valid operation number 0-12");
Scanner scanner = new Scanner(System.in);
value = scanner.nextInt();
switch (value) {
case 1:
mainclass.addEmployee();
break;
case 2:
mainclass.updateEmployee();
mainclass.fetchAllEmployee();
break;
case 3:
mainclass.deleteEmployee();
break;
case 4:
mainclass.fetchEmployeeById();
break;
case 5:
mainclass.fetchEmployeeByEmail();
break;
case 6:
mainclass.fetchEmployeeByMobileNo();
break;
case 7:
mainclass.searchEmployeeByName();
break;
case 8:
mainclass.fetchEmployeeByCity();
break;
case 9:
mainclass.fetchEmployeeBySalaryRange();
break;
case 10:
mainclass.fetchEmployeeByDob();
break;
case 11:
mainclass.fetchEmployeeByDOjRange();
break;
case 12:
mainclass.fetchAllEmployee();
break;
case 0:
System.out.println("Exiting code");
break;
default:
System.out.println("Not a valid entry");
}
} while (value != 0);
}
public void addEmployee() {
Employee employee = new Employee();
Scanner insert = new Scanner(System.in);
System.out.println("Enter First Name");
employee.setFname(insert.next());
System.out.println("Enter Last Name");
employee.setLname(insert.next());
System.out.println("Enter Address");
employee.setAddress(insert.next());
System.out.println("Enter Mobile Number");
employee.setMobileNo(insert.next());
System.out.println("Enter Mail Id");
employee.setMailId(insert.next());
System.out.println("Enter City");
employee.setCity(insert.next());
System.out.println("Enter Designation");
employee.setDesignation(insert.next());
System.out.println("Enter Dob (yyyy-mm-dd)");
employee.setDob(Date.valueOf(insert.next()));
System.out.println("Enter Doj (yyyy-mm-dd)");
employee.setDoj(Date.valueOf(insert.next()));
System.out.println("Enter Salary");
employee.setSalary(insert.nextBigDecimal());
action.insert(employee);
}
public void updateEmployee() {
Employee employee = new Employee();
Scanner insert = new Scanner(System.in);
System.out.println("Enter Employee Id");
employee.setId(insert.nextLong());
System.out.println("Enter First Name");
employee.setFname(insert.next());
System.out.println("Enter Last Name");
employee.setLname(insert.next());
System.out.println("Enter Address");
employee.setAddress(insert.next());
System.out.println("Enter Mobile Number");
employee.setMobileNo(insert.next());
System.out.println("Enter Mail Id");
employee.setMailId(insert.next());
System.out.println("Enter City");
employee.setCity(insert.next());
System.out.println("Enter Designation");
employee.setDesignation(insert.next());
System.out.println("Enter Dob (yyyy-mm-dd)");
employee.setDob(Date.valueOf(insert.next()));
System.out.println("Enter Doj (yyyy-mm-dd)");
employee.setDoj(Date.valueOf(insert.next()));
System.out.println("Enter Salary");
employee.setSalary(insert.nextBigDecimal());
action.update(employee);
}
public void deleteEmployee() {
Scanner insert = new Scanner(System.in);
System.out.println("Enter Employee Id");
long id = insert.nextLong();
action.delete(id);
}
public void fetchEmployeeById() {
Scanner insert = new Scanner(System.in);
System.out.println("Enter Employee Id");
long id = insert.nextLong();
action.fetchById(id);
}
public void fetchEmployeeByEmail() {
Scanner insert = new Scanner(System.in);
System.out.println("Enter Employee Mail Id");
String mailId = insert.next();
action.fetchByEmailId(mailId);
}
public void fetchEmployeeByMobileNo() {
Scanner insert = new Scanner(System.in);
System.out.println("Enter Employee Mobile Number");
String mobileNo = insert.next();
action.fetchByMobileNo(mobileNo);
}
public void searchEmployeeByName() {
Scanner insert = new Scanner(System.in);
System.out.println("Enter Employee Name");
String name = insert.next();
action.searchByName(name);
}
public void fetchEmployeeByCity() {
Scanner insert = new Scanner(System.in);
System.out.println("Enter Employee City");
String city = insert.next();
action.fetchByCity(city);
}
public void fetchEmployeeBySalaryRange() {
Scanner insert = new Scanner(System.in);
System.out.println("Enter Salary Start Range");
BigDecimal salaryRange1 = insert.nextBigDecimal();
System.out.println("Enter Salary End Range");
BigDecimal salaryRange2 = insert.nextBigDecimal();
action.fetchBySalaryRange(salaryRange1, salaryRange2);
}
public void fetchEmployeeByDob() {
Scanner insert = new Scanner(System.in);
System.out.println("Enter Date of Birth (yyyy-mm-dd)");
String dob = insert.next();
action.fetchByDob(Date.valueOf(dob));
}
public void fetchEmployeeByDOjRange() {
Scanner insert = new Scanner(System.in);
System.out.println("Enter Start Date of Joining (yyyy-mm-dd)");
String dob1 = insert.next();
System.out.println("Enter End Date of Joining (yyyy-mm-dd)");
String dob2 = insert.next();
action.fetchByRangeDoj(Date.valueOf(dob1), Date.valueOf(dob2));
}
public void fetchAllEmployee() {
action.fetchAll();
}
}
Create Action Class
The above class will call the method of the below action class
package ebhor.action;
import ebhor.dao.EmployeeDao;
import java.util.List;
import ebhor.model.Employee;
import java.math.BigDecimal;
import java.sql.Date;
/**
* All data accept and manipulation should be done here
*
* @author Manish Sahu
*/
public class EmployeeAction {
EmployeeDao dao = new EmployeeDao();
int st;
public void insert(Employee employee) {
st = dao.insert(employee);
if (st == 1) {
System.out.println("Employee Inserted Successfully");
} else if (st == -1) {
System.out.println("Employee Already exists");
} else {
System.out.println("Unable to Insert Employee");
}
}
public void update(Employee employee) {
st = dao.update(employee);
if (st == 1) {
System.out.println("Employee Updated Successfully");
} else {
System.out.println("Unable to update Employee");
}
}
public void delete(Long id) {
st = dao.delete(id);
if (st == 1) {
System.out.println("Employee Deleted Successfully");
} else {
System.out.println("No Record Found");
}
}
public void fetchById(Long id) {
Employee employee = dao.fetchById(id);
if (employee.getId() == 0) {
System.out.println("No Record Found");
} else {
System.out.println("Employee Details are :");
System.out.println(employee);
}
}
public void fetchByEmailId(String emailId) {
Employee employee = dao.fetchByEmailId(emailId);
if (employee.getId() == 0) {
System.out.println("No Record Found");
} else {
System.out.println("Employee Details are :");
System.out.println(employee);
}
}
public void fetchByMobileNo(String mobileNo) {
Employee employee = dao.fetchByMobileNo(mobileNo);
if (employee.getId() == 0) {
System.out.println("No Record Found");
} else {
System.out.println("Employee Details are :");
System.out.println(employee);
}
}
public void searchByName(String name) {
List employeeList = dao.searchByName(name);
if (employeeList.isEmpty()) {
System.out.println("No Record Found");
} else {
System.out.println("Employee Details are :");
for (Employee employee : employeeList) {
System.out.println(employee);
}
}
}
public void fetchByCity(String city) {
List employeeList = dao.fetchByCity(city);
if (employeeList.isEmpty()) {
System.out.println("No Record Found");
} else {
System.out.println("Employee Details are :");
for (Employee employee : employeeList) {
System.out.println(employee);
}
}
}
public void fetchBySalaryRange(BigDecimal lowerSalary, BigDecimal higherSalary) {
List employeeList = dao.fetchBySalaryRange(lowerSalary, higherSalary);
if (employeeList.isEmpty()) {
System.out.println("No Record Found");
} else {
System.out.println("Employee Details are :");
for (Employee employee : employeeList) {
System.out.println(employee);
}
}
}
public void fetchByDob(Date dob) {
List employeeList = dao.fetchByDob(dob);
if (employeeList.isEmpty()) {
System.out.println("No Record Found");
} else {
System.out.println("Employee Details are :");
for (Employee employee : employeeList) {
System.out.println(employee);
}
}
}
public void fetchByRangeDoj(Date startDate, Date endDate) {
List employeeList = dao.fetchByRangeDoj(startDate, endDate);
if (employeeList.isEmpty()) {
System.out.println("No Record Found");
} else {
System.out.println("Employee Details are :");
for (Employee employee : employeeList) {
System.out.println(employee);
}
}
}
public void fetchAll() {
List employeeList = dao.fetchAll();
if (employeeList.isEmpty()) {
System.out.println("No Record Found");
} else {
System.out.println("Employee Details are :");
for (Employee employee : employeeList) {
System.out.println(employee);
}
}
}
}
Create DAO class
The above action class will call to below DAO class method
Here All JDBC Prepared statement are used to interact with mysql
Various JDBC operations used here are
- Insert
- Update
- Delete
- FetchById
- FetchByEmailId
- FetchByMobileNo
- SearchByName
- FetchByCity
- FetchBySalaryRange
- FetchByDob
- FetchByDojRange
- FetchAll
Let’s see one by one each method
JDBC Insert Employee Details in MySql
public int insert(Employee employee) {
con = ConnectionFactory.getConnection();
try {
String query = "insert into employee(fname,lname,address,mobile_no,email_id,city,designation,dob,doj,salary) "
+ "values(?,?,?,?,?,?,?,?,?,?)";
ps = con.prepareStatement(query);
ps.setString(1, employee.getFname());
ps.setString(2, employee.getLname());
ps.setString(3, employee.getAddress());
ps.setString(4, employee.getMobileNo());
ps.setString(5, employee.getMailId());
ps.setString(6, employee.getCity());
ps.setString(7, employee.getDesignation());
ps.setDate(8, employee.getDob());
ps.setDate(9, employee.getDoj());
ps.setBigDecimal(10, employee.getSalary());
st = ps.executeUpdate();
System.out.println("inserted employee " + st);
} catch (MySQLIntegrityConstraintViolationException e) {
st = -1;
e.printStackTrace();
} catch (Exception e) {
st = -2;
e.printStackTrace();
} finally {
try {
con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
return st;
}
JDBC Update Employee Details in MySQL
public int update(Employee employee) {
con = ConnectionFactory.getConnection();
try {
String query = "update employee set fname=?,lname=?,address=?,mobile_no=?,email_id=?,city=?, "
+ "designation=?,dob=?,doj=?,salary=? "
+ "where id=? ";
ps = con.prepareStatement(query);
ps.setString(1, employee.getFname());
ps.setString(2, employee.getLname());
ps.setString(3, employee.getAddress());
ps.setString(4, employee.getMobileNo());
ps.setString(5, employee.getMailId());
ps.setString(6, employee.getCity());
ps.setString(7, employee.getDesignation());
ps.setDate(8, employee.getDob());
ps.setDate(9, employee.getDoj());
ps.setBigDecimal(10, employee.getSalary());
ps.setLong(11, employee.getId());
st = ps.executeUpdate();
System.out.println("updated employee " + st);
} catch (Exception e) {
st = -2;
e.printStackTrace();
} finally {
try {
con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
return st;
}
JDBC Delete Employee Details from MySQL
public int delete(long id) {
con = ConnectionFactory.getConnection();
try {
String query = "delete from employee where id=? ";
ps = con.prepareStatement(query);
ps.setLong(1, id);
st = ps.executeUpdate();
System.out.println("deleted employee " + st);
} catch (Exception e) {
st = -2;
e.printStackTrace();
} finally {
try {
con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
return st;
}
JDBC Fetch Employee Details based on Id
public Employee fetchById(long id) {
Employee employee = new Employee();
con = ConnectionFactory.getConnection();
try {
String query = "select * from employee where id=?";
ps = con.prepareStatement(query);
ps.setLong(1, id);
rs = ps.executeQuery();
while (rs.next()) {
employee.setId(rs.getLong("id"));
employee.setFname(rs.getString("fname"));
employee.setLname(rs.getString("lname"));
employee.setAddress(rs.getString("address"));
employee.setMobileNo(rs.getString("mobile_no"));
employee.setMailId(rs.getString("email_id"));
employee.setCity(rs.getString("city"));
employee.setDesignation(rs.getString("designation"));
employee.setDob(rs.getDate("dob"));
employee.setDoj(rs.getDate("doj"));
employee.setSalary(rs.getBigDecimal("salary"));
employee.setAddDate(rs.getTimestamp("add_date"));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
return employee;
}
JDBC Fetch Employee Details based on Email Id
public Employee fetchByEmailId(String emailId) {
Employee employee = new Employee();
con = ConnectionFactory.getConnection();
try {
String query = "select * from employee where email_id=?";
ps = con.prepareStatement(query);
ps.setString(1, emailId);
rs = ps.executeQuery();
while (rs.next()) {
employee.setId(rs.getLong("id"));
employee.setFname(rs.getString("fname"));
employee.setLname(rs.getString("lname"));
employee.setAddress(rs.getString("address"));
employee.setMobileNo(rs.getString("mobile_no"));
employee.setMailId(rs.getString("email_id"));
employee.setCity(rs.getString("city"));
employee.setDesignation(rs.getString("designation"));
employee.setDob(rs.getDate("dob"));
employee.setDoj(rs.getDate("doj"));
employee.setSalary(rs.getBigDecimal("salary"));
employee.setAddDate(rs.getTimestamp("add_date"));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
return employee;
}
JDBC Fetch Employee Details based on Mobile Number
public Employee fetchByMobileNo(String mobileNo) {
Employee employee = new Employee();
con = ConnectionFactory.getConnection();
try {
String query = "select * from employee where mobile_no=?";
ps = con.prepareStatement(query);
ps.setString(1, mobileNo);
rs = ps.executeQuery();
while (rs.next()) {
employee.setId(rs.getLong("id"));
employee.setFname(rs.getString("fname"));
employee.setLname(rs.getString("lname"));
employee.setAddress(rs.getString("address"));
employee.setMobileNo(rs.getString("mobile_no"));
employee.setMailId(rs.getString("email_id"));
employee.setCity(rs.getString("city"));
employee.setDesignation(rs.getString("designation"));
employee.setDob(rs.getDate("dob"));
employee.setDoj(rs.getDate("doj"));
employee.setSalary(rs.getBigDecimal("salary"));
employee.setAddDate(rs.getTimestamp("add_date"));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
return employee;
}
JDBC Search Employee Details based on Name
public ListsearchByName(String name) { List employeeList = new ArrayList (); con = ConnectionFactory.getConnection(); try { String query = "select * from employee where fname like ? or lname like ?"; ps = con.prepareStatement(query); ps.setString(1, "%" + name + "%"); ps.setString(2, "%" + name + "%"); rs = ps.executeQuery(); while (rs.next()) { Employee employee = new Employee(); employee.setId(rs.getLong("id")); employee.setFname(rs.getString("fname")); employee.setLname(rs.getString("lname")); employee.setAddress(rs.getString("address")); employee.setMobileNo(rs.getString("mobile_no")); employee.setMailId(rs.getString("email_id")); employee.setCity(rs.getString("city")); employee.setDesignation(rs.getString("designation")); employee.setDob(rs.getDate("dob")); employee.setDoj(rs.getDate("doj")); employee.setSalary(rs.getBigDecimal("salary")); employee.setAddDate(rs.getTimestamp("add_date")); employeeList.add(employee); } } catch (Exception e) { e.printStackTrace(); } finally { try { con.close(); } catch (SQLException ex) { ex.printStackTrace(); } } return employeeList; }
JDBC Fetch Employee Details based on City Name
public ListfetchByCity(String city) { List employeeList = new ArrayList (); con = ConnectionFactory.getConnection(); try { String query = "select * from employee where city=?"; ps = con.prepareStatement(query); ps.setString(1, city); rs = ps.executeQuery(); while (rs.next()) { Employee employee = new Employee(); employee.setId(rs.getLong("id")); employee.setFname(rs.getString("fname")); employee.setLname(rs.getString("lname")); employee.setAddress(rs.getString("address")); employee.setMobileNo(rs.getString("mobile_no")); employee.setMailId(rs.getString("email_id")); employee.setCity(rs.getString("city")); employee.setDesignation(rs.getString("designation")); employee.setDob(rs.getDate("dob")); employee.setDoj(rs.getDate("doj")); employee.setSalary(rs.getBigDecimal("salary")); employee.setAddDate(rs.getTimestamp("add_date")); employeeList.add(employee); } } catch (Exception e) { e.printStackTrace(); } finally { try { con.close(); } catch (SQLException ex) { ex.printStackTrace(); } } return employeeList; }
JDBC Fetch Employee Details based on Salary Range
public ListfetchBySalaryRange(BigDecimal lowerSalary, BigDecimal higherSalary) { List employeeList = new ArrayList (); con = ConnectionFactory.getConnection(); try { String query = "select * from employee where salary between ? and ?"; ps = con.prepareStatement(query); ps.setBigDecimal(1, lowerSalary); ps.setBigDecimal(2, higherSalary); rs = ps.executeQuery(); while (rs.next()) { Employee employee = new Employee(); employee.setId(rs.getLong("id")); employee.setFname(rs.getString("fname")); employee.setLname(rs.getString("lname")); employee.setAddress(rs.getString("address")); employee.setMobileNo(rs.getString("mobile_no")); employee.setMailId(rs.getString("email_id")); employee.setCity(rs.getString("city")); employee.setDesignation(rs.getString("designation")); employee.setDob(rs.getDate("dob")); employee.setDoj(rs.getDate("doj")); employee.setSalary(rs.getBigDecimal("salary")); employee.setAddDate(rs.getTimestamp("add_date")); employeeList.add(employee); } } catch (Exception e) { e.printStackTrace(); } finally { try { con.close(); } catch (SQLException ex) { ex.printStackTrace(); } } return employeeList; }
JDBC Fetch Employee Details based on Date of Birth (DOB)
public ListfetchByDob(Date dob) { List employeeList = new ArrayList (); con = ConnectionFactory.getConnection(); try { String query = "select * from employee where dob=?"; ps = con.prepareStatement(query); ps.setDate(1, dob); rs = ps.executeQuery(); while (rs.next()) { Employee employee = new Employee(); employee.setId(rs.getLong("id")); employee.setFname(rs.getString("fname")); employee.setLname(rs.getString("lname")); employee.setAddress(rs.getString("address")); employee.setMobileNo(rs.getString("mobile_no")); employee.setMailId(rs.getString("email_id")); employee.setCity(rs.getString("city")); employee.setDesignation(rs.getString("designation")); employee.setDob(rs.getDate("dob")); employee.setDoj(rs.getDate("doj")); employee.setSalary(rs.getBigDecimal("salary")); employee.setAddDate(rs.getTimestamp("add_date")); employeeList.add(employee); } } catch (Exception e) { e.printStackTrace(); } finally { try { con.close(); } catch (SQLException ex) { ex.printStackTrace(); } } return employeeList; }
JDBC Fetch Employee Details based on Date of Joining Range (DOJ)
public ListfetchByRangeDoj(Date startDate, Date endDate) { List employeeList = new ArrayList (); con = ConnectionFactory.getConnection(); try { String query = "select * from employee where doj between ? and ?"; ps = con.prepareStatement(query); ps.setDate(1, startDate); ps.setDate(2, endDate); rs = ps.executeQuery(); while (rs.next()) { Employee employee = new Employee(); employee.setId(rs.getLong("id")); employee.setFname(rs.getString("fname")); employee.setLname(rs.getString("lname")); employee.setAddress(rs.getString("address")); employee.setMobileNo(rs.getString("mobile_no")); employee.setMailId(rs.getString("email_id")); employee.setCity(rs.getString("city")); employee.setDesignation(rs.getString("designation")); employee.setDob(rs.getDate("dob")); employee.setDoj(rs.getDate("doj")); employee.setSalary(rs.getBigDecimal("salary")); employee.setAddDate(rs.getTimestamp("add_date")); employeeList.add(employee); } } catch (Exception e) { e.printStackTrace(); } finally { try { con.close(); } catch (SQLException ex) { ex.printStackTrace(); } } return employeeList; }
JDBC Fetch Employee Details Order by Id DESC
public ListfetchAll() { List employeeList = new ArrayList (); con = ConnectionFactory.getConnection(); try { String query = "select * from employee order by id desc"; ps = con.prepareStatement(query); rs = ps.executeQuery(); while (rs.next()) { Employee employee = new Employee(); employee.setId(rs.getLong("id")); employee.setFname(rs.getString("fname")); employee.setLname(rs.getString("lname")); employee.setAddress(rs.getString("address")); employee.setMobileNo(rs.getString("mobile_no")); employee.setMailId(rs.getString("email_id")); employee.setCity(rs.getString("city")); employee.setDesignation(rs.getString("designation")); employee.setDob(rs.getDate("dob")); employee.setDoj(rs.getDate("doj")); employee.setSalary(rs.getBigDecimal("salary")); employee.setAddDate(rs.getTimestamp("add_date")); employeeList.add(employee); } } catch (Exception e) { e.printStackTrace(); } finally { try { con.close(); } catch (SQLException ex) { ex.printStackTrace(); } } return employeeList; }
All Complete codes for JDBC Operations
/*
* All database operation create update delete and select
* of employee should be done here
*
*/
package ebhor.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import ebhor.model.Employee;
import com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException;
import java.math.BigDecimal;
import java.sql.Date;
public class EmployeeDao {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
int st;//status
public int insert(Employee employee) {
con = ConnectionFactory.getConnection();
try {
String query = "insert into employee(fname,lname,address,mobile_no,email_id,city,designation,dob,doj,salary) "
+ "values(?,?,?,?,?,?,?,?,?,?)";
ps = con.prepareStatement(query);
ps.setString(1, employee.getFname());
ps.setString(2, employee.getLname());
ps.setString(3, employee.getAddress());
ps.setString(4, employee.getMobileNo());
ps.setString(5, employee.getMailId());
ps.setString(6, employee.getCity());
ps.setString(7, employee.getDesignation());
ps.setDate(8, employee.getDob());
ps.setDate(9, employee.getDoj());
ps.setBigDecimal(10, employee.getSalary());
st = ps.executeUpdate();
System.out.println("inserted employee " + st);
} catch (MySQLIntegrityConstraintViolationException e) {
st = -1;
e.printStackTrace();
} catch (Exception e) {
st = -2;
e.printStackTrace();
} finally {
try {
con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
return st;
}
public int update(Employee employee) {
con = ConnectionFactory.getConnection();
try {
String query = "update employee set fname=?,lname=?,address=?,mobile_no=?,email_id=?,city=?, "
+ "designation=?,dob=?,doj=?,salary=? "
+ "where id=? ";
ps = con.prepareStatement(query);
ps.setString(1, employee.getFname());
ps.setString(2, employee.getLname());
ps.setString(3, employee.getAddress());
ps.setString(4, employee.getMobileNo());
ps.setString(5, employee.getMailId());
ps.setString(6, employee.getCity());
ps.setString(7, employee.getDesignation());
ps.setDate(8, employee.getDob());
ps.setDate(9, employee.getDoj());
ps.setBigDecimal(10, employee.getSalary());
ps.setLong(11, employee.getId());
st = ps.executeUpdate();
System.out.println("updated employee " + st);
} catch (Exception e) {
st = -2;
e.printStackTrace();
} finally {
try {
con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
return st;
}
public int delete(long id) {
con = ConnectionFactory.getConnection();
try {
String query = "delete from employee where id=? ";
ps = con.prepareStatement(query);
ps.setLong(1, id);
st = ps.executeUpdate();
System.out.println("deleted employee " + st);
} catch (Exception e) {
st = -2;
e.printStackTrace();
} finally {
try {
con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
return st;
}
public Employee fetchById(long id) {
Employee employee = new Employee();
con = ConnectionFactory.getConnection();
try {
String query = "select * from employee where id=?";
ps = con.prepareStatement(query);
ps.setLong(1, id);
rs = ps.executeQuery();
while (rs.next()) {
employee.setId(rs.getLong("id"));
employee.setFname(rs.getString("fname"));
employee.setLname(rs.getString("lname"));
employee.setAddress(rs.getString("address"));
employee.setMobileNo(rs.getString("mobile_no"));
employee.setMailId(rs.getString("email_id"));
employee.setCity(rs.getString("city"));
employee.setDesignation(rs.getString("designation"));
employee.setDob(rs.getDate("dob"));
employee.setDoj(rs.getDate("doj"));
employee.setSalary(rs.getBigDecimal("salary"));
employee.setAddDate(rs.getTimestamp("add_date"));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
return employee;
}
public Employee fetchByEmailId(String emailId) {
Employee employee = new Employee();
con = ConnectionFactory.getConnection();
try {
String query = "select * from employee where email_id=?";
ps = con.prepareStatement(query);
ps.setString(1, emailId);
rs = ps.executeQuery();
while (rs.next()) {
employee.setId(rs.getLong("id"));
employee.setFname(rs.getString("fname"));
employee.setLname(rs.getString("lname"));
employee.setAddress(rs.getString("address"));
employee.setMobileNo(rs.getString("mobile_no"));
employee.setMailId(rs.getString("email_id"));
employee.setCity(rs.getString("city"));
employee.setDesignation(rs.getString("designation"));
employee.setDob(rs.getDate("dob"));
employee.setDoj(rs.getDate("doj"));
employee.setSalary(rs.getBigDecimal("salary"));
employee.setAddDate(rs.getTimestamp("add_date"));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
return employee;
}
public Employee fetchByMobileNo(String mobileNo) {
Employee employee = new Employee();
con = ConnectionFactory.getConnection();
try {
String query = "select * from employee where mobile_no=?";
ps = con.prepareStatement(query);
ps.setString(1, mobileNo);
rs = ps.executeQuery();
while (rs.next()) {
employee.setId(rs.getLong("id"));
employee.setFname(rs.getString("fname"));
employee.setLname(rs.getString("lname"));
employee.setAddress(rs.getString("address"));
employee.setMobileNo(rs.getString("mobile_no"));
employee.setMailId(rs.getString("email_id"));
employee.setCity(rs.getString("city"));
employee.setDesignation(rs.getString("designation"));
employee.setDob(rs.getDate("dob"));
employee.setDoj(rs.getDate("doj"));
employee.setSalary(rs.getBigDecimal("salary"));
employee.setAddDate(rs.getTimestamp("add_date"));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
return employee;
}
public List searchByName(String name) {
List employeeList = new ArrayList();
con = ConnectionFactory.getConnection();
try {
String query = "select * from employee where fname like ? or lname like ?";
ps = con.prepareStatement(query);
ps.setString(1, "%" + name + "%");
ps.setString(2, "%" + name + "%");
rs = ps.executeQuery();
while (rs.next()) {
Employee employee = new Employee();
employee.setId(rs.getLong("id"));
employee.setFname(rs.getString("fname"));
employee.setLname(rs.getString("lname"));
employee.setAddress(rs.getString("address"));
employee.setMobileNo(rs.getString("mobile_no"));
employee.setMailId(rs.getString("email_id"));
employee.setCity(rs.getString("city"));
employee.setDesignation(rs.getString("designation"));
employee.setDob(rs.getDate("dob"));
employee.setDoj(rs.getDate("doj"));
employee.setSalary(rs.getBigDecimal("salary"));
employee.setAddDate(rs.getTimestamp("add_date"));
employeeList.add(employee);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
return employeeList;
}
public List fetchByCity(String city) {
List employeeList = new ArrayList();
con = ConnectionFactory.getConnection();
try {
String query = "select * from employee where city=?";
ps = con.prepareStatement(query);
ps.setString(1, city);
rs = ps.executeQuery();
while (rs.next()) {
Employee employee = new Employee();
employee.setId(rs.getLong("id"));
employee.setFname(rs.getString("fname"));
employee.setLname(rs.getString("lname"));
employee.setAddress(rs.getString("address"));
employee.setMobileNo(rs.getString("mobile_no"));
employee.setMailId(rs.getString("email_id"));
employee.setCity(rs.getString("city"));
employee.setDesignation(rs.getString("designation"));
employee.setDob(rs.getDate("dob"));
employee.setDoj(rs.getDate("doj"));
employee.setSalary(rs.getBigDecimal("salary"));
employee.setAddDate(rs.getTimestamp("add_date"));
employeeList.add(employee);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
return employeeList;
}
public List fetchBySalaryRange(BigDecimal lowerSalary, BigDecimal higherSalary) {
List employeeList = new ArrayList();
con = ConnectionFactory.getConnection();
try {
String query = "select * from employee where salary between ? and ?";
ps = con.prepareStatement(query);
ps.setBigDecimal(1, lowerSalary);
ps.setBigDecimal(2, higherSalary);
rs = ps.executeQuery();
while (rs.next()) {
Employee employee = new Employee();
employee.setId(rs.getLong("id"));
employee.setFname(rs.getString("fname"));
employee.setLname(rs.getString("lname"));
employee.setAddress(rs.getString("address"));
employee.setMobileNo(rs.getString("mobile_no"));
employee.setMailId(rs.getString("email_id"));
employee.setCity(rs.getString("city"));
employee.setDesignation(rs.getString("designation"));
employee.setDob(rs.getDate("dob"));
employee.setDoj(rs.getDate("doj"));
employee.setSalary(rs.getBigDecimal("salary"));
employee.setAddDate(rs.getTimestamp("add_date"));
employeeList.add(employee);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
return employeeList;
}
public List fetchByDob(Date dob) {
List employeeList = new ArrayList();
con = ConnectionFactory.getConnection();
try {
String query = "select * from employee where dob=?";
ps = con.prepareStatement(query);
ps.setDate(1, dob);
rs = ps.executeQuery();
while (rs.next()) {
Employee employee = new Employee();
employee.setId(rs.getLong("id"));
employee.setFname(rs.getString("fname"));
employee.setLname(rs.getString("lname"));
employee.setAddress(rs.getString("address"));
employee.setMobileNo(rs.getString("mobile_no"));
employee.setMailId(rs.getString("email_id"));
employee.setCity(rs.getString("city"));
employee.setDesignation(rs.getString("designation"));
employee.setDob(rs.getDate("dob"));
employee.setDoj(rs.getDate("doj"));
employee.setSalary(rs.getBigDecimal("salary"));
employee.setAddDate(rs.getTimestamp("add_date"));
employeeList.add(employee);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
return employeeList;
}
public List fetchByRangeDoj(Date startDate, Date endDate) {
List employeeList = new ArrayList();
con = ConnectionFactory.getConnection();
try {
String query = "select * from employee where doj between ? and ?";
ps = con.prepareStatement(query);
ps.setDate(1, startDate);
ps.setDate(2, endDate);
rs = ps.executeQuery();
while (rs.next()) {
Employee employee = new Employee();
employee.setId(rs.getLong("id"));
employee.setFname(rs.getString("fname"));
employee.setLname(rs.getString("lname"));
employee.setAddress(rs.getString("address"));
employee.setMobileNo(rs.getString("mobile_no"));
employee.setMailId(rs.getString("email_id"));
employee.setCity(rs.getString("city"));
employee.setDesignation(rs.getString("designation"));
employee.setDob(rs.getDate("dob"));
employee.setDoj(rs.getDate("doj"));
employee.setSalary(rs.getBigDecimal("salary"));
employee.setAddDate(rs.getTimestamp("add_date"));
employeeList.add(employee);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
return employeeList;
}
public List fetchAll() {
List employeeList = new ArrayList();
con = ConnectionFactory.getConnection();
try {
String query = "select * from employee order by id desc";
ps = con.prepareStatement(query);
rs = ps.executeQuery();
while (rs.next()) {
Employee employee = new Employee();
employee.setId(rs.getLong("id"));
employee.setFname(rs.getString("fname"));
employee.setLname(rs.getString("lname"));
employee.setAddress(rs.getString("address"));
employee.setMobileNo(rs.getString("mobile_no"));
employee.setMailId(rs.getString("email_id"));
employee.setCity(rs.getString("city"));
employee.setDesignation(rs.getString("designation"));
employee.setDob(rs.getDate("dob"));
employee.setDoj(rs.getDate("doj"));
employee.setSalary(rs.getBigDecimal("salary"));
employee.setAddDate(rs.getTimestamp("add_date"));
employeeList.add(employee);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
return employeeList;
}
}
Download this Java Project with source code for GitHub