/**
* 废弃
*
* @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 List<Vote> findVoteEntitiesByPollAltIds(
Long pollId, Long altId, boolean all, int maxResults, int firstResult) {
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Vote> q = cb.createQuery(Vote.class);
Root<Vote> vote = q.from(Vote.class);
ParameterExpression<Long> pid = cb.parameter(Long.class);
ParameterExpression<Long> aid = cb.parameter(Long.class);
q.select(vote)
.where(
cb.and(
cb.equal(vote.get(Vote_.alternative).get(Alternative_.poll).get(Poll_.id), pid),
cb.equal(vote.get(Vote_.alternative).get(Alternative_.id), aid)));
TypedQuery<Vote> query = em.createQuery(q);
query.setParameter(pid, pollId).setParameter(aid, altId);
if (!all) {
query.setMaxResults(maxResults);
query.setFirstResult(firstResult);
}
List<Vote> results = query.getResultList();
System.out.println(">>>>>>>>>>>>>>>>>>>> Votes: " + results);
return results;
}
/** This test provides a demonstration of using a math formual within the where clause. */
@Test
public void testFormulas() {
log.info("*** testFormulas() ***");
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Number> qdef = cb.createQuery(Number.class);
// select count(s) from Sale s
// where (s.amount * :tax) > :amount"
Root<Sale> s = qdef.from(Sale.class);
qdef.select(cb.count(s))
.where(
cb.greaterThan(
cb.prod(s.<BigDecimal>get("amount"), cb.parameter(BigDecimal.class, "tax")),
new BigDecimal(10.0)));
TypedQuery<Number> query = em.createQuery(qdef);
// keep raising taxes until somebody pays $10.00 in tax
double tax = 0.05;
for (;
query.setParameter("tax", new BigDecimal(tax)).getSingleResult().intValue() == 0;
tax += 0.01) {
log.debug("tax=" + NumberFormat.getPercentInstance().format(tax));
}
log.info("raise taxes to: " + NumberFormat.getPercentInstance().format(tax));
assertEquals("unexpected level for tax:" + tax, 0.07, tax, .01);
}
public List<MyClass138> findByCriteria(MyClass138Criteria myClass138Criteria) {
javax.persistence.criteria.CriteriaBuilder cb = this.entityManager.getCriteriaBuilder();
javax.persistence.criteria.CriteriaQuery<MyClass138> c = cb.createQuery(MyClass138.class);
javax.persistence.criteria.Root<MyClass138> emp = c.from(MyClass138.class);
c.select(emp);
List<javax.persistence.criteria.Predicate> criteria =
new java.util.ArrayList<javax.persistence.criteria.Predicate>();
if (myClass138Criteria.getId() != null) {
javax.persistence.criteria.Expression<Long> p = emp.get("id");
javax.persistence.criteria.Expression<Long> val = cb.parameter(Long.class, "id");
criteria.add(cb.equal(p, val));
}
if (criteria.size() == 1) {
c.where(criteria.get(0));
} else {
c.where(cb.and(criteria.toArray(new javax.persistence.criteria.Predicate[0])));
}
javax.persistence.TypedQuery<MyClass138> q = this.entityManager.createQuery(c);
if (myClass138Criteria.getId() != null) {
q.setParameter("id", myClass138Criteria.getId());
}
return new java.util.ArrayList<MyClass138>(q.getResultList());
}
@Override
public ReferenceNumberType getByCode(String code) {
ReferenceNumberType org = null;
try {
CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<ReferenceNumberType> query = builder.createQuery(ReferenceNumberType.class);
Root<ReferenceNumberType> rootEntity = query.from(ReferenceNumberType.class);
ParameterExpression<String> p = builder.parameter(String.class);
query.select(rootEntity).where(builder.equal(rootEntity.get("code"), p));
TypedQuery<ReferenceNumberType> typedQuery = em.createQuery(query);
typedQuery.setParameter(p, code);
org = typedQuery.getSingleResult();
// TypedQuery<ReferenceNumberType> q = em.createQuery("select o from
// com.conx.logistics.mdm.domain.referencenumber.ReferenceNumberType o WHERE o.code =
// :code",ReferenceNumberType.class);
// q.setParameter("code", code);
// org = q.getSingleResult();
} catch (NoResultException e) {
} catch (Exception e) {
e.printStackTrace();
} catch (Error e) {
StringWriter sw = new StringWriter();
e.printStackTrace(new PrintWriter(sw));
String stacktrace = sw.toString();
logger.error(stacktrace);
}
return org;
}
public List<Movimentacao> pesquisarPorPeriodo(Calendar dataDe, Calendar dataAte) {
if (naoExiste(dataDe)) {
// Primeiro verificamos os campos que podemos usar
return new ArrayList<Movimentacao>();
}
if (naoExiste(dataAte)) {
// Primeiro verificamos os campos que podemos usar
return new ArrayList<Movimentacao>();
}
CriteriaBuilder cb = getEm().getCriteriaBuilder();
CriteriaQuery<Movimentacao> c = cb.createQuery(Movimentacao.class);
Root<Movimentacao> tabelaDeMovimentacao = c.from(Movimentacao.class);
c.select(tabelaDeMovimentacao);
List<Predicate> criteria = new ArrayList<Predicate>();
if (existe(dataDe)) {
ParameterExpression<String> p = cb.parameter(String.class, "data");
criteria.add(cb.greaterThan(tabelaDeMovimentacao.<String>get("data"), p));
}
if (existe(dataAte)) {
ParameterExpression<String> p = cb.parameter(String.class, "data");
criteria.add(cb.lessThan(tabelaDeMovimentacao.<String>get("data"), p));
}
if (criteria.size() == 0) {
throw new RuntimeException("no criteria");
} else if (criteria.size() == 1) {
c.where(criteria.get(0));
} else {
c.where(cb.and(criteria.toArray(new Predicate[0])));
}
TypedQuery<Movimentacao> q = getEm().createQuery(c);
if (existe(dataDe)) {
q.setParameter("data", dataDe);
}
if (existe(dataAte)) {
q.setParameter("data", dataAte);
}
return q.getResultList();
}
protected final <T> ParameterExpression<T> createParameterExpression(
Class<T> paramClass, String name) {
final EntityManager entityManager = this.getEntityManager();
final EntityManagerFactory entityManagerFactory = entityManager.getEntityManagerFactory();
final CriteriaBuilder criteriaBuilder = entityManagerFactory.getCriteriaBuilder();
return criteriaBuilder.parameter(paramClass, name);
}
public Printable getObject(Map<String, Object> params) throws SQLException {
String name = (String) params.get(Constants.GROUPNAME);
try {
CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
CriteriaQuery<? extends Printable> criteriaQuery = criteriaBuilder.createQuery(Group.class);
Root<? extends Printable> root = criteriaQuery.from(Group.class);
ParameterExpression<String> parameters = criteriaBuilder.parameter(String.class);
criteriaQuery.where(criteriaBuilder.equal(root.get("name"), parameters));
TypedQuery<? extends Printable> query = em.createQuery(criteriaQuery);
query.setParameter(parameters, name);
return query.getSingleResult();
} catch (Exception e) {
throw new SQLException("Unable to execute getObject.", e);
}
}
public List<Movimentacao> find(Movimentacao paramMovimentacao) {
Long codigo = null;
String descricao = null;
TipoMovimento tipo = null;
BigDecimal valor = null;
if (existe(paramMovimentacao)) {
// Primeiro verificamos os campos que podemos usar
codigo = paramMovimentacao.getCodigo();
descricao = paramMovimentacao.getDescricao();
tipo = paramMovimentacao.getTipo();
valor = paramMovimentacao.getValor();
}
CriteriaBuilder cb = getEm().getCriteriaBuilder();
CriteriaQuery<Movimentacao> c = cb.createQuery(Movimentacao.class);
Root<Movimentacao> tabelaDeMovimentacao = c.from(Movimentacao.class);
c.select(tabelaDeMovimentacao);
List<Predicate> criteria = new ArrayList<Predicate>();
if (existe(codigo)) {
ParameterExpression<String> p = cb.parameter(String.class, "codigo");
criteria.add(cb.equal(tabelaDeMovimentacao.<String>get("codigo"), p));
}
if (existe(descricao)) {
ParameterExpression<String> p = cb.parameter(String.class, "descricao");
criteria.add(cb.equal(tabelaDeMovimentacao.<String>get("descricao"), p));
}
if (existe(tipo)) {
ParameterExpression<String> p = cb.parameter(String.class, "tipo");
criteria.add(cb.equal(tabelaDeMovimentacao.<String>get("tipo"), p));
}
if (existe(valor)) {
ParameterExpression<String> p = cb.parameter(String.class, "valor");
criteria.add(cb.equal(tabelaDeMovimentacao.<String>get("valor"), p));
}
if (criteria.size() == 0) {
throw new RuntimeException("no criteria");
} else if (criteria.size() == 1) {
c.where(criteria.get(0));
} else {
c.where(cb.and(criteria.toArray(new Predicate[0])));
}
TypedQuery<Movimentacao> q = getEm().createQuery(c);
if (existe(codigo)) {
q.setParameter("codigo", codigo);
}
if (existe(descricao)) {
q.setParameter("descricao", descricao);
}
if (existe(tipo)) {
q.setParameter("tipo", tipo);
}
if (existe(valor)) {
q.setParameter("valor", valor);
}
return q.getResultList();
}
/** This test demonstrates the use of like in where clauses */
@Test
public void testLike() {
log.info("*** testLike() ***");
CriteriaBuilder cb = em.getCriteriaBuilder();
{
CriteriaQuery<Clerk> qdef = cb.createQuery(Clerk.class);
// select c from Clerk c
// where c.firstName like 'M%'
Root<Clerk> c = qdef.from(Clerk.class);
qdef.select(c).where(cb.like(c.<String>get("firstName"), "M%"));
int rows = executeQuery(qdef).size();
assertEquals("unexpected number of rows", 2, rows);
}
{
CriteriaQuery<Clerk> qdef = cb.createQuery(Clerk.class);
// select c from Clerk c
// where c.firstName like :firstName
Root<Clerk> c = qdef.from(Clerk.class);
qdef.select(c)
.where(cb.like(c.<String>get("firstName"), cb.parameter(String.class, "firstName")));
TypedQuery<Clerk> query = em.createQuery(qdef).setParameter("firstName", "M%");
List<Clerk> results = query.getResultList();
for (Object o : results) {
log.info("found result:" + o);
}
assertEquals("unexpected number of rows", 2, results.size());
}
{
CriteriaQuery<Clerk> qdef = cb.createQuery(Clerk.class);
// select c from Clerk c
// where c.firstName like concat(:firstName,'%')
Root<Clerk> c = qdef.from(Clerk.class);
qdef.select(c)
.where(
cb.like(
c.<String>get("firstName"),
cb.concat(cb.parameter(String.class, "firstName"), "%")));
TypedQuery<Clerk> query = em.createQuery(qdef).setParameter("firstName", "M");
List<Clerk> results = query.getResultList();
for (Object o : results) {
log.info("found result:" + o);
}
assertEquals("unexpected number of rows", 2, results.size());
}
{
CriteriaQuery<Clerk> qdef = cb.createQuery(Clerk.class);
// select c from Clerk c
// where c.firstName like '_anny'
Root<Clerk> c = qdef.from(Clerk.class);
qdef.select(c).where(cb.like(c.<String>get("firstName"), "_anny"));
TypedQuery<Clerk> query = em.createQuery(qdef);
List<Clerk> results = query.getResultList();
for (Object o : results) {
log.info("found result:" + o);
}
assertEquals("unexpected number of rows", 1, results.size());
}
}
private List<User> getUsersUsingCriteria(
String fromUserId,
String toUserId,
String userName,
String email,
String qq,
String weibo,
String phoneNumber,
String fromCreationDate,
String toCreationDate)
throws ParseException {
if (QueryUtil.queryParameterProvided(fromUserId)
&& !QueryUtil.queryParameterProvided(toUserId)) {
toUserId = fromUserId;
}
if (QueryUtil.queryParameterProvided(toUserId)
&& !QueryUtil.queryParameterProvided(fromUserId)) {
fromUserId = toUserId;
}
EntityManager em = emf.createEntityManager();
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<User> c = cb.createQuery(User.class);
Root<User> user = c.from(User.class);
c.select(user);
c.orderBy(cb.asc(user.get("userId")));
c.distinct(true);
List<Predicate> criteria = new ArrayList<>();
if (QueryUtil.queryParameterProvided(fromUserId)) {
ParameterExpression<String> p = cb.parameter(String.class, "fromUserId");
criteria.add(cb.greaterThanOrEqualTo(user.<String>get("userId"), p));
}
if (QueryUtil.queryParameterProvided(toUserId)) {
ParameterExpression<String> p = cb.parameter(String.class, "toUserId");
criteria.add(cb.lessThanOrEqualTo(user.<String>get("userId"), p));
}
if (QueryUtil.queryParameterProvided(userName)) {
ParameterExpression<String> p = cb.parameter(String.class, "userName");
if (userName.contains(QueryUtil.WILDCARDS)) {
criteria.add(cb.like(user.<String>get("userName"), p));
} else {
criteria.add(cb.equal(user.get("userName"), p));
}
}
if (QueryUtil.queryParameterProvided(fromCreationDate)) {
ParameterExpression<Calendar> p = cb.parameter(Calendar.class, "fromCreationDate");
criteria.add(cb.greaterThanOrEqualTo(user.<Calendar>get("creationDate"), p));
}
if (QueryUtil.queryParameterProvided(toCreationDate)) {
ParameterExpression<Calendar> p = cb.parameter(Calendar.class, "toCreationDate");
criteria.add(cb.lessThanOrEqualTo(user.<Calendar>get("creationDate"), p));
}
if (QueryUtil.queryParameterProvided(email)) {
ParameterExpression<String> p = cb.parameter(String.class, "email");
if (email.contains(QueryUtil.WILDCARDS)) {
criteria.add(cb.like(user.<String>get("email"), p));
} else {
criteria.add(cb.equal(user.get("email"), p));
}
}
if (QueryUtil.queryParameterProvided(qq)) {
ParameterExpression<String> p = cb.parameter(String.class, "qq");
if (qq.contains(QueryUtil.WILDCARDS)) {
criteria.add(cb.like(user.<String>get("qq"), p));
} else {
criteria.add(cb.equal(user.get("qq"), p));
}
}
if (QueryUtil.queryParameterProvided(weibo)) {
ParameterExpression<String> p = cb.parameter(String.class, "weibo");
if (weibo.contains(QueryUtil.WILDCARDS)) {
criteria.add(cb.like(user.<String>get("weibo"), p));
} else {
criteria.add(cb.equal(user.get("weibo"), p));
}
}
if (QueryUtil.queryParameterProvided(phoneNumber)) {
ParameterExpression<String> p = cb.parameter(String.class, "phoneNumber");
if (phoneNumber.contains(QueryUtil.WILDCARDS)) {
criteria.add(cb.like(user.<String>get("phoneNumber"), p));
} else {
criteria.add(cb.equal(user.get("phoneNumber"), p));
}
}
if (criteria.size() == 1) {
c.where(criteria.get(0));
} else if (criteria.size() > 1) {
c.where(cb.and(criteria.toArray(new Predicate[0])));
}
TypedQuery<User> q = em.createQuery(c);
if (QueryUtil.queryParameterProvided(fromUserId)) {
q.setParameter("fromUserId", fromUserId);
}
if (QueryUtil.queryParameterProvided(toUserId)) {
q.setParameter("toUserId", toUserId);
}
if (QueryUtil.queryParameterProvided(userName)) {
q.setParameter("userName", userName.replace(QueryUtil.WILDCARDS, QueryUtil.PERCENTAGE));
}
if (QueryUtil.queryParameterProvided(email)) {
q.setParameter("email", email.replace(QueryUtil.WILDCARDS, QueryUtil.PERCENTAGE));
}
if (QueryUtil.queryParameterProvided(qq)) {
q.setParameter("qq", qq.replace(QueryUtil.WILDCARDS, QueryUtil.PERCENTAGE));
}
if (QueryUtil.queryParameterProvided(weibo)) {
q.setParameter("weibo", weibo.replace(QueryUtil.WILDCARDS, QueryUtil.PERCENTAGE));
}
if (QueryUtil.queryParameterProvided(phoneNumber)) {
q.setParameter("phoneNumber", phoneNumber.replace(QueryUtil.WILDCARDS, QueryUtil.PERCENTAGE));
}
if (QueryUtil.queryParameterProvided(fromCreationDate)) {
Calendar fromDate = Calendar.getInstance();
fromDate.setTime(new SimpleDateFormat("yyyy-MM-dd").parse(fromCreationDate));
q.setParameter("fromCreationDate", fromDate);
}
if (QueryUtil.queryParameterProvided(toCreationDate)) {
Calendar toDate = Calendar.getInstance();
toDate.setTime(new SimpleDateFormat("yyyy-MM-dd").parse(toCreationDate));
q.setParameter("toCreationDate", toDate);
}
return q.getResultList();
}
