JDBC DESCRIPTION

Q. JDBC stands for _______

Ans. Java Database Connectivity.

Q. What is JDBC API?

Ans. JDBC API is a collection of classes and interfaces, which help a Java application to connect to SQL based relational databases by abstracting vendor specific details of the database.

Q. The JDBC classes and interfaces are found in the packages _________ and
________

Ans. java.sql and javax.sql

Q. The latest version of JDBC is __________

Ans. JDBC 3.0

Q. What is a JDBC Driver?

Ans. A JDBC Driver is a middleware that translates the JDBC API calls to database vendor specific API calls. It is an implementation of the classes and interfaces specified in the java.sql and javax.sql packages by the database vendors or third-party vendors.

Q. How does JDBC provide database abstraction to a Java application developer?

Ans. Since the JDBC drivers follow the JDBC specification, the JDBC application developers can replace one driver for their application with another better one without having to rewrite their application. If they had used some proprietary API provided by some RDBMS vendor, they would not have been able to change the driver and/or database without having to rewrite the complete application.

Q. Who develops JDBC Specification?

Ans. SUN Microsystems prepares and maintains the JDBC specification. Since JDBC is just a specification (suggestions for writing and using JDBC drivers), third-party vendors develop JDBC drivers adhering to this specification. JDBC developers then use these drivers to access data sources.

Q. There are _____ types of JDBC drivers.

Ans. 4

Q. What are the four types of JDBC drivers?

Ans. There are four types of JDBC drivers. Here is a brief description of each of them:

1. JDBC Type 1 Driver (JDBC-ODBC Bridge)

The JDBC-ODBC Bridge translates the standard JDBC API calls to corresponding ODBC calls. This configuration requires the client application to have the JDBC-ODBC API implementation (SUN provides the JDBC-ODBC Driver), the ODBC Driver, and the database vendor specific native APIs such as the OCI library for Oracle installed on each client machine. Since each data access call has to go through many layers, this approach is the slowest and the most inefficient for high performance database access requirements. The use of JDBC-ODBC should be considered for experimental purposes only or when no other driver is available (this is because the Bridge comes with the Java 2 SDK, Standard Edition, and Enterprise Edition, and it doesn't require any extra setup itself). However this approach has to be used in some situations for example, a Microsoft Access 2000 database can only be accessed using the JDBC-ODBC Bridge.

2. JDBC Type 2 Driver (Part Java, Part Native Driver)

These type of drivers use a mixture of JDBC API implementation and database vendor specific native APIs for data access. When a database call is made, the JDBC driver translates the request into database vendor specific API call. In this approach, the JDBC Driver along with the database vendor specific native APIs such as the OCI library for Oracle should be installed on each client machine. It doesn't require the presence of ODBC. Type 2 drivers are faster than Type 1 drivers because of the absence of ODBC.

3. JDBC Type 3 Driver (Intermediate Database Access Server)

Type 3 drivers use an intermediate database server (middleware) that has the ability to connect multiple Java clients to multiple databases. The Java client application sends a database call through the JDBC Driver which forwards it to the Intermediate Database Access Server. The intermediate server can use different native protocols to connect to different databases.
One of the benefits of using a Type 3 driver is that it allows the Java application to be flexible, as the Intermediate Database Server can abstract the details of connection to various databases.

4. JDBC Type 4 Driver (Pure Java Driver)

Type 4 Drivers are a pure Java alternative to Type 2 Drivers. When a database call is made, Type 4 drivers connect to the database directly, using database vendor specific network protocols. They do so by making direct socket connections with the database. Type 4 drivers are the simplest to deploy since there are no additional libraries or middleware to install. They are also the most efficient and most widely used JDBC drivers in industry. All major database vendors provide Type 4 JDBC Drivers for their databases and they are also available from third party driver vendors.

Q. Classify the following as a class or an interface:
a) java.sql.Drivermanager
b) java.sql.Connection
c) java.sql.Statement
d) java.sql.ResultSet
e) java.sql.DatabaseMetaData
f) java.sql.ResultSetMetaData
g) java.sql.PreparedStatement
h) java.sql.CallableStatement
i) java.sql.Driver

Ans. a) java.sql.DriverManager is a class.
b) java.sql.Connection is an interface.
c) java.sql.Statement is an interface.
d) java.sql.ResultSet is an interface.
e) java.sql.DatabaseMetaData is an interface.
f) java.sql.ResultSetMetaData is an interface.
g) java.sql.PreparedStatement is an interface which extends the java.sql.Statement interface.
h) java.sql.CallableStatement is an interface which extends the java.sql.Statement interface.
i) Java.sql.Driver is an interface.

Q. The Exception subclass which handles JDBC related exceptions is _________

Ans. java.sql.SQLException







Q. Which interfaces in the java.sql package are implemented by the database vendors/third party driver vendors?

Ans. a) java.sql.Driver
b) java.sql.Connection
c) java.sql.Statement
d) java.sql.PreparedStatement
e) java.sql.CallableStatement

Q. Can we establish more than one connection with a database using a single driver?

Ans. Yes, we can establish more than one connection with a database using a single driver.

Q. What is a URL in JDBC? Why is it used?

Ans. A URL is a method of uniquely identifying a database driver. In a scenario where more than one driver is used for connecting to one or databases, there has to be some way for uniquely identifying each driver.

Q. Each driver needed by a Java application must first be registered with the _________

Ans. java.sql.DriverManager class

Q. What are the two different ways to load a JDBC driver?

Ans. A JDBC driver can be loaded by using any of the following two ways:

a) By using Class.forName() method as:

Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);

b) By using DriverManager class as:

DriverManager.registerDriver(new sun.jdbc.odbc.JdbcOdbcDriver());

Q. What are the basic operations that can be performed on any database? How do we handle such operations in JDBC?

Ans. The basic operations that can be performed on any database are:


a) Create a table.
b) Insert a row in the table.
c) Retrieve row(s) from the table.
d) Modify row(s) in the table.
e) Delete row(s) from the table.

Using JDBC, we can perform the aforesaid operations in the following way:

a) Create a table
……
stmt.executeUpdate(“create table Employee (EMPNO (varchar(6), NAME(char(40)”);
……

b) Insert a row into the table
……
stmt.executeUpdate(“insert into Employee values(‘E0001’,’XYZ’”);
……

c) Retrieve row(s) from the table
……
//select ALL the rows from the table.
ResultSet rst=stmt.executeQuery(“select * from Employee”);
while(rst.next())
{
System.out.println(rst.getString(“EMPNO”));
System.out.println(rst.getString(“NAME”));
}
……
//select PARTICULAR rows from the table.
ResultSet rst=stmt.executeQuery(“select * from Employee where
EMPNO=’E0001’”);
while(rst.next())
{
System.out.println(rst.getString(“EMPNO”));
System.out.println(rst.getString(“NAME”));
}

d) Modify row(s) in the table
……
int numUpdated=stmt.executeUpdate(“update Employee set NAME=’ABC’ where EMPNO=’E0001’”);
……

e) Delete row(s) from the table
……
int updated=stmt.executeUpdate(“delete from Employee where EMPNO=’E0001’”);
……
Q. What is the utility of prepared statements?

Ans. Databases are tuned to do statement caches. This cache uses the statement itself as a key and the access plan is stored in the cache with the corresponding statement. This allows the database engine to reuse the plans for statements that have been executed previously. For example, if we sent the database a statement such as "select a, b from t where c = 2", then the computed access plan is cached. If we send the same statement later, the database can reuse the previous access plan, thus saving us CPU power.
Note however, that the entire statement is the key. For example, if we later sent the statement "select a, b from t where c = 3", it would not find an access plan. This is because the "c=3" is different from the cached plan "c=2". So, for example:

for(int i = 0; i < 1000; ++i)
{
PreparedStatement ps = conn.prepareStatement("select a, b
from t where c = " + I);
ResultSet rs = Ps.executeQuery();
rs.close();
ps.close();
}

Here the cache won't be used. Each iteration of the loop sends a different SQL statement to the database. A new access plan is computed for each iteration and we're basically throwing CPU cycles away using this approach. However, look at the next snippet:

PreparedStatement ps = conn.prepareStatement("select a, b from t
where c = ?");
for(int i = 0; i < 1000; ++i)
{
ps.setInt(1, I);
ResultSet rs = ps.executeQuery();
rs.close();
}
ps.close();

Here it will be much more efficient. The statement sent to the database is parameterized using the '?' marker in the SQL. This means every iteration is sending the same statement to the database with different parameters for the "c=?" part. This allows the database to reuse the access plans for the statement and makes the program execute more efficiently inside the database. This basically let's your application run faster or makes more CPU available to users of the database.

Q. What does the method executeUpdate() return?

Ans. It returns the number of rows successfully inserted, updated or deleted. When it is used in conjunction with a select query, it returns a 0 since no rows are updated.

Q. Should we use column names or column index in the getXXX() methods of ResultSet interface?

Ans. Since the column indexes are subject to change due to a change in the database schema, it is advisable to use column names in these methods.

Q. What does the ResultSet.next() method return?

Ans. It returns false when the last record is reached and true otherwise.

Q. How many types of ResultSet objects are provided by JDBC?

Ans. JDBC classifies ResultSet objects on the basis of two characteristics:

a) Scroll Sensitivity
b) Updateability

a) Scroll Sensitivity

A scroll-insensitive resultset represents a static snapshot of the results when the query was made. On the other hand, a scroll-sensitive resultset is sensitive to the changes made to the data after the query has been executed, thus providing a dynamic view of the data as it changes.
The ResultSet class has two static data members; TYPE_SCROLL_INSENSITIVE and TYPE_SCROLL_SENSITIVE to denote scroll-insensitive and scroll-sensitive resultsets respectively.

c) Updateability

The contents of an updateable resultset can be changed whereas those of a non-updateable resultset cannot be changed. The ResultSet class has two static data members; CONCUR_READ_ONLY and CONCUR_UPDATABLE to denote non-updateable and updateable resultsets.


Q What is the sequence of steps followed by JDBC to communicate with a database?

Ans. JDBC follows the following sequence to talk to a database:

a) Create an instance of a JDBC driver.
b) Register the driver with the DriverManager class.
c) Open a database connection.
d) Submit a query.
e) Receive results.