public int account(String sql) throws SQLException { int ret = 0; SqlRowSet sqlRowset = jdbcTemplate.queryForRowSet(sql); if (sqlRowset.next()) { ret = sqlRowset.getInt(1); } return ret; }
private void removeOldPoolMessages(long olderThan) { Set<Integer> proccessedIds = new HashSet<Integer>(); long actualTimeInMillis = new DateTime().getMillis(); SqlRowSet srs = this.getJdbcTemplate().queryForRowSet("SELECT id,created FROM pn_pool_message"); while (srs.next()) { Timestamp timeStamp = srs.getTimestamp("created"); if (timeStamp.getTime() + olderThan < actualTimeInMillis) { proccessedIds.add(srs.getInt("id")); } } removeAllPoolMessages(proccessedIds); } }
public int account(String sql) throws TinyDbException { try { int ret = 0; SqlRowSet sqlRowset = jdbcTemplate.queryForRowSet(sql); if (sqlRowset.next()) { ret = sqlRowset.getInt(1); } return ret; } catch (DataAccessException e) { throw new TinyDbException(e); } }
private SequenceRange applyNextRange0(String seqName) { SequenceRange result = null; SqlRowSet rowSet = jdbcTemplate.queryForRowSet(selectSql, seqName); if(rowSet.next()) { long maxLimit = rowSet.getLong("max_limit"); long minLimit = rowSet.getLong("min_limit"); int range = rowSet.getInt("step"); int value = rowSet.getInt("value"); long min = value; long max = value + range; if(max < 0 || max-1 > maxLimit) { min = minLimit; max = minLimit + range; } int updatedRows = jdbcTemplate.update(updateSql, max, seqName, value); if(updatedRows==1) { result = new SequenceRange(min, max, getDbDate(), getDbName()); } } else { long value = defaultMinLimit+step; int insertedRows = jdbcTemplate.update(insertSql, seqName, defaultMinLimit, defaultMaxLimit, step, value); if(insertedRows == 1) { result = new SequenceRange(defaultMinLimit, value, getDbDate(), getDbName()); } } return result; }
/** * 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; }
balance = getSimpleJdbcTemplate().queryForMap(balanceStatementSql, sessionId, pendingEntryRowSet.getInt(GeneralLedgerConstants.ColumnNames.UNIVERSITY_FISCAL_YEAR), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.CHART_OF_ACCOUNTS_CODE), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.ACCOUNT_NUMBER), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.SUB_ACCOUNT_NUMBER), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.OBJECT_CODE), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.SUB_OBJECT_CODE)); getSimpleJdbcTemplate().update(updateBalanceStatementSql, budget, actual, encumb, sessionId, pendingEntryRowSet.getInt(GeneralLedgerConstants.ColumnNames.UNIVERSITY_FISCAL_YEAR), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.CHART_OF_ACCOUNTS_CODE), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.ACCOUNT_NUMBER), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.SUB_ACCOUNT_NUMBER), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.OBJECT_CODE), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.SUB_OBJECT_CODE)); getSimpleJdbcTemplate().update(insertBalanceStatementSql, pendingEntryRowSet.getInt(GeneralLedgerConstants.ColumnNames.UNIVERSITY_FISCAL_YEAR), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.CHART_OF_ACCOUNTS_CODE), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.ACCOUNT_NUMBER), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.SUB_ACCOUNT_NUMBER), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.OBJECT_CODE), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.SUB_OBJECT_CODE), budget, actual, encumb, sessionId);
balance = getSimpleJdbcTemplate().queryForMap(balanceStatementSql, sessionId, pendingEntryRowSet.getInt(GeneralLedgerConstants.ColumnNames.UNIVERSITY_FISCAL_YEAR), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.CHART_OF_ACCOUNTS_CODE), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.ACCOUNT_NUMBER), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.SUB_ACCOUNT_NUMBER), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.OBJECT_CODE), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.SUB_OBJECT_CODE), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.OBJECT_TYPE_CODE)); getSimpleJdbcTemplate().update(updateBalanceStatementSql, budget, actual, encumb, sessionId, pendingEntryRowSet.getInt(GeneralLedgerConstants.ColumnNames.UNIVERSITY_FISCAL_YEAR), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.CHART_OF_ACCOUNTS_CODE), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.ACCOUNT_NUMBER), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.SUB_ACCOUNT_NUMBER), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.OBJECT_CODE), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.SUB_OBJECT_CODE), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.OBJECT_TYPE_CODE)); getSimpleJdbcTemplate().update(insertBalanceStatementSql, pendingEntryRowSet.getInt(GeneralLedgerConstants.ColumnNames.UNIVERSITY_FISCAL_YEAR), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.CHART_OF_ACCOUNTS_CODE), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.ACCOUNT_NUMBER), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.SUB_ACCOUNT_NUMBER), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.OBJECT_CODE), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.SUB_OBJECT_CODE), budget, actual, encumb, sortCode, pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.OBJECT_TYPE_CODE), sessionId);
balance = getSimpleJdbcTemplate().queryForMap(balanceStatementSql, sessionId, pendingEntryRowSet.getInt(GeneralLedgerConstants.ColumnNames.UNIVERSITY_FISCAL_YEAR), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.CHART_OF_ACCOUNTS_CODE), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.ACCOUNT_NUMBER), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.SUB_ACCOUNT_NUMBER), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.OBJECT_CODE), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.SUB_OBJECT_CODE)); getSimpleJdbcTemplate().update(updateBalanceStatementSql, budget, actual, encumb, sessionId, pendingEntryRowSet.getInt(GeneralLedgerConstants.ColumnNames.UNIVERSITY_FISCAL_YEAR), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.CHART_OF_ACCOUNTS_CODE), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.ACCOUNT_NUMBER), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.SUB_ACCOUNT_NUMBER), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.OBJECT_CODE), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.SUB_OBJECT_CODE)); getSimpleJdbcTemplate().update(insertBalanceStatementSql, pendingEntryRowSet.getInt(GeneralLedgerConstants.ColumnNames.UNIVERSITY_FISCAL_YEAR), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.CHART_OF_ACCOUNTS_CODE), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.ACCOUNT_NUMBER), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.SUB_ACCOUNT_NUMBER), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.OBJECT_CODE), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.SUB_OBJECT_CODE), budget, actual, encumb, pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.REPORT_SORT_CODE), pendingEntryRowSet.getString(GeneralLedgerConstants.ColumnNames.OBJECT_LEVEL_CODE2), sessionId);
Integer dataElementId = rowSet.getInt( 1 ); Integer periodId = rowSet.getInt( 2 ); Integer organisationUnitId = rowSet.getInt( 3 ); Integer categoryOptionComboId = rowSet.getInt( 4 ); Integer attributeOptionComboId = rowSet.getInt( 5 ); String value = rowSet.getString( 6 ); String storedBy = rowSet.getString( 7 );
@Override public List<TrackedEntityDataValue> getTrackedEntityDataValuesForSynchronization( ProgramStageInstance programStageInstance ) { List<TrackedEntityDataValue> dataValues = new ArrayList<>(); String sql = "SELECT tedv.* FROM trackedentitydatavalue tedv " + "LEFT JOIN programstageinstance psi on tedv.programstageinstanceid = psi.programstageinstanceid " + "LEFT JOIN programstagedataelement psde ON tedv.dataelementid = psde.dataelementid AND psi.programstageid = psde.programstageid " + "WHERE tedv.programstageinstanceid = " + programStageInstance.getId() + " AND psde.skipsynchronization = false"; SqlRowSet rowSet = jdbcTemplate.queryForRowSet( sql ); while ( rowSet.next() ) { TrackedEntityDataValue tedv = new TrackedEntityDataValue(); tedv.setCreated( rowSet.getDate( "created" ) ); tedv.setLastUpdated( rowSet.getDate( "lastupdated" ) ); tedv.setProgramStageInstance( programStageInstance ); tedv.setValue( rowSet.getString( "value" ) ); tedv.setStoredBy( rowSet.getString( "storedby" ) ); tedv.setProvidedElsewhere( rowSet.getBoolean( "providedelsewhere" ) ); tedv.setDataElement( dataElementStore.get( rowSet.getInt( "dataelementid" ) ) ); dataValues.add( tedv ); } return dataValues; }
glKemLine.setLineNumber(archiveTransactions.getInt(EndowPropertyConstants.ColumnNames.GlInterfaceBatchProcessLine.TRANSACTION_ARCHIVE_FDOC_LN_NBR)); glKemLine.setLineTypeCode(archiveTransactions.getString(EndowPropertyConstants.ColumnNames.GlInterfaceBatchProcessLine.TRANSACTION_ARCHIVE_FDOC_LN_TYP_CD)); glKemLine.setSubTypeCode(archiveTransactions.getString(EndowPropertyConstants.ColumnNames.GlInterfaceBatchProcessLine.TRANSACTION_ARCHIVE_TRAN_SUB_TYP_CD));
private long verifyDbObjects(JdbcTemplate jdbcTemplate, TestStorage storage, Collection<? extends SyncObject> objects) { Date now = new Date(); long count = 0; for (SyncObject object : objects) { count++; String identifier = storage.getIdentifier(object.getRelativePath(), object.getMetadata().isDirectory()); SqlRowSet rowSet = jdbcTemplate.queryForRowSet("SELECT * FROM " + AbstractDbService.DEFAULT_OBJECTS_TABLE_NAME + " WHERE target_id=?", identifier); Assert.assertTrue(rowSet.next()); Assert.assertEquals(identifier, rowSet.getString("target_id")); Assert.assertEquals(object.getMetadata().isDirectory(), rowSet.getBoolean("is_directory")); Assert.assertEquals(object.getMetadata().getContentLength(), rowSet.getLong("size")); // mtime in the DB is actually pulled from the target system, so we don't know what precision it will be in // or if the target system's clock is in sync, but let's assume it will always be within 5 minutes Assert.assertTrue(Math.abs(object.getMetadata().getModificationTime().getTime() - rowSet.getLong("mtime")) < 5 * 60 * 1000); Assert.assertEquals(ObjectStatus.Verified.getValue(), rowSet.getString("status")); long transferStart = rowSet.getLong("transfer_start"), transferComplete = rowSet.getLong("transfer_complete"); if (transferStart > 0) Assert.assertTrue(now.getTime() - transferStart < 10 * 60 * 1000); // less than 10 minutes ago if (transferComplete > 0) Assert.assertTrue(now.getTime() - transferComplete < 10 * 60 * 1000); // less than 10 minutes ago Assert.assertTrue(now.getTime() - rowSet.getLong("verify_start") < 10 * 60 * 1000); // less than 10 minutes ago Assert.assertTrue(now.getTime() - rowSet.getLong("verify_complete") < 10 * 60 * 1000); // less than 10 minutes ago Assert.assertEquals(0, rowSet.getInt("retry_count")); if (object.getMetadata().isDirectory()) count += verifyDbObjects(jdbcTemplate, storage, storage.getChildren(identifier)); } return count; } }
event.setOptionSize( rowSet.getInt( "option_size" ) );
int value = rowSet.getInt( "value" ); grid.addValue( value );
Assert.assertEquals(id, rowSet.getString("target_id")); Assert.assertFalse(rowSet.getBoolean("is_directory")); Assert.assertEquals(data.length, rowSet.getInt("size")); Assert.assertEquals(now.getTime(), getUnixTime(rowSet, "mtime")); Assert.assertEquals(ObjectStatus.InTransfer.getValue(), rowSet.getString("status")); Assert.assertEquals(0, getUnixTime(rowSet, "verify_start")); Assert.assertEquals(0, getUnixTime(rowSet, "verify_complete")); Assert.assertEquals(0, rowSet.getInt("retry_count")); Assert.assertNull(rowSet.getString("error_message")); Assert.assertFalse(rowSet.getBoolean("is_source_deleted")); Assert.assertEquals(id, rowSet.getString("target_id")); Assert.assertFalse(rowSet.getBoolean("is_directory")); Assert.assertEquals(data.length, rowSet.getInt("size")); Assert.assertEquals(now.getTime(), getUnixTime(rowSet, "mtime")); Assert.assertEquals(ObjectStatus.RetryQueue.getValue(), rowSet.getString("status")); Assert.assertEquals(0, getUnixTime(rowSet, "verify_start")); Assert.assertEquals(0, getUnixTime(rowSet, "verify_complete")); Assert.assertEquals(0, rowSet.getInt("retry_count")); Assert.assertEquals(error, rowSet.getString("error_message")); Assert.assertFalse(rowSet.getBoolean("is_source_deleted")); Assert.assertEquals(id, rowSet.getString("target_id")); Assert.assertFalse(rowSet.getBoolean("is_directory")); Assert.assertEquals(data.length, rowSet.getInt("size")); Assert.assertEquals(now.getTime(), getUnixTime(rowSet, "mtime")); Assert.assertEquals(ObjectStatus.InTransfer.getValue(), rowSet.getString("status"));
Assert.assertNull(rowSet.getString("target_id")); Assert.assertFalse(rowSet.getBoolean("is_directory")); Assert.assertEquals(0, rowSet.getInt("size")); Assert.assertEquals(0, getUnixTime(rowSet, "mtime")); Assert.assertEquals(ObjectStatus.InTransfer.getValue(), rowSet.getString("status")); Assert.assertEquals(0, getUnixTime(rowSet, "verify_start")); Assert.assertEquals(0, getUnixTime(rowSet, "verify_complete")); Assert.assertEquals(0, rowSet.getInt("retry_count")); Assert.assertNull(rowSet.getString("error_message")); Assert.assertFalse(rowSet.getBoolean("is_source_deleted")); Assert.assertNull(rowSet.getString("target_id")); Assert.assertTrue(rowSet.getBoolean("is_directory")); Assert.assertEquals(0, rowSet.getInt("size")); Assert.assertEquals(now.getTime(), getUnixTime(rowSet, "mtime")); Assert.assertEquals(ObjectStatus.Verified.getValue(), rowSet.getString("status")); Assert.assertEquals(0, getUnixTime(rowSet, "verify_start")); Assert.assertNotEquals(0, getUnixTime(rowSet, "verify_complete")); Assert.assertEquals(1, rowSet.getInt("retry_count")); Assert.assertEquals("foo", rowSet.getString("error_message")); Assert.assertFalse(rowSet.getBoolean("is_source_deleted")); Assert.assertNull(rowSet.getString("target_id")); Assert.assertFalse(rowSet.getBoolean("is_directory")); Assert.assertEquals(data.length, rowSet.getInt("size")); Assert.assertEquals(0, getUnixTime(rowSet, "mtime")); Assert.assertEquals(ObjectStatus.Transferred.getValue(), rowSet.getString("status"));