Prev Next

Java / JDBC

1. What is JDBC? 2. Differences Between JDBC and ODBC. 3. What is JDBC driver? 4. Why thin driver is preferred for JDBC? 5. Explain JDBC-ODBC bridge driver. 6. Explain Native-API driver or Type 2 driver. 7. Explain network protocol driver in JDBC. 8. Which is the pure Java driver among the 4 types of JDBC Driver? 9. How does JDBC achieve loose coupling between Java program and JDBC drivers? 10. What are the steps involved in creating a JDBC connection? 11. What is a Driver in JDBC? 12. What is the role of DriverManager in JDBC? 13. How to get the database details in a Java program? 14. Explain JDBC statement. 15. Difference between execute, executeQuery and executeUpdate in JDBC. 16. Explain JDBC PreparedStatement. 17. Why JDBC PreparedStatement is preferred? 18. How do I set NULL as an input value for JDBC PreparedStatement? 19. Advantages of PreparedStatement over Statement in JDBC. 20. What are the steps to connect to the database in JDBC? 21. What are the JDBC API? 22. What are the types of JDBC statements? 23. Which JDBC interface is responsible for managing transactions? 24. How many layers are there in JDBC architecture? 25. What are the design patterns involved in JDBC architecture? 26. Is the connection pool object thread safe? 27. How do you execute stored procedures and functions using JDBC? 28. What is JDBC ResultSet? 29. Different types of ResultSet in JDBC. 30. What is row prefetching in JDBC? 31. Difference between TYPE_SCROLL_INSENSITIVE and TYPE_SCROLL_SENSITIVE ResultSet. 32. How to determine your data store or the JDBC driver supports different ResultSet types and concurrency? 33. What is the return type of Class.forName method in Java? 34. Does Oracle JDBC drivers support Java multithreading? 35. How do you create your own transactions in JDBC? 36. List few good practices in JDBC. 37. What are the isolation levels defined by JDBC? 38. Explain the role of JDBC Connection interface. 39. Explain the role of JDBC ResultSet interface. 40. How can we store images in the database using JDBC? 41. Explain JDBC batch processing and its benefits. 42. What does JDBC setMaxRows method do? 43. What does JDBC setFetchSize method do? 44. What are the locking systems in JDBC? 45. Does the JDBC-ODBC Bridge support many concurrent open statements per connection? 46. Difference between RowSet and ResultSet in JDBC. 47. Difference between connected and disconnected RowSet in JDBC. 48. Types of RowSet in JDBC. 49. Explain JdbcRowSet in JDBC. 50. What is SQLWarning In JDBC? 51. When does JDBC No suitable driver error occurs? 52. Explain steps involved in writing a Java program using JDBC. 53. Which data type do you prefer: byte array or a java.sql.Blob in JDBC?
Could not find what you were looking for? send us the question and we would be happy to answer your question.

What is JDBC?

Java Database Connectivity (JDBC) is a standard Java API for database-independent connectivity between Java and a wide range of databases.

Differences Between JDBC and ODBC.

JDBC is language and platform dependent. On the other hand, the ODBC is language and platform dependent.

ODBC(Open Database Connectivity) is complex and hard to learn. However, the code for JDBC is simpler.

What is JDBC driver?

JDBC Driver is a software component that enables Java application to interact with the database.

There are 4 types of driver.

  • JDBC-ODBC Bridge or Type 1,
  • Native API or Type 2 (partially implemented using Java),
  • Network protocol driver or Type 3 (complete Java),
  • Thin driver or Type 4 driver (complete Java).
Why thin driver is preferred for JDBC?

Thin driver directly converts the JDBC calls into vendor database specific protocols without any intermediate transformation. It is implemented using Java.

Advantages: faster performance compared to the other drivers.

Disadvantages: database specific and hence dependent on type of database.

Explain JDBC-ODBC bridge driver.

This driver converts the JDBC calls into ODBC function calls and utilizes an ODBC driver to connect to the database. This type of driver is not recommended.

The advantage is easy to use and connect to the database while the disadvantages are,

  • Degraded performance due to ODBC call conversion,
  • Requires ODBC driver to be installed on client machine.
Explain Native-API driver or Type 2 driver.

Native-API driver utilizes the client side native libraries to connect to the database by converting JDBC calls to native API calls. It is implemented partially using Java

The performance is better than the JDBC ODBC driver however the native libraries need to be installed on each client machine.

Explain network protocol driver in JDBC.

In type 3 driver, JDBC clients use standard network sockets to communicate with the application server and the server translates it to the call format supported by the database and sent to the database server.

The main advantage is no client libraries required to install on client machines and the driver is compatible with multiple database types.

Which is the pure Java driver among the 4 types of JDBC Driver?

Thin driver or Type 4 driver is the pure Java driver as it is completely implemented using Java.

JDBC is a Java module and having the driver implemented in Java makes it easier to invoke methods directly without any additional translations.

How does JDBC achieve loose coupling between Java program and JDBC drivers?

JDBC API enables loose coupling by using Java REfelection API. Class.forName() method is usually used to register the drivers that make sure application doesn't work directly with Drivers API. This also enables to change drivers easily when we migrate from one database to the other.

What are the steps involved in creating a JDBC connection?

Creating JDBC connection in a Java program involves 2 steps.

Load the JDBC Driver using Class.forName (String driverClass). Driver gets registered with the DriverManager and loads.

Invoke the getConnection() method of DriverManager to obtain a JDBC connection. getConnection also takes Database URL, user name and password as arguments.

What is a Driver in JDBC?

The driver is a contract between a Java program and the data store. It enables to write standardized code to query database and abstracts its implementation and how the call is physically made to the database.

What is the role of DriverManager in JDBC?

DriverManager is a factory class that is responsible for maintaining the single instance of each registered driver. DriverManager eliminates having multiple instances of the same driver.

The DriverManager acts as a registry and provides lookup mechanism by taking the connection URL and finding the suitable driver for it. When a driver understands the URL, DriverManager passes the URL to it to create the database connection.

How to get the database details in a Java program?

From the JDBC connection object, the class DatabaseMetaData that describes the database can be retrieved by calling getMetaData method.

Explain JDBC statement.

JDBC statements helps to issue sql queries to the database using JDBC connection. createStatement method from connection object can be called to create a JDBC statement. Once created the SQL query can be passed to it and executing using different methods such as execute, executeUpdate and executeQuery.

Difference between execute, executeQuery and executeUpdate in JDBC.

execute method can be used with any type of SQL statements and it returns a boolean. A true indicates that the execute method returned a result set object which can be retrieved using getResultSet method. false indicates that the query returned an int value or void. execute method can run both select and insert/update statements.

executeQuery method execute statements that returns a result set by fetching some data from the database. It executes only select statements.

executeUpdate method execute sql statements that insert/update/delete data at the database. This method return int value representing number of records affected; Returns 0 if the query returns nothing. The method accepts only non-select statements.

Explain JDBC PreparedStatement.

JDBC PreparedStatement object stores the precompiled SQL statement that can be used efficiently to execute the query multiple times.

The Prepared Statement may be parametrized. It also provides a bunch of setter methods (setInt, setString and so on) to set the IN parameters for the query and it is compatible with specified sql types as input parameter. As an example, for INTEGER sql datatype use setInt method.

PreparedStatement pstmt = con.prepareStatement("UPDATE DEPARTMENT
                                     SET EMP_COUNT = ? WHERE ID = ?");
   pstmt.setBigDecimal(1, 1500);
   pstmt.setInt(2, 100);

Why JDBC PreparedStatement is preferred?

PreparedStatement is precompiled so it eliminates the overhead of compiling again when the query is executed. PreparedStatement also automatically escapes special character and eliminates sql injection threat.

How do I set NULL as an input value for JDBC PreparedStatement?

Use the setNull method to bind null to the parameter. The setNull method accepts two parameter, index and the sql type as arguments.

PreparedStatement ps = conn.prepareStatement(sqlQuery);
ps.setNull(5, java.sql.Types.INTEGER);

Advantages of PreparedStatement over Statement in JDBC.

PreparedStatement prevents SQL injection threats as it automatically escapes the special characters.

Precompiles and caches the SQL statement for faster execution and to reuse the same SQL statement in batch.

PreparedStatement provides clear separation between the query code and the parameter values that improves readability.

PreparedStatement provides convenient way to transform Java object types to SQL Data types to pass input parameters.

What are the steps to connect to the database in JDBC?
  • Registering the JDBC driver class,
  • Creating connection using DriverManager,
  • Creating statementd,
  • Executing queries,
  • and closing connection.
What are the JDBC API?

The java.sql package has the JDBC API core interface and classes.

Core interfaces include Connection, Statement, DatabaseMetaData, PreparedStatement, CallableStatement, ResultSet, ResultSetMetaData and so on.

Core classes include DriverManager, Types, SQLException and many more.

What are the types of JDBC statements?

There are 3 types.

  • Statement,
  • PreparedStatement,
  • and CallableStatement.
Which JDBC interface is responsible for managing transactions?

The Connection interface manages the database transactions by providing commit, rollback methods.

How many layers are there in JDBC architecture?

There are 2 layers.

  • JDBC API that bridges application to JDBC Manager,
  • and JDBC Driver API that connects JDBC connection with the driver.
What are the design patterns involved in JDBC architecture?

The Complete JDBC architecture implements bridge design pattern, an abstract concept that decouples abstraction from implementation so that both can vary independently.

Other design patterns also involved in the JDBC API. DriverManager.getConnection Implements static factory method pattern, connection and statement implements Transactional pattern and proxy pattern, ResultSet iterator and data mapper pattern.

Is the connection pool object thread safe?

Yes. The connection pool implementations are threadsafe.

How do you execute stored procedures and functions using JDBC?

Using Callable statement interface, the stored procedure and the functions can be executed.

CallableStatement myStatement = conn.prepareCall ("{call myDbStoredProc (?,?)}");

What is JDBC ResultSet?

JDBC ResultSet object is similar to a table of data representing a database result set, which is generated by executing a statement that queries the database.

ResultSet object maintains a cursor that points to the current row, initially points to the first row. Calling next() on the result set moves the cursor to next row and returns false when the last row is already reached.

A ResultSet object is automatically closed when the Statement object that generated it is closed, re-executed.

A ResultSet is readable only and the cursor moves only forward by default. It is also possible to create scrollable and updateable ResultSet. We can use ResultSet getter methods with column name or index starting from 1 to retrieve the column data.

Different types of ResultSet in JDBC.

Based on the cursor scroll behavior, there are 3 types.

ResultSet.TYPE_FORWARD_ONLY, the default type where cursor can only move forward in the result set.

ResultSet.TYPE_SCROLL_INSENSITIVE cursor can move forward and backward, and the resultset is insensitive to changes made by others to the database after the result set was created.

ResultSet.TYPE_SCROLL_SENSITIVE, the cursor can move In both direction, and the result set is sensitive to changes made by others to the database after the result set has been created.

Based on the concurrency there are 2 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 row data.

What is row prefetching in JDBC?

To minimize the round trips to the database, pre fetching gets multiple rows of data each time data is fetched and the extra row data is stored in client-side buffers for later access by the client. The number of rows to prefetch can be set using setRowPrefetch method.

Difference between TYPE_SCROLL_INSENSITIVE and TYPE_SCROLL_SENSITIVE ResultSet.

The external changes made at the data store are not reflected while the result is open in TYPE_SCROLL_INSENSITIVE, where as TYPE_SCROLL_SENSITIVE does.

How to determine your data store or the JDBC driver supports different ResultSet types and concurrency?

Not all databases or JDBC Drivers support all ResultSet types or concurrency. DatabaseMetaData.supportsResultSetType checks if the specified ResultSet type supported and the DatabaseMetaData.supportsResultSetConcurrency finds if the specified concurrency level is supported.

What is the return type of Class.forName method in Java?

Class.forName method returns java.lang.Class object.

Does Oracle JDBC drivers support Java multithreading?

Yes. Multiple threads can share a connection object however the oracle JDBC driver API methods are synchronized, other threads need to hold while one thread is executing.

How do you create your own transactions in JDBC?

By invoking setAutoCommit method on a connection object with false as argument, we can manually handle transactions.

List few good practices in JDBC.

Implement connection pooling.

Reuse prepared statement for similar queries so that the DB will reuse the previous query plan for faster performance.

Always close the PreparedStatement when done. With Java 7, use try-with-resources for auto closing.

try (PreparedStatement pstmt = conn.prepareStatement(sqlSt);
      ResultSet r = pstmt.executeQuery();) {
    // do some logic with ResultSet 
}
What are the isolation levels defined by JDBC?

The isolation levels are,

  • TRANSACTION_NONE,
  • TRANSACTION_READ_COMMITTED,
  • TRANSACTION_READ_UNCOMMITTED,
  • TRANSACTION_REPEATABLE_READ,
  • TRANSACTION_SERIALIZABLE.
Explain the role of JDBC Connection interface.

The Connection interface maintains the session with the database and facilitates transaction management. It has factory methods that return the instances of Statement, PreparedStatement, CallableStatement, and DatabaseMetaData.

Explain the role of JDBC ResultSet interface.

The ResultSet object represents a row or record in a database table. It can be used to fetch and change the cursor pointer and retrieves the data from the database.

How can we store images in the database using JDBC?

Using PreparedStatement interface, we can store and retrieve images.

The setBinaryStream() method of PreparedStatement sets binary information into the parameterIndex.

PreparedStatement ps=con.prepareStatement("insert into images values(?,?)");  
ps.setInt(1,400); //Image index  
  
FileInputStream fis=new FileInputStream("d:\\myImage.jpg");  
ps.setBinaryStream(2,fis,fis.available());  
int i=ps.executeUpdate();  
Explain JDBC batch processing and its benefits.

JDBC API provides Batch Processing feature through which we can execute 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 as the number of database calls are less.

What does JDBC setMaxRows method do?

setMaxRows(int i) method limits the number of rows that the database returns from the query. The same can be done in SQL query itself, for example in MySQL, we can use the LIMIT clause to set the maximum number of rows returned by the query.

What does JDBC setFetchSize method do?

setFetchSize issues the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed for ResultSet objects generated by this Statement. If the value specified is zero, then the hint is ignored. The default value is zero.

What are the locking systems in JDBC?

There are 2 types of locking in JDBC by which we can handle multiple users trying to update the same record.

Optimistic locking locks the record only when update happens. Optimistic locking does not use exclusive locks when reading or selecting the record.

Pessimistic Locking locks the record as soon as it selects the row to update. This locking strategy guarantees the changes are made safely and consistently.

Does the JDBC-ODBC Bridge support many concurrent open statements per connection?

No, it doesn't.

Difference between RowSet and ResultSet in JDBC.

RowSet extends ResultSet and adds JDBC API support to the Java Bean component model. RowSet can be connected or disconnected.

Disconnected RowSet can be serialized while ResultSet cannot be serialized.

Difference between connected and disconnected RowSet in JDBC.

A connected RowSet always keeps connection with database, while a disconnected RowSet connects to database, get the data and then close the connection.

Disconnected RowSet are Serializable while connected are not.

Types of RowSet in JDBC.

Java provides 5 types of RowSet.

  • JdbcRowSet,
  • JoinRowSet,
  • CachedRowSet,
  • FilteredRowSet
  • and WebRowSet.

JdbcRowSet is connected RowSet while the rest is disconnected row sets.

Explain JdbcRowSet in JDBC.

The JdbcRowSet implementation is a wrapper for ResultSet object and has following advantages over ResultSet.

  • Uses ResultSet object as a JavaBeans component.
  • A JdbcRowSet can be used as a JavaBeans component, thus it can be created and configured at design time and executed at run time.
  • used to make a ResultSet object scrollable and updatable.

What is SQLWarning In JDBC?

SQLWarning is a subclass of SQLException that holds database access warnings. Warnings do not stop the execution of a specific application, as exceptions do.

A warning may be retrieved on the Connection object, the Statement object, PreparedStatement and CallableStatement objects, or on the ResultSet using getWarnings method.

SQLWarning warning = stmt.getWarnings();

   while (warning != null)
   {
       System.out.println("Message: " + warning.getMessage());
       System.out.println("SQLState: " + warning.getSQLState());
       System.out.println("Vendor error code: " + warning.getErrorCode());
       warning = warning.getNextWarning();
   }
When does JDBC No suitable driver error occurs?

"No suitable driver" may occur during a call to the DriverManager.getConnection method due to the following reasons:

  • Failed to load the appropriate JDBC drivers before calling the getConnection method.
  • Due to invalid JDBC URL, not being recognized by JDBC driver.
  • One or more the shared libraries needed by the bridge cannot be loaded.
Explain steps involved in writing a Java program using JDBC.
  • Load the DB specific JDBC driver that communicates with the database.
  • Open the connection to the database, for sending SQL statements and getting results.
  • Create JDBC Statement object containing SQL query.
  • Execute statement which returns the result set. ResultSet contains the tuples of DB table as a result of SQL query.
  • Process the result set.
  • Close the connection.
Which data type do you prefer: byte array or a java.sql.Blob in JDBC?

java.sql.Blob is used when extraction of the data is performed. Blob does not extract any data from the database until we trigger a query to the database.

byte array is used for writing/inserting data to the database.

«
»
JNDI

Comments & Discussions