What is JDBC and what is it good for?

Ans :

JDBC is a set of classes and interfaces written in Java to allow other Java programs to send SQL statements to a relational database management system. 
Oracle provides three categories of JDBC drivers: 

JDBC Thin for writing Java applets 
JDBC OCI for writing Java applications 
JDBC KPRB driver is used form Oracle8i for Java Stored Procedures 
Oracle's JDBC Thin driver is a Type 4 driver that uses Java sockets to connect directly to Oracle. It provides its own implementation of a TCP/IP version of Oracle's SQL*Net. Because it is 100% Java, this driver is platform independent and can also run from a Web Browser. 
The Oracle Call Interface (OCI) is an application programming interface to Oracle databases. It consists of a library of C language routines to allow C programs (or programs written in other third generation languages) to send SQL statements to the database and interact with it in other ways. 

Oracle's JDBC OCI drivers are Type 2 JDBC drivers. They provide an implementation of the JDBC interfaces that uses the OCI to interact with an Oracle database. You must use a JDBC OCI driver appropriate to your Oracle client installation. The OCI driver works through either SQL*Net or Net8. 

JDBC OCI7 works with an Oracle7 client. 
JDBC OCI8 works with an Oracle8 client. 
Either of these client versions can access Oracle7 or Oracle8 servers. 
The JDBC OCI drivers allow you to call the OCI directly from Java, thereby providing a high degree of compatibility with a specific Version of Oracle. Because they use native methods, they are platform specific. 

Oracle's JDBC KBPR driver is used equivalent to the server side OCI driver. The KBRP driver is mainly used for writing Java stored procedures and triggers. 

All three drivers support the same syntax and API's. Oracle needs three drivers to support different deployment options. Looking at source code, they will only differ in the way you connect to the database. Remember, you must use a JDBC version that matches your Java Development Kit. 

Q . How does one use the Oracle JDBC Thin Driver?

Ans : 

The the JDBC thin driver provides the only way to access Oracle from the Web (applets). It is smaller and faster than the OCI drivers, and doesn't require a pre-installed version of the JDBC drivers. 
import java.sql.*;
class dbAccess {
    public static void main (String args []) throws SQLException
    {
            DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
            Connection conn = DriverManager.getConnection
                               ("jdbc:oracle:thin:@qit-uq-cbiw:1526:orcl", "scott", "tiger");
              // @machineName:port:SID, userid, password
             Statement stmt = conn.createStatement();
             ResultSet rset = stmt.executeQuery("select BANNER from SYS.V_$VERSION");
             while (rset.next())
                     System.out.println (rset.getString(1)); // Print col 1
             stmt.close();

     }
}

Q . How does one use the Oracle OCI Drivers?

Ans :

One must have SQL*Net installed and working before attempting to use the OCI drivers. 
import java.sql.*;
class dbAccess {
        public static void main (String args []) throws SQLException
        {
              try {
                   Class.forName ("oracle.jdbc.driver.OracleDriver");
              } catch (ClassNotFoundException e) {
                  e.printStackTrace();
              }
              Connection conn = DriverManager.getConnection
                                  ("jdbc:oracle:oci8:@qit-uq-cbiw_orcl", "scott", "tiger");
              // or oci7 @TNSNames_Entry, userid, password

              Statement stmt = conn.createStatement();
              ResultSet rset = stmt.executeQuery("select BANNER from SYS.V_$VERSION");
              while (rset.next())
                   System.out.println (rset.getString(1)); // Print col 1
               stmt.close();
         }
}

Q . What is JSQL and what is it good for?

Ans :

JSQL is an ANSI standard way of interfacing SQL and Java. It provides a Java precompiler that translates JSQL call to JDBC calls. The idea is similar to that of other Oracle Precompilers. 
SQLJ is more concise and thus easier to write than JDBC, and provides compile-time schema validation and syntax checking for easier debugging. SQLJ reads input either from a SQLJ file, or a Java source file in which SQLJ statements are embedded. The SQLJ precompiler translates the SQLJ statements into their equivalent JDBC calls. SQLJ supports static SQL.     

Q . Where can I find a list of the latest JDBC drivers ?

Ans :

The latest list of jdbc drivers is available on Suns Website.
at the time of writing this faq the web site is: http://java.sun.com/products/jdbc/drivers.html

The latest InterBase InterClient drivers can be found at: http://www.interbase.com/downloads/products.html

Q . Can I connect to my database from Microsoft Access or Visual Basic via ODBC? 

Ans :

There is a great ODBC driver available for Windows that lets you connect to your database from ODBC-enabled Windows programs such as Microsoft Access. Download links can be found in the MyODBC section of this page: http://www.mysql.com/download.html

Q . Why do I have more than one database connection? 

Ans :

A servlet, being a multithreaded application, may have several instances of itself running at the same time. When instances of the servlet try to share the same connection, they line up in a queue and wait for their turn. You'll get better performance if you have more than one database connection for them to share. There's a great way of efficiently using the database connections allotted to your account - a technique called database connection pooling.

Q . How do I pool my database connections? 

Ans :

Database connection pooling uses your database connections in an intelligent manner to help reduce the time a servlets waits for a database connection. The DbConnectionBroker and JDBCGlobalBroker classes, available at Java Exchange, come pre-installed with your MySQL Database service. We also provide sample code to make your job even easier. Here's what DbConnectionBroker and JDBCGlobalBroker will supply to your database-intensive servlets:

Quicker connections. Normally, when a servlet requests a connection, the database server starts a new database thread, initializes it, then hands it to the servlet. This process takes time. A good database connection pooling system gets in between the servlet and the database engine. It holds a certain number of database connections open and just hands them to servlets as they need them. This is much faster than initializing database threads.

Intelligent connection use. As long as your servlet only needs a few database connections, DbConnectionBroker keeps a small number of connections open to streamline database and system memory use. When you request more connections, it dynamically adjusts its connection pool to provide your servlets with the connectivity they need.

We highly recommend using a global pool of database connections that is available to all of your servlets. Rather than having each servlet manage its own set of database connections, all of your servlets will access the same set of database connections. Sample code is available that demonstrates how to do this.

Q . Indirect Database Access from Applets

Ans :

Even if you are creating an applet, you can still access databases from applets. You can use it in (at least) four ways: 
(1) Create a CGI application on your web server, and call that CGI application from within your applet. In this case, your applet is communicating only with the web server from which your applet is downloaded, so there are no security violations. 
(2) Use Java servlets. Servlets are like CGI, but are more memory-efficient. They also are run by the web server. 
(3) Create a server application that runs on your web server, and open a socket to that server application from your Java applet. Once again, no security violations are created. 
(4) Find a commercial database server, such as Symantec's dbAnywhere, or a shareware or freeware one(such as RmiJDBC, described below) and a JDBC driver to communicate with it. 


It should be noted that if you choose the first or third option, you are not limited to using JDBC to do your database calls. A CGI program can be written in any language that reads from standard in and writes to standard out, and can invoked from within an applet. (The "Java Pro" link below explains how.) Similarly, you could write a server application in C that would receive requests over a socket port from your applet. In terms of ease of programming, the fourth option is best. It allows you to use JDBC just as if your database were on your local machine. 

Q . How do I determine the number of columns in a ResultSet?

Ans :

When you send a SELECT statement to a database using JDBC, you'll read the query results into a ResultSet object. We demonstrated how to do this in our article on Creating and Executing an SQL Query.

In many database programs you'll need to go a bit further, and dynamically determine the number of columns that are in a ResultSet object. As an example, I've been working on a Contact Management servlet-based Intranet application. Because I can dynamically determine the number of columns returned from my queries, I can write one QueryServlet to handle all of my database query needs. This makes it very easy to create a simple report writer for my Intranet app!

You can get information about your ResultSet object, including the number of columns of data in the ResultSet, by creating a ResultSetMetaData object, and using it's methods to extract the desired information from the ResultSet object. Here's how to determine the number of columns in a ResultSet object:

//------------------------------------------------------//
// Here's the code to determine the number of columns //
// in the ResultSet. //
//------------------------------------------------------//
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery("SELECT * from Customer");
ResultSetMetaData rsmd = rs.getMetaData();

int numCols = rsmd.getColumnCount();


In this example, the variable numCols contains the number of columns of data that are in the ResultSet.

As a final note, once you know the number of columns that are in a ResultSet, you can use the next() method of the ResultSet object to cycle through the columns and the ResultSet data.

Q . What is a database URL?

Ans :

A database URL (or JDBC URL) is a platform independent way of adressing a database. A database/JDBC URL is of the form 

jdbc:[subprotocol]:[node]/[databaseName] 

If you are accessing a database called wham on the server yoghurt.jguru.com using the xyz subprotocol, your database URL could be: 

jdbc:xyz:yoghurt.jguru.com/wham 

Notice that the ordinary URL is of the form [protocol]://[node]/[path], such as http://www.javafaq.com/index.html. The jdbc database URL mimics the ordinary URL, just adding a subprotocol, and - depending on the driver implementation - omitting the double slashes. 

If the database resides on the same computer node as the java program, the hostname part and the corresponding double slashes of the jdbc can be skipped: 

jdbc:odbc:wham 

All standard database URLs should commence with the string jdbc.

Q . How do I create a database connection?

Ans :

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");

Q . What is Metadata and why should I use it?

Ans :

Metadata ('data about data') is information about one of two things: 
Database information (java.sql.DatabaseMetaData), or 
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

Q . What is the advantage of using a PreparedStatement?

Ans :

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. 

Q . What is a "dirty read"?

Ans :

Quite often in database processing, we come across the situation wherein one transaction can change a value, and a second transaction can read this value before the original change has been committed or rolled back. This is known as a dirty read scenario because there is always the possibility that the first transaction may rollback the change, resulting in the second transaction having read an invalid value. 
While you can easily command a database to disallow dirty reads, this usually degrades the performance of your application due to the increased locking overhead. Disallowing dirty reads also leads to decreased system concurrency. 

Q . How do I check what table types exist in a database?

Ans :

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();
}

Q . How do I extract SQL table column type information? 

Ans :

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();
}

Q . How do I find all database stored procedures in a database?

Ans :

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();
}

Q . How can I investigate the parameters to send into and receive from a database stored procedure?

Ans :

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();
}

Q . What properties should I supply to a database driver in order to connect to a database?

Ans :

Most JDBC drivers should accept 3 properties: 

user 
password 
hostname 
However, a JDBC driver may accept an arbitrary number of properties thrown at it. Drivers can be interrogated for their supported properties using the DriverPropertyInfo metadata class. Most drivers will also contain documentation which should specify all properties and their meaning for creating the jdbc database connection.

NOTE! The JDBC/ODBC bridge driver does not properly return an array of DriverPropertyInfo objects, but instead throws a NullPointerException. Other database drivers work better in this respect.

public static void printPropertyInfo(Driver aDriver, String jdbcURL, Properties daProps) throws Exception
{
   // Get the DriverPropertyInfo of the given driver
   DriverPropertyInfo[] props = aDriver.getPropertyInfo(jdbcURL, daProps);

   // If the driver is poorly implemented, a null object may be returned.
   if(props == null) return;

   System.out.println("Resolving properties for: " + aDriver.getClass().getName());
  // List all properties.
   for(int i = 0; i < props.length; i++)
   {
           // Get the property metadata
          String propName = props[i].name;
          String[] propChoices = props[i].choices;
          boolean req = props[i].required;
          String propDesc = props[i].description;

          // Printout
          System.out.println("" + propName + " (Req: " + req + ")");
          if(propChoices == null)
          {
                System.out.println(" No choices.");
          }
          else
          {
                System.out.print(" Choices: ");
                for(int j = 0; j < propChoices.length; j++)
                {
                    System.out.print(" " + propChoices[j]);
               }
        }

      System.out.println(" Desc: " + propDesc);  
   }
}

Q . Which is the preferred collection class to use for storing database result sets?

Ans :

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 

Q . Do I need to commit after an INSERT call in JDBC or does JDBC do it automatically in the DB?

Ans :

If your autoCommit flag (managed by the Connection.setAutoCommit method) is false, you are required to call the commit() method - and vice versa

Q .How can I retrieve only the first n rows, second n rows of a database using a particular WHERE clause ? For example, if a SELECT typically returns a 1000 rows, how do first retrieve the 100 rows, then go back and retrieve the next 100 rows and so on ? 

Ans :

Use the Statement.setFetchSize method to indicate the size of each database fetch. Note that this method is only available in the Java 2 platform. For Jdk 1.1.X and Jdk 1.0.X, no standardized way of setting the fetch size exists. Please consult the Db driver manual.

Q . 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?

Ans :

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. 

Q . How can I make batch updates using JDBC?

Ans :

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. 

Q . Do I need to commit after an INSERT call in JDBC or does JDBC do it automatically in the DB?

Ans :

When you open a connection to a database, the default JDBC behavior is to automatically commit each SQL statement after execution.

To change this behavior, and to execute multiple SQL statements as part of a transaction, you have to invoke the setAutoCommit(false) method on your Connection object. If auto commit is false, then SQL statements won't be committed until you invoke the commit() method on the connection. You also have the option of invoking the rollback() method, to rollback the database changes since the previous commit (or since the opening of the connection, if there was no previous commit). 

If the underlying database doesn't support transactions, then all SQL statements will be committed after execution, regardless of whether you have set auto commit or not. See your database vendor's documentation and your JDBC driver's documentation for detailed information about what features are supported in your specific case.

Q . 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. 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.

Ans :

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. 

Q . The java.sql package contains mostly interfaces. When and how are these interfaces implemented while connecting to database? 

Ans :

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. 

Q . How can I connect from an applet to a database on the server?

Ans :

There are two ways of connecting to a database on the server side.

The hard way. Untrusted applets cannot touch the hard disk of a computer. Thus, your applet cannot use native or other local files (such as JDBC database drivers) on your hard drive. The first alternative solution is to create a digitally signed applet which may use locally installed JDBC drivers, able to connect directly to the database on the server side. 
The easy way. Untrusted applets may only open a network connection to the server from which they were downloaded. Thus, you must place a database listener (either the database itself, or a middleware server) on the server node from which the applet was downloaded. The applet would open a socket connection to the middleware server, located on the same computer node as the webserver from which the applet was downloaded. The middleware server is used as a mediator, connecting to and extract data from the database. This is illustrated below: 

Q . How do i insert an image file (or other raw data) into a database?

Ans :

All raw data types (including binary documents or images) should be read and uploaded to the database as an array of bytes, byte[]. Originating from a binary file, 

1.Read all data from the file using a FileInputStream. 
2.Create a byte array from the read data. 
3.Use method setBytes(int index, byte[] data); of java.sql.PreparedStatement to upload the data.

Q . How can resultset records be restricted to certain rows?

Ans :

The easy answer is "Use a JDBC 2.0 compliant driver". 
With a 2.0 driver, you can use the setFetchSize() method within a Statement or a ResultSet object. 

For example, 


Statement stmt = con.createStatement();
stmt.setFetchSize(400);
ResultSet rs = stmt.executeQuery("select * from customers");

will change the default fetch size to 400. 

You can also control the direction in which the rows are processed. For instance: 


stmt.setFetchDirection(ResultSet.FETCH_REVERSE)

will process the rows from bottom up. 
The driver manager usually defaults to the most efficient fetch size...so you may try experimenting with different value for optimal performance.

Q . How can I pool my database connections so I don't have to keep reconnecting to the database?

Ans :

There are plenty of connection pool implementations described in books or availalble on the net. 
Most of them implement the same model. The process is always the same : 

you gets a reference to the pool 
you gets a free connection from the pool 
you performs your different tasks 
you frees the connection to the pool 

Since your application retrieves a pooled connection, you don't consume your time to connect / disconnect from your data source.
You can find some implementation of pooled connection over the net : 

Db Connection Broker (http://www.javaexchange.com/), a package quite stable ( I used it in the past to pool an ORACLE database on VMS system) 
An implementation can be found at ftp://ftp.wrox.com/professional/2777/chap09.zip, part of the book Professional Java Server Programming 
You can look at the JDBC 2.0 standard extension API specification from SUN which defines a number of additional concepts. 

Q . In distributed architecture (typical three tier consisting of thin client, middleware & database) which type of JDBC driver should be used and why?

Ans :

Normally in 3-tier architectures, you would preferably connect to database via middleware. Pure java drivers are slower than native driver but they do not require any client-side installation. So for middleware, as perfomance is important, native JDBC drivers like Oracle's OCI driver should be preferred. Also native installation(like ODBC or Oracle's Sqlnet ) would be required at middleware level but it would not affect the clients. In the exceptional case, if the database is needed to be accessed by thin client than pure Java driver like Oracle's thin driver is recommended as they do not require any client-side installation.

Q . How do I pass a Timestamp from JDBC to a database? Give me some database specific examples (MS Access etc.).

Ans :

Use the setTimestamp() method to load your timestamp into a PreparedStatement. First create a connection to the database and then: 

String upsql="UPDATE MYTABLE SET MYDATETIME = ? WHERE MYTABLEID=100"; 
PreparedStatement pstmt = con.prepareStatement(upsql); 
pstmt.setTimestamp(1,mytimestampvar); 
pstmt.execute(); 


This method can be used with any database that supports timestamps and any driver that supports PreparedStatement. 

For Windows desktop databases, the JDataConnect driver (and a number of other Windows specific type 3 drivers) support PreparedStatement. If the JDBC-ODBC bridge isn't doing what you need, try a more industrial strength driver. You can obtain a copy of JDataConnect from http://www.softsyn.com/. 

Q . Will a call to PreparedStatement.executeQuery() always close the ResultSet from the previous executeQuery()?

Ans :

A quote from the Java API docs, java.sql.ResultSet: 
A ResultSet is automatically closed by the Statement that generated it when that Statement is closed, re-executed, or is used to retrieve the next result from a sequence of multiple results. 

I think that this should answer your question. It is recommended to retrieve the results into an abitrary datastructure, but be aware that even if in Java parameters are always passed per value; an object variable is a reference (i.e. can be seen as a pointer to an object) and only that reference will be passed per value, but not the object itself. That's a hint for the case that you suddenly get an exception accessing an object you retrieved with myResultSet.getObject(). 

Q . How do you find the number of records returned using the JDBC API? Is there a direct function call(like in other languages)? 

Ans :

Well java.sql.ResultSet does not offer any method to retrieve the amount of rows that have been selected.
Now I have following ideas that I hope might help: 
It is recommended to retrieve the results into an abitray datastructure, especially in case of pooled connections. Now ensure using a datastructure that has an accessor method for its size. 
Another possibility would be to use a seperate prepared statement that returns nothing but a count of the rows that will be selected. 
i.e. select count(*) from myTable
Instead of * you can also use any column existing in myTable. 
A third way I could think of is to add the count(<column>) to the request, thus retrieving the count of all rows as the first column of each row. 
i.e. select count(&lt;existing column&gt;),* from myTable
I am not sure if it really makes sense, but it's at least a possibility. 

Q . I have stored image files in a database. Is there any way to display that image in a web browser by querying the database?

Ans :

I would recommend you to retrieve the image via JDBC from a simple HTTP servlet.
Things you have to take care about:
Ensure to set the correct Mime type. This has to be done calling HttpServletResponse.setContentType(String type);
e.g. myHttpServletResponse.setContentType("image/jpeg");

Ensure that your RDBMS does not limit result sizes (i.e. check the manuals if you get half images, and always the same block sizes). 
Attach ?<param>=<value> to your src URL to specify the picture to be retrieved. This param can be retrieved within your service method very simple, using:
HttpServletRequest.getParameter(String name);
The HTML tag for the image would then be something like follows:
<img src="http://www.mydomain.com/PictureServlet?id=35">
(Sure you can use more params if you need to do so.) 
Use some simple or sophisticated caching algorithm to limit your systems load. 

Q . How can I get data from multiple ResultSets?

Ans :

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(); 
}