JPA默认得查询会把表里所有数据字段都返回
这样会带来一些问题
1.数据量大的字段会导致mysql I/O问题
2.一些敏感的信息我们不应该直接返回给用户，这样的话我们查出来数据后还得手动过滤一遍
但是JPA在这方面并没有支持得很好，而mybatis可以利用resultMap,resultType
那么JPA里面要怎么做呢
一种是利用jpa的@Query
@Query("SELECT new cn.hbnet.newsguess.web.dto.app.ConfigAttrDto(cf.appId,cf.attrType,cf.attrName,cf.attrValue,cf.attrDesc) FROM ConfigAttr cf WHERE cf.attrName=?1 ")
ConfigAttrDto findFirstByAttrNameDto(String attrName);
当然我们首先要创建要返回数据的DTO对象
然后使用HQL语句封装。
不我们可以看看JPA生成的sql
Hibernate: select configattr0_.app_id as col_0_0_, configattr0_.attr_type as col_1_0_, configattr0_.attr_name as col_2_0_, configattr0_.attr_value as col_3_0_, configattr0_.attr_desc as col_4_0_ from config_attr configattr0_ where configattr0_.attr_name=?
第二种是利用entityManager
@Autowired
private EntityManager entityManager;
public ConfigAttrDto selectByNameDto(String attrDesc){
String sql = "SELECT app_id as appId,attr_type as attrType,attr_name as attrName," +
"attr_value as attrValue,attr_desc as attrDesc FROM config_attr WHERE attr_name=:attrName LIMIT 1";
Query query = entityManager.createNativeQuery(sql);
query.setParameter("attrName",attrDesc);
query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(ConfigAttrDto.class));
Object singleResult = query.getSingleResult();
return (ConfigAttrDto)singleResult;
}
生成的sql
Hibernate: SELECT app_id as appId,attr_type as attrType,attr_name as attrName,attr_value as attrValue,attr_desc as attrDesc FROM config_attr WHERE attr_name=? LIMIT 1
