JDBC Interview Questions with Answers Page V
Wiki Home | Placement Papers | Interview Question | Consultant Address | Study Abroad | Aptitude QuestionsFrom freshersonline.com
1.What does ResultSet actually contain? Is it the actual data of the result or some links to databases? If it is the actual data then why can't we
access it after connection is closed?
A ResultSet is an interface. Its implementation depends on the driver and hence ,what it "contains" depends partially on the driver and what the query
returns.
For example with the Odbc bridge what the underlying implementation layer contains is an ODBC result set. A Type 4 driver executing a stored procedure that
returns a cursor - on an oracle database it actually returns a cursor in the databse. The oracle cursor can however be processed like a ResultSet would be
from the client. Closing a connection closes all interaction with the database and releases any locks that might have been obtained in the process.
2. How do I extract a BLOB from a database?
A BLOB (Binary Large OBject) is essentially an array of bytes (byte[]), stored in the database. You extract the data in two steps:
1. Call the getBlob method of the Statement class to retrieve a java.sql.Blob object
2. 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);
}
}
</div>
3. How do I extract the SQL statements required to move all tables and views from an existing database to another database?
The operation is performed in 9 steps:
1. Open a connection to the source database. Use the DriverManager class.
2. Find the entire physical layout of the current database. Use the DatabaseMetaData interface.
3. Create DDL SQL statements for re-creating the current database structure. Use the DatabaseMetaData interface.
4. Build a dependency tree, to determine the order in which tables must be setup. Use the DatabaseMetaData interface.
5. Open a connection to the target database. Use the DriverManager class.
6. Execute all DDL SQL statements from (3) in the order given by (4) in the target database to setup the table and view structure. Use the
PreparedStatement interface.
7. If (6) threw exceptions, abort the entire process.
8. Loop over all tables in the physical structure to generate DML SQL statements for re-creating the data inside the table. Use the ResultSetMetaData
interface.
9. Execute all DML SQL statements from (8) in the target database.
4. How do I check what table types exist in a database?
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();
}
5. What is the advantage of using a PreparedStatement?
For SQL statements that are executed repeatedly, using a PreparedStatement object would almost always be faster than using a Statement object. This is
because creating a PreparedStatement object by explicitly giving the SQL statement causes the statement to be precompiled within the database
immediately. Thus, when the PreparedStatement is later executed, the DBMS does not have to recompile the SQL statement and prepared an execution plan -
it simply runs the statement.
Typically, PreparedStatement objects are used for SQL statements that take parameters. However, they can also be used with repeatedly executed SQL
statements that do not accept parameters.
6. How do I find all database stored procedures in a database?
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();
}
7. How can I investigate the physical structure of a database?
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();
}
8. How does the Java Database Connectivity (JDBC) work?
The JDBC is used whenever a Java application should communicate with a relational database for which a JDBC driver exists. JDBC is part of the Java
platform standard; all visible classes used in the Java/database communication are placed in package java.sql.
Main JDBC classes:
- DriverManager. Manages a list of database drivers. Matches connection requests from the java application with the proper database driver using
communication subprotocol. The first driver that recognizes a certain subprotocol under jdbc (such as odbc or dbAnywhere/dbaw) will be used to establish
a database Connection.
- Driver. The database communications link, handling all communication with the database. Normally, once the driver is loaded, the developer need not
call it explicitly.
- Connection. Interface with all methods for contacting a database
- Statement. Encapsulates an SQL statement which is passed to the database to be parsed, compiled, planned and executed.
- ResultSet. The answer/result from a statement. A ResultSet is a fancy 2D list which encapsulates all outgoing results from a given SQL query.
9. What is Metadata and why should I use it?
Metadata ('data about data') is information about one of two things:
1. Database information (java.sql.DatabaseMetaData), or
2. Information about a specific ResultSet (java.sql.ResultSetMetaData).
Use DatabaseMetaData to find information about your database, such as its capabilities and structure. Use ResultSetMetaData to find information about the
results of an SQL query, such as size and types of columns.
10. How do I create a database connection?
The database connection is created in 3 steps:
1. Find a proper database URL (see FAQ on JDBC URL)
2. Load the database driver
3. Ask the Java DriverManager class to open a connection to your database
In java code, the steps are realized in code as follows:
1. Create a properly formatted JDBR URL for your database. (See FAQ on JDBC URL for more information). A JDBC URL has the form
jdbc:someSubProtocol://myDatabaseServer/theDatabaseName
2.
try {
Class.forName("my.database.driver");
}
catch(Exception ex)
{
System.err.println("Could not load database driver: " + ex);
}
3. Connection conn = DriverManager.getConnection("a.JDBC.URL", "databaseLogin", "databasePassword");
11. Can I use the JDBC-ODBC bridge driver in an applet?
Short answer: No.
Longer answer: You may create a digitally signed applet using a Certicate to circumvent the security sandbox of the browser.
12. Which is the preferred collection class to use for storing database result sets?
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.
13. The java.sql package contains mostly interfaces. When and how are these interfaces implemented while connecting to database?
The implementation of these interfaces is all part of the driver. A JDBC driver is not just one class - it is a complete set of database-specific
implementations for the interfaces defined by the JDBC.
These driver classes come into being through a bootstrap process. This is best shown by stepping through the process of using JDBC to connect to a
database, using Oracle's type 4 JDBC driver as an example:
- First, the main driver class must be loaded into the VM:
Class.forName("oracle.jdbc.driver.OracleDriver");
The specified driver must implement the Driver interface. A class initializer (static code block) within the OracleDriver class registers the driver with
the DriverManager.
- Next, we need to obtain a connection to the database:
String jdbcURL = "jdbc:oracle:thin:@www.jguru.com:1521:ORCL";
Connection connection = DriverManager.getConnection(jdbcURL);
DriverManager determines which registered driver to use by invoking the acceptsURL(String url) method of each driver, passing each the JDBC URL. The
first driver to return "true" in response will be used for this connection. In this example, OracleDriver will return "true", so DriverManager then
invokes the connect() method of OracleDriver to obtain an instance of OracleConnection. It is this database-specific connection instance implementing the
Connection interface that is passed back from the DriverManager.getConnection() call.
- The bootstrap process continues when you create a statement:
Statement statement = connection.createStatement();
The connection reference points to an instance of OracleConnection. This database-specific implementation of Connection returns a database-specific
implementation of Statement, namely OracleStatement
- Invoking the execute() method of this statement object will execute the database-specific code necessary to issue an SQL statement and retrieve the
results:
ResultSet result = statement.executeQuery("SELECT * FROM TABLE");
Again, what is actually returned is an instance of OracleResultSet, which is an Oracle-specific implementation of the ResultSet interface.
So the purpose of a JDBC driver is to provide these implementations that hide all the database-specific details behind standard Java interfaces.
14. How can I manage special characters (for example: " _ ' % ) when I execute an INSERT query? If I don't filter the quoting marks or the
apostrophe, for example, the SQL string will cause an error.
In JDBC, strings containing SQL commands are just normal strings - the SQL is not parsed or interpreted by the Java compiler. So there is no special
mechanism for dealing with special characters; if you need to use a quote (") within a Java string, you must escape it.
The Java programming language supports all the standard C escapes, such as \n for newline, \t for tab, etc. In this case, you would use \" to represent a
quote within a string literal:
String stringWithQuote =
"\"No,\" he replied, \"I did not like that salted licorice.\"";
This only takes care of one part of the problem: letting us control the exact string that is passed on to the database. If you want tell the database to
interpret characters like a single quote (') literally (and not as string delimiters, for instance), you need to use a different method. JDBC allows you
to specify a separate, SQL escape character that causes the character following to be interpreted literally, rather than as a special character.
An example of this is if you want to issue the following SQL command:
SELECT * FROM BIRDS
WHERE SPECIES='Williamson's Sapsucker'
In this case, the apostrophe in "Williamson's" is going to cause a problem for the database because SQL will interpret it as a string delimiter. It is
not good enough to use the C-style escape \', because that substitution would be made by the Java compiler before the string is sent to the database.
Different flavors of SQL provide different methods to deal with this situation. JDBC abstracts these methods and provides a solution that works for all
databases. With JDBC you could write the SQL as follows:
Statement statement = // obtain reference to a Statement
statement.executeQuery(
"SELECT * FROM BIRDS WHERE SPECIES='Williamson/'s Sapsucker' {escape '/'}");
The clause in curly braces, namely {escape '/'}, is special syntax used to inform JDBC drivers what character the programmer has chosen as an escape
character. The forward slash used as the SQL escape has no special meaning to the Java compiler; this escape sequence is interpreted by the JDBC driver
and translated into database-specific SQL before the SQL command is issued to the database.
Escape characters are also important when using the SQL LIKE clause. This usage is explicitly addressed in section 11.5 of the JDBC specification:
The characters "%" and "_" have special meaning in SQL LIKE clauses (to match zero or more characters, or exactly one character, respectively). In order
to interpret them literally, they can be preceded with a special escape character in strings, e.g. "\". In order to specify the escape character used to
quote these characters, include the following syntax on the end of the query:
{escape 'escape-character'}
For example, the query
SELECT NAME FROM IDENTIFIERS WHERE ID LIKE '\_%' {escape '\'}
finds identifier names that begin with an underbar.
15. How can I make batch updates using JDBC?
One of the more advanced features of JDBC 2.0 is the ability to submit multiple update statements to the database for processing as a single unit. This
batch updating can be significantly more efficient compared to JDBC 1.0, where each update statement has to be executed separately.
Consider the following code segment demonstrating a batch update:
try {
dbCon.setAutoCommit(false);
Statement stmt= dbCon.createStatement();
stmt.addBatch("INSERT INTO bugs "+
"VALUES (1007, 'Server stack overflow', 1,2,{d '1999-01-01'})");
stmt.addBatch("INSERT INTO bugs "+
"VALUES (1008,'Cannot load DLL', 3,1,{d '1999-01-01'})");
stmt.addBatch("INSERT INTO bugs "+
"VALUES (1009,'Applet locks up',2,2,{d '1999-01-01'})");
int[] updCnt = stmt.executeBatch();
dbCon.commit();
} catch (BatchUpdateException be) {
//handle batch update exception
int[] counts = be.getUpdateCounts();
for (int i=0; I counts.length; i++) {
System.out.println("Statement["+i+"] :"+counts[i]);
}
dbCon.rollback();
}
catch (SQLException e) {
//handle SQL exception
dbCon.rollback();
}
Before carrying out a batch update, it is important to disable the auto-commit mode by calling setAutoCommit(false). This way, you will be able to
rollback the batch transaction in case one of the updates fail for any reason. When the Statement object is created, it is automatically associated a
"command list", which is initially empty. We then add our SQL update statements to this command list, by making successive calls to the addBatch()
method. On calling executeBatch(), the entire command list is sent over to the database, and are then executed in the order they were added to the list.
If all the commands in the list are executed successfully, their corresponding update counts are returned as an array of integers. Please note that you
always have to clear the existing batch by calling clearBatch() before creating a new one.
If any of the updates fail to execute within the database, a BatchUpdateException is thrown in response to it. In case there is a problem in returning
the update counts of each SQL statement, a SQLException will be thrown to indicate the error.
16. How do I extract SQL table column type information?
Use the getColumns method of the java.sql.DatabaseMetaData interface to investigate the column type information of a particular table. Note that most
arguments to the getColumns method (pinpointing the column in question) may be null, to broaden the search criteria. A code sample can be seen 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 column types for the table "sysforeignkeys", in schema
// "dbo" and catalog "test".
ResultSet rs = dbmd.getColumns("test", "dbo", "sysforeignkeys", "%");
// Printout table data
while(rs.next())
{
// Get dbObject metadata
String dbObjectCatalog = rs.getString(1);
String dbObjectSchema = rs.getString(2);
String dbObjectName = rs.getString(3);
String dbColumnName = rs.getString(4);
String dbColumnTypeName = rs.getString(6);
int dbColumnSize = rs.getInt(7);
int dbDecimalDigits = rs.getInt(9);
String dbColumnDefault = rs.getString(13);
int dbOrdinalPosition = rs.getInt(17);
String dbColumnIsNullable = rs.getString(18);
// Printout
System.out.println("Col(" + dbOrdinalPosition + "): " + dbColumnName
+ " (" + dbColumnTypeName +")");
System.out.println(" Nullable: " + dbColumnIsNullable +
", Size: " + dbColumnSize);
System.out.println(" Position in table: " + dbOrdinalPosition
+ ", Decimal digits: " + dbDecimalDigits);
}
// Free database resources
rs.close();
conn.close();
}
17. How can I investigate the parameters to send into and receive from a database stored procedure?
Use the method getProcedureColumns in interface DatabaseMetaData to probe a stored procedure for metadata. The exact usage is described in the code
below.
NOTE! This method can only discover parameter values. For databases where a returning ResultSet is created simply by executing a SELECT statement within
a stored procedure (thus not sending the return ResultSet to the java application via a declared parameter), the real return value of the stored
procedure cannot be detected. This is a weakness for the JDBC metadata mining which is especially present when handling Transact-SQL databases such as
those produced by SyBase and Microsoft.
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 column definitions for procedure "getFoodsEaten" in
// schema "testlogin" and catalog "dbo".
System.out.println("Procedures are called '" + dbmd.getProcedureTerm() +"' in the DBMS.");
ResultSet rs = dbmd.getProcedureColumns("test", "dbo", "getFoodsEaten", "%");
// 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 dbColumnName = rs.getString(4);
short dbColumnReturn = rs.getShort(5);
String dbColumnReturnTypeName = rs.getString(7);
int dbColumnPrecision = rs.getInt(8);
int dbColumnByteLength = rs.getInt(9);
short dbColumnScale = rs.getShort(10);
short dbColumnRadix = rs.getShort(11);
String dbColumnRemarks = rs.getString(13);
// Interpret the return type (readable for humans)
String procReturn = null;
switch(dbColumnReturn)
{
case DatabaseMetaData.procedureColumnIn:
procReturn = "In";
break;
case DatabaseMetaData.procedureColumnOut:
procReturn = "Out";
break;
case DatabaseMetaData.procedureColumnInOut:
procReturn = "In/Out";
break;
case DatabaseMetaData.procedureColumnReturn:
procReturn = "return value";
break;
case DatabaseMetaData.procedureColumnResult:
procReturn = "return ResultSet";
default:
procReturn = "Unknown";
}
// Printout
System.out.println("Procedure: " + dbProcedureCatalog + "." + dbProcedureSchema
+ "." + dbProcedureName);
System.out.println(" ColumnName [ColumnType(ColumnPrecision)]: " + dbColumnName
+ " [" + dbColumnReturnTypeName + "(" + dbColumnPrecision + ")]");
System.out.println(" ColumnReturns: " + procReturn + "(" + dbColumnReturnTypeName + ")");
System.out.println(" Radix: " + dbColumnRadix + ", Scale: " + dbColumnScale);
System.out.println(" Remarks: " + dbColumnRemarks);
}
// Close database resources
rs.close();
conn.close();
}
18. How do I check what table-like database objects (table, view, temporary table, alias) are present in a particular database?
Use java.sql.DatabaseMetaData to probe the database for metadata. Use the getTables method to retrieve information about all database objects (i.e.
tables, views, system tables, temporary global or local tables or aliases). The exact usage is described in the code below.
NOTE! Certain JDBC drivers throw IllegalCursorStateExceptions when you try to access fields in the ResultSet in the wrong order (i.e. not consecutively).
Thus, you should not change the order in which you retrieve the metadata from the ResultSet.
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 dbObjects. Replace the last argument in the getTables
// method with objectCategories below to obtain only database
// tables. (Sending in null retrievs all dbObjects).
String[] objectCategories = {"TABLE"};
ResultSet rs = dbmd.getTables(null, null, "%", null);
// Printout table data
while(rs.next())
{
// Get dbObject metadata
String dbObjectCatalog = rs.getString(1);
String dbObjectSchema = rs.getString(2);
String dbObjectName = rs.getString(3);
String dbObjectType = rs.getString(4);
// Printout
System.out.println("" + dbObjectType + ": " + dbObjectName);
System.out.println(" Catalog: " + dbObjectCatalog);
System.out.println(" Schema: " + dbObjectSchema);
}
// Close database resources
rs.close();
conn.close();
}
