Interview questions in Java on JDBC drivers
Available drivers in JDBC?
Available drivers in JDBC?
JDBC technology drivers fit into one of four categories:
1. A JDBC-ODBC bridge provides JDBC API access via one or more ODBC drivers. Note that some ODBC native code and in many cases native database client code must be
loaded on each client machine that uses this type of driver. Hence, this kind of driver is generally most appropriate when automatic installation and downloading
of a Java technology application is not important.
2. A native-API partly Java technology-enabled driver converts JDBC calls into calls on the client API for Oracle, Sybase, Informix, DB2, or other DBMS. Note that,
like the bridge driver, this style of driver requires that some binary code be loaded on each client machine.
3. A net-protocol fully Java technology-enabled driver translates JDBC API calls into a DBMS-independent net protocol which is then translated to a DBMS protocol
by a server. This net server middleware is able to connect all of its Java technology-based clients to many different databases. The specific protocol used
depends on the vendor. In general, this is the most flexible JDBC API alternative. It is likely that all vendors of this solution will provide products suitable
for Intranet use. In order for these products to also support Internet access they must handle the additional requirements for security, access through firewalls,
etc., that the Web imposes. Several vendors are adding JDBC technology-based drivers to their existing database middleware products.
4. A native-protocol fully Java technology-enabled driver converts JDBC technology calls into the network protocol used by DBMSs directly. This allows a direct call
from the client machine to the DBMS server and is a practical solution for Intranet access. Since many of these protocols are proprietary the database vendors
themselves will be the primary source for this style of driver. Several database vendors have these in progress.
What are the types of statements in JDBC?
the JDBC API has 3 Interfaces, (1. Statement, 2. PreparedStatement, 3. CallableStatement ). The key features of these are as follows:
Statement: This interface is used for executing a static SQL statement and returning the results it produces. The object of Statement class can be created using
Connection.createStatement() method.
PreparedStatement :A SQL statement is pre-compiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement
multiple times.The object of PreparedStatement class can be created using Connection.prepareStatement() method. This extends Statement interface.
CallableStatement: This interface is used to execute SQL stored procedures.This extends PreparedStatement interface.The object of CallableStatement class can be
created using Connection.prepareCall() method.
What is a stored procedure?How to call stored procedure using JDBC API?
Stored procedure is a group of SQL statements that forms a logical unit and performs a particular task. Stored Procedures are used to encapsulate a set of operations or queries to execute on database. Stored procedures can be compiled and executed with different parameters and results and may have any combination
of input/output parameters. Stored procedures can be called using CallableStatement class in JDBC API. Below code snippet shows how this can be achieved.
CallableStatement cs = con.prepareCall("{call MY_STORED_PROC_NAME}");
ResultSet rs = cs.executeQuery();
What is Connection pooling? What are the advantages of using a connection pool?
Connection Pooling is a technique used for sharing the server resources among requested clients. It was pioneered by database vendors to allow multiple clients to
share a cached set of connection objects that provides access to a database.Getting connection and disconnecting are costly operation, which affects the
application performance, so we should avoid creating multiple connection during multiple database interactions. A pool contains set of Database connections
which are already connected, and any client who wants to use it can take it from pool and when done with using it can be returned back to the pool.
Apart from performance this also saves you resources as there may be limited database connections available for your application.
How to do database connection using JDBC thin driver?
import java.sql.*;
class JDBCTest {
public static void main (String args []) throws Exception
{
//Load driver class
Class.forName ("oracle.jdbc.driver.OracleDriver");
//Create connection
Connection conn = DriverManager.getConnection
("jdbc:oracle:thin:@hostname:1526:testdb", "scott", "tiger");
// @machineName:port:SID, userid, password
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select 'Hi' from dual");
while (rs.next())
System.out.println (rs.getString(1)); // Print col 1 => Hi
stmt.close();
}
}
What does Class.forName() method do?
Method forName() is a static method of java.lang.Class. This can be used to dynamically load a class at run-time.
- Load the class MyClass.
- Execute any static block code of MyClass.
- Return an instance of MyClass.
JDBC Driver loading using Class.forName is a good example of best use of this method. The driver loading is done like this :
Class.forName("org.mysql.Driver");
All JDBC Drivers have a static block that registers itself with DriverManager and DriverManager has static initializer method registerDriver() which can be
called in a static blocks of Driver class. A MySQL JDBC Driver has a static initializer which looks like this:
static {
try {
java.sql.DriverManager.registerDriver(new Driver());
} catch (SQLException E) {
throw new RuntimeException("Can't register driver!");
}
}
Which one will you use Statement or PreparedStatement?
Statement is a object used for executing a static SQL statement and returning the results it produces.
PreparedStatement is a SQL statement which is precompiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this
statement multiple times.
There are few advantages of using PreparedStatements over Statements
Since its pre-compiled, Executing the same query multiple times in loop, binding different parameter values each time is faster. (What does pre-compiled statement
means? The prepared statement(pre-compiled) concept is not specific to Java, it is a database concept. Statement precompiling means: when you execute a SQL query,
database server will prepare a execution plan before executing the actual query, this execution plan will be cached at database server for further execution.)
In PreparedStatement the setDate()/setString() methods can be used to escape dates and strings properly, in a database-independent way.
What does setAutoCommit(false) do?
A JDBC connection is created in auto-commit mode by default. This means that each individual SQL statement is treated as a transaction and will be automatically
committed as soon as it is executed. If you require two or more statements to be grouped into a transaction then you need to disable auto-commit mode using
below command :
con.setAutoCommit(false);
Once auto-commit mode is disabled, no SQL statements will be committed until you explicitly call the commit method. A Simple transaction with use of
autocommit flag is demonstrated below.
con.setAutoCommit(false);
PreparedStatement updateStmt =
con.prepareStatement( "UPDATE EMPLOYEE SET SALARY = ? WHERE EMP_NAME LIKE ?");
updateStmt.setInt(1, 5000); updateSales.setString(2, "Jack");
updateStmt.executeUpdate();
updateStmt.setInt(1, 6000); updateSales.setString(2, "Tom");
updateStmt.executeUpdate();
con.commit();
con.setAutoCommit(true);
Database warnings and How to handle database warnings in JDBC?
Warnings are issued by database to notify user of a problem which may not be very severe. Database warnings do not stop the execution of SQL statements.
In JDBC SQLWarning is an exception that provides information on database access warnings. Warnings are silently chained to the object whose method caused it
to be reported.
Warnings may be retrieved from Connection, Statement, and ResultSet objects.
A) Handling SQLWarning from connection object :
//Retrieving warning from connection object
SQLWarning warning = conn.getWarnings();
//Retrieving next warning from warning object itself
SQLWarning nextWarning = warning.getNextWarning();
//Clear all warnings reported for this Connection object.
conn.clearWarnings();
//Retrieving warning from connection object
SQLWarning warning = conn.getWarnings();
//Retrieving next warning from warning object itself
SQLWarning nextWarning = warning.getNextWarning();
//Clear all warnings reported for this Connection object.
conn.clearWarnings();
B) Handling SQLWarning from Statement object
//Retrieving warning from statement object
stmt.getWarnings();
//Retrieving next warning from warning object itself
SQLWarning nextWarning = warning.getNextWarning();
//Clear all warnings reported for this Statement object.
stmt.clearWarnings();
//Retrieving warning from statement object
stmt.getWarnings();
//Retrieving next warning from warning object itself
SQLWarning nextWarning = warning.getNextWarning();
//Clear all warnings reported for this Statement object.
stmt.clearWarnings();
C) Handling SQLWarning from ResultSet object
//Retrieving warning from resultset object
rs.getWarnings();
//Retrieving next warning from warning object itself
SQLWarning nextWarning = warning.getNextWarning();
//Clear all warnings reported for this resultset object.
rs.clearWarnings();
The call to getWarnings() method in any of above way retrieves the first warning reported by calls on this object. If there is more than one warning,
subsequent warnings will be chained to the first one and can be retrieved by calling the method SQLWarning.getNextWarning on the warning that was retrieved
previously.
A call to clearWarnings() method clears all warnings reported for this object. After a call to this method, the method getWarnings returns null until a new
warning is reported for this object.
Trying to call getWarning() on a connection after it has been closed will cause an SQLException to be thrown. Similarly, trying to retrieve a warning on a
statement after it has been closed or on a result set after it has been closed will cause an SQLException to be thrown. Note that closing a statement also
closes a result set that it might have produced.
What is Metadata and why should I use it?
JDBC API has 2 Metadata interfaces DatabaseMetaData & ResultSetMetaData.
The DatabaseMetaData provides Comprehensive information about the database as a whole. This interface is implemented by driver vendors to let users know the
capabilities of a Database Management System (DBMS) in combination with the driver based on JDBC technology ("JDBC driver") that is used with it. Below is a
sample code which demonstrates how we can use the DatabaseMetaData
DatabaseMetaData md = conn.getMetaData();
System.out.println("Database Name: " + md.getDatabaseProductName());
System.out.println("Database Version: " + md.getDatabaseProductVersion());
System.out.println("Driver Name: " + md.getDriverName());
System.out.println("Driver Version: " + md.getDriverVersion());
DatabaseMetaData md = conn.getMetaData();
System.out.println("Database Name: " + md.getDatabaseProductName());
System.out.println("Database Version: " + md.getDatabaseProductVersion());
System.out.println("Driver Name: " + md.getDriverName());
System.out.println("Driver Version: " + md.getDriverVersion());
The ResultSetMetaData is an object that can be used to get information about the types and properties of the columns in a ResultSet object. Use DatabaseMetaData
to find information about your database, such as its capabilities and structure. Use ResultSetMetaData to find information about the results of an SQL query,
such as size and types of columns. Below a sample code which demonstrates how we can use the ResultSetMetaData
ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM TABLE2");
ResultSetMetaData rsmd = rs.getMetaData();
int numberOfColumns = rsmd.getColumnCount();
boolean b = rsmd.isSearchable(1);
ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM TABLE2");
ResultSetMetaData rsmd = rs.getMetaData();
int numberOfColumns = rsmd.getColumnCount();
boolean b = rsmd.isSearchable(1);
What is the difference between RowSet and ResultSet?
RowSet is a interface that adds support to the JDBC API for the JavaBeans component model. A rowset, which can be used as a JavaBeans component in a visual Bean
development environment, can be created and configured at design time and executed at run time. The RowSet interface provides a set of JavaBeans properties that
allow a RowSet instance to be configured to connect to a JDBC data source and read some data from the data source.
A group of setter methods (setInt, setBytes, setString, and so on) provide a way to pass input parameters to a rowset's command property. This command is the
SQL query the rowset uses when it gets its data from a relational database, which is generally the case.
Rowsets are easy to use since the RowSet interface extends the standard java.sql.ResultSet interface so it has all the methods of ResultSet. There are two clear
advantages of using RowSet over ResultSet
RowSet makes it possible to use the ResultSet object as a JavaBeans component. As a consequence, a result set can, for example, be a component in a Swing
application.
What is a connected RowSet?
A RowSet object may make a connection with a data source and maintain that connection throughout its life cycle, in which case it is called a connected rowset.
A rowset may also make a connection with a data source, get data from it, and then close the connection. Such a rowset is called a disconnected rowset.
A disconnected rowset may make changes to its data while it is disconnected and then send the changes back to the original source of the data, but it
must reestablish a connection to do so.
Example of Connected RowSet:
A JdbcRowSet object is a example of connected RowSet, which means it continually maintains its connection to a database using a JDBC technology-enabled driver.
Disconnected RowSet
A disconnected rowset may have a reader (a RowSetReader object) and a writer (a RowSetWriter object) associated with it. The reader may be implemented in many
different ways to populate a rowset with data, including getting data from a non-relational data source. The writer can also be implemented in many different
ways to propagate changes made to the rowset's data back to the underlying data source.
Example of Disconnected RowSet:
A CachedRowSet object is a example of disconnected rowset, which means that it makes use of a connection to its data source only briefly. It connects to its
data source while it is reading data to populate itself with rows and again while it is propagating changes back to its underlying data source. The rest of the
time, a CachedRowSet object is disconnected, including while its data is being modified. Being disconnected makes a RowSet object much leaner and therefore much
easier to pass to another component.
Post a Comment