package com.yb.fw.core.helper;
public enum Op {
LIKE,// like
NOTLIKE,// notlike
EQ,// =
NOTEQ,// !=
GT, // >
GTEQ,//>=
LT,//<
LTEQ,//<=
NULL,// is null
NOTNULL,// is not null
IN,// in
NOTIN,// not in
}
package com.yb.fw.core.dao;
import java.io.Serializable;
import java.util.List;
import java.util.Map;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.repository.NoRepositoryBean;
import org.springframework.lang.Nullable;
import com.yb.fw.core.helper.Condition;
@NoRepositoryBean //表示该接口不会创建这个接口的实例
public interface BaseRepository<T, ID extends Serializable> extends JpaRepository<T, ID>, JpaSpecificationExecutor<T> {
List<Object[]> listBySQL(String sql);
public List<Map<String, Object>> getData(String sql, Map<String, Object> params);
public void save(Object... entitys);
/**
* 更新实体
*
* @param entity 实体id
*/
public void update(Object... entitys);
/**
* 删除实体
*
* @param entityClass 实体类
* @param entityid 实体id
*/
public <E> void delete(Class<T> entityClass, Object entityid);
/**
* 删除实体
*
* @param entityClass 实体类
* @param entityids 实体id数组
*/
public <E> void delete(Class<T> entityClass, Object[] entityids);
/**
* 获取实体
*
* @param <T>
* @param entityClass 实体类
* @param entityId 实体id
* @return
*/
public <E> T find(Class<T> entityClass, Object entityId);
public T findOne(ID id);
/**
* 执行ql语句
* @param qlString 基于jpa标准的jpql语句
* @param values jpql中的?参数值,单个参数值或者多个参数值
* @return 返回执行后受影响的数据个数
*/
int executeUpdate(String qlString, Object... values);
/**
* 执行ql语句
* @param qlString 基于jpa标准的jpql语句
* @param params key表示jpql中参数变量名，value表示该参数变量值
* @return 返回执行后受影响的数据个数
*/
int executeUpdate(String qlString, Map<String, Object> params);
/**
* 执行ql语句，可以是更新或者删除操作
* @param qlString 基于jpa标准的jpql语句
* @param values jpql中的?参数值
* @return 返回执行后受影响的数据个数
* @throws Exception
*/
int executeUpdate(String qlString, List<Object> values);
/**
* 执行原生SQL语句，可以是更新或者删除操作
* @param sql 标准的sql语句
* @return 返回执行后受影响的数据个数
* @throws Exception
*/
int executeBySQL(String sql);
int executeBySQL(String sql, Object... values);
/**
* jpql查询语句
* @param qlString 基于jpa标准的jpql语句
* @param values jpql中的?参数值,单个参数值或者多个参数值
* @return 返回查询的数据集合
*/
List<T> findAll(String qlString, Object... values);
List<T> findAll(String qlString, Map<String, Object> params);
List<T> findAll(@Nullable List<Condition> conditions);
boolean support(String modelType);
}
package com.yb.fw.core.dao;
import java.io.Serializable;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import javax.persistence.EntityManager;
import javax.persistence.Query;
import org.hibernate.SQLQuery;
import org.hibernate.query.internal.NativeQueryImpl;
import org.hibernate.transform.Transformers;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.support.JpaEntityInformation;
import org.springframework.data.jpa.repository.support.SimpleJpaRepository;
import org.springframework.data.repository.NoRepositoryBean;
import com.yb.fw.core.helper.Condition;
import com.yb.fw.core.helper.QueryUtil;
//Spring Data JPA都是调用SimpleJpaRepository来创建实例
public class BaseRepositoryImpl<T, ID extends Serializable> extends SimpleJpaRepository<T, ID>
implements BaseRepository<T, ID> {
// 用于操作数据库
private final EntityManager em;
private final JpaEntityInformation<T, ID> entityInformation;
BaseRepositoryImpl(JpaEntityInformation<T, ID> entityInformation,
EntityManager entityManager) {
super(entityInformation, entityManager);
this.em = entityManager;
this.entityInformation=entityInformation;
}
// 通过EntityManager来完成查询
@Override
public List<Object[]> listBySQL(String sql) {
return em.createNativeQuery(sql).getResultList();
}
public List<Map<String, Object>> getData(String sql, Map<String, Object> params) {
Query query = em.createNativeQuery(sql);
query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
if(params!=null)
for (String name : params.keySet()) {
query.setParameter(name, params.get(name));
}
return query.getResultList();
}
@Override
public void save(Object... entities) {
if (null != entities) {
for (Object entity : entities) {
em.persist(entity);
}
}
}
@Override
public void update(Object... entities) {
if (null != entities) {
for (Object entity : entities) {
em.merge(entity);
}
}
}
public <E> void delete(Class<T> entityClass, Object entityid) {
delete(entityClass, new Object[] { entityid });
}
@Override
public <E> void delete(Class<T> entityClass, Object[] entityids) {
for (Object id : entityids) {
em.remove(em.getReference(entityClass, id));
}
}
public <E> T find(Class<T> entityClass, Object entityId) {
return em.find(entityClass, entityId);
}
public T findOne(ID id) {
Optional<T> entityById=findById(id);
if(entityById.isPresent())
return entityById.get();
return null;
}
@Override
public boolean support(String modelType) {
System.out.println(modelType+"###"+entityInformation.getEntityName());
return entityInformation.getEntityName().equals(modelType);
}
@Override
public int executeUpdate(String qlString, Object... values) {
Query query = em.createQuery(qlString);
if (values != null) {
for (int i = 0; i < values.length; i++) {
query.setParameter(i + 1, values[i]);
}
}
return query.executeUpdate();
}
@Override
public int executeUpdate(String qlString, Map<String, Object> params) {
Query query = em.createQuery(qlString);
for (String name : params.keySet()) {
query.setParameter(name, params.get(name));
}
return query.executeUpdate();
}
@Override
public int executeUpdate(String qlString, List<Object> values) {
Query query = em.createQuery(qlString);
for (int i = 0; i < values.size(); i++) {
query.setParameter(i + 1, values.get(i));
}
return query.executeUpdate();
}
@Override
public int executeBySQL(String sql) {
return em.createNativeQuery(sql).executeUpdate();
}
@Override
public int executeBySQL(String sql,Object... values) {
Query query = em.createNativeQuery(sql);
if (values != null) {
for (int i = 0; i < values.length; i++) {
query.setParameter(i + 1, values[i]);
}
}
return query.executeUpdate();
}
@Override
public List<T> findAll(String qlString, Object... values) {
Query query = em.createQuery(qlString);
if (values != null) {
for (int i = 0; i < values.length; i++) {
query.setParameter(i + 1, values[i]);
}
}
return query.getResultList();
}
@Override
public List<T> findAll(String qlString, Map<String, Object> params) {
Query query = em.createQuery(qlString);
for (String name : params.keySet()) {
query.setParameter(name, params.get(name));
}
return query.getResultList();
}
@Override
public List<T> findAll(Sort sort) {
// TODO 这是系统自动生成描述，请在此补完后续代码
return super.findAll(sort);
}
@Override
public Page<T> findAll(Pageable pageable) {
// TODO 这是系统自动生成描述，请在此补完后续代码
return super.findAll(pageable);
}
@Override
public List<T> findAll(List<Condition> conditions) {
// TODO Auto-generated method stub
return findAll(QueryUtil.where(conditions));
}
}
package com.yb.fw.core.helper;
import org.apache.commons.lang3.StringUtils;
import org.springframework.data.jpa.domain.Specification;
import javax.persistence.criteria.*;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Map;
import java.util.Set;
/**
* SQL拼接工具类
*
*/
public class QueryUtil {
private Specification spec;
private QueryUtil(Specification spec) {
this.spec = spec;
}
public static QueryUtil wheres(Specification spec) {
return new QueryUtil(spec);
}
@SuppressWarnings("unchecked")
public QueryUtil and(Specification other) {
this.spec.and(other);
return this;
}
@SuppressWarnings("unchecked")
public QueryUtil or(Specification other) {
this.spec.or(other);
return this;
}
public Specification build() {
return this.spec;
}
/**
* 单where条件
*
* @param p
* @return
*/
public static Specification where(Condition p) {
List<Condition> ps = new ArrayList<>();
ps.add(p);
return where(ps);
}
/**
* 多where条件and连接
*
* @param ps
* @param <T>
* @return
*/
public static <T> Specification<T> where(List<Condition> ps) {
return (Root<T> root, CriteriaQuery<?> query, CriteriaBuilder builder) ->
builder.and(getPredicateList(root, builder, ps));
}
/**
* 多where条件or连接
*
* @param ps
* @param <T>
* @return
*/
public static <T> Specification<T> or(List<Condition> ps) {
return (Root<T> root, CriteriaQuery<?> query, CriteriaBuilder builder) ->
builder.or(getPredicateList(root, builder, ps));
}
/**
* 获取查询条件数组
*
* @param root
* @param builder
* @param ps
* @return
*/
private static Predicate[] getPredicateList(Root<?> root, CriteriaBuilder builder, List<Condition> ps) {
List<Predicate> predicateList = new ArrayList<>();
ps.forEach(p -> {
Predicate predicate = buildPredicate(builder, root, p);
predicateList.add(predicate);
});
return predicateList.toArray(new Predicate[predicateList.size()]);
}
/**
* 选取查询方式
*
* @param cb
* @param path
* @param p
* @return
*/
private static Predicate buildPredicate(CriteriaBuilder cb, Root<?> root,Condition p) {
String fieldName=p.getName();
Path path;
if (fieldName.contains(".")) {
String[] names = StringUtils.split(fieldName, ".");
//获取该属性的类型，Set？List？Map？
path = root.get(names[0]);
Class clazz = path.getJavaType();
if (clazz.equals(Set.class)) {
SetJoin setJoin = root.joinSet(names[0]);
path = setJoin.get(names[1]);
} else if (clazz.equals(List.class)) {
ListJoin listJoin = root.joinList(names[0]);
path = listJoin.get(names[1]);
} else if (clazz.equals(Map.class)) {
MapJoin mapJoin = root.joinMap(names[0]);
path = mapJoin.get(names[1]);
} else {
//是many to one时
path = path.get(names[1]);
}
} else {
//单表查询
path = root.get(fieldName);
}
// Path path=root.get(p.getName());
Predicate predicate;
switch (p.getOperator()) {
case LIKE:
predicate = cb.like(path, "%"+p.getValue().toString()+"%");
break;
case NOTLIKE:
predicate = cb.notLike(path, "%"+p.getValue().toString()+"%");
break;
case EQ:
predicate = cb.equal(path, p.getValue());
break;
case NOTEQ:
predicate = cb.notEqual(path, p.getValue());
break;
case GT:
predicate = cb.greaterThan(path, (Comparable) p.getValue());
break;
case GTEQ:
predicate = cb.greaterThanOrEqualTo(path, (Comparable) p.getValue());
break;
case LT:
predicate = cb.lessThan(path, (Comparable) p.getValue());
break;
case LTEQ:
predicate = cb.lessThanOrEqualTo(path, (Comparable) p.getValue());
break;
case NULL:
predicate = cb.isNull(path);
break;
case NOTNULL:
predicate = cb.isNotNull(path);
break;
case IN:
predicate = getIn(path, p.getValue());
break;
case NOTIN:
predicate = getIn(path, p.getValue()).not();
break;
default:
throw new IllegalArgumentException("非法的操作符");
}
return predicate;
}
/**
* 创建in操作
*
* @param path
* @param value
* @param <T>
* @return
*/
private static <T> Predicate getIn(Path path, T value) {
if (value instanceof Object[]) {
return path.in((Object[]) value);
} else if (value instanceof Collection) {
return path.in((Collection) value);
} else {
throw new IllegalArgumentException("非法的IN操作");
}
}
/***********************************************单where条件查询********************************************************/
// like
public static Specification like(String name, String value) {
return (root, query, cb) ->
cb.like(root.get(name), value);
}
// notlike
public static Specification notLike(String name, String value) {
return (root, query, cb) ->
cb.like(root.get(name), value).not();
}
// =
public static Specification eq(String name, Object value) {
return (root, query, cb) ->
cb.equal(root.get(name), value);
}
// !=
public static Specification notEq(String name, Object value) {
return (root, query, cb) ->
cb.notEqual(root.get(name), value);
}
// >
public static Specification gt(String name, Object value) {
return (root, query, cb) ->
cb.greaterThan(root.get(name), (Comparable) value);
}
// >=
public static Specification gtEq(String name, Object value) {
return (root, query, cb) ->
cb.greaterThanOrEqualTo(root.get(name), (Comparable) value);
}
// <
public static Specification lt(String name, Object value) {
return (root, query, cb) ->
cb.lessThan(root.get(name), (Comparable) value);
}
// <=
public static Specification ltEq(String name, Object value) {
return (root, query, cb) ->
cb.lessThanOrEqualTo(root.get(name), (Comparable) value);
}
// is null
public static Specification isNull(String name) {
return (root, query, cb) ->
cb.isNull(root.get(name));
}
// is not null
public static Specification notNull(String name) {
return (root, query, cb) ->
cb.isNotNull(root.get(name));
}
// in
public static Specification in(String name, Object value) {
return (root, query, cb) ->
root.get(name).in(value);
}
// not in
public static Specification notIn(String name, Object value) {
return (root, query, cb) ->
root.get(name).in(value).not();
}
}
public List<MConsignment> findSellingByUserId(String userId) {
List<Condition> conditions=new ArrayList<Condition>();
conditions.add(Condition.eq("userId", userId));
conditions.add(Condition.eq("endTime",-1));
conditions.add(Condition.eq("isValid",YesOrNo.YES));
return dao.findAll(QueryUtil.where(conditions));
}
public List<MConsignment> findSoldByUserId(String userId) {
List<Condition> conditions=new ArrayList<Condition>();
conditions.add(Condition.eq("userId", userId));
conditions.add(Condition.notEq("endTime",-1));
return dao.findAll(conditions);
}
public List<MConsignment> findSellFailByUserId(String userId) {
List<Condition> conditions=new ArrayList<Condition>();
conditions.add(Condition.gt("remNum", 0));
conditions.add(Condition.eq("isValid",YesOrNo.NO));
return dao.findAll(QueryUtil.where(conditions));
}
浙公网安备 33010602011771号