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:Main.java

public static void main(String[] args) throws Exception {
    CachedRowSet rs;
    String ROWSET_IMPL_CLASS = "com.sun.rowset.CachedRowSetImpl";

    Class c = Class.forName(ROWSET_IMPL_CLASS);
    rs = (CachedRowSet) c.newInstance();

    rs.setUrl("jdbc:postgresql:dbname");
    rs.setUsername("username");
    rs.setPassword("password");

    rs.setCommand("select * from members where name like ?");
    rs.setString(1, "I%");

    rs.execute();//from   w  w w.jav a  2  s  .c  om

    while (rs.next()) {
        if (rs.getInt("id") == 42) {
            rs.setString(1, "newString");
            rs.updateRow(); // Normal JDBC

            rs.acceptChanges();
        }
    }
    rs.close();
}

From source file:Main.java

public static void main(String[] args) throws Exception {
    CachedRowSet rs;

    // Create the class with class.forName to avoid importing
    // from the unsupported com.sun packages.
    Class c = Class.forName(ROWSET_IMPL_CLASS);
    rs = (CachedRowSet) c.newInstance();

    rs.setUrl("jdbc:postgresql:tmclub");
    rs.setUsername("ian");
    rs.setPassword("secret");

    rs.setCommand("select * from members where name like ?");
    rs.setString(1, "I%");

    // This will cause the RowSet to connect, fetch its data, and
    // disconnect
    rs.execute();/*  ww w  .  j  a v  a  2 s  .  c o m*/

    // Some time later, the client tries to do something.

    // Suppose we want to update data:
    while (rs.next()) {
        if (rs.getInt("id") == 42) {
            rs.setString(1, "Marvin");
            rs.updateRow(); // Normal JDBC

            // This additional call tells the CachedRowSet to connect
            // to its database and send the updated data back.
            rs.acceptChanges();
        }
    }

    // If we're all done...
    rs.close();
}

From source file:CachedRS.java

public static void main(String[] args) throws Exception {
    FileInputStream fis = new FileInputStream(CRS_FILE_LOC);
    ObjectInputStream in = new ObjectInputStream(fis);
    CachedRowSet crs = (CachedRowSet) in.readObject();
    fis.close();/*ww  w.  j  a v  a2  s  .  c o  m*/
    in.close();

    Class.forName("oracle.jdbc.driver.OracleDriver");
    crs.setUrl("jdbc:oracle:thin:@localhost:1521:ORCL");
    crs.setUsername("yourName");
    crs.setPassword("mypwd");
    String sql = "SELECT SSN, Name, Salary, Hiredate FROM Employees WHERE SSN=?";
    crs.setCommand(sql);
    crs.setInt(1, 111111111);
    crs.execute();

    FileOutputStream fos = new FileOutputStream(CRS_FILE_LOC);
    ObjectOutputStream out = new ObjectOutputStream(fos);
    out.writeObject(crs);
    out.close();
    crs.close();

    fis = new FileInputStream(CRS_FILE_LOC);
    in = new ObjectInputStream(fis);
    crs = (CachedRowSet) in.readObject();
    fis.close();
    in.close();

    while (crs.next()) {
        System.out.print("SSN: " + crs.getInt("ssn"));
        System.out.print(", Name: " + crs.getString("name"));
        System.out.print(", Salary: $" + crs.getDouble("salary"));
        System.out.print(", HireDate: " + crs.getDate("hiredate"));
    }
    crs.close();
}

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

private void viewFilteredRowSet(FilteredRowSet frs) throws SQLException {

    if (frs == null) {
        return;/*from   ww  w. jav  a2s  . co m*/
    }

    CachedRowSet crs = (CachedRowSet) frs;

    while (crs.next()) {
        if (crs == null) {
            break;
        }
        System.out.println(crs.getInt("STORE_ID") + ", " + crs.getString("CITY") + ", " + crs.getInt("COFFEE")
                + ", " + crs.getInt("MERCH") + ", " + crs.getInt("TOTAL"));
    }
}

From source file:entity.Chart.java

private void init() {
    try {//from   w ww. j  a  v a 2s  .  com
        if (this.reportID < 0) {
            this.reportName = "Maj_Runs";
            this.sql = "exec sp_getMaj_Runs @BotID = " + Math.abs(reportID);
            this.server = SQLHelper.LOCAL_IP;
            this.database = SQLHelper.DB_QA_DATA;
            this.reportType = "tablechart";
            this.comments = "";
            this.startRow = 1;
            this.referQueryID = -1;
            this.groupClause = null;
            return;
        }
        HashMap<String, Object> params = new HashMap<String, Object>();
        params.put("node", reportID);
        SqlCommand command = new SqlCommand(SQLHelper.LOCAL_IP, SQLHelper.DB_QA, CommandType.StoredProcedure,
                "sp_getQuery", params);
        HashMap<String, Object> cells = SQLHelper.executeCommand(command, new MapRowHandler());

        this.reportName = (String) cells.get("name");
        this.sql = (String) cells.get("queryText");
        this.server = (String) cells.get("server");
        if (this.server.equals("127.0.0.1") || this.server.equals("localhost")) {
            this.server = SQLHelper.LOCAL_IP;
        }
        this.database = (String) cells.get("database");
        this.reportType = (String) cells.get("queryType");
        this.comments = (String) cells.get("comments");
        this.startRow = (Integer) cells.get("startRow");
        this.referQueryID = (Integer) cells.get("ReferQueryID");
        this.groupClause = (String) cells.get("GroupClause");
        this.totalColumnName = (String) cells.get("TotalItemColumn");
        //set report parameters
        command = new SqlCommand(SQLHelper.LOCAL_IP, SQLHelper.DB_QA, CommandType.Text,
                "SELECT * FROM Qa_Query_Params WHERE ReportID = " + reportID);
        CachedRowSet rowSet = SQLHelper.executeCommand(command, new CachedRowSetResultHandler());
        rParams = new QueryParameter[rowSet.size()];
        int i = 0;
        while (rowSet.next()) {
            rParams[i] = new QueryParameter();
            rParams[i].name = rowSet.getString("Name");
            rParams[i].value = rowSet.getString("DefaultValue");
            rParams[i].type = rowSet.getString("DataType");
            i++;
        }
    } catch (Exception ex) {
        throw new RuntimeException(ex);
    }
}

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

public ColumnRequest getColumnMetadata(OrdsPhysicalDatabase database, String tableName, String columnName,
        boolean staging) throws Exception {

    String server = database.getDatabaseServer();
    String databaseName = database.getDbConsumedName();

    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 command = String.format(
            "select %s from INFORMATION_SCHEMA.COLUMNS where table_name = ? and column_name = ? ORDER BY ordinal_position ASC",
            StringUtils.join(fields.iterator(), ","));

    CachedRowSet results = this.runJDBCQuery(command, createParameterList(tableName, columnName), server,
            databaseName);//w  w  w .  j a va2 s  .  c  o  m

    if (!results.next()) {
        return null;
    }

    ColumnRequest column = new ColumnRequest();

    column.setNewname(results.getString("column_name"));
    column.setDatatype(results.getString("data_type"));
    column.setDefaultvalue(results.getString("column_default"));
    column.setNullable(results.getString("is_nullable").equals("YES"));

    boolean autoIncrement = false;
    // Parse the default value to an interface-friendly
    // format, and identify if the field is auto-incremented
    if (column.getDefaultvalue() != null) {
        if (column.getDefaultvalue().equals("''::text")) { // CSV
            column.setDefaultvalue("");
        } else if (column.getDefaultvalue().matches("nextval\\('[A-Za-z0-9_\"]+'::regclass\\)")) {
            column.setDefaultvalue("");
            autoIncrement = true;
        } else if (column.getDefaultvalue().startsWith("NULL::")) {
            column.setDefaultvalue(null);
        }
    }
    column.setAutoincrement(autoIncrement);

    return column;
}

From source file:org.openecomp.sdnc.sli.resource.sql.SqlResource.java

@Override
public QueryStatus exists(String resource, String key, String prefix, SvcLogicContext ctx)
        throws SvcLogicException {

    DbLibService dblibSvc = getDbLibService();
    if (dblibSvc == null) {
        return (QueryStatus.FAILURE);
    }//from   w w w  .  j  a v  a2 s.  com

    String theStmt = resolveCtxVars(key, ctx);

    try {
        CachedRowSet results = dblibSvc.getData(theStmt, null, null);

        if (!results.next()) {
            return (QueryStatus.NOT_FOUND);
        }

        int numRows = results.getInt(1);

        if (numRows > 0) {
            return (QueryStatus.SUCCESS);
        } else {
            return (QueryStatus.NOT_FOUND);
        }
    } catch (Exception e) {
        LOG.error("Caught SQL exception", e);
        return (QueryStatus.FAILURE);
    }
}

From source file:org.openecomp.sdnc.sli.resource.sql.SqlResource.java

public String parameterizedQuery(Map<String, String> parameters, SvcLogicContext ctx) throws SvcLogicException {
    DbLibService dblibSvc = getDbLibService();
    String prefix = parameters.get("prefix");
    String query = parameters.get("query");

    ArrayList<String> arguments = new ArrayList<String>();
    for (Entry<String, String> a : parameters.entrySet()) {
        if (a.getKey().startsWith("param")) {
            arguments.add(a.getValue());
        }//from w  w  w. j a  v  a 2s  .co  m
    }

    try {
        if (dblibSvc == null) {
            return mapQueryStatus(QueryStatus.FAILURE);
        }
        if (query.contains("count") || query.contains("COUNT")) {
            CachedRowSet results = dblibSvc.getData(query, arguments, null);

            if (!results.next()) {
                return mapQueryStatus(QueryStatus.FAILURE);
            }

            int numRows = results.getInt(1);
            ctx.setAttribute(prefix + ".count", String.valueOf(numRows));
            if (numRows > 0) {
                return "true";
            } else {
                return "false";
            }
        } else if (query.startsWith("select") || query.startsWith("SELECT")) {
            CachedRowSet results = dblibSvc.getData(query, arguments, null);
            if (!results.next()) {
                return mapQueryStatus(QueryStatus.NOT_FOUND);
            } else {
                saveCachedRowSetToCtx(results, ctx, prefix, dblibSvc);
            }
        } else {
            if (!dblibSvc.writeData(query, arguments, null)) {
                return mapQueryStatus(QueryStatus.FAILURE);
            }
        }
        return mapQueryStatus(QueryStatus.SUCCESS);
    } catch (SQLException e) {
        LOG.error("Caught SQL exception", e);
        return mapQueryStatus(QueryStatus.FAILURE);
    }
}

From source file:org.openecomp.sdnc.sli.resource.sql.SqlResource.java

public QueryStatus query(String resource, boolean localOnly, String select, String key, String prefix,
        String orderBy, SvcLogicContext ctx) throws SvcLogicException {

    DbLibService dblibSvc = getDbLibService();

    if (dblibSvc == null) {
        return (QueryStatus.FAILURE);
    }/*from ww  w .  j av a2s  . c  o m*/

    String sqlQuery = resolveCtxVars(key, ctx);

    try {

        CachedRowSet results = dblibSvc.getData(sqlQuery, null, null);

        QueryStatus retval = QueryStatus.SUCCESS;

        if (!results.next()) {
            retval = QueryStatus.NOT_FOUND;
            LOG.debug("No data found");
        } else {
            saveCachedRowSetToCtx(results, ctx, prefix, dblibSvc);
        }
        return (retval);
    } catch (Exception e) {
        LOG.error("Caught SQL exception", e);
        return (QueryStatus.FAILURE);
    }
}

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

public void updateColumn(OrdsPhysicalDatabase database, String tableName, String columnName,
        ColumnRequest request, boolean staging) throws Exception {
    String databaseName = database.getDbConsumedName();
    if (staging) {
        databaseName = this.calculateStagingName(databaseName);
    }//from www.ja  va2s  .c o  m
    String server = database.getDatabaseServer();

    if (!this.checkColumnExists(columnName, tableName, databaseName, server)) {
        throw new NotFoundException(String.format("Column name %s does not exist", columnName));
    }
    log.debug("doQuery");
    String newName = request.getNewname();
    Boolean nullable = request.isNullable();
    String datatype = request.getDatatype();
    String defaultValue = request.getDefaultvalue();
    Boolean autoinc = request.isAutoincrement();
    String sequenceName = "";
    String query;
    String message = "";

    // Check that some new metadata has been specified
    if ((newName == null || newName.isEmpty()) && nullable == null && (datatype == null || datatype.isEmpty())
            && defaultValue == null && autoinc == null) {
        log.error("Null values set in column request");
        throw new BadParameterException("Null values in column request");
    }

    if (autoinc != null) {
        // If the auto-incrament status is being changed
        if (autoinc) {
            // If we're "enabling" autoincrement, check that a
            // default value hasn't been set as well.
            if (defaultValue != null && !defaultValue.isEmpty()) {
                log.error("Specified a default for an auto-increment field");
                throw new BadParameterException("You cannot specify a default for an auto-increment field");
            }
            // Get the data type and any current sequence for the
            // column.
            String command = "SELECT data_type, pg_get_serial_sequence(?, ?) AS sequence"
                    + " FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = ?" + " AND column_name = ?";
            List<Object> parameters = this.createParameterList(tableName, columnName, tableName, columnName);
            CachedRowSet results = this.runJDBCQuery(command, parameters, server, databaseName);
            String currentDatatype = "";
            while (results.next()) {
                currentDatatype = results.getString("data_type");
                sequenceName = results.getString("sequence");
            }
            if (sequenceName != null && !sequenceName.isEmpty()) {
                message = String.format("Cannot add auto-increment to field %s: it is already auto-incremented",
                        columnName);
                log.error(message);
                throw new BadParameterException(message);
            }
            if (!integerTypes.contains(currentDatatype.toLowerCase()) && (datatype == null || datatype.isEmpty()
                    || !integerTypes.contains(datatype.toLowerCase()))) {
                if (datatype != null && !integerTypes.contains(datatype)) {
                    message = String.format("Attempted to create sequence for column %s with datatype %s",
                            columnName, datatype);
                } else {
                    message = String.format("Attempted to create sequence for column %s with datatype %s",
                            columnName, currentDatatype);
                }
                throw new BadParameterException("Only integer fields can have auto-increment enabled");
            }
        } else {
            // If we're "disabling" autoincrement, check that there's
            // an existing sequence to remove.
            String command = "SELECT pg_get_serial_sequence(?, ?) AS sequence";
            List<Object> parameters = this.createParameterList(tableName, columnName);
            CachedRowSet results = this.runJDBCQuery(command, parameters, server, databaseName);
            if (!results.first()) {
                log.error("Attempt to remove autoincrement where non is set");
                throw new BadParameterException("Auto-increment is not set so cannot be removed");
            } else {
                sequenceName = results.getString("sequence");
            }

        }
    }
    // If a new name for the column is specified, check that a column
    // with that name doesn't already exist in the table
    if (newName != null && !newName.isEmpty() && checkColumnExists(newName, tableName, databaseName, server)) {

        log.error("Attempted to rename column %s to existing name %s in table %s", columnName, newName,
                tableName);
        message = String.format("Cannot rename field to %s: field with that name already exists in table %s",
                newName, tableName);
        throw new BadParameterException(message);
    }
    // Validation all done, now perform the specified operations
    // If we're changing the nullability, create an execute the
    // approprate ALTER TABLE query
    if (nullable != null) {
        String operation;
        if (nullable) {
            operation = "DROP";
            message += String.format("Field %s no longer nullable", columnName) + "\n";
        } else {
            operation = "SET";
            message += String.format("Field %s now nullable", columnName) + "\n";
        }
        query = String.format("ALTER TABLE %s ALTER %s %s NOT NULL;", quote_ident(tableName),
                quote_ident(columnName), operation);
        this.runJDBCQuery(query, null, server, databaseName);
    }
    ArrayList<String> statements = new ArrayList<String>();
    if (datatype != null && !datatype.isEmpty()) {
        // If the data type is being altered get the existing data type
        String convertedCol = quote_ident(columnName);
        String command = "SELECT data_type" + " FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = ?"
                + " AND column_name = ?";
        List<Object> parameters = this.createParameterList(tableName, columnName);
        CachedRowSet results = this.runJDBCQuery(command, parameters, server, databaseName);
        String fromDataType = "";
        while (results.next()) {
            fromDataType = results.getString("data_type");
        }
        if (fromDataType.equals("integer") || fromDataType.equals("bigint")) {
            if (datatype.toLowerCase().contains("date") || datatype.toLowerCase().contains("time")) {
                convertedCol = String.format("to_timestamp(%s)", quote_ident(columnName));
            }
        } else if (fromDataType.contains("date") || fromDataType.contains("time")) {
            if (datatype.toLowerCase().equals("integer") || datatype.toLowerCase().equals("bigint")) {
                convertedCol = String.format("extract(epoch from %s)", quote_ident(columnName));
            }
        }
        datatype = SqlDesignerTranslations.convertDatatypeForPostgres(request.getDatatype());
        statements.add(String.format("ALTER TABLE %1$s ALTER %2$s TYPE %3$s USING CAST(%4$s AS %3$s)",
                quote_ident(tableName), quote_ident(columnName), datatype, convertedCol));
    }

    if (autoinc != null) {
        // If we're changing the autoincrement status
        if (autoinc) {
            // If we're enabline autoincrement, create a new sequence
            // and attach it to the column, then alter the default
            // value of the column to the next value in the sequence
            sequenceName = generateSequenceName(tableName, columnName);
            statements.add(String.format("CREATE SEQUENCE %s", quote_ident(sequenceName)));

            statements.add(String.format("ALTER SEQUENCE %s OWNED BY %s.%s", quote_ident(sequenceName),
                    quote_ident(tableName), quote_ident(columnName)));

            statements.add(String.format("ALTER TABLE %s ALTER %s SET DEFAULT nextval('%s')",
                    quote_ident(tableName), quote_ident(columnName), sequenceName));

            // message += String.format(emd.getMessage("Rst066").getText(),
            // columnName);
        } else {
            // If we're disabling autoincrement, remove the default
            // value and drop the sequence.
            statements.add(String.format("ALTER TABLE %s ALTER %s DROP DEFAULT", quote_ident(tableName),
                    quote_ident(columnName)));

            statements.add(String.format("DROP SEQUENCE %s", sequenceName));
            // message += String.format(emd.getMessage("Rst067").getText(),
            // columnName);
        }
    }

    // Always do defaultValue after autoinc, in case we're removing
    // an autoinc and adding a default.
    if (defaultValue != null) {
        if (defaultValue.isEmpty()) {
            statements.add(String.format("ALTER TABLE %s ALTER %s DROP DEFAULT", quote_ident(tableName),
                    quote_ident(columnName)));

            // message += String.format(emd.getMessage("Rst023").getText(),
            // columnName)+"\n";
        } else {
            statements.add(String.format("ALTER TABLE %s ALTER %s SET DEFAULT %s", quote_ident(tableName),
                    quote_ident(columnName), quote_literal(defaultValue)));
            // message += String.format(emd.getMessage("Rst024").getText(),
            // columnName,
            // defaultValue)+"\n";
        }
    }
    // Always do the rename last so that everything else works.
    if (newName != null && !newName.isEmpty()) {

        statements.add(String.format("ALTER TABLE %s RENAME %s to %s;", quote_ident(tableName),
                quote_ident(columnName), quote_ident(newName)));

        // message += String.format(emd.getMessage("Rst019").getText(),
        // columnName, newName)+"\n";
    }

    // run em in 1 go
    this.runSQLStatements(statements, server, databaseName);
}