Example usage for java.sql ResultSet updateRow

List of usage examples for java.sql ResultSet updateRow

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. ResultSet
  6. updateRow

Introduction

In this page you can find the example usage for java.sql ResultSet updateRow.

Prototype

void updateRow() throws SQLException;

Source Link

Document

Updates the underlying database with the new contents of the current row of this ResultSet object.

Usage

From source file:DemoUpdatableResultSet.java

public static void main(String[] args) {
    ResultSet rs = null;
    Connection conn = null;/* w w  w.j av  a  2  s.co  m*/
    PreparedStatement pstmt = null;
    try {
        conn = getConnection();
        String query = "select id, name, age from employees where age > ?";
        pstmt = conn.prepareStatement(query, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        pstmt.setInt(1, 20); // set input values
        rs = pstmt.executeQuery(); // create an updatable ResultSet
                                   // update a column value in the current row.
        rs.absolute(2); // moves the cursor to the 2nd row of rs
        rs.updateString("name", "newName"); // updates the 'name' column of row 2 to newName
        rs.updateRow(); // updates the row in the data source
                        // insert column values into the insert row.
        rs.moveToInsertRow(); // moves cursor to the insert row
        rs.updateInt(1, 1234); // 1st column id=1234
        rs.updateString(2, "newName"); // updates the 2nd column
        rs.updateInt(3, 99); // updates the 3rd column to 99
        rs.insertRow();
        rs.moveToCurrentRow();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            rs.close();
            pstmt.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

From source file:Transaction.java

public void doWork() {
    try {/*from w  w  w  .j  ava 2 s .  c o m*/
        java.util.Date now = new java.util.Date();
        connection.setAutoCommit(false);
        Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_UPDATABLE);
        ResultSet rs = statement.executeQuery("SELECT * FROM acc_add WHERE acc_id = 1034055 and ts = 0");

        // set old row ts = current time
        rs.next();
        rs.updateTimestamp("ts", new Timestamp(now.getTime()));
        rs.updateRow();

        rs.moveToInsertRow();
        rs.updateInt("add_id", rs.getInt("add_id"));
        rs.updateInt("acc_id", rs.getInt("acc_id"));
        rs.updateString("name", rs.getString("name"));
        rs.updateString("address1", "555 East South Street");
        rs.updateString("address2", "");
        rs.updateString("address3", "");
        rs.updateString("city", rs.getString("city"));
        rs.updateString("state", rs.getString("state"));
        rs.updateString("zip", rs.getString("zip"));
        rs.updateTimestamp("ts", new Timestamp(0));
        rs.updateTimestamp("act_ts", new Timestamp(now.getTime()));
        rs.insertRow();
        connection.commit();

        rs.close();
        statement.close();
        connection.close();

    } catch (Exception e) {
        try {
            connection.rollback();
        } catch (SQLException error) {
        }
        e.printStackTrace();
    }
}

From source file:org.esupportail.dining.web.controllers.EditAdminController.java

@RequestMapping(params = { "action=setDefaultArea" })
public String setDefaultArea(@RequestParam(value = "chkArea[]", required = false) String[] listAreas)
        throws Exception {
    String areanames = "";
    if (listAreas != null) {
        for (int i = 0; i < listAreas.length; i++) {
            areanames += listAreas[i] + (i < listAreas.length - 1 ? "," : "");
        }/* www  .j a  v a2  s. com*/
    }

    ResultSet results = this.dc.executeQuery("SELECT * FROM PATHFLUX");
    results.next();
    results.updateString("AREANAME", areanames);
    results.updateRow();

    return "redirect:/admin/?areaSubmit=true";
}

From source file:com.porvak.bracket.social.database.upgrade.v3.UpdateEncryptionMethod.java

License:asdf

@Override
protected void doExecuteStatement(Statement statement) throws SQLException {
    ResultSet rs = statement.executeQuery("select apiKey, secret from App");
    while (rs.next()) {
        rs.updateString("apiKey", encrypt(decrypt(rs.getString("apiKey"))));
        rs.updateString("secret", encrypt(decrypt(rs.getString("secret"))));
        rs.updateRow();
    }/*from  w w  w  . ja v  a2s. com*/
    rs = statement.executeQuery("select accessToken, secret from AppConnection");
    while (rs.next()) {
        rs.updateString("accessToken", encrypt(decrypt(rs.getString("accessToken"))));
        rs.updateString("secret", encrypt(decrypt(rs.getString("secret"))));
        rs.updateRow();
    }
    rs = statement.executeQuery("select member, provider, accessToken, secret from AccountConnection");
    while (rs.next()) {
        rs.updateString("accessToken", encrypt(decrypt(rs.getString("accessToken"))));
        String secret = rs.getString("secret");
        if (secret != null) {
            rs.updateString("secret", encrypt(decrypt(secret)));
        }
        rs.updateRow();
    }
}

From source file:org.esupportail.dining.web.controllers.EditAdminController.java

@RequestMapping(params = { "action=urlFeed" })
public String setURLFeed(@RequestParam(value = "feedId", required = true) int feedId) throws Exception {

    boolean urlError;
    try {/*from   w  w w .ja v a  2 s  .  c o m*/
        this.dc.executeUpdate("UPDATE PATHFLUX SET is_default=false WHERE is_default=true");

        ResultSet result = this.dc.executeQuery("SELECT * FROM PATHFLUX WHERE ID=" + feedId);
        result.next();
        result.updateBoolean("is_default", true);
        result.updateRow();
        URL urlFeed = new URL(result.getString("URLFLUX"));
        urlError = false;

        // We just avoid problem this way
        // Favorite are feed related
        // If admin update default feed then fav are not relevant anymore.
        this.dc.execute("DELETE FROM USERAREA");
        this.dc.execute("DELETE FROM FAVORITERESTAURANT");

        this.feed.setPath(urlFeed);

    } catch (Exception e) {
        e.printStackTrace();
        urlError = true;
    }
    return "redirect:/admin?urlError=" + urlError;

}

From source file:org.esupportail.dining.web.controllers.EditController.java

@RequestMapping(params = { "action=setUserArea" })
public String setUserArea(@RequestParam(value = "chkArea[]", required = false) String[] listAreas)
        throws Exception {

    User user = this.authenticator.getUser();

    String areanames = "";

    if (listAreas != null) {

        for (int i = 0; i < listAreas.length; i++) {
            areanames += listAreas[i] + (i < listAreas.length - 1 ? "," : "");
        }/*from  ww  w  .jav  a2 s. c om*/

        try {
            ResultSet results = this.dc
                    .executeQuery("SELECT * FROM USERAREA WHERE USERNAME='" + user.getLogin() + "';");
            results.next();
            results.updateString("AREANAME", areanames);
            results.updateRow();
            results.close();
        } catch (SQLException e) {
            this.dc.executeUpdate("INSERT INTO USERAREA (USERNAME, AREANAME) VALUES ('"
                    + StringEscapeUtils.escapeSql(user.getLogin()) + "', '"
                    + StringEscapeUtils.escapeSql(areanames) + "');");
        }

        return "redirect:/settings?zoneSubmit=true";
    }
    return "redirect:/settings";
}

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

public void modifyPrices(float percentage) throws SQLException {
    Statement stmt = null;/*from  w  w w  . j av  a 2  s.  co m*/
    try {
        stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        ResultSet uprs = stmt.executeQuery("SELECT * FROM COFFEES");

        while (uprs.next()) {
            float f = uprs.getFloat("PRICE");
            uprs.updateFloat("PRICE", f * percentage);
            uprs.updateRow();
        }

    } catch (SQLException e) {
        JDBCTutorialUtilities.printSQLException(e);
    } finally {
        if (stmt != null) {
            stmt.close();
        }
    }
}

From source file:net.solarnetwork.node.dao.jdbc.JdbcSettingDao.java

private void storeSettingInternal(final String key, final String ttype, final String value, final int flags) {
    final String type = (ttype == null ? "" : ttype);
    final Timestamp now = new Timestamp(System.currentTimeMillis());
    // to avoid bumping modified date column when values haven't changed, we are careful here
    // to compare before actually updating
    getJdbcTemplate().query(new PreparedStatementCreator() {

        @Override//w w  w . j a va2 s .c om
        public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
            PreparedStatement queryStmt = con.prepareStatement(sqlGet, ResultSet.TYPE_SCROLL_SENSITIVE,
                    ResultSet.CONCUR_UPDATABLE, ResultSet.CLOSE_CURSORS_AT_COMMIT);
            queryStmt.setString(1, key);
            queryStmt.setString(2, type);
            return queryStmt;
        }
    }, new ResultSetExtractor<Object>() {

        @Override
        public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
            if (rs.next()) {
                String oldValue = rs.getString(1);
                if (!value.equals(oldValue)) {
                    rs.updateString(1, value);
                    rs.updateTimestamp(2, now);
                    rs.updateRow();
                }
            } else {
                rs.moveToInsertRow();
                rs.updateString(1, value);
                rs.updateTimestamp(2, now);
                rs.updateString(3, key);
                rs.updateString(4, type);
                rs.updateInt(5, flags);
                rs.insertRow();
            }
            return null;
        }
    });
}

From source file:com.tascape.reactor.report.MySqlBaseBean.java

public void importJson(JSONObject json) throws NamingException, SQLException {
    JSONObject sr = json.getJSONObject("suite_result");
    String srid = sr.getString(SuiteResult.SUITE_RESULT_ID);
    LOG.debug("srid {}", srid);

    try (Connection conn = this.getConnection()) {
        String sql = "SELECT * FROM " + SuiteResult.TABLE_NAME + " WHERE " + SuiteResult.SUITE_RESULT_ID
                + " = ?;";
        PreparedStatement stmt = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE,
                ResultSet.CONCUR_UPDATABLE);
        stmt.setString(1, srid);/*from   ww w.  ja  va 2  s .  c  o m*/
        ResultSet rs = stmt.executeQuery();
        ResultSetMetaData rsmd = rs.getMetaData();
        if (rs.first()) {
            LOG.debug("already imported {}", srid);
            return;
        }
        rs.moveToInsertRow();
        for (int col = 1; col <= rsmd.getColumnCount(); col++) {
            String cn = rsmd.getColumnLabel(col);
            rs.updateObject(cn, sr.opt(cn));
        }
        rs.insertRow();
        rs.last();
        rs.updateRow();
        LOG.debug("sr imported");
    }

    try (Connection conn = this.getConnection()) {
        String sql = "SELECT * FROM " + SuiteProperty.TABLE_NAME + " WHERE " + SuiteProperty.SUITE_RESULT_ID
                + " = ?;";
        PreparedStatement stmt = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE,
                ResultSet.CONCUR_UPDATABLE);
        stmt.setString(1, srid);
        ResultSet rs = stmt.executeQuery();
        ResultSetMetaData rsmd = rs.getMetaData();

        JSONArray sps = sr.getJSONArray("suite_properties");
        int len = sps.length();
        for (int i = 0; i < len; i++) {
            rs.moveToInsertRow();
            JSONObject tr = sps.getJSONObject(i);
            for (int col = 1; col <= rsmd.getColumnCount(); col++) {
                String cn = rsmd.getColumnLabel(col);
                if (SuiteProperty.SUITE_PROPERTY_ID.equals(cn)) {
                    continue;
                }
                rs.updateObject(cn, tr.get(cn));
            }
            rs.insertRow();
            rs.last();
            rs.updateRow();
        }
        LOG.debug("sps imported");
    }

    JSONArray trs = sr.getJSONArray("case_results");
    int len = trs.length();

    try (Connection conn = this.getConnection()) {
        String sql = String.format("SELECT * FROM %s WHERE %s=? AND %s=? AND %s=? AND %s=? AND %s=?;",
                TaskCase.TABLE_NAME, TaskCase.SUITE_CLASS, TaskCase.CASE_CLASS, TaskCase.CASE_METHOD,
                TaskCase.CASE_DATA_INFO, TaskCase.CASE_DATA);
        PreparedStatement stmt = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE,
                ResultSet.CONCUR_UPDATABLE);
        stmt.setMaxRows(1);
        for (int i = 0; i < len; i++) {
            JSONObject tr = trs.getJSONObject(i);
            stmt.setString(1, tr.getString(TaskCase.SUITE_CLASS));
            stmt.setString(2, tr.getString(TaskCase.CASE_CLASS));
            stmt.setString(3, tr.getString(TaskCase.CASE_METHOD));
            stmt.setString(4, tr.getString(TaskCase.CASE_DATA_INFO));
            stmt.setString(5, tr.getString(TaskCase.CASE_DATA));
            ResultSet rs = stmt.executeQuery();
            if (!rs.first()) {
                rs.moveToInsertRow();
                rs.updateString(TaskCase.SUITE_CLASS, tr.getString(TaskCase.SUITE_CLASS));
                rs.updateString(TaskCase.CASE_CLASS, tr.getString(TaskCase.CASE_CLASS));
                rs.updateString(TaskCase.CASE_METHOD, tr.getString(TaskCase.CASE_METHOD));
                rs.updateString(TaskCase.CASE_DATA_INFO, tr.getString(TaskCase.CASE_DATA_INFO));
                rs.updateString(TaskCase.CASE_DATA, tr.getString(TaskCase.CASE_DATA));
                rs.insertRow();
                rs.last();
                rs.updateRow();
                rs = stmt.executeQuery();
                rs.first();
            }
            tr.put(TaskCase.TASK_CASE_ID, rs.getLong(TaskCase.TASK_CASE_ID));
        }
        LOG.debug("tcid updated");
    }

    try (Connection conn = this.getConnection()) {
        String sql = "SELECT * FROM " + CaseResult.TABLE_NAME + " WHERE " + CaseResult.SUITE_RESULT + " = ?;";
        PreparedStatement stmt = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE,
                ResultSet.CONCUR_UPDATABLE);
        stmt.setString(1, srid);
        ResultSet rs = stmt.executeQuery();
        ResultSetMetaData rsmd = rs.getMetaData();
        for (int i = 0; i < len; i++) {
            rs.moveToInsertRow();
            JSONObject tr = trs.getJSONObject(i);
            for (int col = 1; col <= rsmd.getColumnCount(); col++) {
                String cn = rsmd.getColumnLabel(col);
                rs.updateObject(cn, tr.opt(cn));
            }
            rs.insertRow();
            rs.last();
            rs.updateRow();
        }
        LOG.debug("crs imported");
    }

    try (Connection conn = this.getConnection()) {
        String sql = "SELECT * FROM " + CaseResultMetric.TABLE_NAME + ";";
        PreparedStatement stmt = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE,
                ResultSet.CONCUR_UPDATABLE);
        stmt.setMaxRows(1);
        ResultSet rs = stmt.executeQuery();
        ResultSetMetaData rsmd = rs.getMetaData();
        for (int i = 0; i < len; i++) {
            JSONArray jarr = trs.getJSONObject(i).optJSONArray("CASE_result_metrics");
            if (jarr == null) {
                continue;
            }
            int l = jarr.length();
            for (int j = 0; j < l; j++) {
                JSONObject trm = jarr.getJSONObject(j);
                rs.moveToInsertRow();
                for (int col = 1; col <= rsmd.getColumnCount(); col++) {
                    String cn = rsmd.getColumnLabel(col);
                    if (cn.equals(CaseResultMetric.CASE_RESULT_METRIC_ID)) {
                        continue;
                    }
                    rs.updateObject(cn, trm.get(cn));
                }
                rs.insertRow();
                rs.last();
                rs.updateRow();
            }
        }
        LOG.debug("crms imported");
    }
}

From source file:gov.nih.nci.migration.MigrationDriver.java

private void encryptDecryptApplicationInformation() throws EncryptionException, SQLException {

    Connection connection = getConnection();
    Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

    ResultSet resultSet = null;
    if ("oracle".equals(DATABASE_TYPE)) {
        resultSet = stmt.executeQuery("SELECT CSM_APPLICATION.* FROM CSM_APPLICATION FOR UPDATE");
    } else {//from   w w w .  j  a  v  a2  s  . c o m
        resultSet = stmt.executeQuery("SELECT * FROM CSM_APPLICATION");
    }

    String databasePassword = null;
    String encryptedDatabasePassword = null;

    while (resultSet.next()) {
        databasePassword = resultSet.getString("DATABASE_PASSWORD");

        if (!StringUtilities.isBlank(databasePassword)) {
            String orgPasswordStr = desEncryption.decrypt(databasePassword);
            encryptedDatabasePassword = aesEncryption.encrypt(orgPasswordStr);
            if (!StringUtilities.isBlank(encryptedDatabasePassword)) {
                resultSet.updateString("DATABASE_PASSWORD", encryptedDatabasePassword);
            }
        }
        System.out.println("Updating Application:" + resultSet.getString("APPLICATION_NAME"));
        resultSet.updateRow();
    }

}