How To Build a REST Webservice Using Spring Boot and JdbcTemplate.

Talk to EasyAssistant

This page shows how to build a REST Webservice using Springboot and Spring JdbcTemplate. ..

Springboot is commonly used to build REST service with in Spring Framework. In real business application REST service needs to send data in the response to the client and data comes from database. Or it needs to receive a request and update database table records. Mainly it needs to intaract with database. Here we will learn how to build a REST webserice which will insert records in multiple database table using springjdbctemplate.
Following example is given to show how to build a REST webservice with Springboot and Spring JDBC Template.

Link is given at the end of this page to download the entire project code

---

---

Service Description: It will accept a JSON Request and insert record in two tables. Its mainly a create REST API/Service
Service Input JSON:

 {
    "Designation": "Manager",
    "Department": "HRD",
    "FirstName": "Frank",
    "Dependants": {"Dependant": [
        {
            "Relation": "Father",
            "Age": 70,
            "Location": "London",
            "Name": "George Moor"
        },
        {
            "Relation": "Mother",
            "Age": 65,
            "Location": "London",
            "Name": "Sara Moor"
        }
    ]},
    "EmployeeNo": 1001,
    "LastName": "Moor"
}

Input in XML format(It's not required. Given to have better understanding in the input request data.):

<?xml version="1.0" encoding="UTF-8"?>
<Employee Department="HRD" Designation="Manager" EmployeeNo="1001" FirstName="Frank" LastName="Moor">
    <Dependants>
        <Dependant Age="70" Location="London" Name="George Moor" Relation="Father"/>
        <Dependant Age="65" Location="London" Name="Sara Moor" Relation="Mother"/>
    </Dependants>
</Employee>

---

Service URL & Http Method: http://localhost:8080/createEmployee/create . Http Method = PUT

---

Service Output: It will insert record and database tables and return the Employee objects current state.

{
    "lockId": "1",
    "createTs": "2021-08-08 19:35:47.987",
    "modifyTs": "2021-08-08 19:35:47.987",
    "modifyUser": "SYSUSER",
    "modifySystem": "SYSTEM",
    "createUser": "SYSUSER",
    "createSystem": "SYSTEM",
    "status": "",
    "message": "",
    "errorCode": "",
    "errorMessage": "",
    "employeeKey": "1628437362882",
    "department": "HRD",
    "designation": "Manager",
    "employeeNo": "1001",
    "firstName": "Frank",
    "lastName": "Moor",
    "dependants": {
        "lockId": "",
        "status": "",
        "message": "",
        "errorCode": "",
        "errorMessage": "",
        "dependant": [
            {
                "lockId": "1",
                "createTs": "2021-08-08 19:35:48.98",
                "modifyTs": "2021-08-08 19:35:48.98",
                "modifyUser": "SYSUSER",
                "modifySystem": "SYSTEM",
                "createUser": "SYSUSER",
                "createSystem": "SYSTEM",
                "status": "",
                "message": "",
                "errorCode": "",
                "errorMessage": "",
                "dependantKey": "1628438075506",
                "employeeKey": "1628437362882",
                "age": "70",
                "location": "London",
                "name": "George Moor",
                "relation": "Father"
            },
            {
                "lockId": "1",
                "createTs": "2021-08-08 19:35:49.091",
                "modifyTs": "2021-08-08 19:35:49.091",
                "modifyUser": "SYSUSER",
                "modifySystem": "SYSTEM",
                "createUser": "SYSUSER",
                "createSystem": "SYSTEM",
                "status": "",
                "message": "",
                "errorCode": "",
                "errorMessage": "",
                "dependantKey": "1628438152148",
                "employeeKey": "1628437362882",
                "age": "75",
                "location": "London",
                "name": "Sara Moor",
                "relation": "Mother"
            }
        ]
    }
}

---

Key Components and their functions:

• Database Tables :- There will be two tables EMPLOYEE and DEPENDANT to store data from user request.
• Bean and Helper Classes :- To hold and transport data from controller to service to DAO layer.
• Controller Class : To Receive the user request and set the data in the Request bean object.
• Service Classes : To invoke the appropriate DAO class and methods.
• Application Properties : To define database user, password and connection URL
• DAO Properties : Holds the DDL statements which will be used to insert record in Employee and Dependent table.
• POM XML : Where we define all project dependencies.
• Application Starter file. - To start a springboot application
• DAO and RowMapper Classes Class : To fetch data from database table and it them in Java Objects (Employee and Dependent bean)

---

Execution Steps:
1). Create following table in MySQL dtatbase (or in any other database which you have). Here we will be using MySql DB.

• EMPLOYEE
• DEPENDANT

EMPLOYEE Table Structure:

CREATE TABLE FND_EMPLOYEE 
( EMPLOYEE_KEY BIGINT , DATE_OF_JOINING DATE , DEPARTMENT VARCHAR(250) , DESIGNATION VARCHAR(250) , EMPLOYEE_NO VARCHAR(250) , 
FIRST_NAME VARCHAR(250) , LAST_NAME VARCHAR(250) , LOCK_ID BIGINT , CREATE_TS TIMESTAMP , CREATE_USER VARCHAR(100) ,
 CREATE_SYSTEM VARCHAR(100) , MODIFY_TS TIMESTAMP , MODIFY_USER VARCHAR(100) , MODIFY_SYSTEM VARCHAR(100) );

DEPANDANT Table Structure:

CREATE TABLE FND_DEPENDANT (   DEPENDANT_KEY BIGINT  , EMPLOYEE_KEY BIGINT(100)  , AGE VARCHAR(250)  , LOCATION VARCHAR(250)  ,
 NAME VARCHAR(250)  , RELATION VARCHAR(250)  , LOCK_ID BIGINT  , CREATE_TS TIMESTAMP  , CREATE_USER VARCHAR(100)  , 
 CREATE_SYSTEM VARCHAR(100)  , MODIFY_TS TIMESTAMP  , MODIFY_USER VARCHAR(100)  , MODIFY_SYSTEM VARCHAR(100) );

---

2). Create Spring Boot Project. Create a SpringBoot starter project on your eclipse or download it from STS site. Note: This option will be available if Springboot eclipse is installed in your eclipse.

---

---

Next:

---

Click On Finish button.

---

(Project Created In Eclipse)

---

3.)Delete Test File: SpringjdbcdemoApplicationTests.java

---

4.) Add Dependency:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.5.3</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.example</groupId>
	<artifactId>springjdbcdemo</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>war</packaging>
	<name>springjdbcdemo</name>
	<description>Demo project for Spring Boot</description>
	<properties>
		<java.version>11</java.version>
	</properties>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-tomcat</artifactId>
			<scope>provided</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>

		<!-- this is comment -->

		<dependency>
			<groupId>log4j</groupId>
			<artifactId>log4j</artifactId>
			<version>1.2.17</version>
		</dependency>
		<!-- https://mvnrepository.com/artifact/org.slf4j/slf4j-api -->
		<dependency>
			<groupId>org.slf4j</groupId>
			<artifactId>slf4j-api</artifactId>
			<version>1.7.26</version>
		</dependency>
		<!-- https://mvnrepository.com/artifact/org.slf4j/slf4j-simple -->
		<!-- https://mvnrepository.com/artifact/org.slf4j/slf4j-simple -->
		<dependency>
			<groupId>org.slf4j</groupId>
			<artifactId>slf4j-log4j12</artifactId>
			<version>1.7.26</version>
		</dependency>

		<dependency>
			<groupId>com.fasterxml.jackson.core</groupId>
			<artifactId>jackson-core</artifactId>
			<version>2.10.0</version>
		</dependency>
		<dependency>
			<groupId>com.fasterxml.jackson.core</groupId>
			<artifactId>jackson-databind</artifactId>
			<version>2.10.0</version>
		</dependency>
		<dependency>
			<groupId>com.fasterxml.jackson.core</groupId>
			<artifactId>jackson-annotations</artifactId>
			<version>2.10.0</version>
		</dependency>
		<!-- https://mvnrepository.com/artifact/com.fasterxml.jackson.dataformat/jackson-dataformat-xml -->
		<dependency>
			<groupId>com.fasterxml.jackson.dataformat</groupId>
			<artifactId>jackson-dataformat-xml</artifactId>
			<version>2.10.0</version>
		</dependency>
		<!-- https://mvnrepository.com/artifact/com.fasterxml.jackson.jaxrs/jackson-jaxrs-json-provider -->
		<dependency>
			<groupId>com.fasterxml.jackson.jaxrs</groupId>
			<artifactId>jackson-jaxrs-json-provider</artifactId>
			<version>2.10.0</version>
		</dependency>


		<!-- jdbc <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> 
			<version>5.2.0.RELEASE</version> </dependency> -->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-jdbc</artifactId>
		</dependency>

		<dependency>
			<groupId>org.hibernate</groupId>
			<artifactId>hibernate-validator</artifactId>
			<version>5.0.3.Final</version>
		</dependency>

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>8.0.25</version>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>

</project>

---

5). Create application.properties:

logging.level.org.springframework=DEBUG
logging.level.org.springframework.jdbc=DEBUG
logging.level.root=DEBUG

logging.pattern.console=%-5level %logger{36} - %msg%n

## MySQL
spring.datasource.url=jdbc:mysql://localhost:3306/easycodeforall
spring.datasource.username=root
spring.datasource.password=root

'

---

6). Create following Java Classes.

---

A.) Controller:

package com.easy.hr.mainapi.controller;

import java.util.ArrayList;
import java.util.List;

import javax.validation.Valid;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.validation.BindingResult;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseStatus;
import org.springframework.web.bind.annotation.RestController;

//IMPORT_ALL_BEAN_CLASS
import com.easy.base.CommonBean;
import com.easy.base.TransactionException;
import com.easy.hr.dependant.bean.Dependant;
import com.easy.hr.dependant.bean.DependantRequest;
import com.easy.hr.dependant.bean.DependantResponse;
import com.easy.hr.dependant.bean.Dependants;
import com.easy.hr.dependant.bean.DependantsRequest;
import com.easy.hr.dependant.bean.DependantsResponse;
import com.easy.hr.dependant.service.DependantHelper;
import com.easy.hr.employee.bean.Employee;
import com.easy.hr.employee.bean.EmployeeRequest;
import com.easy.hr.employee.bean.EmployeeResponse;
import com.easy.hr.employee.service.EmployeeHelper;
import com.easy.hr.mainapi.service.CreateEmployeeService;

@RestController
@RequestMapping(value = "/createEmployee/", produces = { MediaType.APPLICATION_JSON_VALUE }, consumes = {
		MediaType.APPLICATION_JSON_VALUE })
public class createEmployeeController {
	private static Logger log = Logger.getLogger(createEmployeeController.class);
	@Autowired
	private CreateEmployeeService service;

	@RequestMapping(value = "/test", method = RequestMethod.GET)
	@ResponseStatus(HttpStatus.OK)
	public ResponseEntity test(@RequestBody @Valid EmployeeRequest employeeRequest,
			BindingResult result) throws Exception {
		log.debug("Update Demand. input =." + employeeRequest);

		return new ResponseEntity(employeeRequest, HttpStatus.OK);

	}

	@RequestMapping(value = { "/update", "/update/" }, method = RequestMethod.POST)
	@ResponseStatus(HttpStatus.OK)
	public ResponseEntity update(@RequestBody @Valid EmployeeRequest employeeRequest,
			BindingResult result) throws Exception {
		log.debug("Update input." + employeeRequest);

		Employee employee = this.copyValueFromRequestToEntity(employeeRequest);

		log.debug("employee Object=" + employee);
		CommonBean commonBean = service.create(employee, null);

		Employee employeeOutputObj = (Employee) commonBean;

		EmployeeResponse employeeResponse = this.copyFromTransBeanToResponse(employeeOutputObj);
		return new ResponseEntity(employeeResponse, HttpStatus.OK);
	}

	@RequestMapping(value = { "/create", "/create/" }, method = RequestMethod.PUT)
	@ResponseStatus(HttpStatus.OK)
	public ResponseEntity create(@RequestBody @Valid EmployeeRequest employeeRequest,
			BindingResult result) throws Exception {
		log.debug("Create input." + employeeRequest);

		Employee employee = this.copyValueFromRequestToEntity(employeeRequest);

		log.debug("employee Object=" + employee);
		CommonBean commonBean = service.create(employee, null);

		Employee employeeOutputObj = (Employee) commonBean;

		EmployeeResponse employeeResponse = this.copyFromTransBeanToResponse(employeeOutputObj);
		return new ResponseEntity(employeeResponse, HttpStatus.OK);
	}

	public Employee copyValueFromRequestToEntity(EmployeeRequest employeeRequest) throws Exception {

		Employee employee = EmployeeHelper.copyValuesFromRequestBeanToEntity(employeeRequest);
		DependantsRequest dependantsRequest = employeeRequest.getDependantsRequest();
		Dependants dependants = new Dependants();
		employee.setDependants(dependants);
		List dependantRequestList = dependantsRequest.getDependantRequestList();
		List dependantList = new ArrayList();
		dependants.setDependantList(dependantList);
		for (int k = 0; k < dependantRequestList.size(); k++) {
			DependantRequest dependantRequest = dependantRequestList.get(k);
			Dependant dependant = DependantHelper.copyValuesFromRequestBeanToEntity(dependantRequest);
			dependantList.add(dependant);

		} // ccn
		return employee;
	}

	public EmployeeResponse copyFromTransBeanToResponse(Employee employee) throws TransactionException {

		EmployeeResponse employeeResponse = new EmployeeResponse();
		EmployeeHelper.copyValuesFromEntityToResponse(employee, employeeResponse);// K2
		Dependants dependants = employee.getDependants();
		DependantsResponse dependantsResponse = new DependantsResponse();
		employeeResponse.setDependantsResponse(dependantsResponse);
		List dependantList = dependants.getDependantList();
		List dependantResponseList = new ArrayList();

		dependantsResponse.setDependantResponseList(dependantResponseList);

		for (int k = 0; k < dependantList.size(); k++) {
			Dependant dependant = dependantList.get(k);
			log.debug("Update Dependant has been Dependant=." + dependant);
			DependantResponse dependantResponse = new DependantResponse();
			dependantResponse = DependantHelper.copyValuesFromEntityToResponse(dependant, dependantResponse);
			dependantResponseList.add(dependantResponse);
		}
		return employeeResponse;
	}// M
}

---

B.) Main Service:

package com.easy.hr.mainapi.service;

import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;

//IMPORT_ALL_BEAN_CLASS
import com.easy.base.CommonBean;
import com.easy.base.Service;
import com.easy.base.TransactionException;
import com.easy.base.TransactionUtil;
import com.easy.hr.dependant.bean.Dependant;
import com.easy.hr.dependant.bean.Dependants;
import com.easy.hr.dependant.service.DependantHelper;
import com.easy.hr.dependant.service.DependantService;
import com.easy.hr.employee.bean.Employee;
import com.easy.hr.employee.service.EmployeeHelper;
import com.easy.hr.employee.service.EmployeeService;

@org.springframework.stereotype.Service
public class CreateEmployeeService implements Service {
	private static Logger log = Logger.getLogger(CreateEmployeeService.class);
	@Autowired
	EmployeeService employeeService;
	@Autowired
	DependantService dependantService;

	@Override
	public CommonBean create(CommonBean commonBean, Map params) throws TransactionException {

		try {
			Employee employee = (Employee) commonBean;
			EmployeeHelper.setAuditColumValuesForCreate(employee);
			employee = EmployeeHelper.setKeyColumnValue(employee);

			Object employeeObj = employeeService.manage(employee, params);
			employee = (Employee) employeeObj;
			Long employeeKey = employee.getEmployeeKey();
			if (employeeKey == null)
				throw new TransactionException("employeeKeyIsNull", "employeeKeyIsNull");
			Dependants dependants = employee.getDependants();
			List dependantList = dependants.getDependantList();
			for (Dependant dependant : dependantList) {

				DependantHelper.setAuditColumValuesForCreate(dependant);
				dependant = DependantHelper.setKeyColumnValue(dependant);
				dependant.setEmployeeKey(employeeKey);

				Object objDependant = dependantService.manage(dependant, params);
				dependant = (Dependant) objDependant;
				Long dependantKey = dependant.getDependantKey();
			}
			return employee;
		} catch (Exception ex) {
			throw TransactionUtil.convertToTransactionException(ex, "MAIN_API_CREATE_EXCEPTION",
					"Exception in create public api. Check stacktrace for details.");
		}
	}
}

---

C.) Individual Employee Service

package com.easy.hr.employee.service;

import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.ComponentScan;

//IMPORT_ALL_BEAN_CLASS
import com.easy.base.CommonBean;
import com.easy.base.Service;
import com.easy.hr.employee.bean.Employee;
import com.easy.hr.employee.dao.EmployeeDAO;


@org.springframework.stereotype.Service
public class EmployeeService implements Service {
	private static Logger log = Logger.getLogger(EmployeeService.class.getName());
	@Autowired
	private EmployeeDAO EmployeeDAO;

	public CommonBean create(Employee Employee, Map params) throws Exception {
		log.debug("Create Employee has been initiated.");
		try {
			EmployeeDAO.createEmployee(Employee);
			return Employee;
		} catch (Exception exception) {
			log.error("Errorin.create", exception);
			throw exception;

		}
	}

	public CommonBean update(Employee Employee, Map params) throws Exception {

		log.debug("Update Employee has been initiated.");
		try {
			Employee EmployeeObjFromDB = EmployeeDAO.getEmployeeByEmployeeKeyAndOths(Employee); // change it to a
																								// correct method to get
																								// the Obj from DB.
			setValuesToDbObjectToUpdate(EmployeeObjFromDB, Employee);
			EmployeeDAO.updateEmployee(EmployeeObjFromDB);
			return EmployeeObjFromDB;
		} catch (Exception exception) {
			log.error("Employee.Update", exception);
			throw exception;
		}

	}

	private void setValuesToDbObjectToUpdate(Employee EmployeeObjFromDB, Employee Employee) throws Exception {

		if (Employee.getEmployeeKey() != null) {
			EmployeeObjFromDB.setEmployeeKey(Employee.getEmployeeKey());
		}
		if (Employee.getDateOfJoining() != null) {
			EmployeeObjFromDB.setDateOfJoining(Employee.getDateOfJoining());
		}
		if (Employee.getDepartment() != null) {
			EmployeeObjFromDB.setDepartment(Employee.getDepartment());
		}
		if (Employee.getDesignation() != null) {
			EmployeeObjFromDB.setDesignation(Employee.getDesignation());
		}
		if (Employee.getEmployeeNo() != null) {
			EmployeeObjFromDB.setEmployeeNo(Employee.getEmployeeNo());
		}
		if (Employee.getFirstName() != null) {
			EmployeeObjFromDB.setFirstName(Employee.getFirstName());
		}
		if (Employee.getLastName() != null) {
			EmployeeObjFromDB.setLastName(Employee.getLastName());
		}

	}

	public CommonBean manage(Employee Employee, Map params) throws Exception {

		log.debug("Update Employee has been initiated.");
		try {
			Employee EmployeeObjFromDB = EmployeeDAO.getEmployeeByEmployeeKeyAndOths(Employee); // change it to a
																								// correct method to get
																								// the Obj from DB.
			if (EmployeeObjFromDB == null) {
				create(Employee, params);
				return Employee;
			}
			setValuesToDbObjectToUpdate(EmployeeObjFromDB, Employee);
			EmployeeDAO.updateEmployee(EmployeeObjFromDB);
			return EmployeeObjFromDB;
		} catch (Exception exception) {
			log.error("Employee.Update", exception);
			throw exception;
		}
	}

	public CommonBean delete(Employee Employee, Map params) throws Exception {

		log.debug("delete Employee has been initiated.");
		try {
			Employee EmployeeObjFromDB = EmployeeDAO.getEmployeeByEmployeeKeyAndOths(Employee); // change it to a
																								// correct method to get
																								// the Obj from DB.
			EmployeeDAO.deleteEmployee(EmployeeObjFromDB);
			return EmployeeObjFromDB;
		} catch (Exception exception) {
			log.error("Employee.delete", exception);
			throw exception;
		}

		// log.debug("delete done EmployeeKey=" + Employee);

	}

	public CommonBean get(Employee Employee, Map params) throws Exception {
		log.debug("Update invDemandType has been initiated.");
		Employee EmployeeObjFromDB = null;
		try {
			EmployeeObjFromDB = EmployeeDAO.getEmployeeByEmployeeKeyAndOths(Employee); // Change it to correct mentods
			return EmployeeObjFromDB;
		} catch (Exception exception) {
			log.error("Employee.delete", exception);
			throw exception;
		}

	}

	public List getEmployeeListByEmployeeKey(Employee Employee, Map params) throws Exception {
		log.debug("ListApi has been initiated.");
		List EmployeeObjListFromDB = null;
		try {
			EmployeeObjListFromDB = EmployeeDAO.getEmployeeListByEmployeeKeyAndOtrs(Employee);
			return EmployeeObjListFromDB;
		} catch (Exception exception) {
			log.error("Employee.ListApi", exception);
			throw exception;
		}

	}
}

---

D.) Individual Service Dependent Service:

package com.easy.hr.dependant.service;

import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;

//IMPORT_ALL_BEAN_CLASS
import com.easy.base.CommonBean;
import com.easy.base.Service;
import com.easy.hr.dependant.bean.Dependant;
import com.easy.hr.dependant.dao.DependantDAO;

@org.springframework.stereotype.Service
public class DependantService implements Service {
	private static Logger log = Logger.getLogger(DependantService.class.getName());
	@Autowired
	private DependantDAO DependantDAO;

	public CommonBean create(Dependant Dependant, Map params) throws Exception {
		log.debug("Create Dependant has been initiated.");
		try {
			DependantDAO.createDependant(Dependant);
			return Dependant;
		} catch (Exception exception) {
			log.error("Errorin.create", exception);
			throw exception;

		}
	}

	public CommonBean update(Dependant Dependant, Map params) throws Exception {

		log.debug("Update Dependant has been initiated.");
		try {
			Dependant DependantObjFromDB = DependantDAO.getDependantByDependantKeyAndOths(Dependant); // change it to a
																										// correct
																										// method to get
																										// the Obj from
																										// DB.
			setValuesToDbObjectToUpdate(DependantObjFromDB, Dependant);
			DependantDAO.updateDependant(DependantObjFromDB);
			return DependantObjFromDB;
		} catch (Exception exception) {
			log.error("Dependant.Update", exception);
			throw exception;
		}

	}

	private void setValuesToDbObjectToUpdate(Dependant DependantObjFromDB, Dependant Dependant) throws Exception {

		if (Dependant.getDependantKey() != null) {
			DependantObjFromDB.setDependantKey(Dependant.getDependantKey());
		}
		if (Dependant.getEmployeeKey() != null) {
			DependantObjFromDB.setEmployeeKey(Dependant.getEmployeeKey());
		}
		if (Dependant.getAge() != null) {
			DependantObjFromDB.setAge(Dependant.getAge());
		}
		if (Dependant.getLocation() != null) {
			DependantObjFromDB.setLocation(Dependant.getLocation());
		}
		if (Dependant.getName() != null) {
			DependantObjFromDB.setName(Dependant.getName());
		}
		if (Dependant.getRelation() != null) {
			DependantObjFromDB.setRelation(Dependant.getRelation());
		}

	}

	public CommonBean manage(Dependant Dependant, Map params) throws Exception {

		log.debug("Update Dependant has been initiated.");
		try {
			Dependant DependantObjFromDB = DependantDAO.getDependantByDependantKeyAndOths(Dependant); // change it to a
																										// correct
																										// method to get
																										// the Obj from
																										// DB.
			if (DependantObjFromDB == null) {
				create(Dependant, params);
				return Dependant;
			}
			setValuesToDbObjectToUpdate(DependantObjFromDB, Dependant);
			DependantDAO.updateDependant(DependantObjFromDB);
			return DependantObjFromDB;
		} catch (Exception exception) {
			log.error("Dependant.Update", exception);
			throw exception;
		}
	}

	public CommonBean delete(Dependant Dependant, Map params) throws Exception {

		log.debug("delete Dependant has been initiated.");
		try {
			Dependant DependantObjFromDB = DependantDAO.getDependantByDependantKeyAndOths(Dependant); // change it to a
																										// correct
																										// method to get
																										// the Obj from
																										// DB.
			DependantDAO.deleteDependant(DependantObjFromDB);
			return DependantObjFromDB;
		} catch (Exception exception) {
			log.error("Dependant.delete", exception);
			throw exception;
		}

		// log.debug("delete done DependantKey=" + Dependant);

	}

	public CommonBean get(Dependant Dependant, Map params) throws Exception {
		log.debug("Update invDemandType has been initiated.");
		Dependant DependantObjFromDB = null;
		try {
			DependantObjFromDB = DependantDAO.getDependantByDependantKeyAndOths(Dependant); // Change it to correct
																							// mentods
			return DependantObjFromDB;
		} catch (Exception exception) {
			log.error("Dependant.delete", exception);
			throw exception;
		}

	}

	public List getDependantListByDependantKey(Dependant Dependant, Map params)
			throws Exception {
		log.debug("ListApi has been initiated.");
		List DependantObjListFromDB = null;
		try {
			DependantObjListFromDB = DependantDAO.getDependantListByDependantKeyAndOtrs(Dependant);
			return DependantObjListFromDB;
		} catch (Exception exception) {
			log.error("Dependant.ListApi", exception);
			throw exception;
		}

	}

}

---

E.) Employee DAO:

package com.easy.hr.employee.dao;

import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

//IMPORT_ALL_BEAN_CLASS
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.PropertySource;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import com.easy.hr.employee.bean.Employee;
import com.fasterxml.jackson.core.JsonParseException;
import com.fasterxml.jackson.databind.JsonMappingException;
import com.fasterxml.jackson.databind.ObjectMapper;

@Repository
@PropertySource(value = { "classpath:employee-dao.properties" })
public class EmployeeDAO {
	private static Logger log = Logger.getLogger(EmployeeDAO.class.getName());
	@Autowired
	private JdbcTemplate jdbcTemplate;
	@Autowired
	private NamedParameterJdbcTemplate namedJdbcTemplate;
	@Value(value = "")
	private String createEmployeeQuery;
	@Value(value = "")
	private String updateEmployeeQuery;
	@Value(value = "")
	private String deleteEmployeeQuery;
	@Value(value = "")
	private String getEmployeeQuery;
	@Value(value = "")
	private String getEmployeeListByEmployeeKeyOtrs;
	@Value(value = "")
	private String getEmployeeListByEmployeeKeyList;
	@Value(value = "")
	private String getEmployeeListByDynamicAttrs;
	@Value(value = "")
	private String getEmployeeListByEmployeeKeyOths;
	/** The batch insert size. */
	@Value(value = "")
	private int batchInsertSize;

	/** The find Employee query. */
	@Value(value = "")
	private String findEmployeeQuery;

	public void createEmployee(final Employee Employee) throws SQLException, DataAccessException {
		log.debug("createRecord start");
		log.debug("createRecord start");
		jdbcTemplate.execute(createEmployeeQuery, new PreparedStatementCallback&llt;Object>() {
			@Override
			public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
				int indx = 1;
				ps.setLong(indx++, Employee.getEmployeeKey());
				ps.setDate(indx++, Employee.getDateOfJoining());
				ps.setString(indx++, Employee.getDepartment());
				ps.setString(indx++, Employee.getDesignation());
				if (Employee.getEmployeeNo() != null)
					ps.setInt(indx++, Employee.getEmployeeNo());
				else
					ps.setNull(indx++, Types.INTEGER);
				ps.setString(indx++, Employee.getFirstName());
				ps.setString(indx++, Employee.getLastName());
				ps.setLong(indx++, Employee.getLockId());
				ps.setTimestamp(indx++, Employee.getCreateTs());
				ps.setString(indx++, Employee.getCreateUser());
				ps.setString(indx++, Employee.getCreateSystem());
				ps.setTimestamp(indx++, Employee.getModifyTs());
				ps.setString(indx++, Employee.getModifyUser());
				ps.setString(indx++, Employee.getModifySystem());

				return ps.executeUpdate();
			}
		});
	}

	public void updateEmployee(final Employee Employee) throws JsonParseException, JsonMappingException, IOException {

		log.debug("updateEmployee start");
		jdbcTemplate.execute(updateEmployeeQuery, new PreparedStatementCallback&llt;Object>() {

			@Override
			public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
				int indx = 1;
				ps.setDate(indx++, Employee.getDateOfJoining());
				ps.setString(indx++, Employee.getDepartment());
				ps.setString(indx++, Employee.getDesignation());
				if (Employee.getEmployeeNo() != null)
					ps.setInt(indx++, Employee.getEmployeeNo());
				else
					ps.setNull(indx++, Types.INTEGER);
				ps.setString(indx++, Employee.getFirstName());
				ps.setString(indx++, Employee.getLastName());
				ps.setLong(indx++, Employee.getLockId());
				ps.setTimestamp(indx++, Employee.getModifyTs());
				ps.setString(indx++, Employee.getModifyUser());
				ps.setString(indx++, Employee.getModifySystem());
				ps.setLong(indx++, Employee.getEmployeeKey());

				int count = ps.executeUpdate();
				log.debug("Number of records updated=" + count);
				if (count == 0)
					throw new SQLException("STALE_DATA_ERROR");
				return count;

			}

		});
		log.debug("updateEmployee end");

	}

	public void deleteEmployee(final Employee Employee) throws IOException {
		log.debug("deleteEmployee start");

		Long employeeKey = Employee.getEmployeeKey();
		log.debug("Key.=" + employeeKey);

		jdbcTemplate.execute(deleteEmployeeQuery, new PreparedStatementCallback&llt;Object>() {

			@Override
			public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
				ps.setLong(1, Employee.getEmployeeKey());
				return ps.executeUpdate();
			}

		});
		log.debug("delete Employee end");
	}

	public Employee getEmployee(Employee Employee) {
		Long employeeKey = Employee.getEmployeeKey();

		log.debug("KeyCol for Get::" + employeeKey);
		Employee EmployeeFromDB = null;
		try {

			EmployeeFromDB = jdbcTemplate.queryForObject(getEmployeeQuery, new Object[] { employeeKey },
					new BeanPropertyRowMapper&llt;Employee>(Employee.class));
			log.debug("VAlue=::" + EmployeeFromDB);
		} catch (EmptyResultDataAccessException ex) {
			log.info("No Record Found. It Maybe expected." + ex.toString());
			return null;
		}

		return EmployeeFromDB;
	}

	public Employee getEmployeeForUpdate(Employee Employee) {
		Long employeeKey = Employee.getEmployeeKey();

		log.debug("KeyCol for Get::" + employeeKey);
		Employee EmployeeFromDB = null;
		try {

			EmployeeFromDB = jdbcTemplate.queryForObject(getEmployeeQuery + " FOR UPDATE ",
					new Object[] { employeeKey }, new BeanPropertyRowMapper&llt;Employee>(Employee.class));
			log.debug("VAlue=::" + EmployeeFromDB);
		} catch (EmptyResultDataAccessException ex) {
			log.info("NO RECORD. IT MAY BE EXPECTED." + ex.toString());
			return null;
		}

		return EmployeeFromDB;
	}

	public List&llt;Employee> getEmployeeListByEmployeeKeyAndOtrs(Employee Employee) {
		log.debug("Employee::" + Employee);
		Object[] parameters = new Object[] { Employee.getEmployeeKey() };

		List&llt;Employee> EmployeeObjListFromDB = jdbcTemplate.query(getEmployeeListByEmployeeKeyOtrs, parameters,
				new BeanPropertyRowMapper&llt;Employee>(Employee.class));

		log.debug("No.Of RecordFoundCount in list=::" + EmployeeObjListFromDB.size());
		return EmployeeObjListFromDB;
	}

	public Employee getEmployeeByEmployeeKeyAndOtrs(Employee Employee) {
		List&llt;Employee> EmployeeObjListFromDB = getEmployeeListByEmployeeKeyAndOtrs(Employee);
		Employee EmployeeFromDB = null;

		if (EmployeeObjListFromDB.size() > 0) {
			EmployeeFromDB = EmployeeObjListFromDB.get(EmployeeObjListFromDB.size() - 1);
			log.debug("EmployeeFromDBVAlue=::" + EmployeeObjListFromDB.get(EmployeeObjListFromDB.size() - 1));
		}

		return EmployeeFromDB;
	}

	public List&llt;Employee> getEmployeeListByEmployeeKeyAndOths(Employee Employee) {
		log.debug("Employee::" + Employee);
		Object[] parameters = new Object[] { Employee.getEmployeeKey() };

		List&llt;Employee> EmployeeObjListFromDB = jdbcTemplate.query(getEmployeeListByEmployeeKeyOths, parameters,
				new BeanPropertyRowMapper&llt;Employee>(Employee.class));

		log.debug("No.Of RecordFoundCount in list=::" + EmployeeObjListFromDB.size());
		return EmployeeObjListFromDB;
	}

	public Employee getEmployeeByEmployeeKeyAndOths(Employee Employee) {
		List&llt;Employee> EmployeeObjListFromDB = getEmployeeListByEmployeeKeyAndOths(Employee);
		Employee EmployeeFromDB = null;

		if (EmployeeObjListFromDB.size() > 0) {
			EmployeeFromDB = EmployeeObjListFromDB.get(EmployeeObjListFromDB.size() - 1);
			log.debug("EmployeeFromDBVAlue=::" + EmployeeObjListFromDB.get(EmployeeObjListFromDB.size() - 1));
		}

		return EmployeeFromDB;
	}

	public List&llt;Employee> getEmployeeListByEmployeeKeyList(List&llt;Employee> EmployeeList) {
		log.debug("EmployeeList::" + EmployeeList);
		String whereClause = " WHERE 1=2 ";
		List&llt;Object> list = new ArrayList&llt;Object>();
		for (Employee Employee : EmployeeList) {
			list.add(Employee.getEmployeeKey());
			whereClause = whereClause + " OR EMPLOYEE_KEY=? ";
		}

		Object[] parameters = list.toArray();

		List&llt;Employee> EmployeeObjListFromDB = jdbcTemplate.query(getEmployeeListByEmployeeKeyList + whereClause,
				parameters, new BeanPropertyRowMapper&llt;Employee>(Employee.class));

		log.debug("No.Of RecordFoundCount in list=::" + EmployeeObjListFromDB.size());
		return EmployeeObjListFromDB;
	}

	public List&llt;Employee> getEmployeeListByDynamicAttrs(Employee Employee) {
		log.debug("Employee::" + Employee);
		List&llt;Object> paramList = new ArrayList&llt;Object>();
		String whereClause = " WHERE 1=1 ";
		if (Employee.getEmployeeKey() != null) {
			whereClause = whereClause + " AND EMPLOYEE_KEY=? ";
			paramList.add(Employee.getEmployeeKey());
		}
		if (Employee.getDateOfJoining() != null) {
			whereClause = whereClause + " AND DATE_OF_JOINING=? ";
			paramList.add(Employee.getDateOfJoining());
		}
		if (Employee.getDepartment() != null) {
			whereClause = whereClause + " AND DEPARTMENT=? ";
			paramList.add(Employee.getDepartment());
		}
		if (Employee.getDesignation() != null) {
			whereClause = whereClause + " AND DESIGNATION=? ";
			paramList.add(Employee.getDesignation());
		}
		if (Employee.getEmployeeNo() != null) {
			whereClause = whereClause + " AND EMPLOYEE_NO=? ";
			paramList.add(Employee.getEmployeeNo());
		}
		if (Employee.getFirstName() != null) {
			whereClause = whereClause + " AND FIRST_NAME=? ";
			paramList.add(Employee.getFirstName());
		}
		if (Employee.getLastName() != null) {
			whereClause = whereClause + " AND LAST_NAME=? ";
			paramList.add(Employee.getLastName());
		}

		Object[] parameters = paramList.toArray();

		log.debug("DynamicSQL::" + getEmployeeListByDynamicAttrs + whereClause);
		List&llt;Employee> EmployeeObjListFromDB = jdbcTemplate.query(getEmployeeListByDynamicAttrs + whereClause,
				parameters, new BeanPropertyRowMapper&llt;Employee>(Employee.class));

		log.debug("No.Of RecordFoundCount in list=::" + EmployeeObjListFromDB.size());
		return EmployeeObjListFromDB;
	}

	public void insertEmployeeBatch(List&llt;Employee> EmployeeList) throws SQLException {
		for (int i = 0; i &llt; EmployeeList.size(); i += this.batchInsertSize) {
			final List&llt;Employee> EmployeeBatch = EmployeeList.subList(i,
					i + this.batchInsertSize > EmployeeList.size() ? EmployeeList.size() : i + batchInsertSize);
			int[] noOfEmployeesInserted = jdbcTemplate.batchUpdate(this.createEmployeeQuery,
					new BatchPreparedStatementSetter() {
						public void setValues(PreparedStatement pStmt, int j) throws SQLException {
							Employee Employee = (Employee) EmployeeBatch.get(j);
							int indx = 1;
							pStmt.setLong(indx++, Employee.getEmployeeKey());
							pStmt.setDate(indx++, Employee.getDateOfJoining());
							pStmt.setString(indx++, Employee.getDepartment());
							pStmt.setString(indx++, Employee.getDesignation());
							pStmt.setInt(indx++, Employee.getEmployeeNo());
							pStmt.setString(indx++, Employee.getFirstName());
							pStmt.setString(indx++, Employee.getLastName());
							pStmt.setLong(indx++, Employee.getLockId());
							pStmt.setTimestamp(indx++, Employee.getCreateTs());
							pStmt.setString(indx++, Employee.getCreateUser());
							pStmt.setString(indx++, Employee.getCreateSystem());
							pStmt.setTimestamp(indx++, Employee.getModifyTs());
							pStmt.setString(indx++, Employee.getModifyUser());
							pStmt.setString(indx++, Employee.getModifySystem());
							ObjectMapper mapper = new ObjectMapper();
							// Java objects to JSON string
							String jsonString = "";

							log.debug("attributes json" + jsonString);
							pStmt.setString(indx++, jsonString);
						}

						public int getBatchSize() {
							return EmployeeBatch.size();
						}
					});
			log.debug("insertEmployeesBatch end");
		}
	}

//##PLACE_FOR_PUB_API_DAO_CODE##// 
}

---

F.) Employee RowMapper:

package com.easy.hr.employee.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
//IMPORT_ALL_BEAN_CLASS
import com.easy.base.*;
import com.easy.hr.employee.bean.*;
import com.easy.hr.employee.service.*;

import org.apache.log4j.*;
import org.springframework.jdbc.core.RowMapper;
import com.easy.hr.employee.bean.Employee;

public class EmployeeRowMapper implements RowMapper {
	private static Logger log = Logger.getLogger(EmployeeRowMapper.class.getName());

	public Employee mapRow(ResultSet resultSet, int rowNum) throws SQLException {
		Employee Employee = new Employee();
		Employee.setEmployeeKey(resultSet.getLong("EMPLOYEE_KEY"));
		Employee.setDateOfJoining(resultSet.getDate("DATE_OF_JOINING"));
		Employee.setDepartment(resultSet.getString("DEPARTMENT"));
		Employee.setDesignation(resultSet.getString("DESIGNATION"));
		Employee.setEmployeeNo(resultSet.getInt("EMPLOYEE_NO"));
		Employee.setFirstName(resultSet.getString("FIRST_NAME"));
		Employee.setLastName(resultSet.getString("LAST_NAME"));
		Employee.setLockId(resultSet.getLong("LOCK_ID"));
		Employee.setCreateTs(resultSet.getTimestamp("CREATE_TS"));
		Employee.setCreateUser(resultSet.getString("CREATE_USER"));
		Employee.setCreateSystem(resultSet.getString("CREATE_SYSTEM"));
		Employee.setModifyTs(resultSet.getTimestamp("MODIFY_TS"));
		Employee.setModifyUser(resultSet.getString("MODIFY_USER"));
		Employee.setModifySystem(resultSet.getString("MODIFY_SYSTEM"));
		return Employee;
	};
}

---

G.) DempendantDAO:

package com.easy.hr.dependant.dao;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.util.*;
import java.io.*;
import org.apache.log4j.*;
//IMPORT_ALL_BEAN_CLASS
import com.easy.base.*;
import com.easy.hr.employee.bean.*;
import com.easy.hr.employee.service.*;
import com.easy.hr.dependant.bean.*;
import com.easy.hr.dependant.service.*;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.PropertySource;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.stereotype.Repository;
import javax.xml.bind.annotation.XmlAttribute;
import org.springframework.jdbc.core.*;
import java.sql.*;

import com.fasterxml.jackson.core.JsonParseException;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.JsonMappingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.easy.hr.dependant.bean.Dependant;
import javax.xml.bind.annotation.*;
import com.fasterxml.jackson.annotation.*;

@Repository
@PropertySource(value = { "classpath:dependant-dao.properties" })
public class DependantDAO {
	private static Logger log = Logger.getLogger(DependantDAO.class.getName());
	@Autowired
	private JdbcTemplate jdbcTemplate;
	@Autowired
	private NamedParameterJdbcTemplate namedJdbcTemplate;
	@Value(value = "")
	private String createDependantQuery;
	@Value(value = "")
	private String updateDependantQuery;
	@Value(value = "")
	private String deleteDependantQuery;
	@Value(value = "")
	private String getDependantQuery;
	@Value(value = "")
	private String getDependantListByDependantKeyOtrs;
	@Value(value = "")
	private String getDependantListByDependantKeyList;
	@Value(value = "")
	private String getDependantListByDynamicAttrs;
	@Value(value = "")
	private String getDependantListByDependantKeyOths;
	/** The batch insert size. */
	@Value(value = "")
	private int batchInsertSize;

	/** The find Dependant query. */
	@Value(value = "")
	private String findDependantQuery;

	public void createDependant(final Dependant Dependant) throws SQLException, DataAccessException {
		log.debug("createRecord start");
		log.debug("createRecord start");
		jdbcTemplate.execute(createDependantQuery, new PreparedStatementCallback&llt;Object>() {
			@Override
			public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
				int indx = 1;
				ps.setLong(indx++, Dependant.getDependantKey());
				ps.setLong(indx++, Dependant.getEmployeeKey());
				ps.setString(indx++, Dependant.getAge());
				ps.setString(indx++, Dependant.getLocation());
				ps.setString(indx++, Dependant.getName());
				ps.setString(indx++, Dependant.getRelation());
				ps.setLong(indx++, Dependant.getLockId());
				ps.setTimestamp(indx++, Dependant.getCreateTs());
				ps.setString(indx++, Dependant.getCreateUser());
				ps.setString(indx++, Dependant.getCreateSystem());
				ps.setTimestamp(indx++, Dependant.getModifyTs());
				ps.setString(indx++, Dependant.getModifyUser());
				ps.setString(indx++, Dependant.getModifySystem());

				return ps.executeUpdate();
			}
		});
	}

	public void updateDependant(final Dependant Dependant)
			throws JsonParseException, JsonMappingException, IOException {

		log.debug("updateDependant start");
		jdbcTemplate.execute(updateDependantQuery, new PreparedStatementCallback&llt;Object>() {

			@Override
			public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
				int indx = 1;
				ps.setLong(indx++, Dependant.getEmployeeKey());
				ps.setString(indx++, Dependant.getAge());
				ps.setString(indx++, Dependant.getLocation());
				ps.setString(indx++, Dependant.getName());
				ps.setString(indx++, Dependant.getRelation());
				ps.setLong(indx++, Dependant.getLockId());
				ps.setTimestamp(indx++, Dependant.getModifyTs());
				ps.setString(indx++, Dependant.getModifyUser());
				ps.setString(indx++, Dependant.getModifySystem());
				ps.setLong(indx++, Dependant.getDependantKey());

				int count = ps.executeUpdate();
				log.debug("Number of records updated=" + count);
				if (count == 0)
					throw new SQLException("STALE_DATA_ERROR");
				return count;

			}

		});
		log.debug("updateDependant end");

	}

	public void deleteDependant(final Dependant Dependant) throws IOException {
		log.debug("deleteDependant start");

		Long dependantKey = Dependant.getDependantKey();
		log.debug("Key.=" + dependantKey);

		jdbcTemplate.execute(deleteDependantQuery, new PreparedStatementCallback&llt;Object>() {

			@Override
			public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
				ps.setLong(1, Dependant.getDependantKey());
				return ps.executeUpdate();
			}

		});
		log.debug("delete Dependant end");
	}

	public Dependant getDependant(Dependant Dependant) {
		Long dependantKey = Dependant.getDependantKey();

		log.debug("KeyCol for Get::" + dependantKey);
		Dependant DependantFromDB = null;
		try {

			DependantFromDB = jdbcTemplate.queryForObject(getDependantQuery, new Object[] { dependantKey },
					new BeanPropertyRowMapper&llt;Dependant>(Dependant.class));
			log.debug("VAlue=::" + DependantFromDB);
		} catch (EmptyResultDataAccessException ex) {
			log.info("No Record Found. It Maybe expected." + ex.toString());
			return null;
		}

		return DependantFromDB;
	}

	public Dependant getDependantForUpdate(Dependant Dependant) {
		Long dependantKey = Dependant.getDependantKey();

		log.debug("KeyCol for Get::" + dependantKey);
		Dependant DependantFromDB = null;
		try {

			DependantFromDB = jdbcTemplate.queryForObject(getDependantQuery + " FOR UPDATE ",
					new Object[] { dependantKey }, new BeanPropertyRowMapper&llt;Dependant>(Dependant.class));
			log.debug("VAlue=::" + DependantFromDB);
		} catch (EmptyResultDataAccessException ex) {
			log.info("NO RECORD. IT MAY BE EXPECTED." + ex.toString());
			return null;
		}

		return DependantFromDB;
	}

	public List&llt;Dependant> getDependantListByDependantKeyAndOtrs(Dependant Dependant) {
		log.debug("Dependant::" + Dependant);
		Object[] parameters = new Object[] { Dependant.getDependantKey() };

		List&llt;Dependant> DependantObjListFromDB = jdbcTemplate.query(getDependantListByDependantKeyOtrs, parameters,
				new BeanPropertyRowMapper&llt;Dependant>(Dependant.class));

		log.debug("No.Of RecordFoundCount in list=::" + DependantObjListFromDB.size());
		return DependantObjListFromDB;
	}

	public Dependant getDependantByDependantKeyAndOtrs(Dependant Dependant) {
		List&llt;Dependant> DependantObjListFromDB = getDependantListByDependantKeyAndOtrs(Dependant);
		Dependant DependantFromDB = null;

		if (DependantObjListFromDB.size() > 0) {
			DependantFromDB = DependantObjListFromDB.get(DependantObjListFromDB.size() - 1);
			log.debug("DependantFromDBVAlue=::" + DependantObjListFromDB.get(DependantObjListFromDB.size() - 1));
		}

		return DependantFromDB;
	}

	public List&llt;Dependant> getDependantListByDependantKeyAndOths(Dependant Dependant) {
		log.debug("Dependant::" + Dependant);
		Object[] parameters = new Object[] { Dependant.getDependantKey() };

		List&llt;Dependant> DependantObjListFromDB = jdbcTemplate.query(getDependantListByDependantKeyOths, parameters,
				new BeanPropertyRowMapper&llt;Dependant>(Dependant.class));

		log.debug("No.Of RecordFoundCount in list=::" + DependantObjListFromDB.size());
		return DependantObjListFromDB;
	}

	public Dependant getDependantByDependantKeyAndOths(Dependant Dependant) {
		List&llt;Dependant> DependantObjListFromDB = getDependantListByDependantKeyAndOths(Dependant);
		Dependant DependantFromDB = null;

		if (DependantObjListFromDB.size() > 0) {
			DependantFromDB = DependantObjListFromDB.get(DependantObjListFromDB.size() - 1);
			log.debug("DependantFromDBVAlue=::" + DependantObjListFromDB.get(DependantObjListFromDB.size() - 1));
		}

		return DependantFromDB;
	}

	public List&llt;Dependant> getDependantListByDependantKeyList(List&llt;Dependant> DependantList) {
		log.debug("DependantList::" + DependantList);
		String whereClause = " WHERE 1=2 ";
		List&llt;Object> list = new ArrayList&llt;Object>();
		for (Dependant Dependant : DependantList) {
			list.add(Dependant.getDependantKey());
			whereClause = whereClause + " OR DEPENDANT_KEY=? ";
		}

		Object[] parameters = list.toArray();

		List&llt;Dependant> DependantObjListFromDB = jdbcTemplate.query(getDependantListByDependantKeyList + whereClause,
				parameters, new BeanPropertyRowMapper&llt;Dependant>(Dependant.class));

		log.debug("No.Of RecordFoundCount in list=::" + DependantObjListFromDB.size());
		return DependantObjListFromDB;
	}

	public List&llt;Dependant> getDependantListByDynamicAttrs(Dependant Dependant) {
		log.debug("Dependant::" + Dependant);
		List&llt;Object> paramList = new ArrayList&llt;Object>();
		String whereClause = " WHERE 1=1 ";
		if (Dependant.getDependantKey() != null) {
			whereClause = whereClause + " AND DEPENDANT_KEY=? ";
			paramList.add(Dependant.getDependantKey());
		}
		if (Dependant.getEmployeeKey() != null) {
			whereClause = whereClause + " AND EMPLOYEE_KEY=? ";
			paramList.add(Dependant.getEmployeeKey());
		}
		if (Dependant.getAge() != null) {
			whereClause = whereClause + " AND AGE=? ";
			paramList.add(Dependant.getAge());
		}
		if (Dependant.getLocation() != null) {
			whereClause = whereClause + " AND LOCATION=? ";
			paramList.add(Dependant.getLocation());
		}
		if (Dependant.getName() != null) {
			whereClause = whereClause + " AND NAME=? ";
			paramList.add(Dependant.getName());
		}
		if (Dependant.getRelation() != null) {
			whereClause = whereClause + " AND RELATION=? ";
			paramList.add(Dependant.getRelation());
		}

		Object[] parameters = paramList.toArray();

		log.debug("DynamicSQL::" + getDependantListByDynamicAttrs + whereClause);
		List&llt;Dependant> DependantObjListFromDB = jdbcTemplate.query(getDependantListByDynamicAttrs + whereClause,
				parameters, new BeanPropertyRowMapper&llt;Dependant>(Dependant.class));

		log.debug("No.Of RecordFoundCount in list=::" + DependantObjListFromDB.size());
		return DependantObjListFromDB;
	}

	public void insertDependantBatch(List&llt;Dependant> DependantList) throws SQLException {
		for (int i = 0; i &llt; DependantList.size(); i += this.batchInsertSize) {
			final List&llt;Dependant> DependantBatch = DependantList.subList(i,
					i + this.batchInsertSize > DependantList.size() ? DependantList.size() : i + batchInsertSize);
			int[] noOfDependantsInserted = jdbcTemplate.batchUpdate(this.createDependantQuery,
					new BatchPreparedStatementSetter() {
						public void setValues(PreparedStatement pStmt, int j) throws SQLException {
							Dependant Dependant = (Dependant) DependantBatch.get(j);
							int indx = 1;
							pStmt.setLong(indx++, Dependant.getDependantKey());
							pStmt.setLong(indx++, Dependant.getEmployeeKey());
							pStmt.setString(indx++, Dependant.getAge());
							pStmt.setString(indx++, Dependant.getLocation());
							pStmt.setString(indx++, Dependant.getName());
							pStmt.setString(indx++, Dependant.getRelation());
							pStmt.setLong(indx++, Dependant.getLockId());
							pStmt.setTimestamp(indx++, Dependant.getCreateTs());
							pStmt.setString(indx++, Dependant.getCreateUser());
							pStmt.setString(indx++, Dependant.getCreateSystem());
							pStmt.setTimestamp(indx++, Dependant.getModifyTs());
							pStmt.setString(indx++, Dependant.getModifyUser());
							pStmt.setString(indx++, Dependant.getModifySystem());
							ObjectMapper mapper = new ObjectMapper();
							// Java objects to JSON string
							String jsonString = "";

							log.debug("attributes json" + jsonString);
							pStmt.setString(indx++, jsonString);
						}

						public int getBatchSize() {
							return DependantBatch.size();
						}
					});
			log.debug("insertDependantsBatch end");
		}
	}

//##PLACE_FOR_PUB_API_DAO_CODE##// 
}

---

H.) Dependent RowMapper

package com.easy.hr.dependant.dao;

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

import org.apache.log4j.Logger;
import org.springframework.jdbc.core.RowMapper;

import com.easy.hr.dependant.bean.Dependant;

public class DependantRowMapper implements RowMapper {
	private static Logger log = Logger.getLogger(DependantRowMapper.class.getName());

	public Dependant mapRow(ResultSet resultSet, int rowNum) throws SQLException {
		Dependant Dependant = new Dependant();
		Dependant.setDependantKey(resultSet.getLong("DEPENDANT_KEY"));
		Dependant.setEmployeeKey(resultSet.getLong("EMPLOYEE_KEY"));
		Dependant.setAge(resultSet.getString("AGE"));
		Dependant.setLocation(resultSet.getString("LOCATION"));
		Dependant.setName(resultSet.getString("NAME"));
		Dependant.setRelation(resultSet.getString("RELATION"));
		Dependant.setLockId(resultSet.getLong("LOCK_ID"));
		Dependant.setCreateTs(resultSet.getTimestamp("CREATE_TS"));
		Dependant.setCreateUser(resultSet.getString("CREATE_USER"));
		Dependant.setCreateSystem(resultSet.getString("CREATE_SYSTEM"));
		Dependant.setModifyTs(resultSet.getTimestamp("MODIFY_TS"));
		Dependant.setModifyUser(resultSet.getString("MODIFY_USER"));
		Dependant.setModifySystem(resultSet.getString("MODIFY_SYSTEM"));
		return Dependant;
	};
}

---

7.) Add Annotation to the Application startup springboot file.(e.g;) SpringjdbcdemoApplication.java)

package com.example.demo;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.ComponentScan;

@SpringBootApplication
@ComponentScan(basePackages = {"com.*"})
@ComponentScan(basePackages={"org.springframework.jdbc.*"})
public class SpringjdbcdemoApplication {

	public static void main(String[] args) {
		SpringApplication.run(SpringjdbcdemoApplication.class, args);
	}

}

---

8.) Bean Classes, Helper Classes and Other Utility classes:
Bean and helper classes does not have any logic. Bean classes has attributes and its getter and setter methods. Helper classes has only the copy method to copy the values Please download the bean helper classes. Ulitlity classes has some util methods.

---

9.) DAO Properties Files: Create following properties file for database operations. employee-dao.properties

EmployeeDao.createEmployee=INSERT INTO FND_EMPLOYEE(EMPLOYEE_KEY, DATE_OF_JOINING, DEPARTMENT, DESIGNATION, EMPLOYEE_NO, FIRST_NAME, LAST_NAME, LOCK_ID, CREATE_TS, CREATE_USER, CREATE_SYSTEM, MODIFY_TS, MODIFY_USER, MODIFY_SYSTEM) VALUES ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?); 
EmployeeDao.updateEmployee=UPDATE FND_EMPLOYEE SET DATE_OF_JOINING = ?, DEPARTMENT = ?, DESIGNATION = ?, EMPLOYEE_NO = ?, FIRST_NAME = ?, LAST_NAME = ?, LOCK_ID = ?, MODIFY_TS = ?, MODIFY_USER = ?, MODIFY_SYSTEM = ? WHERE EMPLOYEE_KEY=?  AND 1=1 ;
EmployeeDao.deleteEmployee=DELETE FROM FND_EMPLOYEE WHERE EMPLOYEE_KEY=?;
EmployeeDao.getEmployee=SELECT EMPLOYEE_KEY, DATE_OF_JOINING, DEPARTMENT, DESIGNATION, EMPLOYEE_NO, FIRST_NAME, LAST_NAME, LOCK_ID, CREATE_TS, CREATE_USER, CREATE_SYSTEM, MODIFY_TS, MODIFY_USER, MODIFY_SYSTEM FROM FND_EMPLOYEE WHERE EMPLOYEE_KEY=? 
EmployeeDao.getEmployeeForUpdate=SELECT EMPLOYEE_KEY, DATE_OF_JOINING, DEPARTMENT, DESIGNATION, EMPLOYEE_NO, FIRST_NAME, LAST_NAME, LOCK_ID, CREATE_TS, CREATE_USER, CREATE_SYSTEM, MODIFY_TS, MODIFY_USER, MODIFY_SYSTEM FROM FND_EMPLOYEE WHERE EMPLOYEE_KEY=? FOR UPDATE 
EmployeeDao.findEmployeeQuery=SELECT EMPLOYEE_KEY, DATE_OF_JOINING, DEPARTMENT, DESIGNATION, EMPLOYEE_NO, FIRST_NAME, LAST_NAME, LOCK_ID, CREATE_TS, CREATE_USER, CREATE_SYSTEM, MODIFY_TS, MODIFY_USER, MODIFY_SYSTEM FROM FND_EMPLOYEE WHERE EMPLOYEE_KEY=?;
EmployeeDao.getEmployeeListByEmployeeKeyOtrs=SELECT EMPLOYEE_KEY, DATE_OF_JOINING, DEPARTMENT, DESIGNATION, EMPLOYEE_NO, FIRST_NAME, LAST_NAME, LOCK_ID, CREATE_TS, CREATE_USER, CREATE_SYSTEM, MODIFY_TS, MODIFY_USER, MODIFY_SYSTEM FROM FND_EMPLOYEE WHERE  EMPLOYEE_KEY=? ;
EmployeeDao.getEmployeeListByEmployeeKeyList=SELECT EMPLOYEE_KEY, DATE_OF_JOINING, DEPARTMENT, DESIGNATION, EMPLOYEE_NO, FIRST_NAME, LAST_NAME, LOCK_ID, CREATE_TS, CREATE_USER, CREATE_SYSTEM, MODIFY_TS, MODIFY_USER, MODIFY_SYSTEM FROM FND_EMPLOYEE 
EmployeeDao.getEmployeeListByDynamicAttrs=SELECT EMPLOYEE_KEY, DATE_OF_JOINING, DEPARTMENT, DESIGNATION, EMPLOYEE_NO, FIRST_NAME, LAST_NAME, LOCK_ID, CREATE_TS, CREATE_USER, CREATE_SYSTEM, MODIFY_TS, MODIFY_USER, MODIFY_SYSTEM FROM FND_EMPLOYEE 
EmployeeDao.getEmployeeListByEmployeeKeyOths=SELECT EMPLOYEE_KEY, DATE_OF_JOINING, DEPARTMENT, DESIGNATION, EMPLOYEE_NO, FIRST_NAME, LAST_NAME, LOCK_ID, CREATE_TS, CREATE_USER, CREATE_SYSTEM, MODIFY_TS, MODIFY_USER, MODIFY_SYSTEM FROM FND_EMPLOYEE WHERE  EMPLOYEE_KEY=? ;
EmployeeDao.insertBatchSize=100

dependant-dao.properties

DependantDao.createDependant=INSERT INTO FND_DEPENDANT(DEPENDANT_KEY, EMPLOYEE_KEY, AGE, LOCATION, NAME, RELATION, LOCK_ID, CREATE_TS, CREATE_USER, CREATE_SYSTEM, MODIFY_TS, MODIFY_USER, MODIFY_SYSTEM) VALUES ( ?,?,?,?,?,?,?,?,?,?,?,?,?); 
DependantDao.updateDependant=UPDATE FND_DEPENDANT SET EMPLOYEE_KEY = ?, AGE = ?, LOCATION = ?, NAME = ?, RELATION = ?, LOCK_ID = ?, MODIFY_TS = ?, MODIFY_USER = ?, MODIFY_SYSTEM = ? WHERE DEPENDANT_KEY=?  AND 1=1 ;
DependantDao.deleteDependant=DELETE FROM FND_DEPENDANT WHERE DEPENDANT_KEY=?;
DependantDao.getDependant=SELECT DEPENDANT_KEY, EMPLOYEE_KEY, AGE, LOCATION, NAME, RELATION, LOCK_ID, CREATE_TS, CREATE_USER, CREATE_SYSTEM, MODIFY_TS, MODIFY_USER, MODIFY_SYSTEM FROM FND_DEPENDANT WHERE DEPENDANT_KEY=? 
DependantDao.getDependantForUpdate=SELECT DEPENDANT_KEY, EMPLOYEE_KEY, AGE, LOCATION, NAME, RELATION, LOCK_ID, CREATE_TS, CREATE_USER, CREATE_SYSTEM, MODIFY_TS, MODIFY_USER, MODIFY_SYSTEM FROM FND_DEPENDANT WHERE DEPENDANT_KEY=? FOR UPDATE 
DependantDao.findDependantQuery=SELECT DEPENDANT_KEY, EMPLOYEE_KEY, AGE, LOCATION, NAME, RELATION, LOCK_ID, CREATE_TS, CREATE_USER, CREATE_SYSTEM, MODIFY_TS, MODIFY_USER, MODIFY_SYSTEM FROM FND_DEPENDANT WHERE DEPENDANT_KEY=?;
DependantDao.getDependantListByDependantKeyOtrs=SELECT DEPENDANT_KEY, EMPLOYEE_KEY, AGE, LOCATION, NAME, RELATION, LOCK_ID, CREATE_TS, CREATE_USER, CREATE_SYSTEM, MODIFY_TS, MODIFY_USER, MODIFY_SYSTEM FROM FND_DEPENDANT WHERE  DEPENDANT_KEY=? ;
DependantDao.getDependantListByDependantKeyList=SELECT DEPENDANT_KEY, EMPLOYEE_KEY, AGE, LOCATION, NAME, RELATION, LOCK_ID, CREATE_TS, CREATE_USER, CREATE_SYSTEM, MODIFY_TS, MODIFY_USER, MODIFY_SYSTEM FROM FND_DEPENDANT 
DependantDao.getDependantListByDynamicAttrs=SELECT DEPENDANT_KEY, EMPLOYEE_KEY, AGE, LOCATION, NAME, RELATION, LOCK_ID, CREATE_TS, CREATE_USER, CREATE_SYSTEM, MODIFY_TS, MODIFY_USER, MODIFY_SYSTEM FROM FND_DEPENDANT 
DependantDao.getDependantListByDependantKeyOths=SELECT DEPENDANT_KEY, EMPLOYEE_KEY, AGE, LOCATION, NAME, RELATION, LOCK_ID, CREATE_TS, CREATE_USER, CREATE_SYSTEM, MODIFY_TS, MODIFY_USER, MODIFY_SYSTEM FROM FND_DEPENDANT WHERE  DEPENDANT_KEY=? ;
DependantDao.insertBatchSize=100

---

10.) Common Mistakes

While creating the project, Not selecting the War option instead of Jar option Not having @ComponentScan annotation (e.g; @ComponentScan(basePackages = {"com.*"})) in Starter Java File Not having Annotation on the service class annotation (e.g; @org.springframework.stereotype.Service) in Starter Java File Not having entry for Datasource, userid, and password in application.properties file Not having correct dependencies defined in pom.xml. Dependencis are different from Spring MVC.

---

11.)Do Maven Update and RunAs Maven Install :

---

12.)Run project as Springboot App : 13.)Test Service : Invoke the following URL with PUT mehtod.
http://localhost:8080/createEmployee/create
Method=PUT
Input:

{
    "firstName": "Frank",
    "lastName": "Moor",
    "dependants": {
        "dependantx": [
            {
                "name": "George Moor",
                "location": "London",
                "age": 70,
                "relation": "Father"
            },
            {
                "name": "Sara Moor",
                "location": "London",
                "age": 75,
                "relation": "Mother"
            }
        ]
    },
    "designation": "Manager",
    "department": "HRD",
    "employeeNo": 1001
}

---

14.)Output : Json:

{
    "lockId": "1",
    "createTs": "2021-08-08 19:35:47.987",
    "modifyTs": "2021-08-08 19:35:47.987",
    "modifyUser": "SYSUSER",
    "modifySystem": "SYSTEM",
    "createUser": "SYSUSER",
    "createSystem": "SYSTEM",
    "status": "",
    "message": "",
    "errorCode": "",
    "errorMessage": "",
    "employeeKey": "1628437362882",
    "department": "HRD",
    "designation": "Manager",
    "employeeNo": "1001",
    "firstName": "Frank",
    "lastName": "Moor",
    "dependants": {
        "lockId": "",
        "status": "",
        "message": "",
        "errorCode": "",
        "errorMessage": "",
        "dependant": [
            {
                "lockId": "1",
                "createTs": "2021-08-08 19:35:48.98",
                "modifyTs": "2021-08-08 19:35:48.98",
                "modifyUser": "SYSUSER",
                "modifySystem": "SYSTEM",
                "createUser": "SYSUSER",
                "createSystem": "SYSTEM",
                "status": "",
                "message": "",
                "errorCode": "",
                "errorMessage": "",
                "dependantKey": "1628438075506",
                "employeeKey": "1628437362882",
                "age": "70",
                "location": "London",
                "name": "George Moor",
                "relation": "Father"
            },
            {
                "lockId": "1",
                "createTs": "2021-08-08 19:35:49.091",
                "modifyTs": "2021-08-08 19:35:49.091",
                "modifyUser": "SYSUSER",
                "modifySystem": "SYSTEM",
                "createUser": "SYSUSER",
                "createSystem": "SYSTEM",
                "status": "",
                "message": "",
                "errorCode": "",
                "errorMessage": "",
                "dependantKey": "1628438152148",
                "employeeKey": "1628437362882",
                "age": "75",
                "location": "London",
                "name": "Sara Moor",
                "relation": "Mother"
            }
        ]
    }
}

---

15.) Download Entire Project Source Code.

Please click here to download the entire project code(shown in this page) . Entire code was generated by using online tool REST API Code Generator One video on this topic is available in youtube!

Please watch this video. Everything is explained in this video. https://youtu.be/sWx8p9vLOF0

Please click here to download the entire project code(shown in video). Entire code was generated by using online tool REST API Code Generator One video on this topic will be available soon in youtube!

---

---

User Comments:

Post Your Comment: