Well, here we go. The following are some questions that you may encounter on JDBC concepts during your interviews. Remember that if you are a junior level developer (with less than 3 years working experience) many of these questions may seem too complicated for you and frankly you won’t be expected to know them either.
You cannot think of an Enterprise Java Application that does not connect to a database. So, a strong understanding of JDBC concepts is crucial to your success as a potential candidate during an interview.
Questions – Part 1:
1. What is the JDBC?
Java Database Connectivity (JDBC) is a standard Java API that is used to interact with relational databases from within a Java application. JDBC has set of classes and interfaces which can use from our Java application and interact with the database.
2. What are the Basic Steps in writing a Java program to connect to a database using JDBC?
The basic steps in connecting to a database using JDBC are:
1. Load the RDBMS specific JDBC driver because this driver actually communicates with the database (Incase of JDBC 4.0 this is automatically loaded).
2. Open the connection to database which is then used to send SQL statements and get results back.
3. Create JDBC Statement object. This object contains SQL query.
4. Execute statement which returns resultset(s). ResultSet contains the tuples of database table as a result of SQL query.
5. Process the result set.
6. Close the connection.
3. What are the main components of JDBC ?
The five main components of JDBC are:
1. Driver Manager
2. Driver
3. Connection
4. Statement &
5. ResultSet
4. How does a JDBC application work?
A JDBC application can be logically divided into two layers:
1. Driver layer
2. Application layer
First the Driver layer consists of DriverManager class and the available JDBC drivers. The application begins with requesting the DriverManager for the connection. An appropriate driver is choosen and is used for establishing the connection. This connection is given to the application which falls under the application layer. The application uses this connection to create Statement kind of objects, through which SQL commands are sent to backend and obtain the results.
5. How do I load a database driver with JDBC 4.0 / Java 6?
Provided the JAR file containing the driver is properly configured, just place the JAR file in the classpath. Java developers NO longer need to explicitly load JDBC drivers using code like Class.forName() to register a JDBC driver.The DriverManager class takes care of this by automatically locating a suitable driver when the DriverManager.getConnection() method is called. This feature is backward-compatible, so no changes are needed to the existing JDBC code.
Remember - if the correct JAR Files are missing in the classpath, this auto feature will not work
6. What is JDBC Driver interface?
The JDBC Driver interface provides vendor-specific implementations of the abstract classes provided by the JDBC API. Each vendor driver must provide implementations of the java.sql.Connection, Statement, PreparedStatement, CallableStatement, ResultSet and Driver so that the Java classes that connect to the database can use them.
7. What does the connection object represents?
The connection object represents a literal connection or a communication context through which our Java code can interact with a database. i.e., all communication with database is through connection object only.
8. What is a Statement?
The Statement acts like a vehicle through which SQL commands can be sent. You can create a Statement as follows:
Statement stmt = conn.createStatement();
This method returns an object which implements statement interface which can be executed.
9. What is a PreparedStatement?
A prepared statement is an SQL statement that is precompiled by the database. It is a sub-set of the Statement. Through precompilation, prepared statements improve the performance of SQL commands that are executed multiple times (given that the database supports prepared statements). Once compiled, prepared statements can be customized prior to each execution by altering predefined SQL parameters.
You can create a prepared statement as follows:
PreparedStatement pstmt = conn.prepareStatement("UPDATE Emp_Salary SET Salary = ? WHERE Emp_Id = ?");
pstmt.setBigDecimal(1, 75000.00);
pstmt.setInt(2, 12345);
Note that conn is an instance of the collection class and the "?" represent the parameters that are to be passed to the query/prepared statement before it can be executed.
10. What are callable statements?
Callable statements are used from JDBC application to invoke stored procedures and functions objects of a database.
11. Can you call a stored procedure using JDBC? If so, how?
Yes, you can call Stored Procedures using JDBC. we have to use the CallableStatement to do so.
Example:
CallableStatement stproc_stmt = conn.prepareCall("{call procname(?,?,?)}");
Here conn is an instance of the Connection class.
12. How many types of JDBC drivers are there and What are they?
There are four types of drivers defined by JDBC as follows:
1. Type 1 – JDBC-ODBC Bridge
2. Type 2 – Native API (Partly Java) Driver
3. Type 3 – Net-Protocol Fully Java Driver
4. Type 4 – Pure Java Driver
Type 4 JDBC driver is most preferred kind of approach in JDBC.
13. Which type of JDBC driver is the fastest one?
JDBC Net pure Java driver(Type IV) is the fastest driver because it converts the JDBC calls into vendor specific protocol calls and it directly interacts with the database.
14. Does the JDBC-ODBC Bridge support multiple concurrent open statements per connection?
No. You can open only one Statement object per connection when you are using the JDBC-ODBC Bridge.
15. What are the standard transaction isolation levels defined by JDBC?
The values are defined in the class java.sql.Connection and are:
TRANSACTION_NONE
TRANSACTION_READ_COMMITTED
TRANSACTION_READ_UNCOMMITTED
TRANSACTION_REPEATABLE_READ
TRANSACTION_SERIALIZABLE
You need to double check that the database you are connecting to, supports these isolation levelts.
16. What is a ResultSet?
The ResultSet represents set of rows retrieved due to query execution. You get a result set object as the output when you execute a query using the Statement or the PreparedStatement objects.
Example:
ResultSet rs = stmt.executeQuery(sqlQuery);
17. What are the types of resultsets?
JDBC supports 3 types of ResultSets. They are:
1. TYPE_FORWARD_ONLY
2. TYPE_SCROLL_INSENSITIVE and
3. TYPE_SCROLL_SENSITIVE
18. What are the types of statements in JDBC?
The JDBC API has 3 types of Statement Interfaces. They are:
1. Statement
2. PreparedStatement
3. CallableStatement
19. What are the differences/key features between the 3 different types of Statements in JDBC?
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.
20. 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.
21. What does the 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. Class.forName() loads the class if its not already loaded. It also executes the static block of loaded class. Then this method returns an instance of the loaded class. So a call to Class.forName('MyClass') is going to do following
- 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!");
}
}
Class.forName() loads driver class and executes the static block and the Driver registers itself with the DriverManager.
22. When to use a PreparedStatement and when to use a Statement?
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.
* In PreparedStatement the setDate()/setString() methods can be used to escape dates and strings properly, in a database-independent way.
* SQL injection attacks on a system are virtually impossible while using PreparedStatements.
23. What do you mean by the term pre-compiled from a PreparedStatemnet perspective?
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 which makes it much faster than executing a fresh query that has to be compiled before execution.
24. 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 manually as follows:
con.setAutoCommit(false);
Once auto-commit mode is disabled, no SQL statements will be committed until you explicitly call the commit method as follows:
con.commit();
Alternately, if you want to ensure that all further transactions/queries are auto-committed then you can enable auto-commit as follows:
con.setAutoCommit(true);
25. What are database warnings and How can I get them?
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.
The call to the getWarnings() method of either of these objects 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.
Questions – Part 2:
1. How will you retreive database warnings from a Connection object in JDBC?
//Retrieving warning from connection object
SQLWarning warning = conn.getWarnings();
//Retrieving next warning from warning object itself
SQLWarning nextWarning = warning.getNextWarning();
2. How will you retreive database warnings from a Statement object in JDBC?
//Retrieving warning from statement object
stmt.getWarnings();
//Retrieving next warning from warning object itself
SQLWarning nextWarning = warning.getNextWarning();
3. How will you retreive database warnings from a ResultSet object in JDBC?
//Retrieving warning from resultset object
rs.getWarnings();
//Retrieving next warning from warning object itself
SQLWarning nextWarning = warning.getNextWarning();
4. What does the clearWarnings() method do?
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.
5. What happens when I try to call the getWarning() method on a connection/statement/resultset after it has been closed?
Trying to call the getWarning() method on either of these 3 objects after they are closed will cause an SQLException to be thrown.
6. Let us say that I just closed my Statement object so, I cannot access the getWarning() on my statement. Can I still access the getWarning() on my ResultSet?
No. Closing a Statement automatically closes the ResultSet connected to it. So, you will get the same SQLException if you try to do so.
7. What is DatabaseMetaData?
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. Use DatabaseMetaData to find information about your database, such as its capabilities and structure.
8. How will you use the DatabaseMetaData? Can you write a sample example code?
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());
9. What is ResultSetMetaData?
JDBC API has 2 Metadata interfaces DatabaseMetaData & ResultSetMetaData. 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 ResultSetMetaData to find information about the results of an SQL query, such as size and types of columns.
10. How will you use the ResultSetMetaData? Can you write a sample example code?
ResultSet rs = stmt.executeQuery("SELECT * FROM TABLE_NAME");
ResultSetMetaData rsmd = rs.getMetaData();
int numberOfColumns = rsmd.getColumnCount();
boolean b = rsmd.isSearchable(1);
11. What is rowset?
A RowSet is an object that encapsulates a set of rows from either Java Database Connectivity (JDBC) result sets or tabular data sources like a file or spreadsheet.
12. Why do we need a RowSet?
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
13. What are the advantages of using RowSet over ResultSet?
There are two clear advantages of using RowSet over ResultSet:
* RowSet makes it possible to use the ResultSet object as a JavaBeans component.
* RowSet be used to make a ResultSet object scrollable and updatable. All RowSet objects are by default scrollable and updatable. If the driver and database being used do not support scrolling and/or updating of result sets, an application can populate a RowSet object implementation (e.g. JdbcRowSet) with the data of a ResultSet object and then operate on the RowSet object as if it were the ResultSet object.
14. What are the different types of RowSet ?
There are two types of RowSet are there. They are:
* Connected - A connected RowSet object connects to the database once and remains connected until the application terminates.
* Disconnected - A disconnected RowSet object connects to the database, executes a query to retrieve the data from the database and then closes the connection. A program may change the data in a disconnected RowSet while it is disconnected. Modified data can be updated in the database after a disconnected RowSet reestablishes the connection with the database.
15. Can you give an 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.
16. Can you give an 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. For example, a disconnected RowSet object can be serialized and passed over the wire to a thin client such as a personal digital assistant (PDA).
17. What are the benefits of having JdbcRowSet implementation?
The JdbcRowSet implementation is a wrapper around a ResultSet object that has following advantages over ResultSet
* This implementation makes it possible to use the ResultSet object as a JavaBeans component. A JdbcRowSet 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.
* It can be used to make a ResultSet object scrollable and updatable. All RowSet objects are by default scrollable and updatable. If the driver and database being used do not support scrolling and/or updating of result sets, an application can populate a JdbcRowSet object with the data of a ResultSet object and then operate on the JdbcRowSet object as if it were the ResultSet object.
18. What is the need of BatchUpdates feature in JDBC?
The BatchUpdates feature allows us to group SQL statements together and send to database server in one single shot instead of multiple calls.
19. What is a DataSource?
A DataSource object is the representation of a source of data in the Java programming language.
In basic terms:
* A DataSource is a facility for storing data.
* DataSource can be referenced by JNDI.
* Data Source may point to RDBMS, file System , any DBMS etc..
Typically in enterprise application perspective, the term DataSource can be used interchangeably with a RDBMS database because in almost all cases, our DataSource will be pointing to an RDBMS database.
20. What are the advantages of DataSource?
The few advantages of using data source are :
* An application does not need to hardcode driver information, as it does with the DriverManager.
* The DataSource implementations can easily change the properties of data sources. For example: There is no need to modify the application code when making changes to the database details.
* The DataSource facility allows developers to implement a DataSource class to take advantage of features like connection pooling and distributed transactions.
21. What is the difference between a Statement and a PreparedStatement?
Some of the main differences between a statement & PreparedStatement are:
A standard Statement is used to create a Java representation of a literal SQL statement and execute it on the database. A PreparedStatement is a precompiled statement. This means that when the PreparedStatement is executed, the RDBMS can just run the PreparedStatement SQL statement without having to compile it first.
Statement has to verify its metadata against the database every time. A prepared statement has to verify its metadata against the database only once.
If you want to execute the SQL statement once go for STATEMENT If you want to execute a single SQL statement multiple number of times, then go for PREPAREDSTATEMENT. PreparedStatement objects can be reused with passing different values to the queries.
22. What’s the difference between TYPE_SCROLL_INSENSITIVE and TYPE_SCROLL_SENSITIVE in ResultSets?
An insensitive resultset is like the snapshot of the data in the database when query was executed. A sensitive resultset does NOT represent a snapshot of data, rather it contains points to those rows which satisfy the query condition.
After we get the resultset the changes made to data are not visible through the resultset, and hence they are known as insensitive. After we obtain the resultset if the data is modified then such modifications are visible through resultset.
Performance not effected with insensitive. Since a trip is made for every ‘get’ operation, the performance drastically get affected.
If you have any questions that you want answer for, please leave a comment on this page OR drop a note to Snehal[at]TechProceed[dot]com and I will answer them.
Happy Learning!
Happy Learning!
No comments :
Post a Comment