/**
 * @author: kl @kailing.pub
 * @date: 2019/11/11
 */
@Repository
public interface SendLogRepository extends JpaRepository {
    /**
     * 派生的通過(guò)解析方法名稱的查詢
     * @param templateName
     * @return
     */
    ListfindSendLogByTemplateName(String templateName);
    /**
     * HQL
     * @param templateName
     * @return
     */
    @Query(value ="select SendLog from  SendLog s where s.templateName = :templateName")
    ListfindByTempLateName(String templateName);
    /**
     * 原生sql
     * @param templateName
     * @return
     */
    @Query(value ="select s.* from  sms_sendlog s where s.templateName = :templateName",nativeQuery = true)
    ListfindByTempLateNameNative(String templateName);
}

public void testExampleQuery() {
        SendLog log = new SendLog();
        log.setTemplateName("kl");
        /*
         * 注意：withMatcher方法的propertyPath參數(shù)值填寫(xiě)領(lǐng)域?qū)ο蟮淖侄沃?，而不是?shí)際的表字段
         */
        ExampleMatcher matcher = ExampleMatcher.matching()
                .withMatcher("templateName", match -> match.contains());
        Example example = Example.of(log, matcher);
        Pageable pageable = PageRequest.of(0, 10);
        Page logPage = repository.findAll(example, pageable);
    }

public void testJpaSpecificationQuery() {
        String templateName = "kk";
        Specification specification = (Specification) (root, query, criteriaBuilder) -> {
            Predicate predicate = criteriaBuilder.like(root.get("templateName"),templateName);
            query.where(predicate);
            return predicate;
        };

        Pageable pageable = PageRequest.of(0, 2);
        Page logPage = sendLogRepository.findAll(specification, pageable);
    }

/**
 * @author: kl @kailing.pub
 * @date: 2019/11/8
 */
public abstract class BaseJpaRepository extends SimpleJpaRepository {
    public EntityManager em;
    BaseJpaRepository(ClassdomainClass, EntityManager em) {
        super(domainClass, em);
        this.em = em;
    }
}

/**
 * @author: kl @kailing.pub
 * @date: 2019/11/11
 */
@Repository
public class SendLogJpaRepository extends BaseJpaRepository {
    public SendLogJpaRepository(EntityManager em) {
        super(SendLog.class, em);
    }
    /**
     * 原生查詢
     * @param templateName
     * @return
     */
    public SendLog findByTemplateName(String templateName){
        String sql = "select * from send_log where templateName = :templateName";
        Query query =em.createNativeQuery(sql);
        query.setParameter("templateName",templateName);
        return (SendLog) query.getSingleResult();
    }
    /**
     * hql查詢
     * @param templateName
     * @return
     */
    public SendLog findByTemplateNameNative(String templateName){
        String hql = "from SendLog where templateName = :templateName";
        TypedQueryquery =em.createQuery(hql,SendLog.class);
        query.setParameter("templateName",templateName);
       return query.getSingleResult();
    }
    /**
     *  JPASpecification 實(shí)現(xiàn)復(fù)雜分頁(yè)查詢
     * @param logDto
     * @param pageable
     * @return
     */
    public PagefindAll(SendLogDto logDto,Pageable pageable) {
        Specification specification = (Specification) (root, query, criteriaBuilder) -> {
            Predicate predicate = criteriaBuilder.conjunction();
            if(!StringUtils.isEmpty(logDto.getTemplateName())){
                predicate.getExpressions().add( criteriaBuilder.like(root.get("templateName"),logDto.getTemplateName()));
            }
            if(logDto.getStartTime() !=null){
                predicate.getExpressions().add(criteriaBuilder.greaterThanOrEqualTo(root.get("createTime").as(Timestamp.class),logDto.getStartTime()));
            }
            query.where(predicate);
            return predicate;
        };
        return  findAll(specification, pageable);
    }
}

/**
 * @author: kl @kailing.pub
 * @date: 2019/11/8
 */
public abstract class BaseJpaRepository extends SimpleJpaRepository {
    public EntityManager em;
    protected final QuerydslJpaPredicateExecutorjpaPredicateExecutor;
    BaseJpaRepository(ClassdomainClass, EntityManager em) {
        super(domainClass, em);
        this.em = em;
        this.jpaPredicateExecutor = new QuerydslJpaPredicateExecutor<>(JpaEntityInformationSupport.getEntityInformation(domainClass, em), em, SimpleEntityPathResolver.INSTANCE, getRepositoryMethodMetadata());
    }
}

/**
     * QSendLog實(shí)體是QueryDsl插件自動(dòng)生成的，插件會(huì)自動(dòng)掃描加了@Entity的實(shí)體，生成一個(gè)用于查詢的EntityPath類
     */
    private  final  static QSendLog sendLog = QSendLog.sendLog;
    public PagefindAll(SendLogDto logDto, Pageable pageable) {
        BooleanExpression expression = sendLog.isNotNull();
        if (logDto.getStartTime() != null) {
            expression = expression.and(sendLog.createTime.gt(logDto.getStartTime()));
        }
        if (!StringUtils.isEmpty(logDto.getTemplateName())) {
            expression = expression.and(sendLog.templateName.like("%"+logDto.getTemplateName()+"%"));
        }
        return jpaPredicateExecutor.findAll(expression, pageable);
    }

public interface QuerydslPredicateExecutor{
	OptionalfindOne(Predicate predicate);
	IterablefindAll(Predicate predicate);
	IterablefindAll(Predicate predicate, Sort sort);
	IterablefindAll(Predicate predicate, OrderSpecifier... orders);
	IterablefindAll(OrderSpecifier... orders);
	PagefindAll(Predicate predicate, Pageable pageable);
	long count(Predicate predicate);
	boolean exists(Predicate predicate);
}

/**
 * @author: kl @kailing.pub
 * @date: 2019/11/8
 */
public abstract class BaseJpaRepository extends SimpleJpaRepository {
    protected final JPAQueryFactory jpaQueryFactory;
    protected final QuerydslJpaPredicateExecutorjpaPredicateExecutor;
    protected final EntityManager em;
    private final EntityPathpath;
    protected final Querydsl querydsl;
    BaseJpaRepository(ClassdomainClass, EntityManager em) {
        super(domainClass, em);
        this.em = em;
        this.jpaPredicateExecutor = new QuerydslJpaPredicateExecutor<>(JpaEntityInformationSupport.getEntityInformation(domainClass, em), em, SimpleEntityPathResolver.INSTANCE, getRepositoryMethodMetadata());
        this.jpaQueryFactory = new JPAQueryFactory(em);
        this.path = SimpleEntityPathResolver.INSTANCE.createPath(domainClass);
        this.querydsl = new Querydsl(em, new PathBuilder(path.getType(), path.getMetadata()));
    }
    protected PagefindAll(Predicate predicate, Pageable pageable, OrderSpecifier... orders) {
        final JPAQuery countQuery = jpaQueryFactory.selectFrom(path);
        countQuery.where(predicate);
        JPQLQueryquery = querydsl.applyPagination(pageable, countQuery);
        query.orderBy(orders);
        return PageableExecutionUtils.getPage(query.fetch(), pageable, countQuery::fetchCount);
    }
}

/**
     * QSendLog實(shí)體是QueryDsl插件自動(dòng)生成的，插件會(huì)自動(dòng)掃描加了@Entity的實(shí)體，生成一個(gè)用于查詢的EntityPath類
     */
    private  final  static QSendLog qSendLog = QSendLog.sendLog;
    private  final static QTemplate qTemplate = QTemplate.template;

    public PagefindAll(SendLogDto logDto, Template template, Pageable pageable) {
        JPAQuery  countQuery = jpaQueryFactory.selectFrom(qSendLog).leftJoin(qTemplate);
        countQuery.where(qSendLog.templateCode.eq(qTemplate.code));
        if(!StringUtils.isEmpty(template.getName())){
            countQuery.where(qTemplate.name.eq(template.getName()));
        }
        JPQLQuery query = querydsl.applyPagination(pageable, countQuery);
        return PageableExecutionUtils.getPage(query.fetch(), pageable, countQuery::fetchCount);
    }

<dependency>
                <groupId>p6spy</groupId>
                <artifactId>p6spy</artifactId>
                <version>${p6spy.version}</version>
</dependency>

jdbc:mysql://127.0.0.1:3306 改成 jdbc:p6spy:mysql://127.0.0.1:3306

appender=com.p6spy.engine.spy.appender.Slf4JLogger
logMessageFormat=com.p6spy.engine.spy.appender.CustomLineFormat
customLogMessageFormat = executionTime:%(executionTime)| sql:%(sqlSingleLine)