Java Database Connectivity (JDBC) is an application programming interface (API) for the programming language Java, which defines how a client may access a database. It is a Java-based data access technology used for Java database connectivity. It is part of the Java Standard Edition platform, from Oracle Corporation.
1. What is JDBC API and when do we use it?
Java DataBase Connectivity API allows us to work with relational databases. JDBC API interfaces and classes are part of java.sql and javax.sql package. We can use JDBC API to get the database connection, run SQL queries and stored procedures in the database server and process the results. JDBC API is written in a way to allow loose coupling between our Java program and actual JDBC drivers that make our life easier in switching from one database to another database servers easily.
2. What are different types of JDBC Drivers?
There are four types of JDBC drivers. Any java program that works with database has two parts, first part is the JDBC API and second part is the driver that does the actual work.
JDBC-ODBC Bridge plus ODBC Driver (Type 1): It uses ODBC driver to connect to database. We should have ODBC drivers installed to connect to database, that’s why this driver is almost obsolete.
Native API partly Java technology-enabled driver (Type 2): This driver converts JDBC class to the client API for the database servers. We should have database client API installed. Because of extra dependency on database client API drivers, this is also not preferred driver.
Pure Java Driver for Database Middleware (Type 3): This driver sends the JDBC calls to a middleware server that can connect to different type of databases. We should have a middleware server installed to work with this driver. This adds to extra network calls and slow performance and thats why not widely used JDBC driver.
Direct-to-Database Pure Java Driver (Type 4): This driver converts the JDBC calls to the network protocol understood by the database server. This solution is simple and suitable for database connectivity over the network. However for this solution, we should use database specific drivers, for example OJDBC jars by Oracle for Oracle DB and MySQL Connector/J for MySQL databases.
3. How does JDBC API helps us in achieving loose coupling between Java Program and JDBC Drivers API?
JDBC API uses Java Reflection API to achieve loose coupling between java programs and JDBC Drivers. If you look at a simple JDBC example, you will notice that all the programming is done in terms of JDBC API and Driver comes in picture only when it’s loaded through reflection using Class.forName() method.
4. How to get the Database server details in java program?
We can use DatabaseMetaData object to get the database server details. When the database connection is created successfully, we can get the meta data object by calling getMetaData() method. There are so many methods in DatabaseMetaData that we can use to get the database product name, it’s version and configuration details.
DatabaseMetaData metaData = con.getMetaData();
String dbProduct = metaData.getDatabaseProductName();5. What is JDBC Statement?
JDBC API Statement is used to execute SQL queries in the database. We can create the Statement object by calling Connection createStatement() method. We can use Statement to execute static SQL queries by passing query through different execute methods such as execute(), executeQuery(), executeUpdate() etc.
6. What is JDBC PreparedStatement?
JDBC PreparedStatement object represents a precompiled SQL statement. We can use it’s setter method to set the variables for the query. Since PreparedStatement is precompiled, it can then be used to efficiently execute this statement multiple times. PreparedStatement is better choice that Statement because it automatically escapes the special characters and avoid SQL injection attacks.
7. What is the use of getGeneratedKeys() method in Statement?
Sometimes a table can have auto generated keys used to insert the unique column value for primary key. We can use Statement getGeneratedKeys() method to get the value of this auto generated key.
8. What are the Advantages and Disadvantages of PreparedStatement over Statement?
Some of the benefits of PreparedStatement over Statement are:
PreparedStatement helps us in preventing SQL injection attacks because it automatically escapes the special characters.
PreparedStatement allows us to execute dynamic queries with parameter inputs.
PreparedStatement is faster than Statement. It becomes more visible when we reuse the PreparedStatement or use it’s batch processing methods for executing multiple queries.
PreparedStatement helps us in writing object Oriented code with setter methods whereas with Statement we have to use String Concatenation to create the query. If there are multiple parameters to set, writing Query using String concatenation looks very ugly and error prone.
Some of the alternative approaches to use PreparedStatement with IN clause are;
Execute Single Queries – very slow performance and not recommended
Using Stored Procedure – Database specific and hence not suitable for multiple database applications.
Creating PreparedStatement Query dynamically – Good approach but looses the benefit of cached PreparedStatement.
Using NULL in PreparedStatement Query – A good approach when you know the maximum number of variables inputs, can be extended to allow unlimited parameters by executing in parts.
9. What is JDBC Batch Processing and what are it’s benefits?
Sometimes we need to run bulk queries of a similar kind for a database. For example, loading data from CSV files to relational database tables. As we know that we have the option to use Statement or PreparedStatement to execute queries. Apart from that JDBC API provides Batch Processing feature through which we can execute the bulk of queries in one go for a database.
JDBC API supports batch processing through Statement and PreparedStatement addBatch() and executeBatch() methods.
Batch Processing is faster than executing one statement at a time because the number of database calls is less.
10. How to rollback a JDBC transaction?
We can use Connection object rollback() method to rollback the transaction. It will rollback all the changes made by the transaction and release any database locks currently held by this Connection object.
11. What is JDBC DataSource and what are it’s benefits?
JDBC DataSource is the interface defined in javax.sql package and it is more powerful that DriverManager for database connections. We can use DataSource to create the database connection and Driver implementation classes does the actual work for getting connection. Apart from getting Database connection, DataSource provides some additional features such as:
Caching of PreparedStatement for faster processing
Connection timeout settings
Logging features
ResultSet maximum size threshold
Connection Pooling in servlet container using JNDI support
12. What is the different between ResultSet and RowSet?
RowSet objects are derived from ResultSet, so they have all the features of ResultSet with some additional features. One of the huge benefit of RowSet is that they can be disconnected and that makes it lightweight and easy to transfer over a network.
Whether to use ResultSet or RowSet depends on your requirements but if you are planning to use ResultSet for longer duration, then a disconnected RowSet is better choice to free database resources.
13. What is JDBC RowSet? What are different types of RowSet?
JDBC RowSet holds tabular data in more flexible ways that ResultSet. All RowSet objects are derived from ResultSet, so they have all the capabilities of ResultSet with some additional features. RowSet interface is defined in javax.sql package.
Some additional features provided by RowSet are:
Functions as Java Beans with properties and their getter-setter methods. RowSet uses JavaBeans event model and they can send notifications to any registered component for events such as cursor movement, update/insert/delete of a row and change to RowSet contents.
RowSet objects are scrollable and updatable by default, so if DBMS doesn’t support scrollable or updatable ResultSet, we can use RowSet to get these features.
RowSet are broadly divided into two types:
Connected RowSet Objects – These objects are connected to database and are most similar to ResultSet object. JDBC API provides only one connected RowSet object javax.sql.rowset.JdbcRowSet and it’s standard implementation class is com.sun.rowset.JdbcRowSetImpl
Disconnected RowSet Objects – These RowSet objects are not required to connected to a database, so they are more lightweight and serializable. They are suitable for sending data over a network. There are four types of disconnected RowSet implementations.
CachedRowSet – They can get the connection and execute a query and read the ResultSet data to populate the RowSet data. We can manipulate and update data while it is disconnected and reconnect to database and write the changes.
WebRowSet derived from CachedRowSet – They can read and write XML document.
JoinRowSet derived from WebRowSet – They can form SQL JOIN without having to connect to a data source.
FilteredRowSet derived from WebRowSet – We can apply filtering criteria so that only selected data is visible.
14. . What are common JDBC Exceptions?
Some of the common JDBC Exceptions are:
java.sql.SQLException – This is the base exception class for JDBC exceptions.
java.sql.BatchUpdateException – This exception is thrown when Batch operation fails, but it depends on the JDBC driver whether they throw this exception or the base SQLException.
java.sql.SQLWarning – For warning messages in SQL operations.
java.sql.DataTruncation – when a data values is unexpectedly truncated for reasons other than its having exceeded MaxFieldSize.
15. What is JDBC Savepoint? How to use it?
Sometimes a transaction can be group of multiple statements and we would like to rollback to a particular point in the transaction. JDBC Savepoint helps us in creating checkpoints in a transaction and we can rollback to that particular checkpoint.
Any savepoint created for a transaction is automatically released and become invalid when the transaction is committed, or when the entire transaction is rolled back. Rolling a transaction back to a savepoint automatically releases and makes invalid any other savepoints that were created after the savepoint in question.
16. What is JDBC ResultSet?
JDBC ResultSet is like a table of data representing a database result set, which is usually generated by executing a statement that queries the database.
ResultSet object maintains a cursor pointing to its current row of data. Initially, the cursor is positioned before the first row. The next() method moves the cursor to the next row. If there are no more rows, next() method returns false and it can be used in a while loop to iterate through the result set.
A default ResultSet object is not updatable and has a cursor that moves forward only. Thus, you can iterate through it only once and only from the first row to the last row. It is possible to produce ResultSet objects that are scrollable and/or updatable using below syntax.
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); A ResultSet object is automatically closed when the Statement object that generated it is closed, re-executed, or used to retrieve the next result from a sequence of multiple results.
We can use ResultSet getter method with column name or index number starting from 1 to retrieve the column data.
17. What are different types of ResultSet?
There are different types of ResultSet objects that we can get based on the user input while creating the Statement. If you will look into the Connection methods, you will see that createStatement() and prepareStatement() method are overloaded to provide ResultSet type and concurrency as input argument.
There are three types of ResultSet object.
ResultSet.TYPE_FORWARD_ONLY: This is the default type and cursor can only move forward in the result set.
ResultSet.TYPE_SCROLL_INSENSITIVE: The cursor can move forward and backward, and the result set is not sensitive to changes made by others to the database after the result set was created.
ResultSet.TYPE_SCROLL_SENSITIVE: The cursor can move forward and backward, and the result set is sensitive to changes made by others to the database after the result set was created.
Based on the concurrency there are two types of ResultSet object.
ResultSet.CONCUR_READ_ONLY: The result set is read only, this is the default concurrency type.
ResultSet.CONCUR_UPDATABLE: We can use ResultSet update method to update the rows data.
18. What is Apache DBCP API?
If you use DataSource to get the Database connection, usually the code to get the connection is tightly coupled with the Driver specific DataSource implementation. Also most of the code is boiler-plate code except the choice of the DataSource implementation class.
Apache DBCP helps us in getting rid of these issues by providing DataSource implementation that works as an abstraction layer between our program and different JDBC drivers. Apache DBCP library depends on Commons Pool library, so make sure they both are in the build path.
19. What is difference between java.util.Date and java.sql.Date?
java.util.Date contains information about the date and time whereas java.sql.Date contains information only about the date, it doesn’t have time information. So if you have to keep time information in the database, it is advisable to use Timestamp or DateTime fields.
20. What is the use of JDBC DriverManager class?
JDBC DriverManager is the factory class through which we get the Database Connection object. When we load the JDBC Driver class, it registers itself to the DriverManager, you can look up the JDBC Driver classes source code to check this.
Then when we call DriverManager.getConnection() method by passing the database configuration details, DriverManager uses the registered drivers to get the Connection and return it to the caller program.
The Tech Platform