How many types of JDBC Drivers are present and what are they?
JDBC drivers are divided into four types or levels. The different types of jdbc drivers are:
Type 1: JDBC-ODBC Bridge driver (Bridge)
Type 2: Native-API/partly Java driver (Native)
Type 3: All Java/Net-protocol driver (Middleware)
Type 4: All Java/Native-protocol driver (Pure)
What Class.forName will do while loading drivers?
It is used to create an instance of a driver and register it with the Driver Manager. When you have loaded a driver, it is available for making a connection with a DBMS.
What are stored procedures? How is it useful?
A stored procedure is a set of statements/commands which reside in the database. The stored procedure is pre-compiled and saves the database the effort of parsing and compiling sql statements every time a query is run. Each database has its own stored procedure language, usually a variant of C with a SQL preprocessor.
Before the advent of 3-tier/n-tier architecture it was pretty common for stored procedures to implement the business logic (A lot of systems still do it). The biggest advantage is of course speed. Also certain kinds of data manipulations are not achieved in SQL. Stored procedures provide a mechanism to do these manipulations. Stored procedures are also useful when you want to do Batch updates/exports kind of stuff on the db. The overhead of a JDBC Connection may be significant in these cases.
How to call a Stored Procedure from JDBC?
The first step is to create a CallableStatement object. As with Statement and PreparedStatement objects, this is done with an open Connection object. A CallableStatement object contains a call to a stored procedure.
CallableStatement cs = con.prepareCall("{call SHOW_SUPPLIERS}");
ResultSet rs = cs.executeQuery();
The part that is enclosed in curly braces is the escape syntax for stored procedures. When the driver encounters "{call SHOW_SUPPLIERS}", it will translate this escape syntax into the native SQL used by the database to call the stored procedure named SHOW_SUPPLIERS.
Is the JDBC-ODBC Bridge multi-threaded?
No. The JDBC-ODBC Bridge does not support concurrent access from different threads. The JDBC-ODBC Bridge uses synchronized methods to serialize all of the calls that it makes to ODBC. Multi-threaded Java programs may use the Bridge, but they won't get the advantages of multi-threading.
What is the advantage of using PreparedStatement?
If we are using PreparedStatement the execution time will be less. This is because PreparedStatement object contains SQL statement that has been precompiled. Thus, when the PreparedStatement is later executed, the DBMS does not have to recompile the SQL statement and prepared an execution plan - it simply runs the statement.
What is a "dirty read"?
Quite often in database processing, we come across the situation wherein one transaction can change a value, and a second transaction can read this value before the original change has been committed or rolled back. This is known as a dirty read scenario because there is always the possibility that the first transaction may rollback the change, resulting in the second transaction having read an invalid value.
What are different types of isolation levels in JDBC and explain where you can use them?
Five types of isolation levels are as follows:
1. TRANSACTION_READ_COMMITED
If the application needs only committed records, then TRANSACTION_READ_COMMITED isolation is the good choice.
2. TRANSACTION_REPEATABLE_READ
If the application needs to read a row exclusively till you finish your work, then TRANSACTION_REPEATABLE_READ is the best choice.
3. TRANSACTION_SERIALIZABLE
If the application needs to control all of the transaction problems (dirty read, phantom read and unrepeatable read), you can choose TRANSACTION_SERIALIZABLE for maximum safety.
4. TRANSACTION_NONE
If the application doesn’t have to deal with concurrent transactions, then the best choice is TRANSACTION_NONE to improve performance.
5. TRANSACTION_READ_UNCOMMITED
If the application is searching for records from the database then you can easily choose TRANSACTION_READ_UNCOMMITED because you need not worry about other programs that are inserting records at the same time.
What are the different types of statements in JDBC?
The JDBC API has 3 Statements
1. Statement, 2. PreparedStatement, 3. CallableStatement.
The key features of these are as follows:
Statement
1. This interface is used for executing a static SQL statement and returning the results it produces.
2. The object of Statement class can be created using Connection.createStatement() method.
PreparedStatement
1. A SQL statement is pre-compiled and stored in a PreparedStatement object.
2. This object can then be used to efficiently execute this statement multiple times.
3. The object of PreparedStatement class can be created using Connection.prepareStatement() method. This extends Statement interface.
CallableStatement
1. This interface is used to execute SQL stored procedures.
2. This extends PreparedStatement interface.
3. The object of CallableStatement class can be created using Connection.prepareCall() method.
What is JDBC?
JDBC may stand for Java Database Connectivity. It is also a trade mark. JDBC is a layer of abstraction that allows users to choose between databases. It allows you to change to a different database engine and to write to a single API. JDBC allows you to write database applications in Java without having to concern yourself with the underlying details of a particular database.
What are the common tasks of JDBC?
Common tasks of JDBC are as follows:
1. Create an instance of a JDBC driver or load JDBC drivers through jdbc.drivers
2. Register a driver
3. Specify a database
4. Open a database connection
5. Submit a query
6. Receive result
7. Process result