Java SqlRowSet類代碼示例

import org.springframework.jdbc.support.rowset.SqlRowSet; //導入依賴的package包/類
@Override
@Transactional
public List<Long> selectparentFolders(List<Long> nodesid) throws SaodException {
	if (nodesid == null || nodesid.size() == 0) {
		return Collections.emptyList();
	}

	NamedParameterJdbcTemplate jdbcNamesTpl = new NamedParameterJdbcTemplate(this.jdbcTemplate);

	MapSqlParameterSource parameters = new MapSqlParameterSource();
	parameters.addValue("ids", nodesid);

	String query = sqlQueries.getQuery("select_parents_folders.sql");
	final SqlRowSet queryForRowSet = jdbcNamesTpl.queryForRowSet(query, parameters);

	final List<Long> ids = new ArrayList<>();
	while (queryForRowSet.next()) {
		ids.add(queryForRowSet.getLong(1));
	}

	return ids;
}
 

import org.springframework.jdbc.support.rowset.SqlRowSet; //導入依賴的package包/類
private void removeDuplicateRows(final JdbcTemplate jdbcTemplate, final Long zone) {
    final List<SubjectEntity> subjects = jdbcTemplate
            .query("SELECT DISTINCT subject_identifier, attributes FROM subject", new SubjectRowMapper());
    jdbcTemplate.update("DELETE FROM subject *");
    for (SubjectEntity s : subjects) {
        jdbcTemplate.update("INSERT INTO subject (subject_identifier, attributes, "
                        + " authorization_zone_id) VALUES (?,?,?)", s.getSubjectIdentifier(), s
                        .getAttributesAsJson(),
                zone);
    }
    final List<ResourceEntity> resources = jdbcTemplate
            .query("SELECT DISTINCT resource_identifier, attributes FROM resource", new ResourceRowMapper());
    jdbcTemplate.update("DELETE FROM resource *");
    for (ResourceEntity r : resources) {
        jdbcTemplate.update("INSERT INTO resource (resource_identifier, attributes, "
                        + " authorization_zone_id) VALUES (?,?,?)", r.getResourceIdentifier(), r
                        .getAttributesAsJson(),
                zone);
    }

    final List<PolicySetEntity> policysets = jdbcTemplate
            .query("SELECT DISTINCT policy_set_id, policy_set_json FROM policy_set", new PolicySetRowMapper());
    jdbcTemplate.update("DELETE FROM policy_set *");
    for (PolicySetEntity ps : policysets) {
        SqlRowSet row = jdbcTemplate
                .queryForRowSet("SELECT * FROM policy_set WHERE policy_set_id =?", ps.getPolicySetID());
        if (row.next()) {
            jdbcTemplate.update("UPDATE policy_set SET policy_set_json = ? WHERE policy_set_id = ?",
                    ps.getPolicySetJson(), ps.getPolicySetID());
        } else {
            jdbcTemplate.update("INSERT INTO policy_set (policy_set_id, policy_set_json, "
                    + " authorization_zone_id) VALUES (?,?,?)", ps.getPolicySetID(), ps.getPolicySetJson(), zone);
        }
    }
}
 

import org.springframework.jdbc.support.rowset.SqlRowSet; //導入依賴的package包/類
@Override
public void dumpAfnemerindicatieTabelNaarFile(final File outputFile) {
    LOGGER.info("Genereer afnemerindicatie rijen naar {}", outputFile.getAbsolutePath());
    try (FileOutputStream fos = new FileOutputStream(outputFile)) {
        final SqlRowSet sqlRowSet = new JdbcTemplate(masterDataSource).queryForRowSet("select * from autaut.persafnemerindicatie where levsautorisatie "
                + "IN (select id from autaut.levsautorisatie where dateinde is null)");
        while (sqlRowSet.next()) {
            IOUtils.write(String.format("%s,%s,%s,%s,%s,%s,%s%n",
                    sqlRowSet.getString(INDEX_ID),
                    sqlRowSet.getString(INDEX_PERS),
                    sqlRowSet.getString(INDEX_AFNEMER),
                    sqlRowSet.getString(INDEX_LEVSAUTORISATIE),
                    StringUtils.defaultIfEmpty(sqlRowSet.getString(INDEX_DATAANVANGMATERIELEPERIODE), AfnemerindicatieConversie.NULL_VALUE),
                    StringUtils.defaultIfEmpty(sqlRowSet.getString(INDEX_DATEINDEVOLGEN), AfnemerindicatieConversie.NULL_VALUE),
                    sqlRowSet.getString(INDEX_INDAG)
            ), fos, StandardCharsets.UTF_8);
        }
    } catch (IOException e) {
        throw new IllegalStateException(e);
    }
}
 

import org.springframework.jdbc.support.rowset.SqlRowSet; //導入依賴的package包/類
@Override
public void dumpHisAfnemerindicatieTabel(final File outputFile) {
    LOGGER.info("Genereer hisafnemerindicatie rijen naar {}", outputFile.getAbsolutePath());
    try (FileOutputStream fos = new FileOutputStream(outputFile)) {
        final SqlRowSet sqlRowSet = new JdbcTemplate(masterDataSource).queryForRowSet("select hpa.* from autaut.his_persafnemerindicatie hpa\n"
                + "inner join (select pa.id as afnemerindid \n"
                + "from autaut.persafnemerindicatie pa, autaut.levsautorisatie la where pa.levsautorisatie = la.id and la.dateinde is null) \n"
                + "as x on hpa.persafnemerindicatie = x.afnemerindid");
        while (sqlRowSet.next()) {
            IOUtils.write(String.format("%s,%s,%s,%s,%s,%s,%s,%s%n",
                    sqlRowSet.getString(INDEX_HIS_ID),
                    sqlRowSet.getString(INDEX_HIS_PERSAFNEMERINDICATIE),
                    sqlRowSet.getString(INDEX_HIS_TSREG),
                    StringUtils.defaultIfBlank(sqlRowSet.getString(INDEX_HIS_TSVERVAL), AfnemerindicatieConversie.NULL_VALUE),
                    StringUtils.defaultIfBlank(sqlRowSet.getString(INDEX_HIS_DIENSTINHOUD), AfnemerindicatieConversie.NULL_VALUE),
                    StringUtils.defaultIfBlank(sqlRowSet.getString(INDEX_HIS_DIENSTVERVAL), AfnemerindicatieConversie.NULL_VALUE),
                    StringUtils.defaultIfBlank(sqlRowSet.getString(INDEX_HIS_DATAANVANGMATERIELEPERIODE), AfnemerindicatieConversie.NULL_VALUE),
                    StringUtils.defaultIfBlank(sqlRowSet.getString(INDEX_HIS_DATEINDEVOLGEN), AfnemerindicatieConversie.NULL_VALUE)
            ), fos, StandardCharsets.UTF_8);
        }
    } catch (IOException e) {
        throw new IllegalStateException(e);
    }
}
 

import org.springframework.jdbc.support.rowset.SqlRowSet; //導入依賴的package包/類
@Override
@Transactional
public PrintNode loadRow(Long nodeid) throws SaodException {
	String query = sqlQueries.getQuery("select_row_node_id.sql");
	final SqlRowSet queryForRowSet = this.jdbcTemplate.queryForRowSet(query, nodeid);

	while (queryForRowSet.next()) {
		PrintNode node = new PrintNode(nodeid);
		node.setParent(queryForRowSet.getLong(2));
		node.setLocalSize(queryForRowSet.getLong(3));
		node.setDirSize(queryForRowSet.getLong(4));
		return node;
	}

	return null;
}
 

import org.springframework.jdbc.support.rowset.SqlRowSet; //導入依賴的package包/類
@Override
@Transactional
public Map<Long, Long> selectParentNodeId(List<Long> child_id) throws SaodException {
	NamedParameterJdbcTemplate jdbcNamesTpl = new NamedParameterJdbcTemplate(this.jdbcTemplate);

	final String query = sqlQueries.getQuery("select_parent_node_id.sql");
	final Map<Long, Long> libelle = new HashMap<>();

	for (int i = 0; i < child_id.size(); i += MAX_NUM_EXP_LIST) {
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		parameters.addValue("ids", child_id.subList(i, Math.min(i + MAX_NUM_EXP_LIST, child_id.size())));

		final SqlRowSet queryForRowSet = jdbcNamesTpl.queryForRowSet(query, parameters);
		while (queryForRowSet.next()) {
			libelle.put(queryForRowSet.getLong(1), queryForRowSet.getLong(2));
		}

	}

	return libelle;
}
 

import org.springframework.jdbc.support.rowset.SqlRowSet; //導入依賴的package包/類
/**
 * getAllUsers
 */
public List<User> getAllUsers() {
    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    List<User> userList = new ArrayList<User>();
    SqlRowSet rowset = jdbcTemplate.queryForRowSet("select * from users_auto");

    while (rowset.next()) {
        User user = new User();
        user.setId(rowset.getInt("ID"));
        user.setName(rowset.getString("NAME"));
        user.setMail(rowset.getString("MAIL"));
        userList.add(user);
    }

    return userList;
}
 

import org.springframework.jdbc.support.rowset.SqlRowSet; //導入依賴的package包/類
private void updatePasswordLastUpdated()
{
    try
    {
        String timestamp = DateUtils.getLongDateString();

        SqlRowSet resultSet = jdbcTemplate.queryForRowSet( "SELECT * from users WHERE passwordlastupdated IS NULL" );

        while ( resultSet.next() )
        {
            String sql = "UPDATE users SET passwordlastupdated = '" + timestamp + "' WHERE passwordlastupdated IS NULL";
            jdbcTemplate.update( sql );
        }
    }
    catch ( Exception ex ) // Log and continue
    {
        log.error( "Problem updating passwordLastUpdated on table user: " + ex.getMessage() );
    }
}
 

import org.springframework.jdbc.support.rowset.SqlRowSet; //導入依賴的package包/類
@Override
public Rectangle getRectangle( EventQueryParams params )
{
    String clusterField = params.getCoordinateField();
    String quotedClusterField = statementBuilder.columnQuote( clusterField );
            
    String sql = "select count(psi) as " + COL_COUNT + ", ST_Extent(" + quotedClusterField + ") as " + COL_EXTENT + " ";
    
    sql += getFromWhereClause( params );

    log.debug( String.format( "Analytics event count and extent SQL: %s", sql ) );
    
    Rectangle rectangle = new Rectangle();
    
    SqlRowSet rowSet = jdbcTemplate.queryForRowSet( sql );
    
    if ( rowSet.next() )
    {
        Object extent = rowSet.getObject( COL_EXTENT );
        
        rectangle.setCount( rowSet.getLong( COL_COUNT ) );
        rectangle.setExtent( extent != null ? String.valueOf( rowSet.getObject( COL_EXTENT ) ) : null );
    }
    
    return rectangle;
}
 

import org.springframework.jdbc.support.rowset.SqlRowSet; //導入依賴的package包/類
@Override
public Grid addRows( SqlRowSet rs )
{
    int cols = rs.getMetaData().getColumnCount();

    while ( rs.next() )
    {
        addRow();

        for ( int i = 1; i <= cols; i++ )
        {
            addValue( rs.getObject( i ) );
        }
    }

    return this;
}
 

import org.springframework.jdbc.support.rowset.SqlRowSet; //導入依賴的package包/類
/**
 * Convert a name to an object identifier.
 * <p>
 * If an object is renamed, the old name remains as an alias.
 * The separate name resolution handles that case.
 * 
 * @param name  the name, not null
 * @param onDeleted  how to handle deletion
 * @return the object identifier, null if not found
 */
ObjectId lookupName(String name, OnDeleted onDeleted) {
  ArgumentChecker.notNull(name, "name");
  try (Timer.Context context = _lookupNameTimer.time()) {
    final DbMapSqlParameterSource args = createParameterSource()
      .addValue("name_ci", caseInsensitive(name));
    final NamedParameterJdbcOperations namedJdbc = getDbConnector().getJdbcTemplate();
    final String sql = getElSqlBundle().getSql("GetIdByName", args);
    SqlRowSet rowSet = namedJdbc.queryForRowSet(sql, args);
    if (rowSet.next() == false) {
      throw new DataNotFoundException("Name not found: " + name);
    }
    String deleted = rowSet.getString("DELETED");
    if (deleted.equals("Y")) {
      if (onDeleted == OnDeleted.RETURN_NULL) {
        return null;
      } else if (onDeleted == OnDeleted.EXCEPTION) {
        throw new DataNotFoundException("Name not found: " + name);
      }
    }
    return createObjectId(rowSet.getLong("DOC_ID")).getObjectId();
  }
}
 

import org.springframework.jdbc.support.rowset.SqlRowSet; //導入依賴的package包/類
private static void dump(String sql) {
    logger.fine("\n" + sql);
    SqlRowSet rs = dao.getJdbcTemplate().queryForRowSet(sql);
    try {
        SqlRowSetMetaData rsmd = rs.getMetaData();
        int c = rsmd.getColumnCount();
        StringBuffer buff = new StringBuffer();
        for (int i = 1; i <= c; i++) {
            buff.append(rsmd.getColumnLabel(i));
            buff.append(i == c ? "\n" : "\t");
        }
        logger.fine(buff.toString());
        buff.delete(0, buff.length());
        while (rs.next()) {
            for (int i = 1; i <= c; i++) {
                buff.append(rs.getObject(i));
                buff.append(i == c ? "\n" : "\t");
            }
            logger.fine(buff.toString());
            buff.delete(0, buff.length());
        }
        logger.fine("================\n\n");
    } catch (Exception e) {
        e.printStackTrace();
    }
}
 

import org.springframework.jdbc.support.rowset.SqlRowSet; //導入依賴的package包/類
public <T> T streamQuery(String sql, Function<Stream<SqlRow>, ? extends T> streamer, Object... args) {
    return query(sql, resultSet -> {
        final SqlRowSet rowSet = new ResultSetWrappingSqlRowSet(resultSet);
        final SqlRow sqlRow = new SqlRowAdapter(rowSet);

        Supplier<Spliterator<SqlRow>> supplier = () -> Spliterators.spliteratorUnknownSize(new Iterator<SqlRow>() {
            @Override
            public boolean hasNext() {
                return !rowSet.isLast();
            }

            @Override
            public SqlRow next() {
                if (!rowSet.next()) {
                    throw new NoSuchElementException();
                }
                return sqlRow;
            }
        }, Spliterator.IMMUTABLE);
        return streamer.apply(StreamSupport.stream(supplier, Spliterator.IMMUTABLE, false));

    }, args);
}
 

import org.springframework.jdbc.support.rowset.SqlRowSet; //導入依賴的package包/類
public Stream<SqlRow> stream() throws SQLException {
    final SqlRowSet rowSet = new ResultSetWrappingSqlRowSet(preparedStatement.executeQuery());
    final SqlRow sqlRow = new SqlRowAdapter(rowSet);

    Supplier<Spliterator<SqlRow>> supplier = () -> Spliterators.spliteratorUnknownSize(new Iterator<SqlRow>() {
        @Override
        public boolean hasNext() {
            return !rowSet.isLast();
        }

        @Override
        public SqlRow next() {
            if (!rowSet.next()) {
                throw new NoSuchElementException();
            }
            return sqlRow;
        }
    }, Spliterator.IMMUTABLE);
    return StreamSupport.stream(supplier, Spliterator.IMMUTABLE, false);
}
 

import org.springframework.jdbc.support.rowset.SqlRowSet; //導入依賴的package包/類
public List<CmsField> listFields(String tablename) {
	String sql = " desc  " + tablename;
	List<CmsField> fields = new ArrayList<CmsField>();
	SqlRowSet set = getJdbcTemplate().queryForRowSet(sql);
	while (set.next()) {
		CmsField field = new CmsField();
		field.setName(set.getString(1));
		field.setFieldType(set.getString(2));
		field.setNullable(set.getString(3));
		field.setFieldProperty(set.getString(4));
		field.setFieldDefault(set.getString(5));
		field.setExtra(set.getString(6));
		fields.add(field);
	}
	return fields;
}