Java Database Connectivity API contains commonly asked Java interview questions. A good understanding of JDBC API is required to understand and leverage many powerful features of Java technology. Here are few important practical questions and answers which can be asked in a Core Java JDBC interview.
These are the most commonly asked questions:-
1) what is Index and difference with cluster index?
Non-clusteredThe data is present in arbitrary order, but the logical ordering is specified by the index. The data rows may be spread throughout the table regardless of the value of the indexed column or expression. The non-clustered index tree contains the index keys in sorted order, with the leaf level of the index containing the pointer to the record (page and the row number in the data page in page-organized engines; row offset in file-organized engines).
In a non-clustered index:
- The physical order of the rows is not the same as the index order.
- Typically created on non-primary key columns used in JOIN, WHERE, and ORDER BY clauses.
ClusteredClustering alters the data block into a certain distinct order to match the index, resulting in the row data being stored in order. Therefore, only one clustered index can be created on a given database table. Clustered indices can greatly increase overall speed of retrieval, but usually only where the data is accessed sequentially in the same or reverse order of the clustered index, or when a range of items is selected.
Since the physical records are in this sort order on disk, the next row item in the sequence is immediately before or after the last one, and so fewer data block reads are required. The primary feature of a clustered index is therefore the ordering of the physical data rows in accordance with the index blocks that point to them. Some databases separate the data and index blocks into separate files, others put two completely different data blocks within the same physical file(s). Create an object where the physical order of rows is the same as the index order of the rows and the bottom (leaf) level of clustered index contains the actual data rows.
Good read on index : http://www.orafaq.com/node/1403
2) DB query performance and optimization?
Among the various ways to tune the performance of a query are:
- Rewriting the query. There is no one way to write a query. If you find that a query with an OUTER JOIN is taking a long time, rewrite it using an INNER JOIN. Or perhaps the WHERE clause is written in such a way that it is causing excessive database read operations.
- Normalizing or de-normalizing tables. While normalizing tables results in ideal database design, it often leads to poor querying. If you find that frequently-used queries have to span too many normalized tables, consider some duplication of data. For example, in the Northwind database, the Unit Cost column is present in both the Order Details and Products table.
- Adding indexes. Lack of well-defined indexes is a frequent cause of queries taking a long time to execute. Creating indexes will cause a query to lookup the index rather than the table.
- Removing indexes. In some cases, it is a poorly defined index that is the cause of slow query execution. For example, an index which does not include the frequently looked up columns in a table. In such a case, it is better to drop the index and recreate it.
3) Benefits of prepared statement?
DB execution happens in this steps
1.Parse the incoming SQL query
2. Compile the SQL query
3. Plan/optimize the data acquisition path
4. Execute the optimized query / acquire and return data
- statement perform step 1-4 to do the each task and prepared statement will pre execute the 1-3 and perform only 4 for each task.
- prepared statements are safe as they safeguard against sql injection on using direct query.
- We can bind non standard java objects like blob etc in prepared statement, but not in statement.
4) DB execute plan and how to use it?
5) Foreign key and primary key difference?
6) Materialized view in database?
7) Truncate and delete difference?
8) How to design table?
9) Database transaction management?
10) what is cursor and trigger in dB?
These are very standard API question in interview:
What are available drivers in JDBC
- A JDBC-ODBC bridge provides JDBC API access via one or more ODBC drivers. Note that some ODBC native code and in many cases native database client code must be loaded on each client machine that uses this type of driver. Hence, this kind of driver is generally most appropriate when automatic installation and downloading of a Java technology application is not important. For information on the JDBC-ODBC bridge driver provided by Sun, see JDBC-ODBC Bridge Driver.
- A native-API partly Java technology-enabled driver converts JDBC calls into calls on the client API for Oracle, Sybase, Informix, DB2, or other DBMS. Note that, like the bridge driver, this style of driver requires that some binary code be loaded on each client machine.
- A net-protocol fully Java technology-enabled driver translates JDBC API calls into a DBMS-independent net protocol which is then translated to a DBMS protocol by a server. This net server middleware is able to connect all of its Java technology-based clients to many different databases. The specific protocol used depends on the vendor. In general, this is the most flexible JDBC API alternative. It is likely that all vendors of this solution will provide products suitable for Intranet use. In order for these products to also support Internet access they must handle the additional requirements for security, access through firewalls, etc., that the Web imposes. Several vendors are adding JDBC technology-based drivers to their existing database middleware products.
- A native-protocol fully Java technology-enabled driver converts JDBC technology calls into the network protocol used by DBMSs directly. This allows a direct call from the client machine to the DBMS server and is a practical solution for Intranet access. Since many of these protocols are proprietary the database vendors themselves will be the primary source for this style of driver. Several database vendors have these in progress.
What is a stored procedure? How to call stored procedure using JDBC API?
How to do database connection using JDBC thin driver ?
What does 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
Which one will you use Statement or PreparedStatement? Or Which one to use when (Statement/PreparedStatement)? Compare PreparedStatement vs 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
What does setAutoCommit(false) do?
Warnings may be retrieved from Connection, Statement, and ResultSet objects.
Handling SQLWarning from connection object
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.
Trying to call getWarning() on a connection after it has been closed will cause an SQLException to be thrown. Similarly, trying to retrieve a warning on a statement after it has been closed or on a result set after it has been closed will cause an SQLException to be thrown. Note that closing a statement also closes a result set that it might have produced.
What is Metadata and why should I use it?
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. Below is a sample code which demonstrates how we can use the DatabaseMetaData
What is RowSet? or What is the difference between RowSet and ResultSet? or Why do we need RowSet? or What are the advantages of using RowSet over ResultSet?
Rowsets are easy to use since the RowSet interface extends the standard java.sql.ResultSet interface so it has all the methods of ResultSet. There are two clear advantages of using RowSet over ResultSet
What is a connected RowSet? or What is the difference between connected RowSet and disconnected RowSet? or Connected vs Disconnected RowSet, which one should I use and when?
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.
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).
Table creation script:
( ID NUMBER(10, 0) NOT NULL,
FIRST_NAME VARCHAR2(20) NOT NULL,
SALARY NUMBER NOT NULL,
DEPTNO NUMBER NOT NULL
, CONSTRAINT EMPLOYEE_PK PRIMARY KEY
ENABLE ) ;
CREATE TABLE DEPARTMENT
DEPT_ID NUMBER NOT NULL,
, CONSTRAINT DEPARTMENT_PK PRIMARY KEY
Function and store procedure code
CREATE OR REPLACE PROCEDURE raise_sal(i_empno NUMBER, i_newsal NUMBER)
UPDATE employee SET salary = i_newsal WHERE id = i_empno;
SELECT salary INTO sal FROM employee
WHERE id = inID;