Multipart Task (4/4) - Spring Data Access - Plain JDBC - Completing the Backend Data Access Layer

[akash-coded]

Write models, DAOs, repositories, services, controllers and driver code for the operations on bonus and title tables in the jdbc_demo database.

[GouthamGunji]

WorkerDao

package com.spring.jdbc.demo.dao;

import java.util.List;

import com.spring.jdbc.demo.model.Worker;

public interface WorkerDao {
	// Create Operation
	Integer createWorker(Worker worker);

	// Read/Retrieve Operations
	Worker findWorkerById(Integer id);

	List<Worker> findAllWorkers();

	Integer findWorkersCountByDepartment(String department);

	// Update Operations
	Integer updateWorker(Worker worker);

	Integer updateSalaryByDepartment(String department, Integer bonusFactor);

	// Delete Operations
	Integer deleteWorkerById(Integer id);
}

WorkerResultSetExtractor

package com.spring.jdbc.demo.mapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;

import com.spring.jdbc.demo.model.Worker;

public class WorkerResultSetExtractor implements ResultSetExtractor<List<Worker>> {

	@Override
	public List<Worker> extractData(ResultSet rs) throws SQLException, DataAccessException {
		// TODO Auto-generated method stub
		List<Worker> workers = new ArrayList<>();
		while (rs.next()) {
			Worker worker = new Worker();
			worker.setWorkerId(rs.getInt("WORKER_ID"));
			worker.setFirstName(rs.getString("FIRST_NAME"));
			worker.setLastName(rs.getString("LAST_NAME"));
			worker.setSalary(rs.getInt("SALARY"));
			worker.setJoiningDate(rs.getTimestamp("JOINING_DATE"));
			worker.setDepartment(rs.getString("DEPARTMENT"));

			workers.add(worker);
		}
		return workers;
	}

}

WorkerRowMapper

package com.spring.jdbc.demo.mapper;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

import com.spring.jdbc.demo.model.Worker;

public class WorkerRowMapper implements RowMapper<Worker> {

	@Override
	public Worker mapRow(ResultSet rs, int rowNum) throws SQLException {
		// TODO Auto-generated method stub
		Worker worker = new Worker();

		worker.setWorkerId(rs.getInt("WORKER_ID"));
		worker.setFirstName(rs.getString("FIRST_NAME"));
		worker.setLastName(rs.getString("LAST_NAME"));
		worker.setSalary(rs.getInt("SALARY"));
		worker.setJoiningDate(rs.getTimestamp("JOINING_DATE"));
		worker.setDepartment(rs.getString("DEPARTMENT"));
		return worker;

	}

}

Worker Repository

package com.spring.jdbc.demo.repository;

import java.util.List;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;

import com.spring.jdbc.demo.dao.WorkerDao;
import com.spring.jdbc.demo.mapper.WorkerResultSetExtractor;
import com.spring.jdbc.demo.mapper.WorkerRowMapper;
import com.spring.jdbc.demo.model.Worker;

public class WorkerRepository implements WorkerDao {

	private DataSource dataSource;
	private JdbcTemplate jdbcTemplate;

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
		this.jdbcTemplate = new JdbcTemplate(this.dataSource);
	}

	@Override
	public Integer createWorker(Worker worker) {
		String insertWorkerFormat = """
				INSERT INTO Worker (
				    WORKER_ID,
				    FIRST_NAME,
				    LAST_NAME,
				    SALARY,
				    JOINING_DATE,
				    DEPARTMENT)
				VALUES (
				    ?, ?, ?, ?, NOW(), ?)""";

		return jdbcTemplate.update(insertWorkerFormat, worker.getWorkerId(), worker.getFirstName(),
				worker.getLastName(), worker.getSalary(), worker.getDepartment());

	}

	@Override
	public Worker findWorkerById(Integer id) {

		String getWorkerFormat = """
				SELECT *
				FROM
				    Worker
				WHERE
				    WORKER_ID = ?""";

		return jdbcTemplate.queryForObject(getWorkerFormat, new Object[] { id }, new WorkerRowMapper());
	}

	@Override
	public List<Worker> findAllWorkers() {
		String getWorkersFormat = """
				SELECT *
				FROM
				    Worker""";

		return jdbcTemplate.query(getWorkersFormat, new WorkerResultSetExtractor());
	}

	@Override
	public Integer updateWorker(Worker worker) {
		String updateWorkerFormat = """
				UPDATE Worker
				SET
				    DEPARTMENT = ?
				WHERE
				    WORKER_ID = ?""";

		return jdbcTemplate.update(updateWorkerFormat, worker.getDepartment(), worker.getWorkerId());
	}

	@Override
	public Integer updateSalaryByDepartment(String department, Integer bonusFactor) {
		String updateSalaryByDepartmentFormat = """
				UPDATE Worker
				SET
				    SALARY = SALARY * ?
				WHERE
				    WORKER_ID = ?""";
		return jdbcTemplate.update(updateSalaryByDepartmentFormat, department, bonusFactor);
	}

	@Override
	public Integer deleteWorkerById(Integer id) {
		String deleteWorkerFormat = """
				DELETE FROM Worker
				WHERE
				    WORKER_ID = ?""";

		return jdbcTemplate.update(deleteWorkerFormat, id);
	}

	@Override
	public Integer findWorkersCountByDepartment(String department) {

		String getWorkersCountFormat = """
				SELECT count(*)
				FROM
					Worker
				WHERE
					DEPARTMENT = ?""";

		return jdbcTemplate.queryForObject(getWorkersCountFormat, Integer.class, department);
	}

}

WorkerService

package com.spring.jdbc.demo.service;

import java.util.List;

import com.spring.jdbc.demo.dao.WorkerDao;
import com.spring.jdbc.demo.model.Worker;

public class WorkerService {
	private WorkerDao workerRepository;

	WorkerService(WorkerDao workerRepository) {
		super();
		this.workerRepository = workerRepository;
	}

	public Boolean addWorker(Worker worker) {
		Integer recordsInserted = this.workerRepository.createWorker(worker);

		return recordsInserted > 0;
	}

	public Worker getWorker(Integer id) {
		return this.workerRepository.findWorkerById(id);
	}

	public List<Worker> getAllWorkers() {
		return this.workerRepository.findAllWorkers();
	}

	public Boolean updateWorker(String department, Integer id) {
		Worker worker = this.workerRepository.findWorkerById(id);
		worker.setDepartment(department);
		Integer recordsUpdated = this.workerRepository.updateWorker(worker);

		return recordsUpdated > 0;
	}

	public Integer updateSalaryByDepartment(String department, Integer bonusFactor) {
		int updatedRows = this.workerRepository.updateSalaryByDepartment(department, bonusFactor);
		int rowsToBeUpdated = this.workerRepository.findWorkersCountByDepartment(department);

		System.out.println("Rows with Department : " + department + " are :" + rowsToBeUpdated);

		return rowsToBeUpdated - updatedRows;
	}

	public Boolean deleteWorker(Integer id) {
		Integer recordsDeleted = this.workerRepository.deleteWorkerById(id);

		return recordsDeleted > 0;
	}
}

WorkerController

package com.spring.jdbc.demo.controller;

import java.util.List;

import com.spring.jdbc.demo.model.Worker;
import com.spring.jdbc.demo.service.WorkerService;

public class WorkerController {
	private WorkerService workerService;

	WorkerController(WorkerService workerService) {
		this.workerService = workerService;
	}

	public String create(Integer id, String firstName, String lastName, Integer salary, String department) {
		Worker worker = new Worker(id, firstName, lastName, salary, department);
		if (Boolean.TRUE.equals(this.workerService.addWorker(worker))) {
			return "New worker record added successfully";
		}

		return "Failure in inserting new worker record!";
	}

	public String get(Integer id) {
		Worker worker = this.workerService.getWorker(id);
		return worker == null ? "Empty set!" : worker.toString();

	}

	public String getAll() {
		List<Worker> workers = this.workerService.getAllWorkers();

		return workers.isEmpty() ? "Empty set!"
				: workers.stream().map(Worker::toString).reduce("",
						(workerRecords, workerString) -> workerRecords + "\n" + workerString);

	}

	public String update(String department, Integer id) {
		if (Boolean.TRUE.equals(this.workerService.updateWorker(department, id))) {
			return String.format("Record of worker #%d updated successfully", id);
		}

		return String.format("Failure in updating the record of worker #%d!", id);
	}

	public String updateSalaryByDepartment(String department, Integer bonusFactor) {

		if (this.workerService.updateSalaryByDepartment(department, bonusFactor) == 0) {
			return String.format("Updated the Salary for #%s with Bonus Factor #%d", department, bonusFactor);
		} else if (this.workerService.updateSalaryByDepartment(department, bonusFactor) > 0) {
			return "Salaries of Workers are updated partially, Please check the records";
		}

		return String.format("Failure in updating the salary of workers with department #%s!", department);

	}

	public String delete(Integer id) {
		if (Boolean.TRUE.equals(this.workerService.deleteWorker(id))) {
			return String.format("Record of worker #%d deleted successfully", id);
		}

		return String.format("Failure in deleting the record of worker #%d!", id);
	}
}

ApplicationContext.xml

<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:context="http://www.springframework.org/schema/context"
	xsi:schemaLocation="http://www.springframework.org/schema/beans
      http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
      http://www.springframework.org/schema/context
      http://www.springframework.org/schema/context/spring-context-3.0.xsd">

	<bean id="ds"
		class="org.springframework.jdbc.datasource.DriverManagerDataSource"
		lazy-init="true">
		<property name="driverClassName"
			value="com.mysql.cj.jdbc.Driver" />
		<property name="url"
			value="jdbc:mysql://localhost:3306/jdbc_demo" />
		<property name="username" value="root" />
		<property name="password" value="password" />
	</bean>

	<bean id="workerRepository"
		class="com.spring.jdbc.demo.repository.WorkerRepository"
		lazy-init="true">
		<property name="dataSource" ref="ds"></property>
	</bean>

	<bean id="workerService"
		class="com.spring.jdbc.demo.service.WorkerService" lazy-init="true">
		<constructor-arg index="0" ref="workerRepository"></constructor-arg>
	</bean>

	<bean id="workerController"
		class="com.spring.jdbc.demo.controller.WorkerController"
		lazy-init="true">
		<constructor-arg index="0" ref="workerService"></constructor-arg>
	</bean>

</beans>

Driver Class

package com.spring.jdbc.demo;

import org.springframework.context.support.AbstractApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.spring.jdbc.demo.controller.WorkerController;

public class SpringJdbcApplication {

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		AbstractApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");

		context.start();
		System.out.println("Spring JDBC app with MySQL Connector works!\n");

		WorkerController workerController = context.getBean("workerController", WorkerController.class);

		System.out.println("Creating a new worker record::");
		System.out.println(workerController.create(10, "Tony", "Stark", 5000, "R&D") + "\n");

		System.out.println("Retrieving a worker record::");
		System.out.println(workerController.get(10) + "\n");

		System.out.print("Retrieving all worker records::");
		System.out.println(workerController.getAll() + "\n");

		System.out.println("Updating a worker record::");
		System.out.println(workerController.update("Boss", 10));
		System.out.println("Retrieving the worker record after updation::");
		System.out.println(workerController.get(10) + "\n");

		System.out.println("Updating the Salary of Worker By Department ::");
		System.out.println(workerController.updateSalaryByDepartment("HR", 2));
		System.out.println("Retrieving the worker record after updation::");
		System.out.println(workerController.getAll() + "\n");

		System.out.println("Deleting a worker record::");
		System.out.println(workerController.delete(10) + "\n");
		System.out.print("Retrieving all worker records after deletion::");
		System.out.println(workerController.getAll() + "\n");

		context.close();

	}

}

[Anandhi22]

WorkerDao Interface

package com.spring.jdbc.demo.dao;

import java.util.List;

import com.spring.jdbc.demo.model.Worker;

public interface WorkerDao {
	// Create Operation
	Integer createWorker(Worker worker);

	// Read/Retrieve Operations
	Worker findWorkerById(Integer id);

	List<Worker> findAllWorkers();

	Integer findWorkersCountByDepartment(String department);

	// Update Operations
	Integer updateWorker(Worker worker);

	Integer updateSalaryByDepartment(String department, Integer bonusFactor);

	// Delete Operations
	Integer deleteWorkerById(Integer id);
}

WorkerResultSetExtractor

package com.spring.jdbc.demo.mapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;

import com.spring.jdbc.demo.model.Worker;

public class WorkerResultSetExtractor implements ResultSetExtractor<List<Worker>> {

	@Override
	public Worker extractData(ResultSet rs) throws SQLException, DataAccessException {
		// TODO Auto-generated method stub
		
		
			Worker workers = new Worker();
			worker.setWorkerId(rs.getInt("WORKER_ID"));
			worker.setFirstName(rs.getString("FIRST_NAME"));
			worker.setLastName(rs.getString("LAST_NAME"));
			worker.setSalary(rs.getInt("SALARY"));
			worker.setJoiningDate(rs.getTimestamp("JOINING_DATE"));
			worker.setDepartment(rs.getString("DEPARTMENT"));

			workers.add(worker);
		}
		return workers;
	}

}

WorkerRowMapper

package com.spring.jdbc.demo.mapper;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

import com.spring.jdbc.demo.model.Worker;

public class WorkerRowMapper implements RowMapper<Worker> {

	@Override
	public Worker mapRow(ResultSet rs, int rowNum) throws SQLException {
		// TODO Auto-generated method stub
		Worker worker = new Worker();

		worker.setWorkerId(rs.getInt("WORKER_ID"));
		worker.setFirstName(rs.getString("FIRST_NAME"));
		worker.setLastName(rs.getString("LAST_NAME"));
		worker.setSalary(rs.getInt("SALARY"));
		worker.setJoiningDate(rs.getTimestamp("JOINING_DATE"));
		worker.setDepartment(rs.getString("DEPARTMENT"));
		return worker;

	}

}

Worker Repository

package com.spring.jdbc.demo.repository;

import java.util.List;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;

import com.spring.jdbc.demo.dao.WorkerDao;
import com.spring.jdbc.demo.mapper.WorkerResultSetExtractor;
import com.spring.jdbc.demo.mapper.WorkerRowMapper;
import com.spring.jdbc.demo.model.Worker;

public class WorkerRepository implements WorkerDao {

	private DataSource dataSource;
	private JdbcTemplate jdbcTemplate;

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
		this.jdbcTemplate = new JdbcTemplate(this.dataSource);
	}

	@Override
	public Integer createWorker(Worker worker) {
		String insertWorkerFormat = """
				INSERT INTO Worker (
				    WORKER_ID,
				    FIRST_NAME,
				    LAST_NAME,
				    SALARY,
				    JOINING_DATE,
				    DEPARTMENT)
				VALUES (
				    ?, ?, ?, ?, NOW(), ?)""";

		return jdbcTemplate.update(insertWorkerFormat, worker.getWorkerId(), worker.getFirstName(),
				worker.getLastName(), worker.getSalary(), worker.getDepartment());

	}

	@Override
	public Worker findWorkerById(Integer id) {

		String getWorkerFormat = """
				SELECT *
				FROM
				    Worker
				WHERE
				    WORKER_ID = ?""";

		return jdbcTemplate.queryForObject(getWorkerFormat, new Object[] { id }, new WorkerRowMapper());
	}

	@Override
	public List<Worker> findAllWorkers() {
		String getWorkersFormat = """
				SELECT *
				FROM
				    Worker""";

		return jdbcTemplate.query(getWorkersFormat, new WorkerResultSetExtractor());
	}

	@Override
	public Integer updateWorker(Worker worker) {
		String updateWorkerFormat = """
				UPDATE Worker
				SET
				    DEPARTMENT = ?
				WHERE
				    WORKER_ID = ?""";

		return jdbcTemplate.update(updateWorkerFormat, worker.getDepartment(), worker.getWorkerId());
	}

	@Override
	public Integer updateSalaryByDepartment(String department, Integer bonusFactor) {
		String updateSalaryByDepartmentFormat = """
				UPDATE Worker
				SET
				    SALARY = SALARY * ?
				WHERE
				    WORKER_ID = ?""";
		return jdbcTemplate.update(updateSalaryByDepartmentFormat, department, bonusFactor);
	}

	@Override
	public Integer deleteWorkerById(Integer id) {
		String deleteWorkerFormat = """
				DELETE FROM Worker
				WHERE
				    WORKER_ID = ?""";

		return jdbcTemplate.update(deleteWorkerFormat, id);
	}

	@Override
	public Integer findWorkersCountByDepartment(String department) {

		String getWorkersCountFormat = """
				SELECT count(*)
				FROM
					Worker
				WHERE
					DEPARTMENT = ?""";

		return jdbcTemplate.queryForObject(getWorkersCountFormat, Integer.class, department);
	}

}

WorkerService

package com.spring.jdbc.demo.service;

import java.util.List;

import com.spring.jdbc.demo.dao.WorkerDao;
import com.spring.jdbc.demo.model.Worker;

public class WorkerService {
	private WorkerDao workerRepository;

	WorkerService(WorkerDao workerRepository) {
		super();
		this.workerRepository = workerRepository;
	}

	public Boolean addWorker(Worker worker) {
		Integer recordsInserted = this.workerRepository.createWorker(worker);

		return recordsInserted > 0;
	}

	public Worker getWorker(Integer id) {
		return this.workerRepository.findWorkerById(id);
	}

	public List<Worker> getAllWorkers() {
		return this.workerRepository.findAllWorkers();
	}

	public Boolean updateWorker(String department, Integer id) {
		Worker worker = this.workerRepository.findWorkerById(id);
		worker.setDepartment(department);
		Integer recordsUpdated = this.workerRepository.updateWorker(worker);

		return recordsUpdated > 0;
	}

	public Integer updateSalaryByDepartment(String department, Integer bonusFactor) {
		int updatedRows = this.workerRepository.updateSalaryByDepartment(department, bonusFactor);
		int rowsToBeUpdated = this.workerRepository.findWorkersCountByDepartment(department);

		System.out.println("Rows with Department : " + department + " are :" + rowsToBeUpdated);

		return rowsToBeUpdated - updatedRows;
	}

	public Boolean deleteWorker(Integer id) {
		Integer recordsDeleted = this.workerRepository.deleteWorkerById(id);

		return recordsDeleted > 0;
	}
}

WorkerController

package com.spring.jdbc.demo.controller;

import java.util.List;

import com.spring.jdbc.demo.model.Worker;
import com.spring.jdbc.demo.service.WorkerService;

public class WorkerController {
	private WorkerService workerService;

	WorkerController(WorkerService workerService) {
		this.workerService = workerService;
	}

	public String create(Integer id, String firstName, String lastName, Integer salary, String department) {
		Worker worker = new Worker(id, firstName, lastName, salary, department);
		if (Boolean.TRUE.equals(this.workerService.addWorker(worker))) {
			return "New worker record added successfully";
		}

		return "Failure in inserting new worker record!";
	}

	public String get(Integer id) {
		Worker worker = this.workerService.getWorker(id);
		return worker == null ? "Empty set!" : worker.toString();

	}

	public String getAll() {
		List<Worker> workers = this.workerService.getAllWorkers();

		return workers.isEmpty() ? "Empty set!"
				: workers.stream().map(Worker::toString).reduce("",
						(workerRecords, workerString) -> workerRecords + "\n" + workerString);

	}

	public String update(String department, Integer id) {
		if (Boolean.TRUE.equals(this.workerService.updateWorker(department, id))) {
			return String.format("Record of worker #%d updated successfully", id);
		}

		return String.format("Failure in updating the record of worker #%d!", id);
	}

	public String updateSalaryByDepartment(String department, Integer bonusFactor) {

		if (this.workerService.updateSalaryByDepartment(department, bonusFactor) == 0) {
			return String.format("Updated the Salary for #%s with Bonus Factor #%d", department, bonusFactor);
		} else if (this.workerService.updateSalaryByDepartment(department, bonusFactor) > 0) {
			return "Salaries of Workers are updated partially, Please check the records";
		}

		return String.format("Failure in updating the salary of workers with department #%s!", department);

	}

	public String delete(Integer id) {
		if (Boolean.TRUE.equals(this.workerService.deleteWorker(id))) {
			return String.format("Record of worker #%d deleted successfully", id);
		}

		return String.format("Failure in deleting the record of worker #%d!", id);
	}
}

ApplicationContext.xml

<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:context="http://www.springframework.org/schema/context"
	xsi:schemaLocation="http://www.springframework.org/schema/beans
      http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
      http://www.springframework.org/schema/context
      http://www.springframework.org/schema/context/spring-context-3.0.xsd">

	<bean id="ds"
		class="org.springframework.jdbc.datasource.DriverManagerDataSource"
		lazy-init="true">
		<property name="driverClassName"
			value="com.mysql.cj.jdbc.Driver" />
		<property name="url"
			value="jdbc:mysql://localhost:3306/jdbc_demo" />
		<property name="username" value="root" />
		<property name="password" value="password" />
	</bean>

	<bean id="workerRepository"
		class="com.spring.jdbc.demo.repository.WorkerRepository"
		lazy-init="true">
		<property name="dataSource" ref="ds"></property>
	</bean>

	<bean id="workerService"
		class="com.spring.jdbc.demo.service.WorkerService" lazy-init="true">
		<constructor-arg index="0" ref="workerRepository"></constructor-arg>
	</bean>

	<bean id="workerController"
		class="com.spring.jdbc.demo.controller.WorkerController"
		lazy-init="true">
		<constructor-arg index="0" ref="workerService"></constructor-arg>
	</bean>

</beans>

Driver Class

package com.spring.jdbc.demo;

import org.springframework.context.support.AbstractApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.spring.jdbc.demo.controller.WorkerController;

public class SpringJdbcApplication {

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		AbstractApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");

		context.start();
		System.out.println("Spring JDBC app with MySQL Connector works!\n");

		WorkerController workerController = context.getBean("workerController", WorkerController.class);

		System.out.println("Creating a new worker record::");
		System.out.println(workerController.create(10, "Tony", "Stark", 5000, "R&D") + "\n");

		System.out.println("Retrieving a worker record::");
		System.out.println(workerController.get(10) + "\n");

		System.out.print("Retrieving all worker records::");
		System.out.println(workerController.getAll() + "\n");

		System.out.println("Updating a worker record::");
		System.out.println(workerController.update("Boss", 10));
		System.out.println("Retrieving the worker record after updation::");
		System.out.println(workerController.get(10) + "\n");

		System.out.println("Updating the Salary of Worker By Department ::");
		System.out.println(workerController.updateSalaryByDepartment("HR", 2));
		System.out.println("Retrieving the worker record after updation::");
		System.out.println(workerController.getAll() + "\n");

		System.out.println("Deleting a worker record::");
		System.out.println(workerController.delete(10) + "\n");
		System.out.print("Retrieving all worker records after deletion::");
		System.out.println(workerController.getAll() + "\n");

		context.close();

	}

}

[Thiripurasundari-1223]

``

Bonus Controller

public class BonusController {

	private BonusService bonusService;

	BonusController(BonusService bonusService) {
		this.bonusService =bonusService;
	}

	public String create(Integer workerRefId, String bonusAmount) {
		Bonus bonus = new Bonus(workerRefId, bonusAmount);
		try {
			this.bonusService.addBonus(bonus);
			return "New Bonus record added successfully";

		} catch (MyResourceNotCreatedException e) {

			return e.getMessage();
		}

	}

	public String get(Integer id) {
		try {

			return this.bonusService.getBonus(id).toString();
		} catch (MyResourceNotFoundException e) {
			return e.getMessage();
		}

	}
}

Bonus Service

public class BonusService {
	private BonusDao bonusRepository;

	BonusService(BonusDao bonusRepository) {
		super();
		this.bonusRepository = bonusRepository;
	}
	
	public void addBonus(Bonus bonus) throws MyResourceNotCreatedException{
		
		try {
			Integer recordsInserted = this.bonusRepository.createBonus(bonus);
			if (recordsInserted == 0)
				throw new MyResourceNotCreatedException("Couldn't create bonus for new worker record!");
	
		}catch(DataAccessException e) {
			e.printStackTrace();
			throw new MyResourceNotCreatedException("Something went wrong when creating new bonus record!");
		}
	}
	

	public Bonus getBonus(Integer id) throws MyResourceNotFoundException {
		Bonus bonus= this.bonusRepository.findBonusById(id);
		try {
			if (bonus == null)
				throw new MyResourceNotFoundException(String.format("No Bonus found with ID %d!", id));
			return bonus;
		} catch (DataAccessException e) {
			throw new MyResourceNotFoundException("Something went wrong when fetching the Bonus record!");
		}

	}

	
}

Bonus Repository

public class BonusRepository implements BonusDao {
	private DataSource datasource;
	private JdbcTemplate jdbcTemplate;

	public void setDataSource(DataSource datasource) {
		this.datasource = datasource;
		this.jdbcTemplate = new JdbcTemplate(this.datasource);
	}

	@Override
	public Integer createBonus(Bonus bonus) {
		System.out.println("coming ");
		String insertWorkerFormat = """
				INSERT INTO Bonus (
				    WORKER_REF_ID,
				   BONUS_AMOUNT,
				   BONUS_DATE
				  )
				VALUES (
				    ?, ?,  NOW()
				    )""";
		return jdbcTemplate.update(insertWorkerFormat, bonus.getWorkerRefId(),bonus.getBonusAmount());
	}

	@Override
	public Bonus findBonusById(Integer id) {
		String getBonusFormat = """
				SELECT *
				FROM
				    Bonus
				WHERE
				    WORKER_REF_ID = ?""";
		return jdbcTemplate.queryForObject(getBonusFormat, new Object[] {id}, new BonusRowMapper());

	}
}

Bonus Dao

public interface BonusDao {

	Integer createBonus(Bonus bonus);
	
	Bonus findBonusById(Integer id);
}

applicationContext.xml

<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:context="http://www.springframework.org/schema/context"
	xsi:schemaLocation="http://www.springframework.org/schema/beans
      http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
      http://www.springframework.org/schema/context
      http://www.springframework.org/schema/context/spring-context-3.0.xsd">

<bean id="dataSource"
		class="org.springframework.jdbc.datasource.DriverManagerDataSource"
		scope="singleton" lazy-init="true">
		<constructor-arg index="0"
			value="jdbc:mysql://localhost:3306/jdbc_demo"></constructor-arg>
		<constructor-arg index="1" value="root"></constructor-arg>
		<constructor-arg index="2" value="admin@123"></constructor-arg>
	</bean>
<bean id="bonusRepository"
		class="com.spring.jdbc.n.tier.demo.repository.BonusRepository"
		lazy-init="true">
		<property name="dataSource" ref="dataSource"></property>
	</bean>

	<bean id="bonusService"
		class="com.spring.jdbc.n.tier.demo.service.BonusService" lazy-init="true">
		<constructor-arg index="0" ref="bonusRepository"></constructor-arg>
	</bean>
	<bean id="bonusController"
		class="com.spring.jdbc.n.tier.demo.controller.BonusController"
		lazy-init="true">
		<constructor-arg index="0" ref="bonusService"></constructor-arg>
	</bean>
</beans>

BonusRowMapper

public class BonusRowMapper implements RowMapper<Bonus> {

		Bonus bonus = new Bonus();

		@Override
		public Bonus mapRow(ResultSet rs, int rowNum) throws SQLException {
			bonus.setWorkerRefId(rs.getInt("WORKER_REF_ID"));
			bonus.setBonusAmount(rs.getString("BONUS_AMOUNT"));
			bonus.setBonusDate(rs.getTimestamp("BONUS_DATE"));
				return bonus;
		}	
	
}

Driver Class

public class MySqlConnectorNTierApplication {

	public static void main(String[] args) {
	
                AbstractApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
		context.start();
		System.out.println("Spring JDBC app with MySQL Connector works!\n");

BonusController bonusController = context.getBean("bonusController", BonusController.class);

		System.out.println(bonusController.create(6, "100000"));
		System.out.println(bonusController.get(6));
		context.close(); 

Worker Controller

public class WorkerController {
	private WorkerService workerService;
	
	WorkerController(WorkerService workerService) {
		this.workerService = workerService;
	}
	
	public String create(Integer id, String firstName, String lastName, Integer salary, String department) {
		Worker worker = new Worker(id, firstName, lastName, salary, department);
		try {
			if (Boolean.TRUE.equals(this.workerService.addWorker(worker))) {
				return "New worker record added successfully";
			} 
		} catch (SQLException ex) {
			System.out.println("Exception occurred while inserting a new worker record!\n" + ex);
		}
		
		return "Failure in inserting new worker record!";
	}
	
	public String get(Integer id) {
		try {
			Worker worker = this.workerService.getWorker(id);
			return worker == null ? "Empty set!" : worker.toString();
		} catch (SQLException ex) {
			System.out.println("Exception occurred while fetching the record of worker #" + id + "!\n" + ex);
		}
		
		return "Something went wrong while fetchingthe record of worker #" + id ;
	}	
	
	public String getAll() {
		try {
			List<Worker> workers = this.workerService.getAllWorkers();
		
			return workers.isEmpty() ? "Empty set!" : workers.toString();
			
		} catch (SQLException ex) {
			System.out.println("Exception occurred while fetching all worker records!\n" + ex);
		}
		
		return "Something went wrong while fetching the record of all worker";
	}
	
	public String update(String department, Integer id) {
		try {
			if (Boolean.TRUE.equals(this.workerService.updateWorker(department, id))) {
				return String.format("Record of worker #%d updated successfully", id);
			} 
		} catch (SQLException ex) {
			System.out.println("Exception occurred while updating the record of worker #" + id + "!\n" + ex);
		}
		
		return String.format("Failure in updating the record of worker #%d!", id);
	}
	
	public String delete(Integer id) {
		try {
			if (Boolean.TRUE.equals(this.workerService.deleteWorker(id))) {
				return String.format("Record of worker #%d deleted successfully", id);
			} 
		} catch (SQLException ex) {
			System.out.println("Exception occurred while deleting the record of worker #" + id + "!\n" + ex);
		}
		
		return String.format("Failure in deleting the record of worker #%d!", id);
	}

	public String updateSalary(String department, Integer bonus) {
		try {
			if(Boolean.TRUE.equals(this.workerService.updateWorkerSalary(department,bonus))){
				return String.format("Bonus Updated for:"+department);
			}
		} catch (SQLException ex) {
			System.out.println("Exception occurred while Updating the record of worker salary#" + "!\n" + ex);
		}
		return String.format("Failure in updating the record of worker salary");
		
	}
}

Worker Model

public class Worker implements Comparable<Worker>{
	Integer WorkerId;
	String FirstName;
	String LastName;
	Integer Salary;
	Timestamp JoiningDate;
	String Department;
	

	public Worker() {
		super();
	}
	public Worker(Integer workerId, String firstName, String lastName, Integer salary,
			String department) {
		super();
		WorkerId = workerId;
		FirstName = firstName;
		LastName = lastName;
		Salary = salary;
		Department = department;
	}
	public Worker(Integer workerId, String firstName, String lastName, Integer salary, Timestamp joiningDate,
			String department) {
		super();
		WorkerId = workerId;
		FirstName = firstName;
		LastName = lastName;
		Salary = salary;
		JoiningDate = joiningDate;
		Department = department;
	}

	public Integer getWorkerId() {
		return WorkerId;
	}


	public void setWorkerId(Integer workerId) {
		WorkerId = workerId;
	}


	public String getFirstName() {
		return FirstName;
	}


	public void setFirstName(String firstName) {
		FirstName = firstName;
	}


	public String getLastName() {
		return LastName;
	}


	public void setLastName(String lastName) {
		LastName = lastName;
	}


	public Integer getSalary() {
		return Salary;
	}


	public void setSalary(Integer salary) {
		Salary = salary;
	}


	public Timestamp getJoiningDate() {
		return JoiningDate;
	}


	public void setJoiningDate(Timestamp timestamp) {
		JoiningDate = timestamp;
	}


	public String getDepartment() {
		return Department;
	}


	public void setDepartment(String department) {
		Department = department;
	}

	
	
	@Override
	public String toString() {
		return "Worker [WorkerId=" + WorkerId + ", FirstName=" + FirstName + ", LastName=" + LastName + ", Salary="
				+ Salary + ", JoiningDate=" + JoiningDate + ", Department=" + Department + "]";
	}

	
	@Override
	public int hashCode() {
		return Objects.hash(Department, WorkerId);
	}

	@Override
	public boolean equals(Object obj) {
		if (this == obj)
			return true;
		if (obj == null)
			return false;
		if (getClass() != obj.getClass())
			return false;
		Worker other = (Worker) obj;
		if (WorkerId ==null || other.WorkerId==null)
			return false;
		if (Department ==null || other.Department==null)
			return false;
		if(WorkerId!=other.WorkerId)
			return false;
		if(Department!=other.Department)
			return false;
		
		return Objects.equals(Department, other.Department) && Objects.equals(WorkerId, other.WorkerId);
	}

	@Override
	public int compareTo(Worker o) {
		if (this.WorkerId<o.WorkerId) 
			return -1;
		if (this.WorkerId>o.WorkerId) 
			return 1;
		if (this.WorkerId==o.WorkerId) 
			return 0;
		return this.Department.compareTo(o.Department);
	}

	
}

Worker Service

public class WorkerService {
	private WorkerDao workerRepository;
	
	WorkerService(WorkerDao workerRepository) {
		super();
		this.workerRepository = workerRepository;
	}
	
	public Boolean addWorker(Worker worker) throws SQLException {
		Integer recordsInserted = this.workerRepository.createWorker(worker);
		
		return recordsInserted > 0;
	}
	
	public Worker getWorker(Integer id) throws SQLException {
		return this.workerRepository.findWorkerById(id);
	}

	public List<Worker> getAllWorkers() throws SQLException {
		return this.workerRepository.findAllWorkers();
	}
	
	
	public Boolean updateWorker(String department, Integer id) throws SQLException {
		Worker worker = this.workerRepository.findWorkerById(id);
		worker.setDepartment(department);
		Integer recordsUpdated = this.workerRepository.updateWorker(worker);
		
		return recordsUpdated > 0;
	}
	public Boolean updateWorkerSalary(String department, Integer bonusFactor) throws SQLException {
		
		Integer recordsUpdated = this.workerRepository.updateSalaryByDepartment(department,bonusFactor);
		return recordsUpdated > 0;
	}
	
	public Boolean deleteWorker(Integer id) throws SQLException {
		Integer recordsDeleted = this.workerRepository.deleteWorkerById(id);
		
		return recordsDeleted > 0;
	}
}

WorkerDAO

public interface WorkerDao {

	void setDataSource(DataSource datasource);

	Integer deleteWorkerById(Integer id);

	Integer updateSalaryByDepartment(String department, Integer bonusFactor);

	Integer updateWorker(Worker worker);

	List<Worker> findAllWorkers();

	Worker findWorkerById(Integer id);
	
	Integer createWorker(Worker worker);

}

WorkerRepository

package com.spring.jdbc.demo.repository;


import java.util.List;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;

import com.spring.jdbc.demo.dao.WorkerDao;
import com.spring.jdbc.demo.mapper.WorkerRowMapper;
import com.spring.jdbc.demo.model.Worker;

public class WorkerRepository implements WorkerDao {
	private DataSource datasource;
	private JdbcTemplate jdbcTemplate;

	public void setDataSource(DataSource datasource) {
		this.datasource = datasource;
		jdbcTemplate = new JdbcTemplate(this.datasource);
	}

	@Override
	public Integer createWorker(Worker worker) {
		String insertWorkerFormat = """
				INSERT INTO Worker (
				    WORKER_ID,
				    FIRST_NAME,
				    LAST_NAME,
				    SALARY,
				    JOINING_DATE,
				    DEPARTMENT)
				VALUES (
				    ?, ?, ?, ?, NOW(), ?
				    )""";
		return jdbcTemplate.update(insertWorkerFormat, worker.getWorkerId(), worker.getFirstName(),
				worker.getLastName(), worker.getSalary(), worker.getDepartment());
	}

	@Override
	public Worker findWorkerById(Integer id) {
		
		String getWorkerFormat = """
				SELECT *
				FROM
				    Worker
				WHERE
				    WORKER_ID = ?""";
		return jdbcTemplate.queryForObject(getWorkerFormat, new Object[] { id }, new WorkerRowMapper());

	}
	
	@Override
	public Worker findWorkersByDepartment(String department) {
		// Read (Retrieve) Operation using PreparedStatement
		String getWorkerByDepartmentFormat = """
				SELECT *
				FROM
				    Worker
				WHERE
				    department = ?
				    """;
		return  jdbcTemplate.queryForObject(getWorkerByDepartmentFormat, new Object[] { department},new WorkerRowMapper());

	}
	
	@Override
	public List<Worker> findAllWorkers() {
		String getWorkersFormat = """
				SELECT *
				FROM
				    Worker""";

		return (List<Worker>) jdbcTemplate.query(getWorkersFormat, new WorkerRowMapper());

	}

	@Override
	public Integer updateWorker(Worker worker) {
		String updateWorkerFormat = """
				UPDATE Worker
				SET
				    WORKER_ID = ?,
				    FIRST_NAME = ?,
				    LAST_NAME = ?,
				    SALARY = ?,
				    JOINING_DATE = ?,
				    DEPARTMENT = ?
				WHERE
				    WORKER_ID = ?""";

		return jdbcTemplate.update(updateWorkerFormat, worker.getWorkerId(), worker.getFirstName(),
				worker.getLastName(), worker.getSalary(), worker.getJoiningDate(), worker.getDepartment(),
				worker.getWorkerId());

	}

	@Override
	public Integer updateSalaryByDepartment(String department, Integer bonusFactor) {

		String UpdateBonusBySelectFormat = """
				update worker
				set
				salary =salary * ?
				WHERE
				department=?""";

		return jdbcTemplate.update(UpdateBonusBySelectFormat, bonusFactor, department);

	}

	@Override
	public Integer deleteWorkerById(Integer id) {
		String deleteWorkerFormat = """
				DELETE FROM Worker
				WHERE
				    WORKER_ID = ?""";

		return jdbcTemplate.update(deleteWorkerFormat, id);

	}
}

applicationContext.xml

<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:context="http://www.springframework.org/schema/context"
	xsi:schemaLocation="http://www.springframework.org/schema/beans
      http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
      http://www.springframework.org/schema/context
      http://www.springframework.org/schema/context/spring-context-3.0.xsd">

	<bean id="dataSource"
		class="org.springframework.jdbc.datasource.DriverManagerDataSource"
		scope="singleton" lazy-init="true">
		<constructor-arg index="0"
			value="jdbc:mysql://localhost:3306/jdbc_demo"></constructor-arg>
		<constructor-arg index="1" value="root"></constructor-arg>
		<constructor-arg index="2" value="admin@123"></constructor-arg>
	</bean>


	<bean id="workerRepository"
		class="com.spring.jdbc.demo.repository.WorkerRepository"
		lazy-init="true">
		<property name="dataSource" ref="dataSource"></property>
	</bean>

	<bean id="workerService"
		class="com.spring.jdbc.demo.service.WorkerService" lazy-init="true">
		<constructor-arg index="0" ref="workerRepository"></constructor-arg>
	</bean>
	<bean id="workerController"
		class="com.spring.jdbc.demo.controller.WorkerController"
		lazy-init="true">
		<constructor-arg index="0" ref="workerService"></constructor-arg>
	</bean>
</beans>

Driver

public class MySqlConnectorNTierApplication {

	public static void main(String[] args) {
		AbstractApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
		
		context.start();
		System.out.println("Spring JDBC app with MySQL Connector works!\n");
		
		WorkerController workerController = context.getBean("workerController", WorkerController.class);
		
		System.out.println("Creating a new worker record::");
		System.out.println(workerController.create(10, "Tony", "Stark", 50000000, "R&D") + "\n");
		
		System.out.println("Retrieving a worker record::");
		System.out.println(workerController.get(10) + "\n");
		
		System.out.println("Retrieving all worker records::");
		System.out.println(workerController.getAll() + "\n");
		
		System.out.println("Updating a worker record::");
		System.out.println(workerController.update("Boss", 10));
		
		//System.out.println("Adding Bonus for Hr record::");
		System.out.println(workerController.updateSalary("HR", 2));
		
		//System.out.println(workerController.getWorkerByDepartment("HR"));
		
			
		System.out.println("Retrieving the worker record after updation of Bonus::");
		System.out.println(workerController.get(1) + "\n");
		System.out.println(workerController.get(3) + "\n");
		
		
		System.out.println("Deleting a worker record::");
		System.out.println(workerController.delete(10) + "\n");
		
		context.close();
	}

}

[Kalairajavadivel]

WorkerDao

package com.spring.jdbc.demo.dao;

import java.util.List;

import com.spring.jdbc.demo.model.Worker;

public interface WorkerDao {
	// Create Operation
	Integer createWorker(Worker worker);

	// Read/Retrieve Operations
	Worker findWorkerById(Integer id);

	List<Worker> findAllWorkers();

	Integer findWorkersCountByDepartment(String department);

	// Update Operations
	Integer updateWorker(Worker worker);

	Integer updateSalaryByDepartment(String department, Integer bonusFactor);

	// Delete Operations
	Integer deleteWorkerById(Integer id);
}

[Kalairajavadivel]

WorkerResultSetExtractor

package com.spring.jdbc.demo.mapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;

import com.spring.jdbc.demo.model.Worker;

public class WorkerResultSetExtractor implements ResultSetExtractor<List<Worker>> {

	@Override
	public Worker extractData(ResultSet rs) throws SQLException, DataAccessException {
		// TODO Auto-generated method stub
		
		
			Worker workers = new Worker();
			worker.setWorkerId(rs.getInt("WORKER_ID"));
			worker.setFirstName(rs.getString("FIRST_NAME"));
			worker.setLastName(rs.getString("LAST_NAME"));
			worker.setSalary(rs.getInt("SALARY"));
			worker.setJoiningDate(rs.getTimestamp("JOINING_DATE"));
			worker.setDepartment(rs.getString("DEPARTMENT"));

			workers.add(worker);
		}
		return workers;
	}

}

[Kalairajavadivel]

WorkerRepository

package com.spring.jdbc.demo.repository;

import java.util.List;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;

import com.spring.jdbc.demo.dao.WorkerDao;
import com.spring.jdbc.demo.mapper.WorkerResultSetExtractor;
import com.spring.jdbc.demo.mapper.WorkerRowMapper;
import com.spring.jdbc.demo.model.Worker;

public class WorkerRepository implements WorkerDao {

	private DataSource dataSource;
	private JdbcTemplate jdbcTemplate;

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
		this.jdbcTemplate = new JdbcTemplate(this.dataSource);
	}

	@Override
	public Integer createWorker(Worker worker) {
		String insertWorkerFormat = """
				INSERT INTO Worker (
				    WORKER_ID,
				    FIRST_NAME,
				    LAST_NAME,
				    SALARY,
				    JOINING_DATE,
				    DEPARTMENT)
				VALUES (
				    ?, ?, ?, ?, NOW(), ?)""";

		return jdbcTemplate.update(insertWorkerFormat, worker.getWorkerId(), worker.getFirstName(),
				worker.getLastName(), worker.getSalary(), worker.getDepartment());

	}

	@Override
	public Worker findWorkerById(Integer id) {

		String getWorkerFormat = """
				SELECT *
				FROM
				    Worker
				WHERE
				    WORKER_ID = ?""";

		return jdbcTemplate.queryForObject(getWorkerFormat, new Object[] { id }, new WorkerRowMapper());
	}

	@Override
	public List<Worker> findAllWorkers() {
		String getWorkersFormat = """
				SELECT *
				FROM
				    Worker""";

		return jdbcTemplate.query(getWorkersFormat, new WorkerResultSetExtractor());
	}

	@Override
	public Integer updateWorker(Worker worker) {
		String updateWorkerFormat = """
				UPDATE Worker
				SET
				    DEPARTMENT = ?
				WHERE
				    WORKER_ID = ?""";

		return jdbcTemplate.update(updateWorkerFormat, worker.getDepartment(), worker.getWorkerId());
	}

	@Override
	public Integer updateSalaryByDepartment(String department, Integer bonusFactor) {
		String updateSalaryByDepartmentFormat = """
				UPDATE Worker
				SET
				    SALARY = SALARY * ?
				WHERE
				    WORKER_ID = ?""";
		return jdbcTemplate.update(updateSalaryByDepartmentFormat, department, bonusFactor);
	}

	@Override
	public Integer deleteWorkerById(Integer id) {
		String deleteWorkerFormat = """
				DELETE FROM Worker
				WHERE
				    WORKER_ID = ?""";

		return jdbcTemplate.update(deleteWorkerFormat, id);
	}

	@Override
	public Integer findWorkersCountByDepartment(String department) {

		String getWorkersCountFormat = """
				SELECT count(*)
				FROM
					Worker
				WHERE
					DEPARTMENT = ?""";

		return jdbcTemplate.queryForObject(getWorkersCountFormat, Integer.class, department);
	}
}

[Kalairajavadivel]

WorkerService

package com.spring.jdbc.demo.service;

import java.util.List;

import com.spring.jdbc.demo.dao.WorkerDao;
import com.spring.jdbc.demo.model.Worker;

public class WorkerService {
	private WorkerDao workerRepository;

	WorkerService(WorkerDao workerRepository) {
		super();
		this.workerRepository = workerRepository;
	}

	public Boolean addWorker(Worker worker) {
		Integer recordsInserted = this.workerRepository.createWorker(worker);

		return recordsInserted > 0;
	}

	public Worker getWorker(Integer id) {
		return this.workerRepository.findWorkerById(id);
	}

	public List<Worker> getAllWorkers() {
		return this.workerRepository.findAllWorkers();
	}

	public Boolean updateWorker(String department, Integer id) {
		Worker worker = this.workerRepository.findWorkerById(id);
		worker.setDepartment(department);
		Integer recordsUpdated = this.workerRepository.updateWorker(worker);

		return recordsUpdated > 0;
	}

	public Integer updateSalaryByDepartment(String department, Integer bonusFactor) {
		int updatedRows = this.workerRepository.updateSalaryByDepartment(department, bonusFactor);
		int rowsToBeUpdated = this.workerRepository.findWorkersCountByDepartment(department);

		System.out.println("Rows with Department : " + department + " are :" + rowsToBeUpdated);

		return rowsToBeUpdated - updatedRows;
	}

	public Boolean deleteWorker(Integer id) {
		Integer recordsDeleted = this.workerRepository.deleteWorkerById(id);

		return recordsDeleted > 0;
	}
}

[Kalairajavadivel]

WorkerController

package com.spring.jdbc.demo.controller;

import java.util.List;

import com.spring.jdbc.demo.model.Worker;
import com.spring.jdbc.demo.service.WorkerService;

public class WorkerController {
	private WorkerService workerService;

	WorkerController(WorkerService workerService) {
		this.workerService = workerService;
	}

	public String create(Integer id, String firstName, String lastName, Integer salary, String department) {
		Worker worker = new Worker(id, firstName, lastName, salary, department);
		if (Boolean.TRUE.equals(this.workerService.addWorker(worker))) {
			return "New worker record added successfully";
		}

		return "Failure in inserting new worker record!";
	}

	public String get(Integer id) {
		Worker worker = this.workerService.getWorker(id);
		return worker == null ? "Empty set!" : worker.toString();

	}

	public String getAll() {
		List<Worker> workers = this.workerService.getAllWorkers();

		return workers.isEmpty() ? "Empty set!"
				: workers.stream().map(Worker::toString).reduce("",
						(workerRecords, workerString) -> workerRecords + "\n" + workerString);

	}

	public String update(String department, Integer id) {
		if (Boolean.TRUE.equals(this.workerService.updateWorker(department, id))) {
			return String.format("Record of worker #%d updated successfully", id);
		}

		return String.format("Failure in updating the record of worker #%d!", id);
	}

	public String updateSalaryByDepartment(String department, Integer bonusFactor) {

		if (this.workerService.updateSalaryByDepartment(department, bonusFactor) == 0) {
			return String.format("Updated the Salary for #%s with Bonus Factor #%d", department, bonusFactor);
		} else if (this.workerService.updateSalaryByDepartment(department, bonusFactor) > 0) {
			return "Salaries of Workers are updated partially, Please check the records";
		}

		return String.format("Failure in updating the salary of workers with department #%s!", department);

	}

	public String delete(Integer id) {
		if (Boolean.TRUE.equals(this.workerService.deleteWorker(id))) {
			return String.format("Record of worker #%d deleted successfully", id);
		}

		return String.format("Failure in deleting the record of worker #%d!", id);
	}
}

[Kalairajavadivel]

ApplicationContext.xml

<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:context="http://www.springframework.org/schema/context"
	xsi:schemaLocation="http://www.springframework.org/schema/beans
      http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
      http://www.springframework.org/schema/context
      http://www.springframework.org/schema/context/spring-context-3.0.xsd">

	<bean id="ds"
		class="org.springframework.jdbc.datasource.DriverManagerDataSource"
		lazy-init="true">
		<property name="driverClassName"
			value="com.mysql.cj.jdbc.Driver" />
		<property name="url"
			value="jdbc:mysql://localhost:3306/jdbc_demo" />
		<property name="username" value="root" />
		<property name="password" value="Password@123" />
	</bean>

	<bean id="workerRepository"
		class="com.spring.jdbc.demo.repository.WorkerRepository"
		lazy-init="true">
		<property name="dataSource" ref="ds"></property>
	</bean>

	<bean id="workerService"
		class="com.spring.jdbc.demo.service.WorkerService" lazy-init="true">
		<constructor-arg index="0" ref="workerRepository"></constructor-arg>
	</bean>

	<bean id="workerController"
		class="com.spring.jdbc.demo.controller.WorkerController"
		lazy-init="true">
		<constructor-arg index="0" ref="workerService"></constructor-arg>
	</bean>

</beans>

[Kalairajavadivel]

DriverClassMain

package com.spring.jdbc.demo;

import org.springframework.context.support.AbstractApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.spring.jdbc.demo.controller.WorkerController;

public class SpringJdbcApplication {

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		AbstractApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");

		context.start();
		System.out.println("Spring JDBC app with MySQL Connector works!\n");

		WorkerController workerController = context.getBean("workerController", WorkerController.class);

		System.out.println("Creating a new worker record::");
		System.out.println(workerController.create(10, "Tony", "Stark", 5000, "R&D") + "\n");

		System.out.println("Retrieving a worker record::");
		System.out.println(workerController.get(10) + "\n");

		System.out.print("Retrieving all worker records::");
		System.out.println(workerController.getAll() + "\n");

		System.out.println("Updating a worker record::");
		System.out.println(workerController.update("Boss", 10));
		System.out.println("Retrieving the worker record after updation::");
		System.out.println(workerController.get(10) + "\n");

		System.out.println("Updating the Salary of Worker By Department ::");
		System.out.println(workerController.updateSalaryByDepartment("HR", 2));
		System.out.println("Retrieving the worker record after updation::");
		System.out.println(workerController.getAll() + "\n");

		System.out.println("Deleting a worker record::");
		System.out.println(workerController.delete(10) + "\n");
		System.out.print("Retrieving all worker records after deletion::");
		System.out.println(workerController.getAll() + "\n");

		context.close();

	}

}