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

/**
 *
 * @param sid
 * @param did
 * @param username
 * @param fkid - Unique identifier for a key
 * @return - Returns a Key identified by the fkid parameter.
 */
@Override
public FidoKeys getByfkid(Short sid, Long did, String username, Long fkid) throws SKFEException {
    try {
        Query q = em.createNamedQuery("FidoKeys.findBySidDidFkid");
        q.setHint("javax.persistence.cache.storeMode", "REFRESH");
        q.setParameter("fkid", fkid);
        q.setParameter("did", did);
        q.setParameter("username", username);
        q.setParameter("sid", sid);
        FidoKeys rk = (FidoKeys) q.getSingleResult();
        if (rk != null) {
            verifyDBRecordSignature(did, rk);
        }
        return rk;
    } catch (NoResultException ex) {
        return null;
    }
}
 

@Transactional(readOnly = false, propagation = Propagation.REQUIRED)
public boolean update(String uuid, ITemplate template) {
    Template templateDB = null;
    try {
        Query query = entityManager.createNamedQuery(Template.QUERY_FIND_BY_UUID);
        query.setParameter("uuid", uuid);
        templateDB = (Template)query.getSingleResult();
    } catch (NoResultException e) {
        logger.debug("No Result found: " + e);
    }
    
    if (templateDB!=null){
        template.setId(templateDB.getId());
        logger.info("template to update with id"+template.getId());        
        entityManager.merge(template);
        entityManager.flush();
    }else
        return false;
    return true;
}
 

@Override
public Object getTables(String name, int[] startEnd) {
    // 使用预编译防止sql注入
    String sql = "select table_name ,create_time , engine, table_collation, table_comment from information_schema.tables " +
            "where table_schema = (select database()) " +
            "and table_name like ? order by create_time desc";
    Query query = em.createNativeQuery(sql);
    query.setFirstResult(startEnd[0]);
    query.setMaxResults(startEnd[1] - startEnd[0]);
    query.setParameter(1, StringUtils.isNotBlank(name) ? ("%" + name + "%") : "%%");
    List result = query.getResultList();
    List<TableInfo> tableInfo = new ArrayList<>();
    for (Object obj : result) {
        Object[] arr = (Object[]) obj;
        tableInfo.add(new TableInfo(arr[0], arr[1], arr[2], arr[3], ObjectUtil.isNotEmpty(arr[4]) ? arr[4] : "-"));
    }
    Query query1 = em.createNativeQuery("SELECT COUNT(*) from information_schema.tables where table_schema = (select database())");
    Object totalElements = query1.getSingleResult();
    return PageUtil.toPage(tableInfo, totalElements);
}
 

/**
 * 根据用户Id查询未读提醒数量
 * @param userId 用户Id
 * @return
 */
@Transactional(readOnly=true, propagation=Propagation.NOT_SUPPORTED)
public Long findUnreadRemindByUserId(Long userId){
	Long count = 0L;
	//表编号
	int tableNumber = remindConfig.userIdRemainder(userId);
	Query query  = null;
	
	if(tableNumber == 0){//默认对象
		query = em.createQuery("select count(o) from Remind o where o.receiverUserId=?1 and o.status =?2 ");
		query.setParameter(1, userId);
		query.setParameter(2, 10);
		count = (Long)query.getSingleResult();
		
	}else{//带下划线对象
		query = em.createQuery("select count(o) from Remind_"+tableNumber+" o where o.receiverUserId=?1 and o.status =?2 ");
		query.setParameter(1, userId);
		query.setParameter(2, 10);
		count = (Long)query.getSingleResult();
	}
	return count;
}
 

@Override
public Mentor findByID(int id) {
	EntityManager em = PersistenceManager.INSTANCE.getEntityManager();
	Query query = em.createQuery("FROM Mentor m WHERE m.id = :id").setParameter("id", id);
	Mentor result = (Mentor) query.getSingleResult();
	return result;
}
 

@Override
public Test retrieveTestFromAuditAndLabel(Audit audit, String testLabel) {
    Query query = entityManager.createQuery(
            "SELECT t FROM "
            + getAuditEntityClass().getName() + " a"
            + " LEFT JOIN a.testList t"
            + " WHERE t.label=:testLabel"
            + " AND a = :audit");
    query.setParameter("audit", audit);
    query.setParameter("testLabel", testLabel);
    try {
        return (Test)query.getSingleResult();
    } catch (NoResultException nre) {
        return null;
    }
}
 

@Transactional
public Long getCount() throws DataAccessException {
	try {
		Query query = createNamedQuery("RegularExpression.getCount",-1,-1);
		return  (Long) query.getSingleResult();
	} catch (NoResultException nre) {
		return null;
	}
}
 

@Override
public User findUserFromName(String name) {
    Query query = entityManager.createQuery("SELECT u FROM "
            + getEntityClass().getName() + " u"
            + " left join fetch u.contractSet c"
            + " WHERE u.name = :name");
    query.setParameter("name", name);
    try {
        return (User)query.getSingleResult();
    } catch (NoResultException e) {
        return null;
    }
}
 

public CloneWorkspace getWorkspace(String id) {
    String queryStr = "select w from CloneWorkspace w where"
            + "            w.id = :id";        
    Query query = config.getDatabase().getEntityManager().createQuery(queryStr, CloneWorkspace.class);
    query.setHint("javax.persistence.cache.storeMode", "REFRESH");
    query.setHint("eclipselink.cache-usage", "DoNotCheckCache");        
    
    query.setParameter("id", id);
    CloneWorkspace workspace = (CloneWorkspace)query.getSingleResult();
    
    return workspace;
}
 

@Transactional
private void queryCache(){
	_logger.warn("Query Cache");
	Query query = entityManager.createQuery("SELECT a FROM Account a WHERE a.accountId=:accountId", Account.class);
	query.setParameter("accountId", 7L);
	query.setHint("org.hibernate.cacheable", true);
	Account account = (Account)query.getSingleResult();
	_logger.warn(account);
}
 

@Override
public ParameterFamily findParameterFamilyFromCode(String parameterFamilyCode) {
    Query query = entityManager.createQuery("SELECT pf FROM "
            + getEntityClass().getName() + " pf"
            + " WHERE pf.paramFamilyCode = :parameterFamilyCode");
    query.setParameter("parameterFamilyCode", parameterFamilyCode);
    try {
        return (ParameterFamily) query.getSingleResult();
    } catch (NoResultException nre) {
        return null;
    } catch (NonUniqueResultException nure) {
        return null;
    }
}
 

@Transactional
public SurveyDefinitionPage findById(Long id) throws DataAccessException {
	try {
		Query query = createNamedQuery("SurveyDefinitionPage.findById", -1, -1, id);
		return (SurveyDefinitionPage) query.getSingleResult();
	} catch (NoResultException nre) {
		return null;
	}

}
 

@Override
public Long getWebServerForciblyStoppedCount(final String groupName) {
    final Query query = entityManager.createNamedQuery(JpaWebServer.QUERY_GET_WS_COUNT_BY_STATE_AND_GROUP_NAME);
    query.setParameter(JpaWebServer.QUERY_PARAM_STATE, WebServerReachableState.FORCED_STOPPED);
    query.setParameter(JpaWebServer.QUERY_PARAM_GROUP_NAME, groupName);
    return (Long) query.getSingleResult();
}
 

@Override
public Long findNumberOfOccurrencesByWebResourceAndResultType(
		Long webresourceId, TestSolution testSolution, boolean isManualAudit) {
	if (webresourceId == null) {
		return null;
	}
	String queryStringForAutomatic = "SELECT count(pk.id) FROM "
			+ getWebResourceEntityClass().getName() + " r"
			+ JOIN_PROCESS_RESULT + " JOIN pr.remarkSet pk"
			+ " WHERE (r.id=:id OR r.parent.id=:id)"
			+ " AND pr.definiteValue = :value" + " AND pk.issue = :value";
	String queryStringForManual = "SELECT count(pk.id) FROM "
			+ getWebResourceEntityClass().getName() + " r"
			+ JOIN_PROCESS_RESULT + " JOIN pr.remarkSet pk"
			+ " WHERE (r.id=:id OR r.parent.id=:id)"
			+ " AND pr.manualDefiniteValue = :value" + " AND pk.issue = :value";
	Query query;
	if(isManualAudit){
		query = entityManager.createQuery(queryStringForManual);
	} else {
		query = entityManager.createQuery(queryStringForAutomatic);
	}
	
	query.setParameter("id", webresourceId);
	query.setParameter("value", testSolution);
	try {
		return (Long) query.getSingleResult();
	} catch (NoResultException e) {
		return null;
	}
}
 

@Override
public Long getWebServerStoppedCount(final String groupName) {
    final Query query = entityManager.createNamedQuery(JpaWebServer.QUERY_GET_WS_COUNT_BY_STATE_AND_GROUP_NAME);
    query.setParameter(JpaWebServer.QUERY_PARAM_STATE, WebServerReachableState.WS_UNREACHABLE);
    query.setParameter(JpaWebServer.QUERY_PARAM_GROUP_NAME, groupName);
    return (Long) query.getSingleResult();
}
 

@Transactional
public Sector findByName(String name) throws DataAccessException {
	try {
		Query query = createNamedQuery("Sector.findByName", -1, -1, name);
		return (Sector) query.getSingleResult();
	} catch (NoResultException nre) {
		return null;
	}

}
 

/**
 * 查询符合的中心工作信息总数
 * @param id
 * @param count
 * @param sequence
 * @param com.x.okr.assemble.control.jaxrs.okrcenterworkinfo.WrapInFilter wrapIn
 * @return
 * @throws Exception
 */
public long getCountForCenterInfoWithFilter( com.x.okr.assemble.control.jaxrs.WorkCommonQueryFilter wrapIn ) throws Exception {
	//先获取上一页最后一条的sequence值，如果有值的话，以此sequence值作为依据取后续的count条数据
	EntityManager em = this.entityManagerContainer().get( OkrWorkPerson.class );
	List<Object> vs = new ArrayList<>();
	StringBuffer sql_stringBuffer = new StringBuffer();
	Integer index = 1;
	
	sql_stringBuffer.append( "SELECT count( o.id ) FROM "+OkrWorkPerson.class.getCanonicalName()+" o where o.workId is null and o.processIdentity = '观察者'  " );
	
	// 根据标题模糊查询
	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++;
	}

	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 (Long) query.getSingleResult();
}
 

private JpaJvm getJpaJvm(final String jvmName, final String groupName) {
    final Query q = em.createNamedQuery(JpaJvm.QUERY_FIND_JVM_BY_GROUP_AND_JVM_NAME);
    q.setParameter(JpaJvm.QUERY_PARAM_JVM_NAME, jvmName);
    q.setParameter(JpaJvm.QUERY_PARAM_GROUP_NAME, groupName);
    return (JpaJvm) q.getSingleResult();
}
 

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

@Override
public int getCountAll() {
    Query query = manager.createNamedQuery(JpaRegionWidget.REGION_WIDGET_COUNT_ALL);
    Number countResult = (Number) query.getSingleResult();
    return countResult.intValue();
}