CRUD JDBC Project in Java with MySql Source Code

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;

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

}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

/* 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();
    }

}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

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

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

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

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

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

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

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

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

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 List<Employee> searchByName(String name) {
        List<Employee> employeeList = new ArrayList<Employee>();
        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<Employee> searchByName(String name) {

List<Employee> employeeList = new ArrayList<Employee>();

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 List<Employee> fetchByCity(String city) {
        List<Employee> employeeList = new ArrayList<Employee>();
        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<Employee> fetchByCity(String city) {

List<Employee> employeeList = new ArrayList<Employee>();

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 List<Employee> fetchBySalaryRange(BigDecimal lowerSalary, BigDecimal higherSalary) {
        List<Employee> employeeList = new ArrayList<Employee>();
        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<Employee> fetchBySalaryRange(BigDecimal lowerSalary, BigDecimal higherSalary) {

List<Employee> employeeList = new ArrayList<Employee>();

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 List<Employee> fetchByDob(Date dob) {
        List<Employee> employeeList = new ArrayList<Employee>();
        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<Employee> fetchByDob(Date dob) {

List<Employee> employeeList = new ArrayList<Employee>();

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 List<Employee> fetchByRangeDoj(Date startDate, Date endDate) {
        List<Employee> employeeList = new ArrayList<Employee>();
        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<Employee> fetchByRangeDoj(Date startDate, Date endDate) {

List<Employee> employeeList = new ArrayList<Employee>();

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 List<Employee> fetchAll() {
        List<Employee> employeeList = new ArrayList<Employee>();
        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;
    }

public List<Employee> fetchAll() {

List<Employee> employeeList = new ArrayList<Employee>();

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<Employee> searchByName(String name) {
        List<Employee> employeeList = new ArrayList<Employee>();
        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<Employee> fetchByCity(String city) {
        List<Employee> employeeList = new ArrayList<Employee>();
        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<Employee> fetchBySalaryRange(BigDecimal lowerSalary, BigDecimal higherSalary) {
        List<Employee> employeeList = new ArrayList<Employee>();
        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<Employee> fetchByDob(Date dob) {
        List<Employee> employeeList = new ArrayList<Employee>();
        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<Employee> fetchByRangeDoj(Date startDate, Date endDate) {
        List<Employee> employeeList = new ArrayList<Employee>();
        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<Employee> fetchAll() {
        List<Employee> employeeList = new ArrayList<Employee>();
        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;
    }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

412

413

414

415

416

417

418

* 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<Employee> searchByName(String name) {

List<Employee> employeeList = new ArrayList<Employee>();

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<Employee> fetchByCity(String city) {

List<Employee> employeeList = new ArrayList<Employee>();

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<Employee> fetchBySalaryRange(BigDecimal lowerSalary, BigDecimal higherSalary) {

List<Employee> employeeList = new ArrayList<Employee>();

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<Employee> fetchByDob(Date dob) {

List<Employee> employeeList = new ArrayList<Employee>();

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<Employee> fetchByRangeDoj(Date startDate, Date endDate) {

List<Employee> employeeList = new ArrayList<Employee>();

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<Employee> fetchAll() {

List<Employee> employeeList = new ArrayList<Employee>();

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

Employee Management System Project JSP Servlet