Web Design Development Lec 16 1 Web Design
Web Design & Development Lec - 16 1
Web Design & Development More on JDBC 2
Web Design & Development Result Set 3
Result. Set • Overview – A Result. Set contains the results of the SQL query • Represented by a table with rows and columns • Maintains a cursor pointing to its current row of data. • Initially the cursor positioned before the row (0). • First row has index 1 4 JDBC Umair Javed© 2005
Result. Set (cont. ) Row numbers Result. Set 0 5 JDBC id Name Address phone. Num 1 1 ali model town 9203256 2 2 usman gulberg 8219065 3 3 raza defence 5173946 Umair Javed© 2005
Result. Set (cont. ) • A default Result. Set object is not updateable and has a cursor that moves forward only – You can iterate through it only once and only from the first row to last row. String sql = “SELECT * FROM Person”; Prepared. Statement p. Stmt = con. prepare. Statement(sql); Result. Set rs = p. Stmt. execute. Query( ); 6 JDBC Umair Javed© 2005
Result. Set (cont. ) • Useful Methods – next( ) • Attempts to move to the next row in the Result. Set • If successful true is returned; otherwise, false • The first call to next, moves the cursor to the first row – close( ) • Releases the JDBC and database resources • The result set is automatically closed when the associated Statement object executes a new query or closed by method call 7 JDBC Umair Javed© 2005
Result. Set (cont. ) • Useful Methods – getters • Returns the value from the column specified by the column name or index – String name = rs. get. String(“name”); – String add = rs. get. String(3); – double sal = rs. get. Double(“Salary”) • Returns the value in a specified format double float 8 JDBC byte short int long Date Time String Object Umair Javed© 2005
Result. Set (cont. ) • It is possible to produce Result. Set objects that are scrollable and/or updatable (since JDK 1. 2). String sql = “SELECT * FROM Person”; Prepared. Statement p. Stmt = con. prepare. Statement( sql, Result. Set. TYPE_SCROLL_INSENSITIVE, Result. Set. CONCUR_UPDATABLE ); Result. Set rs = p. Stmt. execute. Query( ); 9 JDBC Umair Javed© 2005
Result. Set (cont. ) • Useful Methods – previous( ) • Moves the cursor to the previous row in the Result. Set object. • Returns true if cursor is on a valid row, false it is off the result set. • Throws exception if result type is TYPE_FORWARD_ONLY. 10 JDBC Umair Javed© 2005
Example Code: Result. Set. Ex previous, next & getters methods import java. sql. *; public class Result. Set. Ex { public static void main ( String args[ ]) { try { // load driver & make connection String sql = “SELECT * FROM Person”; Prepared. Statement p. Stmt = con. prepare. Statement( sql, Result. Set. TYPE_SCROLL_INSENSITIVE , Result. Set. CONCUR_UPDATABLE ); Result. Set rs = p. Stmt. execute. Query( ); 11 JDBC Umair Javed© 2005
Example Code: Result. Set. Ex previous, next & getters methods rs. next(); System. out. println(“moving cursor forward”); String name = rs. get. String(“name”); System. out. println(name); rs. next(); rs. previous(); System. out. println(“moving cursor backward”); name = rs. get. String(“name”); System. out. println(name); 12 JDBC Umair Javed© 2005
Example Code: Result. Set. Ex previous, next & getters methods con. close(); } catch (Exception ex) { System. out. println(ex); } }// end main }//end class 13 JDBC Umair Javed© 2005
Compile & Execute 14 JDBC Umair Javed© 2005
Result. Set (cont. ) • Useful Methods – absolute(int) • Move the cursor to the given row number in the Result. Set object • If the row number is positive, moves the cursor forward with respect to beginning of the result set. • If the given row number is negative, the cursor moves to the absolute row position with respect to the end of the result set. – For example, calling absolute(-1) positions the cursor on the last row; calling absolute(-2) moves the cursor to next-to-last row, and so on. • Throws exception if result type is TYPE_FORWARD_ONLY. 15 JDBC Umair Javed© 2005
Result. Set (cont. ) • Useful Methods – updaters (for primitives, Object & String) • Used to update column values in the current row or the insert row. • Do not update the underlying database • Each update method is overloaded. • For example of String update. String(String column. Name, String value) update. String(int column. Index, String value) 16 JDBC Umair Javed© 2005
Result. Set (cont. ) • Useful Methods – update. Row( ) • Updates the underlying database with new contents of the current row of this Result. Set object. 17 JDBC Umair Javed© 2005
Modify Example : Result. Set. Ex updating existing rows Import java. sql. *; public class Result. Set. Ex { …. . // main method …. . Load driver, make connection …. . Make updatable resultset //move cursor to 2 nd row of rs rs. absolute(2); //update address column of 2 nd row in rs rs. update. String(“address”, “model town’); //update the row in database rs. update. Row(); ………. // close connection etc …. //end main }// end class 18 JDBC Umair Javed© 2005
Compile & Execute Before execution After execution 19 JDBC Umair Javed© 2005
Result. Set (cont. ) • Useful Methods – move. To. Insert. Row( ) • An Updatable Result. Set object has a special row associated with it i. e. insert row. • Insert row – a buffer, where a new row may be construted by calling the updater methods • Doesn’t insert row into a result set or into a database 20 JDBC Umair Javed© 2005
Result. Set (cont. ) • Useful Methods – insert. Row( ) • Inserts the contents of the insert row into this Result. Set object and into the database. • The cursor must be on the insert row when this method is called 21 JDBC Umair Javed© 2005
Result. Set (cont. ) move. To. Insert. Row( ) Row numbers 0 id Name Address phone. Num 1 1 ali model town 9203256 2 2 usman gulberg 8219065 3 3 raza defence 5173946 imitiaz cantt Updatable Result. Set 9201211 Insert Row 22 JDBC Umair Javed© 2005
Result. Set (cont. ) insert. Row( ) Row numbers 0 id Name Address phone. Num 1 1 ali model town 9203256 2 2 usman gulberg 8219065 3 3 raza defence 5173946 4 4 imitiaz cantt Updatable Result. Set 9201211 Insert Row 23 JDBC Umair Javed© 2005
Modify Example : Result. Set. Ex Inserting new row Import java. sql. *; public class Result. Set. Ex { …. . // main method …. . Load driver, make connection …. . Make updatable resultset //move cursor to insert row rs. move. To. Insert. Row(); // updating values into insert row rs. update. String(“name”, “imitaz’); rs. update. String(“address”, “cantt’); rs. update. String(“phone. Num”, “ 9201211’); //insert row into rs & db rs. insert. Row(); ………. …. //end main }// end class 24 JDBC Umair Javed© 2005
Compile & Execute Before execution After execution 25 JDBC Umair Javed© 2005
Result. Set (cont. ) • Useful Methods – last( ) & first( ) • Moves the cursor to the last & first row of the Result. Set object respectively. • Throws exception if the result set is TYPE_FORWARD_ONLY – get. Row( ) • Returns the current row numner • The first row number is 1, second row number is 2 and s on 26 JDBC Umair Javed© 2005
Result. Set (cont. ) • Useful Methods – delete. Row( ) • Deletes the current row from this Result. Set object and from the underlying database. • Throws exception when the cursor is on the insert row 27 JDBC Umair Javed© 2005
Modify Example : Result. Set. Ex deleting existing row Import java. sql. *; public class Result. Set. Ex { …. . // main method …. . Load driver, make connection …. . Make updatable resultset //moves to last row rs. last( ); int r. No = rs. get. Row(); System. out. println(“curr row no: ”+ r. No ); //delete current row (4) from rs & db rs. delete. Row(); ………. …. //end main }// end class 28 JDBC Umair Javed© 2005
Compile & Execute Before execution After execution 29 JDBC Umair Javed© 2005
- Slides: 29