Q. What is DAO factory design pattern in Java?
Data Access Object Pattern or DAO pattern is used to separate low level data accessing API or operations from high level business services.
DAO pattern is based on abstraction and encapsulation design principles and shields rest of application from any change in the persistence layer e.g. change of database from Oracle to MySQL, change of persistence technology e.g. from File System to Database.
Q. What are the differences between ResultSet and RowSet?
A ResultSet maintains a connection to a database and because of that it can’t be serialized and also we cant pass the Resultset object from one class to other class across the network.
RowSet is a disconnected, serializable version of a JDBC ResultSet and also the RowSet extends the ResultSet interface so it has all the methods of ResultSet. The RowSet can be serialized because it doesn’t have a connection to any database and also it can be sent from one class to another across the network.
| ResultSet | RowSet |
|---|---|
| A ResultSet always maintains connection with the database. | A RowSet can be connected, disconnected from the database. |
| It cannot be serialized. | A RowSet object can be serialized. |
| ResultSet object cannot be passed other over network. | You can pass a RowSet object over the network. |
| ResultSet object is not a JavaBean object You can create/get a result set using the executeQuery() method. | ResultSet Object is a JavaBean object. You can get a RowSet using the RowSetProvider.newFactory().createJdb cRowSet() method. |
| By default, ResultSet object is not scrollable or, updatable. | By default, RowSet object is scrollable and updatable. |
Q. How can we execute stored procedures using CallableStatement?
CallableStatement interface in java is used to call stored procedure from java program. Stored Procedures are group of statements that we compile in the database for some task. Stored procedures are beneficial when we are dealing with multiple tables with complex scenario and rather than sending multiple queries to the database, we can send required data to the stored procedure and have the logic executed in the database server itself.
A CallableStatement object provides a way to call stored procedures using JDBC. Connection.prepareCall() method provides you CallableStatement object.
Q. What are the differences between Statement and PreparedStatement interface?
JDBC API provides 3 different interfaces to execute the different types of SQL queries. They are,
- Statement – Used to execute normal SQL queries.
- PreparedStatement – Used to execute dynamic or parameterized SQL queries.
- CallableStatement – Used to execute the stored procedures.
1. Statement
Statement interface is used to execute normal SQL queries. We can’t pass the parameters to SQL query at run time using this interface. This interface is preferred over other two interfaces if we are executing a particular SQL query only once. The performance of this interface is also very less compared to other two interfaces. In most of time, Statement interface is used for DDL statements like CREATE, ALTER, DROP etc.
2. PreparedStatement
PreparedStatement is used to execute dynamic or parameterized SQL queries. PreparedStatement extends Statement interface. We can pass the parameters to SQL query at run time using this interface. It is recommended to use PreparedStatement if we are executing a particular SQL query multiple times. It gives better performance than Statement interface. Because, PreparedStatement are precompiled and the query plan is created only once irrespective of how many times we are executing that query.
3. CallableStatement
CallableStatement is used to execute the stored procedures. CallableStatement extends PreparedStatement. Usng CallableStatement, we can pass 3 types of parameters to stored procedures. They are : IN – used to pass the values to stored procedure, OUT – used to hold the result returned by the stored procedure and IN OUT – acts as both IN and OUT parameter. Before calling the stored procedure, we must register OUT parameters using registerOutParameter() method of CallableStatement. The performance of this interface is higher than the other two interfaces. Because, it calls the stored procedures which are already compiled and stored in the database server.
Q. What are the different types of locking in JDBC?
The types of locks in JDBC:
1. Row and Key Locks: Useful when updating the rows (update, insert or delete operations), as they increase concurrency.
2. Page Locks: Locks the page when the transaction updates or inserts or deletes rows or keys. The database server locks the entire page that contains the row. The lock is made only once by database server, even more rows are updated. This lock is suggested in the situation where large number of rows is to be changed at once.
3. Table Locks: Utilizing table locks is efficient when a query accesses most of the tables of a table. These are of two types:
a) Shared lock: One shared lock is placed by the database server, which prevents other to perform any update operations.
b) Exclusive lock: One exclusive lock is placed by the database server, irrespective of the number of the rows that are updated.
4. Database Lock: In order to prevent the read or update access from other transactions when the database is open, the database lock is used.