Code Robo
Tester
Converter
Utility
Java Code Complience
Validator
Formatter
Encoder & Decoder
How To Retrieve Data From Multiple Tables Using Spring JdbcTemplate.

This page demonstrate how to retrieve data as a java object from many tables using spring framework JDBCTemplate.. There could be One-To-Many or Many-To-Many relationship between the tables.

Here one example is given to show how to retrieve data joining multiple tables using SpringJdbcTemplate and ResultSetExtractor. In this example we have 3 datable tables. They have One-To-Many relationship between the tables. Here are the 3 tables
  • EMPLOYEE
  • DEPENDANT
  • HOSPITALISATION
We want to fetch list of all the employee objects (whose salary < 10000) along with their dempendant and hospitalisation record. So we will be executing following query.
SELECT
   EMPLOYEE.ADDRESS,
   EMPLOYEE.BASIC_SAL,
   EMPLOYEE.DATE_OF_JOINING,
   EMPLOYEE.DEPT_CODE,
   EMPLOYEE.EMP_ID,
   EMPLOYEE.EMPLOYEE_KEY,
   EMPLOYEE.NAME,
   DEPENDANT.AGE,
   DEPENDANT.DEPENDANT_ID,
   DEPENDANT.DEPENDANT_KEY,
   DEPENDANT.DEPENDANT_NAME,
   DEPENDANT.EMP_ID,
   DEPENDANT.RELATION_WITH_EMP,
   HOSPITALISATION.ADMISSION_DATE,
   HOSPITALISATION.DEPENDANT_ID,
   HOSPITALISATION.EMP_ID,
   HOSPITALISATION.HOSPITAL_BILL_AMT,
   HOSPITALISATION.HOSPITAL_NAME,
   HOSPITALISATION.HOSPITALISATION_KEY,
   HOSPITALISATION.RELEASE_DATE,
   HOSPITALISATION.TREATMENT_DTLS 
FROM
   EMPLOYEE,
   DEPENDANT,
   HOSPITALISATION 
WHERE
   1 = 1 
   AND EMPLOYEE.EMP_ID = DEPENDANT.EMP_ID 
   AND DEPENDANT.DEPENDANT_ID = HOSPITALISATION.DEPENDANT_ID 
   AND EMPLOYEE.BASIC_SAL < 10000
Now lets see how we can execute the query and get the data as list of employee objects.

1. Database Table Structure
EMPLOYEE TABLE:
 	
CREATE TABLE `employee` (
  `EMPLOYEE_KEY` bigint  PRIMARY KEY, 
  `EMP_ID` varchar(100) DEFAULT NULL,
  `NAME` varchar(100) DEFAULT NULL,
  `DEPT_CODE` varchar(100) DEFAULT NULL,
  `BASIC_SAL` decimal(8,2) DEFAULT NULL,
  `DATE_OF_JOINING` date DEFAULT NULL,
  `ADDRESS` varchar(500) DEFAULT NULL
)
DEPENDANT:
CREATE TABLE `dependant` (
  `DEPENDANT_KEY` bigint  PRIMARY KEY,
  `DEPENDANT_ID` varchar(100) DEFAULT NULL,
  `DEPENDANT_NAME` varchar(100) DEFAULT NULL,
  `EMP_ID` varchar(100) DEFAULT NULL,
  `RELATION_WITH_EMP` varchar(100) DEFAULT NULL,
  `AGE` decimal(5,2) DEFAULT NULL
) 
HOSPITALISATION:
CREATE TABLE `hospitalisation` (
  `HOSPITALISATION_KEY` bigint  PRIMARY KEY,
  `DEPENDANT_ID` varchar(100) DEFAULT NULL,
  `EMP_ID` varchar(100) DEFAULT NULL,
  `ADMISSION_DATE` date DEFAULT NULL,
  `RELEASE_DATE` date DEFAULT NULL,
  `HOSPITAL_NAME` varchar(200) DEFAULT NULL,
  `TREATMENT_DTLS` varchar(500) DEFAULT NULL,
  `HOSPITAL_BILL_AMT` double(7,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

2. JAVA BEAN CLASSES (Getter and Setter methods are not given to keep the document shorter.)
Employee :
public class Employee {
	Dependants dependants;
	private String address;
	private Double basicSal;
	private Date dateOfJoining;
	private String deptCode;
	private String empId;
	private Long employeeKey;
	private String name;
}
Employees:
public class Employees  {
	private List<Employee> employeeList = new ArrayList();
}
Dependant:
public class Dependant {
	Hospitalisations hospitalisations;
	private String age;
	private Long employeeKey;
	private String dependantId;
	private Long dependantKey;
	private String dependantName;
	private String empId;
	private String relationWithEmp;
	private String tableName;
}
Dependants:
public class Dependants {
	private List<Dependant> dependantList = new ArrayList();
}
Hospitalisations:
public class Hospitalisations {
	private List<Hospitalisation> hospitalisationList = new ArrayList();
}
Hospitalisation:
public class Hospitalisation {
	private Long hospitalisationKey;
	private String dependantId;
	private String empId;
	private Date admissionDate;
	private Date releaseDate;
	private String hospitalName;
	private String treatmentDtls;
	private Double hospitalBillAmt;
	private String defaultstr;
	
}
3. DAO Class
DAO Code:
				
public class EmployeeDAO {
	private static Logger log = LoggerFactory.getLogger(EmployeeDAO.class);
	@Autowired
	private JdbcTemplate jdbcTemplate;

	public List<Employee> getEmplist(Employee employee) {
	
String str="";
str=str+"SELECT\n";
str=str+"   EMPLOYEE.ADDRESS,\n";
str=str+"   EMPLOYEE.BASIC_SAL,\n";
str=str+"   EMPLOYEE.DATE_OF_JOINING,\n";
str=str+"   EMPLOYEE.DEPT_CODE,\n";
str=str+"   EMPLOYEE.EMP_ID,\n";
str=str+"   EMPLOYEE.EMPLOYEE_KEY,\n";
str=str+"   EMPLOYEE.NAME,\n";
str=str+"   DEPENDANT.AGE,\n";
str=str+"   DEPENDANT.DEPENDANT_ID,\n";
str=str+"   DEPENDANT.DEPENDANT_KEY,\n";
str=str+"   DEPENDANT.DEPENDANT_NAME,\n";
str=str+"   DEPENDANT.EMP_ID,\n";
str=str+"   DEPENDANT.RELATION_WITH_EMP,\n";
str=str+"   HOSPITALISATION.ADMISSION_DATE,\n";
str=str+"   HOSPITALISATION.DEPENDANT_ID,\n";
str=str+"   HOSPITALISATION.EMP_ID,\n";
str=str+"   HOSPITALISATION.HOSPITAL_BILL_AMT,\n";
str=str+"   HOSPITALISATION.HOSPITAL_NAME,\n";
str=str+"   HOSPITALISATION.HOSPITALISATION_KEY,\n";
str=str+"   HOSPITALISATION.RELEASE_DATE,\n";
str=str+"   HOSPITALISATION.TREATMENT_DTLS \n";
str=str+"FROM\n";
str=str+"   EMPLOYEE,\n";
str=str+"   DEPENDANT,\n";
str=str+"   HOSPITALISATION \n";
str=str+"WHERE\n";
str=str+"   1 = 1 \n";
str=str+"   AND EMPLOYEE.EMP_ID = DEPENDANT.EMP_ID \n";
str=str+"   AND DEPENDANT.DEPENDANT_ID = HOSPITALISATION.DEPENDANT_ID \n";
str=str+"   AND EMPLOYEE.BASIC_SAL < 10000\n";
	
	
	return jdbcTemplate.query(str, new ResultSetExtractor<List
								<Employee> >() {
		@Override
		public List<Employee> extractData(ResultSet rs) throws SQLException, DataAccessException {

			List<Employee> list = new ArrayList<Employee>();
			Map<Long, Employee> employeeKeyEmployeeMap = new HashMap<Long, Employee>();
			Map<Long, Dependant> dependantKeyDependantMap = new HashMap<Long, Dependant>();
			Map<Long, Hospitalisation> hospitalisationKeyHospitalisationMap = new HashMap<Long, Hospitalisation>();
			while (rs.next()) {

				Long employeeKey = rs.getLong("EMPLOYEE_KEY");
				Employee employee = employeeKeyEmployeeMap.get(employeeKey);
				if (employee == null) {
					employee = new Employee();
					list.add(employee);
					employee.setEmployeeKey(employeeKey);
					log.info("employeeKey=" + employee.getEmployeeKey());
					employee.setAddress(rs.getString("ADDRESS"));
					employee.setBasicSal(rs.getDouble("BASIC_SAL"));
					employee.setDateOfJoining(rs.getDate("DATE_OF_JOINING"));
					employee.setDeptCode(rs.getString("DEPT_CODE"));
					employee.setEmpId(rs.getString("EMP_ID"));
					employee.setEmployeeKey(rs.getLong("EMPLOYEE_KEY"));
					employee.setName(rs.getString("NAME"));
				}
				Long dependantKey = rs.getLong("BOOK_KEY");
				Dependant dependant = dependantKeyDependantMap.get(dependantKey);
				log.info("dependantKeyML=" + dependantKey);
				if (dependant == null) {
					dependant = new Dependant();
					if (employee.getDependants() == null)
						employee.setDependants(new Dependants());
					if (employee.getDependants().getDependantList() == null)
						employee.getDependants().setDependantList(new ArrayList<Dependant>());

					employee.getDependants().getDependantList().add(dependant);
					dependantKeyDependantMap.put(dependantKey, dependant);
					dependant.setDependantKey(dependantKey);
					dependant.setAge(rs.getString("AGE"));
					dependant.setDependantId(rs.getString("DEPENDANT_ID"));
					dependant.setDependantKey(rs.getLong("DEPENDANT_KEY"));
					dependant.setDependantName(rs.getString("DEPENDANT_NAME"));
					dependant.setEmpId(rs.getString("EMP_ID"));
					dependant.setRelationWithEmp(rs.getString("RELATION_WITH_EMP"));
				}
				Long hospitalisationKey = rs.getLong("BOOK_KEY");
				Hospitalisation hospitalisation = hospitalisationKeyHospitalisationMap.get(hospitalisationKey);
				log.info("hospitalisationKeyML=" + hospitalisationKey);
				if (hospitalisation == null) {
					hospitalisation = new Hospitalisation();
					if (dependant.getHospitalisations() == null)
						dependant.setHospitalisations(new Hospitalisations());
					if (dependant.getHospitalisations().getHospitalisationList() == null)
						dependant.getHospitalisations().setHospitalisationList(new ArrayList<Hospitalisation>());

					dependant.getHospitalisations().getHospitalisationList().add(hospitalisation);
					hospitalisationKeyHospitalisationMap.put(hospitalisationKey, hospitalisation);
					hospitalisation.setHospitalisationKey(hospitalisationKey);
					hospitalisation.setAdmissionDate(rs.getDate("ADMISSION_DATE"));
					hospitalisation.setDependantId(rs.getString("DEPENDANT_ID"));
					hospitalisation.setEmpId(rs.getString("EMP_ID"));
					hospitalisation.setHospitalBillAmt(rs.getDouble("HOSPITAL_BILL_AMT"));
					hospitalisation.setHospitalName(rs.getString("HOSPITAL_NAME"));
					hospitalisation.setHospitalisationKey(rs.getLong("HOSPITALISATION_KEY"));
					hospitalisation.setReleaseDate(rs.getDate("RELEASE_DATE"));
					hospitalisation.setTreatmentDtls(rs.getString("TREATMENT_DTLS"));
				}
			}
			return list;
		}
	});
	}

}

It has been explained in this video. Please watch it.
Here only bean classes and DAO code are given. Entire example code is available for download.
Please provide your feedback here