org.hibernate.query.NativeQuery 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();
}
 

@Override
   public int countAttemptsPerOption(Long optionUid) {
String COUNT_ATTEMPTS_BY_OPTION_UID = "SELECT count(*) "
	+ "FROM tl_laasse10_assessment_result AS result "
	+ "JOIN tl_laasse10_question_result AS questionResult ON result.uid = questionResult.result_uid "
	+ "JOIN tl_laasse10_option_answer AS optionAnswer ON questionResult.uid = optionAnswer.question_result_uid AND optionAnswer.answer_boolean=1 AND optionAnswer.question_option_uid = :optionUid "
	+ "WHERE (result.finish_date IS NOT NULL) AND result.latest=1";

NativeQuery<?> query = getSession().createNativeQuery(COUNT_ATTEMPTS_BY_OPTION_UID);
query.setParameter("optionUid", optionUid);
List list = query.list();
if (list == null || list.size() == 0) {
    return 0;
}
return ((Number) list.get(0)).intValue();
   }
 

@SuppressWarnings("unchecked")
   @Override
   public int getCountUsersBySessionWithSearch(final Long toolSessionId, String searchString) {

StringBuilder queryText = new StringBuilder(GET_COUNT_USERS_FOR_SESSION_AND_QUESTION_WITH_NAME_SEARCH);
buildNameSearch(queryText, searchString);

NativeQuery<Object[]> query = getSession().createNativeQuery(queryText.toString());
query.setParameter("toolSessionId", toolSessionId);
List list = query.list();

if (list == null || list.size() == 0) {
    return 0;
}
return ((Number) list.get(0)).intValue();
   }
 

@Test
public void testNativeQueryResultMapping() {
    doInJPA(entityManager -> {
        List<BookDTO> books = entityManager.createNativeQuery(
            "SELECT " +
            "       b.id as id, " +
            "       b.properties as properties " +
            "FROM book b")
        .unwrap(NativeQuery.class)
        .setResultSetMapping("BookDTO")
        .getResultList();

        assertEquals(1, books.size());
        BookDTO book = books.get(0);

        assertEquals(expectedPrice(), book.getProperties().get("price").asText());
    });
}
 

@SuppressWarnings({ "unchecked" })
   private SortedSet<Comment> getNextThreadByThreadIdNewestFirst(final Long rootTopicId,
    final Long previousThreadMessageId, Integer numberOfThreads, Integer sortBy, Integer userId) {

// the search to get to the top level is quite light, so get just the uids
// then build a complete set.
List<Number> threadUidList = null;
if (previousThreadMessageId == null || previousThreadMessageId == 0L) {
    threadUidList = getSession().createSQLQuery(SQL_QUERY_FIND_FIRST_THREAD_TOP_BY_UID)
	    .setParameter("rootUid", rootTopicId).setMaxResults(numberOfThreads).list();
} else {
    threadUidList = getSession().createSQLQuery(SQL_QUERY_FIND_NEXT_THREAD_TOP).setParameter("rootUid", rootTopicId)
	    .setParameter("lastUid", previousThreadMessageId).setMaxResults(numberOfThreads).list();
}

if (threadUidList != null && threadUidList.size() > 0) {
    NativeQuery<Object[]> query = getSession().createNativeQuery(SQL_QUERY_FIND_NEXT_THREAD_MESSAGES);
    query.addEntity("comment", Comment.class).addScalar("likes_total", IntegerType.INSTANCE)
	    .addScalar("user_vote", IntegerType.INSTANCE).setParameter("userId", userId != null ? userId : 0)
	    .setParameterList("threadIds", threadUidList);
    List<Object[]> results = query.list();
    return upgradeComments(results, sortBy);
}
return new TreeSet<Comment>();
   }
 

@SuppressWarnings("unchecked")
   @Override
   public Object[] getStatsMarksForLeaders(Long toolContentId) {
NativeQuery<Object[]> query = getSession().createNativeQuery(FIND_MARK_STATS_FOR_LEADERS)
	.addScalar("min_grade", FloatType.INSTANCE)
	.addScalar("avg_grade", FloatType.INSTANCE)
	.addScalar("max_grade", FloatType.INSTANCE)
	.addScalar("num_complete", IntegerType.INSTANCE);
query.setParameter("toolContentId", toolContentId);
List list = query.list();
if ((list == null) || (list.size() == 0)) {
    return null;
} else {
    return (Object[]) list.get(0);
}
   }
 

@Override
   public Object[] getStatsMarksForLeaders(Long toolContentId) {
NativeQuery<?> query = getSession().createNativeQuery(FIND_MARK_STATS)
	.addScalar("min_grade", FloatType.INSTANCE)
	.addScalar("avg_grade", FloatType.INSTANCE)
	.addScalar("max_grade", FloatType.INSTANCE)
	.addScalar("num_complete", IntegerType.INSTANCE);
query.setParameter("toolContentId", toolContentId);
@SuppressWarnings("unchecked")
List<Object[]> list = (List<Object[]>) query.list();
if ((list == null) || (list.size() == 0)) {
    return null;
} else {
    return (Object[]) list.get(0);
}
   }
 

@SuppressWarnings({ "unchecked" })
   private SortedSet<Comment> getStickyByThreadIdLikes(final Long rootTopicId, Integer sortBy, String extraSortParam,
    Integer userId) {

List<Object[]> topThreadObjects = getSession().createNativeQuery(SQL_QUERY_FIND_STICKY_BY_LIKES)
	.addEntity("comment", Comment.class).addScalar("likes_total", IntegerType.INSTANCE)
	.addScalar("user_vote", IntegerType.INSTANCE).setParameter("rootUid", rootTopicId)
	.setParameter("userId", userId != null ? userId : 0).list();

if (topThreadObjects != null && topThreadObjects.size() > 0) {
    // build the list of uids
    List<Number> threadUidList = new ArrayList<Number>();
    for (Object[] rawObject : topThreadObjects) {
	Comment comment = (Comment) rawObject[0];
	threadUidList.add(comment.getUid());
    }
    NativeQuery<Object[]> query = getSession().createNativeQuery(SQL_QUERY_FIND_NEXT_THREAD_MESSAGES_REPLIES_ONLY);
    query.addEntity("comment", Comment.class).addScalar("likes_total", IntegerType.INSTANCE)
	    .addScalar("user_vote", IntegerType.INSTANCE).setParameter("userId", userId != null ? userId : 0)
	    .setParameterList("threadIds", threadUidList);
    List<Object[]> results = query.list();
    topThreadObjects.addAll(results);
    return upgradeComments(topThreadObjects, sortBy);
}
return new TreeSet<Comment>();
   }
 

@Override
public boolean setSelect(int value) {
	// TODO Auto-generated method stub
	
	String sql = "update syscontrol set ifSelect = " + value;
	try {
		NativeQuery<?> query = sessionFactory.getCurrentSession().createNativeQuery(sql);
		query.executeUpdate();
		return true;
	} catch (Exception e) {
		// TODO: handle exception
		System.err.println("系统表更新失败！");
		return false;
	}
	
}
 

@Override
   public void delete(Object object) {
// remove references to the removed page
WikiPage removedWikiPage = (WikiPage) object;
String title = removedWikiPage.getTitle();
String escapedTitle = WikiPageDTO.javaScriptEscape(title);
String codeToReplace = WikiPageDAO.CHANGE_WIKI_JAVASCRIPT_METHOD.replace("?", escapedTitle);
String replacementCode = "#";

NativeQuery<?> query = getSessionFactory().getCurrentSession().createNativeQuery(REMOVE_WIKI_REFERENCES);
query.setParameter("codeToReplace", codeToReplace);
query.setParameter("replacementCode", replacementCode);
query.setParameter("parentWikiUid", removedWikiPage.getParentWiki().getUid());

super.delete(object);
query.executeUpdate();
   }
 

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

public NativeQuery.ReturnProperty addProperty(final String propertyName) {
	if ( propertyMappings == null ) {
		propertyMappings = new HashMap<>();
	}
	return new NativeQuery.ReturnProperty() {
		public NativeQuery.ReturnProperty addColumnAlias(String columnAlias) {
			String[] columnAliases = propertyMappings.get( propertyName );
			if ( columnAliases == null ) {
				columnAliases = new String[] {columnAlias};
			}
			else {
				String[] newColumnAliases = new String[columnAliases.length + 1];
				System.arraycopy( columnAliases, 0, newColumnAliases, 0, columnAliases.length );
				newColumnAliases[columnAliases.length] = columnAlias;
				columnAliases = newColumnAliases;
			}
			propertyMappings.put( propertyName, columnAliases );
			return this;
		}
	};
}
 

@Override
   public int createUsersForSession(final PeerreviewSession session) {

NativeQuery<?> query = getSession().createNativeQuery(CREATE_USERS);
query.setParameter("session_uid", session.getUid()).setParameter("tool_session_id", session.getSessionId());
return query.executeUpdate();
   }
 

@Override
   @SuppressWarnings("unchecked")
   /** Returns < [surveySession, numUsers] ... [surveySession, numUsers]> */
   public List<Object[]> getStatisticsBySession(final Long contentId) {

NativeQuery<Object[]> query = getSession().createNativeQuery(GET_STATISTICS);
query.addEntity(SurveySession.class).addScalar("numUsers", IntegerType.INSTANCE).setParameter("contentId",
	contentId);
return query.list();
   }
 

@Override
   @SuppressWarnings("rawtypes")
   public int getCountUsersForOpenTextEntries(Long sessionUid, Long toolContentId, String searchStringVote,
    String searchStringUsername) {

NativeQuery query;
StringBuilder queryText = new StringBuilder(COUNT_USERS_OPEN_TEXT_BY_SESSION_UID);

if (sessionUid != null) {
    // get all the users who did an open text reply, restricting by session
    queryText.append(FIND_USER_OPEN_TEXT_SESSION_UID_ADD);
    buildCombinedSearch(searchStringVote, searchStringUsername, queryText);
    query = getSession().createSQLQuery(queryText.toString());
    query.setParameter("sessionUid", sessionUid);

} else {

    // get all the users for this content (more than one session potentially)
    queryText.append(FIND_USER_OPEN_TEXT_CONTENT_UID_ADD);
    buildCombinedSearch(searchStringVote, searchStringUsername, queryText);
    query = getSession().createSQLQuery(queryText.toString());
    query.setParameter("toolContentId", toolContentId);

}

List list = query.list();
if (list == null || list.size() == 0) {
    return 0;
}
return ((Number) list.get(0)).intValue();
   }
 

@Override
   @SuppressWarnings("unchecked")
   public List<VoteStatsDTO> getStatisticsBySession(Long toolContentId) {

NativeQuery<VoteStatsDTO> query = getSession().createSQLQuery(GET_STATISTICS);
query.addScalar("sessionUid", LongType.INSTANCE).addScalar("sessionName", StringType.INSTANCE)
	.addScalar("countUsersComplete", IntegerType.INSTANCE).setParameter("contentId", toolContentId)
	.setResultTransformer(Transformers.aliasToBean(VoteStatsDTO.class));

return query.list();
   }
 

@Override
   public Object[] getDateRangeOfMessages(Long userUid) {
NativeQuery<?> query =  getSession().createNativeQuery(SQL_QUERY_DATES_BY_USER_SESSION.toString())
	.setParameter("userUid", userUid);
Object[] values = (Object[]) query.list().get(0);
return values;
   }
 

@Override
   public List<AssessmentUserDTO> getFirstTotalScoresByContentId(Long toolContentId) {
final String FIRST_SCORES_BY_CONTENT_ID = "SELECT user.user_id, res.grade "
	+ "FROM tl_laasse10_assessment_result AS res "
	+ "JOIN tl_laasse10_user AS user ON res.user_uid = user.uid "
	+ "JOIN tl_laasse10_assessment AS assess ON res.assessment_uid = assess.uid AND assess.content_id = :contentId "
	+ "INNER JOIN (SELECT user_uid, MIN(start_date) AS startDate FROM tl_laasse10_assessment_result WHERE finish_date IS NOT NULL GROUP BY user_uid) firstRes "
	+ "ON (res.user_uid = firstRes.user_uid AND res.start_date = firstRes.startDate) GROUP BY res.user_uid";

NativeQuery<?> query = getSession().createNativeQuery(FIRST_SCORES_BY_CONTENT_ID);
query.setParameter("contentId", toolContentId);
@SuppressWarnings("unchecked")
List<Object[]> list = (List<Object[]>) query.list();
return convertResultsToAssessmentUserDTOList(list);
   }
 

private Query bind(Query query, Object[] values) {
    //get proxy target if exist.
    //must be hibernate QueryImpl
    NativeQuery targetQuery = AopTargetUtils.getTarget(query);
    Map<String, Object> params = getParams(values);
    if (!CollectionUtils.isEmpty(params)) {
        QueryBuilder.setParams(targetQuery, params);
    }
    return query;
}
 

@Test
@Ignore
public void testIndexing() {
    doInJPA(entityManager -> {

        YearMonth yearMonth = YearMonth.of(1970, 1);

        for (int i = 0; i < 5000; i++) {
            yearMonth = yearMonth.plusMonths(1);

            Book book = new Book();
            book.setTitle(
                    String.format(
                            "IT industry newsletter - %s edition", yearMonth
                    )
            );
            book.setPublishedOn(yearMonth);

            entityManager.persist(book);
        }
    });

    List<String> executionPlanLines = doInJPA(entityManager -> {
        return entityManager.createNativeQuery(
                "EXPLAIN ANALYZE " +
                        "SELECT " +
                        "    b.published_on " +
                        "FROM " +
                        "    book b " +
                        "WHERE " +
                        "   b.published_on BETWEEN :startYearMonth AND :endYearMonth ")
                .unwrap(NativeQuery.class)
                .setParameter("startYearMonth", YearMonth.of(2010, 12), YearMonthEpochType.INSTANCE)
                .setParameter("endYearMonth", YearMonth.of(2018, 1), YearMonthEpochType.INSTANCE)
                .getResultList();
    });

    LOGGER.info("Execution plan: \n{}", executionPlanLines.stream().collect(Collectors.joining("\n")));
}
 

@SuppressWarnings("unchecked")
   @Override
   public List<Number> getRawLeaderMarksByToolContentId(Long toolContentId) {

NativeQuery<Number> query = getSession().createNativeQuery(LOAD_MARKS_FOR_LEADERS);
query.setParameter("toolContentId", toolContentId);
List<Number> list = query.list();
return list;
   }
 

@Override
   @SuppressWarnings("unchecked")
   public List<Long> getReportsForGroup(final Long sessionId, final Long reportId) {
NativeQuery<Long> query = getSession().createNativeQuery(GET_GROUP_REPORTS);
query.addScalar("reportId", LongType.INSTANCE)
	.setParameter("sessionId", sessionId)
	.setParameter("reportId", reportId);

return query.list();
   }
 

@Override
@SuppressWarnings("unchecked")
public boolean exists( PotentialDuplicate potentialDuplicate )
{
    NativeQuery<BigInteger> query;
    if ( potentialDuplicate.getTeiA() == null )
    {
        return false;
    }

    if ( potentialDuplicate.getTeiB() == null )
    {
        query = getSession().createNativeQuery( "select count(potentialduplicateid) from potentialduplicate pd " +
            "where pd.teiA = :teia limit 1" );
        query.setParameter( "teia", potentialDuplicate.getTeiA() );
    }
    else
    {
        query = getSession().createNativeQuery( "select count(potentialduplicateid) from potentialduplicate pd " +
            "where (pd.teiA = :teia and pd.teiB = :teib) or (pd.teiA = :teib and pd.teiB = :teia) limit 1" );

        query.setParameter( "teia", potentialDuplicate.getTeiA() );
        query.setParameter( "teib", potentialDuplicate.getTeiB() );
    }

    return query.getSingleResult().intValue() != 0;
}
 

@Test
public void whenQueryExecutedWithUnmappedEntity_thenMappingException() {
    thrown.expectCause(isA(MappingException.class));
    thrown.expectMessage("Unknown entity: java.lang.String");

    Session session = sessionFactory.openSession();
    NativeQuery<String> query = session
        .createNativeQuery("select name from PRODUCT", String.class);
    query.getResultList();
}
 

private boolean getAllFoosExists() {
    try {
        @SuppressWarnings("unchecked")
        NativeQuery<Foo> sqlQuery = session.createSQLQuery("CALL GetAllFoos()").addEntity(Foo.class);
        sqlQuery.list();
        return true;
    } catch (SQLGrammarException e) {
        LOGGER.error("WARNING : GetAllFoos() Procedure is may be missing ", e);
        return false;
    }
}
 

@Override
   @SuppressWarnings("unchecked")
   public List<Object[]> getUsersWithPortraitsBySessionID(Long sessionId) {
final String LOAD_USERS_WITH_PORTRAITS_BY_SESSION_ID = "SELECT user.user_id, luser.portrait_uuid portraitId FROM tl_lamc11_que_usr user  " +
	" INNER JOIN tl_lamc11_session session ON user.mc_session_id=session.uid" +
	" INNER JOIN lams_user luser ON luser.user_id = user.que_usr_id" +
	" WHERE session.mc_session_id = :sessionId";

NativeQuery<Object[]> query = getSession().createNativeQuery(LOAD_USERS_WITH_PORTRAITS_BY_SESSION_ID);
query.setParameter("sessionId", sessionId);
List<Object[]> list = query.list();

ArrayList<Object[]> userDtos = new ArrayList<Object[]>();
if (list != null && list.size() > 0) {
    for (Object[] element : list) {

	Long userId = ((Number) element[0]).longValue();
	Long portraitId = element[1] == null ? null : ((Number) element[1]).longValue();

	Object[] userDto = new Object[2];
	userDto[0] = userId;
	userDto[0] = portraitId;
	userDtos.add(userDto);
    }

}

return userDtos;
   }
 

@Override
   public Object[] getDateRangeOfTasks(Long userUid, Long sessionId) {
NativeQuery query = (NativeQuery) getSession().createNativeQuery(SQL_QUERY_DATES_BY_USER_SESSION.toString())
	.setParameter("userUid", userUid).setParameter("sessionId", sessionId);
Object[] values = (Object[]) query.list().get(0);
return values;
   }
 

@SuppressWarnings("unchecked")
   @Override
   public List<Number> getRawUserMarksBySession(Long sessionId) {

NativeQuery<Number> query = getSession().createNativeQuery(LOAD_MARKS_FOR_SESSION);
query.setParameter("sessionId", sessionId);
List<Number> list = query.list();
return list;
   }
 

@Override
public NativeQuery setResultSetMapping(String name) {
	ResultSetMappingDefinition mapping = getProducer().getFactory().getNamedQueryRepository().getResultSetMappingDefinition( name );
	if ( mapping == null ) {
		throw new MappingException( "Unknown SqlResultSetMapping [" + name + "]" );
	}
	NativeSQLQueryReturn[] returns = mapping.getQueryReturns();
	queryReturns.addAll( Arrays.asList( returns ) );
	return this;
}
 

@Test
public void givenQueryWithDataTypeMismatch_WhenQueryExecuted_thenDataException() {
    thrown.expectCause(isA(DataException.class));
    thrown.expectMessage(
        "org.hibernate.exception.DataException: could not prepare statement");

    Session session = sessionFactory.openSession();
    NativeQuery<Product> query = session.createNativeQuery(
        "select * from PRODUCT where id='wrongTypeId'", Product.class);
    query.getResultList();
}