作用:
1.提高性能
2.指定要返回哪几个字段,为指定的不返回(主要用于select *查询全部)
3.也可指定返回字段的具体类型
详细讲解如下:
最基本的SQL查询就是获得一个标量的列表:
session.createSQLQuery("select * from person_inf").list();
session.createSQLQuery("select id,name,age from person_inf").list();
它们都将返回一个Object数组组成的List,数组每个元素都是person_inf表的一个字段值。Hibernate会使用ResultSetMetadata来判定返回的标量值的实际顺序和类型。
但是在JDBC中过多的使用ResultSetMetadata会降低程序的性能。所以为了过多的避免使用ResultSetMetadata或者为了指定更加明确的返回值类型,我们可以使用addScalar()方法:
session.createSQLQuery("select * from person_inf")
.addScalar("name",StandardBasicTypes.STRING)
.addScalar("age",StandardBasicTypes.INT)
.list();
/**
* 查询设定时间范围内的预约客户
*
* @param orgCode orgCode
* @param beginDate 起始时间
* @param endDate 结束时间
* @return 客户姓名、年龄、预约类别
*/
public List<?> findByReserveTime(String orgCode, LocalDate beginDate, LocalDate endDate) {
LocalDateTime beginTime = beginDate.atStartOfDay();
LocalDateTime endTime = endDate.atTime(23, 59, 59);
String queryString = "SELECT\n" +
"customer.csr_name AS name,\n" +
"customer.csr_age AS age,\n" +
"model.reserve_time AS reserveTime " +
"FROM sys_reserve model\n" +
"LEFT JOIN sys_customer customer ON model.csr_pid = customer.pid\n" +
"WHERE customer.pid IS NOT NULL\n" +
"AND model.reserve_type = 2\n" +
"AND model.org_pid IN(SELECT org.pid FROM sys_org org WHERE org.org_code LIKE :orgCode)\n" +
"AND model.reserve_time BETWEEN :beginTime AND :endTime\n" +
"ORDER BY model.create_time DESC";
return entityManager.unwrap(Session.class)
.createNativeQuery(queryString)
.addScalar("name", StringType.INSTANCE)
.addScalar("age", StringType.INSTANCE)
.addScalar("reserveTime", LocalDateTimeType.INSTANCE)
.setParameter("orgCode", orgCode + "%")
.setParameter("beginTime", beginTime)
.setParameter("endTime", endTime)
.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
.getResultList();
}