下面列出了怎么用org.hibernate.query.NativeQuery的API类实例代码及写法，或者点击链接到github查看源代码。
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();
}
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();
}
@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 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")
@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 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" })
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 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 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;
}
};
}
@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")));
}
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;
}
@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();
}
@Test
public void test() {
doInJPA(entityManager -> {
Book book = entityManager.unwrap(Session.class)
.bySimpleNaturalId(Book.class)
.load("978-9730228236");
QueryCountHolder.clear();
book.setProperties(
"{" +
" \"title\": \"High-Performance Java Persistence\"," +
" \"author\": \"Vlad Mihalcea\"," +
" \"publisher\": \"Amazon\"," +
" \"price\": 44.99," +
" \"url\": \"https://www.amazon.com/High-Performance-Java-Persistence-Vlad-Mihalcea/dp/973022823X/\"" +
"}"
);
});
QueryCount queryCount = QueryCountHolder.getGrandTotal();
assertEquals(1, queryCount.getTotal());
assertEquals(1, queryCount.getUpdate());
doInJPA(entityManager -> {
JsonNode properties = (JsonNode) entityManager
.createNativeQuery(
"SELECT " +
" properties AS properties " +
"FROM book " +
"WHERE " +
" isbn = :isbn")
.setParameter("isbn", "978-9730228236")
.unwrap(NativeQuery.class)
.addScalar("properties", new JsonBinaryType(JsonNode.class))
.getSingleResult();
assertEquals("High-Performance Java Persistence", properties.get("title").asText());
});
}
@Test
public void testFetchAndPaginateUsingDenseRank() {
doInJPA(entityManager -> {
List<Post> posts = entityManager.createNamedQuery("PostWithCommentByRank")
.setParameter("titlePattern", "High-Performance Java Persistence %")
.setParameter("rank", 5)
.unwrap(NativeQuery.class)
.setResultTransformer(
new DistinctPostResultTransformer(entityManager))
.getResultList();
assertEquals(5, posts.size());
Post post1 = posts.get(0);
List<PostComment> comments = post1.getComments();
for (int i = 0; i < COMMENT_COUNT - 1; i++) {
PostComment postComment1 = comments.get(i);
assertEquals(
String.format(
"Comment nr. %d - A must-read!",
i + 1
),
postComment1.getReview()
);
}
});
}
@Test
public void test() {
doInJPA(entityManager -> {
Book book = entityManager.unwrap(Session.class)
.bySimpleNaturalId(Book.class)
.load("978-9730228236");
QueryCountHolder.clear();
book.setProperties(
"{" +
" \"title\": \"High-Performance Java Persistence\"," +
" \"author\": \"Vlad Mihalcea\"," +
" \"publisher\": \"Amazon\"," +
" \"price\": 44.99," +
" \"url\": \"https://www.amazon.com/High-Performance-Java-Persistence-Vlad-Mihalcea/dp/973022823X/\"" +
"}"
);
});
QueryCount queryCount = QueryCountHolder.getGrandTotal();
assertEquals(1, queryCount.getTotal());
assertEquals(1, queryCount.getUpdate());
doInJPA(entityManager -> {
JsonNode properties = (JsonNode) entityManager
.createNativeQuery(
"SELECT " +
" properties AS properties " +
"FROM book " +
"WHERE " +
" isbn = :isbn")
.setParameter("isbn", "978-9730228236")
.unwrap(NativeQuery.class)
.addScalar("properties", new JsonStringType(JsonNode.class))
.getSingleResult();
assertEquals("High-Performance Java Persistence", properties.get("title").asText());
});
}
@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);
}
@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();
}
@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), YearMonthIntegerType.INSTANCE)
.setParameter("endYearMonth", YearMonth.of(2018, 1), YearMonthIntegerType.INSTANCE)
.getResultList();
});
LOGGER.info("Execution plan: \n{}", executionPlanLines.stream().collect(Collectors.joining("\n")));
}
private void addParameters(Date startDate, Date endDate, String area, Integer typeId, NativeQuery<Object[]> query) {
if (startDate != null && endDate != null) {
query.setParameter("startDate", startDate);
query.setParameter("endDate", endDate);
}
if (typeId != null) {
query.setParameter("typeId", typeId);
} else if (area != null) {
query.setParameter("area", area);
}
}
@Override
public Object[] getDateRangeOfAccesses(Long userUid, Long toolSessionId) {
@SuppressWarnings("unchecked")
NativeQuery<Object[]> query = getSession().createNativeQuery(SQL_QUERY_DATES_BY_USER_SESSION.toString())
.setParameter("userUid", userUid).setParameter("sessionId", toolSessionId);
Object[] values = (Object[]) query.list().get(0);
return values;
}
@Override
@SuppressWarnings("unchecked")
public List<StatisticDTO> getStatisticsBySession(final Long contentId) {
NativeQuery<StatisticDTO> query = getSession().createNativeQuery(GET_STATISTICS);
query.addScalar("sessionId", LongType.INSTANCE).addScalar("sessionName", StringType.INSTANCE)
.addScalar("numLearners", IntegerType.INSTANCE).addScalar("numLearnersFinished", IntegerType.INSTANCE)
.setParameter("contentId", contentId).setResultTransformer(Transformers.aliasToBean(StatisticDTO.class));
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;
}
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;
}
@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();
}