SqlResultSetMapping을 정의해야 한다.
Unknown SqlResultSetMapping 에러가 발생하면 해당 Entity 클래스가 제대로 디플로이 되지 않은 것이다.
정상적으로 디플로이된 Entity 클래스에 넣어야 한다.
또한 조회되는 sql 컬럼명은 모든 Entity field 로 매핑해줘야 한다.
아래 내용은 순서적으로 Query결과를 Entity 로 2개, column Scalar 로 1개를 받는 것에 대한 내용이다.
@Entity
@Table(name = "EM_TEMPLOYEE_DATA")
@SqlResultSetMapping(name="GetEmpRelation",
entities = {
@EntityResult(entityClass = ep.emprelation.entity.EmpInfo.class, fields={
@FieldResult(name="id", column="sourceEpId"),
@FieldResult(name="name", column="sourceName"),
@FieldResult(name="orgCode", column="sourceOrgCode"),
@FieldResult(name="orgName", column="sourceOrgName"),
@FieldResult(name="subOrgCode", column="sourceSubOrgCode"),
@FieldResult(name="subOrgName", column="sourceSubOrgName"),
@FieldResult(name="deptCode", column="sourceDeptCode"),
@FieldResult(name="deptName", column="sourceDeptName"),
@FieldResult(name="titleCode", column="sourceTitleCode"),
@FieldResult(name="titleName", column="sourceTitleName"),
@FieldResult(name="mail", column="sourceMail"),
@FieldResult(name="nickName", column="sourceNickName"),
@FieldResult(name="mobile", column="sourceMobile"),
@FieldResult(name="picture", column="sourcePicture")
}),
@EntityResult(entityClass = ep.emprelation.entity.EmpInfo.class, fields={
@FieldResult(name="id", column="targetEpId"),
@FieldResult(name="name", column="targetName"),
@FieldResult(name="orgCode", column="targetOrgCode"),
@FieldResult(name="orgName", column="targetOrgName"),
@FieldResult(name="subOrgCode", column="targetSubOrgCode"),
@FieldResult(name="subOrgName", column="targetSubOrgName"),
@FieldResult(name="deptCode", column="targetDeptCode"),
@FieldResult(name="deptName", column="targetDeptName"),
@FieldResult(name="titleCode", column="targetTitleCode"),
@FieldResult(name="titleName", column="targetTitleName"),
@FieldResult(name="mail", column="targetMail"),
@FieldResult(name="nickName", column="targetNickName"),
@FieldResult(name="mobile", column="targetMobile"),
@FieldResult(name="picture", column="targetPicture")
})
},
columns = {
@ColumnResult(name="point")
}
)
public class EmpInfo {.......}
다음은 DAO에서 객체를 매팅으로 받을 때
public EmpRelation findRelationById(final String targetId) {
return (EmpRelation) this.getJpaTemplate().execute(new JpaCallback<Object>() {
String sql = " select A.sourceEpID sourceEpId, A.targetEpId targetEpId, A.point point, " +
" B.name sourceName, B.orgCode sourceOrgCode, B.orgName sourceOrgName, " +
" B.subOrgCode sourceSubOrgCode, B.subOrgName sourceSubOrgName, " +
" B.deptCode sourceDeptCode, B.deptName sourceDeptName, " +
" B.titleCode sourceTitleCode, B.titleName sourceTitleName, " +
" B.mail sourceMail, B.nickName sourceNickName, " +
" B.mobile sourceMobile, B.picture sourcePicture, " +
" C.name targetName, C.orgCode targetOrgCode, C.orgName targetOrgName, " +
" C.subOrgCode targetSubOrgCode, C.subOrgName targetSubOrgName, " +
" C.deptCode targetDeptCode, C.deptName targetDeptName, " +
" C.titleCode targetTitleCode, C.titleName targetTitleName, " +
" C.mail targetMail, C.nickName targetNickName, " +
" C.mobile targetMobile, C.picture targetPicture " +
" from EM_TRELATION A, EM_TEMPLOYEE_DATA B, " +
" EM_TEMPLOYEE_DATA C " +
" where A.sourceEpId = B.epId " +
" and A.targetEpId = C.epId " +
" and A.targetEpId = ? " +
" order by A.point desc " +
" limit 0, 10;";
@Override
public Object doInJpa(EntityManager em) {
EmpRelation result = new EmpRelation();
try {
Query query = em.createNativeQuery(sql, "GetEmpRelation");
query.setParameter(1, targetId);
List<Object> results = query.getResultList();
Object[] resultSet = null;
EmpInfo source = null;
EmpInfo target = null;
long point = 0L;
for (int i=0; i<results.size(); i++) {
resultSet = (Object[]) results.get(i);
if (resultSet.length > 0) {
source = (EmpInfo) resultSet[0];
result.addSource(source);
}
if (resultSet.length > 1) {
target = (EmpInfo) resultSet[1];
result.setTarget(target);
}
if (resultSet.length > 2) {
point = ((BigInteger) resultSet[2]).longValue();
result.setPoint(point);
}
result.setRowCount(String.valueOf(i));
result.setSuccess("true");
}
} catch (Exception e) {
result.setSuccess("false");
log.debug(e.getMessage());
}
return result;
}
});
}
SQL문을 사용할 때 주의할 점은 SqlResultSetMapping은 순차적인 파라미터 세팅을 써야한다.
파라미터 이름으로 세팅할려고 하면 에러가 난다.
이정도면 모든 에러가 나올 수 있는 가능성에 대해서는 다 설명한 듯
