Java Code Examples for org.springframework.jdbc.support.rowset.SqlRowSet#getInt()

@Override
public boolean isDispatchable(JobId job) {
    SqlRowSet row =  jdbc.queryForRowSet(HAS_PENDING_FRAMES, job.getJobId());
    if (!row.first()) {
        return true;
    }
    if (row.getInt("int_state") == JobState.FINISHED.ordinal()) {
        return true;
    }
    if (row.getInt("pending") == 0) {
        return true;
    }
    if (!row.getBoolean("bool_paused")) {
        return true;
    }
    return false;
}
 

@Override
public int queryCount(String commodityCode) {
    SqlRowSet rowSet = jdbcTemplate.queryForRowSet("select count from storage_tbl where commodity_code = ?",
            commodityCode);
    if (rowSet.next()) {
        int count = rowSet.getInt("count");
        LOGGER.info("Storage has " + count + " for " + commodityCode);
        return count;
    }
    return -1;
}
 

@Override
public Map<String, Integer> getOrgUnitData( OrgUnitQueryParams params )
{
    Map<String, Integer> dataMap = new HashMap<>();

    List<String> columns = getMetadataColumns( params );

    String sql = getQuerySql( params );

    SqlRowSet rowSet = jdbcTemplate.queryForRowSet( sql );

    while ( rowSet.next() )
    {
        StringBuilder key = new StringBuilder();

        for ( String column : columns )
        {
            key.append( rowSet.getString( column ) ).append( DIMENSION_SEP );
        }

        key.deleteCharAt( key.length() - 1 );

        int value = rowSet.getInt( "count" );

        dataMap.put( key.toString(), value );
    }

    return dataMap;
}
 

@Override
public boolean isFinished(JobId job) {
    SqlRowSet row =  jdbc.queryForRowSet(HAS_PENDING_FRAMES, job.getJobId());
    if (!row.first()) {
        return true;
    }
    if (row.getInt("int_state") == JobState.FINISHED.ordinal()) {
        return true;
    }
    if (row.getInt("pending") == 0) {
        return true;
    }
    return false;
}
 

@Override
protected void service(HttpServletRequest request, HttpServletResponse response)
		throws ServletException, IOException {
	try {
		Session session = ServiceUtil.validateSession(request);

		response.setContentType("text/xml");
		response.setCharacterEncoding("UTF-8");

		// Avoid resource caching
		response.setHeader("Pragma", "no-cache");
		response.setHeader("Cache-Control", "no-store");
		response.setDateHeader("Expires", 0);

		Integer max = request.getParameter("max") != null ? Integer.parseInt(request.getParameter("max")) : null;

		PrintWriter writer = response.getWriter();
		writer.write("<list>");

		TicketDAO dao = (TicketDAO) Context.get().getBean(TicketDAO.class);
		StringBuffer query = new StringBuffer(
				"select A.ld_id, A.ld_ticketid, A.ld_docid, A.ld_creation, A.ld_expired, A.ld_count, A.ld_maxcount, A.ld_suffix, A.ld_enabled, B.ld_filename, B.ld_folderid from ld_ticket as A, ld_document as B where A.ld_deleted = 0 and A.ld_type = 0 and A.ld_tenantid="
						+ session.getTenantId()
						+ " and B.ld_deleted=0 and A.ld_docid=B.ld_id order by A.ld_creation desc");

		DateFormat df = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss");
		df.setTimeZone(TimeZone.getTimeZone("UTC"));

		SqlRowSet set = dao.queryForRowSet(query.toString(), null, max);

		/*
		 * Iterate over records composing the response XML document
		 */
		while (set.next()) {
			Integer count = set.getInt(6);
			Integer maxCount = set.getInt(7);
			Date creation = set.getDate(4);
			Date expired = set.getDate(5);
			boolean enabled = set.getInt(9) == 1;
			String fileName = set.getString(10);
			String suffix = set.getString(8);
			if (suffix != null && StringUtils.isNotEmpty(suffix.trim()))
				fileName = fileName + ".pdf";

			writer.print("<ticket>");
			writer.print("<id>" + set.getInt(1) + "</id>");
			writer.print("<ticketId><![CDATA[" + set.getString(2) + "]]></ticketId>");
			writer.print("<docId>" + set.getLong(3) + "</docId>");
			writer.print("<creation>" + df.format(creation) + "</creation>");
			writer.print("<expired>" + df.format(expired) + "</expired>");
			writer.print("<count>" + count + "</count>");
			if (maxCount != null)
				writer.print("<maxCount>" + maxCount + "</maxCount>");
			if (StringUtils.isNotEmpty(suffix))
				writer.print("<suffix><![CDATA[" + suffix + "]]></suffix>");
			writer.print("<eenabled>" + (enabled ? "0" : "2") + "</eenabled>");
			writer.print("<valid>" + (enabled && (maxCount == null || maxCount > count)
					&& (expired == null || expired.getTime() > new Date().getTime())) + "</valid>");
			writer.print("<fileName><![CDATA[" + fileName + "]]></fileName>");
			writer.print("<icon>"
					+ FilenameUtils.getBaseName(IconSelector.selectIcon(FilenameUtils.getExtension(fileName)))
					+ "</icon>");
			writer.print("<folderId>" + set.getLong(11) + "</folderId>");
			writer.print("</ticket>");
		}
		writer.write("</list>");
	} catch (Throwable e) {
		log.error(e.getMessage(), e);
		if (e instanceof ServletException)
			throw (ServletException) e;
		else if (e instanceof IOException)
			throw (IOException) e;
		else
			throw new ServletException(e.getMessage(), e);
	}
}
 

private void folderRights(HttpServletResponse response, Long folderId, String locale)
		throws IOException, PersistenceException {
	FolderDAO folderDao = (FolderDAO) Context.get().getBean(FolderDAO.class);
	Folder folder = folderDao.findById(folderId);
	folderDao.initialize(folder);

	Folder ref = folder;
	if (folder.getSecurityRef() != null) {
		ref = folderDao.findById(folder.getSecurityRef());
		folderDao.initialize(ref);
	}

	// Prepare a map of users
	Map<Long, String> users = getUsers(ref.getTenantId());

	PrintWriter writer = response.getWriter();
	writer.write("<list>");

	// Prepare the query on the folder group in join with groups
	StringBuffer query = new StringBuffer(
			"select A.ld_groupid, B.ld_name, B.ld_type, A.ld_write, A.ld_add, A.ld_security, A.ld_immutable, A.ld_delete, A.ld_rename, A.ld_import, A.ld_export, A.ld_sign, A.ld_archive, A.ld_workflow, A.ld_download, ");
	query.append(
			" A.ld_calendar, A.ld_subscription, A.ld_print, A.ld_password, A.ld_move, A.ld_email, A.ld_automation from ld_foldergroup as A, ld_group B where A.ld_folderid = ");
	query.append("" + ref.getId());
	query.append(" and B.ld_tenantid = " + ref.getTenantId());
	query.append(" and B.ld_deleted=0 and A.ld_groupid = B.ld_id order by B.ld_type asc, B.ld_name asc");

	SqlRowSet set = folderDao.queryForRowSet(query.toString(), null, null);

	/*
	 * Iterate over records composing the response XML document
	 */
	while (set.next()) {
		long groupId = set.getLong(1);
		String groupName = set.getString(2);
		int groupType = set.getInt(3);
		long userId = 0L;
		if (groupType == Group.TYPE_USER)
			userId = Long.parseLong(groupName.substring(groupName.lastIndexOf('_') + 1));

		writer.print("<right>");
		writer.print("<entityId>" + groupId + "</entityId>");

		if (groupType == Group.TYPE_DEFAULT)
			writer.print("<entity><![CDATA[" + I18N.message("group", locale) + ": " + groupName + "]]></entity>");
		else
			writer.print(
					"<entity><![CDATA[" + I18N.message("user", locale) + ": " + users.get(userId) + "]]></entity>");

		writer.print("<read>true</read>");
		writer.print("<write>" + (set.getInt(4) == 1 ? true : false) + "</write>");
		writer.print("<add>" + (set.getInt(5) == 1 ? true : false) + "</add>");
		writer.print("<security>" + (set.getInt(6) == 1 ? true : false) + "</security>");
		writer.print("<immutable>" + (set.getInt(7) == 1 ? true : false) + "</immutable>");
		writer.print("<delete>" + (set.getInt(8) == 1 ? true : false) + "</delete>");
		writer.print("<rename>" + (set.getInt(9) == 1 ? true : false) + "</rename>");
		writer.print("<import>" + (set.getInt(10) == 1 ? true : false) + "</import>");
		writer.print("<export>" + (set.getInt(11) == 1 ? true : false) + "</export>");
		writer.print("<sign>" + (set.getInt(12) == 1 ? true : false) + "</sign>");
		writer.print("<archive>" + (set.getInt(13) == 1 ? true : false) + "</archive>");
		writer.print("<workflow>" + (set.getInt(14) == 1 ? true : false) + "</workflow>");
		writer.print("<download>" + (set.getInt(15) == 1 ? true : false) + "</download>");
		writer.print("<calendar>" + (set.getInt(16) == 1 ? true : false) + "</calendar>");
		writer.print("<subscription>" + (set.getInt(17) == 1 ? true : false) + "</subscription>");
		writer.print("<print>" + (set.getInt(18) == 1 ? true : false) + "</print>");
		writer.print("<password>" + (set.getInt(19) == 1 ? true : false) + "</password>");
		writer.print("<move>" + (set.getInt(20) == 1 ? true : false) + "</move>");
		writer.print("<email>" + (set.getInt(21) == 1 ? true : false) + "</email>");
		writer.print("<automation>" + (set.getInt(22) == 1 ? true : false) + "</automation>");
		writer.print("<type>" + groupType + "</type>");
		writer.print("</right>");

	}

	writer.write("</list>");
}
 

private void menuRights(HttpServletResponse response, Long menuId, String locale, long tenantId)
		throws IOException, PersistenceException {
	MenuDAO menuDao = (MenuDAO) Context.get().getBean(MenuDAO.class);
	Menu menu = menuDao.findById(menuId);
	menuDao.initialize(menu);

	// Prepare a map of users
	Map<Long, String> users = getUsers(menu.getTenantId());

	PrintWriter writer = response.getWriter();
	writer.write("<list>");

	// Prepare the query on the folder group in join with groups
	StringBuffer query = new StringBuffer(
			"select A.ld_groupid, B.ld_name, B.ld_type from ld_menugroup as A, ld_group B where A.ld_menuid = ");
	query.append("" + menu.getId());
	query.append(" and B.ld_deleted=0 and A.ld_groupid = B.ld_id and B.ld_tenantid = " + menu.getTenantId());
	query.append(" order by B.ld_type asc, B.ld_name asc");

	SqlRowSet set = menuDao.queryForRowSet(query.toString(), null, null);

	/*
	 * Iterate over records composing the response XML document
	 */
	while (set.next()) {
		long groupId = set.getLong(1);
		String groupName = set.getString(2);
		int groupType = set.getInt(3);
		long userId = 0L;
		if (groupType == Group.TYPE_USER)
			userId = Long.parseLong(groupName.substring(groupName.lastIndexOf('_') + 1));

		writer.print("<right>");
		writer.print("<entityId>" + groupId + "</entityId>");

		if (groupType == Group.TYPE_DEFAULT)
			writer.print("<entity><![CDATA[" + I18N.message("group", locale) + ": " + groupName + "]]></entity>");
		else
			writer.print(
					"<entity><![CDATA[" + I18N.message("user", locale) + ": " + users.get(userId) + "]]></entity>");

		writer.print("<type>" + groupType + "</type>");
		writer.print("</right>");
	}

	writer.write("</list>");
}
 

@Override
public List<DataAnalysisMeasures> getDataAnalysisMeasures( DataElement dataElement,
    Collection<CategoryOptionCombo> categoryOptionCombos, Collection<String> parentPaths, Date from )
{
    List<DataAnalysisMeasures> measures = new ArrayList<>();

    if ( categoryOptionCombos.isEmpty() || parentPaths.isEmpty() )
    {
        return measures;
    }

    String catOptionComboIds = TextUtils.getCommaDelimitedString( getIdentifiers( categoryOptionCombos ) );

    String matchPaths = "(";
    for ( String path : parentPaths )
    {
        matchPaths += "ou.path like '" + path + "%' or ";
    }
    matchPaths = TextUtils.removeLastOr( matchPaths ) + ") ";

    String sql = "select dv.sourceid, dv.categoryoptioncomboid, " + "avg( cast( dv.value as "
        + statementBuilder.getDoubleColumnType() + " ) ) as average, " + "stddev_pop( cast( dv.value as "
        + statementBuilder.getDoubleColumnType() + " ) ) as standarddeviation " + "from datavalue dv "
        + "join organisationunit ou on ou.organisationunitid = dv.sourceid "
        + "join period pe on dv.periodid = pe.periodid " + "where dv.dataelementid = " + dataElement.getId() + " "
        + "and dv.categoryoptioncomboid in (" + catOptionComboIds + ") " + "and pe.startdate >= '"
        + DateUtils.getMediumDateString( from ) + "' " + "and " + matchPaths + "and dv.deleted is false "
        + "group by dv.sourceid, dv.categoryoptioncomboid";

    SqlRowSet rowSet = jdbcTemplate.queryForRowSet( sql );

    while ( rowSet.next() )
    {
        int orgUnitId = rowSet.getInt( 1 );
        int categoryOptionComboId = rowSet.getInt( 2 );
        double average = rowSet.getDouble( 3 );
        double standardDeviation = rowSet.getDouble( 4 );

        if ( standardDeviation != 0.0 )
        {
            measures
                .add( new DataAnalysisMeasures( orgUnitId, categoryOptionComboId, average, standardDeviation ) );
        }
    }

    return measures;
}
 

/**
 * Get the id for the workflow period that spans the given end date.
 * The workflow period may or may not be the same as the period for which
 * we are checking data validity. The workflow period will have a period
 * type that matches the workflow period type, and it will contain the
 * end date of the period for which we are checking data validity.
 *
 * Returns zero if there is no such workflow period.
 *
 * It turns out that this is much faster done as a separate query in
 * postgresql than imbedding this as a subquery in the larger query above.
 *
 * @param workflow workflow we are checking
 * @param endDate end date of the period we are checking approval for,
 *                formatted as a string for a SQL query.
 * @return id of the workflow period which overlaps with the endDate
 */
private int getWorkflowPeriodId( DataApprovalWorkflow workflow, String endDate )
{
    final String sql = "select periodid from period where '" + endDate + "' >= startdate and '" + endDate + "' <= enddate and periodtypeid = " + workflow.getPeriodType().getId();

    SqlRowSet rowSet = jdbcTemplate.queryForRowSet( sql );

    if ( rowSet.next() )
    {
        return rowSet.getInt( 1 );
    }

    return 0;
}