标签（空格分隔）： springJPA
在UserRepositoryImpl中使用CriteriaBuilder实现根据id查询,下面是代码:
public void findById(Integer id){ //select u from User u where u.id = 1 CriteriaBuilder cb = entityManager.getCriteriaBuilder(); CriteriaQuery<User> cq = cb.createQuery(User.class); Root<User> root = cq.from(User.class); //from User cq.select(root); //select * from User javax.persistence.criteria.Predicate pre = cb.equal(root.get("id").as(Integer.class),id);//id=1 cq.where(pre);//where id=1 Query query = entityManager.createQuery(cq);//select u from User u where u.id = 1 System.out.println(query.getResultList()); }缺点:
个人认为,CriteriaQuery不适合用来复杂sql的查询实现,不过可以使用其对一些通用的sql查询封装到BaseDao中.具体封装接着往下看.
该接口有如下方法,里面传入条件都是Specification,该接口会返回一个Predicate条件集合,因此就可以在这里封装.
public interface JpaSpecificationExecutor<T> { T findOne(Specification<T> spec); List<T> findAll(Specification<T> spec); Page<T> findAll(Specification<T> spec, Pageable pageable); List<T> findAll(Specification<T> spec, Sort sort); long count(Specification<T> spec); }写法学习自shop++项目.
Operator枚举类里面的operator属性为了构建原生sql使用
/** * @author Niu Li * @date 2016/11/17 */ import org.apache.commons.lang3.builder.EqualsBuilder; import org.apache.commons.lang3.builder.HashCodeBuilder; import java.io.Serializable; /** * 筛选 * copy from shopXX */ public class Filter implements Serializable { private static final long serialVersionUID = -8712382358441065075L; /** * 运算符 */ public enum Operator { /** 等于 */ eq(" = "), /** 不等于 */ ne(" != "), /** 大于 */ gt(" > "), /** 小于 */ lt(" < "), /** 大于等于 */ ge(" >= "), /** 小于等于 */ le(" <= "), /** 类似 */ like(" like "), /** 包含 */ in(" in "), /** 为Null */ isNull(" is NULL "), /** 不为Null */ isNotNull(" is not NULL "); Operator(String operator) { this.operator = operator; } private String operator; public String getOperator() { return operator; } public void setOperator(String operator) { this.operator = operator; } } /** 默认是否忽略大小写 */ private static final boolean DEFAULT_IGNORE_CASE = false; /** 属性 */ private String property; /** 运算符 */ private Filter.Operator operator; /** 值 */ private Object value; /** 是否忽略大小写 */ private Boolean ignoreCase = DEFAULT_IGNORE_CASE; /** * 构造方法 */ public Filter() { } /** * 构造方法 * * @param property * 属性 * @param operator * 运算符 * @param value * 值 */ public Filter(String property, Filter.Operator operator, Object value) { this.property = property; this.operator = operator; this.value = value; } /** * 构造方法 * * @param property * 属性 * @param operator * 运算符 * @param value * 值 * @param ignoreCase * 忽略大小写 */ public Filter(String property, Filter.Operator operator, Object value, boolean ignoreCase) { this.property = property; this.operator = operator; this.value = value; this.ignoreCase = ignoreCase; } /** * 返回等于筛选 * * @param property * 属性 * @param value * 值 * @return 等于筛选 */ public static Filter eq(String property, Object value) { return new Filter(property, Filter.Operator.eq, value); } /** * 返回等于筛选 * * @param property * 属性 * @param value * 值 * @param ignoreCase * 忽略大小写 * @return 等于筛选 */ public static Filter eq(String property, Object value, boolean ignoreCase) { return new Filter(property, Filter.Operator.eq, value, ignoreCase); } /** * 返回不等于筛选 * * @param property * 属性 * @param value * 值 * @return 不等于筛选 */ public static Filter ne(String property, Object value) { return new Filter(property, Filter.Operator.ne, value); } /** * 返回不等于筛选 * * @param property * 属性 * @param value * 值 * @param ignoreCase * 忽略大小写 * @return 不等于筛选 */ public static Filter ne(String property, Object value, boolean ignoreCase) { return new Filter(property, Filter.Operator.ne, value, ignoreCase); } /** * 返回大于筛选 * * @param property * 属性 * @param value * 值 * @return 大于筛选 */ public static Filter gt(String property, Object value) { return new Filter(property, Filter.Operator.gt, value); } /** * 返回小于筛选 * * @param property * 属性 * @param value * 值 * @return 小于筛选 */ public static Filter lt(String property, Object value) { return new Filter(property, Filter.Operator.lt, value); } /** * 返回大于等于筛选 * * @param property * 属性 * @param value * 值 * @return 大于等于筛选 */ public static Filter ge(String property, Object value) { return new Filter(property, Filter.Operator.ge, value); } /** * 返回小于等于筛选 * * @param property * 属性 * @param value * 值 * @return 小于等于筛选 */ public static Filter le(String property, Object value) { return new Filter(property, Filter.Operator.le, value); } /** * 返回相似筛选 * * @param property * 属性 * @param value * 值 * @return 相似筛选 */ public static Filter like(String property, Object value) { return new Filter(property, Filter.Operator.like, value); } /** * 返回包含筛选 * * @param property * 属性 * @param value * 值 * @return 包含筛选 */ public static Filter in(String property, Object value) { return new Filter(property, Filter.Operator.in, value); } /** * 返回为Null筛选 * * @param property * 属性 * @return 为Null筛选 */ public static Filter isNull(String property) { return new Filter(property, Filter.Operator.isNull, null); } /** * 返回不为Null筛选 * * @param property * 属性 * @return 不为Null筛选 */ public static Filter isNotNull(String property) { return new Filter(property, Filter.Operator.isNotNull, null); } /** * 返回忽略大小写筛选 * * @return 忽略大小写筛选 */ public Filter ignoreCase() { this.ignoreCase = true; return this; } /** * 获取属性 * * @return 属性 */ public String getProperty() { return property; } /** * 设置属性 * * @param property * 属性 */ public void setProperty(String property) { this.property = property; } /** * 获取运算符 * * @return 运算符 */ public Filter.Operator getOperator() { return operator; } /** * 设置运算符 * * @param operator * 运算符 */ public void setOperator(Filter.Operator operator) { this.operator = operator; } /** * 获取值 * * @return 值 */ public Object getValue() { return value; } /** * 设置值 * * @param value * 值 */ public void setValue(Object value) { this.value = value; } /** * 获取是否忽略大小写 * * @return 是否忽略大小写 */ public Boolean getIgnoreCase() { return ignoreCase; } /** * 设置是否忽略大小写 * * @param ignoreCase * 是否忽略大小写 */ public void setIgnoreCase(Boolean ignoreCase) { this.ignoreCase = ignoreCase; } /** * 重写equals方法 * * @param obj * 对象 * @return 是否相等 */ @Override public boolean equals(Object obj) { if (obj == null) { return false; } if (getClass() != obj.getClass()) { return false; } if (this == obj) { return true; } Filter other = (Filter) obj; return new EqualsBuilder().append(getProperty(), other.getProperty()).append(getOperator(), other.getOperator()).append(getValue(), other.getValue()).isEquals(); } /** * 重写hashCode方法 * * @return HashCode */ @Override public int hashCode() { return new HashCodeBuilder(17, 37).append(getProperty()).append(getOperator()).append(getValue()).toHashCode(); } }import org.apache.commons.lang3.builder.EqualsBuilder; import org.apache.commons.lang3.builder.HashCodeBuilder; import java.io.Serializable; /** * 排序 * * @author copy from shopxx */ public class Order implements Serializable { private static final long serialVersionUID = -3078342809727773232L; /** * 方向 */ public enum Direction { /** 递增 */ asc, /** 递减 */ desc } /** 默认方向 */ private static final Order.Direction DEFAULT_DIRECTION = Order.Direction.desc; /** 属性 */ private String property; /** 方向 */ private Order.Direction direction = DEFAULT_DIRECTION; @Override public String toString() { return property+" " + direction.name(); } /** * 构造方法 */ public Order() { } /** * 构造方法 * * @param property * 属性 * @param direction * 方向 */ public Order(String property, Order.Direction direction) { this.property = property; this.direction = direction; } /** * 返回递增排序 * * @param property * 属性 * @return 递增排序 */ public static Order asc(String property) { return new Order(property, Order.Direction.asc); } /** * 返回递减排序 * * @param property * 属性 * @return 递减排序 */ public static Order desc(String property) { return new Order(property, Order.Direction.desc); } /** * 获取属性 * * @return 属性 */ public String getProperty() { return property; } /** * 设置属性 * * @param property * 属性 */ public void setProperty(String property) { this.property = property; } /** * 获取方向 * * @return 方向 */ public Order.Direction getDirection() { return direction; } /** * 设置方向 * * @param direction * 方向 */ public void setDirection(Order.Direction direction) { this.direction = direction; } /** * 重写equals方法 * * @param obj * 对象 * @return 是否相等 */ @Override public boolean equals(Object obj) { if (obj == null) { return false; } if (getClass() != obj.getClass()) { return false; } if (this == obj) { return true; } Order other = (Order) obj; return new EqualsBuilder().append(getProperty(), other.getProperty()).append(getDirection(), other.getDirection()).isEquals(); } /** * 重写hashCode方法 * * @return HashCode */ @Override public int hashCode() { return new HashCodeBuilder(17, 37).append(getProperty()).append(getDirection()).toHashCode(); } }/** * 封装查询条件的实体 * @author Niu Li * @date 2016/11/17 */ public class QueryParams<T> implements Specification<T> { /** 属性分隔符 */ private static final String PROPERTY_SEPARATOR = "."; /** * and条件 */ private List<Filter> andFilters = new ArrayList<>(); /** * or条件 */ private List<Filter> orFilters = new ArrayList<>(); /** * 排序属性 */ private List<Order> orders = new ArrayList<>(); /** * 获取Path * * @param path * Path * @param propertyPath * 属性路径 * @return Path */ @SuppressWarnings("unchecked") private <X> Path<X> getPath(Path<?> path, String propertyPath) { if (path == null || StringUtils.isEmpty(propertyPath)) { return (Path<X>) path; } String property = StringUtils.substringBefore(propertyPath, PROPERTY_SEPARATOR); return getPath(path.get(property), StringUtils.substringAfter(propertyPath, PROPERTY_SEPARATOR)); } } /** * 转换为Predicate * * @param root * Root * @return Predicate */ @SuppressWarnings("unchecked") private Predicate toAndPredicate(Root<T> root,CriteriaBuilder criteriaBuilder) { Predicate restrictions = criteriaBuilder.conjunction(); if (root == null || CollectionUtils.isEmpty(andFilters)) { return restrictions; } for (Filter filter : andFilters) { if (filter == null) { continue; } String property = filter.getProperty(); Filter.Operator operator = filter.getOperator(); Object value = filter.getValue(); Boolean ignoreCase = filter.getIgnoreCase(); Path<?> path = getPath(root, property); if (path == null) { continue; } //根据运算符生成相应条件 switch (operator) { case eq: if (value != null) { if (BooleanUtils.isTrue(ignoreCase) && String.class.isAssignableFrom(path.getJavaType()) && value instanceof String) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(criteriaBuilder.lower((Path<String>) path), ((String) value).toLowerCase())); } else { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(path, value)); } } else { restrictions = criteriaBuilder.and(restrictions, path.isNull()); } break; case ne: if (value != null) { if (BooleanUtils.isTrue(ignoreCase) && String.class.isAssignableFrom(path.getJavaType()) && value instanceof String) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.notEqual(criteriaBuilder.lower((Path<String>) path), ((String) value).toLowerCase())); } else { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.notEqual(path, value)); } } else { restrictions = criteriaBuilder.and(restrictions, path.isNotNull()); } break; case gt: if (Number.class.isAssignableFrom(path.getJavaType()) && value instanceof Number) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.gt((Path<Number>) path, (Number) value)); } break; case lt: if (Number.class.isAssignableFrom(path.getJavaType()) && value instanceof Number) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.lt((Path<Number>) path, (Number) value)); } break; case ge: if (Number.class.isAssignableFrom(path.getJavaType()) && value instanceof Number) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.ge((Path<Number>) path, (Number) value)); } break; case le: if (Number.class.isAssignableFrom(path.getJavaType()) && value instanceof Number) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.le((Path<Number>) path, (Number) value)); } break; case like: if (String.class.isAssignableFrom(path.getJavaType()) && value instanceof String) { if (BooleanUtils.isTrue(ignoreCase)) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.like(criteriaBuilder.lower((Path<String>) path), ((String) value).toLowerCase())); } else { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.like((Path<String>) path, (String) value)); } } break; case in: restrictions = criteriaBuilder.and(restrictions, path.in(value)); break; case isNull: restrictions = criteriaBuilder.and(restrictions, path.isNull()); break; case isNotNull: restrictions = criteriaBuilder.and(restrictions, path.isNotNull()); break; } } return restrictions; }把and中的and改为or即可
/** * 转换为Predicate * * @param root * Root * @return Predicate */ @SuppressWarnings("unchecked") private Predicate toOrPredicate(Root<T> root,CriteriaBuilder criteriaBuilder) { Predicate restrictions = criteriaBuilder.disjunction(); if (root == null || CollectionUtils.isEmpty(andFilters)) { return restrictions; } for (Filter filter : orFilters) { if (filter == null) { continue; } String property = filter.getProperty(); Filter.Operator operator = filter.getOperator(); Object value = filter.getValue(); Boolean ignoreCase = filter.getIgnoreCase(); Path<?> path = getPath(root, property); if (path == null) { continue; } switch (operator) { case eq: if (value != null) { if (BooleanUtils.isTrue(ignoreCase) && String.class.isAssignableFrom(path.getJavaType()) && value instanceof String) { restrictions = criteriaBuilder.or(restrictions, criteriaBuilder.equal(criteriaBuilder.lower((Path<String>) path), ((String) value).toLowerCase())); } else { restrictions = criteriaBuilder.or(restrictions, criteriaBuilder.equal(path, value)); } } else { restrictions = criteriaBuilder.or(restrictions, path.isNull()); } break; case ne: if (value != null) { if (BooleanUtils.isTrue(ignoreCase) && String.class.isAssignableFrom(path.getJavaType()) && value instanceof String) { restrictions = criteriaBuilder.or(restrictions, criteriaBuilder.notEqual(criteriaBuilder.lower((Path<String>) path), ((String) value).toLowerCase())); } else { restrictions = criteriaBuilder.or(restrictions, criteriaBuilder.notEqual(path, value)); } } else { restrictions = criteriaBuilder.or(restrictions, path.isNotNull()); } break; case gt: if (Number.class.isAssignableFrom(path.getJavaType()) && value instanceof Number) { restrictions = criteriaBuilder.or(restrictions, criteriaBuilder.gt((Path<Number>) path, (Number) value)); } break; case lt: if (Number.class.isAssignableFrom(path.getJavaType()) && value instanceof Number) { restrictions = criteriaBuilder.or(restrictions, criteriaBuilder.lt((Path<Number>) path, (Number) value)); } break; case ge: if (Number.class.isAssignableFrom(path.getJavaType()) && value instanceof Number) { restrictions = criteriaBuilder.or(restrictions, criteriaBuilder.ge((Path<Number>) path, (Number) value)); } break; case le: if (Number.class.isAssignableFrom(path.getJavaType()) && value instanceof Number) { restrictions = criteriaBuilder.or(restrictions, criteriaBuilder.le((Path<Number>) path, (Number) value)); } break; case like: if (String.class.isAssignableFrom(path.getJavaType()) && value instanceof String) { if (BooleanUtils.isTrue(ignoreCase)) { restrictions = criteriaBuilder.or(restrictions, criteriaBuilder.like(criteriaBuilder.lower((Path<String>) path), ((String) value).toLowerCase())); } else { restrictions = criteriaBuilder.or(restrictions, criteriaBuilder.like((Path<String>) path, (String) value)); } } break; case in: restrictions = criteriaBuilder.or(restrictions, path.in(value)); break; case isNull: restrictions = criteriaBuilder.or(restrictions, path.isNull()); break; case isNotNull: restrictions = criteriaBuilder.or(restrictions, path.isNotNull()); break; } } return restrictions; }/** * 转换为Order * * @param root * Root * @return Order */ private List<javax.persistence.criteria.Order> toOrders(Root<T> root,CriteriaBuilder criteriaBuilder) { List<javax.persistence.criteria.Order> orderList = new ArrayList<javax.persistence.criteria.Order>(); if (root == null || CollectionUtils.isEmpty(orders)) { return orderList; } for (Order order : orders) { if (order == null) { continue; } String property = order.getProperty(); Order.Direction direction = order.getDirection(); Path<?> path = getPath(root, property); if (path == null || direction == null) { continue; } switch (direction) { case asc: orderList.add(criteriaBuilder.asc(path)); break; case desc: orderList.add(criteriaBuilder.desc(path)); break; } } return orderList; }最后在toPredicate方法中构造最终条件
/** * 生成条件的 * @param root 该对象的封装 * @param query 查询构建器 * @param cb 构建器 * @return 条件集合 */ @Override public Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder cb) { //如果无or条件,toOrPredicate会自带一个 or 1=1,前面用and的话则变为and 1=1造成错误,所以这里判断下 //这个貌似和版本有关,公司线上版本出现这个问题,本地测试没问题 Predicate restrictions = cb.and(toAndPredicate(root,cb)); if(!orders.isEmpty()){ restrictions = cb.and(restrictions,toOrPredicate(root,cb));} query.orderBy(toOrders(root,cb)); return restrictions; }加上方便的链式调用方法
/** * 添加一个and条件 * @param filter 该条件 * @return 链式调用 */ public QueryParams and(Filter filter){ this.andFilters.add(filter); return this; } /** * 添加多个and条件 * @param filter 该条件 * @return 链式调用 */ public QueryParams and(Filter ...filter){ this.andFilters.addAll(Arrays.asList(filter)); return this; } /** * 添加一个or条件 * @param filter 该条件 * @return 链式调用 */ public QueryParams or(Filter filter){ this.orFilters.add(filter); return this; } /** * 添加多个or条件 * @param filter 该条件 * @return 链式调用 */ public QueryParams or(Filter ...filter){ this.orFilters.addAll(Arrays.asList(filter)); return this; } /** * 升序字段 * @param property 该字段对应变量名 * @return 链式调用 */ public QueryParams orderASC(String property){ this.orders.add(Order.asc(property)); return this; } /** * 降序字段 * @param property 该字段对应变量名 * @return 链式调用 */ public QueryParams orderDESC(String property){ this.orders.add(Order.desc(property)); return this; } /** * 清除所有条件 * @return 该实例 */ public QueryParams clearAll(){ if (!this.andFilters.isEmpty()) this.andFilters.clear(); if (!this.orFilters.isEmpty()) this.orFilters.clear(); if (!this.orders.isEmpty()) this.orders.clear(); return this; } /** * 清除and条件 * @return 该实例 */ public QueryParams clearAnd(){ if (!this.andFilters.isEmpty()) this.andFilters.clear(); return this; } /** * 清除or条件 * @return 该实例 */ public QueryParams clearOr(){ if (!this.orFilters.isEmpty()) this.andFilters.clear(); return this; } /** * 清除order条件 * @return 该实例 */ public QueryParams clearOrder(){ if (!this.orders.isEmpty()) this.orders.clear(); return this; } //省略get和set方法首先让PcardOrderRepository接口继承加上JpaSpecificationExecutor
public interface PcardOrderRepository extends JpaRepository<PcardOrder,String> ,PcardOrderRepositoryCustom,JpaSpecificationExecutor<PcardOrder> { }编写测试代码,这个使用的是CriteriaBuilder构建查询的,所以查询字段都是JPQL字段,并不是原生sql
QueryParams<PcardOrder> queryParams = new QueryParams<>(); //使用Specification条件查询,使用JPQL字段查询 queryParams .and(Filter.eq("acctId","0014779934917371041"),Filter.ne("orderAmt",0L), Filter.eq("orderRespCd","00")) .or(Filter.eq("orderTypeId","A003"),Filter.eq("orderTypeId","A007"), Filter.eq("orderTypeId","A021"),Filter.eq("orderTypeId","A018")) .orderDESC("createTime"); Page<PcardOrder> JPQLlist = pcardOrderRepository.findAll(queryParams,new PageRequest(0,2)); //构造出来的条件 where 1=1 and pcardorder0_.acct_id=? and pcardorder0_.order_amt<>0 and pcardorder0_.order_resp_cd=? and ( 0=1 or pcardorder0_.order_type_id=? or pcardorder0_.order_type_id=? or pcardorder0_.order_type_id=? or pcardorder0_.order_type_id=? ) order by pcardorder0_.create_time desc limit ?还是利用上面的Filter,具体还是遍历+拼接,示例中我卸载了公共方法中,需要使用的Impl直接extends即可.
解析条件实际上就是拼接sql,代码很简单.
/** * 公共方法的repository * @author Niu Li * @date 2016/11/17 */ @NoRepositoryBean public class BaseRepository { private static Logger logger = LoggerFactory.getLogger(BaseRepository.class); /** * 分析查询参数,并且合并到sql语句中 * @param sql JPQL查询语句 * @param params 查询参数 * @return 参数对应的value */ @SuppressWarnings("Unchecked") protected List<Object> analysisQueryParams(StringBuilder sql, QueryParams<?> params){ List<String> strList = new ArrayList<>(); List<Object> valueList = new ArrayList<>(); int i = 1; //分析or条件 for (Filter filter : params.getOrFilters()) { if (filter.getValue() != null){ strList.add(filter.getProperty()+" " + filter.getOperator().getOperator()+" ?" + (i++)); valueList.add(filter.getValue()); }else { strList.add(filter.getProperty()+" " + filter.getOperator().getOperator()+" "); } } if (!strList.isEmpty()){ sql.append(" and ").append("( ").append(StringUtils.join(strList," or ")).append(" )"); } strList.clear(); //分析and条件 for (Filter filter : params.getAndFilters()) { if (filter.getValue() != null){ strList.add(filter.getProperty()+" " + filter.getOperator().getOperator()+" ?" + (i++)); valueList.add(filter.getValue()); }else { strList.add(filter.getProperty()+" " + filter.getOperator().getOperator()+" "); } } sql.append(" and ").append(StringUtils.join(strList," and ")); //分析排序字段 if (!params.getOrders().isEmpty()){ sql.append(" order by "); sql.append(StringUtils.join(params.getOrders(),",")); } logger.debug("解析后的sql:"+sql.toString()); logger.debug("对应的值为:"+valueList); return valueList; } }在自定义接口中加入方法
/** * @author Niu Li * @date 2016/11/17 */ public interface PcardOrderRepositoryCustom { List findByQueryParam(QueryParams queryParams, Pageable pageable); }然后实现类中需要写部分sql
/** * @author Niu Li * @date 2016/11/18 */ @NoRepositoryBean public class PcardOrderRepositoryImpl extends BaseRepository implements PcardOrderRepositoryCustom { @PersistenceContext private EntityManager entityManager; @Override public List findByQueryParam(QueryParams queryParams, Pageable pageable) { StringBuilder sql = new StringBuilder("select * from tbl_pcard_order where 1=1 "); List values = analysisQueryParams(sql,queryParams); Query query = entityManager.createNativeQuery(sql.toString()); for (int i = 0; i < values.size(); i++) { query.setParameter(i+1,values.get(i)); } query.setFirstResult(pageable.getOffset()); query.setMaxResults(pageable.getPageSize()); return query.getResultList(); } }测试代码:
//使用原生sql查询,注意这里使用原生sql字段,并非JPQL字段, //本质是根据BaseRepository.analysisQueryParams 来拼接条件,可以根据自己的需求更改 queryParams.clearAll() .and(Filter.eq("acct_id","0014779934917371041"),Filter.ne("order_amt",0L), Filter.eq("order_resp_cd","00")) .or(Filter.eq("order_type_id","A003"),Filter.eq("order_type_id","A007"), Filter.eq("order_type_id","A021"),Filter.eq("order_type_id","A018")) .orderDESC("create_time"); List nativeSqlList = pcardOrderRepository.findByQueryParam(queryParams,new PageRequest(0,2)); //构造出来的sql where 1=1 and ( order_type_id = ? or order_type_id = ? or order_type_id = ? or order_type_id = ? ) and acct_id = ? and order_amt != ? and order_resp_cd = ? order by create_time desc limit ?使用原生sql进行表关联查询,返回值一般都用List<Object[]>来接收,这种方法很不利于项目的维护,你并不一定记得Object[0]是什么字段,一旦sql发生变化,则整个Object[]数组的顺序就会发生变化.
public List<Object[]> findById(int id) { String sql = "select u.*,c.* from user u,commont c where u.id = c.id and id=?1"; Query query = entityManager.createNativeQuery(sql); query.setParameter(1,id); return query.getResultList(); }那么就要改进,使其返回Map
public List findById (int id) { String sql = "select u.*,c.* from user u,commont c where u.id = c.id and id=?1"; Query query = entityManager.createNativeQuery(sql); query.setParameter(1,id); //转换为Map集合 query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP); return query.getResultList(); } //实际上返回的是一个List<Map>集合这样的返回值是一个List<Map>类型,取出的时候直接根据键值取即可.