Purposes of JDBC API
Following are the purposes of JDBC API:
1. To access tables and its data from relational databases.
2. It is key enterprise API, as it is very rare that an enterprise does not use database.
3. JDBC allows operations on a database, such as creating tables, querying data, updating data, inserting data from a Java application.
4. The results can be obtained and modified to and from a JDBC application.
5. The metadata of tables can be obtained.
6. Allowing only to navigate the result set in unidirectional, avoiding updates to the tables.
Describe 4 types of JDBC drivers and their characteristics with usages.
- A JDBC driver, which is a set of classes to implement the interfaces, is developed by a database vendor
- The interfaces and classes are database specific.
- Any number of drivers can be used by a JDBC application
- The drivers:
Type 1 – JDBC-ODBC Bridge : Provides JDBC API access through a set of ODBC drivers
At times, the ODBC native code and the database client need to be installed in one system.
ODBC calls need to be converted into JDBC calls to access data from database manager.
Type 2 – Native-API Driver: Converts JDBC calls into calls compatible on the client API for database managers.
Some binary code need to be loaded on each of the client systems
Performance is faster than the other drivers. This is due to native API
Type 3 – Network-Protocol Driver: Translates JDBC API calls into DBMS independent network protocol. In turn, they are translated to a DBMS protocol by the server.
Follows a 3-tier communication process
Provides facilities to obtain data from multiple databases
Type 4 – Native Protocol / Thin driver: All JDBC calls are converted directly into a vendor-specific database protocol.
Communicates directly with the vendor’s database, through socket connections
No middleware layers are needed
No need to translate the requests into intermediary format such as ODBC.
State the functionalities of important classes in JDBC packages.
The important interfaces in JDBC are:
1. java.sql.DriverManager: Used to manage JDBC drivers, thus establishes a connection to the database server
2. java.sql.Connection; Used for establishing connection to a database. All SQL statements executes within the connection context.
3. java.sql.Driver: Used for locating the driver to a particular DBMS.
4. java.sql.DatabaseMetaData: Used to return information about the tables, attributes
5. java.sql.Statement: Used to execute an SQL statement
6. java.sql.PreparedStatement: Used to represent a precompiled SQL statements to the database server to obtain results
7. java.sql.ResultSet: Used for processing the results that is returned by executing SQL statements.
8. java.sql.Date: Used to handle the SQL ‘date’ data type.
Explain how to use JDBC statement to execute SQL queries. Show in an example
A JDBC statement such as SELECT is executed by using a ResultSet object. The process is as follows:
- Create a Statement / PreparedStatement object.
- Create a ResultSet object
- Execute the Statement / PreparedStatement using – executeQuery(), executeUpdate() or execute() methods
- If a SELECT statement is executed, read the results and process them
Example Statement stmt = conn.createStatement();
ResultSet rslSet = stmt.executeQuery("SELECT LastName FROM Employees WHERE empId = 548521");
// Displaying the details of the result set
while (rslSet.next())
{
String lastName = rslSet.getString("LastName");
System.out.println(lastName + "\n");
}
Explain how to use Prepared Statement to execute parameterized queries. Show in an example
1. PreparedStatement is used to reduce the execution time
2. It supports sending parameters to the statement, which can be used for several times with similar action
3. It has SQL statement which is precompiled. This feature facilitates the execution of DBMS statement without compiling first.
The following code segment illustrates the use of PreparedStatement with parameters PreparedStatement updateSales = con.prepareStatement("UPDATE BOOKS SET SALES = ? WHERE BOOKNAME LIKE ? ");
updateSales.setInt(1, 75);
updateSales.setString(2, "PERFORMANCE");
updateSales.executeUpdate()
4. The prepareStatement has two parameters specified by ? signs. The methods setInt and setString substitutes the values 75 and PERFORMANCE respectively.
5. Likewise multiple set of values can be substituted for updating the table with a single PreparedStatement object.
What is JDBC Callable Statement?
1. CallableStatement object is utilized to invoke a stored procedure from a DBMS
2. A call to the stored procedure is available in a CallableStatement object.
3. The result of a stored procedure is obtained either with a result parameter or without result parameter
4. The result parameter is an OUT parameter, which is returned from the stored procedure
The following code snippet illustrates the use of a CallableStatement CallableStatement cstmt = con.prepareCall( "{call getTestData()}");
5. The parameter getTestData() is the name of the procedure and ‘call’ is a keyword which invokes the procedure.
6. The functionality in the procedure will affect the concerned tables in the DBMS.
Explain how to use JDBC Callable Statement to invoke stored procedures.
1. The CallableStatement is used to invoke stored procedures (procedures / functions in the database from a JDBC application
2. The CallableStatement object contains a call to the stored procedure
3. The prepareCall() method executes the procedure given followed by the keyword ‘call’,
The following code snippet illustrates how to use the CallableStatement object CallableStatement cs = con.prepareCall("{call ViewClients}");
ResultSet rs = cs.executeQuery();
4. The prepareCall specifies the execution of ViewClients procedure
5. The executeQuery() method of CallableStatement sends the instructions to the DBMS to execute the procedure.
6. The procedure has a query statement, hence the method executeQuery() is used from CallableStatement object.
7. If the procedure has DML or DDL statement, the method executeUpdate() should be used.
What is batch updates in JDBC? Explain with an example.
1. Multiple update operations on a data source to be performed all at once is known as Batch Update.
2. Multiple updates submission together, can greatly improve the performance of the application.
3. The objects of Statement,PreparedStatement and CallableStatement are used for submitting batch updates.
The following code snippet illustrates the use of batch updates // turn off autocommit
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
stmt.addBatch("INSERT INTO employees VALUES (1230, 'Bill Fleming')");
stmt.addBatch("INSERT INTO departments VALUES (951, 'Liasion')");
stmt.addBatch("INSERT INTO emp_dept VALUES (1230, 951)");
// invoke executeBatch() method to update the above 3 table together
int[] updateCounts = stmt.executeBatch();
4. The executeBatch() method submits the set of batch statements to the data source for the purpose of execution.
5. The executeBatch() method returns an integer array which has entries of each element in the abatch
6. The entry of the array value is greater than or equal to zero, when the batch element was processed successfully and the value is an update count which indicates the number of rows that were affected by the update.
7. The entry of the array value is -2, when the batch element was processed successfully, but the number of the effected rows is unknown
8. The batch update works the same way for PreparedStatement as well as CallableStatement.
