Logo F2FInterview

JDBC Interview Questions

Q   |   QA

StatementPreparedStatement
A standard Statement is used to create a Java representation of a literal SQL statement and execute it on the database.A PreparedStatement is a precompiled  statement. This means that when the PreparedStatement is executed, the RDBMS can just run the PreparedStatement SQL statement without having to compile it first.
Statement has to verify its metadata against the database every time.While a prepared statement has to verify its metadata against the database only once.
If you want to execute the SQL statement once go for STATEMENTIf you want to execute a single SQL statement multiple number of times, then go for PREPAREDSTATEMENT. PreparedStatement objects can be reused with passing different values to the queries

Callable statements are used from JDBC application to invoke stored procedures and functions.

PL/SQL stored procedures are called from within JDBC programs by means of the prepareCall() method of the Connection object created. A call to this method takes variable bind parameters as input parameters as well as output variables and creates an object instance of the CallableStatement class.
The following line of code illustrates this:   

CallableStatement stproc_stmt = conn.prepareCall("{call procname(?,?,?)}");

Here conn is an instance of the Connection class.

There are four types of drivers defined by JDBC as follows:

  • Type 1: JDBC/ODBC: These require an ODBC (Open Database Connectivity) driver for the database to be installed. This type of driver works by translating the submitted queries into equivalent ODBC queries and forwards them via native API calls directly to the ODBC driver. It provides no host redirection capability.
  • Type2: Native API (partly-Java driver): This type of driver uses a vendor-specific driver or database API to interact with the database. An example of such an API is Oracle OCI (Oracle Call Interface). It also provides no host redirection.
  • Type 3: Open Protocol-Net: This is not vendor specific and works by forwarding database requests to a remote database source using a net server component. How the net server component accesses the database is transparent to the client. The client driver communicates with the net server using a database-independent protocol and the net server translates this protocol into database calls. This type of driver can access any database.
  • Type 4: Proprietary Protocol-Net(pure Java driver): This has a same configuration as a type 3 driver but uses a wire protocol specific to a particular vendor and hence can access only that vendor's database. Again this is all transparent to the client.

Note: Type 4 JDBC driver is most preferred kind of approach in JDBC.

A stored procedure is a set of statements/commands which reside in the database. The stored procedure is pre-compiled and saves the database the effort of parsing and compiling sql statements every time a query is run. Each database has its own stored procedure language, usually a variant of C with a SQL preprocessor.

Before the advent of 3-tier/n-tier architecture it was pretty common for stored procedures to implement the business logic (A lot of systems still do it). The biggest advantage is of course speed. Also certain kinds of data manipulations are not achieved in SQL. Stored procedures provide a mechanism to do these manipulations. Stored procedures are also useful when you want to do Batch updates/exports kind of stuff on the db. The overhead of a JDBC Connection may be significant in these cases.

In order to link this F2FInterview's page as Reference on your website or Blog, click on below text area and pres (CTRL-C) to copy the code in clipboard or right click then copy the following lines after that paste into your website or Blog.

Get Reference Link To This Page: (copy below code by (CTRL-C) and paste into your website or Blog)
HTML Rendering of above code: