Java / JDBC
1. What is JDBC?
Java Database Connectivity (JDBC) is a standard Java API for database-independent connectivity between Java and a wide range of databases.
2. Differences Between JDBC and ODBC.
JDBC is language and platform dependent. On the other hand, the ODBC is language and platform dependent.
3. 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....
4. 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....
5. Explain JDBC-ODBC bridge driver.
JDBC is the Java API for SQL databases: DriverManager or DataSource, Connection, Statement or PreparedStatement, and ResultSet.
6. 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...
7. 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...
8. 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...
9. 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....
10. 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)....
11. 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.
12. What is the role of DriverManager in JDBC?
DriverManager locates a registered Driver for a JDBC URL and opens a Connection. Prefer a DataSource in servers; DriverManager is the basic standalone entry point.
13. 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.
14. 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....
15. Difference between execute, executeQuery and executeUpdate in JDBC.
executeQuery is for SELECT and returns a ResultSet. executeUpdate is for DML/DDL and returns a count. execute handles either and reports which result it produced.
16. 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....
17. 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.
18. 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.
19. 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....
20. 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.
21. 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.
22. What are the types of JDBC statements?
There are 3 types. Statement, PreparedStatement, and CallableStatement.
23. Which JDBC interface is responsible for managing transactions?
The Connection interface manages the database transactions by providing commit, rollback methods.
24. 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.
25. 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.
26. Is the connection pool object thread safe?
Yes. The connection pool implementations are threadsafe.
27. 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 (?,?)}");
28. What is JDBC ResultSet?
JDBC is the Java API for SQL databases: DriverManager or DataSource, Connection, Statement or PreparedStatement, and ResultSet.
29. 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....
30. 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...
31. 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.
32. 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.
33. What is the return type of Class.forName method in Java?
Class.forName method returns java.lang.Class object.
34. 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.
35. 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.
36. 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....
37. 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.
38. 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.
39. 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.
40. 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.
41. 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....
42. 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...
43. 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...
44. 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....
45. Does the JDBC-ODBC Bridge support many concurrent open statements per connection?
No, it doesn't.
46. 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.
47. 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.
48. Types of RowSet in JDBC.
Java provides 5 types of RowSet. JdbcRowSet, JoinRowSet, CachedRowSet, FilteredRowSet and WebRowSet.
49. 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....
50. 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....
51. 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....
52. 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....
53. 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.
