IF you come here directly- you are suggested to read the article http://codingbasics.blogspot.com/2011/02/java-sql-basics-1-processing-sql.html
Updating Rows in ResultSet Objects
You cannot update a default ResultSet object, and you can only move its cursor forward.
However, you can create ResultSet objects that can be scrolled (the cursor can move backwards or move to an absolute position) and updated.
The following method, multiplies the PRICE column of each row by the argument percentage:
public void modifyPrices(float percentage) throws SQLException {
Statement stmt = null;
try {
stmt = con.createStatement();
stmt = con.createStatement(
ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet uprs = stmt.executeQuery(
"SELECT * FROM " + dbName + ".COFFEES");
while (uprs.next()) {
float f = uprs.getFloat("PRICE");
uprs.updateFloat("PRICE", f * percentage);
uprs.updateRow();
}
} catch (SQLException e ) {
JDBCTutorialUtilities.printSQLException(e);
} finally {
stmt.close();
}
}
The field ResultSet.TYPE_SCROLL_SENSITIVE creates a ResultSet object whose cursor can move both forward and backward relative to the current position
and to an absolute position. The field ResultSet.CONCUR_UPDATABLE creates a ResultSet object that can be updated.
The method ResultSet.updateFloat updates the specified column (in this example, PRICE with the specified float value in the row where the cursor is positioned.
ResultSet contains various updater methods that enable you to update column values of various data types.
However, none of these updater methods modifies the database; you must call the method ResultSet.updateRow to update the database.
Learn about Batch Updates using Statement Objects
Batch Updates using Statement Objects
Statement, PreparedStatement and CallableStatement objects have a list of commands that is associated with them.
This list may contain statements for updating, inserting, or deleting a row;
and it may also contain DDL statements such as CREATE TABLE and DROP TABLE.
It cannot, however, contain a statement that would produce a ResultSet object, such as a SELECT statement.
In other words, the list can contain only statements that produce an update count.
The list, which is associated with a Statement object at its creation, is initially empty.
You can add SQL commands to this list with the method addBatch and empty it with the method clearBatch.
When you have finished adding statements to the list, call the method executeBatch to send them all to the database to be executed as a unit, or batch.
For example, the following method adds four rows to the COFFEES table with a batch update:
public void batchUpdate() throws SQLException {
Statement stmt = null;
try {
this.con.setAutoCommit(false);
stmt = this.con.createStatement();
stmt.addBatch("INSERT INTO COFFEES " +
"VALUES('Bru', 49, 9.99, 0, 0)");
stmt.addBatch("INSERT INTO COFFEES " +
"VALUES('barista', 49, 9.99, 0, 0)");
stmt.addBatch("INSERT INTO COFFEES " +
"VALUES('cappucino', 49, 10.99, 0, 0)");
stmt.addBatch("INSERT INTO COFFEES " +
"VALUES('black_coffee', 49, 10.99, 0, 0)");
int [] updateCounts = stmt.executeBatch();
this.con.commit();
} catch(BatchUpdateException b) {
JDBCTutorialUtilities.printBatchUpdateException(b);
} catch(SQLException ex) {
JDBCTutorialUtilities.printSQLException(ex);
} finally {
this.con.setAutoCommit(true);
stmt.close();
}
}
The following line disables auto-commit mode for the Connection object con so that the transaction will not be automatically committed or rolled back when the method executeBatch is called.
this.con.setAutoCommit(false);
To allow for correct error handling, you should always disable auto-commit mode before beginning a batch update.
The method Statement.addBatch adds a command to the list of commands associated with the Statement object stmt.
In this example, these commands are all INSERT INTO statements, each one adding a row consisting of five column values.
The values for the columns COF_NAME and PRICE are the name of the coffee and its price, respectively.
The second value in each row is 49 because that is the identification number for the supplier, Superior Coffee.
The last two values, the entries for the columns SALES and TOTAL, all start out being zero because there have been no sales yet.
is the number of pounds of this row's coffee sold in the current week; TOTAL is the total of all the cumulative sales of this coffee.)
The following line sends the four SQL commands that were added to its list of commands to the database to be executed as a batch:
int [] updateCounts = stmt.executeBatch();
Note that stmt uses the method executeBatch to send the batch of insertions, not the method executeUpdate,
which sends only one command and returns a single update count.
The DBMS executes the commands in the order in which they were added to the list of commands,
so it will first add the row of values for Bru, then add the row for barista, then Bru decaf, and finally barista decaf.
If all four commands execute successfully, the DBMS will return an update count for each command in the order in which it was executed.
The update counts that indicate how many rows were affected by each command are stored in the array updateCounts.
If all four of the commands in the batch are executed successfully, updateCounts will contain four values,
all of which are 1 because an insertion affects one row.
The list of commands associated with stmt will now be empty because the four commands added previously were sent to the database
when stmt called the method executeBatch. You can at any time explicitly empty this list of commands with the method clearBatch.
The Connection.commit method makes the batch of updates to the COFFEES table permanent.
This method needs to be called explicitly because the auto-commit mode for this connection was disabled previously.
The following line enables auto-commit mode for the current Connection object.
this.con.setAutoCommit(true);
Now each statement in the example will automatically be committed after it is executed, and it no longer needs to invoke the method commit.
Performing Parameterized Batch Update
It is also possible to have a parameterized batch update, as shown in the following code fragment, where con is a Connection object:
con.setAutoCommit(false);
PreparedStatement pstmt = con.prepareStatement(
"INSERT INTO COFFEES VALUES(
?, ?, ?, ?, ?)");
pstmt.setString(1, "Bru");
pstmt.setInt(2, 49);
pstmt.setFloat(3, 9.99);
pstmt.setInt(4, 0);
pstmt.setInt(5, 0);
pstmt.addBatch();
pstmt.setString(1, "barista");
pstmt.setInt(2, 49);
pstmt.setFloat(3, 9.99);
pstmt.setInt(4, 0);
pstmt.setInt(5, 0);
pstmt.addBatch();
// ... and so on for each new type of coffee
int [] updateCounts = pstmt.executeBatch();
con.commit();
con.setAutoCommit(true);
Inserting Rows in ResultSet Objects
The following method, inserts a row into the COFFEES through a ResultSet object:
public void insertRow(String coffeeName, int supplierID, float price, int sales, int total)
throws SQLException {
Statement stmt = null;
try {
stmt = con.createStatement();
stmt = con.createStatement(
ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet uprs = stmt.executeQuery(
"SELECT * FROM " + dbName + ".COFFEES");
uprs.moveToInsertRow();
uprs.updateString("COF_NAME", coffeeName);
uprs.updateInt("SUP_ID", supplierID);
uprs.updateFloat("PRICE", price);
uprs.updateInt("SALES", sales);
uprs.updateInt("TOTAL", total);
uprs.insertRow();
uprs.beforeFirst();
} catch (SQLException e ) {
JDBCTutorialUtilities.printSQLException(e);
} finally {
stmt.close();
}
}
The method ResultSet.moveToInsertRow moves the cursor to the insert row.
The insert row is a special row associated with an updatable result set.
It is essentially a buffer where a new row can be constructed by calling the updater methods prior to inserting the row into the result set.
For example, this method calls the method ResultSet.updateString to update the insert row's COF_NAME column to Kona.
The method ResultSet.insertRow inserts the contents of the insert row into the ResultSet object and into the database.
No comments:
Post a Comment