Java Code Examples for org.springframework.jdbc.core.JdbcTemplate#queryForRowSet()

The following examples show how to use org.springframework.jdbc.core.JdbcTemplate#queryForRowSet() . You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source File: HibernatePersistentObjectDAO.java    From document-management-software with GNU Lesser General Public License v3.0 6 votes vote down vote up 
@Override
public SqlRowSet queryForRowSet(String sql, Object[] args, Integer maxRows) throws PersistenceException {
	SqlRowSet rs = null;
	try {
		DataSource dataSource = (DataSource) Context.get().getBean("DataSource");
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		if (maxRows != null)
			jdbcTemplate.setMaxRows(maxRows);
		if (args != null)
			rs = jdbcTemplate.queryForRowSet(insertTopClause(sql, maxRows), args);
		else
			rs = jdbcTemplate.queryForRowSet(insertTopClause(sql, maxRows));
		return rs;
	} catch (Throwable e) {
		throw new PersistenceException(e);
	}
}
Example 2
Source File: EndToEndTest.java    From ecs-sync with Apache License 2.0 5 votes vote down vote up 
private void verifyDb(TestStorage storage) {
    JdbcTemplate jdbcTemplate = dbService.getJdbcTemplate();

    long totalCount = verifyDbObjects(jdbcTemplate, storage, storage.getRootObjects());

    SqlRowSet rowSet = jdbcTemplate.queryForRowSet("SELECT count(target_id) FROM " + storage.getOptions().getDbTable() + " WHERE target_id != ''");
    Assert.assertTrue(rowSet.next());
    Assert.assertEquals(totalCount, rowSet.getLong(1));
}
Example 3
Source File: EndToEndTest.java    From ecs-sync with Apache License 2.0 5 votes vote down vote up 
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 " + storage.getOptions().getDbTable() + " 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;
}
Example 4
Source File: SqlDbReportDownloader.java    From adwords-alerting with Apache License 2.0 4 votes vote down vote up 
@Override
public List<ReportData> downloadReports(
    ImmutableAdWordsSession protoSession, Set<Long> clientCustomerIds) {
  Map<Long, ReportData> reportDataMap = new HashMap<Long, ReportData>();
  
  JdbcTemplate jdbcTemplate = getJdbcTemplate();
  String sqlQuery = getSqlQueryWithReportColumnNames();
  ReportDefinitionReportType reportType = getReportType();
  SqlRowSet rowSet = jdbcTemplate.queryForRowSet(sqlQuery);
  
  // Get the column index of customer id. 
  int customerIdColumnIndex = rowSet.findColumn(EXTERNAL_CUSTOMER_ID_REPORT_COLUMN_NAME);
  Preconditions.checkState(
      customerIdColumnIndex >= 0,
      "You must choose \"%s\" field to generate report data",
      EXTERNAL_CUSTOMER_ID_REPORT_COLUMN_NAME);
  
  List<String> columnNames = Arrays.asList(rowSet.getMetaData().getColumnNames());
  int columns = columnNames.size();
  
  // Read result into map.
  int rows = 0;
  while (rowSet.next()) {
    rows++;
    List<String> row = new ArrayList<String>(columns);
    for (int i = 0; i < columns; i++) {
      row.add(rowSet.getString(i));
    }

    String customerIdStr = row.get(customerIdColumnIndex);
    Long customerId = Long.parseLong(customerIdStr);
    ReportData reportData = reportDataMap.get(customerId);
    if (reportData == null) {
      reportData = new ReportData(customerId, reportType, columnNames);
      reportDataMap.put(customerId, reportData);
    }
    reportData.addRow(row);
  }
  
  LOGGER.info("Retrieved and parsed {} rows from database.", rows);
  return new ArrayList<ReportData>(reportDataMap.values());
}
Example 5
Source File: OLATUpgrade_7_3_0.java    From olat with Apache License 2.0 4 votes vote down vote up 
/**
  * @param string
  */
 private void migrateDataFromGUIPreferences(final UpgradeManager upgradeManager, final UpgradeHistoryData uhd) {

     if (!uhd.getBooleanDataValue(TASK_MIGRATE_DATA_FROM_GUI_PROPERTIES_DONE)) {
         GUIPreferencesParser parser = new GUIPreferencesParser();
         JdbcTemplate template = new JdbcTemplate(upgradeManager.getDataSource());
         SqlRowSet srs = template
                 .queryForRowSet("SELECT textvalue, identity FROM  o_property WHERE identity IS NOT NULL AND textvalue IS NOT NULL AND textvalue LIKE  '%InfoSubscription::subs%'");
         Long identityKey = 0L;
         int rowCount = 0;
int counter = 0;
         while (srs.next()) {
             try {

                 String prefsXml = srs.getString("textvalue");
                 identityKey = srs.getLong("identity");
                 Identity identity = security.loadIdentityByKey(identityKey);

                 Document doc = parser.createDocument(prefsXml);

                 List<String> infoSubscriptions = parser.parseDataForInputQuery(doc, parser.queryInfo);
                 persistInfo(infoSubscriptions, "InfoSubscription::subscribed", identity);

                 List<String> calendarSubscriptions = parser.parseDataForInputQuery(doc, parser.queryCal);
                 persistInfo(calendarSubscriptions, "CourseCalendarSubscription::subs", identity);

                 List<String> infoSubscriptionsNot = parser.parseDataForInputQuery(doc, parser.queryInfoNot);
                 persistInfo(infoSubscriptionsNot, "InfoSubscription::notdesired", identity);

                 List<String> calendarSubscriptionsNot = parser.parseDataForInputQuery(doc, parser.queryCalNot);
                 persistInfo(calendarSubscriptionsNot, "CourseCalendarSubscription::notdesired", identity);

             } catch (Exception e) {
                 log.error("could not migrate gui preferences for identity: " + identityKey, e);
             }
  	  counter++;
     if (counter % 10 == 0) {
		DBFactory.getInstance().intermediateCommit();
	}

             rowCount++;
         }
   // Final commit 
DBFactory.getInstance().intermediateCommit();

         uhd.setBooleanDataValue(TASK_MIGRATE_DATA_FROM_GUI_PROPERTIES_DONE, true);
         upgradeManager.setUpgradesHistory(uhd, VERSION);
     }

 }
Example 6
Source File: OLATUpgrade_7_3_0.java    From olat with Apache License 2.0 4 votes vote down vote up 
/**
 * @param string
 */
private void migrateDataFromGUIPreferences(final UpgradeManager upgradeManager, final UpgradeHistoryData uhd) {

    if (!uhd.getBooleanDataValue(TASK_MIGRATE_DATA_FROM_GUI_PROPERTIES_DONE)) {
        GUIPreferencesParser parser = new GUIPreferencesParser();
        JdbcTemplate template = new JdbcTemplate(upgradeManager.getDataSource());
        SqlRowSet srs = template
                .queryForRowSet("SELECT textvalue, identity FROM  o_property WHERE identity IS NOT NULL AND textvalue IS NOT NULL AND textvalue LIKE  '%InfoSubscription::subs%'");
        Long identityKey = 0L;
        int rowCount = 0;
        int counter = 0;
        while (srs.next()) {
            try {

                String prefsXml = srs.getString("textvalue");
                identityKey = srs.getLong("identity");
                Identity identity = security.loadIdentityByKey(identityKey);

                Document doc = parser.createDocument(prefsXml);

                List<String> infoSubscriptions = parser.parseDataForInputQuery(doc, parser.queryInfo);
                persistInfo(infoSubscriptions, "InfoSubscription::subscribed", identity);

                List<String> calendarSubscriptions = parser.parseDataForInputQuery(doc, parser.queryCal);
                persistInfo(calendarSubscriptions, "CourseCalendarSubscription::subs", identity);

                List<String> infoSubscriptionsNot = parser.parseDataForInputQuery(doc, parser.queryInfoNot);
                persistInfo(infoSubscriptionsNot, "InfoSubscription::notdesired", identity);

                List<String> calendarSubscriptionsNot = parser.parseDataForInputQuery(doc, parser.queryCalNot);
                persistInfo(calendarSubscriptionsNot, "CourseCalendarSubscription::notdesired", identity);

            } catch (Exception e) {
                log.error("could not migrate gui preferences for identity: " + identityKey, e);
            }
            counter++;
            if (counter % 10 == 0) {
                DBFactory.getInstance().intermediateCommit();
            }

            rowCount++;
        }
        // Final commit
        DBFactory.getInstance().intermediateCommit();

        uhd.setBooleanDataValue(TASK_MIGRATE_DATA_FROM_GUI_PROPERTIES_DONE, true);
        upgradeManager.setUpgradesHistory(uhd, VERSION);
    }

}
Example 7
Source File: BizDataObjectBehaviorImpl.java    From FoxBPM with Apache License 2.0 4 votes vote down vote up 
public List<BizDataObject> getDataObjects(String dataSource) {
	LOG.debug("getDataObjects(String dataSource),dataSource=" + dataSource);
	try {
		JdbcTemplate jdbcTemplate = new JdbcTemplate(DBUtils.getDataSource());
		DatabaseMetaData dm = DBUtils.getDataSource().getConnection().getMetaData();
		String databaseType = dm.getDatabaseProductName();
		String databaseVersion = dm.getDatabaseProductVersion();
		LOG.info("the database type is " + databaseType + ",version is " + databaseVersion);
		// 定义sql返回结果集
		SqlRowSet rs = null;
		boolean isOracle = false;
		if (MySQL_TYPE.equalsIgnoreCase(databaseType)) {
			// 获取此 当前数据源连接 对象的当前目录名称。
			String catalog = jdbcTemplate.getDataSource().getConnection().getCatalog();
			LOG.info("the sql is " + TABLE_INFOR);
			rs = jdbcTemplate.queryForRowSet(TABLE_INFOR, new Object[]{catalog});
		} else if (ORACLE_TYPE.equalsIgnoreCase(databaseType)) {
			isOracle = true;
			StringBuffer sql = new StringBuffer("select distinct t_col.DATA_TYPE,t_col.TABLE_NAME,t_col.COLUMN_NAME,t_des.comments as TABLE_COMMENT,c_des.comments as COLUMN_COMMENT from user_tab_columns t_col,user_col_comments c_des,user_tab_comments t_des ").append("where t_col.table_name = c_des.table_name and t_col.table_name = t_des.table_name order by t_col.table_name");
			rs = jdbcTemplate.queryForRowSet(sql.toString());
		} else if("mssql".equalsIgnoreCase(databaseType)){
			
		}
		
		
		List<BizDataObject> bizDataObjects = new ArrayList<BizDataObject>();
		BizDataObject bizDataObject = null;
		DataVariableDefinition dataVariableDefine = null;
		String tableName = null;
		StringBuffer sbExpression = new StringBuffer();
		if(rs != null){
		// 获取表信息
		while (rs.next()) {
			// 处理首次和区分不同表
			if (!rs.getString(TABLE_NAME).equals(tableName)) {
				bizDataObject = new BizDataObject();
				bizDataObject.setId(rs.getString(TABLE_NAME));
				if (isOracle) {
					bizDataObject.setName(rs.getString(TABLE_COMMENT));
				}
				bizDataObject.setDataSource(dataSource);
				// 添加业务数据对象
				bizDataObjects.add(bizDataObject);
			}
			dataVariableDefine = new DataVariableDefinition();
			dataVariableDefine.setId(rs.getString(COLUMN_NAME));
			dataVariableDefine.setFieldName(rs.getString(COLUMN_NAME));
			dataVariableDefine.setDataType(rs.getString(DATA_TYPE));
			// 生成表达式
			sbExpression.append("import org.foxbpm.engine.impl.util.DataVarUtil;\n");
			sbExpression.append("DataVarUtil.getInstance().getDataValue(");
			sbExpression.append("\"" + dataSource + "\"").append(',').append("processInfo.getProcessInstance().getBizKey(),");
			sbExpression.append("\"" + dataVariableDefine.getId() + "\"");
			sbExpression.append(",processInfo);");
			dataVariableDefine.setExpression(sbExpression.toString());
			
			dataVariableDefine.setDocumentation(rs.getString(COLUMN_COMMENT));
			dataVariableDefine.setBizType(Constant.DB_BIZTYPE);
			// 添加数据变量定义
			bizDataObject.getDataVariableDefinitions().add(dataVariableDefine);
			tableName = rs.getString(TABLE_NAME);
			// 清空sbExpression缓存
			sbExpression.delete(0, sbExpression.length());
		}}
		LOG.debug("end getDataObjects(String dataSource)");
		return bizDataObjects;
	} catch (SQLException e) {
		throw ExceptionUtil.getException("获取数据对象失败",e);
	}
}
Example 8
Source File: SqliteDbServiceTest.java    From ecs-sync with Apache License 2.0 4 votes vote down vote up 
private SqlRowSet getRowSet(String id) {
    JdbcTemplate jdbcTemplate = dbService.getJdbcTemplate();
    SqlRowSet rowSet = jdbcTemplate.queryForRowSet("SELECT * FROM " + dbService.getObjectsTableName() + " WHERE source_id=?", id);
    rowSet.next();
    return rowSet;
}