JPA 查询通过别名 转实体

首先 说缺点：映射的对象 如果 引用另外一个实体，是没办法映射。
使用的规则：别名和实体的属性要一一对应。

public class test｛     private String name;      private String password;      //必须有get set方法，我这里就不写了｝

sql 这样写: select aaa as name,bbb as password from tableName;

使用的：

Page<ApppintmentDepositFlowingReportVO> tList = 

(Page<ApppintmentDepositFlowingReportVO>) this.query(sql, ApppintmentDepositFlowingReportVO.class, pageable, "A");

最后一个参数 是 为 求 count 用的。后面我会 标出来。

public Page<?> query(String sql, Class<?> classs, Pageable pageable, String header) {EntityManager em = entityManagerFactory.createEntityManager();Session session = (Session) em.getDelegate();Sort sort = pageable.getSort();StringBuilder orderString = new StringBuilder();if (sort != null) {Iterator<Order> it = sort.iterator();if (it != null && it.hasNext()) {Order order = it.next();String dir = order.getDirection().toString();String name = order.getProperty();orderString.append(" order by ").append(name).append(" ").append(dir);}}SQLQuery query = session.createSQLQuery(sql + orderString.toString());String countSql = this.getSqlForCount(sql, header);SQLQuery query2 = session.createSQLQuery(countSql);Object obj = query2.uniqueResult();Long totleDatas = SqlResultUtil.getSqlResultLong(obj);query.setResultTransformer(new XaAliasToBeanResultTransformer(classs));query.setFirstResult((pageable.getPageNumber()) * pageable.getPageSize());query.setMaxResults(pageable.getPageSize());List<?> list = query.list();PageImpl<?> page = new PageImpl<>(list, pageable, totleDatas.longValue());em.close();return page;}

private String getSqlForCount(String sql, String header) {int d = sql.indexOf("from");return "select count(" + header + ".id) " + sql.substring(d);}

其实关键是这个类：

package com.baihui.base.util;import java.util.Arrays;import org.apache.commons.lang.StringUtils;import org.hibernate.HibernateException;import org.hibernate.property.ChainedPropertyAccessor;import org.hibernate.property.PropertyAccessor;import org.hibernate.property.PropertyAccessorFactory;import org.hibernate.property.Setter;import org.hibernate.transform.AliasToBeanResultTransformer;public class XaAliasToBeanResultTransformer extends AliasToBeanResultTransformer {public Xa3itAliasToBeanResultTransformer(Class resultClasss) {super(resultClasss);this.resultClass = resultClasss;}private final Class resultClass;private boolean isInitialized;private String[] aliases;private Setter[] setters;@Overridepublic boolean isTransformedValueATupleElement(String[] aliases, int tupleLength) {return false;}@Overridepublic Object transformTuple(Object[] tuple, String[] aliases) {Object result;try {if ( ! isInitialized ) {initialize( aliases );}else {//check( aliases );}result = resultClass.newInstance();for ( int i = 0; i < aliases.length; i++ ) {Setter setter = setters[i];if ( setter != null ) {Class[] clas = setter.getMethod().getParameterTypes();Object obj = this.getNewValObject(clas[0], tuple[i]);setter.set( result, obj, null );}}}catch ( InstantiationException e ) {throw new HibernateException( "Could not instantiate resultclass: " + resultClass.getName() );}catch ( IllegalAccessException e ) {throw new HibernateException( "Could not instantiate resultclass: " + resultClass.getName() );}return result;}private Object getNewValObject(Class clas,Object val){if(val == null){return null;}String name = clas.getName();if(StringUtils.equals(name, "java.lang.Long")){return SqlResultUtil.getSqlResultLong(val);}else if(StringUtils.equals(name,"java.lang.Double")){return SqlResultUtil.getSqlResultDouble(val);}else if(StringUtils.equals(name,"java.lang.Integer")){return SqlResultUtil.getSqlResultInteger(val);}else if(StringUtils.equals(name,"java.util.Date")){return SqlResultUtil.getSqlResultDate(val);}else if(StringUtils.equals(name,"boolean")){String v = SqlResultUtil.getSqlResultString(val);return Boolean.parseBoolean(v);}else if(StringUtils.equals(name, "java.lang.Boolean")){String v = SqlResultUtil.getSqlResultString(val);if(v.toLowerCase().equals("true") || v.toLowerCase().equals("false")){return Boolean.parseBoolean(v);}else{if(v.equals("1")){return Boolean.TRUE;}else{return Boolean.FALSE;}}}else if(StringUtils.equals(name,"java.lang.String")){return SqlResultUtil.getSqlResultString(val);}return null;}private void initialize(String[] aliases) {PropertyAccessor propertyAccessor = new ChainedPropertyAccessor(new PropertyAccessor[] {PropertyAccessorFactory.getPropertyAccessor( resultClass, null ),PropertyAccessorFactory.getPropertyAccessor( "field" )});this.aliases = new String[ aliases.length ];setters = new Setter[ aliases.length ];for ( int i = 0; i < aliases.length; i++ ) {String alias = aliases[ i ];if ( alias != null ) {//this.aliases[ i ] = alias;this.aliases[i] = this.getSqlAliasToJavaAlias(alias);setters[ i ] = propertyAccessor.getSetter( resultClass, this.aliases[i] );}}isInitialized = true;}private String getSqlAliasToJavaAlias(String s){if(s.indexOf("_") > 0){String[] sss = s.split("_");StringBuilder sb = new StringBuilder(sss[0]);for(int i = 1 ; i < sss.length;i++){String ss = sss[i];String head = ss.substring(0,1);sb.append(head.toUpperCase()).append(ss.substring(1));}return sb.toString();}else{return s;}}private void check(String[] aliases) {if ( ! Arrays.equals( aliases, this.aliases ) ) {throw new IllegalStateException("aliases are different from what is cached; aliases=" + Arrays.asList( aliases ) +" cached=" + Arrays.asList( this.aliases ) );}}@Overridepublic boolean equals(Object o) {if ( this == o ) {return true;}if ( o == null || getClass() != o.getClass() ) {return false;}Xa3itAliasToBeanResultTransformer that = ( Xa3itAliasToBeanResultTransformer ) o;if ( ! resultClass.equals( that.resultClass ) ) {return false;}if ( ! Arrays.equals( aliases, that.aliases ) ) {return false;}return true;}@Overridepublic int hashCode() {int result = resultClass.hashCode();result = 31 * result + ( aliases != null ? Arrays.hashCode( aliases ) : 0 );return result;}}

就是  参数中有个 page 因为用的是 spring data：

@ResponseBody
@RequestMapping(value = "operationOrderr", method = RequestMethod.POST)
public XaResult<Page<OperationOrderCashierFlowingReportForAOuterVO>> operationOrderCashierFlowingReportForAOuter(
HttpServletRequest request,
@RequestParam(defaultValue = "0") Integer nextPage,
@RequestParam(defaultValue = "10") Integer pageSize,
@RequestParam(defaultValue = "[{property:'A.id',direction:'DESC'}]") String sortData,
@RequestParam(defaultValue = "{}") String jsonFilter)
throws BusinessException {
Pageable pageable = WebUitl.buildPageRequest(nextPage, pageSize,sortData);
Map<String, Object> filterParams = WebUitl.getParametersStartingWith(jsonFilter, "search_");
XaResult<Page<OperationOrderCashierFlowingReportForAOuterVO>> xa = new XaResult<>();
try
{
Page<OperationOrderCashierFlowingReportForAOuterVO> p = this.operationOrderCashierFlowingReportForAOuterService.getResult(pageable, filterParams);
xa.success(p);
}catch(Exception e)
{
xa.error("数据查询出错");
}
return xa;
}