下面列出了javax.persistence.EntityManager#createQuery ( ) 实例代码，或者点击链接到github查看源代码，也可以在右侧发表评论。
@Test
public void callSizeFunction() {
log.info("... callSizeFunction ...");
EntityManager em = emf.createEntityManager();
em.getTransaction().begin();
Query q = em.createQuery("SELECT a, size(a.books) FROM Author a GROUP BY a.id");
@SuppressWarnings("unchecked")
List<Object[]> results = q.getResultList();
for (Object[] r : results) {
log.info(r[0] + " wrote " + r[1] + " books.");
}
em.getTransaction().commit();
em.close();
}
@Test
public void adHocJpqlQuery() {
log.info("... adHocJpqlQuery ...");
EntityManager em = emf.createEntityManager();
em.getTransaction().begin();
TypedQuery<Book> q = em.createQuery("SELECT b FROM Book b WHERE b.id = :id", Book.class);
q.setParameter("id", 1L);
Book b = q.getSingleResult();
Assert.assertTrue(b instanceof Book);
Assert.assertEquals(new Long(1), ((Book)b).getId());
em.getTransaction().commit();
em.close();
}
public RelationLink getRelationLinkByUniqueId( final String uniqueIdentifier )
{
EntityManager em = daoManager.getEntityManagerFactory().createEntityManager();
RelationLink result = null;
try
{
Query qr = em.createQuery(
"SELECT ss FROM RelationLinkImpl AS ss" + " WHERE ss.uniqueIdentifier=:uniqueIdentifier" );
qr.setParameter( "uniqueIdentifier", uniqueIdentifier );
List<RelationLink> list = qr.getResultList();
if ( !list.isEmpty() )
{
result = list.get( 0 );
}
}
catch ( Exception ex )
{
logger.warn( "Error querying for trust item.", ex );
}
finally
{
daoManager.closeEntityManager( em );
}
return result;
}
/**
* @see io.apiman.manager.api.core.IStorage#getAllContracts(java.lang.String, java.lang.String, java.lang.String)
*/
@Override
public Iterator<ContractBean> getAllContracts(String organizationId, String clientId, String version)
throws StorageException {
EntityManager entityManager = getActiveEntityManager();
String jpql =
"SELECT c from ContractBean c " +
" JOIN c.client clientv " +
" JOIN clientv.client client " +
" JOIN client.organization aorg" +
" WHERE client.id = :clientId " +
" AND aorg.id = :orgId " +
" AND clientv.version = :version " +
" ORDER BY c.id ASC";
Query query = entityManager.createQuery(jpql);
query.setParameter("orgId", organizationId);
query.setParameter("clientId", clientId);
query.setParameter("version", version);
return getAll(ContractBean.class, query);
}
/**
* Gets all order items for the given productId.
* @param productId The id of the product ordered.
* @param start The index of the first orderItem to return. Negative value to start at the beginning.
* @param limit The maximum number of orderItem to return. Negative value to return all.
* @return List of order items with the specified product.
*/
public List<PersistenceOrderItem> getAllEntitiesWithProduct(long productId, int start, int limit) {
List<PersistenceOrderItem> entities = null;
EntityManager em = getEM();
try {
em.getTransaction().begin();
PersistenceProduct prod = em.find(PersistenceProduct.class, productId);
if (prod != null) {
TypedQuery<PersistenceOrderItem> allMatchesQuery =
em.createQuery("SELECT u FROM " + getEntityClass().getName()
+ " u WHERE u.product = :prod", getEntityClass());
allMatchesQuery.setParameter("prod", prod);
entities = resultsWithStartAndLimit(em, allMatchesQuery, start, limit);
}
em.getTransaction().commit();
} finally {
em.close();
}
if (entities == null) {
return new ArrayList<PersistenceOrderItem>();
}
return entities;
}
/**
* Method to remove the audit trail of the cluster.
*
* @param clusterId
*/
public void removeAuditTrail(Long clusterId) {
try {
// Get entity manager.
EntityManager em = HibernateUtils.getEntityManager();
// Get the transaction
EntityTransaction tc = em.getTransaction();
// begin the transaction
tc.begin();
// build the query.
Query query = em
.createQuery("delete from com.impetus.ankush.common.domain.Configuration_AUD where clusterId=:clusterId");
// setting the cluster id parameter in query.
query.setParameter(
com.impetus.ankush2.constant.Constant.Keys.CLUSTERID,
clusterId);
// execute the query
query.executeUpdate();
// commit the transaction
tc.commit();
} catch (Exception e) {
LOG.error(e.getMessage(), e);
}
}
@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);
}
}
/**
* @return Either the newest product, or null it if does not exist
*/
public static Product selectNewestProduct() {
EntityManager em = DBUtil.getEmFactory().createEntityManager();
String queryString = "SELECT p FROM Product p "
+ "ORDER BY p.productId";
Query query = em.createQuery(queryString);
Product product = null;
try {
List<Product> products = query.getResultList();
product = products.get(products.size() - 1);
} catch (Exception e) {
System.err.println(e);
} finally {
em.close();
}
return product;
}
@Override
public void preDelete(EntityManager em) {
super.preDelete(em);
Query query = em.createQuery("Delete from Issue p where p.tracker = :tracker");
query.setParameter("tracker", detach());
query.executeUpdate();
}
@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();
}
public Relation findBySourceTargetObject( final RelationLink source, final RelationLink target,
final RelationLink object )
{
EntityManager em = daoManager.getEntityManagerFactory().createEntityManager();
Relation result = null;
try
{
Query qr = em.createQuery( "select ss from RelationImpl AS ss" + " WHERE ss.source.uniqueIdentifier=:source"
+ " AND ss.target.uniqueIdentifier=:target"
+ " AND ss.trustedObject.uniqueIdentifier=:trustedObject" );
qr.setParameter( "source", source.getUniqueIdentifier() );
qr.setParameter( "target", target.getUniqueIdentifier() );
qr.setParameter( "trustedObject", object.getUniqueIdentifier() );
List<Relation> list = qr.getResultList();
if ( !list.isEmpty() )
{
result = list.get( 0 );
}
}
catch ( Exception ex )
{
logger.warn( "Error querying for trust relation.", ex );
}
finally
{
daoManager.closeEntityManager( em );
}
return result;
}
public TicketCategory fromDTO(TicketCategory entity, EntityManager em)
{
if (entity == null)
{
entity = new TicketCategory();
}
if (this.id != null)
{
TypedQuery<TicketCategory> findByIdQuery = em
.createQuery(
"SELECT DISTINCT t FROM TicketCategory t WHERE t.id = :entityId",
TicketCategory.class);
findByIdQuery.setParameter("entityId", this.id);
try
{
entity = findByIdQuery.getSingleResult();
}
catch (javax.persistence.NoResultException nre)
{
entity = null;
}
return entity;
}
entity.setDescription(this.description);
entity = em.merge(entity);
return entity;
}
@Test
@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
}
}
public <T extends JpaObject> List<String> idsGreaterThan(Class<T> cls, String attribute, Object value)
throws Exception {
EntityManager em = this.get(cls);
String str = "SELECT o.id FROM " + cls.getCanonicalName() + " o where o." + attribute + " > ?1";
TypedQuery<String> query = em.createQuery(str, String.class);
query.setParameter(1, value);
List<String> os = query.getResultList();
return new ArrayList<>(os);
}
public void removeAllRelationsWithLink( RelationLink link )
{
EntityManager em = daoManager.getEntityManagerFactory().createEntityManager();
try
{
daoManager.startTransaction( em );
Query qr = em.createQuery( "DELETE FROM RelationImpl AS rln" + " WHERE rln.source.uniqueIdentifier=:id"
+ " OR rln.target.uniqueIdentifier=:id" + " OR rln.trustedObject.uniqueIdentifier=:id" );
qr.setParameter( "id", link.getUniqueIdentifier() );
qr.executeUpdate();
qr = em.createQuery( "DELETE FROM RelationLinkImpl AS link" + " WHERE link.uniqueIdentifier=:id" );
qr.setParameter( "id", link.getUniqueIdentifier() );
qr.executeUpdate();
daoManager.commitTransaction( em );
}
catch ( Exception ex )
{
daoManager.rollBackTransaction( em );
}
finally
{
daoManager.closeEntityManager( em );
}
}
/**
* 根据条件搜索中心工作ID
* @param id
* @param count
* @param sequence
* @param com.x.okr.assemble.control.jaxrs.okrcenterworkinfo.WrapInFilter wrapIn
* @return
* @throws Exception
*/
@SuppressWarnings("unchecked")
public List<OkrWorkPerson> listCenterWorkPersonNextWithFilter(String id, Integer count, Object sequence,
com.x.okr.assemble.control.jaxrs.WorkCommonQueryFilter wrapIn) throws Exception {
// 先获取上一页最后一条的sequence值，如果有值的话，以此sequence值作为依据取后续的count条数据
EntityManager em = this.entityManagerContainer().get( OkrWorkPerson.class );
String order = wrapIn.getOrder();// 排序方式
List<Object> vs = new ArrayList<>();
StringBuffer sql_stringBuffer = new StringBuffer();
if (order == null || order.isEmpty()) {
order = "DESC";
}
Integer index = 1;
sql_stringBuffer.append("SELECT o FROM " + OkrWorkPerson.class.getCanonicalName() + " o where o.workId is null and o.processIdentity = '观察者' ");
if ((null != sequence)) {
sql_stringBuffer.append(" and o." + wrapIn.getSequenceField() + " " + (StringUtils.equalsIgnoreCase(order, "DESC") ? "<" : ">") + (" ?" + (index)));
vs.add(sequence);
index++;
}
//根据标题模糊查询
if (null != wrapIn.getTitle() && !wrapIn.getTitle().isEmpty()) {
sql_stringBuffer.append(" and o.centerTitle like ?" + (index));
vs.add("%" + wrapIn.getTitle() + "%");
index++;
}
//根据信息状态查询，比如：正常，已删除
if (null != wrapIn.getQ_statuses() && wrapIn.getQ_statuses().size() > 0) {
sql_stringBuffer.append(" and o.status in ( ?" + (index) + " )");
vs.add(wrapIn.getQ_statuses());
index++;
}
//根据默认的工作类别查询
if (null != wrapIn.getDefaultWorkTypes() && wrapIn.getDefaultWorkTypes().size() > 0) {
sql_stringBuffer.append(" and o.workType in ( ?" + (index) + " )");
vs.add(wrapIn.getDefaultWorkTypes());
index++;
}
//根据用户身份查询查询
if (null != wrapIn.getIdentity() && !wrapIn.getIdentity().isEmpty() ) {
sql_stringBuffer.append(" and o.employeeIdentity = ?" + (index) );
vs.add(wrapIn.getIdentity());
index++;
}
sql_stringBuffer.append(" order by o." + wrapIn.getSequenceField() + " " + (StringUtils.equalsIgnoreCase(order, "DESC") ? "DESC" : "ASC"));
Query query = em.createQuery( sql_stringBuffer.toString(), OkrWorkPerson.class );
for (int i = 0; i < vs.size(); i++) {
query.setParameter(i + 1, vs.get(i));
}
return query.setMaxResults(count).getResultList();
}
/**
* @see io.apiman.manager.api.core.IStorageQuery#getApiVersions(java.lang.String, java.lang.String)
*/
@Override
public List<ApiVersionSummaryBean> getApiVersions(String orgId, String apiId)
throws StorageException {
beginTx();
try {
EntityManager entityManager = getActiveEntityManager();
String jpql =
"SELECT v "
+ " FROM ApiVersionBean v"
+ " JOIN v.api s"
+ " JOIN s.organization o"
+ " WHERE o.id = :orgId"
+ " AND s.id = :apiId"
+ " ORDER BY v.createdOn DESC";
Query query = entityManager.createQuery(jpql);
query.setMaxResults(500);
query.setParameter("orgId", orgId);
query.setParameter("apiId", apiId);
List<ApiVersionBean> apiVersions = query.getResultList();
List<ApiVersionSummaryBean> rval = new ArrayList<>(apiVersions.size());
for (ApiVersionBean apiVersion : apiVersions) {
ApiVersionSummaryBean svsb = new ApiVersionSummaryBean();
svsb.setOrganizationId(apiVersion.getApi().getOrganization().getId());
svsb.setOrganizationName(apiVersion.getApi().getOrganization().getName());
svsb.setId(apiVersion.getApi().getId());
svsb.setName(apiVersion.getApi().getName());
svsb.setDescription(apiVersion.getApi().getDescription());
svsb.setVersion(apiVersion.getVersion());
svsb.setStatus(apiVersion.getStatus());
svsb.setPublicAPI(apiVersion.isPublicAPI());
rval.add(svsb);
}
return rval;
} catch (Throwable t) {
logger.error(t.getMessage(), t);
throw new StorageException(t);
} finally {
rollbackTx();
}
}
public boolean deleteUser() {
try {
checkAdmin();
EntityManager em = AdminDatabase.instance().getFactory().createEntityManager();
try {
Query query = em.createQuery("Select count(p) from BotInstance p where p.creator = :user");
query.setParameter("user", this.viewUser);
if (((Number)query.getSingleResult()).intValue() > 0) {
throw new BotException("You must first delete your bots");
}
query = em.createQuery("Select count(p) from ChatChannel p where p.creator = :user");
query.setParameter("user", this.viewUser);
if (((Number)query.getSingleResult()).intValue() > 0) {
throw new BotException("You must first delete your channels");
}
query = em.createQuery("Select count(p) from Forum p where p.creator = :user");
query.setParameter("user", this.viewUser);
if (((Number)query.getSingleResult()).intValue() > 0) {
throw new BotException("You must first delete your forums");
}
query = em.createQuery("Select count(p) from Analytic p where p.creator = :user");
query.setParameter("user", this.viewUser);
if (((Number)query.getSingleResult()).intValue() > 0) {
throw new BotException("You must first delete your analytics");
}
query = em.createQuery("Select count(p) from Graphic p where p.creator = :user");
query.setParameter("user", this.viewUser);
if (((Number)query.getSingleResult()).intValue() > 0) {
throw new BotException("You must first delete your graphics");
}
query = em.createQuery("Select count(p) from Avatar p where p.creator = :user");
query.setParameter("user", this.viewUser);
if (((Number)query.getSingleResult()).intValue() > 0) {
throw new BotException("You must first delete your avatars");
}
query = em.createQuery("Select count(p) from Script p where p.creator = :user");
query.setParameter("user", this.viewUser);
if (((Number)query.getSingleResult()).intValue() > 0) {
throw new BotException("You must first delete your scripts");
}
query = em.createQuery("Select count(p) from IssueTracker p where p.creator = :user");
query.setParameter("user", this.viewUser);
if (((Number)query.getSingleResult()).intValue() > 0) {
throw new BotException("You must first delete your issue trackers");
}
} finally {
em.close();
}
AdminDatabase.instance().deleteUser(this.viewUser.getUserId());
setViewUser(null);
if (!isSuper()) {
logout();
}
} catch (Exception failed) {
error(failed);
return false;
}
return true;
}
/**
* 查询符合的文档信息总数
* @param id
* @param count
* @param sequence
* @param wrapIn
* @return
* @throws Exception
*/
public long getCountWithFilter( WrapInFilterStatisticPersonForMonth wrapIn ) throws Exception {
//先获取上一页最后一条的sequence值，如果有值的话，以此sequence值作为依据取后续的count条数据
EntityManager em = this.entityManagerContainer().get( StatisticPersonForMonth.class );
List<Object> vs = new ArrayList<>();
StringBuffer sql_stringBuffer = new StringBuffer();
Integer index = 1;
sql_stringBuffer.append( "SELECT count(o.id) FROM "+StatisticPersonForMonth.class.getCanonicalName()+" o where 1=1" );
if ((null != wrapIn.getEmployeeName()) && wrapIn.getEmployeeName().size() > 0) {
sql_stringBuffer.append(" and o.employeeName in ?" + (index));
vs.add( wrapIn.getEmployeeName() );
index++;
}
if ((null != wrapIn.getUnitName()) && wrapIn.getUnitName().size() > 0 ) {
sql_stringBuffer.append(" and o.unitName in ?" + (index));
vs.add( wrapIn.getUnitName() );
index++;
}
if ((null != wrapIn.getTopUnitName()) && wrapIn.getTopUnitName().size() > 0 ) {
sql_stringBuffer.append(" and o.topUnitName in ?" + (index));
vs.add( wrapIn.getTopUnitName() );
index++;
}
if ((null != wrapIn.getStatisticYear() ) && (!wrapIn.getStatisticYear().isEmpty())) {
sql_stringBuffer.append(" and o.statisticYear = ?" + (index));
vs.add( wrapIn.getStatisticYear() );
index++;
}
if ((null != wrapIn.getStatisticMonth()) && (!wrapIn.getStatisticMonth().isEmpty())) {
sql_stringBuffer.append(" and o.statisticMonth = ?" + (index));
vs.add( wrapIn.getStatisticMonth() );
index++;
}
Query query = em.createQuery( sql_stringBuffer.toString(), StatisticPersonForMonth.class );
//为查询设置所有的参数值
for (int i = 0; i < vs.size(); i++) {
query.setParameter(i + 1, vs.get(i));
}
return (Long) query.getSingleResult();
}
/**
* 查询符合的文档信息总数
* @param wrapIn
* @return
* @throws Exception
*/
public long getCountWithFilter( WrapInFilterAppeal wrapIn ) throws Exception {
//先获取上一页最后一条的sequence值，如果有值的话，以此sequence值作为依据取后续的count条数据
EntityManager em = this.entityManagerContainer().get( AttendanceAppealInfo.class );
List<Object> vs = new ArrayList<>();
StringBuffer sql_stringBuffer = new StringBuffer();
Integer index = 1;
sql_stringBuffer.append( "SELECT count(o.id) FROM "+AttendanceAppealInfo.class.getCanonicalName()+" o where 1=1" );
if ((null != wrapIn.getDetailId()) && (!wrapIn.getDetailId().isEmpty())) {
sql_stringBuffer.append(" and o.detailId = ?" + (index));
vs.add( wrapIn.getDetailId() );
index++;
}
if ((null != wrapIn.getEmpName()) && (!wrapIn.getEmpName().isEmpty())) {
sql_stringBuffer.append(" and o.empName = ?" + (index));
vs.add( wrapIn.getEmpName() );
index++;
}
if ((null != wrapIn.getUnitName()) && (!wrapIn.getUnitName().isEmpty())) {
sql_stringBuffer.append(" and o.unitName = ?" + (index));
vs.add( wrapIn.getUnitName() );
index++;
}
if ((null != wrapIn.getTopUnitName()) && (!wrapIn.getTopUnitName().isEmpty())) {
sql_stringBuffer.append(" and o.topUnitName = ?" + (index));
vs.add( wrapIn.getTopUnitName() );
index++;
}
if ((null != wrapIn.getYearString() ) && (!wrapIn.getYearString().isEmpty())) {
sql_stringBuffer.append(" and o.yearString = ?" + (index));
vs.add( wrapIn.getYearString() );
index++;
}
if ((null != wrapIn.getMonthString()) && (!wrapIn.getMonthString().isEmpty())) {
sql_stringBuffer.append(" and o.monthString = ?" + (index));
vs.add( wrapIn.getMonthString() );
index++;
}
if (wrapIn.getStatus()!=999) {
sql_stringBuffer.append(" and o.status = ?" + (index));
vs.add( wrapIn.getStatus() );
index++;
}
if ((null != wrapIn.getAppealReason()) && (!wrapIn.getAppealReason().isEmpty())) {
sql_stringBuffer.append(" and o.appealReason = ?" + (index));
vs.add( wrapIn.getAppealReason() );
index++;
}
if ((null != wrapIn.getProcessPerson1()) && (!wrapIn.getProcessPerson1().isEmpty())) {
sql_stringBuffer.append(" and o.processPerson1 = ?" + (index));
vs.add( wrapIn.getProcessPerson1() );
index++;
}
if ((null != wrapIn.getProcessPerson2()) && (!wrapIn.getProcessPerson2().isEmpty())) {
sql_stringBuffer.append(" and o.processPerson2 = ?" + (index));
vs.add( wrapIn.getProcessPerson2() );
index++;
}
//添加OR
if (wrapIn.getOrAtrribute() != null && wrapIn.getOrAtrribute().size() > 0) {
sql_stringBuffer.append(" and (");
NameValueCountPair nameValueCountPair = null;
for (int p = 0; p < wrapIn.getOrAtrribute().size(); p++) {
nameValueCountPair = wrapIn.getOrAtrribute().get(p);
if (p == 0) {
sql_stringBuffer.append(" o." + nameValueCountPair.getName() + " = ?" + (index));
} else {
sql_stringBuffer.append(" or o." + nameValueCountPair.getName() + " = ?" + (index));
}
vs.add(nameValueCountPair.getValue());
index++;
}
sql_stringBuffer.append(" )");
}
Query query = em.createQuery( sql_stringBuffer.toString(), AttendanceAppealInfo.class );
//为查询设置所有的参数值
for (int i = 0; i < vs.size(); i++) {
query.setParameter(i + 1, vs.get(i));
}
return (Long) query.getSingleResult();
}