Java Code Examples for javax.persistence.Query#unwrap()

The following examples show how to use javax.persistence.Query#unwrap() . You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source File: DefaultJpaRepository.java    From ueboot with BSD 3-Clause "New" or "Revised" License 8 votes vote down vote up
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();
}
 
Example 2
Source File: DefaultJpaRepository.java    From ueboot with BSD 3-Clause "New" or "Revised" License 6 votes vote down vote up
public List findBySql(StringQuery stringQuery, ResultTransformer 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(transformer).list();
}
 
Example 3
Source File: JqlUtils.java    From spring-boot with Apache License 2.0 6 votes vote down vote up
/**
     * 同上,构造查询语句,不分页,自定义返回值包装
     *
     * @param em
     * @param queryJql
     * @param queryArgs
     * @param resultClass
     * @param <T>
     * @return
     */
    private static <T> List<T> queryListByJQLString(final EntityManager em, final String queryJql, Object[] queryArgs, Class<T> resultClass) {


        log.info("queryJqlString : \n {} ", queryJql);

        Query query = em.createQuery(queryJql);
//        final List<Object[]> content = query.getResultList();
//        List<Something> somethingList = Lists.newArrayList();
//        content.forEach(object -> somethingList.add(//map obj to something));

        // 可以代替上述?未测试
        query.unwrap(resultClass); // 返回值包装

        //Bind an argument value to a positional parameter.
        for (int i = 0; i < queryArgs.length; i++)
            query.setParameter(i, queryArgs[i]);
        List<T> content = query.getResultList();
        return content;
    }
 
Example 4
Source File: DefaultJpaRepository.java    From ueboot with BSD 3-Clause "New" or "Revised" License 5 votes vote down vote up
public Page findBySql(StringQuery stringQuery, Pageable pageable, ResultTransformer transformer) {
    Assert.notNull(stringQuery, "StringQuery must not be null!");
    Assert.notNull(pageable, "PageRequest must not be null!");
    Assert.notNull(transformer, "Transformer must not be null!");
    String sql = stringQuery.getQuery();
    Assert.hasText(sql, "Sql must has text!");
    Query query = em.createNativeQuery(sql);
    NamedParams params = stringQuery.getParams();
    setQueryParams(query, params);
    query.setMaxResults(pageable.getPageSize());
    query.setFirstResult((int) pageable.getOffset());

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

    List resultList = sqlQuery.setResultTransformer(transformer).list();
    String countSql = QueryUtils.genCountQueryString(sql);

    Query countQuery = em.createNativeQuery(countSql);
    setQueryParams(countQuery, params);
    Long total = Long.valueOf(countQuery.getSingleResult().toString());

    Page page = new PageImpl(resultList, pageable, total);
    return page;
}
 
Example 5
Source File: DefaultJpaRepository.java    From ueboot with BSD 3-Clause "New" or "Revised" License 5 votes vote down vote up
public <S> Page<S> findBySql(StringQuery stringQuery, Pageable pageable, Class<S> transformer) {
    Assert.notNull(stringQuery, "StringQuery must not be null!");
    Assert.notNull(pageable, "PageRequest must not be null!");
    Assert.notNull(transformer, "TransformerClass must not be null!");
    String sql = stringQuery.getQuery();
    Assert.hasText(sql, "Sql must has text!");
    Query query = em.createNativeQuery(sql);
    NamedParams params = stringQuery.getParams();
    setQueryParams(query, params);
    query.setMaxResults(pageable.getPageSize());
    query.setFirstResult((int) pageable.getOffset());

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

    List<S> resultList = sqlQuery.setResultTransformer(Transformers.aliasToBean(transformer)).list();
    String countSql = QueryUtils.genCountQueryString(sql);

    Query countQuery = em.createNativeQuery(countSql);
    setQueryParams(countQuery, params);
    Long total = Long.valueOf(countQuery.getSingleResult().toString());

    Page page = new PageImpl(resultList, pageable, total);
    return page;
}
 
Example 6
Source File: CustomBaseSqlDaoImpl.java    From tianti with Apache License 2.0 5 votes vote down vote up
@SuppressWarnings("unchecked")
public List<Map<String, Object>> querySqlObjects(String sql, Object params, Integer currentPage,Integer rowsInPage){
	Query qry = em.createNativeQuery(sql);
	SQLQuery s = qry.unwrap(SQLQuery.class);
	
	//设置参数
	if(params != null){
		if(params instanceof List){
			List<Object> paramList = (List<Object>) params;
			for(int i = 0, size = paramList.size(); i < size; i++){
				qry.setParameter(i+1, paramList.get(i));
			}
		}else if(params instanceof Map){
			Map<String, Object> paramMap = (Map<String, Object>) params;
			for(String key : paramMap.keySet()){
				qry.setParameter(key, paramMap.get(key));
			}
		}
	}
	
	if (currentPage != null && rowsInPage != null) {//判断是否有分页
		// 起始对象位置
		qry.setFirstResult(rowsInPage * (currentPage - 1));
		// 查询对象个数
		qry.setMaxResults(rowsInPage);
	}
	s.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
	List<Map<String, Object>> resultList=new ArrayList<Map<String, Object>>();
	try {
		resultList=s.list();
	} catch (Exception e) {
	}finally{
		em.close();
	}
	return resultList;
}
 
Example 7
Source File: CustomBaseSqlDaoImpl.java    From tianti with Apache License 2.0 5 votes vote down vote up
/**
 * 根据SQL语句查询分页
 * @param sql
 * @param currentPage
 * @param pageSize
 * @return
 */
@SuppressWarnings({ "rawtypes", "unchecked" })
public PageModel queryForPageBySql(String sql,Integer currentPage,Integer pageSize){
	PageModel page = new PageModel();
	Integer totalCount = 0;
	Integer totalPage = 0;
	
	//EntityManager em = this.emf.createEntityManager();
	Query qry = em.createNativeQuery(sql);
	Query qry2 = em.createNativeQuery(sql);
	SQLQuery s = qry.unwrap(SQLQuery.class);
	if (currentPage != null && pageSize != null) {//判断是否有分页
		// 起始对象位置
		qry.setFirstResult(pageSize * (currentPage - 1));
		// 查询对象个数
		qry.setMaxResults(pageSize);
	}
	s.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
	List<Map> resultList=new ArrayList<Map>();
	List list = qry2.getResultList();
	totalCount = (list == null ? 0 : list.size());
	if(totalCount % pageSize == 0){
		totalPage = totalCount/pageSize;
	}else{
		totalPage = totalCount/pageSize + 1;
	}
	try {
		resultList=s.list();
		page.setCurrentPage(currentPage);
		page.setPageSize(pageSize);
		page.setList(resultList);
		page.setTotalCount(totalCount);
		page.setTotalPage(totalPage);
	} catch (Exception e) {
		e.printStackTrace();
	}finally{
		em.close();
	}		
	return page;
}
 
Example 8
Source File: SQLExtractor.java    From hibernate-types with Apache License 2.0 5 votes vote down vote up
/**
 * Get the underlying SQL generated by the provided JPA query.
 *
 * @param query JPA query
 * @return the underlying SQL generated by the provided JPA query
 */
public static String from(Query query) {
    AbstractProducedQuery abstractProducedQuery = query.unwrap(AbstractProducedQuery.class);
    String[] sqls = abstractProducedQuery
        .getProducer()
        .getFactory()
        .getQueryPlanCache()
        .getHQLQueryPlan(abstractProducedQuery.getQueryString(), false, Collections.emptyMap())
        .getSqlStrings();

    return sqls.length > 0 ? sqls[0] : null;
}
 
Example 9
Source File: SQLExtractor.java    From hibernate-types with Apache License 2.0 5 votes vote down vote up
/**
 * Get the underlying SQL generated by the provided JPA query.
 *
 * @param query JPA query
 * @return the underlying SQL generated by the provided JPA query
 */
public static String from(Query query) {
    AbstractQueryImpl abstractQuery = query.unwrap(AbstractQueryImpl.class);
    SessionImplementor session = ReflectionUtils.getFieldValue(abstractQuery, "session");
    String[] sqls = session.getFactory()
        .getQueryPlanCache()
        .getHQLQueryPlan(abstractQuery.getQueryString(), false, Collections.<String, Filter>emptyMap())
        .getSqlStrings();

    return sqls.length > 0 ? sqls[0] : null;
}
 
Example 10
Source File: SQLExtractor.java    From hibernate-types with Apache License 2.0 5 votes vote down vote up
/**
 * Get the underlying SQL generated by the provided JPA query.
 *
 * @param query JPA query
 * @return the underlying SQL generated by the provided JPA query
 */
public static String from(Query query) {
    AbstractQueryImpl abstractQuery = query.unwrap(AbstractQueryImpl.class);
    SessionImplementor session = ReflectionUtils.getFieldValue(abstractQuery, "session");
    String[] sqls = session.getFactory()
        .getQueryPlanCache()
        .getHQLQueryPlan(abstractQuery.getQueryString(), false, Collections.<String, Filter>emptyMap())
        .getSqlStrings();

    return sqls.length > 0 ? sqls[0] : null;
}
 
Example 11
Source File: SQLExtractor.java    From hibernate-types with Apache License 2.0 5 votes vote down vote up
/**
 * Get the underlying SQL generated by the provided JPA query.
 *
 * @param query JPA query
 * @return the underlying SQL generated by the provided JPA query
 */
public static String from(Query query) {
    AbstractQueryImpl abstractQuery = query.unwrap(AbstractQueryImpl.class);
    SessionImplementor session = ReflectionUtils.getFieldValue(abstractQuery, "session");
    String[] sqls = session.getFactory()
        .getQueryPlanCache()
        .getHQLQueryPlan(abstractQuery.getQueryString(), false, Collections.<String, Filter>emptyMap())
        .getSqlStrings();

    return sqls.length > 0 ? sqls[0] : null;
}
 
Example 12
Source File: JtaQueryTest.java    From tomee with Apache License 2.0 5 votes vote down vote up
@Test
public void jtaUnwrap() {
    for (int i = 0; i < 2; i++) { // no exception already closed
        final Query query = wrapped.createNativeQuery("select 1 from INFORMATION_SCHEMA.SYSTEM_USERS");
        assertTrue(query instanceof JtaQuery);
        final JtaQuery q = query.unwrap(JtaQuery.class);
        assertNotNull(Reflections.get(q, "entityManager"));
        query.getResultList();
        assertFalse(EntityManager.class.cast(Reflections.get(q, "entityManager")).isOpen());
    }
}
 
Example 13
Source File: CustomBaseSqlDaoImpl.java    From tianti with Apache License 2.0 4 votes vote down vote up
@SuppressWarnings({ "rawtypes", "unchecked" })
public PageModel queryForPageBySql(String sql,Map<String,Object> params,Integer currentPage,Integer pageSize){
	PageModel page = new PageModel();
	Integer totalCount = 0;
	Integer totalPage = 0;
	
	Query qry = em.createNativeQuery(sql);
	Query qry2 = em.createNativeQuery(sql);
	
	for(Map.Entry<String,Object> entry: params.entrySet()){
		qry.setParameter(entry.getKey(), entry.getValue());
		qry2.setParameter(entry.getKey(), entry.getValue());
	}
	
	SQLQuery s = qry.unwrap(SQLQuery.class);
	if (currentPage != null && pageSize != null) {//判断是否有分页
		// 起始对象位置
		qry.setFirstResult(pageSize * (currentPage - 1));
		// 查询对象个数
		qry.setMaxResults(pageSize);
	}
	s.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
	List<Map> resultList=new ArrayList<Map>();
	List list = qry2.getResultList();
	totalCount = (list == null ? 0 : list.size());
	if(totalCount % pageSize == 0){
		totalPage = totalCount/pageSize;
	}else{
		totalPage = totalCount/pageSize + 1;
	}
	try {
		resultList=s.list();
		page.setCurrentPage(currentPage);
		page.setPageSize(pageSize);
		page.setList(resultList);
		page.setTotalCount(totalCount);
		page.setTotalPage(totalPage);
	} catch (Exception e) {
		e.printStackTrace();
	}finally{
		em.close();
	}		
	return page;
}
 
Example 14
Source File: JqlUtils.java    From spring-boot with Apache License 2.0 4 votes vote down vote up
/**
     * 利用 JQL 语句进行分页查询
     * JQL 需在 Hibernate 环境中,entityManager 管理 Entity(表)
     * 分页需要进行两次查询
     * 排序条件不在这里维护,在 JQL 语句中。计算总数的语句和查询语句不同,计算总数的语句可以简化很多条件,所以分开写
     *
     * @param em            注入 JdbcTemplate 方法
     *                      //@Autowired EntityManager em;
     *                      -
     *                      如果是多数据源,在可以指定数据源
     * @param queryJql      查询 JQL 语句 ,和不分页时相同,含 Order 如
     *                      String query = "select s.* from Something s "
     *                      + "join somethingelse selse on selse.id = s.fk_somethingelse "
     *                      + "where selse.id = :somethingElseId "
     *                      + "order by selse.date";
     * @param queryArgs     arguments to bind to the query ,查询时,绑定在 queryJql 上的参数,按照位置对应
     *                      按照占位参数的位置,逐个对应 Object[] 中的参数, query.setParameter(i, queryArgs[i]);
     *                      -
     *                      (leaving it to the PreparedStatement to guess the corresponding SQL type ,是 Object 类型,系统可以自动匹配成 sql 类型)
     *                      无参数时,传入 new Objects[]{} 空数组即可
     *                      占位符方式,可以避免 sql 注入  Queries with Named Parameters
     * @param countJql      计算总数的 JQL 语句 , 如
     *                      String countQuery = "select count(s.*) from Something s "
     *                      + "join somethingelse selse on selse.id = s.fk_somethingelse "
     *                      + "where selse.id = :somethingElseId ";
     * @param countArgs     计算总数时,绑定 countJql 上的参数
     * @param currentPageNo 当前页码,从 1 开始
     * @param pageSize      页大小
     * @param resultClass   自定义返回值类型,一般为 Bean
     * @param <T>
     * @return
     */
    private static <T> PageBean<T> queryPageByJQLString(final EntityManager em,
                                                        final String queryJql, Object[] queryArgs,
                                                        final String countJql, Object[] countArgs,
                                                        int currentPageNo, int pageSize, Class<T> resultClass) {


        log.info("queryJqlString : \n {} ", queryJql);
        log.info("countJqlString : \n {} ", countJql);


        Assert.isTrue(currentPageNo >= 1, "currentPageNo : 起始页不应小于 1 ,且从 1 开始。");
        Assert.isTrue(pageSize >= 0, "pageSize : 页大小不能小于 0");
        Assert.isTrue(countJql.contains("count"), "queryNativeSql 和 countNativeSql 参数顺序不对");

        // 计算总数
        Query queryTotal = em.createQuery(countJql);
        for (int i = 0; i < countArgs.length; i++)
            queryTotal.setParameter(i, countArgs[i]);
        int totalRecordsSize = (int) queryTotal.getSingleResult();


        if (totalRecordsSize == 0)
            return new PageBean(pageSize, 0 + 1, 0, Collections.EMPTY_LIST); //currentPageNo 从 1 开始
//        log.info("totalRecordsSize : " + totalRecordsSize);
        //分页
        Query query = em.createQuery(queryJql);
        query.setFirstResult((currentPageNo - 1) * pageSize);
        query.setMaxResults(pageSize);
//        final List<Object[]> content = query.getResultList();
//        List<Something> somethingList = Lists.newArrayList();
//        content.forEach(object -> somethingList.add(//map obj to something));

        // 可以代替上述?未测试
        query.unwrap(resultClass); // 返回值包装

        //Bind an argument value to a positional parameter.
        for (int i = 0; i < queryArgs.length; i++)
            query.setParameter(i, queryArgs[i]);
        List<T> content = query.getResultList();

        return new PageBean(pageSize, currentPageNo, totalRecordsSize, content);
    }