Java.sql package defines the classes and interfaces required for the JDBC program to access the relation data stored in a database. These APIs can be used to connect to the relational database and manipulate the data (insert, update, delete, and so on) stored in tabular form using the SQL standard. The interfaces defined in this package are implemented by the driver specific classes and the definition can differ from vendor to vendor.
Getting a connection
A connection to a database can be obtained using the DriverManager class of the java.sql package.Before getting connection, the driver specific classes must be loaded and registered to the DriverManager. Any number of drivers can be loaded and registered with the Driver Manager. You can use the code shown below to load a driver specific class.
Loading the Type 4 driver classes
The forName method take a string argument whose value is the name of the package which implements the interfaces defined in java.sql package. The connection to a database can be obtained by calling the getConnection method of DriverManager class. This method takes a string value (URL) as an input, which gives the information required to connect to the database. A typical URL format for Type 4 driver is:
The code below returns the connection as Connection class object. Loading the Type 4 driver and getting connection to the database
In the case where multiple drivers are loaded, DriverManager is responsible for making use of the appropriate driver to make a connection.
After getting the connection, data can be selected, inserted, updated, or deleted from the relational tables using SQL statements. JDBC driver implements two interfaces Statement and PreparedStatement for this purpose. An object of any of these classes is required for running an SQL statement.
An object of Statement (or class implementing the Statement interface) can be used to execute the SQL statement which does not contain parameter markers. An object can be created from the Connection object using createStatement method.
Any number of statements can be created for a particular connection object. Statement interface defines executeQuery and executeUpdate methods to execute a query statement. The executeQuery method is used when the result set is expected (for example, for the SELECT statement) as output of the query.
Alternatively, executeUpdate method is used for updating the database contents (for example, INSERT, UPDATE, and DELETE statements). The executeQuery method returns the ResultSet object, which represents a set of rows returned by the SELECT query. This ResultSet object can be used to fetch the result row by row. executeUpdate returns an integer value, which indicates the number of rows updated, inserted, or deleted from the database based on the type of SQL statement.
SELECT using Statement object
below example contains a code snippet for the method getProducts from the application code. The method select the product names from the CUSTOMER table and stores them in the String array.
SELECT using Statement object
The Statement object also provides execute methods to execute any type of query. The execute method is useful when the decision on SQL statement type is taken at runtime. It returns true if the result of the SQL statement is a ResultSet object (for example, for SELECT statement) and false if the result is update count or there is no result. Based on the return value, getResultSet and getUpdateCount methods can be used to get the resultSet object or the update count.
SELECT using Statement object’s execute method
UPDATE using Statement object
Below it give the code snippet to update a value in the table using the Statement object.
UPDATE using Statement object
An INSERT or DELETE statement can be executed using the Statement object in the same fashion.
An object of PreparedStatement (or a class implementing the PreparedStatement interface) can be used to run the queries, which can contain parameter markers. A PreparedStatement object can be created using the prepareStatement method of Connection object. PreparedStatement extends the Statement interface.
If the SQL statement contains parameter markers, the values for these parameter markers need to be set before executing the statement. Value can be set using setXXX methods of PreparedStatement object where XXX denoted the data type of the parameter marker. setXXX methods are also called setter methods.
The following are the examples of setXXX methods:
After setting the parameter values, the SQL statement can be executed using any of the executeQuery, executeUpdate, or execute method based on the SQL type.
SELECT using PreparedStatement object
below it gives the code snippet for the method isCustomer from the application code. The method passes the value of the customer id to the method, which is used to pass the value to the SELECT query. The method returns null if the customer id does not exist.
SELECT using PreparedStatement object
INSERT using PreparedStatement
This example shows how to insert the value in the table using the PreparedStatement object.
INSERT using the PreparedStatement object
Tip: Trying to run a query which updates the content of the database with the executeQuery method will result in an exception. Similarly, trying to run a select query using the executeUpdate method will give an exception. Use the execute method whenever you are unsure about the query at compile time (that is, the query is generated at runtime using user’s input) and check for the Boolean value returned by the method to decide the result.
An object of the Callable Statement interface (or the class implementing the Callable Statement interface) can be used to call the stored procedure. The Callable Statement interface extends the Prepared Statement interface. An object of Callable Statement can be created using the prepare Call method of Connection object.
The parameter for a CallableStatement can be of three types:
The value for IN and INOUT parameters must be set before executing the Callable Statement. In the same way, OUT and INOUT parameters should be registered to the database before executing the statement. The Callable Statement can be executed using execute, executeQuery, and executeUpdate methods. The usage of these three methods is:
Below example gives the code snippet from the createPorder method of the application code. The method calls a stored procedure named createOrder. The stored procedure code is given in the class CreateOrder. The stored procedure has two IN parameters and one OUT parameter. The stored procedure should be registered before using it.
Calling a stored procedure using CallableStatement object
The executeQuery method of Statement, PreparedStatement, and CallableStatement returns the ResultSet object as an output of the query. The ResultSet object maintains a cursor to the current row of the result set of a query.
This cursor can be advanced to the next row by using the next method of this object. The cursor by default can only be moved forward and is read-only. The columns value for the current row can be fetched by calling the getXXX method of the ResultSet object where XXX denotes the data type of the column. The getXXX takes the column number or name for the current row as an input.
Once all the rows are fetched, the next method returns an exception. By default, the cursor cannot be moved backward and updated. Cursors can be moved backward or moved directly to a specific row by defining the scrollability of the ResultSet object. Similarly, the cursor can be updated by defining the updateability of the ResultSet object.
The following three properties for the ResultSet can be set while creating the Statement object:
While resultSetType defines the scrollability of the cursor, resultSetConcurrency defines the updatabilty of the cursor. resultSetHoldability indicates that the result set will remain open even after the statement is closed. The createStatement and prepareStatement supporting all of these properties have the following syntax:
All three properties are optional. Example 5-10 defines a Statement object with a scrollable and updatable ResultSet. It fetched the ResultSet row by row and update the status whenever the record with value 5003 is found. It prints all the records after the update is done.
Scrollable and updatable Result set
The supported methods for the scrollable cursor are:
Tip: Each ResultSet object is associated with a Statement, PreparedStatement, or Callable Statement object. The sensitivity or the updatability cannot be defined at the resultSet level. It should be defined while creating the statement object itself, which means ResultSet objects created for SQL statements using the same statement object will have the same properties. So once the ResultSet object is created, it is not possible to change the properties.
With batch updates, a group of updates can be done to the database at the same time instead of updating one by one. The Statement object provides the following methods for batch updates:
Example shows how to do the batch updates using the Statement object.
Batch update with Statement object
PreparedStatement and CallableStatement can also be used to make the batch updates. This can be done by running the same PreparedStatement or CallableStatement with different parameter values.
Below Example gives the code snippet from the application code from the method createCart. The method takes an HashMap object which has the key-value pair of product name and the quantity as an input and inserts the PID and QUANTITY values using a batch update into a table. The table is also created in the same method. The name of the table is generated by appending the CID value to the word cart.
Batch update with PreparedStatement object
Tip: To execute a batch of statements, all the statements in a single batch should be batch compatible. Batch compatible statements fall in two categories:
All the statement in category 1 are batch compatible. In the same way, all the statements in category 2 are batch compatible, but none of the statements in category 1 are batch compatible with the statements in category 2.
The java.sql package provides three interfaces to access the metadata at the database level, parameter level, and result set level. The driver class implements these interfaces.
DatabaseMetaData object gives the information of the database as a whole. The object can be created by calling the method get Meta Data of Connection object. The Database MetaData object provides a lot of methods to get the database information. Some of them are explained below:
This interface is included in JDBC universal driver and is not supported for Type 2 drivers. This interface contains the methods to retrieve the information regarding the parameter’s markers in a PreparedStatement object.The ParameterMetaData object can be created by calling the getParameterMetaData method of PrepareStatement object. This interface has the following methods:
This interface provides the methods to get the ResultSet information. This information is specially needed to retrieve the data when the application does not know about the column types in the ResultSet. The object of ResultSetMetaData can be created by calling the getResultSetMetaData method on the ResultSet object. This interface has the following important methods:
IBM DB2 Related Interview Questions
|IBM Websphere Interview Questions||IBM-REXX Interview Questions|
|IBM Cognos Interview Questions||IBM DB2 Interview Questions|
|J2EE Interview Questions||COBOL Interview Questions|
|IBM-JCL Interview Questions||DB2 Using SQL Interview Questions|
|IBM WAS Administration Interview Questions||IBM WebSphere Administration Interview Questions|
|Database Administration Interview Questions||DB2 SQL Programming Interview Questions|
|Mainframe DB2 Interview Questions|
IBM DB2 Related Practice Tests
|IBM Websphere Practice Tests||IBM Cognos Practice Tests|
|IBM DB2 Practice Tests||J2EE Practice Tests|
|COBOL Practice Tests||IBM-JCL Practice Tests|
|DB2 Using SQL Practice Tests||IBM WAS Administration Practice Tests|
|Database Administration Practice Tests||DB2 SQL Programming Practice Tests|
Ibm Db2 Tutorial
Db2 Application Development Overview
Application Development With Db2 Purexml
Application Development With Php
Application Development With C/c++
Application Development With Java
Application Development With .net
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.