|
JDBC Interview Questions and Answers
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.
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
<div align="center">
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>
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.
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();
}
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.
Page Numbers :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Have a Question ?
post your questions here. It
will be answered as soon as possible.
Check
Java Interview
Questions for more Java Interview Questions with answers
Check
Structs Interview
Questions for more Structs Interview Questions with answers
Check
Servlet Interview
Questions for more Servlet Interview Questions with answers
|
