org.hibernate.transform.Transformers Java Examples

public <S> List<S> findBySql(StringQuery stringQuery, Class<S> transformer) {
    Assert.notNull(stringQuery, "StringQuery must not be null!");

    String sql = stringQuery.getQuery();
    NamedParams params = stringQuery.getParams();

    Assert.notNull(params, "NamedParams must not be null!");

    Query query = em.createNativeQuery(sql);
    setQueryParams(query, params);

    NativeQuery sqlQuery = query.unwrap(NativeQuery.class);
    stringQuery.getScalars().forEach((s, type) -> {
        sqlQuery.addScalar(s, type);
    });

    return sqlQuery.setResultTransformer(Transformers.aliasToBean(transformer)).list();
}
 

@Test
public void testGroupConcatJPQLQuery() {
    doInJPA(entityManager -> {
        List<PostSummaryDTO> postSummaries = entityManager.createQuery(
            "select " +
            "   p.id as id, " +
            "   p.title as title, " +
            "   group_concat(t.name) as tags " +
            "from Post p " +
            "left join p.tags t " +
            "group by p.id, p.title")
        .unwrap(Query.class)
        .setResultTransformer(Transformers.aliasToBean(PostSummaryDTO.class))
        .getResultList();

        assertEquals(1, postSummaries.size());
        LOGGER.info("Post tags: {}", postSummaries.get(0).getTags());
    });
}
 

public <N extends Object>Pager<N> findBySql(String sql, Object[] args,
		Map<String, Object> alias, Class<?> clz, boolean hasEntity) {
	sql = initSort(sql);
	String cq = getCountHql(sql,false);
	SQLQuery sq = getSession().createSQLQuery(sql);
	SQLQuery cquery = getSession().createSQLQuery(cq);
	setAliasParameter(sq, alias);
	setAliasParameter(cquery, alias);
	setParameter(sq, args);
	setParameter(cquery, args);
	Pager<N> pages = new Pager<N>();
	setPagers(sq, pages);
	if(hasEntity) {
		sq.addEntity(clz);
	} else {
		sq.setResultTransformer(Transformers.aliasToBean(clz));
	}
	List<N> datas = sq.list();
	pages.setDatas(datas);
	long total = ((BigInteger)cquery.uniqueResult()).longValue();
	pages.setTotal(total);
	return pages;
}
 

/**
	 * 传入查询的原生SQL语句
	 * @param sqlQuery 原生SQL语句
	 * @return List<Map<String,String>>
	 */
	public List<Map<String,Object>> findMapBySqlQuery(String sqlQuery){
//		try {
//			Query queryObject = getCurrentSession().createSQLQuery(sqlQuery).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
//			return queryObject.list();
//		} catch (RuntimeException re) {
//			throw re;
//		}
		
		javax.persistence.Query query = entityManager.createNativeQuery(sqlQuery);
//		query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		query.unwrap(Query.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<Map<String,Object>> list= query.getResultList();
        entityManager.close();
        return list;
	}
 

public <N extends Object> List<N> listBySql(String sql, Object[] args, Map<String, Object> alias, Class<?> clz,
        boolean hasEntiry) {
    sql = initSort(sql);
    SQLQuery sq = getSession().createSQLQuery(sql);
    setAliasParameter(sq, alias);
    setParameter(sq, args);
    if (hasEntiry) {

        sq.addEntity(clz);

    } else {

        sq.setResultTransformer(Transformers.aliasToBean(clz));
    }

    return sq.list();
}
 

@Override
public List<Threshold> listThreshold() {
    List<SbiKpiThreshold> sbiLst = list(new ICriterion<SbiKpiThreshold>() {
        @Override
        public Criteria evaluate(Session session) {
            return session
                    .createCriteria(SbiKpiThreshold.class)
                    .setProjection(
                            Projections.projectionList().add(Projections.property("id"), "id").add(Projections.property("name"), "name")
                                    .add(Projections.property("description"), "description"))
                    .setResultTransformer(Transformers.aliasToBean(SbiKpiThreshold.class));
        }
    });
    List<Threshold> thresholds = new ArrayList<>();
    for (SbiKpiThreshold sbiThreshold : sbiLst) {
        thresholds.add(from(sbiThreshold, false));
    }
    return thresholds;
}
 

@Test
@Ignore
public void testGroupConcatJPQLQuery() {
    doInJPA(entityManager -> {
        List<PostSummaryDTO> postSummaries = entityManager.createQuery(
            "select " +
            "   p.id as id, " +
            "   p.title as title, " +
            "   group_concat(t.name) as tags " +
            "from Post p " +
            "left join p.tags t " +
            "group by p.id, p.title")
        .unwrap(Query.class)
        .setResultTransformer(Transformers.aliasToBean(PostSummaryDTO.class))
        .getResultList();

        assertEquals(1, postSummaries.size());
        LOGGER.info("Post tags: {}", postSummaries.get(0).getTags());
    });
}
 

public <S> Page<S> find(String queryString, String queryCount, NamedParams params, Pageable pageable, Class<S> transformer) {
    Assert.hasText(queryString, "Query must has text!");
    Assert.hasText(queryCount, "Query count must has text!");
    Assert.notNull(params, "QueryParams must not be null!");
    Assert.notNull(pageable, "PageRequest must not be null!");
    Assert.notNull(transformer, "TransformerClass must not be null!");

    Query query = em.createQuery(queryString);

    setQueryParams(query, params);
    query.setMaxResults(pageable.getPageSize());
    query.setFirstResult((int) pageable.getOffset());

    List<S> resultList = query.unwrap(QueryImpl.class).setResultTransformer(Transformers.aliasToBean(transformer)).list();

    Query countQuery = em.createQuery(queryCount);
    setQueryParams(countQuery, params);
    Long total = (Long) countQuery.getSingleResult();

    Page<S> page = new PageImpl(resultList, pageable, total);

    return page;
}
 

@Test
@Ignore
public void testGroupConcatJPQLQuery() {
    doInJPA(entityManager -> {
        List<PostSummaryDTO> postSummaries = entityManager.createQuery(
            "select " +
            "   p.id as id, " +
            "   p.title as title, " +
            "   group_concat(t.name) as tags " +
            "from Post p " +
            "left join p.tags t " +
            "group by p.id, p.title")
        .unwrap(Query.class)
        .setResultTransformer(Transformers.aliasToBean(PostSummaryDTO.class))
        .getResultList();

        assertEquals(1, postSummaries.size());
        LOGGER.info("Post tags: {}", postSummaries.get(0).getTags());
    });
}
 

@Test
public void testGroupConcatJPQLQuery() {
    doInJPA(entityManager -> {
        List<PostSummaryDTO> postSummaries = entityManager.createQuery(
            "select " +
            "   p.id as id, " +
            "   p.title as title, " +
            "   group_concat(t.name) as tags " +
            "from Post p " +
            "left join p.tags t " +
            "group by p.id, p.title")
        .unwrap(Query.class)
        .setResultTransformer(Transformers.aliasToBean(PostSummaryDTO.class))
        .getResultList();

        assertEquals(1, postSummaries.size());
        LOGGER.info("Post tags: {}", postSummaries.get(0).getTags());
    });
}
 

@SuppressWarnings("rawtypes")
private Map getJobDateRange(Session session, boolean ignoreTestAccounts) {
	//Get min and max values of dates
	Criteria rangeCriteria = session.createCriteria(JobLogEntry.class);
	testAccountFilter.addCriteriaForTestAccounts(session, ignoreTestAccounts, rangeCriteria);
	ProjectionList projections = Projections.projectionList();
	projections.add(Projections.min(JobLogContainer.START_TIME), "min");
	projections.add(Projections.max(JobLogContainer.START_TIME), "max");
	rangeCriteria.setProjection(projections);
	rangeCriteria.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);

	@SuppressWarnings("unchecked")
	List<Map> rangeList = rangeCriteria.list();

	return rangeList.get(0);
}
 

@SuppressWarnings("unchecked")
public <T> T queryUniqueResult(String queryString, QueryWrapper queryWrapper, Class<T> resultClass, List<ResultType> returnTypeList) {
	Session session = sessionBox.getCurrentSession();
	Query query = createSQLQuery(session, queryString);
	setParameter(query, queryWrapper);
	setScalar(query, returnTypeList);

	if (resultClass != null && !this.isPrimitive(resultClass) && !this.isString(resultClass)) {
		if (null == returnTypeList || returnTypeList.isEmpty()) {
			query.setResultTransformer(new ResultToBean(resultClass));
		} else {
			query.setResultTransformer(Transformers.aliasToBean(resultClass));
		}
	}
	if (null == resultClass) {
		query.setResultTransformer(new ResultToBean(resultClass));
	}
	Object value = query.uniqueResult();
	if (value instanceof BigInteger) {
		if (Long.class == resultClass) {
			value = ((BigInteger) value).longValue();
		}
	}
	return (T) value;
}
 

@Override
public List loadOrganizzationEngines(final String tenant) {

	List orgEngs = list(new ICriterion() {
		@Override
		public Criteria evaluate(Session session) {
			return session
					.createCriteria(SbiEngines.class)
					.createAlias("sbiProductTypeEngine", "_sbiProductTypeEngine")
					.createAlias("_sbiProductTypeEngine.sbiProductType", "_sbiProductType")
					.createAlias("_sbiProductType.sbiOrganizationProductType", "_sbiOrganizationProductType")
					.createAlias("_sbiOrganizationProductType.sbiOrganizations", "_sbiOrganizations")
					.add(Restrictions.eq("_sbiOrganizations.name", tenant))
					.setProjection(
							Projections.projectionList().add(org.hibernate.criterion.Property.forName("label").as("engineLabel"))
									.add(org.hibernate.criterion.Property.forName("_sbiProductType.label").as("productTypeLabel")))
					.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		}
	});
	return orgEngs;
}
 

/**
	 * {@inheritDoc}
	 */
@Override
public List<UserProfile> getUserProfiles(final int start, final int count) {
	
	//get fields directly from the sakaiperson table and use Transformers.aliasToBean to transform into UserProfile pojo
	//the idea is we *dont* want a SakaiPerson object
	final HibernateCallback<List<UserProfile>> hcb = session -> {
           final Query q = session.getNamedQuery(QUERY_GET_SAKAI_PERSON);
           //see scalars in the hbm
           q.setFirstResult(start);
           q.setMaxResults(count);
           q.setResultTransformer(Transformers.aliasToBean(UserProfile.class));
           q.setCacheMode(CacheMode.GET);
           return q.list();
       };
  	
  	return getHibernateTemplate().execute(hcb);
}
 

/**
 * CommonDAO 内部方法, NativeQuery
 * 
 * @param entityManager
 *            entityManager
 * @param sql
 *            sql
 * @param values
 *            values
 * @return Query对象
 */
private Query createNativeQuery(EntityManager entityManager, String sql, Class resultClass, boolean mapResult,
		Map<String, Object> values) {
	Query query = null;
	if (resultClass != null) {
		query = entityManager.createNativeQuery(sql, resultClass);
	} else {
		query = entityManager.createNativeQuery(sql);
	}
	// 封装为Map结果
	if (mapResult) {
		query.unwrap(org.hibernate.Query.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
	}

	if (values != null && values.size() > 0) {
		for (Entry<String, Object> e : values.entrySet()) {
			query.setParameter(e.getKey(), e.getValue());
		}
	}
	return query;
}
 

/**
 * CommonDAO 内部方法, NativeQuery
 * 
 * @param entityManager
 *            entityManager
 * @param sql
 *            sql
 * @param values
 *            values
 * @return Query对象
 */
private Query createNativeQuery(EntityManager entityManager, String sql, Class resultClass, boolean mapResult,
		Map<String, Object> values) {
	Query query = null;
	if (resultClass != null) {
		query = entityManager.createNativeQuery(sql, resultClass);
	} else {
		query = entityManager.createNativeQuery(sql);
	}
	// 封装为Map结果
	if (mapResult) {
		query.unwrap(org.hibernate.Query.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
	}

	if (values != null && values.size() > 0) {
		for (Entry<String, Object> e : values.entrySet()) {
			query.setParameter(e.getKey(), e.getValue());
		}
	}
	return query;
}
 

/**
 * CommonDAO 内部方法, NativeQuery
 * 
 * @param entityManager
 *            entityManager
 * @param sql
 *            sql
 * @param values
 *            values
 * @return Query对象
 */
private Query createNativeQuery(EntityManager entityManager, String sql, Class resultClass, boolean mapResult,
		Map<String, Object> values) {
	Query query = null;
	if (resultClass != null) {
		query = entityManager.createNativeQuery(sql, resultClass);
	} else {
		query = entityManager.createNativeQuery(sql);
	}
	// 封装为Map结果
	if (mapResult) {
		query.unwrap(org.hibernate.Query.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
	}

	if (values != null && values.size() > 0) {
		for (Entry<String, Object> e : values.entrySet()) {
			query.setParameter(e.getKey(), e.getValue());
		}
	}
	return query;
}
 

void run() {
    var db = setup().openStatelessSession();

    var sqlString = "SELECT first_name as firstName, last_name as lastName, count(*) films\n" +
            "FROM actor AS a\n" +
            "JOIN film_actor AS fa USING (actor_id)\n" +
            "GROUP BY a.actor_id, first_name, last_name\n" +
            "ORDER BY films DESC\n" +
            "LIMIT 10";


    db.createNativeQuery(sqlString)
    .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).stream().forEach(o -> out.println(o));

    //need other public classes which jbang can't compile/include at the moment.
    //db.createNativeQuery(sqlString).addScalar("firstName").addScalar("lastName").addScalar("films")
    //        .setResultTransformer(Transformers.aliasToBean(DTO.class)).stream().forEach(o -> out.println(o));

}
 

@Override
public List<String> listPlaceholderByMeasures(final List<String> measures) {
    List<SbiKpiPlaceholder> lst = list(new ICriterion<SbiKpiPlaceholder>() {
        @Override
        public Criteria evaluate(Session session) {
            DetachedCriteria detachedCriteria = DetachedCriteria.forClass(SbiKpiRuleOutput.class).createAlias("sbiKpiRule", "sbiKpiRule")
                    .createAlias("sbiKpiAlias", "sbiKpiAlias").setProjection(Property.forName("sbiKpiRule.sbiKpiRuleId.id"))
                    .add(Restrictions.eq("sbiKpiRule.active", 'T')).add(Restrictions.in("sbiKpiAlias.name", measures));

            Criteria c = session.createCriteria(SbiKpiRule.class).createAlias("sbiKpiPlaceholders", "sbiKpiPlaceholders")
                    .add(Property.forName("sbiKpiRuleId.id").in(detachedCriteria)).add(Restrictions.eq("active", 'T'))
                    .setProjection(Projections.distinct(Projections.property("sbiKpiPlaceholders.name").as("name")))
                    .setResultTransformer(Transformers.aliasToBean(SbiKpiPlaceholder.class));
            return c;
        }
    });
    List<String> placeholdername = new ArrayList<>();
    for (SbiKpiPlaceholder sbiKpiPlaceholder : lst) {
        placeholdername.add(sbiKpiPlaceholder.getName());
    }
    return placeholdername;
}
 

/**
	 * {@inheritDoc}
	 */
@Override
public List<UserProfile> getUserProfiles(final int start, final int count) {
	
	//get fields directly from the sakaiperson table and use Transformers.aliasToBean to transform into UserProfile pojo
	//the idea is we *dont* want a SakaiPerson object
	final HibernateCallback<List<UserProfile>> hcb = session -> {
           final Query q = session.getNamedQuery(QUERY_GET_SAKAI_PERSON);
           //see scalars in the hbm
           q.setFirstResult(start);
           q.setMaxResults(count);
           q.setResultTransformer(Transformers.aliasToBean(UserProfile.class));
           q.setCacheMode(CacheMode.GET);
           return q.list();
       };
  	
  	return getHibernateTemplate().execute(hcb);
}
 

@Override
public Criteria evaluate(Session session) {
	Criteria criteria = session.createCriteria(SbiGlGlossary.class);
	criteria.setProjection(
			Projections.projectionList().add(Projections.property("glossaryId"), "glossaryId").add(Projections.property("glossaryNm"), "glossaryNm"))
			.setResultTransformer(Transformers.aliasToBean(SbiGlGlossary.class));
	if (glossary != null && !glossary.isEmpty()) {
		criteria.add(Restrictions.like("glossaryNm", glossary, MatchMode.ANYWHERE).ignoreCase());
	}

	if (page != null && itemsPerPage != null) {
		criteria.setFirstResult((page - 1) * itemsPerPage);
		criteria.setMaxResults(itemsPerPage);
	}

	return criteria;
}
 

@Test
public void testFetchObjectArrayToDTO() {
    doInJPA(entityManager -> {
        CriteriaBuilder builder = entityManager.getCriteriaBuilder();

        CriteriaQuery<Object[]> query = builder.createQuery(Object[].class);

        Root<PostComment> postComment = query.from(PostComment.class);
        Join<PostComment, Post> post = postComment.join(PostComment_.post);

        query.multiselect(
            postComment.get(PostComment_.id).alias(PostComment_.ID),
            postComment.get(PostComment_.review).alias(PostComment_.REVIEW),
            post.get(Post_.title).alias(Post_.TITLE)
        );

        query.where(
            builder.and(
                builder.like(
                    post.get(Post_.title),
                    "%Java Persistence%"
                ),
                builder.equal(
                    post.get(Post_.details).get(PostDetails_.CREATED_BY),
                    "Vlad Mihalcea"
                )
            )
        );

        List<PostCommentSummary> comments = entityManager
            .createQuery(query)
            .unwrap(Query.class)
            .setResultTransformer(Transformers.aliasToBean(PostCommentSummary.class))
            .getResultList();

        assertEquals(5, comments.size());
    });
}
 

@Override
public Criteria evaluate(Session session) {
	Criteria c = session.createCriteria(SbiGlWord.class);
	c.addOrder(Order.asc("word"));
	c.setProjection(Projections.projectionList().add(Projections.property("wordId"), "wordId").add(Projections.property("word"), "word"))
			.setResultTransformer(Transformers.aliasToBean(SbiGlWord.class));
	
	if(page!=null && item_per_page!=null ){
	c.setFirstResult((page - 1) * item_per_page);
     c.setMaxResults(item_per_page);
	}
	
	return c;
}
 

public List<Service> findServiceByGroups() {
    Criteria criteria = currentSession().createCriteria(Service.class);
    criteria.setProjection(
            Projections.projectionList()
                    .add(Projections.property("group"), "group")
                    .add(Projections.groupProperty("group")))
            .setResultTransformer(Transformers.aliasToBean(Service.class));

    return list(criteria);
}
 

public static ResultTransformer createSelectNewTransformer(Constructor constructor, boolean returnMaps, boolean returnLists) {
	if ( constructor != null ) {
		return new AliasToBeanConstructorResultTransformer(constructor);
	}
	else if ( returnMaps ) {
		return Transformers.ALIAS_TO_ENTITY_MAP;			
	}
	else if ( returnLists ) {
		return Transformers.TO_LIST;
	}		
	else {
		return null;
	}
}
 

public <N extends Object> Pager<N> findBySql(String sql, Object[] args, Map<String, Object> alias, Class<?> clz,
        boolean hasEntiry) {

    sql = initSort(sql);
    String cq = getCountHql(sql, false);
    // cq=initSort(cq);不需要再加上就多了一个Order

    SQLQuery sq = getSession().createSQLQuery(sql);
    SQLQuery cquery = getSession().createSQLQuery(cq);
    setAliasParameter(sq, alias);
    setAliasParameter(cquery, alias);
    setParameter(sq, args);
    setParameter(cquery, args);
    Pager<N> pages = new Pager<N>();
    setPagers(sq, pages);
    if (hasEntiry) {

        sq.addEntity(clz);
    } else {
        sq.setResultTransformer(Transformers.aliasToBean(clz));
    }
    List<N> datas = sq.list();
    pages.setDatas(datas);
    long total = ((BigInteger) cquery.uniqueResult()).longValue();
    pages.setTotal(total);

    return pages;

}
 

public <N extends Object>List<N> listBySql(String sql, Object[] args,
		Map<String, Object> alias, Class<?> clz, boolean hasEntity) {
	sql = initSort(sql);
	SQLQuery sq = getSession().createSQLQuery(sql);
	setAliasParameter(sq, alias);
	setParameter(sq, args);
	if(hasEntity) {
		sq.addEntity(clz);
	} else 
		sq.setResultTransformer(Transformers.aliasToBean(clz));
	return sq.list();
}
 

public static <C> Query transform(Query query, Class<C> clazz) {
    ResultTransformer transformer;
    if (Map.class.isAssignableFrom(clazz)) {
        transformer = Transformers.ALIAS_TO_ENTITY_MAP;
    } else if (Number.class.isAssignableFrom(clazz) || clazz.isPrimitive() || String.class.isAssignableFrom(clazz) ||
            Date.class.isAssignableFrom(clazz)) {
        transformer = transformerCache.computeIfAbsent(clazz, SmartTransformer::new);
    } else {
        transformer = transformerCache.computeIfAbsent(clazz, BeanTransformerAdapter::new);
    }
    return query.setResultTransformer(transformer);
}
 

/**
 * 搜索所有Message信息列表
 * @return
 */
public List findAllAndData(){
	try {
		String queryString = "SELECT message.*,message_data.content, (SELECT user.nickname FROM user WHERE user.id=message.other) AS other_nickname ,(SELECT user.nickname FROM user WHERE user.id=message.self) AS self_nickname FROM message ,message_data ,user WHERE message.id=message_data.id GROUP BY message.id ORDER BY message.id DESC ";
		Query queryObject = getCurrentSession().createSQLQuery(queryString).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		return queryObject.list();
	} catch (RuntimeException re) {
		log.error("find by property name failed", re);
		throw re;
	}
}
 

@Override
public Criteria evaluate(Session session) {
	Criteria c;
	
	if(glossary_id==null){
		c = session.createCriteria(SbiGlWord.class,"gl_word");
	}else{
		//filter by glossary
		c = session.createCriteria(SbiGlWlist.class,"wlist");
		c.createAlias("wlist.word", "gl_word");
		c.createAlias("wlist.content", "gl_cont");
		c.add(Restrictions.eq("gl_cont.glossaryId", glossary_id));
	}
	
	
	c.setProjection(Projections.projectionList().add(Projections.property("gl_word.wordId"), "wordId").add(Projections.property("gl_word.word"), "word"))
			.setResultTransformer(Transformers.aliasToBean(SbiGlWord.class));
	if (word != null && !word.isEmpty()) {
		c.add(Restrictions.like("gl_word.word", word, MatchMode.ANYWHERE).ignoreCase());
	}
	if(page!=null && item_per_page!=null ){
		c.setFirstResult((page - 1) * item_per_page);
	     c.setMaxResults(item_per_page);
		}
	c.addOrder(Order.asc("gl_word.word"));
	return c;
}