JDBC Interview Questions and Answers
Question - 61 : - How Can I Investigate The Physical Structure Of A Database?
Answer - 61 : -
The JDBC view of a database internal structure can be seen in the image below.
Several database objects (tables, views, procedures etc.) are contained within a Schema.
Several schema (user namespaces) are contained within a catalog.
Several catalogs (database partitions; databases) are contained within a DB server (such as Oracle, MS SQL.
The DatabaseMetaData interface has methods for discovering all the Catalogs, Schemas, Tables and Stored Procedures in the database server. The methods are pretty intuitive, returning a ResultSet with a single String column; use them as indicated in the code below:
public static void main(String[] args) throws Exception
{
// Load the database driver - in this case, we
// use the Jdbc/Odbc bridge driver.
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
// Open a connection to the database
Connection conn = DriverManager.getConnection("[jdbcURL]", "[login]", "[passwd]");
// Get DatabaseMetaData
DatabaseMetaData dbmd = conn.getMetaData();
// Get all Catalogs
System.out.println("nCatalogs are called '" + dbmd.getCatalogTerm()
+ "' in this RDBMS.");
processResultSet(dbmd.getCatalogTerm(), dbmd.getCatalogs());
// Get all Schemas
System.out.println("nSchemas are called '" + dbmd.getSchemaTerm()
+ "' in this RDBMS.");
processResultSet(dbmd.getSchemaTerm(), dbmd.getSchemas());
// Get all Table-like types
System.out.println("nAll table types supported in this RDBMS:");
processResultSet("Table type", dbmd.getTableTypes());
// Close the Connection
conn.close();
}
public static void processResultSet(String preamble, ResultSet rs)
throws SQLException
{
// Printout table data
while(rs.next())
{
// Printout
System.out.println(preamble + ": " + rs.getString(1));
}
// Close database resources
rs.close();
}
Question - 62 : - How Do I Find All Database Stored Procedures In A Database?
Answer - 62 : -
Use the getProcedures method of interface java.sql.DatabaseMetaData to probe the database for stored procedures. The exact usage is described in the code below.
public static void main(String[] args) throws Exception
{
// Load the database driver - in this case, we
// use the Jdbc/Odbc bridge driver.
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
// Open a connection to the database
Connection conn = DriverManager.getConnection("[jdbcURL]", "[login]", "[passwd]");
// Get DatabaseMetaData
DatabaseMetaData dbmd = conn.getMetaData();
// Get all procedures.
System.out.println("Procedures are called '" + dbmd.getProcedureTerm() +"' in the DBMS.");
ResultSet rs = dbmd.getProcedures(null, null, "%");
// Printout table data
while(rs.next())
{
// Get procedure metadata
String dbProcedureCatalog = rs.getString(1);
String dbProcedureSchema = rs.getString(2);
String dbProcedureName = rs.getString(3);
String dbProcedureRemarks = rs.getString(7);
short dbProcedureType = rs.getShort(8);
// Make result readable for humans
String procReturn = (dbProcedureType == DatabaseMetaData.procedureNoResult ? "No Result" : "Result");
// Printout
System.out.println("Procedure: " + dbProcedureName + ", returns: " + procReturn);
System.out.println(" [Catalog | Schema]: [" + dbProcedureCatalog + " | " + dbProcedureSchema + "]");
System.out.println(" Comments: " + dbProcedureRemarks);
}
// Close database resources
rs.close();
conn.close();
}
Question - 63 : - How Do I Check What Table Types Exist In A Database?
Answer - 63 : -
Use the getTableTypes method of interface java.sql.DatabaseMetaData to probe the database for table types. The exact usage is described in the code below.
public static void main(String[] args) throws Exception
{
// Load the database driver - in this case, we
// use the Jdbc/Odbc bridge driver.
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
// Open a connection to the database
Connection conn = DriverManager.getConnection("[jdbcURL]", "[login]", "[passwd]");
// Get DatabaseMetaData
DatabaseMetaData dbmd = conn.getMetaData();
// Get all table types.
ResultSet rs = dbmd.getTableTypes();
// Printout table data
while(rs.next())
{
// Printout
System.out.println("Type: " + rs.getString(1));
}
// Close database resources
rs.close();
conn.close();
}
Question - 64 : - How Do I Extract A Blob From A Database?
Answer - 64 : -
A BLOB (Binary Large OBject) is essentially an array of bytes (byte[]), stored in the database. You extract the data in two steps:
Call the getBlob method of the Statement class to retrieve a java.sql.Blob object.
Call either getBinaryStream or getBytes in the extracted Blob object to retrieve the java byte[] which is the Blob object.
Note that a Blob is essentially a pointer to a byte array (called LOCATOR in database-talk), so the java.sql.Blob object essentially wraps a byte pointer. Thus, you must extract all data from the database blob before calling commit or
private void runGetBLOB()
{
try
{ // Prepare a Statement:
PreparedStatement stmnt = conn.prepareStatement("select aBlob from BlobTable");
// Execute
ResultSet rs = stmnt.executeQuery();
while(rs.next())
{
try
{
// Get as a BLOB
Blob aBlob = rs.getBlob(1);
byte[] allBytesInBlob = aBlob.getBytes(1, (int) aBlob.length());
}
catch(Exception ex)
{
// The driver could not handle this as a BLOB...
// Fallback to default (and slower) byte[] handling
byte[] bytes = rs.getBytes(1);
}
}
// Close resources
rs.close();
stmnt.close();
}
catch(Exception ex)
{
this.log("Error when trying to read BLOB: " + ex);
}
}
Question - 65 : - Which Is The Preferred Collection Class To Use For Storing Database Result Sets?
Answer - 65 : -
When retrieving database results, the best collection implementation to use is the LinkedList. The benefits include:
Retains the original retrieval order
Has quick insertion at the head/tail
Doesn't have an internal size limitation like a Vector where when the size is exceeded a new internal structure is created (or you have to find out size beforehand to size properly)
Permits user-controlled synchronization unlike the pre-Collections Vector which is always synchronized.
Basically:
ResultSet result = stmt.executeQuery("...");
List list = new LinkedList();
while(result.next()) {
list.add(result.getString("col"));
}
If there are multiple columns in the result set, you'll have to combine them into their own data structure for each row. Arrays work well for that as you know the size, though a custom class might be best so you can convert the contents to the proper type when extracting from databse, instead of later.
Question - 66 : - How Can I Get Data From Multiple Resultsets?
Answer - 66 : -
With certain database systems, a stored procedure can return multiple result sets, multiple update counts, or some combination of both. Also, if you are providing a user with the ability to enter any SQL statement, you don't know if you are going to get a ResultSet or an update count back from each statement, without analyzing the contents. The Statement.execute() method helps in these cases.
Method Statement.execute() returns a boolean to tell you the type of response:
true indicates next result is a ResultSet
Use Statement.getResultSet to get the ResultSet
false indicates next result is an update count
Use Statement.getUpdateCount to get the update count
false also indicates no more results
Update count is -1 when no more results (usually 0 or positive)
After processing each response, you use Statement.getMoreResults to check for more results, again returning a boolean. The following demonstrates the processing of multiple result sets:
boolean result = stmt.execute(" ... ");
int updateCount = stmt.getUpdateCount();
while (result || (updateCount != -1)) {
if(result) {
ResultSet r = stmt.getResultSet();
// process result set
} else if(updateCount != -1) {
// process update count
}
result = stmt.getMoreResults();
updateCount = stmt.getUpdateCount();
}
Question - 67 : - I Need To Have Result Set On A Page Where The User Can Sort On The Column Headers. Any Ideas?
Answer - 67 : -
One possibility: Have an optional field in your form or GET url called (appropriately) ORDER with a default value of either "no order" or whatever you want your default ordering to be (i.e. timestamp, username, whatever). When you get your request, see what the value of the ORDER element is. If it's null or blank, use the default. Use that value to build your SQL query, and display the results to the page. If you're caching data in your servlet, you can use the Collection framework to sort your data (see java.util.Collections) if you can get it into a List format. Then, you can create a Collator which can impose a total ordering on your results.
Question - 68 : - What's The Best Way, In Terms Of Performance, To Do Multiple Insert/update Statements, A Preparedstatement Or Batch Updates?
Answer - 68 : -
Because PreparedStatement objects are precompiled, their execution can be faster than that of Statement objects. Consequently, an SQL statement that is executed many times is often created as a PreparedStatement object to increase efficiency. A CallableStatement object provides a way to call stored procedures in a standard manner for all DBMSes. Their execution can be faster than that of PreparedStatement object. Batch updates are used when you want to execute multiple statements together. Actually, there is no conflict here. While it depends on the driver/DBMS engine as to whether or not you will get an actual performance benefit from batch updates, Statement, PreparedStatement, and CallableStatement can all execute the addBatch() method.
Question - 69 : - What Is Jdo?
Answer - 69 : -
JDO provides for the transparent persistence of data in a data store agnostic manner, supporting object, hierarchical, as well as relational stores.
Question - 70 : - What do you mean by Metadata and why we are using it?
Answer - 70 : -
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.