javax.persistence.Query#unwrap

Source File: DefaultJpaRepository.java From ueboot with BSD 3-Clause "New" or "Revised" License

8 votes

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

Source File: DefaultJpaRepository.java From ueboot with BSD 3-Clause "New" or "Revised" License

6 votes

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

Source File: JqlUtils.java From spring-boot with Apache License 2.0

6 votes

/**
     * 同上，构造查询语句，不分页,自定义返回值包装
     *
     * @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;
    }

Source File: DefaultJpaRepository.java From ueboot with BSD 3-Clause "New" or "Revised" License

5 votes

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

Source File: DefaultJpaRepository.java From ueboot with BSD 3-Clause "New" or "Revised" License

5 votes

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

Source File: CustomBaseSqlDaoImpl.java From tianti with Apache License 2.0

5 votes

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

Source File: CustomBaseSqlDaoImpl.java From tianti with Apache License 2.0

5 votes

/**
 * 根据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;
}

Source File: SQLExtractor.java From hibernate-types with Apache License 2.0

5 votes

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

Source File: SQLExtractor.java From hibernate-types with Apache License 2.0

5 votes

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

Source File: SQLExtractor.java From hibernate-types with Apache License 2.0

5 votes

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

Source File: SQLExtractor.java From hibernate-types with Apache License 2.0

5 votes

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

Source File: JtaQueryTest.java From tomee with Apache License 2.0

5 votes

@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());
    }
}

Source File: CustomBaseSqlDaoImpl.java From tianti with Apache License 2.0

4 votes

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

Source File: JqlUtils.java From spring-boot with Apache License 2.0

4 votes

/**
     * 利用 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);
    }

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