Example usage for javax.sql.rowset CachedRowSet next

List of usage examples for javax.sql.rowset CachedRowSet next

  1. HOME
  2. Java
  3. javax
  4. javax.sql.rowset.*
  5. CachedRowSet
  6. next

Introduction

In this page you can find the example usage for javax.sql.rowset CachedRowSet next.

Prototype

boolean next() throws SQLException;

Source Link

Document

Moves the cursor forward one row from its current position.

Usage

From source file:uk.ac.ox.it.ords.api.database.structure.services.impl.hibernate.StructureServiceImpl.java

private String[] getPostgresVersionArray(String server) throws Exception {

    CachedRowSet results = this.runJDBCQuery("SELECT version()", null, server, null);
    results.next();
    String version = results.getString(1);

    String[] versionArray = null;
    String[] tempVersionArray = null;
    tempVersionArray = version.split(" ");
    version = tempVersionArray[1];//w w  w  .java2s.  c o  m
    versionArray = version.split("\\.");

    return versionArray;
}

From source file:uk.ac.ox.it.ords.api.database.structure.services.impl.hibernate.StructureServiceImpl.java

public List<HashMap<String, String>> getTableDescription(String databaseName, String tableName, String server)
        throws Exception {
    log.debug("getTableDescription");

    ArrayList<String> fields = new ArrayList<String>();
    fields.add("column_name");
    fields.add("data_type");
    fields.add("character_maximum_length");
    fields.add("numeric_precision");
    fields.add("numeric_scale");
    fields.add("column_default");
    fields.add("is_nullable");
    fields.add("ordinal_position");

    String query = String.format(
            "select %s from INFORMATION_SCHEMA.COLUMNS where table_name = %s ORDER BY ordinal_position ASC",
            StringUtils.join(fields.iterator(), ","), quote_literal(tableName));

    HashMap<String, String> columnDescription;
    List<HashMap<String, String>> columnDescriptions = new ArrayList<HashMap<String, String>>();
    CachedRowSet results = this.runJDBCQuery(query, null, server, databaseName);

    // First get all column names
    while (results.next()) {
        columnDescription = new HashMap<String, String>();
        for (String field : fields) {
            Object c = results.getObject(field);
            if (c != null) {
                columnDescription.put(field, c.toString());
            } else {
                columnDescription.put(field, null);
            }/*www  . j  a  v  a2  s  .  c o m*/
        }
        columnDescriptions.add(columnDescription);
    }

    return columnDescriptions;
}

From source file:uk.ac.ox.it.ords.api.database.structure.services.impl.hibernate.StructureServiceImpl.java

private int runCountSql(String sql, List<Object> parameters, String dbName, String databaseServer)
        throws Exception {
    CachedRowSet result = this.runJDBCQuery(sql, parameters, databaseServer, dbName);
    try {//w  w w .  j  ava  2 s. com
        // If count is 1, then a table with the given name was found
        while (result.next()) {
            return result.getInt("count");
        }
    } finally {
        if (result != null)
            result.close();
    }
    return 0;

}

From source file:uk.ac.ox.it.ords.api.database.structure.services.impl.hibernate.StructureServiceImpl.java

protected String tableComment(String databaseName, String databaseServer, String tableName) throws Exception {
    log.debug("tableComment");
    // obj_description gives the comment for an object, but needs the unique
    // oid for that object. To find the oid for a table from its name, we
    // need to to cast the table ident to regclass then to oid. 'pg_class'
    // is the catalog the table belongs to.
    String identifier = String.format("\'public.%s\'", quote_ident(tableName));
    String query = String.format("SELECT obj_description(%s::regclass::oid, 'pg_class') as comment",
            identifier);/*from  w ww  .jav  a 2s.co m*/

    String comment = "";
    CachedRowSet result = this.runJDBCQuery(query, null, databaseServer, databaseName);
    if (result == null) {
        return comment;
    }
    try {
        while (result.next()) {
            comment = result.getString("comment");
        }
    } finally {
        result.close();
    }
    return comment;
}

From source file:uk.ac.ox.it.ords.api.database.structure.services.impl.hibernate.StructureServiceImpl.java

@SuppressWarnings({ "rawtypes", "unchecked" })
public List<HashMap<String, String>> getForeignKeysFromPostgres(String databaseName, String databaseServer,
        String table) throws Exception {
    log.debug("getForeignKeysFromPostgres");

    String query = "SELECT " + "tc.constraint_name, tc.table_name, kcu.column_name, "
            + "ccu.table_name AS foreign_table_name, " + "ccu.column_name AS foreign_column_name "
            + "FROM information_schema.table_constraints tc " + "JOIN information_schema.key_column_usage kcu "
            + "ON tc.constraint_name = kcu.constraint_name "
            + "JOIN information_schema.constraint_column_usage ccu "
            + "ON ccu.constraint_name = tc.constraint_name " + "WHERE " + "constraint_type = 'FOREIGN KEY' "
            + "AND tc.table_name = ?";

    List foreignKeys = new ArrayList();
    HashMap<String, String> foreignKey;
    ArrayList<Object> parameters = new ArrayList<Object>();
    parameters.add(table);//from   w  w w.ja  v  a 2s . c  o  m
    CachedRowSet rs = this.runJDBCQuery(query, parameters, databaseServer, databaseName);
    // List<Object[]> results = this.runSQLQuery(query, null, null, null);
    while (rs.next()) {
        foreignKey = new HashMap();
        foreignKey.put("constraintName", rs.getString("constraint_name"));
        foreignKey.put("tableName", rs.getString("table_name"));
        foreignKey.put("columnName", rs.getString("column_name"));
        foreignKey.put("foreignTableName", rs.getString("foreign_table_name"));
        foreignKey.put("foreignColumnName", rs.getString("foreign_column_name"));

        foreignKeys.add(foreignKey);
    }

    return foreignKeys;
}

From source file:uk.ac.ox.it.ords.api.database.structure.services.impl.hibernate.StructureServiceImpl.java

protected String columnComment(String databaseName, String databaseServer, String tableName, String columnName)
        throws Exception {
    log.debug("columnComment");
    // col_description gives the comment stored for the given column.
    // As with obj_description, we need the oid of the table which we
    // acheieve/*w w w. jav a  2s.  c  o m*/
    // by casting to regclass then to oid. We also need the column number
    // within the table, which we get from the name by using a subquery to
    // look it up in pg_attribute (Again requiring the table oid).
    String query = "SELECT col_description(quote_ident(?)::regclass::oid, (SELECT attnum FROM pg_attribute WHERE attrelid = quote_ident(?)::regclass::oid AND attname = ?)) as comment";

    ArrayList<Object> parameters = new ArrayList<Object>();

    parameters.add(tableName);
    parameters.add(tableName);
    parameters.add(columnName);
    String comment = "";
    CachedRowSet result = this.runJDBCQuery(query, parameters, databaseServer, databaseName);
    if (result == null) {
        return comment;
    }
    try {
        while (result.next()) {
            comment = result.getString("comment");
        }
    } finally {
        result.close();
    }
    return comment;
}

From source file:uk.ac.ox.it.ords.api.database.structure.services.impl.hibernate.StructureServiceImpl.java

protected List<HashMap<String, Object>> getIndexesFromPostgres(String databaseName, String databaseServer,
        String table) throws Exception {
    String query = "SELECT " + "i.relname as indexname, " + "idx.indrelid::regclass as tablename, " + "ARRAY( "
            + "SELECT pg_get_indexdef(idx.indexrelid, k + 1, true) "
            + "FROM generate_subscripts(idx.indkey, 1) as k " + "ORDER BY k " + ") as colnames, "
            + "indisunique as isunique, " + "indisprimary as isprimary " + "FROM " + "pg_index as idx "
            + "JOIN pg_class as i " + "ON i.oid = idx.indexrelid "
            + "WHERE CAST(idx.indrelid::regclass as text) = quote_ident(?)";
    List<HashMap<String, Object>> indexes = new ArrayList<HashMap<String, Object>>();
    HashMap<String, Object> index;
    String type;/*from   w w w .  j  a  va 2  s  .c om*/
    ArrayList<Object> parameters = new ArrayList<Object>();
    parameters.add(table);
    CachedRowSet rs = this.runJDBCQuery(query, parameters, databaseServer, databaseName);
    // List<Object[]> results = this.runSQLQuery(command, null, null, null);
    while (rs.next()) {
        index = new HashMap<String, Object>();
        index.put("name", rs.getString("indexname"));
        ArrayList<String> columns = new ArrayList<String>();
        Array sqlArray = rs.getArray("colnames");
        Object[] cols = (Object[]) sqlArray.getArray();
        // ResultSet columnSet = sqlArray.getResultSet();
        for (Object column : cols) {
            //
            // PG may store the index columns as quoted identifiers, in which case we need
            // to unquote them to return via the API
            //
            columns.add(unquote(column.toString()));
        }
        index.put("columns", columns);
        if (rs.getBoolean("isprimary")) {
            type = "PRIMARY";
        } else if (rs.getBoolean("isunique")) {
            type = "UNIQUE";
        } else {
            type = "INDEX";
        }
        index.put("type", type);

        indexes.add(index);
    }

    return indexes;
}

From source file:CSVTools.CsvToolsApi.java

public void writeResultSetIntoCSVFile(CachedRowSet resultSet, String path) {

    PrintWriter csvWriter = null;
    try {//from ww  w .ja  va2s  .  c  om
        csvWriter = new PrintWriter(new File(path));

        ResultSetMetaData meta = resultSet.getMetaData();
        int numberOfColumns = meta.getColumnCount();
        String dataHeaders = "\"" + meta.getColumnName(1) + "\"";
        for (int i = 2; i < numberOfColumns + 1; i++) {
            dataHeaders += ",\"" + meta.getColumnName(i) + "\"";
        }
        csvWriter.println(dataHeaders);
        resultSet.beforeFirst();
        while (resultSet.next()) {
            String row = "\"" + resultSet.getString(1) + "\"";
            for (int i = 2; i < numberOfColumns + 1; i++) {
                row += ",\"" + resultSet.getString(i) + "\"";
            }
            csvWriter.println(row);
        }
        csvWriter.close();
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

From source file:com.oracle.tutorial.jdbc.CachedRowSetSample.java

public void testPaging() throws SQLException, MalformedURLException {

    CachedRowSet crs = null;
    this.con.setAutoCommit(false);

    try {/*from  ww  w . ja  v  a 2s  . com*/

        crs = new CachedRowSetImpl();
        crs.setUsername(settings.userName);
        crs.setPassword(settings.password);

        if (this.dbms.equals("mysql")) {
            crs.setUrl(settings.urlString + "?relaxAutoCommit=true");
        } else {
            crs.setUrl(settings.urlString);
        }
        crs.setCommand("select * from MERCH_INVENTORY");

        // Setting the page size to 4, such that we
        // get the data in chunks of 4 rows @ a time.
        crs.setPageSize(100);

        // Now get the first set of data
        crs.execute();

        crs.addRowSetListener(new ExampleRowSetListener());

        // Keep on getting data in chunks until done.

        int i = 1;
        do {
            System.out.println("Page number: " + i);
            while (crs.next()) {
                System.out.println("Found item " + crs.getInt("ITEM_ID") + ": " + crs.getString("ITEM_NAME"));
                if (crs.getInt("ITEM_ID") == 1235) {
                    int currentQuantity = crs.getInt("QUAN") + 1;
                    System.out.println("Updating quantity to " + currentQuantity);
                    crs.updateInt("QUAN", currentQuantity + 1);
                    crs.updateRow();
                    // Syncing the row back to the DB
                    crs.acceptChanges(con);
                }

            } // End of inner while
            i++;
        } while (crs.nextPage());
        // End of outer while

        // Inserting a new row
        // Doing a previous page to come back to the last page
        // as we ll be after the last page.

        int newItemId = 123456;

        if (this.doesItemIdExist(newItemId)) {
            System.out.println("Item ID " + newItemId + " already exists");
        } else {
            crs.previousPage();
            crs.moveToInsertRow();
            crs.updateInt("ITEM_ID", newItemId);
            crs.updateString("ITEM_NAME", "TableCloth");
            crs.updateInt("SUP_ID", 927);
            crs.updateInt("QUAN", 14);
            Calendar timeStamp;
            timeStamp = new GregorianCalendar();
            timeStamp.set(2006, 4, 1);
            crs.updateTimestamp("DATE_VAL", new Timestamp(timeStamp.getTimeInMillis()));
            crs.insertRow();
            crs.moveToCurrentRow();

            // Syncing the new row back to the database.
            System.out.println("About to add a new row...");
            crs.acceptChanges(con);
            System.out.println("Added a row...");
            this.viewTable(con);
        }
    } catch (SyncProviderException spe) {

        SyncResolver resolver = spe.getSyncResolver();

        Object crsValue; // value in the RowSet object
        Object resolverValue; // value in the SyncResolver object
        Object resolvedValue; // value to be persisted

        while (resolver.nextConflict()) {

            if (resolver.getStatus() == SyncResolver.INSERT_ROW_CONFLICT) {
                int row = resolver.getRow();
                crs.absolute(row);

                int colCount = crs.getMetaData().getColumnCount();
                for (int j = 1; j <= colCount; j++) {
                    if (resolver.getConflictValue(j) != null) {
                        crsValue = crs.getObject(j);
                        resolverValue = resolver.getConflictValue(j);

                        // Compare crsValue and resolverValue to determine
                        // which should be the resolved value (the value to persist)
                        //
                        // This example choses the value in the RowSet object,
                        // crsValue, to persist.,

                        resolvedValue = crsValue;

                        resolver.setResolvedValue(j, resolvedValue);
                    }
                }
            }
        }
    } catch (SQLException sqle) {
        JDBCTutorialUtilities.printSQLException(sqle);
    } finally {
        if (crs != null)
            crs.close();
        this.con.setAutoCommit(true);
    }

}

From source file:org.openecomp.sdnc.uebclient.SdncUebCallback.java

private void cleanUpExistingToscaServiceData(String serviceUUID) throws IOException {

    if (jdbcDataSource == null) {
        setJdbcDataSource();/* www .  j  a  v  a  2 s .co  m*/
    }
    try {
        int rowCount = 0;
        CachedRowSet data = jdbcDataSource
                .getData("SELECT * from SERVICE_MODEL where service_uuid = " + serviceUUID + ";", null, "");
        while (data.next()) {
            rowCount++;
        }
        if (rowCount != 0) {
            LOG.info("cleanUpExistingToscaData: " + serviceUUID);
            jdbcDataSource.writeData("DELETE from NETWORK_MODEL where service_uuid = " + serviceUUID + ";",
                    null, null);
            jdbcDataSource.writeData("DELETE from SERVICE_MODEL where service_uuid = " + serviceUUID + ";",
                    null, null);
        }

    } catch (SQLException e) {
        LOG.error("Could not clean up existing NETWORK_MODEL and SERVICE_MODEL for service_UUID " + serviceUUID,
                e);
    }

}