--
A default ResultSet object is not updatable and has a cursor that moves forward only. Thus, you can iterate through it only once and only from the first row to the last row. It is possible to produce ResultSet objects that are scrollable and/or updatable. An updatable result set allows modification to data in a table through the result set. The following code makes a result set that is scrollable and insensitive to updates by others:
The following is an example for this topic:
A default ResultSet object is not updatable and has a cursor that moves forward only. Thus, you can iterate through it only once and only from the first row to the last row. It is possible to produce ResultSet objects that are scrollable and/or updatable. An updatable result set allows modification to data in a table through the result set. The following code makes a result set that is scrollable and insensitive to updates by others:
try {
// Create a statement that will return updatable result sets
Statement stmt = connection.createStatement(
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
//Primary key EmployeeID must be specified
//so that the result set is updatable
ResultSet resultSet = stmt.executeQuery(
"SELECT EmployeeID, Name, Office FROM employees");
} catch (SQLException e) {
}The updatable result set may be used few ways:- to update a column value in the current row. In a scrollable
ResultSetobject, the cursor can be moved backwards and forwards, to an absolute position, or to a position relative to the current row. The following code fragment updates the Office column in the fifth row of the ResultSet object rs and then uses the method updateRow to update the data source table from which rs was derived.
rs.absolute(5); // moves the cursor to the fifth row of rs rs.updateString("Office", "HQ222"); // updates the // Office column of row 5 to be HQ222 rs.updateRow(); // updates the row in the data source - to insert column values into the insert row. An updatable ResultSet object has a special row associated with it that serves as a staging area for building a row to be inserted. The following code fragment moves the cursor to the insert row, builds a three-column row, and inserts it into rs and into the data source table using the method insertRow.
rs.moveToInsertRow(); // moves cursor to the insert row rs.updateInt("EmployeeID", 1001); rs.updateString("Name", "Divad Walker"); rs.updateString("Office", "HQ101"); rs.insertRow(); rs.moveToCurrentRow(); - to delete a row. The following code fragment moves to the first row of the ResultSet object rs and then uses the method deleteRow to delete the data source table from which rs was derived.
rs.first(); // moves cursor to the deleting row rs.deleteRow();
ResultSet object is automatically closed when the Statement object that generated it is closed, re-executed, or used to retrieve the next result from a sequence of multiple results.cancelRowUpdates cancels the updates made to the current row in this ResultSet object. This method may be called after calling an updater method(s) and before calling the method updateRow to roll back the updates made to a row. If no updates have been made or updateRow has already been called, this method has no effect.The following is an example for this topic:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCUpdatableRS {
private static final String DBURL =
"jdbc:mysql://localhost:3306/mydb?user=usr&password=sql" +
"&useUnicode=true&characterEncoding=UTF-8";
private static final String DBDRIVER = "org.gjt.mm.mysql.Driver";
static {
try {
Class.forName(DBDRIVER).newInstance();
} catch (Exception e){
e.printStackTrace();
}
}
private static Connection getConnection()
{
Connection connection = null;
try {
connection = DriverManager.getConnection(DBURL);
}
catch (Exception e) {
e.printStackTrace();
}
return connection;
}
public static void createEmployees()
{
Connection con = getConnection();
Statement stmt =null;
String createString;
createString = "CREATE TABLE `mydb`.`employees` ("+
"`EmployeeID` int(10) unsigned NOT NULL default '0',"+
"`Name` varchar(45) collate utf8_unicode_ci NOT NULL default '',"+
"`Office` varchar(10) collate utf8_unicode_ci NOT NULL default '',"+
"`CreateTime` timestamp NOT NULL default CURRENT_TIMESTAMP,"+
"PRIMARY KEY (`EmployeeID`)"+
") ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;";
try {
stmt = con.createStatement();
stmt.executeUpdate(createString);
} catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
}
finally {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
System.err.println("SQLException: " + e.getMessage());
}
}
if (con != null) {
try {
con.close();
} catch (SQLException e) {
System.err.println("SQLException: " + e.getMessage());
}
}
}
}
private static void dropEmployees()
{
Connection con = getConnection();
Statement stmt =null;
String createString;
createString = "DROP TABLE IF EXISTS `mydb`.`employees`;";
try {
stmt = con.createStatement();
stmt.executeUpdate(createString);
} catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
}
finally {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
System.err.println("SQLException: " + e.getMessage());
}
}
if (con != null) {
try {
con.close();
} catch (SQLException e) {
System.err.println("SQLException: " + e.getMessage());
}
}
}
}
public static void showEmployee() {
Connection con = getConnection();
Statement stmt =null;
try {
stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("Select * from employees "
+ where EmployeeID=1001");
if (rs.next()) {
System.out.println("EmployeeID : " +
rs.getInt("EmployeeID"));
System.out.println("Name : " + rs.getString("Name"));
System.out.println("Office : " + rs.getString("Office"));
}
else {
System.out.println("No Specified Record.");
}
rs.close();
} catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
}
finally {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
System.err.println("SQLException: " + e.getMessage());
}
}
if (con != null) {
try {
con.close();
} catch (SQLException e) {
System.err.println("SQLException: " + e.getMessage());
}
}
}
}
public static void insertEmployee() {
Connection con = getConnection();
Statement stmt =null;
String sqlString = "SELECT EmployeeID, Name, " +
" Office FROM employees;";
try {
stmt = con.createStatement(
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery(sqlString);
//Check the result set is an updatable result set
int concurrency = rs.getConcurrency();
if (concurrency == ResultSet.CONCUR_UPDATABLE) {
rs.moveToInsertRow();
rs.updateInt(1, 1001);
rs.updateString(2, "Divad Walker");
rs.updateString(3, "HQ101");
rs.insertRow();
rs.moveToCurrentRow();
} else {
System.out.println("ResultSet is not an updatable result set.");
}
rs.close();
} catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
}
finally {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
System.err.println("SQLException: " + e.getMessage());
}
}
if (con != null) {
try {
con.close();
} catch (SQLException e) {
System.err.println("SQLException: " + e.getMessage());
}
}
}
}
public static void updateEmployee(){
Connection con = getConnection();
Statement stmt =null;"
String sqlString = "SELECT EmployeeID, Name, Office " +
" FROM employees WHERE EmployeeID=1001";
try {
stmt = con.createStatement(
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery(sqlString);
//Check the result set is an updatable result set
int concurrency = rs.getConcurrency();
if (concurrency == ResultSet.CONCUR_UPDATABLE) {
rs.first();
rs.updateString("Office", "HQ222");
rs.updateRow();
} else {
System.out.println("ResultSet is not an updatable result set.");
}
rs.close();
} catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
}
finally {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
System.err.println("SQLException: " + e.getMessage());
}
}
if (con != null) {
try {
con.close();
} catch (SQLException e) {
System.err.println("SQLException: " + e.getMessage());
}
}
}
}
public static void deleteEmployee(){
Connection con = getConnection();
Statement stmt =null;
String sqlString = "SELECT EmployeeID, Name, Office " +
" FROM employees WHERE EmployeeID=1001";
try {
stmt = con.createStatement(
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery(sqlString);
//Check the result set is an updatable result set
int concurrency = rs.getConcurrency();
if (concurrency == ResultSet.CONCUR_UPDATABLE) {
rs.first();
rs.deleteRow();
} else {
System.out.println("ResultSet is not an updatable result set.");
}
rs.close();
} catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
}
finally {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
System.err.println("SQLException: " + e.getMessage());
}
}
if (con != null) {
try {
con.close();
} catch (SQLException e) {
System.err.println("SQLException: " + e.getMessage());
}
}
}
}
public static void main(String[] args) {
dropEmployees();
createEmployees();
insertEmployee();
System.out.println("\nAfter inserting a Record ...");
showEmployee();
updateEmployee();
System.out.println("\nAfter updating a Record ...");
showEmployee();
deleteEmployee();
System.out.println("\nAfter deleting a Record ...");
showEmployee();
}
}
Comments
Post a Comment