JDBC Interview Questions and Answers

1) What is the JDBC?

JDBC stands for Java Database Connectivity. JDBC is a Java API that communicates with the database and execute SQLquery.

2) What is a JDBC driver and how many JDBC drivers are available?

JDBC driver contains classes and interfaces that help Java application and database.

There are 4 types of JDBC drivers.

1. Type 1 driver or JDBC-ODBC bridge driver.
2. Type 2 driver or Native-API, partly Java driver.
3. Type 3 driver or Network Protocol, pure Java driver.
4. Type 4 driver or Native-protocol, pure Java driver.

3) How can I connect MySQL or Oracle with Java?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44

//for MySQL { try {   Class.forName ("com.mysql.jdbc.Driver");   con=DriverManager.getConnection ("jdbc: mysql://localhost:3306/dbanme", "username", "password");   Statement st=con.createstatement ();   Resultset rs=st.executeQuery ("select * from user");   rs.next (); catch (ClassNotFoundException e) {   System.err.println ("ClassNotFoundException in get Connection," + e.getMessage()); }   catch (SQLException e) {   System.err.println ("SQLException in getConnection," + e.getMessage ()) ;}   //for oracle   {   try   {   Class.forName ("oracle.jdbc.driver.OracleDriver");   con= DriverManager.getConnection ("jdbc:oracle:thin:@localhost:1521:dbname", "username", "password");   Statement st=con.createstatement();   Resultset rs=st.executeQuery("select * from user");   Rs.next();   } catch (ClassNotFoundException e) {   System.err.println("ClassNotFoundException in get Connection," +e.getMessage()); }   catch (SQLException e) {   System.err.println("SQLException in getConnection, " + e.getMessage()); } return con; }

Code explanation

Class.forName creates an instance of JDBC driver and register with DriverManager.

getConnection () method always establishes a connection to a database.

We need to create a Statement object from above connection object. The statement will return resultset object.  ResultSet.next () means If the result set is still returning row.

4) Which JDBC driver is the fastest driver?

Type 4 driver or Native-protocol, pure Java driver, is the fastest driver.

5) What are the JDBC API components?

There are four types of components

1. JDBC API
2. JDBC Driver Manager
3. JDBC Test Suite
4. JDBC-ODBC Bridge

6) What are the JDBC statements?

There are 3 types of JDBC Statements, as given below:

1. Statement:  It will execute SQL query (static SQL query) against the database.
2. Prepared Statement:  Used when we want to execute SQL statement repeatedly.  Input data is dynamic and  taken input at the run time.
3. Callable Statement: Used when we want to execute stored procedures.

7) How can we execute stored procedures?

Stored procedures can be executed using JDBCcallable statement. Here is the code.

1 2 3 4 5

Connection conn = null;   CallableStatement callStmt = conn.prepareCall("{call myStoreproc(?, ?)}");   callStmt.setString(1, "abcdefg");

8) What are the advantages of using PreparedStatement in Java?

Prepared Statement is used to execute same SQL statements repeatedly. The prepared statement is compiled only once even though it used “n” number of times

9) What is ResultSet?

The java.sql.ResultSet interface means the result set of a SQL query. It means a cursor is pointing a row of a table; it points to before the first row.

10) What are types of ResultSet?

There are three types of ResultSet is available. If we do not declare any ResultSet that means we are calling TYPE_FORWARD_ONLY

1. TYPE_FORWARD_ONLY: cursor can move only forward.
2. TYPE_SCROLL_INSENSITIVE: cursor can move forward and backward but not sensitive.
3. TYPE_SCROLL_SENSITIVE: cursor can move forward and backward, but it is sensitive

11) Explain the difference between RowSet vs. ResultSet in JDBC?

In a ResultSet handle connection to a DB, we cannot make Result as a serialized object.

Because of above issue, we cannot pass  Resultset across the network.

RowSet extends the ResultSet interface, so it holds all methods from ResultSet. RowSet is serialized.

So, we can pass Rowset from one class to another class because it has no connection with the database.

12) Why would you use setAutoCommit(false) in JDBC?

If you want to turn Off the Auto Commit then set connection.setAutoCommit(false)

13) What are database warnings in JDBC and how can we handle database warnings in JDBC?

SQL warning or Database warning is the subclass of SQLException class. We can handle it by using getWarnings() method on Connection, Statement, and ResultSet

14) Can I get a null ResultSet?

No, we cannot get null Resultset. ResultSet.next() can return null if the next record does not contain a row.

15) What do you mean by Metadata and why we are using it?

Metadata means data or information about other data. We use metadata to get database product version, driver name, the total number of tables and views.

16) What is the difference between executing, executeQuery, executeUpdate in JDBC?

execute(): it can be used for any kind of SQL Query.

executeQuery() : it can be used for select query.

executeUpdate(): it can be used to change/update table.

17) What is database connection pooling? Advantages of using a connection pool?

Connection pooling means connections will be stored in the cache and we can reuse them in future.

Advantage:

1.  It is faster
2.  Connection pooling becomes easier to diagnose and analyze database connection.

18) What is the function of DriverManager class?

It is an interface between user and drivers. DriverManager tracks all the activity between a database and the appropriate driver.

19) What is the meaning of batch updates?

Batch updates means executing a set/group of SQL queries all at once.

Batch updates can be used only for insert, update and delete but not for select query.

20) How many packages are available in JDBC API?

Two types of packages are available in JDBC API

1. java.sql
2. javax.sql

21) What is the return type of execute, executeQuery and executeUpdate?

Return type of execute is Boolean

Return type of executeQuery is ResultSet object

Return type of executeUpdate is int

22) Result Set’s index Starts with 0 or 1?

Result Set’s index starts with 1.

23) What is the role of Class.forName while loading drivers?

Class.forName creates an instance of JDBC driver and register with DriverManager.

24) JDBC-ODBC Bridge is multi-threaded or not?

No, JDBC-ODBC Bridge uses synchronized methods to serialize all the calls made to ODBC.

25) Which interface handles transaction management in JDBC?

Connection interface handles transaction management in JDBC. It provides method for commit (), rollback () etc.

26) Why “No suitable driver” error occurs?

No suitable driver” occurs when we are calling DriverManager.getConnection method,

it may occur because of following reasons:

• unable to load exact JDBC drivers before calling the getConnection method.
• It may be invalid or wrong JDBC URL.

27) Prepared Statements are faster. Why?

Prepared statement execution is faster than direct execution because the statement is compiled only once. Prepared statements & JDBC driver are connected with each other during execution, and there are no connection overheads.

28) Is it possible to connect to multiple databases? Using single statement can we update or extract data from two or three or many databases?

Yes, it is possible to connect to multiple databases, at the same time, but it depends on the specific driver.

To update and extract data from the different database we can use the single statement. But we need middleware to deal with multiple databases or a single database.

29) Tell me difference between setMaxRows(int) and SetFetchSize(int)?

setMaxRows(int)	SetFetchSize(int)?
Defines how many rows a resultset can contain at a time	Defines the number of rows that will be read from the database.

30) Tell me about special characters?

A special character is preceded by an escape character. Example –

SELECT NAME FROM TABLE WHERE NAME LIKE ‘\_%’ {escape ‘\’}

31) What is the meaning of “dirty read” in the database?

Dirty read means “read the value which may be correct or may not be correct.”

32) What do you mean by two phase commits?

Two phase commit is used in distributed transaction process. If any transaction is executing and it will affect multiple databases. Two phase commits will be used to make all databases synchronized with each other.

33) How many locking systems are there in JDBC?

Two types of locking are available in JDBC by which we can handle more than one user.

If two users are viewing the same record, then no locking is done. If one user is updating a record and the second user is also updating the same record.  At that time, we are going to use locking.

1. Optimistic Locking: it will lock the record only when we are going to “update.”
2. Pessimistic Locking: it will lock the record from the “select” to view, update and commit time.

34) What are the exceptions in JDBC?

There are four types of exceptions in JDBC.

1. batchUpdate Exception
2. Data Truncation
3. SQL Exception
4. SQL Warning

35) Give steps to connect to the DB using JDBC?

There are two ways to connecting database using JDBC

1. Using DriverManager:

It will load the driver class with the help of class.forName(driver class) and Class.forName().

Post Loading it will pass the control to DriverManager.

DriverManager.getConnection() will create the connection to access the database.

2. Using DataSource:

For DataSource, no need to use DriverManager with the help of JNDI.  It will lookup the DataSource from Naming service server. DataSource.getConnection() method will return Connection object to the DB.

36) Explain the JDBC Architecture?

JDBC API supports both 2-tier and 3-tier models for the database.

In 2-tier model Java application interacts to the data source

In 3-tier model commands are redirected to a “middle tier” of services. After that commands sent to the data source.

37) What are the new features available in JDBC 4.0?

The new features are

• Auto loading by JDBC driver class
• Enhanced Connection Management
• RowId SQL enabled
• Dataset implemented by SQL by using Annotations
• Enhancements of SQL exception handling
• Supporting SQL XML files

38) What are the packages are used in JDBC?

8 packages are used in JDBC –

1. sql.Driver
2. Connection
3. Statement
4. PreparedStatement
5. CallableStatement
6. ResultSet
7. ResultSetMetaData
8. DatabaseMetaData

39) How many RowSet are available in JDBC?

There are two types of row sets are available:

1. Connected – A connected RowSet object connects to the database instantaneously. If the application terminates then connected RowSet object also terminates.
2. Disconnected – A disconnected RowSet object connects to the database after execution of the required query.

40) What is the meaning of connection?

Connection interface consists of methods for interaction with the database.

41) Explain JDBC Savepoint?

A savepoint represents a point that the current transaction can roll back to. Instead of rolling all its changes back, it can choose to roll back only some of them.

42) List the advantages of using DataSource?

Data source is dividing work among administrator and programmer/developer.

The administrator creates a DataSource object and ties up it with JNDI registry. A programmer/ developer retrieves the DataSource object from the registry.  Then it will establish the connection with the database.

43) What is the reason why we need a JdbcRowSet like the wrapper around ResultSet?

We can use ResultSet object as a JavaBeans component.

• A JdbcRowSet also can be used as a JavaBeans component. That’s why it can be created and configured at design or compile time and executed at run time.
• All jdbcRowSet objects are scrollable and updatable.

44) How many ways that we can view a result set?

There are 2 ways to view ResultSet

1. column
2. column index.

Example: getInt(String columnName), getInt(int columnIndex)

45) How many ways can you update a result set?

Following methods helps you to update result set

• updateRow()
• deleteRow()
• refreshRow()
• cancelRowUpdates()
• insertRow()

46) Why should we close database connections in Java?

As a best practice, we must close the resultset, the statement and the connection. If the connection is coming from a pool, on closure, the connection is sent back to the pool for reuse. We are doing this in the finally{} block, because if any exception occurs, then we still get the chance to close this.

47) Why are we using blob datatypes in JDBC?

These are used to store a large amount of data into the database like images, movie, etc.

48) How to set the attribute Concurrency in ResultSet?

There are two concurrency levels

1. CONCUR_READ_ONLY – It is only for reading.
2. CONCUR_UPDATABLE − It is for both read and updated.

49) What is the difference between client and server database cursors?

Server side cursor means data & results are saved on the server. Only when requested data is sent to the client.

Client side cursor means all data sent to the client location.

50) How do you insert images into Database using JDBC?

Images in the database using the BLOB datatype wherein the image stored as a byte stream. Below code is showing how to insert the image into DB.

Java

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

Connection con = null;   PreparedStatement prs = null;   InputStream inputstrm = null;   Class.forName("oracle.jdbc.driver.OracleDriver");   con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:mydb","username","yourpassword");   //localhost is your machine or hostname   //1521 is your oracle database port number   //mydb is your oracle database name.   prs = con.prepareCall("insert into emp_profile values (?,?)");   prs.setInt(1, 1223);   inputstrm = new FileInputStream(new File("emp_img.jpg"));   prs.setBinaryStream(2, inputstrm);   int count = prs.executeUpdate();