What is Spring Boot:
Spring Boot is an open source, microservice-based Java web framework.
RowMapper:
RowMapper interface is used by JdbcTemplate for mapping rows of a ResultSet on a per-row basis.
Introduction
Here we are going to use custom row mapper when we want list of objects as return type.
Problem in RowMapper for nested Classes:
We can't directly assign parameters returned from DB to the class model because we never able to exact individual primitive data types variable due to nesting of classes.
Class Diagram used in this Example:
Simple Class : Not Nested Class
SportsMasterDTO.java
package workspace;
public class SportsMasterDTO {
private int id;
private String sportsCode;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getSportsCode() {
return sportsCode;
}
public void setSportsCode(String sportsCode) {
this.sportsCode = sportsCode;
}
}
SportsMetaDTO Class:
package workspace;
public class SportsMetaDTO {
private int id;
private int sportsName;
private int sportsMasterId;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getSportsName() {
return sportsName;
}
public void setSportsName(int sportsName) {
this.sportsName = sportsName;
}
public int getSportsMasterId() {
return sportsMasterId;
}
public void setSportsMasterId(int sportsMasterId) {
this.sportsMasterId = sportsMasterId;
}
@Override
public String toString() {
return "SportsMetaDTO [id=" + id + ", sportsName=" + sportsName + ", sportsMasterId="
+ sportsMasterId + "]";
}
}
ThresholdDTO class
package workspace;
public class ThresholdDTO {
private int id;
private int sportsMetaDataId;
private String value;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getSportsMetaDataId() {
return sportsMetaDataId;
}
public void setSportsMetaDataId(int sportsMetaDataId) {
this.sportsMetaDataId = sportsMetaDataId;
}
public String getValue() {
return value;
}
public void setValue(String value) {
this.value = value;
}
@Override
public String toString() {
return "ThresholdDTO [id=" + id + ", sportsMetaDataId=" + sportsMetaDataId + ", value=" + value
+ "]";
}
}
Nested Class:
Nested Class: SportsThresholdCommonDTO
package workspace;
public class SportsThresholdCommonDTO {
private SportsMasterDTO sportsMasterDTO;
private SportsMetaDTO sportsMetaDTO;
private ThresholdDTO thresholdDTO;
public SportsMasterDTO getSportsMasterDTO() {
return sportsMasterDTO;
}
public void setSportsMasterDTO(SportsMasterDTO sportsMasterDTO) {
this.sportsMasterDTO = sportsMasterDTO;
}
public SportsMetaDTO getSportsMetaDTO() {
return sportsMetaDTO;
}
public void setSportsMetaDTO(SportsMetaDTO sportsMetaDTO) {
this.sportsMetaDTO = sportsMetaDTO;
}
public ThresholdDTO getThresholdDTO() {
return thresholdDTO;
}
public void setThresholdDTO(ThresholdDTO thresholdDTO) {
this.thresholdDTO = thresholdDTO;
}
@Override
public String toString() {
return "SportsThresholdCommonDTO [sportsMasterDTO=" + sportsMasterDTO + ", sportsMetaDTO="
+ sportsMetaDTO + ", thresholdDTO=" + thresholdDTO + "]";
}
}
MYSQL Query Which contain joins on 3 tables with no limit
select
sm.id as sportsMasterId,
sm.sportsCode,
smd.id as sportsMetaId,
smd.sportsName,
smd.sportsMasterId,
t.id as thresholdId,
t.sportsMetaDataId,
t.value
from
sportsMaster
inner join sportsMetaData smd on sm.id = smd.sportsMasterId
inner join threshold t on t.sporsMetaDataId = smd.id
How to use row mapper if mysql returns a list of objects for nested class:
Java Method
public List<AlertingMasterThresholdCommonDTO> getSportsThresholdCommonList() {
String sql =
"select sm.id as sportsMasterId, sm.sportsCode, smd.id as sportsMetaId, smd.sportsName, smd.sportsMasterId, t.id as thresholdId, t.sportsMetaDataId, t.value from sportsMaster inner join sportsMetaData smd on sm.id = smd.sportsMasterId inner join threshold t on t.sporsMetaDataId = smd.id";
return sportsThresholdCommonMapperList
.mapper(jdbcTemplate.queryForList(sql));
}
The Wait Ends Here !!!
Mapper Class
package workspace;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
public class SportsThresholdCommonMapperList {
public List<SportsThresholdCommonDTO> mapper(List<Map<String, Object>> rows) {
List<SportsThresholdCommonDTO> sportsThresholdCommonDTOs = new ArrayList<>();
for (Map rs : rows) {
SportsThresholdCommonDTO sportsThresholdCommonDTO = new SportsThresholdCommonDTO();
SportsMasterDTO sportsMasterDTO = new SportsMasterDTO();
SportsMetaDTO sportsMetaDTO = new SportsMetaDTO();
ThresholdDTO thresholdDTO = new ThresholdDTO();
sportsMasterDTO.setId((Integer) rs.get("sportsMasterId"));
sportsMasterDTO.setSportsCode((String) rs.get("sportsCode"));
sportsMetaDTO.setId((Integer) rs.get("sportsMetaId"));
sportsMetaDTO.setSportsName((String) rs.get("sportsName"));
sportsMetaDTO.setSportsMasterId((Integer) rs.get("sportsMasterId"));
thresholdDTO.setId((Integer) rs.get("thresholdId"));
thresholdDTO.setSportsMetaDataId((Integer) rs.get("sportsMetaId"));
thresholdDTO.setValue((String) rs.get("value"));
sportsThresholdCommonDTO.setSportsMasterDTO(sportsMasterDTO);
sportsThresholdCommonDTO.setSportsMetaDTO(sportsMetaDTO);
sportsThresholdCommonDTO.setThresholdDTO(thresholdDTO);
sportsThresholdCommonDTOs.add(sportsThresholdCommonDTO);
}
return sportsThresholdCommonDTOs;
}
}
Alternate Way:
- Make a new model which contains variable of all classes
- Make Aliases to map t model properly
- Result will be a unnested class with all data.
Drawback of making another model
- Maintainability overheads
- Making aliases and keeping different names in all models
Conclusion
We are able to get nested class object list from mysql with the help of custom row mapper.
Top comments (0)