下面列出了怎么用javax.persistence.Query的API类实例代码及写法,或者点击链接到github查看源代码。
/**
* 分页查询评论内容
* @param firstIndex
* @param maxResult
* @param userName 用户名称
* @param isStaff 是否为员工
* @return
*/
@Transactional(readOnly=true,propagation=Propagation.NOT_SUPPORTED)
public List<String> findCommentContentByPage(int firstIndex, int maxResult,String userName,boolean isStaff){
List<String> topicContentList = new ArrayList<String>();//key:话题Id value:话题内容
String sql = "select o.content from Comment o where o.userName=?1 and o.isStaff=?2";
Query query = em.createQuery(sql);
query.setParameter(1, userName);
query.setParameter(2, isStaff);
//索引开始,即从哪条记录开始
query.setFirstResult(firstIndex);
//获取多少条数据
query.setMaxResults(maxResult);
List<Object> objectList = query.getResultList();
if(objectList != null && objectList.size() >0){
for(int i = 0; i<objectList.size(); i++){
String content = (String)objectList.get(i);
topicContentList.add(content);
}
}
return topicContentList;
}
public Page<T> findBySql(String sql, String countSql, NamedParams params, Pageable pageable) {
Assert.hasText(sql, "Query must has text!");
Assert.hasText(countSql, "Count sql must has text!");
Assert.notNull(params, "QueryParams must not be null!");
Assert.notNull(pageable, "PageRequest must not be null!");
Query query = em.createNativeQuery(sql);
setQueryParams(query, params);
query.setMaxResults(pageable.getPageSize());
query.setFirstResult((int) pageable.getOffset());
List<T> resultList = query.getResultList();
Query countQuery = em.createNativeQuery(countSql);
setQueryParams(countQuery, params);
Long total = Long.valueOf(countQuery.getSingleResult().toString());
Page<T> page = new PageImpl(resultList, pageable, total);
return page;
}
public List<Relation> getTrustedRelationsByOwnership( final RelationLink trustedObject, Ownership ownership )
{
EntityManager em = daoManager.getEntityManagerFactory().createEntityManager();
List<Relation> result = Lists.newArrayList();
try
{
Query qr = em.createQuery(
"select ss from RelationImpl AS ss" + " where ss.trustedObject.uniqueIdentifier=:trustedObject "
+ "and ss.relationInfo.ownershipLevel=:ownershipLevel ORDER BY ss.relationStatus DESC" );
qr.setParameter( "trustedObject", trustedObject.getUniqueIdentifier() );
qr.setParameter( "ownershipLevel", ownership.getLevel() );
result.addAll( qr.getResultList() );
}
catch ( Exception ex )
{
logger.warn( "Error querying for trust relation.", ex );
}
finally
{
daoManager.closeEntityManager( em );
}
return result;
}
@SuppressWarnings("unchecked")
List<Subscription> getSubscriptionsForOrg(PlatformUser user,
Set<SubscriptionStatus> states,
org.oscm.paginator.Pagination pagination, String queryString,
Long... keys) {
Set<String> statesAsString = getSubscriptionStatesAsString(states);
Query query = dataManager.createNativeQuery(queryString,
Subscription.class);
try {
query.setParameter("locale", user.getLocale());
query.setParameter("objecttype",
LocalizedObjectTypes.PRODUCT_MARKETING_NAME.name());
} catch (IllegalArgumentException exc) {
logger.logDebug("Parameters are not found in the query. Not an error, just sorting is not applied.");
}
query.setParameter("organization",
Long.valueOf(user.getOrganization().getKey()));
query.setParameter("states", statesAsString);
setPaginationParameters(pagination, query);
setSubscriptionKeysParameter(query, keys);
return query.getResultList();
}
public Query build(GetEntityCountUriInfo uriInfo) throws ODataJPARuntimeException {
Query query = null;
try {
ODataJPAQueryExtensionEntityListener listener = getODataJPAQueryEntityListener((UriInfo) uriInfo);
if (listener != null) {
query = listener.getQuery(uriInfo, em);
JPQLContext jpqlContext = JPQLContext.getJPQLContext();
query = getParameterizedQueryForListeners(jpqlContext, query);
}
if (query == null) {
query = buildQuery((UriInfo) uriInfo, UriInfoType.GetEntityCount);
}
} catch (Exception e) {
throw ODataJPARuntimeException.throwException(
ODataJPARuntimeException.ERROR_JPQL_QUERY_CREATE, e);
} finally {
JPQLContext.removeJPQLContext();
ODataExpressionParser.removePositionalParametersThreadLocal();
}
return query;
}
@Test
public void adHocNativeQuery() {
log.info("... adHocNativeQuery ...");
EntityManager em = emf.createEntityManager();
em.getTransaction().begin();
Query q = em.createNativeQuery("SELECT * FROM book b WHERE id = ?", Book.class);
q.setParameter(1, 1);
Book b = (Book) q.getSingleResult();
Assert.assertTrue(b instanceof Book);
Assert.assertEquals(new Long(1), ((Book)b).getId());
em.getTransaction().commit();
em.close();
}
/**
* 根据评论Id查询评论在表的第几行
* @param commentId 评论Id
* @param topicId 话题Id
* @param status 状态
* @param sort 按发表时间排序 1.desc 2.asc
* @return
*/
@Transactional(readOnly=true,propagation=Propagation.NOT_SUPPORTED)
public Long findRowByCommentId(Long commentId,Long topicId,Integer status,Integer sort){
String commentId_sql = "";
String sort_sql = "";
if(sort.equals(1)){
commentId_sql = " o.id >=?1";
sort_sql = " desc";
}else{
commentId_sql = " o.id <=?1";
sort_sql = " asc";
}
Query query = em.createQuery("select count(o.id) from Comment o where "+commentId_sql+" and o.topicId= ?2 and o.status= ?3 order by o.postTime"+sort_sql);
//给SQL语句设置参数
query.setParameter(1, commentId);
query.setParameter(2, topicId);
query.setParameter(3, status);
return (Long)query.getSingleResult();
}
@Override
public List findTop(String jpql, int topCount, Map<String, Object> values) {
Query query = entityManager.createQuery(jpql);
query.setFirstResult(0);
query.setMaxResults(topCount);
if (values != null && values.size() > 0) {
for (Entry<String, Object> e : values.entrySet()) {
query.setParameter(e.getKey(), e.getValue());
}
}
List list = query.getResultList();
return list;
}
@SuppressWarnings("unchecked")
public void retrieveThing(StringBuilder builder) throws SystemException, NamingException {
// Look up the EntityManager in JNDI
Context ctx = new InitialContext();
EntityManager em = (EntityManager) ctx.lookup(JNDI_NAME);
// Compose a JPQL query
String query = "SELECT t FROM Thing t";
Query q = em.createQuery(query);
// Execute the query
List<Thing> things = q.getResultList();
builder.append("Query returned " + things.size() + " things").append(newline);
// Let's see what we got back!
for (Thing thing : things) {
builder.append("Thing in list " + thing).append(newline);
}
}
public List<Relation> relationsBySourceAndObject( final RelationLinkImpl source, final RelationLinkImpl object )
{
EntityManager em = daoManager.getEntityManagerFactory().createEntityManager();
List<Relation> result = Lists.newArrayList();
try
{
Query qr = em.createQuery( "SELECT ss FROM RelationImpl AS ss" + " WHERE ss.source.uniqueIdentifier=:source"
+ " AND ss.trustedObject.uniqueIdentifier=:trustedObject" + " ORDER BY ss.relationStatus DESC" );
qr.setParameter( "source", source.getUniqueIdentifier() );
qr.setParameter( "trustedObject", object.getUniqueIdentifier() );
result.addAll( qr.getResultList() );
}
catch ( Exception ex )
{
logger.warn( "Error querying for trust relation.", ex );
}
finally
{
daoManager.closeEntityManager( em );
}
return result;
}
@Test
public void executeQueryLoadTemplateServices() {
// given
Query q = mock(Query.class);
when(ds.createNamedQuery(anyString())).thenReturn(q);
doReturn(new ArrayList<Product>()).when(q).getResultList();
Organization vendor = new Organization();
vendor.setKey(1L);
// when
partnerBean.executeQueryLoadTemplateServices(
EnumSet.of(ServiceType.TEMPLATE), vendor);
// then
verify(q).setParameter("vendorKey", Long.valueOf(vendor.getKey()));
verify(q)
.setParameter("productTypes", EnumSet.of(ServiceType.TEMPLATE));
verify(q).setParameter("filterOutWithStatus",
EnumSet.of(ServiceStatus.OBSOLETE, ServiceStatus.DELETED));
}
@Test
@SuppressWarnings("unchecked")
public void testEntityManagerProxyIsProxy() {
EntityManager em = createContainerManagedEntityManager();
assertTrue(Proxy.isProxyClass(em.getClass()));
Query q = em.createQuery("select p from Person as p");
List<Person> people = q.getResultList();
assertTrue(people.isEmpty());
assertTrue("Should be open to start with", em.isOpen());
try {
em.close();
fail("Close should not work on container managed EM");
}
catch (IllegalStateException ex) {
// OK
}
assertTrue(em.isOpen());
}
/**
* Deactivates and detaches all services of the given supplier on the given
* marketplace. For each service of the supplier that published on the
* marketplace the service is first deactivated and then the service is
* 'detached' from the marketplace. This affects the customer specific
* copies as well.
*
* @param mp
* the marketplace
* @param supplier
* the supplier
* @param dm
* the data service
*/
private void unlinkServices(Marketplace mp, Organization supplier,
DataService dm) {
String mId = mp.getMarketplaceId();
Query query = dm
.createNamedQuery("Product.getProductsForVendorOnMarketplace");
query.setParameter("marketplaceId", mId);
query.setParameter("vendorKey", Long.valueOf(supplier.getKey()));
List<Product> productList = ParameterizedTypes
.list(query.getResultList(), Product.class);
if (productList != null) {
for (Product product : productList) {
if (product.getStatus() == ServiceStatus.ACTIVE) {
product.setStatus(ServiceStatus.INACTIVE);
}
deactivateCustomerServices(product);
for (CatalogEntry ce : product.getCatalogEntries()) {
Marketplace ceMp = ce.getMarketplace();
if (ceMp != null && mId.equals(ceMp.getMarketplaceId())) {
ce.setMarketplace(null);
}
}
}
}
}
@SuppressWarnings("unchecked")
public List<PlatformUser> executeQueryGroupUsers(
PaginationUsersInUnit pagination, String userGroupKey) {
String nativeQuery = getQueryGroupUsers(pagination);
Query query = dm.createNativeQuery(nativeQuery, PlatformUser.class);
query.setParameter("organization_key",
Long.valueOf(dm.getCurrentUser().getOrganization().getKey()));
if (userGroupKey == null || userGroupKey.trim().equals("")) {
userGroupKey = "0";
}
query.setParameter("userGroup_key", Long.valueOf(userGroupKey));
setPaginationParameters(pagination, query);
return query.getResultList();
}
@Transactional(propagation = Propagation.NOT_SUPPORTED)
@SuppressWarnings("unchecked")
public void testQueryNoPersonsNotTransactional() {
EntityManager em = entityManagerFactory.createEntityManager();
Query q = em.createQuery("select p from Person as p");
List<Person> people = q.getResultList();
assertEquals(0, people.size());
try {
assertNull(q.getSingleResult());
fail("Should have thrown NoResultException");
}
catch (NoResultException ex) {
// expected
}
}
/**
* 查询模板版块
* @param dirName 模板目录名称
* @param layoutType 布局类型
* @param layoutFile 布局文件
* @return
*/
@Transactional(readOnly=true,propagation=Propagation.NOT_SUPPORTED)
public List<Forum> findForum(String dirName, Integer layoutType,String layoutFile){
Query query = em.createQuery("select o from Forum o where o.dirName=?1 and o.layoutType=?2 and o.layoutFile=?3");
//给SQL语句设置参数
query.setParameter(1, dirName);
query.setParameter(2, layoutType);
query.setParameter(3, layoutFile);
return query.getResultList();
}
@Transactional
private List<ImageCacheVO> getImageCacheToDelete() {
String sql = "select i from ImageCacheVO i where i.imageUuid = NULL and i.state = :state";
TypedQuery<ImageCacheVO> q = dbf.getEntityManager().createQuery(sql, ImageCacheVO.class);
q.setLockMode(LockModeType.PESSIMISTIC_WRITE);
q.setParameter("state", ImageCacheState.ready);
List<ImageCacheVO> ret = q.getResultList();
// if ImageCacheVO in deleting state and it has been stayed for 1 day
// that means zstack that issued garbage collection exited before removing this entry from database
// we garbage this entry again here
sql = "select i from ImageCacheVO i where i.imageUuid = NULL and i.state = :state and CURRENT_TIMESTAMP > DATE_ADD(i.lastOpDate, INTERVAL 1 DAY)";
Query q1 = dbf.getEntityManager().createNativeQuery(sql, ImageCacheVO.class);
q1.setLockMode(LockModeType.PESSIMISTIC_WRITE);
q1.setParameter("state", ImageCacheState.deleting);
ret.addAll(q1.getResultList());
if (ret.isEmpty()) {
return ret;
}
List<Long> ids = new ArrayList<Long>(ret.size());
for (ImageCacheVO i : ret) {
ids.add(i.getId());
}
sql = "update ImageCacheVO i set i.state = :state where i.id in (:ids)";
TypedQuery<ImageCacheVO> q2 = dbf.getEntityManager().createQuery(sql, ImageCacheVO.class);
q2.setParameter("state", ImageCacheState.deleting);
q2.setParameter("ids", ids);
q2.executeUpdate();
return ret;
}
@Before
public void setup() {
exportBillingService = new ExportBillingDataServiceBean();
exportBillingService.accountService = mock(AccountService.class);
exportBillingService.idService = mock(IdentityService.class);
exportBillingService.billingService = mock(BillingService.class);
exportBillingService.dm = mock(DataService.class);
createAndSetCurrentOrg();
Query query = mock(Query.class);
doReturn(query).when(exportBillingService.dm).createQuery(anyString());
}
@DisableQuickPerf
@ExpectSelect(5)
@Test
public void execute_one_select_but_five_select_expected() {
EntityManager em = emf.createEntityManager();
Query query = em.createQuery("FROM " + Book.class.getCanonicalName());
query.getResultList();
}
/**
* 为查询对象设定参数,注意:如果是JPA,则索引位要加一
* @param query
* @param params
*/
protected void setParameter(Query query, Object[] params){
if(params != null){
for (int i = 0; i < params.length; i++) {
query.setParameter(i+1, params[i]);
}
}
}
@Override
public Owner findById(int id) {
// using 'join fetch' because a single query should load both owners and pets
// using 'left join fetch' because it might happen that an owner does not have pets yet
Query query = this.em.createQuery("SELECT owner FROM Owner owner left join fetch owner.pets WHERE owner.id =:id");
query.setParameter("id", id);
return (Owner) query.getSingleResult();
}
/**
* 根据角色Id查询角色组
* @param userRoleId 角色Id
* @param userName 用户名称
* @return
*/
@Transactional(readOnly=true,propagation=Propagation.NOT_SUPPORTED)
public UserRoleGroup findRoleGroupByUserRoleId(String userRoleId,String userName){
Query query = em.createQuery("select o from UserRoleGroup o where o.userRoleId=?1 and o.userName=?2");
query.setParameter(1, userRoleId);
query.setParameter(2, userName);
List<UserRoleGroup> userRoleGroupList = query.getResultList();
if(userRoleGroupList != null && userRoleGroupList.size() >0){
for(UserRoleGroup userRoleGroup : userRoleGroupList){
return userRoleGroup;
}
}
return null;
}
@Test
public void test37createUserSearchQuery() {
EntityManager entityManager = Mockito.mock(EntityManager.class);
String queryString="Select id,loginId,emailAddress,firstName,lastName,statusList,publicScreenName,status from XXPortalUser";
Query query = Mockito.mock(Query.class);
SearchCriteria searchCriteria = new SearchCriteria();
searchCriteria.setDistinct(true);
searchCriteria.setGetChildren(true);
searchCriteria.setGetCount(true);
searchCriteria.setMaxRows(12);
searchCriteria.setOwnerId(userId);
searchCriteria.setStartIndex(1);
searchCriteria.setSortBy("asc");
VXPortalUser vXPortalUser=userProfile();
List<String> userRoleList = new ArrayList<String>();
userRoleList.add("ROLE_USER");
List<Integer> statusList = new ArrayList<Integer>();
statusList.add(1);
searchCriteria.addParam("roleList", userRoleList);
searchCriteria.addParam("userId", vXPortalUser.getId());
searchCriteria.addParam("loginId", vXPortalUser.getLoginId());
searchCriteria.addParam("emailAddress", vXPortalUser.getEmailAddress());
searchCriteria.addParam("firstName", vXPortalUser.getFirstName());
searchCriteria.addParam("lastName", vXPortalUser.getLastName());
searchCriteria.addParam("statusList", statusList);
searchCriteria.addParam("publicScreenName", vXPortalUser.getPublicScreenName());
searchCriteria.addParam("status", vXPortalUser.getStatus());
searchCriteria.addParam("familyScreenName", vXPortalUser.getPublicScreenName());
Mockito.when(daoManager.getEntityManager()).thenReturn(entityManager);
Mockito.when(entityManager.createQuery(Mockito.anyString())).thenReturn(query);
Query newQuery = userMgr.createUserSearchQuery(query.toString(),queryString,searchCriteria);
Assert.assertNotNull(newQuery);
userRoleList.add("ROLE_SYS_ADMIN");
statusList.add(0);
searchCriteria.addParam("statusList", statusList);
searchCriteria.addParam("roleList", userRoleList);
newQuery = userMgr.createUserSearchQuery(query.toString(),queryString,searchCriteria);
}
/**
* 修改回复状态
* @param answerReplyId 回复Id
* @param status 状态
* @return
*/
public int updateReplyStatus(Long answerReplyId,Integer status){
Query query = em.createQuery("update AnswerReply o set o.status=?1 where o.id=?2")
.setParameter(1, status)
.setParameter(2, answerReplyId);
return query.executeUpdate();
}
/**
* 修改评论状态
* @param commentId 评论Id
* @param status 状态
* @return
*/
public int updateCommentStatus(Long commentId,Integer status){
Query query = em.createQuery("update Comment o set o.status=?1 where o.id=?2")
.setParameter(1, status)
.setParameter(2, commentId);
return query.executeUpdate();
}
@SuppressWarnings("unchecked")
public void testQueryNoPersons() {
EntityManager em = entityManagerFactory.createEntityManager();
Query q = em.createQuery("select p from Person as p");
List<Person> people = q.getResultList();
assertEquals(0, people.size());
try {
assertNull(q.getSingleResult());
fail("Should have thrown NoResultException");
}
catch (NoResultException ex) {
// expected
}
}
private void deletePermission(List<String> permissionIdList){
if(permissionIdList != null && permissionIdList.size() >0){
Query query = em.createQuery("delete from SysPermission o where o.id in(:permissionId)")
.setParameter("permissionId",permissionIdList);
query.executeUpdate();
}
}
@Test
@DisableQueriesWithoutBindParameters
public void test_method() {
EntityManager em = emf.createEntityManager();
String sql = "select * from book where title = 'or isbn = ?' or isbn = :isbn";
Query nativeQuery = em.createNativeQuery(sql)
.setParameter("isbn", "978-0321356680");
nativeQuery.getResultList();
}
/**
* 取得所有模块的权限
*/
@Transactional(readOnly=true,propagation=Propagation.NOT_SUPPORTED)
public List<PermissionObject> findModulePermission(){
Query query = em.createQuery("" +
"select b.url, c.methods,c.name,b.module,b.urlParentId,c.id,c.remarks from SysPermissionResources a,SysResources b," +
"SysPermission c where a.resourceId = b.id and " +
"a.permissionId = c.id ORDER BY b.priority ASC , c.priority ASC");
query.getResultList();
List<PermissionObject> permissionObjectList = new ArrayList<PermissionObject>();
Iterator iter = query.getResultList().iterator();
while (iter.hasNext()){
Object[] o = (Object[]) iter.next();
PermissionObject permissionObject = new PermissionObject();
String url = (String) o[0];
String methods = (String) o[1];
String permissionName = (String) o[2];
String module = (String) o[3];
String urlParentId = (String) o[4];
String permissionId = (String) o[5];
String remarks = (String) o[6];
permissionObject.setUrl(url);
permissionObject.setMethods(methods);
permissionObject.setPermissionName(permissionName);
permissionObject.setModule(module);
if(urlParentId != null && !"".equals(urlParentId.trim())){
permissionObject.setAppendUrl(true);
}else{
permissionObject.setAppendUrl(false);
}
permissionObject.setPermissionId(permissionId);
permissionObject.setRemarks(remarks);
permissionObjectList.add(permissionObject);
}
return permissionObjectList;
}
@GET
@Produces({"application/xml", "application/json"})
@Path("/range/")
public HeatMapRange findRange() {
Query baseRangeQuery = em.createQuery(RANGE_QUERY);
HeatMapRange result = new HeatMapRange();
Object[] queryResult = (Object[]) baseRangeQuery.getSingleResult();
result.setMaxDate((Date) queryResult[0]);
result.setMinDate((Date) queryResult[1]);
return result;
}