Java 类javax.persistence.criteria.ParameterExpression 实例源码

public Object createQuery(CriteriaQueryWrapper criteriaQueryWrapper) {
    TypedQuery typedQuery = this.original.createQuery(criteriaQueryWrapper.getOriginal());
    QLStatement qlStmt = hqlAnalyzer.getQLSelectStatement(typedQuery);

    int i=0;
    for(Objectref parameter : criteriaQueryWrapper.getSymbolicParameter()) {
        qlStmt.setParameter("param"+i++, parameter);
    }

    for(Object o : criteriaQueryWrapper.getOriginal().getParameters()) {
        if(o instanceof ParameterExpression) {
            ParameterExpression<?> paraExpr = (ParameterExpression<?>)o;
            paraExpr.getAlias();
            paraExpr.getName();

        }
    }
    return qlStmt;
}

/**
 * 对getAllByQuery的单元测试
 */
@Test
public void getAllByQueryTest() {
    Query query = new Query(User.class, entityManager);
    ParameterExpression<Enum> parameter1 = query.createParameter(Enum.class);
    ParameterExpression<String> parameter2 = query.createParameter(String.class);
    List resultList = query.whereEqual("authorityType", parameter1)
            .whereLike("passWord", parameter2)
            .createTypedQuery()
            .setParameter(parameter1, AuthorityType.College_Level_Admin)
            .setParameter(parameter2, "BaseRepository")
            .getResultList();
    if (resultList != null) {
        resultList.forEach(System.out::println);
    }
}

@Test
public void selectAuthorsCriteria() {
    log.info("... selectAuthorsCriteria ...");

    EntityManager em = emf.createEntityManager();
    em.getTransaction().begin();

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Author> cq = cb.createQuery(Author.class);
    Root<Author> root = cq.from(Author.class);
    cq.select(root);
    ParameterExpression<Long> idParam = cb.parameter(Long.class, "id");
    cq.where(cb.equal(root.get("id"), idParam));
    TypedQuery<Author> q = em.createQuery(cq);
    q.setParameter("id", 1L);
    q.setHint("org.hibernate.comment", "This is my comment");
    q.getSingleResult();

    em.getTransaction().commit();
    em.close();
}

/**
 * 对getAllByQuery的单元测试
 */
@Test
public void getAllByQueryTest() {
    Query query = new Query(User.class, entityManager);
    ParameterExpression<Enum> parameter1 = query.createParameter(Enum.class);
    ParameterExpression<String> parameter2 = query.createParameter(String.class);
    List resultList = query.whereEqual("authorityType", parameter1)
            .whereLike("passWord", parameter2)
            .createTypedQuery()
            .setParameter(parameter1, AuthorityType.College_Level_Admin)
            .setParameter(parameter2, "BaseRepository")
            .getResultList();
    if (resultList != null) {
        resultList.forEach(System.out::println);
    }
}

@Test
public void shouldUseTuple(){

            String searchName = "borowiec2";
            CriteriaBuilder cb = em.getCriteriaBuilder();
            //CriteriaQuery<Object[]> c = cb.createQuery(Object[].class); //<4>
            CriteriaQuery<Tuple> criteria = cb.createQuery(Tuple.class); //<1>
            Root<Person> root = criteria.from(Person.class);//<2>
            ParameterExpression<String> lastNameParameter = cb.parameter(String.class,"lastName");
            criteria.multiselect(root.get("firstName"), root.get("version")).where(cb.equal(root.get("lastName"),lastNameParameter));

            List<Tuple> tupleResult = em.createQuery(criteria).setParameter("lastName", searchName).getResultList();//<3>
            for (Tuple t : tupleResult) {
               log.info("fistName : {} , version : {} ",t.get(0),t.get(1));
            }
            Assertions.assertThat(tupleResult.size()).isEqualTo(1);
}

@Test
    public void shouldUseConstruct(){

        String searchName = "borowiec2";
        CriteriaBuilder cb = em.getCriteriaBuilder();
        //CriteriaQuery<Object[]> c = cb.createQuery(Object[].class); //<4>
        CriteriaQuery<PersonWrapper> criteria = cb.createQuery(PersonWrapper.class); //<1>
        Root<Person> root = criteria.from(Person.class);//<2>
        ParameterExpression<String> lastNameParameter = cb.parameter(String.class,"lastName");
        criteria.select(cb.construct(PersonWrapper.class, root.get("firstName"),root.get("version"))).where(cb.equal(root.get("lastName"),lastNameParameter));

        List<PersonWrapper> result = em.createQuery(criteria).setParameter("lastName", searchName).getResultList();//<3>
        for (PersonWrapper pw : result) {
           log.info("pw : {}",pw);
        }

}

private List<Alternative> findAlternativeEntitiesByPollId(Long pollId, boolean all, int maxResults, int firstResult) {
    CriteriaBuilder cb = em.getCriteriaBuilder();

    CriteriaQuery<Alternative> q = cb.createQuery(Alternative.class);
    Root<Alternative> alt = q.from(Alternative.class);
    ParameterExpression<Long> pid = cb.parameter(Long.class);
    q.select(alt).where(cb.equal(alt.get(Alternative_.poll).get(Poll_.id), pid));

    TypedQuery<Alternative> query = em.createQuery(q);
    query.setParameter(pid, pollId);

    if (!all) {
        query.setMaxResults(maxResults);
        query.setFirstResult(firstResult);
    }
    List<Alternative> results = query.getResultList();
    System.out.println(">>>>>>>>>>>>>>>>>>>> Alternatives: " + results);
    return results;
}

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;
}

public Optional<Configuration> findByName(ReadContext ctx, String name) {
    EntityManager em = emf.createEntityManager();
    em.setProperty(PersistenceUnitProperties.MULTITENANT_PROPERTY_DEFAULT, ctx.getTenant());
    em.setProperty("private-key", ctx.getPrivateKey());

    CriteriaBuilder cb = em.getCriteriaBuilder();

    CriteriaQuery<Configuration> q = cb.createQuery(Configuration.class);
    Root<Configuration> c = q.from(Configuration.class);
    ParameterExpression<String> p = cb.parameter(String.class);
    q.select(c).where(cb.equal(c.get("name"), p));

    TypedQuery<Configuration> query = em.createQuery(q);
    query.setParameter(p, name);

    Configuration configuration = query.getSingleResult();

    return Optional.ofNullable(configuration);
}

public Optional<Status> findByName(Status.Name statusName) {
    EntityManager em = emf.createEntityManager();

    CriteriaBuilder cb = em.getCriteriaBuilder();

    CriteriaQuery<Status> q = cb.createQuery(Status.class);
    Root<Status> c = q.from(Status.class);
    ParameterExpression<String> p = cb.parameter(String.class);
    q.select(c).where(cb.equal(c.get("name"), p));

    TypedQuery<Status> query = em.createQuery(q);
    query.setParameter(p, statusName.name());

    try {
        Status status = query.getSingleResult();
        return Optional.of(status);
    } catch (NoResultException e) {
        return Optional.empty();
    }
}

/**
 * @param aEmail
 * @return
 */
public OxUser findPersonByEmail( String aEmail )
{
    CriteriaBuilder cb = myEntityManager.getCriteriaBuilder();

    CriteriaQuery<OxUser> criteria = cb.createQuery(OxUser.class);
    Root<OxUser> root = criteria.from(OxUser.class);
    ParameterExpression<String> mailParam = cb.parameter(String.class);
    criteria.select(root).where(cb.equal( root.get("email"), mailParam ));

    TypedQuery<OxUser> queryEMail = myEntityManager.createQuery(criteria);

    queryEMail.setParameter( mailParam, aEmail );
    List<OxUser> results = queryEMail.getResultList();

    OxUser oxUser = null;

    if ( results.size() > 0 )
    {
        oxUser = results.get( 0 );
    } // if results.size() > 0

    return oxUser;
}

/**
 * @param aUserId
 * @return
 */
public OxUserUnapproved findUnapprovedUser( Long aUserId )
{
    CriteriaBuilder cb = myEntityManager.getCriteriaBuilder();

    CriteriaQuery<OxUserUnapproved> criteria = cb.createQuery(OxUserUnapproved.class);
    Root<OxUserUnapproved> root = criteria.from(OxUserUnapproved.class);
    ParameterExpression<Long> param = cb.parameter(Long.class);
    criteria.select(root).where(cb.equal( root.get("userId"), param ));

    TypedQuery<OxUserUnapproved> queryEMail = myEntityManager.createQuery(criteria);

    queryEMail.setParameter( param, aUserId );
    List<OxUserUnapproved> results = queryEMail.getResultList();

    OxUserUnapproved userUnapproved = null;

    if ( results.size() > 0 )
    {
        userUnapproved = results.get( 0 );
    } // if results.size() > 0

    return userUnapproved;
}

/**
 * SELECT e FROM jpa_query_employee e WHERE e.name= :name
 */
@Transactional
public void doParameterQuery(){
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Employee> c = cb.createQuery(Employee.class);
    Root<Employee> e = c.from(Employee.class);

    // parameter, equal to ":name"
    ParameterExpression<String> paraName = cb.parameter(String.class, "name");

    // e.name = ":name"
    c.select(e)
        .where(cb.equal(e.get("name"), paraName));

    // set param value
    TypedQuery<Employee> query = em.createQuery(c);
    query.setParameter("name", "employee_1");
    List<Employee> result = query.getResultList();
    ResultViewer.showResult(result, "criteria query : parameter query");
}

/**
 * Builds a criteria query equal to the JPQL
 * 
 * <code>SELECT _testEntity FROM TestEntity _testEntity WHERE _testEntity.value :value</code>
 * 
 *
 */
private TypedQuery<TestEntity> buildGetByValue() {
    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();

    CriteriaQuery<TestEntity> criteriaQuery = criteriaBuilder.createQuery(TestEntity.class);
    Root<TestEntity> root = criteriaQuery.from(TestEntity.class);
    ParameterExpression<String> valueParameter = criteriaBuilder.parameter(String.class, TestEntity_.value.getName());

    criteriaQuery.select(root)
         .where(
             criteriaBuilder.equal(
                 root.get(TestEntity_.value), valueParameter)
    );

    return entityManager.createQuery(criteriaQuery);
}

public <C> Query<C> createSelectCustom(JPAQueryCustomiser customiser)
{
    customiser.apply(criteriaBuilder, generated, root, this);

    final Query query = session.createQuery(generated);

    if (offset != null)
        query.getQueryOptions().setFirstRow(offset);
    if (limit != null)
        query.getQueryOptions().setMaxRows(limit);

    // Set all the parameters
    for (Map.Entry<ParameterExpression, Object> entry : this.params.entrySet())
    {
        query.setParameter(entry.getKey(), entry.getValue());
    }

    return query;
}

public Query<T> createSelectEntity()
{
    generated.select(root);

    applyFetches();

    generated.orderBy(orders); // Make sure we return the results in the correct order

    final Query<T> query = session.createQuery(generated);

    if (offset != null)
        query.getQueryOptions().setFirstRow(offset);
    if (limit != null)
        query.getQueryOptions().setMaxRows(limit);

    query.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

    // Set all the parameters
    for (Map.Entry<ParameterExpression, Object> entry : this.params.entrySet())
    {
        query.setParameter(entry.getKey(), entry.getValue());
    }

    return query;
}

public Collection<EndPointCheck> findByDateRange(final EndPoint endPoint, final Date startDate,
        final Date endDate) {
    final CriteriaBuilder criteriaBuilder = getEntityManager().getCriteriaBuilder();
    final CriteriaQuery<EndPointCheck> criteriaQuery = criteriaBuilder.createQuery(getEntityClass());
    final Root<EndPointCheck> root = criteriaQuery
            .from(getEntityManager().getMetamodel().entity(getEntityClass()));

    final ParameterExpression<EndPoint> endPointParameter = criteriaBuilder.parameter(EndPoint.class);
    final ParameterExpression<Date> startDateParameter = criteriaBuilder.parameter(Date.class);
    final ParameterExpression<Date> endDateParameter = criteriaBuilder.parameter(Date.class);

    final Predicate endPointIdPredicate = criteriaBuilder
            .equal(root.get("endPoint"), endPointParameter);

    final Path<Date> checkDatePath = root.<Date> get("checkDate");

    final Predicate startDatePredicate = criteriaBuilder
            .greaterThanOrEqualTo(checkDatePath, startDateParameter);

    final Predicate endDatePredicate = criteriaBuilder.lessThanOrEqualTo(
        checkDatePath,
        endDateParameter);

    criteriaQuery.where(criteriaBuilder.and(endPointIdPredicate, startDatePredicate, endDatePredicate));

    criteriaQuery.orderBy(Arrays.asList(criteriaBuilder.asc(checkDatePath)));

    return getEntityManager().createQuery(criteriaQuery)
            .setParameter(endPointParameter, endPoint)
            .setParameter(startDateParameter, startDate, TemporalType.DATE)
            .setParameter(endDateParameter, endDate, TemporalType.DATE)
            .getResultList();
}

@Test
public void getStatisticsByQueryTest2() {
    Query query = new Query(entityManager);
    ParameterExpression parameter = query.createParameter(String.class);
    query.from(Teacher.class)
            .selectMax("id")
            .whereNotEqual("name", parameter)
            .whereIsNotNull("name")
            .groupBy("passWord")
            .createTypedQuery()
            .setParameter(parameter, "name")
            .getResultList()
            .forEach(System.out::println);
}

@Test
public void getBooks() {
    log.info("... getBooks ...");

    EntityManager em = emf.createEntityManager();
    em.getTransaction().begin();

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Book> cq = cb.createQuery(Book.class);
    Root<Book> root = cq.from(Book.class);
    SetJoin<Book, Author> authors = root.join(Book_.authors);

    ParameterExpression<String> paramFirstName = cb.parameter(String.class);
    ParameterExpression<String> paramLastName = cb.parameter(String.class);
    cq.where(
        cb.and(
            cb.equal(authors.get(Author_.firstName), paramFirstName), 
            cb.equal(authors.get(Author_.lastName), paramLastName)));

    TypedQuery<Book> query = em.createQuery(cq);
    query.setParameter(paramFirstName, "Thorben");
    query.setParameter(paramLastName, "Janssen");
    List<Book> books = query.getResultList();
    Assert.assertEquals(1, books.size());

    for (Book b : books) {
        log.info(b);
    }

    em.getTransaction().commit();
    em.close();
}

@Test
public void callFunction() {
    log.info("... callFunction ...");

    EntityManager em = emf.createEntityManager();
    em.getTransaction().begin();

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Book> cq = cb.createQuery(Book.class);
    Root<Book> root = cq.from(Book.class);

    ParameterExpression<Double> doubleParam1 = cb.parameter(Double.class);
    ParameterExpression<Double> doubleParam2 = cb.parameter(Double.class);
    cq.where(cb.greaterThan(doubleParam2, cb.function("calculate", Double.class, root.get(Book_.price), doubleParam1)));

    TypedQuery<Book> q = em.createQuery(cq);
    q.setParameter(doubleParam1, 10.0D);
    q.setParameter(doubleParam2, 40.0D);
    List<Book> books = q.getResultList();

    for (Book b : books) {
        log.info(b);
    }

    em.getTransaction().commit();
    em.close();
}

@Test
public void getStatisticsByQueryTest2() {
    Query query = new Query(entityManager);
    ParameterExpression parameter = query.createParameter(String.class);
    query.from(Teacher.class)
            .selectMax("id")
            .whereNotEqual("name", parameter)
            .whereIsNotNull("name")
            .groupBy("passWord")
            .createTypedQuery()
            .setParameter(parameter, "name")
            .getResultList()
            .forEach(System.out::println);
}

public List<Employee> findWithDeptName(String deptName) {
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Employee> c = cb.createQuery(Employee.class);
    Root<Employee> emp = c.from(Employee.class);
    c.select(emp);

    ParameterExpression<String> dept = cb.parameter(String.class, "deptName");
    c.where(cb.equal(emp.get("dept").get("name"), dept));

    TypedQuery<Employee> query = em.createQuery(c);
    query.setParameter("deptName", deptName);

    return query.getResultList();
}

@Test
public void shouldUseParameterExpressionToFindName(){

            String searchName = "borowiec2";
            CriteriaBuilder cb = em.getCriteriaBuilder();
            CriteriaQuery<String> criteria = cb.createQuery(String.class); //<1>
            Root<Person> root = criteria.from(Person.class);//<2>
            ParameterExpression<String> lastNameParameter = cb.parameter(String.class,"lastName"); //<3>
            criteria.select(root.get("firstName")).where(cb.equal(root.get("lastName"),lastNameParameter)); //<4>
            List<String> result  = em.createQuery(criteria).setParameter("lastName", searchName).getResultList(); //<5>
            log.info("{}",result);


}

@Test
public void shouldUseParameterExpressionToFindLikeName(){

            String searchName = "borowie%";
            CriteriaBuilder cb = em.getCriteriaBuilder();
            CriteriaQuery<String> criteria = cb.createQuery(String.class);
            Root<Person> root = criteria.from(Person.class);
            ParameterExpression<String> lastNameParameter = cb.parameter(String.class,"lastName");
            criteria.select(root.get("firstName")).where(cb.like(root.get("lastName"),lastNameParameter));
            criteria.orderBy(cb.desc(root.get("firstName")));
            List<String> result  = em.createQuery(criteria).setParameter("lastName", searchName).getResultList();
            log.info("{}",result);


}

@Test
public void shouldFindLikeLowerName(){

            String searchName = "borowie%";
            CriteriaBuilder cb = em.getCriteriaBuilder();
            CriteriaQuery<String> criteria = cb.createQuery(String.class);
            Root<Person> root = criteria.from(Person.class);
            ParameterExpression<String> lastNameParameter = cb.parameter(String.class,"lastName");
            criteria.select(root.get("firstName")).where(cb.like(cb.lower(root.get("lastName")),lastNameParameter));
            criteria.orderBy(cb.desc(root.get("firstName")));
            List<String> result  = em.createQuery(criteria).setParameter("lastName", searchName).getResultList();
            log.info("{}",result);


}

/**
 * Creates a TypedQuery which can be further customized by calling its methods such as setMaxResults() or setFirstResult.
 * To get results, call its getResultList() or getSingleResult() method.
 * Method is private, so it cannot be overridden - it's used by other methods.
 */
private TypedQuery<T> createTypedQuery(QueryBuilder<T> queryBuilder) {
    CriteriaBuilder cb = em().getCriteriaBuilder();
    CriteriaQuery<T> q = cb.createQuery(entityClass);
    Root<T> root = q.from(entityClass);
    ParameterExpression<Integer> p = cb.parameter(Integer.class);
    CriteriaQuery<T> criteriaQuery = q.select(root);
    criteriaQuery = queryBuilder.build(cb, root, criteriaQuery);
    TypedQuery<T> typedQuery = em.createQuery(criteriaQuery);
    return typedQuery;
}

public Set<ParameterExpression<?>> collectParameters() {
        final Set<ParameterExpression<?>> parameters = new LinkedHashSet<ParameterExpression<?>>();
//      final ParameterRegistry registry = new ParameterRegistry() {
//          public void registerParameter(ParameterExpression<?> parameter) {
//              parameters.add( parameter );
//          }
//      };
//
//      ParameterContainer.Helper.possibleParameter(selection, registry);
//      ParameterContainer.Helper.possibleParameter(restriction, registry);
//      ParameterContainer.Helper.possibleParameter(having, registry);
//      if ( subqueries != null ) {
//          for ( Subquery subquery : subqueries ) {
//              ParameterContainer.Helper.possibleParameter(subquery, registry);
//          }
//      }
//
//      // both group-by and having expressions can (though unlikely) contain parameters...
//      ParameterContainer.Helper.possibleParameter(having, registry);
//      if ( groupings != null ) {
//          for ( Expression<?> grouping : groupings ) {
//              ParameterContainer.Helper.possibleParameter(grouping, registry);
//          }
//      }
//
        return parameters;
    }

/**
 * Checks if the given hash is present in the ow_base_avatar table
 *
 * @param aHash
 * @return
 */
public boolean isAvatarHashPresent( Long aHash )
{
    if ( myDryRun )
    {
        myLog.debug( "DryRun is active. Not checking for duplicate avatar hash" );
        return false;
    } // if myDryRun

    CriteriaBuilder cb = myEntityManager.getCriteriaBuilder();

    CriteriaQuery<OxAvatar> criteria = cb.createQuery(OxAvatar.class);
    Root<OxAvatar> root = criteria.from(OxAvatar.class);
    ParameterExpression<Long> avatarParam = cb.parameter(Long.class);
    avatarParam = cb.parameter(Long.class);
    criteria.select(root).where(cb.equal( root.get("hash"), avatarParam ));
    TypedQuery<OxAvatar> query = myEntityManager.createQuery(criteria);
    query.setParameter( avatarParam, aHash );
    List<OxAvatar> results = query.getResultList();

    if ( results.size() > 0 )
    {
        myLog.debug( "given avatar hash is already present: " + aHash );
        return true;
    } // if results.size() > 0

    return false;
}

public Query createSelectIDs()
{
    this.generated.distinct(true);

    generated.orderBy(orders); // Make sure we return the results in the correct order

    List<Selection<?>> selects = new ArrayList<>();

    if (root.getModel().hasSingleIdAttribute())
    {
        selects.add(root.get(root.getModel().getId(root.getModel().getIdType().getJavaType())));
    }
    else
        throw new NotImplementedException("Cannot handle ID selection with IdClass!");

    for (Order order : orders)
    {
        selects.add(order.getExpression());
    }

    if (selects.size() == 1)
        generated.select(selects.get(0));
    else
        generated.multiselect(selects);

    final Query query = session.createQuery(generated);

    if (offset != null)
        query.getQueryOptions().setFirstRow(offset);
    if (limit != null)
        query.getQueryOptions().setMaxRows(limit);

    // Set all the parameters
    for (Map.Entry<ParameterExpression, Object> entry : this.params.entrySet())
    {
        query.setParameter(entry.getKey(), entry.getValue());
    }

    return query;
}

@BeforeMethod
public void setup() {
    builder = mock(CriteriaBuilder.class);
    path = mock(Path.class);
    when(path.getJavaType()).thenReturn(String.class);
    expression = mock(ParameterExpression.class);
    root = mock(Root.class);
    query = mock(Query.class);
    Parameter param = mock(Parameter.class);
    when(param.getParameterType()).thenReturn(String.class);
    when(query.getParameter(anyString())).thenReturn(param);

}

@Override
public Set<ParameterExpression<?>> getParameters() {
    // TODO Auto-generated method stub
    return null;
}

@Override
public <T> ParameterExpression<T> parameter(Class<T> paramClass) {
    // TODO Auto-generated method stub
    return null;
}

@Override
public <T> ParameterExpression<T> parameter(Class<T> paramClass,
        String name) {
    // TODO Auto-generated method stub
    return null;
}

/**
 * {@inheritDoc}
 */
@Override
public Map<String, Double> getActiveColumnsByToTV(String columnName, Interval interval,
                                                  int resultSize, boolean withBots) {

    EntityManager entityManager = entityManagerFactory.createEntityManager();
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<Tuple> query = cb.createTupleQuery();

    Root<T> from = query.from(type);

    ParameterExpression<DateTime> startDateParam = cb.parameter(DateTime.class);
    ParameterExpression<DateTime> endDateParam = cb.parameter(DateTime.class);
    Expression<Double> occurrences = from.get("occurrences").as(Double.class);

    // create total query
    Subquery<Long> totalQuery = query.subquery(Long.class);
    Root<T> totalFrom = totalQuery.from(type);
    totalQuery.select(cb.sum(totalFrom.get("occurrences")));
    Path<DateTime> totalMentionTime = totalFrom.get("mentionTime");

    List<Predicate> wherePredicates = Lists.newArrayListWithCapacity(3);
    wherePredicates.add(cb.greaterThanOrEqualTo(totalMentionTime, startDateParam));
    wherePredicates.add(cb.lessThan(totalMentionTime, endDateParam));
    if (!withBots) {
        wherePredicates.add(cb.equal(totalFrom.get("bot"), withBots));
    }
    totalQuery.where(wherePredicates.toArray(new Predicate[wherePredicates.size()]));

    // occurrences / total occurrences
    Expression<Double> ratio = cb.quot(cb.sum(occurrences), totalQuery).as(Double.class);

    Path<String> columnPath = from.get(columnName);
    query.multiselect(columnPath, ratio);
    Path<DateTime> mentionTime = from.get("mentionTime");

    wherePredicates = Lists.newArrayListWithCapacity(4);
    wherePredicates.add(cb.greaterThanOrEqualTo(mentionTime, startDateParam));
    wherePredicates.add(cb.lessThan(mentionTime, endDateParam));
    wherePredicates.add(cb.isNotNull(columnPath));
    if (!withBots) {
        wherePredicates.add(cb.equal(from.get("bot"), withBots));
    }

    query.where(wherePredicates.toArray(new Predicate[wherePredicates.size()]));
    query.groupBy(columnPath);
    query.orderBy(cb.desc(ratio));

    try {
        List<Tuple> resultList = entityManager.createQuery(query)
                                              .setMaxResults(resultSize)
                                              .setParameter(startDateParam, interval.getStart())
                                              .setParameter(endDateParam, interval.getEnd())
                                              .getResultList();
        // linked hashmap to preserve order
        Map<String, Double> result = Maps.newLinkedHashMap();
        for (Tuple tuple : resultList) {
            result.put(tuple.get(columnPath), tuple.get(ratio));
        }
        return result;
    } finally {
        closeEntityManager(entityManager);
    }
}

/**
 * {@inheritDoc}
 */
@Override
public Map<String, Double> getActiveColumnsByToMV(String columnName, Interval interval,
                                                  int resultSize, boolean withBots) {

    EntityManager entityManager = entityManagerFactory.createEntityManager();
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<Tuple> query = cb.createTupleQuery();

    Root<T> from = query.from(type);

    ParameterExpression<DateTime> startDateParam = cb.parameter(DateTime.class);
    ParameterExpression<DateTime> endDateParam = cb.parameter(DateTime.class);
    Expression<Double> occurrences = from.get("occurrences").as(Double.class);

    // create total query
    Subquery<Long> totalQuery = query.subquery(Long.class);
    Root<MessageSummary> totalFrom = totalQuery.from(MessageSummary.class);
    totalQuery.select(cb.sum(totalFrom.get("occurrences")));
    Path<DateTime> totalMentionTime = totalFrom.get("mentionTime");
    Path<MessageType> messageType = totalFrom.get("value");

    List<Predicate> wherePredicates = Lists.newArrayListWithCapacity(4);
    wherePredicates.add(cb.greaterThanOrEqualTo(totalMentionTime, startDateParam));
    wherePredicates.add(cb.lessThan(totalMentionTime, endDateParam));
    wherePredicates.add(cb.equal(messageType, MessageType.MESSAGE));
    if (!withBots) {
        wherePredicates.add(cb.equal(totalFrom.get("bot"), withBots));
    }
    totalQuery.where(wherePredicates.toArray(new Predicate[wherePredicates.size()]));

    // occurrences / total occurrences
    Expression<Double> ratio = cb.quot(cb.sum(occurrences), totalQuery).as(Double.class);
    Path<String> columnPath = from.get(columnName);

    query.multiselect(columnPath, ratio);
    Path<DateTime> mentionTime = from.get("mentionTime");

    wherePredicates = Lists.newArrayListWithCapacity(4);
    wherePredicates.add(cb.greaterThanOrEqualTo(mentionTime, startDateParam));
    wherePredicates.add(cb.lessThan(mentionTime, endDateParam));
    wherePredicates.add(cb.isNotNull(columnPath));
    if (!withBots) {
        wherePredicates.add(cb.equal(from.get("bot"), withBots));
    }
    query.where(wherePredicates.toArray(new Predicate[wherePredicates.size()]));

    query.groupBy(columnPath);
    query.orderBy(cb.desc(ratio));

    try {
        List<Tuple> resultList = entityManager.createQuery(query)
                                              .setMaxResults(resultSize)
                                              .setParameter(startDateParam, interval.getStart())
                                              .setParameter(endDateParam, interval.getEnd())
                                              .getResultList();

        // linked hashmap to preserve order
        Map<String, Double> result = Maps.newLinkedHashMap();
        for (Tuple tuple : resultList) {
            result.put(tuple.get(columnPath), tuple.get(ratio));
        }
        return result;
    } finally {
        closeEntityManager(entityManager);
    }
}

/**
 *
 * @param carroId
 * @return
 */
public Carro buscarPorId(long carroId) {

    final CriteriaBuilder builder = this.getBuilder();

    final CriteriaQuery<Carro> criteria = builder.createQuery(Carro.class);
    final Root<Carro> root = criteria.from(Carro.class);

    final ParameterExpression<Long> parameter = builder.parameter(Long.class);

    criteria.select(root)
            .where(builder.equal(root.get("id"), parameter));

    final TypedQuery<Carro> query = this.entityManager.createQuery(criteria);

    query.setParameter(parameter, carroId);

    return query.getSingleResult();
}

/**
 *
 * @param proprietarioId
 * @return
 */
public Proprietario buscarPorId(long proprietarioId) {

    final CriteriaBuilder builder = this.getBuilder();

    final CriteriaQuery<Proprietario> criteria = builder.createQuery(Proprietario.class);
    final Root<Proprietario> root = criteria.from(Proprietario.class);

    final ParameterExpression<Long> parameter = builder.parameter(Long.class);

    criteria.select(root)
            .where(builder.equal(root.get("id"), parameter));

    final TypedQuery<Proprietario> query = this.entityManager.createQuery(criteria);

    query.setParameter(parameter, proprietarioId);

    return query.getSingleResult();
}

@Override
public Set<ParameterExpression<?>> getParameters() {
    return queryStructure.collectParameters();
}

@Override
public <T> ParameterExpression<T> parameter(Class<T> paramClass) {
    final BasicTypeImpl sqmType = (BasicTypeImpl) consumerContext().getDomainMetamodel().resolveBasicType( paramClass );
    return new ParameterExpressionImpl<T>( this, sqmType, paramClass );
}

@Override
public <T> ParameterExpression<T> parameter(Class<T> paramClass, String name) {
    final BasicTypeImpl sqmType = (BasicTypeImpl) consumerContext().getDomainMetamodel().resolveBasicType( paramClass );
    return new ParameterExpressionImpl<T>( this, sqmType, paramClass, name );
}