/**
* 废弃
*
* @param user
* @param pageable
* @return
*/
public Page<User> getUsersByCondition(User user, Pageable pageable) {
System.out.println(user);
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<User> cq = cb.createQuery(User.class);
Root<User> root = cq.from(User.class);
cq.select(root);
// 使用like的时候可以不用判断是否有该参数传来，因为like %% 就等于没有这个限制条件
Predicate condition1 =
cb.like(root.get("username").as(String.class), cb.parameter(String.class, "username"));
Predicate condition2 =
cb.like(root.get("userAlias").as(String.class), cb.parameter(String.class, "userAlias"));
// Predicate
// condition3=cb.equal(root.get("createTime").as(Date.class),cb.parameter(String.class,
// "createTime"));
cq.where(condition1, condition2);
cb.and(condition1, condition2);
TypedQuery<User> query = em.createQuery(cq);
query.setParameter("username", "%" + user.getUsername() + "%");
query.setParameter("userAlias", "%" + user.getUserAlias() + "%");
query.setFirstResult(pageable.getOffset());
query.setMaxResults(pageable.getPageSize());
List<User> users = query.getResultList();
Page<User> page = new PageImpl<User>(users, pageable, 10);
return page;
}
private Query createQueryParam(String _jpql, List<SqlQueryItem> _paramList, Pageable _pageable) {
Query query = manager.createQuery(_jpql);
if (_pageable != null) {
int maxResult = _pageable.getPageSize();
query.setFirstResult(_pageable.getPageNumber() * _pageable.getPageSize());
query.setMaxResults(maxResult);
}
if (_paramList != null) {
for (SqlQueryItem param : _paramList) {
Object value = param.getValue();
if (param.getAction() == SqlActionType.LIKE) {
String val = param.getValue().toString().trim();
if (!val.startsWith("%")) {
val = "%" + val;
}
if (!val.endsWith("%")) {
val = val + "%";
}
value = val;
}
query.setParameter(param.getPropertyName().replace(".", ""), value);
}
}
return query;
}
// 根据条件筛选课程订单
@SuppressWarnings("unchecked")
@RequestMapping(value = "/{coachID}", method = RequestMethod.GET)
public PageImpl<OrderLine> findOrderLinesByCoach(
@PathVariable Integer coachID,
@RequestParam(value = "order_status", required = false) String orderStatus,
@RequestParam(value = "start_date") String startDate,
@RequestParam(value = "end_date") String endDate,
Pageable pageable) {
CoachEntity coachEntity = coachService.findOne(coachID);
Validate.notNull(coachEntity, "coach id is invalid");
DateTime startDateTime = (null == startDate ? null : DateTime.parse(startDate));
DateTime endDateTime = (null == endDate ? null : DateTime.parse(endDate));
List<OrderLineEntity> orderLines =
orderLineService.findByOrderLineStatus(coachID, orderStatus, startDateTime, endDateTime);
Integer start = (pageable.getPageNumber() * pageable.getPageSize());
Integer end;
if ((pageable.getPageNumber() + 1) * pageable.getPageSize() > orderLines.size()) {
end = orderLines.size();
} else {
end = ((pageable.getPageNumber() + 1) * pageable.getPageSize());
}
List<OrderLineEntity> show = orderLines.subList(start, end);
return ModelConvertUtils.getPageByOrderLineEntity(show, pageable, orderLines.size());
}
/** @see DATAMONGO-445 */
@Test
public void shouldTakeSkipAndLimitSettingsFromGivenPageable() {
Pageable pageable = new PageRequest(3, 5);
NearQuery query = NearQuery.near(new Point(1, 1)).with(pageable);
assertThat(query.getSkip(), is(pageable.getPageNumber() * pageable.getPageSize()));
assertThat(
(Integer) query.toDBObject().get("num"),
is((pageable.getPageNumber() + 1) * pageable.getPageSize()));
}
@Override
public <T> Page<T> getPage(
Class<T> clazz, Query query, Pageable pageable, HibernateParameter... parameters) {
long totalItemCount = count(query, parameters);
List<T> items =
find(
clazz,
query,
pageable.getPageNumber() * pageable.getPageSize(),
pageable.getPageSize(),
parameters);
return new PageImpl<T>(items, pageable, totalItemCount);
}
@Override
public <T> Page<T> getPage(Class<T> clazz, Criteria criteria, Pageable pageable) {
Criteria countCriteria = HibernateTool.copyCriteria(criteria);
long totalItemCount = count(countCriteria);
List<T> items =
find(
clazz,
criteria,
pageable.getPageNumber() * pageable.getPageSize(),
pageable.getPageSize(),
CriteriaTool.toOrders(pageable.getSort()).toArray(new Order[0]));
return new PageImpl<T>(items, pageable, totalItemCount);
}
@Override
public List<T> queryNativeSqlListEntity(String _nativeSql, Object[] _params, Pageable _pageable) {
Query query = getManager().createNativeQuery(_nativeSql, this.getCls());
if (_pageable != null) {
int maxResult = _pageable.getPageSize();
query.setFirstResult(_pageable.getPageNumber() * _pageable.getPageSize());
query.setMaxResults(maxResult);
}
if (_params != null) {
for (int i = 0; i < _params.length; i++) {
query.setParameter(i + 1, _params[i]);
}
}
return query.getResultList();
}
/**
* Append pagination information {@code start, rows} to {@link SolrQuery}
*
* @param query
* @param pageable
*/
protected void appendPagination(SolrQuery query, Pageable pageable) {
if (pageable == null) {
return;
}
query.setStart(pageable.getOffset());
query.setRows(pageable.getPageSize());
}
@Override
@Transactional(readOnly = true)
public Page<UserGroupCheck> findAllByPage(Pageable pageable, Long groupId, long total) {
// TODO use setParameter
String sql = USER_GROUP_CHECK_QUERY;
String orderBy = "";
String sort = "";
Iterator<Order> i = pageable.getSort().iterator();
while (i.hasNext()) {
Order order = i.next();
orderBy = order.getProperty();
sort = order.getDirection().name();
}
sql = sql.replace(ORDER_BY, orderBy);
sql = sql.replace(SORT, sort);
Query query = entityManager.createNativeQuery(sql);
query.setParameter(GROUP_ID, groupId);
query.setParameter(LIMIT, pageable.getPageSize());
query.setParameter(OFFSET, pageable.getOffset());
List<UserGroupCheck> result = JpaUtil.getResultList(query, UserGroupCheck.class);
Page<UserGroupCheck> page = new PageImpl<>(result, pageable, total);
return page;
}
/** @see DATAMONGO-445 */
@Test
public void shouldTakeSkipAndLimitSettingsFromPageableEvenIfItWasSpecifiedOnQuery() {
int limit = 10;
int skip = 5;
Pageable pageable = new PageRequest(3, 5);
NearQuery query =
NearQuery.near(new Point(1, 1))
.query(Query.query(Criteria.where("foo").is("bar")).limit(limit).skip(skip))
.with(pageable);
assertThat(query.getSkip(), is(pageable.getPageNumber() * pageable.getPageSize()));
assertThat(
(Integer) query.toDBObject().get("num"),
is((pageable.getPageNumber() + 1) * pageable.getPageSize()));
}
public PageBuilder<Signon> findAllByPage(Pageable pageable) {
SelectConditionStep<SignonRecord> where = dsl.selectFrom(SIGNON).where();
String sqlBeforePage = where.getSQL(ParamType.INLINED);
where.limit(pageable.getPageSize()).offset(pageable.getOffset());
List<Signon> signons = where.fetchInto(Signon.class);
return new PageBuilder<>(signons, pageable, sqlBeforePage, dsl);
}
@Test
public void testGetUsersWithAuthority() throws SQLException {
String expectedQuery =
"select \"public\".\"users\".\"username\", \"public\".\"users\".\"locked\", \"public\".\"authorities\".\"authority\", count(\"public\".\"users\".\"username\") over () as \"count\" from \"public\".\"users\" join \"public\".\"authorities\" on \"public\".\"users\".\"username\" = \"public\".\"authorities\".\"username\" where \"public\".\"authorities\".\"authority\" = 'ROLE_ADMIN' order by \"public\".\"users\".\"username\" asc limit 5 offset 0";
Pageable pageable = Mockito.mock(Pageable.class);
ResultSet resultSet = Mockito.mock(ResultSet.class);
Mockito.when(pageable.getPageNumber()).thenReturn(0);
Mockito.when(pageable.getPageSize()).thenReturn(5);
Mockito.when(resultSet.getInt(PagingConstants.COUNT)).thenReturn(1);
Mockito.when(resultSet.getString("username")).thenReturn("admin");
RESTPage<UserDTO> page = userDao.getUsersWithAuthority("ROLE_ADMIN", pageable);
ArgumentCaptor<PagingRowCallbackHandler> pagingRowCallbackHandlerCaptor =
ArgumentCaptor.forClass(PagingRowCallbackHandler.class);
Mockito.verify(jdbcTemplate)
.query(Matchers.eq(expectedQuery), pagingRowCallbackHandlerCaptor.capture());
PagingRowCallbackHandler pagingRowCallbackHandler = pagingRowCallbackHandlerCaptor.getValue();
pagingRowCallbackHandler.processRow(resultSet);
Mockito.verify(resultSet).getInt(PagingConstants.COUNT);
Mockito.verify(resultSet).getString("username");
Assert.assertEquals(1, page.getContentSize());
Assert.assertEquals("admin", page.getContent().get(0).getUsername());
}
private PageParams convert(final Pageable pageable) {
final PageParams result = new PageParams();
result.setPageSize(pageable.getPageSize());
result.setDataCount(pageable.getPageNumber());
result.setPageNo(pageable.getPageNumber());
return result;
}
private Page<?> selectPage(List<Object> items, Pageable pageable) {
final List<Object> itemsOnPage =
items.subList(
pageable.getOffset(),
Math.min(items.size(), pageable.getOffset() + pageable.getPageSize()));
return new PageImpl<>(itemsOnPage, pageable, items.size());
}
@SuppressWarnings({"unchecked", "rawtypes"})
protected Object createPage(Iterable<?> result, Pageable pageable) {
final List resultList = IteratorUtil.addToCollection(result, new ArrayList());
if (pageable == null) {
return new PageImpl(resultList);
}
final int currentTotal = pageable.getOffset() + pageable.getPageSize();
return new PageImpl(resultList, pageable, currentTotal);
}
protected Map<String, Object> applyPagination(Map<String, Object> parameter, Pageable pageable) {
if (pageable == null) return parameter;
parameter.put(PAGE_OFFSET, pageable.getOffset());
parameter.put(PAGE_SIZE, pageable.getPageSize());
return applySorting(parameter, pageable.getSort());
}
@RequestMapping("/persons")
PagedResources<Resource<Person>> sample(Pageable pageable) {
Page<Person> page =
new PageImpl<Person>(
Arrays.asList(new Person()),
pageable,
pageable.getOffset() + pageable.getPageSize() + 1);
return assembler.toResource(page);
}
/**
* Reads the given {@link TypedQuery} into a {@link Page} applying the given {@link Pageable} and
* {@link Specification}.
*
* @param query must not be {@literal null}.
* @param spec can be {@literal null}.
* @param pageable can be {@literal null}.
* @return
*/
private Page<M> readPage(TypedQuery<M> query, Pageable pageable, Specification<M> spec) {
query.setFirstResult(pageable.getOffset());
query.setMaxResults(pageable.getPageSize());
Long total = QueryUtils.executeCountQuery(getCountQuery(spec));
List<M> content =
total > pageable.getOffset() ? query.getResultList() : Collections.<M>emptyList();
return new PageImpl<M>(content, pageable, total);
}
private FullTextQuery buildFullTextQuery(
UserSearchRequest request, Pageable pageable, Criteria criteria) {
FullTextEntityManager fullTextEntityManager = Search.getFullTextEntityManager(entityManager);
QueryBuilder qb =
fullTextEntityManager.getSearchFactory().buildQueryBuilder().forEntity(User.class).get();
@SuppressWarnings("rawtypes")
BooleanJunction<BooleanJunction> junction = qb.bool();
junction.must(qb.all().createQuery());
if (StringUtils.hasText(request.getKeyword())) {
Analyzer analyzer = fullTextEntityManager.getSearchFactory().getAnalyzer("synonyms");
String[] fields =
new String[] {
"loginId", "name.firstName", "name.lastName",
};
MultiFieldQueryParser parser = new MultiFieldQueryParser(fields, analyzer);
parser.setDefaultOperator(QueryParser.Operator.AND);
Query query = null;
try {
query = parser.parse(request.getKeyword());
} catch (ParseException e1) {
try {
query = parser.parse(QueryParser.escape(request.getKeyword()));
} catch (ParseException e2) {
throw new RuntimeException(e2);
}
}
junction.must(query);
}
if (!CollectionUtils.isEmpty(request.getRoles())) {
for (User.Role role : request.getRoles()) {
junction.must(qb.keyword().onField("roles").matching(role).createQuery());
}
}
Query searchQuery = junction.createQuery();
Sort sort = new Sort(new SortField("id", SortField.Type.STRING, false));
FullTextQuery persistenceQuery =
fullTextEntityManager
.createFullTextQuery(searchQuery, User.class)
.setCriteriaQuery(criteria)
// .setProjection("id")
.setSort(sort);
if (pageable != null) {
persistenceQuery.setFirstResult(pageable.getOffset());
persistenceQuery.setMaxResults(pageable.getPageSize());
}
return persistenceQuery;
}
public String selectAll(TableDescription table, Pageable page) {
Sort sort = page.getSort() != null ? page.getSort() : sortById(table);
return format(
"SELECT t2__.* FROM ( "
+ "SELECT row_number() OVER (ORDER BY %s) AS rn__, t1__.* FROM ( %s ) t1__ "
+ ") t2__ WHERE t2__.rn__ BETWEEN %s AND %s",
orderByExpression(sort),
selectAll(table),
page.getOffset() + 1,
page.getOffset() + page.getPageSize());
}
protected <T, T1, T2> PageRequestId<T> findWithSpecification(
String requestId,
EntityManager entityManager,
Pageable pageable,
Filter3<T, T1, T2> specification,
Class<T> type,
Class<T1> type1,
Class<T2> type2) {
CriteriaBuilder builder = entityManager.getCriteriaBuilder();
long total;
{
CriteriaQuery<Long> cqTotal = builder.createQuery(Long.class);
Root<T1> root1 = cqTotal.from(type1);
Root<T2> root2 = cqTotal.from(type2);
Predicate predicate = specification.toPredicate(root1, root2, cqTotal, builder);
if (predicate != null) cqTotal.where(predicate);
cqTotal.select(builder.count(root1));
total = entityManager.createQuery(cqTotal).getSingleResult();
}
CriteriaQuery<T> cq = builder.createQuery(type);
Root<T1> root1 = cq.from(type1);
Root<T2> root2 = cq.from(type2);
Predicate predicate = specification.toPredicate(root1, root2, cq, builder);
if (predicate != null) cq.where(predicate);
cq.select(specification.getSelection(root1, root2, cq, builder));
if (pageable.getSort() != null) {
List<Order> orders = new LinkedList<Order>();
for (Iterator<org.springframework.data.domain.Sort.Order> itr = pageable.getSort().iterator();
itr.hasNext(); ) {
org.springframework.data.domain.Sort.Order order = itr.next();
String sx[] = order.getProperty().split("\\.");
Path<Object> p;
if (sx[0].equals("t1")) {
p = root1.get(sx[1]);
} else if (sx[0].equals("t2")) {
p = root2.get(sx[1]);
} else {
throw new RuntimeException("Invalid order " + U.dump(order));
}
for (int i = 2, il = sx.length; i < il; i++) p = p.get(sx[i]);
if (order.isAscending()) {
orders.add(builder.asc(p));
} else {
orders.add(builder.desc(p));
}
}
cq.orderBy(orders);
}
TypedQuery<T> qry = entityManager.createQuery(cq);
qry.setFirstResult(pageable.getOffset()).setMaxResults(pageable.getPageSize());
return new PageRequestIdImpl<T>(qry.getResultList(), pageable, total, requestId);
}
@Test
public void testPagination() {
Pageable pageable = new PageRequest(0, 2);
Page<ProductBean> page1 = repo.findByNameStartingWith("name", pageable);
Assert.assertEquals(pageable.getPageSize(), page1.getNumberOfElements());
Assert.assertTrue(page1.hasNextPage());
Assert.assertEquals(3, page1.getTotalElements());
pageable = new PageRequest(1, 2);
Page<ProductBean> page2 = repo.findByNameStartingWith("name", pageable);
Assert.assertEquals(1, page2.getNumberOfElements());
Assert.assertFalse(page2.hasNextPage());
Assert.assertEquals(3, page2.getTotalElements());
}
@Test
public void testGetUsersWithAuthority_page2() {
String expectedQuery =
"select \"public\".\"users\".\"username\", \"public\".\"users\".\"locked\", \"public\".\"authorities\".\"authority\", count(\"public\".\"users\".\"username\") over () as \"count\" from \"public\".\"users\" join \"public\".\"authorities\" on \"public\".\"users\".\"username\" = \"public\".\"authorities\".\"username\" where \"public\".\"authorities\".\"authority\" = 'ROLE_ADMIN' order by \"public\".\"users\".\"username\" asc limit 5 offset 5";
Pageable pageable = Mockito.mock(Pageable.class);
Mockito.when(pageable.getPageNumber()).thenReturn(1);
Mockito.when(pageable.getPageSize()).thenReturn(5);
userDao.getUsersWithAuthority("ROLE_ADMIN", pageable);
Mockito.verify(jdbcTemplate)
.query(Matchers.eq(expectedQuery), Matchers.any(PagingRowCallbackHandler.class));
}
@Override
public Page<Catalog> findAll(Pageable arg0) {
int pSize = arg0.getPageSize();
int pNumb = arg0.getPageNumber();
int pFirst = pNumb * pSize;
int pLast = pFirst + pSize;
int total = datas.size();
List<Catalog> content = new ArrayList<>();
for (int i = 0; i < total; i++) {
if (i >= pFirst && i < pLast) {
Catalog data = datas.get(i);
content.add(data);
}
}
return new PageImpl<>(content, arg0, total);
}
private <C, X> Page<C> readPage(
TypedQuery<C> query,
Pageable pageable,
Specification<T> spec,
Selections<T, C, X> selection,
List<Expression<?>> groupBy,
Predicate having) {
query.setFirstResult(pageable.getOffset());
query.setMaxResults(pageable.getPageSize());
Long total = executeCountQuery(getCountQuery(spec, selection, groupBy, having));
List<C> content =
total > pageable.getOffset() ? query.getResultList() : Collections.<C>emptyList();
return new PageImpl<>(content, pageable, total);
}
protected <T> PageRequestId<T> findWithSpecification(
String requestId,
EntityManager entityManager,
Pageable pageable,
Filter<T> filter,
Class<T> type) {
CriteriaBuilder builder = entityManager.getCriteriaBuilder();
long total;
{
CriteriaQuery<Long> cqTotal = builder.createQuery(Long.class);
Root<T> root = cqTotal.from(type);
if (filter != null) {
Predicate predicate = filter.toPredicate(root, cqTotal, builder);
if (predicate != null) cqTotal.where(predicate);
}
cqTotal.select(builder.count(root));
total = entityManager.createQuery(cqTotal).getSingleResult();
}
CriteriaQuery<T> cq = builder.createQuery(type);
Root<T> root = cq.from(type);
if (filter != null) {
Predicate predicate = filter.toPredicate(root, cq, builder);
if (predicate != null) cq.where(predicate);
}
cq.select(root);
if (pageable.getSort() != null) {
List<Order> orders = new LinkedList<Order>();
for (Iterator<org.springframework.data.domain.Sort.Order> itr = pageable.getSort().iterator();
itr.hasNext(); ) {
org.springframework.data.domain.Sort.Order order = itr.next();
String sx[] = order.getProperty().split("\\.");
Path<Object> p = root.get(sx[0]);
for (int i = 1, il = sx.length; i < il; i++) p = p.get(sx[i]);
if (order.isAscending()) {
orders.add(builder.asc(p));
} else {
orders.add(builder.desc(p));
}
}
cq.orderBy(orders);
}
TypedQuery<T> qry = entityManager.createQuery(cq);
qry.setFirstResult(pageable.getOffset()).setMaxResults(pageable.getPageSize());
return new PageRequestIdImpl<T>(qry.getResultList(), pageable, total, requestId);
}
/*
* (non-Javadoc)
* @see org.springframework.data.repository.history.RevisionRepository#findRevisions(java.io.Serializable, org.springframework.data.domain.Pageable)
*/
@SuppressWarnings("unchecked")
public Page<Revision<N, T>> findRevisions(ID id, Pageable pageable) {
Class<T> type = entityInformation.getJavaType();
AuditReader reader = AuditReaderFactory.get(entityManager);
List<Number> revisionNumbers = reader.getRevisions(type, id);
if (pageable.getOffset() > revisionNumbers.size()) {
return new PageImpl<Revision<N, T>>(Collections.<Revision<N, T>>emptyList(), pageable, 0);
}
int upperBound = pageable.getOffset() + pageable.getPageSize();
upperBound = upperBound > revisionNumbers.size() ? revisionNumbers.size() : upperBound;
List<? extends Number> subList = revisionNumbers.subList(pageable.getOffset(), upperBound);
Revisions<N, T> revisions = getEntitiesForRevisions((List<N>) subList, id, reader);
return new PageImpl<Revision<N, T>>(revisions.getContent(), pageable, revisionNumbers.size());
}
@Override
public Page<Comment> search(CommentSearchRequest request, Pageable pageable) {
FullTextEntityManager fullTextEntityManager = Search.getFullTextEntityManager(entityManager);
QueryBuilder qb =
fullTextEntityManager.getSearchFactory().buildQueryBuilder().forEntity(Comment.class).get();
@SuppressWarnings("rawtypes")
BooleanJunction<BooleanJunction> junction = qb.bool();
junction.must(qb.all().createQuery());
if (StringUtils.hasText(request.getKeyword())) {
Analyzer analyzer = fullTextEntityManager.getSearchFactory().getAnalyzer("synonyms");
String[] fields = new String[] {"authorName", "content"};
MultiFieldQueryParser parser = new MultiFieldQueryParser(fields, analyzer);
parser.setDefaultOperator(QueryParser.Operator.AND);
Query query = null;
try {
query = parser.parse(request.getKeyword());
} catch (ParseException e1) {
try {
query = parser.parse(QueryParser.escape(request.getKeyword()));
} catch (ParseException e2) {
throw new RuntimeException(e2);
}
}
junction.must(query);
}
if (StringUtils.hasText(request.getLanguage())) {
junction.must(
qb.keyword().onField("post.language").matching(request.getLanguage()).createQuery());
}
if (request.getPostId() != null) {
junction.must(qb.keyword().onField("post.id").matching(request.getPostId()).createQuery());
}
if (request.getApproved() != null) {
junction.must(qb.keyword().onField("approved").matching(request.getApproved()).createQuery());
}
Query searchQuery = junction.createQuery();
Session session = (Session) entityManager.getDelegate();
Criteria criteria =
session
.createCriteria(Comment.class)
.setFetchMode("post", FetchMode.JOIN)
.setFetchMode("author", FetchMode.JOIN);
Sort sort = null;
if (pageable.getSort() != null) {
if (pageable.getSort().getOrderFor("date") != null) {
Order order = pageable.getSort().getOrderFor("date");
sort =
new Sort(
new SortField(
"date", SortField.Type.STRING, order.getDirection().equals(Direction.DESC)),
new SortField(
"id", SortField.Type.LONG, order.getDirection().equals(Direction.DESC)));
}
}
if (sort == null) {
sort =
new Sort(
new SortField("date", SortField.Type.STRING),
new SortField("id", SortField.Type.LONG));
}
FullTextQuery persistenceQuery =
fullTextEntityManager
.createFullTextQuery(searchQuery, Comment.class)
.setCriteriaQuery(criteria)
.setSort(sort);
persistenceQuery.setFirstResult(pageable.getOffset());
persistenceQuery.setMaxResults(pageable.getPageSize());
int resultSize = persistenceQuery.getResultSize();
@SuppressWarnings("unchecked")
List<Comment> results = persistenceQuery.getResultList();
return new PageImpl<>(results, pageable, resultSize);
}
// 動的条件に一致するEntityを全件検索 するサンプルを元に実装
//
// http://terasolunaorg.github.io/guideline/5.0.1.RELEASE/ja/ArchitectureInDetail/DataAccessJpa.html#id21
@Override
public Page<Result> findByCriteria(ResultCriteria criteria, Pageable pageable) {
// collect dynamic conditions.
final List<String> andConditions = new ArrayList<String>();
final List<String> joinConditions = new ArrayList<String>();
final Map<String, Object> bindParameters = new HashMap<String, Object>();
if (criteria.getStaffId() != null) {
joinConditions.add("r.resultDetails rd");
andConditions.add("rd.staff.id = :staffId");
bindParameters.put("staffId", criteria.getStaffId());
}
// TODO result の検索条件はここに書く
// if (criteria.getStaffId() != null) {
// andConditions.add("d.staffId = :staffId");
// bindParameters.put("staffId", criteria.getStaffId());
// }
// if (!CollectionUtils.isEmpty(criteria.getStatusCodes())) {
// andConditions.add("o.status.code IN :statusCodes");
// bindParameters.put("statusCodes", criteria.getStatusCodes());
// }
// if (andConditions.isEmpty()) {
// List<Result> Results = Collections.emptyList();
// return new PageImpl<Result>(Results, pageable, 0); // (3)
// }
// create dynamic query.
final StringBuilder queryString = new StringBuilder();
final StringBuilder countQueryString = new StringBuilder(); // (4)
final StringBuilder conditionsString = new StringBuilder(); // (4)
queryString.append("select r FROM Result r ");
countQueryString.append("SELECT COUNT(r) FROM Result r ");
// (11)
// add join conditions.
for (String joinCondition : joinConditions) {
conditionsString.append(" JOIN ").append(joinCondition);
}
// add conditions.
Iterator<String> andConditionsIt = andConditions.iterator();
if (andConditionsIt.hasNext()) {
conditionsString.append(" WHERE ").append(andConditionsIt.next());
}
while (andConditionsIt.hasNext()) {
conditionsString.append(" AND ").append(andConditionsIt.next());
}
queryString.append(conditionsString); // (6)
countQueryString.append(conditionsString); // (6)
// add Result by condition.
// (7)
String query = QueryUtils.applySorting(queryString.toString(), pageable.getSort(), "r");
// create typed query.
final TypedQuery<Long> countQuery =
entityManager.createQuery(countQueryString.toString(), Long.class); // (8)
final TypedQuery<Result> findQuery = entityManager.createQuery(query, Result.class);
// bind parameters.
for (Map.Entry<String, Object> bindParameter : bindParameters.entrySet()) {
countQuery.setParameter(bindParameter.getKey(), bindParameter.getValue()); // (8)
findQuery.setParameter(bindParameter.getKey(), bindParameter.getValue());
}
long total = countQuery.getSingleResult().longValue(); // (9)
List<Result> Results = null;
if (total != 0) { // (10)
findQuery.setFirstResult(pageable.getOffset());
findQuery.setMaxResults(pageable.getPageSize());
// execute query.
Results = findQuery.getResultList();
} else { // (11)
Results = Collections.emptyList();
}
return new PageImpl<Result>(Results, pageable, total); // (12)
}
@Override
public Page<DBObject> findPage(
String collectionName,
GroupPropertyFilter groupPropertyFilter,
Pageable pageable,
DBObject fields) {
DB db = mongoClient.getDB(mongoDB);
DBCollection dbColl = db.getCollection(collectionName);
BasicDBObject query = new BasicDBObject();
List<BasicDBObject> andQueries = Lists.newArrayList();
List<PropertyFilter> filters = groupPropertyFilter.convertToPropertyFilters();
if (CollectionUtils.isNotEmpty(filters)) {
// Query and Projection Operators: https://docs.mongodb.org/manual/reference/operator/query/
for (PropertyFilter filter : filters) {
Object matchValue = filter.getMatchValue();
if (matchValue == null) {
continue;
}
String[] propertyNames = filter.getConvertedPropertyNames();
List<BasicDBObject> orQueries = Lists.newArrayList();
for (String propertyName : propertyNames) {
BasicDBObject queryItem = new BasicDBObject();
orQueries.add(queryItem);
switch (filter.getMatchType()) {
case EQ:
queryItem.put(propertyName, matchValue);
break;
case NE:
queryItem.put(propertyName, new BasicDBObject("$ne", matchValue));
break;
case BK:
List<BasicDBObject> orList = Lists.newArrayList();
orList.add(new BasicDBObject(propertyName, 0));
orList.add(new BasicDBObject(propertyName, ""));
queryItem.put("$or", orList);
break;
case NB:
queryItem.put(propertyName, new BasicDBObject("$regex", ".{1,}"));
break;
case NU:
queryItem.put(propertyName, 0);
break;
case NN:
queryItem.put(propertyName, 1);
break;
case CN:
queryItem.put(
propertyName,
new BasicDBObject("$regex", ".*" + matchValue + ".*").append("$options", "i"));
break;
case NC:
queryItem.put(
propertyName,
new BasicDBObject(
"$not",
new BasicDBObject("$regex", ".*" + matchValue + ".*")
.append("$options", "i")));
break;
case BW:
queryItem.put(
propertyName,
new BasicDBObject("$regex", "^" + matchValue).append("$options", "i"));
break;
case BN:
queryItem.put(
propertyName,
new BasicDBObject(
"$not",
new BasicDBObject("$regex", "^" + matchValue).append("$options", "i")));
break;
case EW:
queryItem.put(
propertyName,
new BasicDBObject("$regex", matchValue + "$").append("$options", "i"));
break;
case EN:
queryItem.put(
propertyName,
new BasicDBObject(
"$not",
new BasicDBObject("$regex", matchValue + "$").append("$options", "i")));
break;
case BT:
Assert.isTrue(matchValue.getClass().isArray(), "Match value must be array");
Object[] matchValues = (Object[]) matchValue;
Assert.isTrue(matchValues.length == 2, "Match value must have two value");
List<BasicDBObject> andList = Lists.newArrayList();
andList.add(new BasicDBObject(propertyName, new BasicDBObject("$gte", matchValue)));
andList.add(new BasicDBObject(propertyName, new BasicDBObject("$gle", matchValue)));
queryItem.put("$and", andList);
break;
case GT:
queryItem.put(propertyName, new BasicDBObject("$gt", matchValue));
break;
case GE:
queryItem.put(propertyName, new BasicDBObject("$ge", matchValue));
break;
case LT:
queryItem.put(propertyName, new BasicDBObject("$lt", matchValue));
break;
case LE:
queryItem.put(propertyName, new BasicDBObject("$le", matchValue));
break;
case IN:
queryItem.put(propertyName, new BasicDBObject("$in", matchValue));
break;
default:
throw new UnsupportedOperationException(
"Undefined PropertyFilter MatchType: " + filter.getMatchType());
}
}
if (orQueries.size() > 1) {
andQueries.add(new BasicDBObject("$or", orQueries));
} else {
andQueries.add(orQueries.get(0));
}
}
query = new BasicDBObject("$and", andQueries);
}
BasicDBObject sort = new BasicDBObject();
Sort pageSort = pageable.getSort();
if (pageSort != null) {
Iterator<Order> orders = pageSort.iterator();
while (orders.hasNext()) {
Order order = orders.next();
String prop = order.getProperty();
if (order.isAscending()) {
sort.put(prop, 1);
} else {
sort.put(prop, -1);
}
}
}
DBCursor cur =
dbColl
.find(query, fields)
.sort(sort)
.skip(pageable.getOffset())
.limit(pageable.getPageSize());
List<DBObject> rows = Lists.newArrayList();
while (cur.hasNext()) {
DBObject obj = cur.next();
rows.add(obj);
}
Page<DBObject> page = new PageImpl<DBObject>(rows, pageable, dbColl.count(query));
return page;
}
