Web Design Development Lec 15 1 Web Design

Web Design & Development Lec - 15 1

Web Design & Development More on JDBC 2

Web Design & Development Example Code Executing SQL DML statements 3

Useful Statement Methods (N-I) • execute. Query – Executes the SQL query and returns the data in a table (Result. Set) – The resulting table may be empty but never null Result. Set rs = stmt. execute. Query(“select * from table"); 4 JDBC Umair Javed© 2005

Example Code 15. 1 Executing SQL DML Statements /* This progarm will take two command line argument that are used to update records in the database */ import java. sql. *; //step 1 public class Jdbc. Dml. Ex { public static void main (String args [ ]){ try { //steps 2 to 5 Class. for. Name(“driver name”); Connection con=null; con = Driver. Manager. get. Connection(url, usr, pwd); Statement st = con. create. Statement(); 5 JDBC Umair Javed© 2005

Example Code 15. 1 Executing SQL DML Statements (cont. ) //Step 6: Execute the Query / DML String add. Var = args[0]; String name. Var = args[1]; String sql = “UPDATE Person ” + “ SET address = ‘ “ + add. Var + ” ’ ” + “ WHERE name = ‘ “ + name. Var + ” ’ ” ; int num = st. execute. Update(sql); //Step 7: Process the results of the query System. out. println(num + “ records updated”); 6 JDBC Umair Javed© 2005

Example Code 15. 1 Executing SQL DML Statements (cont. ) //Step 8: close the connection con. close(); }catch (Exception sql. Ex) { System. out. println(sql. Ex); } } //end main }//end class 7 JDBC Umair Javed© 2005

Compile & Execute Before execution After execution 8 JDBC Umair Javed© 2005

Useful Statement Methods (Continued) • get. Max. Rows( ) / set. Max. Rows(int) – Determines the number of rows a Result. Set may contain – Unless explicitly set, the number of rows are unlimited (return value of 0) • get. Query. Timeout( ) / set. Query. Timeout(int) – Specifies the amount of a time (seconds) a driver will wait for a STATEMENT to complete before throwing a SQLException 9 JDBC Umair Javed© 2005

Different Types of Statements • Overview – Through the Statement object, SQL statements are sent to the database. – Three types of statement objects are available: 1. Statement – for executing a simple SQL statements 2. Prepared. Statement – for executing a precompiled SQL statement passing in parameters 3. Callable. Statement – for executing a database stored procedure 10 JDBC Umair Javed© 2005

Web Design & Development Prepared Statements 11

Prepared Statements (Precompiled Queries) • Idea – If you are going to execute similar SQL statements multiple times, using “prepared” (parameterized) statements can be more efficient – Create a statement in standard form that is sent to the database for compilation before actually being used – Each time you use it, you simply replace some of the marked parameters (? ) using some set methods 12 JDBC Umair Javed© 2005

Prepared Statement, Example Prepared. Statement p. Stmt = con. prepare. Statement("UPDATE table. Name " + “SET column. Name = ? " + “WHERE column. Name = ? "); • First marked parameter(? ) has index 1. p. Stmt. set. String(1, string. Value); p. Stmt. set. Int (2, int. Value); p. Stmt. execute. Update(); 13 JDBC Umair Javed© 2005

Web Design & Development Example Code Using Prepared Statements 14

Example Code: Modify Jdbc. Dml. Ex. java Executing Prepared Statements 15. 1 /* Modification to the last example code 15. 1, to show the usage of prepared statements */ import java. sql. *; // step 1 public class Jdbc. Dml. Ex { public static void main (String args [ ]){ try { //steps 2 to 4 Class. for. Name(“driver name”); Connection con=null; con = Driver. Manager. get. Connection(url, usr, pwd); 15 JDBC Umair Javed© 2005

Example Code: Modify Jdbc. Dml. Ex. java Executing Prepared Statements 15. 1 //Step 5: Create the statement Prepared. Statement p. Stmt = null; String sql = “UPDATE Person SET address = ? WHERE name = ? ” ; p. Stmt = con. prepare. Statement(sql); //Step 6: Execute the Query String add. Var = args[0]; String name. Var = args[1]; p. Stmt. set. String(1 , add. Var); p. Stmt. set. String(2, name. Var); // sql = “UPDATE Person SET address = “defence” WHERE name = “ali” ” int num = p. Stmt. execute. Update(); 16 JDBC Umair Javed© 2005

Example Code: Modify Jdbc. Dml. Ex. java Executing Prepared Statements //Step 7: Process the results of the query System. out. println(num + “ records updated”); //Step 8: close the connection }catch (Exception sql. Ex) { ………. . } } //end main }//end class 17 JDBC Umair Javed© 2005 15. 1

Compile & Execute Before execution After execution 18 JDBC Umair Javed© 2005

Web Design & Development Result Set 19