JPA사용시 EJB QL 이나 Hibernate QL을 사용하지 않고 순수 Native SQL 을 사용할 때 객체로 매핑하고자 한다면
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은 순차적인 파라미터 세팅을 써야한다.
파라미터 이름으로 세팅할려고 하면 에러가 난다.
이정도면 모든 에러가 나올 수 있는 가능성에 대해서는 다 설명한 듯