Example usage for javax.sql.rowset CachedRowSet getInt

Introduction

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

Prototype

int getInt(int columnIndex) throws SQLException;

Source Link

Document

Retrieves the value of the designated column in the current row of this ResultSet object as an int in the Java programming language.

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 ww .  j  av  a 2 s  .co  m*/

    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  ava2 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();//from   w  w  w  .j  a  v  a 2s .co  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  w  w  w.  jav a 2s  .  c  o  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:com.oracle.tutorial.jdbc.StateFilter.java

public boolean evaluate(RowSet rs) {

    CachedRowSet frs = (CachedRowSet) rs;
    boolean evaluation = false;
    try {/*w w w.  j  ava 2s  .c om*/
        int columnValue = -1;

        if (this.colNumber > 0) {
            columnValue = frs.getInt(this.colNumber);
        } else if (this.colName != null) {
            columnValue = frs.getInt(this.colName);
        } else {
            return false;
        }

        if ((columnValue >= this.lo) && (columnValue <= this.hi)) {
            evaluation = true;
        }
    } catch (SQLException e) {
        JDBCTutorialUtilities.printSQLException(e);
        return false;
    } catch (NullPointerException npe) {
        System.out.println("NullPointerException caught");
        return false;
    }
    return evaluation;
}

---

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

public void testPaging() throws SQLException, MalformedURLException {

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

    try {/* w  w w  . j a  v a 2  s. c o  m*/

        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.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  ww .  j a  v a2 s.  c o m*/

    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. ja  v a2 s .  c om*/
    }

    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:uk.ac.ox.it.ords.api.database.structure.services.impl.hibernate.ConstraintServiceImpl.java

@Override
public void createConstraint(OrdsPhysicalDatabase database, String tableName, String constraintName,
        ConstraintRequest newConstraint, boolean staging) throws Exception {
    String query = "";

    String databaseName = database.getDbConsumedName();
    if (staging) {
        databaseName = this.calculateStagingName(databaseName);
    }/*from w w  w  .j av a2 s . c  om*/
    String server = database.getDatabaseServer();
    // message = String.format(emd.getMessage("Rst048").getText(),
    // constraintName);
    Boolean isUnique = newConstraint.isUnique();
    Boolean isForeign = newConstraint.isForeign();
    Boolean isPrimary = newConstraint.isPrimary();

    List<String> columnsList = new ArrayList<String>();
    String[] columnsArray = newConstraint.getColumns();
    String columns = "";

    if ((isUnique != null && isUnique) || (isForeign != null && isForeign)
            || (isPrimary != null && isPrimary)) {
        // If we're creating a Unique, Foreign or Primary Key constraint
        if (columnsArray == null || columnsArray.length == 0) {
            // If no columns are specified, return an error
            // message = emd.getMessage("Rst035").getText();
            throw new BadParameterException("No columns specified");
        } else if ((isUnique != null && isUnique) || (isPrimary != null && isPrimary)) {
            // If we're creating a Unique or Primary Key constraint,
            // join the columns into a string.
            for (String column : newConstraint.getColumns()) {
                columnsList.add(quote_ident(column));
            }
            columns = StringUtils.join(columnsList.iterator(), ",");
        } else {
            // If we're creating a foreign key, make sure there's
            // only one column
            if (columnsArray.length > 1) {
                // message = emd.getMessage("Rst068").getText();
                throw new BadParameterException("Only 1 column can be specified for a foreign key");
            }
        }
    }
    // Check that the specified table exists
    if (!this.checkTableExists(tableName, databaseName, server)) {
        log.error("Tried to create constraint %s for non-existant table %s", constraintName, tableName);
        // message = String.format(emd.getMessage("Rst052").getText(),
        // tableName);
        throw new NotFoundException();
    }

    // Get the next value from a special sequence created when the
    // database is first cloned in ORDS, which makes sure we can
    // create a unique name for the constraint
    String conIdQuery = "SELECT nextval('ords_constraint_seq'::regclass) AS id";
    String uniqueConstraintName = "";
    CachedRowSet result = this.runJDBCQuery(conIdQuery, null, server, databaseName);

    // Object result = this.singleResultQuery(conIdQuery, databaseName,
    // userName, password);
    if (result != null && result.size() > 0) {
        // Actually generate a name for the constraint
        result.first();
        int conId = result.getInt("id");
        uniqueConstraintName = String.format(constraintName + "_%d", conId);
    } else {
        uniqueConstraintName = constraintName;
    }

    // Generate the SQL for creating the constraint
    if (isUnique != null && isUnique) {
        query = String.format("ALTER TABLE %s ADD CONSTRAINT %s UNIQUE (%s)", quote_ident(tableName),
                quote_ident(uniqueConstraintName), columns);
    } else if (isPrimary != null && isPrimary) {
        query = String.format("ALTER TABLE %s ADD CONSTRAINT %s PRIMARY KEY (%s)", quote_ident(tableName),
                quote_ident(uniqueConstraintName), columns);
    } else if (isForeign != null && isForeign) {
        String column = newConstraint.getColumns()[0];
        String refTable = newConstraint.getReftable();

        String refColumn = newConstraint.getRefcolumn();

        // If this is a foreign key, make sure there is a
        // referenced table specified
        if (refTable == null || refTable.isEmpty()) {
            // message = emd.getMessage("Rst049").getText();
            throw new BadParameterException("A foreign key must have a reference table specified");
        }

        // Make sure there is a referenced column specified
        if (refColumn == null || refColumn.isEmpty()) {
            // message = emd.getMessage("Rst051").getText();
            throw new BadParameterException("A reference column must be specified");
        }

        query = String.format("ALTER TABLE %s ADD CONSTRAINT %s FOREIGN KEY (%s) " + "REFERENCES %s (%s)",
                quote_ident(tableName), quote_ident(uniqueConstraintName), quote_ident(column),
                quote_ident(refTable), quote_ident(refColumn));
    } else {
        // If this isn't a Unique, Foreign or Primary key constraint
        // make sure a check expression is defined and generate the
        // SQL. Check constraints currently aren't implemented in
        // the Schema Designer interface.
        String checkExpression = newConstraint.getCheckExpression();
        if (checkExpression == null || checkExpression.isEmpty()) {
            // message = emd.getMessage("Rst051").getText();
            throw new BadParameterException("Check constraints are not supported");
        }

        query = String.format("ALTER TABLE %s ADD CONSTRAINT %s CHECK (%s) ", quote_ident(tableName),
                quote_ident(uniqueConstraintName), checkExpression);
    }

    // Check that a constraint with this name doesn't already exist.
    if (this.checkConstraintExists(tableName, uniqueConstraintName, databaseName, server)) {
        log.error("Tried to create duplicate constraint name %s on table %s", uniqueConstraintName, tableName);
        // message = String.format(emd.getMessage("Rst053").getText(),
        // uniqueConstraintName,
        // tableName);
        throw new NamingConflictException("Can't duplication constraint name");
    }

    // Create the constraint
    this.runJDBCQuery(query, null, server, databaseName);
}

---

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. ja  v  a 2 s . c om*/
        // 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;

}

---