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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | 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)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | 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)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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