jpa手写sql_JPA实现@Query手写原生sql拼接动态sql并分页的方法 - it610.com

import java.math.BigDecimal;

import java.util.Collections;

import java.util.List;

import javax.persistence.EntityManager;

import javax.persistence.PersistenceContext;

import javax.persistence.Query;

import org.springframework.data.domain.Page;

import org.springframework.data.domain.PageImpl;

import org.springframework.data.domain.Pageable;

import org.springframework.stereotype.Repository;

@Repository("dao")

public class CrawlerHistoryLogDaoImpl implements CrawlerNewHistoryDao {

@PersistenceContext

private EntityManager entityManager;

@Override

public PagegetHistoryInfo(String crawlerId,

String startDate,

String description,

Pageable pageable) {

StringBuffer dataBuffer = new StringBuffer(

"select h.title,h.CONTENT_URL,h.HISTORY_ID,h.CHANNEL_URL,h.description,f.FILE_LOCAL,c.CRAWLER_NAME,h.CREATE_TIME from XT_PC_CRAWLER_HISTORY h left join XT_PC_CRAWLER_FILE f on h.HISTORY_ID=f.ITEM_ID left join XT_PC_CRAWLER c on h.CRAWLER_ID=c.CRAWLER_ID WHERE 1 = 1");

StringBuffer countBuffer = new StringBuffer(

"select count(h.HISTORY_ID) from XT_PC_CRAWLER_HISTORY h left join XT_PC_CRAWLER_FILE f on h.HISTORY_ID=f.ITEM_ID left join XT_PC_CRAWLER c on h.CRAWLER_ID=c.CRAWLER_ID WHERE 1 = 1");

StringBuffer paramBuffer = new StringBuffer();

if (crawlerId != null) {

paramBuffer.append(" AND h.CRAWLER_ID = '" + crawlerId + "'");

}

if(startDate != null) {

paramBuffer

.append(" AND h.CREATE_TIME like '%" + startDate + "%'");

}

if (description != null && description.equals("0")) {

paramBuffer.append(" AND h.description is not null ");

}

if (description != null && description.equals("1")) {

paramBuffer.append(" AND h.description is null ");

}

StringBuffer orderBuffer = new StringBuffer(

" order by h.CREATE_TIME desc");

String dataSql = (dataBuffer.append(paramBuffer).append(orderBuffer))

.toString();

String countSql = (countBuffer.append(paramBuffer)).toString();

System.out.println("{} dataSql= " + dataSql);

System.out.println("{} countSql= " + countSql);

Query dataQuery = entityManager.createNativeQuery(dataSql.toString());

Query countQuery = entityManager.createNativeQuery(countSql.toString());

dataQuery.setFirstResult(pageable.getOffset());

dataQuery.setMaxResults(pageable.getPageSize());

BigDecimal count = (BigDecimal) countQuery.getSingleResult();

Long total = count.longValue();

Listcontent = total > pageable.getOffset() ? dataQuery

.getResultList() : Collections.emptyList();

return new PageImpl<>(content, pageable, total);

// return null;

}

}