Creating Applications Using Advanced Features of JDBC Pre-assessment Questions 1. _______ layer signifies a Java application that uses the JDBC API to interact with the JDBC drivers. a. JDBC application layer b. JDBC driver layer c. JDBC driver manager layer d. JDBC API layer 2. A JDBC _____ is a software that a Java application uses to access a database. a. Driver manager b. Driver c. DSN d. CLI JDBC and Java. Beans Lesson 1 B / Slide 1 of 45
Creating Applications Using Advanced Features of JDBC Pre-assessment Questions (Contd. ) 3. Which JDBC driver works as an interface between the JDBC application and the ODBC driver ? a. Native-API-Partly-Java driver b. Native Protocol Pure-Java driver c. JDBC-ODBC Bridge driver d. JDBC-Net-Pure-Java driver 4. Which JDBC driver maps the JDBC calls to the native method calls, which are passed to the local native Call Level Interface (CLI)? a. Native-API-Partly-Java driver b. JDBC-ODBC Bridge driver c. Native Protocol Pure-Java driver d. JDBC-Net-Pure-Java driver JDBC and Java. Beans Lesson 1 B / Slide 2 of 45
Creating Applications Using Advanced Features of JDBC Pre-assessment Questions (Contd. ) 5. Which interface enables you to establish a connection between a Java application and a database ? a. Statement interface b. Result. Set interface c. Driver. Manager interface d. Connection interface JDBC and Java. Beans Lesson 1 B / Slide 3 of 45
Creating Applications Using Advanced Features of JDBC Solutions to Pre-assessment Questions 1. 2. 3. 4. 5. a. Java application layer b. Driver c. JDBC-ODBC Bridge driver a. Native-API-Partly-Java driver d. Connection interface JDBC and Java. Beans Lesson 1 B / Slide 4 of 45
Creating Applications Using Advanced Features of JDBC Objectives In this section, you will learn about: • • • Creating applications using the Prepared. Statement object Managing database transactions Performing batch updates Creating and calling stored procedures in JDBC Using metadata in JDBC and Java. Beans Lesson 1 B / Slide 5 of 45
Creating Applications Using Advanced Features of JDBC Querying and Modifying Data Using the Prepared. Statement Object • • The Prepared. Statement interface is derived from the Statement interface and is available in the java. sql package. The Prepared. Statement object: • • • Allows you to pass runtime parameters to the SQL statements to query and modify the data in a table. Is compiled and prepared only once by JDBC. The future invocation of the Prepared. Statement object does not recompile the SQL statements. Helps in reducing the load on the database server and thus improving the performance of the application. JDBC and Java. Beans Lesson 1 B / Slide 6 of 45
Creating Applications Using Advanced Features of JDBC Querying and Modifying Data Using the Prepared. Statement Object (Contd. ) • Methods of the Prepared. Statement Interface • The Prepared. Statement interface inherits the following methods to execute SQL statements from the Statement interface: • Result. Set execute. Query(): Executes a SELECT statements and returns the result in a Result. Set object. • int execute. Update(): Executes an SQL statement, INSERT, UPDATE, or DELETE and returns the count of the rows affected. • boolean execute(): Executes an SQL statement and returns a boolean value. JDBC and Java. Beans Lesson 1 B / Slide 7 of 45
Creating Applications Using Advanced Features of JDBC Querying and Modifying Data Using the Prepared. Statement Object (Contd. ) • • • The prepare. Statement() method of the Connection object is used to submit parameterized query to a database. The SQL statement can contain ‘? ’ symbol as placeholders that can be replaced by input parameters at runtime. For example, stat=con. prepare. Statement("SELECT * FROM authors WHERE au_id = ? "); The value of each ‘? ’ parameter is set by calling an appropriate set. XXX() method, where XXX is the data type of the parameter. For example, stat. set. String(1, "1001"); Result. Set result=stat. execute. Query(); JDBC and Java. Beans Lesson 1 B / Slide 8 of 45
Creating Applications Using Advanced Features of JDBC Querying and Modifying Data Using the Prepared. Statement Object (Contd. ) • Retrieving Rows • The code snippet to retrieve books written by an author from the titles table using the Prepared. Statement object is: String str = "SELECT * FROM titles WHERE au_id = ? "; Prepared. Statement ps= con. prepare. Statement(str); ps. set. String(1, "1001"); Result. Set rs=ps. execute. Query(); JDBC and Java. Beans Lesson 1 B / Slide 9 of 45
Creating Applications Using Advanced Features of JDBC Querying and Modifying Data Using the Prepared. Statement Object (Contd. ) • Inserting Rows • The code snippet to create a Prepared. Statement object that inserts a row into authors table by passing authors data at runtime is: String str = "INSERT INTO authors (au_id, au_fname, au_lname) VALUES (? , ? )"; Prepared. Statement ps = con. prepare. Statement(str); ps. set. String(1, "1001"); ps. set. String(2, "Abraham"); ps. set. String(3, "White"); int rt=ps. execute. Update(); JDBC and Java. Beans Lesson 1 B / Slide 10 of 45
Creating Applications Using Advanced Features of JDBC Querying and Modifying Data Using the Prepared. Statement Object (Contd. ) • Updating and Deleting Rows • The code snippet to modify the state to CA where city is Oakland in the authors table using the Prepared. Statement object is: String str = "UPDATE authors SET state= ? WHERE city= ? "; Prepared. Statement ps = con. prepare. Statement(str); ps. set. String(1, "CA"); ps. set. String(2, "Oakland"); int rt=ps. execute. Update(); JDBC and Java. Beans Lesson 1 B / Slide 11 of 45
Creating Applications Using Advanced Features of JDBC Querying and Modifying Data Using the Prepared. Statement Object (Contd. ) • The code snippet to delete a row from the authors table where author’s first name is Abraham using the Prepared. Statement object is: String str = "DELETE FROM authors WHERE au_fname= ? "; Prepared. Statement ps = con. prepare. Statement(str); ps. set. String(1, "Abraham"); int rt=ps. execute. Update(); JDBC and Java. Beans Lesson 1 B / Slide 12 of 45
Creating Applications Using Advanced Features of JDBC Demonstration- Creating an Application that Uses Prepared. Statement Object • Problem Statement • The management of a departmental store has decided to computerize the inventory. You have been asked to create the Product Information application that has an interactive user interface. The application should allow the user to add, update, and delete product information from the product table. JDBC and Java. Beans Lesson 1 B / Slide 13 of 45
Creating Applications Using Advanced Features of JDBC Demonstration- Creating an Application that Uses Prepared. Statement Object (Contd. ) • Problem Statement (Contd. ) • The user interface of the application should be as shown in the following figure: JDBC and Java. Beans Lesson 1 B / Slide 14 of 45
Creating Applications Using Advanced Features of JDBC Demonstration- Creating an Application that Uses Prepared. Statement Object (Contd. ) • Solution • • The GUI for the application is created using the java. swing package. The database operations are performed using the Prepared. Statement object. To solve the above problem, perform the following tasks: 1. Code the application. 2. Compile and execute the application. JDBC and Java. Beans Lesson 1 B / Slide 15 of 45
Creating Applications Using Advanced Features of JDBC Managing Database Transactions • A transaction: • Is a set of one or more SQL statements that are executed as a single unit. • Is complete only when all the SQL statements in a transaction execute successfully. • Maintains consistency of data in a database. JDBC and Java. Beans Lesson 1 B / Slide 16 of 45
Creating Applications Using Advanced Features of JDBC Managing Database Transactions (Contd. ) • • JDBC API provides support for transaction management. The database transactions can be committed in two ways in the JDBC applications: • Implicit: The Connection object uses the auto-commit mode to execute the SQL statements implicitly. • Explicit: The auto-commit mode is set to false to commit the transaction statement explicitly. The method call to set the autocommit mode to false is: con. set. Auto. Commit(false); JDBC and Java. Beans Lesson 1 B / Slide 17 of 45
Creating Applications Using Advanced Features of JDBC Managing Database Transactions (Contd. ) • Committing a Transaction • The commit() method is used to reflect the changes made by the transactions in a database. • The rollback() method is used to undo the changes made in the database after the last commit operation. • You need to explicitly invoke commit() and rollback() methods. JDBC and Java. Beans Lesson 1 B / Slide 18 of 45
Creating Applications Using Advanced Features of JDBC Performing Batch Updates • A batch: • Is a group of update statements that are sent to a database to be executed as a single unit. • Reduces network calls between the application and the database. • Is a more efficient way as compared to the processing of a single SQL statement. JDBC and Java. Beans Lesson 1 B / Slide 19 of 45
Creating Applications Using Advanced Features of JDBC Performing Batch Updates (Contd. ) • Implementing Batch Updates in JDBC • The Statement or Prepared. Statement interface provides the following methods to create and execute a batch of SQL statements: • void add. Batch(): Adds an SQL statement to a batch. • int execute. Batch(): Sends a batch of SQL statements to a database for processing and returns the total number of the rows updated. • void clear. Batch(): Removes the SQL statements from the batch. JDBC and Java. Beans Lesson 1 B / Slide 20 of 45
Creating Applications Using Advanced Features of JDBC Performing Batch Updates (Contd. ) • • • When a Statement object is created to perform batch updates, an empty array is associated with the object. Multiple SQL statements can be added to the empty array to execute them as a batch. You also need to disable the auto-commit mode using set. Auto. Commit(false) while working with batch updates in JDBC. The execute. Batch() method returns an integer array that stores the values of the update count. The update count is the total number of rows affected when an SQL statement in a batch is processed. JDBC and Java. Beans Lesson 1 B / Slide 21 of 45
Creating Applications Using Advanced Features of JDBC Performing Batch Updates (Contd. ) • The code snippet to create a batch of SQL statements is: con. set. Auto. Commit(false); Statement stmt=con. create. Statement(); stmt. add. Batch("INSERT INTO product (p_id, p_desc) VALUES (1001, 'Printer')"); stmt. add. Batch("INSERT INTO product (p_id, p_desc) VALUES (1002, 'Scanner')"); • The SQL statements in a batch are processed in the order in which the statements appear in a batch. • The method call to execute a batch of SQL statements is: int[] updcount=state. execute. Batch(); JDBC and Java. Beans Lesson 1 B / Slide 22 of 45
Creating Applications Using Advanced Features of JDBC Performing Batch Updates (Contd. ) • Exception Handling in Batch Updates • The batch update operations can throw two types of exceptions: • SQLException • Batch. Update. Exception • The Batch. Update. Exception class is derived from SQLException class. JDBC and Java. Beans Lesson 1 B / Slide 23 of 45
Creating Applications Using Advanced Features of JDBC Performing Batch Updates (Contd. ) • • • The the SQLException is thrown by the JDBC API methods, add. Batch() or execute. Batch(), when problem occurs while accessing a database. The Batch. Update. Exception exception is thrown when the SQL statements in the batch cannot be executed due to: • Presence of illegal arguments in the SQL statement. • Absence of the database table from which you need to retrieve data. The Batch. Update. Exception uses an array of the update count to identify the SQL statement that throws the exception. JDBC and Java. Beans Lesson 1 B / Slide 24 of 45
Creating Applications Using Advanced Features of JDBC Creating and Calling Stored Procedures in JDBC • • The java. sql package provides the Callable. Statement interface that contains various methods to enable you to call the stored procedures from a database. The Callable. Statement interface is derived from the Prepared. Statement interface. JDBC and Java. Beans Lesson 1 B / Slide 25 of 45
Creating Applications Using Advanced Features of JDBC Creating and Calling Stored Procedures in JDBC (Contd. ) • Creating Stored Procedure • Stored Procedures: • Can be created using the CREATE PROCEDURE SQL statement in JDBC applications. • Are of two types: • Parameterized • Non-parameterized JDBC and Java. Beans Lesson 1 B / Slide 26 of 45
Creating Applications Using Advanced Features of JDBC Creating and Calling Stored Procedures in JDBC (Contd. ) • • A parameterized stored procedure can accept one or multiple parameters. A parameter of a stored procedure can take any of these forms: • IN: Refers to the argument that you pass to a stored procedure. • OUT: Refers to the return value of a stored procedure. • INOUT: Combines the functionality of the IN and OUT parameters. The INOUT parameter enables you to pass an argument to a stored procedure. The same parameter can also be used to store a return value of a stored procedure. JDBC and Java. Beans Lesson 1 B / Slide 27 of 45
Creating Applications Using Advanced Features of JDBC Creating and Calling Stored Procedures in JDBC (Contd. ) • Calling a Stored Procedure without Parameters • The Connection interface provides the prepare. Call() method that is used to create the Callable. Statement object to call a stored procedure. • The prepare. Call() has the following three forms: • Callable. Statement prepare. Call(String str) • Callable. Statement prepare. Call(String str, int res. Set. Type, int res. Set. Concurrency, int res. Set. Holdability) • The syntax to call a stored procedure without parameters is: { call <procedure_name> }; JDBC and Java. Beans Lesson 1 B / Slide 28 of 45
Creating Applications Using Advanced Features of JDBC Creating and Calling Stored Procedures in JDBC (Contd. ) • Calling a Stored Procedure with Parameters • The SQL escape syntax is a standard way to call a stored procedure from a Relational Database Management System (RDBMS) and is independent of the RDBMS. • There are two forms of the SQL escape syntax, one that contains result parameter and one that does not. • The syntax of the SQL escape syntax is: {[? =] call <procedure_name> [<parameter 1>, <parameter 2>, . . . , <parameter. N>]} JDBC and Java. Beans Lesson 1 B / Slide 29 of 45
Creating Applications Using Advanced Features of JDBC Creating and Calling Stored Procedures in JDBC (Contd. ) • • The placeholders are used to represent the IN, OUT, and INOUT parameters of a stored procedure in the procedure call. The syntax to call a stored procedure with parameters is: { call <procedure_name>(? ) }; You need to set the value of the IN parameters using the set methods before the Callable. Statement object is executed. The syntax to set the value of the IN parameter is: <Callable. Statement_object>. set. Int(<value>); JDBC and Java. Beans Lesson 1 B / Slide 30 of 45
Creating Applications Using Advanced Features of JDBC Creating and Calling Stored Procedures in JDBC (Contd. ) • • • If the stored procedure contains OUT and INOUT parameters, these parameters should be registered with the corresponding JDBC types. The register. Out() method is used to register the parameters. The prototypes of the register. Out() method are: • register. Out(int index, int stype) • register. Out(int index, int stype, int scale) JDBC and Java. Beans Lesson 1 B / Slide 31 of 45
Creating Applications Using Advanced Features of JDBC Using Metadata in JDBC • • • Metadata is the information about data, such as structure and properties of table. The metadata of the employee table includes following information: • • • Names of the columns. Data type of each column. Constraints to enter data values in the table columns. JDBC API provides the following two metadata interfaces to retrieve the information about the database and result set: • Database. Meta. Data interface • Result. Set. Meta. Data interface JDBC and Java. Beans Lesson 1 B / Slide 32 of 45
Creating Applications Using Advanced Features of JDBC Using Metadata in JDBC (Contd. ) • Using Database. Meta. Data Interface • The Database. Meta. Data interface provides the methods that enable you to determine the properties of a database or RDBMS. • An object of Database. Meta. Data is created using the get. Meta. Data() method of the Connection interface. • The method call to create an object of the Database. Meta. Data interface is: Database. Meta. Data dm=con. get. Meta. Data(); JDBC and Java. Beans Lesson 1 B / Slide 33 of 45
Creating Applications Using Advanced Features of JDBC Using Metadata in JDBC (Contd. ) • The following table lists the commonly used methods of the Database. Meta. Data interface: Method Description Result. Set get. Columns(String catalog, String schema, String table_name, String column_name) Retrieves the information about a column of a database table that is available in the specified catalog. Connection get. Connection() Retrieves the database connection that creates the Database. Meta. Data object. String get. Driver. Name() Retrieves the name of the JDBC driver for the Database. Meta. Data object. String get. Driver. Version() Retrieves the version of the JDBC driver. JDBC and Java. Beans Lesson 1 B / Slide 34 of 45
Creating Applications Using Advanced Features of JDBC Using Metadata in JDBC (Contd. ) • The methods of the Database. Meta. Data interface: (Contd. ) Method Description Result. Set get. Primary. Keys(String catalog, String schema, String table) Retrieves the information about the primary keys of the database tables. String get. URL() Retrieves the URL of the database. boolean is. Read. Only() Returns a boolean value that indicates whether the database is a read only database. boolean supports. Savepoints() Returns a boolean value that indicates whether the database supports savepoints. JDBC and Java. Beans Lesson 1 B / Slide 35 of 45
Creating Applications Using Advanced Features of JDBC Using Metadata in JDBC (Contd. ) • Using the Reult. Set. Meta. Data Interface • The Reult. Set. Meta. Data Interface contains various methods that enable you to retrieve information about the data in a result set. • The Result. Set interface provides the get. Meta. Data() method to create an object of the Result. Set. Meta. Data interface. • The method call to create an object of the Result. Set. Meta. Data interface: Result. Set. Meta. Data rm=rs. get. Meta. Data(); JDBC and Java. Beans Lesson 1 B / Slide 36 of 45
Creating Applications Using Advanced Features of JDBC Using Metadata in JDBC (Contd. ) • The following table lists the commonly used methods of the Result. Set. Meta. Data interface: Method Description int get. Column. Count() Returns an integer indicating the total number of columns in a Result. Set object. String get. Column. Label(int column_index) Retrieves the title of the table column corresponding to the index passed as a parameter to this method. String get. Column. Name(int column_index) Retrieves the name of the table column corresponding to the index passed as a parameter to this method. int get. Column. Type(int column_index) Retrieves the SQL data type of the table column corresponding to the index passed as a parameter. JDBC and Java. Beans Lesson 1 B / Slide 37 of 45
Creating Applications Using Advanced Features of JDBC Using Metadata in JDBC (Contd. ) • The methods of the Result. Set. Meta. Data interface: (Contd. ) Method Description String get. Table. Name(int column_index) Retrieves the name of the database table that contains the column corresponding to the index passed as a parameter. boolean is. Auto. Increment(int column_index) Returns a boolean value that indicates whether the table column corresponding to the index passed as a parameter increments automatically. boolean is. Case. Sensitive(int column_index) Returns a boolean value that indicates whether the table column corresponding to the index passed as a parameter is case sensitive. JDBC and Java. Beans Lesson 1 B / Slide 38 of 45
Creating Applications Using Advanced Features of JDBC Using Metadata in JDBC (Contd. ) • The methods of the Result. Set. Meta. Data interface: (Contd. ) Method Description boolean is. Read. Only(int column_index) Returns a boolean value that indicates whether the column in a Result. Set corresponding to the index passed as a parameter is read only. boolean is. Writable(int column_index) Returns a boolean value that indicates whether Result. Set column corresponding to the index passed as a parameter is updatable. JDBC and Java. Beans Lesson 1 B / Slide 39 of 45
Creating Applications Using Advanced Features of JDBC Demonstration- Creating an Application to Retrieve the Information of Database Tables • Problem Statement • The Manager of New Publishers publishing company, sometimes require the information about the tables of the database used by the company. He is not familiar with the SQL statements, therefore, he has asked you to create an application to determine the total number of columns and the data types of the columns of a given table. The table name has to be specified at the runtime. JDBC and Java. Beans Lesson 1 B / Slide 40 of 45
Creating Applications Using Advanced Features of JDBC Demonstration- Creating an Application to Retrieve the Information of Database Tables (Contd. ) • Solution • The get. Column. Name(), get. Column. Count(), and get. Column. Type. Name() methods of the Result. Set. Meta. Data interface are used to develop the above application. To solve the above problem, perform the following tasks: 1. Code the application. 2. Compile and execute the application. JDBC and Java. Beans Lesson 1 B / Slide 41 of 45
Creating Applications Using Advanced Features of JDBC Summary In this lesson, you learned: • The Prepared. Statement object of the Connection interface allows you to pass runtime parameters to the SQL statements using the placeholders. • There can be multiple placeholders in a single SQL statement. An index value is associated with each placeholder depending upon the position of the placeholder in the SQL statement. • The placeholder stores the value assigned to it until the value is explicitly changed. • A transaction is a set of one or more SQL statements that are executed as a single unit. A transaction is complete only when all the SQL statements in a transaction are successfully executed. • If the set. Auto. Commit() method is set to true the database operations performed by the SQL statements are automatically committed in the database. JDBC and Java. Beans Lesson 1 B / Slide 42 of 45
Creating Applications Using Advanced Features of JDBC Summary (Contd. ) • • • The commit() method reflects the changes made by the SQL statements permanently in the database. The rollback() method is used to undo the effect of all the SQL operations performed after the last commit operation. A batch is a group of update statements that are sent to a database to be executed as a single unit. You send the batch to a database as a single request using the same Connection object. The execute. Batch() method returns an integer array that stores the update count for all the SQL statements that are executed successfully in a batch. The update count is the number of database rows affected by the database operation performed by each SQL statement. Batch update operations can throw two types of exceptions, SQLException and Batch. Update. Exception. The SQLException is thrown when the database access problem occurs. The SQLException is also thrown when a SELECT statement that returns a Result. Set object is executed in a batch. JDBC and Java. Beans Lesson 1 B / Slide 43 of 45
Creating Applications Using Advanced Features of JDBC Summary (Contd. ) • • • The Batch. Update. Exception is thrown when the SQL statement in the batch cannot be executed due to the problem in accessing the specified table or presence of illegal arguments in the SQL statement. The Callable. Statement interface contains various methods that enable you to call the stored procedures from a database. The parameters of a stored procedure can take any of these three forms: • IN: Refers to the argument that you pass to a stored procedure. • OUT: Refers to the return value of a stored procedure. • INOUT: Enables you pass an argument to a stored procedure. The same parameters can also be used to pass a return value of a stored procedure. Metadata is the information about data, such as structure and properties of table. JDBC API provides two metadata interfaces to retrieve the information about the database and result set, Database. Meta. Data and Result. Set. Meta. Data. JDBC and Java. Beans Lesson 1 B / Slide 44 of 45
Creating Applications Using Advanced Features of JDBC Summary (Contd. ) • • The Database. Meta. Data interface declares methods that enable you to determine the properties of a database or RDBMS. The Result. Set. Meta. Data interface declares methods that enable you to determine information about data in a result set. The get. Meta. Data() method of the Connection interface enables you to declare the objects of the Database. Meta. Data interface. The methods in the Database. Meta. Data interface retrieve information only about the database to which a Java application is connected. The get. Meta. Data() method of the Result. Set interface enables you to create the instance of the Result. Set. Meta. Data interface. JDBC and Java. Beans Lesson 1 B / Slide 45 of 45
Скачать презентацию Creating Applications Using Advanced Features of JDBC Pre-assessment
409fde47838037e6806be9dea93b72af.ppt