Code Robo
Virtual Service
Comparator
Tester
Converter
Utility
Java Code Complience
Validator
Formatter
EncoderDecoder



How To Retrieve Data From Multiple Tables Using Spring JdbcTemplate.
       Talk to EasyAssistant

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(https://youtu.be/aJFosI9TfzQ). Please watch it.
 
Here only bean classes and DAO code are given. Entire example code is available for download. Click here to download .
Please provide your feedback here

User Comments:
anonymous:
Thanks for this article! I was struggling to map the data from multiple tables to java object hierarchy. It helped me immensely to figure out the solution!
    Reply;


Post Your Comment:
Name :
Email ( Optional) :
Comments / Suggestion (* Required) It is required: :
: