jdbc interview questions and answers for experienced – Java Database Connectivity (JDBC) Interview Questions in Java

jdbc interview questions and answers for experienced: List of topic-wise frequently asked java interview questions with the best possible answers for job interviews.

Java Database Connectivity (JDBC) Interview Questions in Java

Question 1.
What is JDBC?
Answer:
JDBC is a set of Java API for executing SQL statements. This API consists of a set of classes and interfaces to enable programs to write pure Java Database, applications. JDBC is a layer of abstraction that allows users to choose between databases. It allows you to change to a different database engine and to write to a single API. JDBC allows you to write database applications in Java without having to concern yourself with the underlying details of a particular database.

JDBC standards for Java DataBase Connectivity. It is a specification given by Sun Microsystems and standards followed by X/Open SAG (SQL Access Group) CLI (Call Level Interface) to interact with the DB.

Java programming language methods. The JDBC API provides database-independent connectivity between the JAVA Applications and a wide range of tabular databases. JDBC technology allows an application component provider to:

• Perform connection and authentication to a database server
• Manage transactions
• Moves SQL statements to a database engine for preprocessing and execution
• Executes stored procedures
• Inspects and modifies the results from SELECT statements

Question 2.
What is JDBC? What are its advantages?
Answer:
It is an API . The latest version of JDBC api is (3.0). The JDBC 3.0 API is divided into two packages:

1. java.sql and
2. javax.sql.

Both packages are included in the J2SE and J2EE platforms.
Advantages:

• The JDBC API can be used to interact with multiple data sources in a distributed, heterogeneous environment.
• It can connect to any of the databases from the java language.
• It can switch over to any backend database without changing java code or by minute changes.

Question 3.
What is JDBC API and when do we use it?
Answer:
Java DataBase Connectivity API allows us to work with relational databases. JDBC API interfaces and classes are part of java.sql and javax.sql packages. We can use JDBC API to get the database connection, run SQL queries and stored procedures in the database server, and process the results. JDBC API is written in a way to allow loose coupling between our Java program and actual JDBC drivers that make our life easier in switching from one database to another database servers easily.

Question 4.
How many JDBC Drivers are there? What are they?
Answer:
There are 4 types of JDBC drivers.

1. JDBC-ODBC Bridge Driver (Type-1 driver)
2. Native API Partly Java Driver (Type-2 driver)
3. Net protocol pure Java Driver (Type-3 driver)
4. Native protocol Pure Java Driver (Type-4 driver)

Question 5.
What is the difference between JDBC and ODBC?
Answer:

1. OBDC is for Microsoft and JDBC is for Java applications.
2. ODBC can’t be directly used with Java because it uses a C interface.
3. ODBC makes use of pointers that have been removed totally from Java.
4. ODBC mixes simple and advanced features together and has complex options for simple queries. But JDBC is designed to keep things simple while allowing advanced capabilities when required.
5. ODBC requires manual installation of the ODBC driver manager and driver on all client machines. JDBC drivers are written in Java and JDBC code is automatically installable, secure, and portable on all platforms.
6. JDBC API is a natural Java interface and is built on ODBC. JDBC retains some of the basic features of ODBC.

Question 6.
What are the types of JDBC Driver Models and explain them?
Answer:
There are two types of JDBC Driver Models and they are:

1. Two-tier model and
2. Three-tier model

1. Two-tier models: In this model, Java applications interact directly with
the database. A JDBC driver is required to communicate with the particular database management system that is being accessed. SQL statements are sent to the database and the results are given to the user. This model is referred to as client/server configuration where the user is the client and the machine that has the database is called the server.

2. Three-tier model: A middle tier is introduced in this model. The functions of this model are:

• Collection of SQL statements from the client and handing it over to the database,
• Receiving results from the database to the client and
• Maintaining control over accessing and updating the above.

Question 7.
What are the steps involved in making a connection with a database or how do you connect to a database?
Answer:

a) Loading the driver: To load the driver, Class.forName( ) method is used.

Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
When the driver is loaded, it registers itself with java.sqLDriverManager class as an available database driver.

b) Making a connection with database: To open a connection to a given database,
DriverManager.getConnection( ) method is used.
Connection con = DriverManager.getConnection (“jdbc:odbc:somedb”, “user”, “password”);

c) Executing SQL statements: To execute a SQL query, java.sql.statements class is used.

createStatement( ) method of Connection to obtain a new Statement object. Statement stmt = con.createStatement( );

A query that returns data can be executed using the executeQuery( ) method of Statement. This method xecutes the statement and returns a java.sql.ResultSet that encapsulates the retrieved data:

ResultSet rs = stmt.executeQuery(“SELECT * FROM some table”);

d) Process the results : ResultSet returns one row at a time. Next( ) method of ResultSet object can be called to move to the next row. The getString() and getObject( ) methods are used for retrieving column values:

while (rs.next( ) ) {
        String event = rs.getString(“event”);
       object count = (Integer) rs.getObject(“count”);
}

Question 8.
What type of driver did you use in a project?
Answer:
JDBC-ODBC Bridge driver (is a driver that uses native(C language) libraries and makes calls to an existing ODBC driver to access a database engine).

Question 9.
What are the types of statements in JDBC?
Answer:
Statement — To be used createStatement( ) method for executing single SQL statement.
PreparedStatement — To be used prepared statements( ) method for executing same SQL statement over and over.
CallableStatement— To be used prepare Call( ) methods for multiple SQL statements over and over.

Question 10.
What is stored procedure?
Answer:
A stored procedure is a group of SQL statements that forms a logical unit and performs a particular task.
Stored Procedures are used to encapsulate a set of operations or queries to execute on the database. Stored procedures can be compiled and executed with different parameters and results and may have any combination of input/output parameters.

Question 11.
How to create and call stored procedures?
Answer:
To create stored procedures:

Create procedure name (specify in, out, and in-out parameters) 
         BEGIN
         Any multiple SQL statements;
         END;

         To call stored procedures:
         CallableStatement csmt = con.preparecall(“{call procedure name(?,?)}”);
         csmt.registerOutParameter(column no., data type); 
        csmt.setInt(column no., column name)
csmt.execute( );

Question 12.
What is connection pooling? Is it advantageous?
Answer:
A connection pooling is a methodology of reusing open connections. After finishing a transaction the connection is not closed but is put to the pool, and when a user requests a database connection the next time, he gets a pooled connection instead of a newly opened one. This greatly reduces the time to perform single database operations, because of avoiding heavyweight open/close operations. It’s recommended to use database connection pooling as widely as possible.

Question 13.
What is Connection Pooling?
Answer:
Connection pooling is a cache of database connections that are maintained in memory, so that the connections may be reuse.
Connection pooling is a place where a set of connections are kept and are used by the different programmers without creating connections to the database (it means there is a readymade connection available for the programmers where they can use). After using the connection he can send back that connection to the connection pool. The number of connections in the connection pool may vary.

Question 14.
How do you implement Connection Pooling?
Answer:
Connection Pooling can be implemented in the following way.

• A javax.sql.ConnectionPoolDataSource interface that serves as a resource manager connection factory for pooled java.sql.Connection objects. Each database vendors provide the implementation for that interface.
For example, the oracle vendor’s implementation is as follows:

oracle.jdbc.pool.oracleConnectionPoolDataSource Class.

• A javax.sql.PooledConnection interface encapsulates the physical connection for the database. Again, the vendor provides the implementation.

Question 15.
What Class.forName( ) method will do?
Answer:
Class.forName( ) is used to load the Driver class which is used to connect the application with the Database. Here Driver class is a Java class provided by the Database vendor.

Question 16.
What is the difference between JDBC 1.0 and JDBC 2.0?
Answer:
The JDBC 2.0 API includes many new features in the java.sql package as well as the new Standard Extension package, javax.sql. This new JDBC API moves Java applications into the world of heavy-duty database computing. New features in the java.sql package include support for SQL3 data types, scrollable result sets, programmatic updates, and batch updates. The new JDBC Standard Extension API, an integral part of Enterprise JavaBeans (EJB) technology, allows you to write distributed transactions that use connection pooling, and it also makes it possible to connect to virtually any tabular data source, including files and spreadsheets.
The JDBC 2.0 API includes many new features like

1. Scrollable result sets
2. Batch updates
3. Connection Pooling
4. Distributed transactions
5. set auto-commit ( )

Question 17.
Which methods and classes are used for connection pooling?
Answer:
JDBC provides an interface, which must be implemented by a JDBC driver supporting connection pooling. This interface is called ConnectionPoolDataSource. Most existing JDBC drivers (DataSource instances) implement this interface. It provides the following methods for obtaining PooledConnections:
PooledConnection getPooledConnection ( ) throws SQLException;
PooledConnection getPooledConnection (String user, String password) throws SQLException;
Such connections can be closed by issuing connections. close( ), but it’s only a logical closing, not physical. Physically, no connection is closed, it’s just returned to the pool.

Question 18.
What are the two major components of JDBC?
Answer:
One implementation interface for database manufacturers, the other implementation interface for application and applet writers.

Question 19.
What is the JDBC Driver interface?
Answer:
The JDBC Driver interface provides vendor-specific implementations of the abstract classes provided by the JDBC API. Each vendor’s driver must provide implementations of the java.sql.Connection, Statement, PreparedStatement, CallableStatement, ResultSet and Driver.

Question 20.
What are the common tasks of JDBC?
Answer:

• Create an instance of a JDBC driver or load JDBC drivers through jdbc.drivers
• Register a driver
• Specify a database
• Open a database connection
• Submit a query
• Receive results

Question 21.
What packages are used by JDBC?
Answer:
There are 8 packages: java.sql.Driver, Connection,Statement, PreparedStatement, CallableStatement, ResultSet, ResultSetMetaData, DatabaseMetaData.

Question 22.
What are the flow statements of JDBC?
Answer:
A URL string – ->getConnection- ->DriverManager- ->Driver- ->Connection- ->Statement–>executeQuery- ->ResultSet.

Question 23.
What are the steps involved in establishing a connection?
Answer:
This involves two steps: (1) loading the driver and (2) making the connection.

Question 24.
How can you load the drivers?
Answer:
Loading the driver or drivers you want to use is very simple and involves just one line of code. If, for example, you want to use the JDBC-ODBC Bridge driver, the following code will load it:
E.g.

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

Your driver documentation will give you the class name to use. For instance, if the class name is jdbc.DriverXYZ, you would load the driver with the following line of code: E.g. Class.forName(“jdbc.DriverXYZ”);

Question 25.
What Class.forName will do while loading drivers?
Answer:
It is used to create an instance of a driver and register it with the DriverManager. When you have loaded a driver, it is available for making a connection with a DBMS.

Question 26.
How can you make the connection?
Answer:
Establishing a connection is to have the appropriate driver connect to the DBMS. The following line of code illustrates the general idea:
E.g.
String url = “jdbc:odbc:Fred”;
Connection con = DriverManager.getConnection(url, “Fernanda”, “J8”);

Question 27.
How can you create JDBC statements?
Answer:
A Statement object is what sends your SQL statement to the DBMS. You simply create a Statement object and then execute it, supplying the appropriate execute method with the SQL statement you want to send. For a SELECT statement, the method to use is executeQuery. For statements that create or modify tables, the method to use is executeUpdate. E.g. It takes an instance of an active connection to create a Statement object. In the following example, we use our Connection object con to create the Statement object stmt:

Statement stmt = con.createStatement( );

Question 28.
How can you retrieve data from the ResultSet?
Answer:
First:
JDBC returns results in a ResultSet object, so we need to declare an instance of the class ResultSet to hold our results. The following code demonstrates declaring the ResultSet object rs.
E.g.
ResultSet rs = stmt.executeQuery(“SELECT COF_NAME, PRICE FROM COFFEES”);
Second:

String s = rs.getString(“COF_NAME”);

The method getString is invoked on the ResultSet object rs, so getString( ) will retrieve (get) the value stored in the column COF_NAME in the current row of rs.

Question 29.
What are the different types of Statements?
Answer:
1. Statement (use createStatement method) 2. PreparedStatement (Use prepareStatement method) and 3. CallableStatement (Use prepareCall)

Question 30.
How can you use PreparedStatement?
Answer:
This special type of statement is derived from the more general class, Statement. If you want to execute a Statement object many times, it will normally reduce execution time to use a PreparedStatement object instead. The advantage to this is that in most cases, this SQL statement will be sent to the DBMS right away, where it will be compiled. As a result, the PreparedStatement object contains not just an SQL statement, but an SQL statement that has been precompiled. This means that when the PreparedStatement is executed, the DBMS can just run the PreparedStatement’s SQL statement without having to compile it first.
E.g.
PreparedStatement updateSales = con.prepareStatement(“UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE?”);

Question 31.
How to call a Stored Procedure from JDBC?
Answer:
The first step is to create a CallableStatement object. As with Statement an and PreparedStatement objects, this is done with an open Connection object. A CallableStatement object contains a call to a stored procedure;
E.g.
CallableStatement cs = con.prepareCall(“{call SHOW_SUPPLIERS}”);
ResultSet rs = cs.executeQuery( );

Question 32.
How to Retrieve Warnings?
Answer:
SQLWarning objects are a subclass of SQLException that deal with database access warnings. Warnings do not stop the execution of an application, as exceptions do; they simply alert the user that something did not happen as planned. A warning can be reported on a Connection object, a Statement object (including PreparedStatement and CallableStatement objects), or a ResultSet object. Each of these classes has a get warnings method, which you must invoke in order to see the first warning reported on the calling object E.g.

SQLWarning warning = stmt.getWarnings( );
if (warning != null) {
while (warning != null) {
System.out.println(“Message: “ + warning.getMessage( ));
System.out.println(“SQLState: “ + warning.getSQLState( ));
System.out.print(“vendor error code: “);
System.out.println(warning.getErrorcode( )) ;
warning = warning.getNextwarning( );

}
}

Question 33.
How to Make Updates to Updatable Result Sets?
Answer:
Another new feature in the JDBC 2.0 API is the ability to update rows in a result set using methods in the Java programming language rather than havings to send an SQL command. But before you can take advantage of this capability, you need to create a ResultSet object that is updatable. In order to do this, you supply the ResultSet constant CONCUR_UPDATABLE to the createStatement method.
E.g.
Connection con = DriverManager.getConnection(“jdbc:mySubprotocol:mySubNa me”);
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet. CONCURJJPD ATABLE);
ResultSet uprs = (“SELECT COF_NAME, PRICE FROM COFFEES”);

Question 34.
How to Interact with DB?
Answer:
Generally, every DB vendor provides a User Interface through which we can easily execute SQL queries and gets the result (For example Oracle Query Manager for Oracle, and TOAD (www.quest.com) tool common to all the databases). And these tools will help DB developers to create databases. But as a programmer we want to interact with the DB dynamically to execute some SQL queries from our application (Any application like C, C++, JAVA, etc), and for this requirement, DB vendors provide some Native Libraries (Vendor Specific) using this we can interact with the DB i.e. If you want to execute some queries on Oracle DB, oracle vendor provides an OCI (Oracle Call Interface) Libraries to perform the same.

Question 35.
What for JDBC?
Answer:
As we have studied ODBC and its advantages and came to know that it provides a common API to interact with any DB which has an ODBC Service Provider’s Implementation written in Native API that can be used in your applications.
If an application wants to interact with the DB then the options which have been explained up to now in this book are:

1. Using Native Libraries given by the DB vendor
2. Using ODBC API

And we have listed their Advantages and Disadvantages.
But if the application is a JAVA application then the above-given options are not recommended to be used due to the following reasons
1. Native Libraries given by DB vendor

• The application becomes vendor dependent and
• The application has to use JNI to interact with Native Lib which may cause serious problems for Platform Independency in our applications.

2. And the second option given was using ODBC API which can solve the l.a problem but again this ODBC API is also a Native API, so we have to use JNI in our Java applications which leads to the l.b described problem. And the answer for these problems is JDBC (Java DataBase Connectivity) which provides a common Java API to interact with any DB.

Question 36.
What is a transaction?
Answer:
The transaction is collection of logical operation that performs a task. The transaction should have ACID properties.
A for Atomicity
C for Consistency
I for Isolation
D for Durability.
A transaction can be termed as any operation such as storing, retrieving, updating, or deleting records in the table that hits the database.

Question 37.
What is the purpose of setAutoCommit( )
Answer:
It is set as
ConnectionObject.setAutoComit( );
After any updates, the program cannot be affected by the database. We have committed the transactions. For this purpose, we can set the AutoCommit flag to Connection Object.

Question 38.
What is stored procedure? How do you create stored procedures?
Answer:
Stored procedures are a group of SQL statements that performs a logical unit and performs a particular task.
Stored procedures are used to encapsulate a set of operations or queries to execute on data.
A stored procedure is a stored program in the database, PL/SQL program is a Stored Procedure. Stored Procedures can be called from java by CallableStatement

A precompiled collection of SQL statements stored under a name and processed as a unit.
Stored procedures can:

1. Accept input parameters and return multiple values in the form of output parameters to the calling procedure or batch.
2. Contain programming statements that perform operations in the database, including calling other procedures.
3. Return a status value to a calling procedure or batch to indicate success or failure (and the reason for failure).

Question 39.
What are batch updates?
Answer:
The batch Update facility allows multiple update operations to be submitted to a database for processing at once. Using batch updates will improve the performance.

Question 40.
What is the difference between Resultset and Rowset
Answer:
A RowSet is a disconnected, serializable version of a JDBC ResultSet.
The RowSet is different than other JDBC interfaces in that you can write a RowSet to be vendor-neutral. A third party could write a RowSet implementation that could be used with any JDBC-compliant database. The standard implementation supplied by Sun uses a ResultSet to read the rows from a database and then stores those rows as Row objects in a Vector inside the RowSet. In fact, a RowSet implementation could be written to get its data from any source. The only requirement is that the RowSet acts as if it was a ResultSet. Of course, there is no reason that a vendor couldn’t write a RowSet implementation that is vendor-specific.
The standard implementations have been designed to provide a fairly good range of functionality. The implementations provided are:

CachedRowSetlmpI – This is the implementation of the RowSet that is closest to the definition of RowSet functionality that we discussed earlier. There are two ways to load this RowSet. The execute ( ) method will load the RowSet using a Connection object. The populate( ) method will load the RowSet from a previously loaded ResultSet.

WebRowSetlmpl – This is very similar to the CachedRowSetlmpI (it is a child class) but it also includes methods for converting the rows into an XML document and loading the RowSet with an XML document. The XML document can come from any Stream or Reader/Writer object. This could be especially useful for Web Services.

JdbcRowSetlmpl – This is a different style of implementation that is probably less useful in normal circumstances. The purpose of this RowSet is to make a ResultSet look like a JavaBean. It is not serializable and must maintain a connection to the database.
The remaining two implementations are used with the first three implementations:

FilteredRowSetlmpl – This is used to filter data from an existing RowSet. The filter will skip records that don’t match the criteria specified in the filter when a next( ) is used on the RowSet.

JoinRowSetlmpl – This is used to simulate a SQL join command between two or more RowSet objects.

Question 41.
What are the steps for connecting to the database using JDBC Answer: Using DriverManager:
1. Load the driver class using class.forName(driver class) and Class.forName( ) loads the driver class and passes the control to DriverManager class
2. DriverManager.getConnectionO creates the connection to the database

Using DataSource:
DataSource is used instead of DriverManager in Distributed Environment with the help of JNDI.

• Use JNDI to lookup the DataSource from the Naming service server.
• DataSource. getConnection method will return the Connection object to the database.

Question 42.
What are the two major components of JDBC?
Answer:
One implementation interface for database manufacturers, the other implementation interface for application and applet writers.

Question 43.
What is the JDBC Driver interface?
Answer:
The JDBC Driver interface provides vendor-specific implementations of the abstract classes provided by the JDBC API. Each vendor’s driver must provide implementations of the java.sql. Connection, Statement, PreparedStatement,
CallableStatement, ResultSet, and Driver.

Question 44.
What are the common tasks of JDBC?
Answer:
Create an instance of a JDBC driver or load JDBC drivers through jdbc. drivers
Register a driver
Specify a database
Open a database connection
Submit a query
Receive results

Question 45.
What are the steps involved in establishing a connection?
Answer:
This involves two steps:

1. loading the driver and
2. making the connection.

Question 46.
What Class.forName will do while loading drivers?
Answer:
It is used to create an instance of a driver and register it with the DriverManager. When you have loaded a driver, it is available for making a connection with a DBMS.

Question 47.
What are the different types of Statements?
Answer:

1. Create Statement: For a Simple statement used for the static query.
2. Prepared Statement: For a runtime / dynamic query.Where String is a dynamic query you want to execute
3. Callable Statement (Use prepare all): //For Stored procedure Callable statement, where sql is stored procedure.

try {
Connection conn = DriverManager.getConnection(“URL”,’USER”.”PWD”);
statement stmt = conn.createstatement( );
Preparedstatement pstmt = conn.preparestatementfstring sql);
CallableStatement cstmt = conn.prepareCall(String sql);
}
catch (SQLException ee)
{
ee. pri ntStackTrace( );
}

Don’t forget all the above statements will throw the SQLException, so we need to use try-catch for the same to handle the exception.

Question 48.
How can you use PreparedStatement?
Answer:
This special type of statement is derived from the more general class, Statement. If you want to execute a Statement object many times, it will normally reduce execution time to use a Prepared statement object instead. The advantage to this is that in most cases, this SQL statement will be sent to the DBMS right away, where it will be compiled. As a result, the Prepared statement object contains not just an SQL statement, but an SQL statement that has been precompiled. This means that when the Prepared statement is executed, the DBMS can just run the PreparedStatement’s SQL statement without having to compile it first.
E.g. Preparedstatement updateSales = con.prepareStatement(“UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ?”);

Question 49.
How to call a Stored Procedure from JDBC?
Answer:
The first step is to create a CallableStatement. object. As with Statement and Prepared statement objects, this is done with an open Connection object. A

CaUableStatement object contains a call to a stored procedure;
E.g.
CaUableStatement cs = con.prepareCall(“{call SHOW_SUPPLIERS}”);
ResultSet rs = cs.executeQuery( );

Question 50.
Explain about JDBC-ODBC driver (Type-1)? When this type of driver is used?
Answer:
In this mechanism, the flow of execution will be
Java code(JDBC API)<———->JDBC-ODBC bridge driver<———->ODBC API<———–>ODBC Layer<———->DataBase
This type of JDBC Drivers provides a bridge between JDBC API and ODBC API.

Type-1: JDBC-ODBC Bridge Driver:
Since ODBC is written in C-language using pointers, so JAVA doesn’t support pointers, a java program can’t communicate directly with the Database. The JDBC- ODBC bridge driver translates JDBC API calls to ODBC API calls.

Architecture

This type of Driver is designed to convert the JDBC request call to ODBC call and ODBC response call to JDBC call.
The JDBC uses this interface in order to communicate with the database, so neither the database nor the middle tier needs to be Java compliant. However, ODBC binary code must be installed on each client machine that uses this driver. This bridge driver uses a configured data source.

Advantages

• Simple to use because ODBC drivers come with DB installation/Microsoft front/ back office product installation
• JDBC ODBC Drivers comes with JDK software

Disadvantages

• More layers between the application and DB. And more API conversions lead to the downfall of the performance.
• Slower than the type-2 driver

Where to use it?
This type of driver is generally used at the development time to test your applications. Because of the disadvantages listed above, it is not used at production time. But if we are not available with any other type of driver implementations for a DB then we are forced to use this type of driver (for example Microsoft Access).

Examples of this type o£ drivers
JdbcOdbcDriver from sun
Sun’s JdbcOdbcDriver is one of the type-1 drivers and comes along with sun j2sdk (JDK).

Setting environment to use this driver

1. Software
   ODBC libraries have to be installed.
2. classpath
   No additional classpath settings are required apart from the runtime jar (c:\ j2sdkl.4\jre\lib\rt.jar) which is default configured.
3. Path No additional path configuration is required.

How to use this driver
• Driver class name □ sun.jdbc.odbc.JdbcOdbcDriver
• Driver URL □ dbc:odbc:<DSN>
here <DSN> (Data Source Name) is an ODBC data source name that is used by the ODBC driver to locate one of the ODBC Service Provider implementation APIs which can, in turn, connect to DB.

Steps to create <DSN>

• Run ‘Data Sources (ODBC)’ from Control PanelXAdministrative Tools\ (for Windows 2000 server/2000 professional/XP) run ‘ODBC Data Sources’ from Control Panel\

• Click on Add button available on the above-displayed screen. This opens a new window titled ‘Create New Data Source’ which displays all the available DB’s label DB’s ODBC drivers currently installed on your system.

• Select the suitable driver and click on Finish

• Give the required info to the driver (like username, service id, etc)

Question 51.
Explain about Type-2 driver? When this type of driver is used?
Answer:
The Drivers which are written in Native code will come into this category In this mechanism the flow of Execution will be
Java code (JDBC API) <——–>Type-2 driver (JDBC driver) <——–>Native API
(vendor specific) <——->Database
When a database call is made using JDBC, the driver translates the request into vendor-specific API calls.

The database will process the request and sends the results back through the Native API, which will forward them back to the JDBC driver. The JDBC driver will format the results to conform to the JDBC standard and return them to the application.

Type-2 : Native API Partly JAVA Driver (Thick Driver) :
JDBC Database calls are translated into Vendor-specific API calls. The database will process the request and send the results back through API to JDBC Driver – this will translate the results to the JDBC standard and return them to the Java application.
The Vendor-specific language API must be installed on every client that runs the JAVA application.
Architecture:

This driver converts the JDBC call given by the Java application to a DB-specific native call (i.e. to C or C++) using JNI (Java Native Interface).
Advantages: Faster than the other types of drivers due to native library participation in socket programming.
Disadvantage: DB-specific native client library has to be installed in the client machine.

• Preferably work in local network environment because network service name must be configured in client system ‘

Where to use it?
This type of driver is suitable to be used in server-side applications.
Not recommended to use with the applications using two tire models (i.e. the client and database layers) because in this type of model client used to interact with DB using the driver and in such a situation the client system should have the DB native library.
Examples of this type of drivers

1. OCI 8 (Oracle Call Interface) for Oracle implemented by Oracle Corporation.
Setting environment to use this driver

♦ Software: Oracle client software has to be installed in the client machine
♦ classpath -> %ORACLE_HOME%\ora8l\jdbc\lib\classeslll.zip
♦ path %ORACLE_HOME%\ora81\bin How to use this driver?
♦ Driver class name -> oracle.jdbc.driver.OracleDriver
♦ Driver URL -> jdbc:oracle:oci8:@TNSName

Note: TNS Names of Oracle is available in Oracle installed folder %ORACLE_ HOME%\Ora8 l\network\admin\tnsnames.ora

2. Weblogic Jdriver for Oracle implemented by BEA Weblogic:

Setting environment to use this driver
♦ Oracle client software has to be installed in the client machine
♦ weblogic dll’s has to be set in the path
♦ classpath -> d:\bea\weblogic700\server\lib\webiogic.jar
♦ path -> %ORACLE_HOME%\ora81\bin; d:\bea\weblogic700\server\ bin\<subfolder><sub folder> is
♦ oci817_8 if you are using Oracle 8.1.x
♦ oci901_8 for Oracle 9.0.x
♦ oci920_8 for Oracle 9.2.x

How to use this driver
♦ Driver class name -> weblogic.jdbc.oci.Driver
♦ Driver URL-> jdbc:weblogic:oracle:HostName

Question 52.
Explain about Type-3 driver? When this type of driver is used?
Answer:
In this mechanism, the flow of Execution will be Java code (JDBC API) <——-> JDBC driver <——-> JDBC driver server <——>
Native driver <——–> Database
The Java Client Application sends the calls to the Intermediate data access server(JDBC driver server)
The middle tier then handles the request using another driver (Type-II or Type-IV drivers) to complete the request.

Type-3 Intermediate Database Access Server:

Type-3 Driver uses an Intermediate (middleware) database driver that has the ability to connect multiple JAVA clients to multiple database servers. A client connects to the database server via an Intermediate server component (such as a listener) that acts as a gateway for multiple database servers.
Bea WebLogic includes Type-3 Driver.
Architecture:

This type of driver’s responsibility is to convert JDBC call to Net protocol (Middleware listener dependent) format and redirect the client request to Middleware Listener and middleware listener, in turn, uses type-1, type-2, or type-4 driver to interact with DB.

Advantages:

• It allows flexibility in the architecture of the application.
• In absence of a DB vendor-supplied driver we can use this driver
• Suitable for Applet clients to connect DB because it uses Java libraries for communication between client and server.

Disadvantages:

• From client to server communication this driver uses Java libraries, but from server to DB connectivity this driver uses native libraries, hence a number of API conversions and a layer of interactions increases to perform operations that lead to a performance deficit.
• Third-party vendor dependent and this driver may not provide a suitable driver for all DBs

Where to use it?
• Suitable for Applets when connecting to databases
Examples of this type of driver:
1. IDS Server (Intersolv) driver available for most of the Databases
Setting environment to use this driver
• Software: IDS software is required to be downloaded from the following URL
[http://www.idssoftware.com/idsserver.html -> Export Evaluation]
• classpath -> C:\IDSServer\classes\jdkl4drv.jar
• path ->
How to use this driver?
• Driver class name -> ids.sql.IDSDriver
• Driver URL jdbc:ids://localhost:12/conn?dsn=’IDSExamples’
Note: DSN Name must be created in ServerDSN

Question 53.
Explain about Type-4 driver? When this type of driver is used?
Answer:
This is a pure java driver (alternative to Type-II drivers).
In this mechanism, the flow of Execution will be
Java code (JDBC API) <——–>Type-4 driver (JDBC driver) <——–> Database
These types of drivers convert the JDBC API calls to direct network calls using vendor-specific networking protocol by making direct socket connections with the database.
Examples of this type of driver are

1. Tabular Data Stream for Sybase
2. Oracle Thin JDBC driver for Oracle

Type-4 Pure JAVA Driver (Thin driver):
Type-4 Driver translates JDBC-API calls to direct network calls using vendor-specific networking protocols by making direct server connections with the database.
Architecture

This type of driver converts the JDBC call to a DB-defined native protocol.

Advantage

• Type-4 driver is simple to deploy since there are No client native libraries required to be installed in the client machine
• Comes with most of the Databases

Disadvantages:

• Slower in execution compared with other JDBC Driver due to Java libraries are used in socket communication with the DB

Where to use?

• This type of driver is suitable to be used with server-side applications, client-side applications,s, and Java Applets also.
Examples of this type of drivers

1) Thin driver for Oracle implemented by Oracle Corporation

Setting environment to use this driver
♦ classpath -> %ORACLE_HOME%\ora81\jdbc\hb\classeslll.zip

How to use this driver
♦ Driver class name oracle.jdbc.driver.OracleDriver
♦ Driver URL -> jdbc:oracle:thifi:@HostName:<port no>:<SID>

<port no> ->1521
<SID> -> ORCL

2) MySQL Jconnector for MySQL database

Setting environment to use this driver
♦ classpath -> C:\mysql\mysql-connector-java-3.0.8-stable\mysql- connector-java-3.0.8-stable-bin.jar
How to use this driver
♦ Driver class name -> com.mysqbjdbc.Driver
♦ Driver URL -> jdbc:mysql:///test

Question 54.
How JDBC API is common to all the Databases and also to all drivers?
Answer:
Fine! The answer is JDBC API uses Factory Method and Abstract Factory Design pattern implementations to make API common to all the Databases and Drivers. In fact, most of the classes available in JDBC API are interfaces, where Driver vendors must provide the implementation for the above-said interfaces.

Question 55.
Then how JDBC developers can remember or find out the syntaxes of vendor-specific classes?
Answer:
No! developer need not have to find out the syntaxes of vendor-specific implementations why because DriverManager is one named class available in JDBC API into which if you register Driver class name, URL, user, and password, DriverManager class in-turn brings us one Connection object.

Question 56.
Why most of the classes given in JDBC API are interfaces?
Answer:
Abstract class forces all subclasses to implement common methods whichever are required implementations. Only abstract methods and classes can do this job. That’s why most part of the JDBC API is a formation of interfaces.

Question 57.
Which Driver is preferable for using JDBC API in Applets?
Answer:
Type-4 Drivers.

Question 58.
Write the Syntax of the URL to get a connection? Explain?
Answer:
Syntax: jdbc:<subprotocal>:<subname>
jdbc: is a protocol.This is the only allowed protocol in JDBC.
<subprotocal>: The subprotocol is used to identify a database driver, or the name of the database connectivity mechanism, chosen by the database driver providers.
<subname>: The syntax of the surname is driver-specific. The driver may choose any syntax appropriate for its implementation
Ex: jdbc:odbc:dsn
jdbc:oracle:oci8:@ database name.
jdbc:orale:thin:@ database name:port number:SID

Question 59.
How do u Load a driver?
Answer:
Using Driver Class.forName(java.lang.String driverclass) or registerDriver(Driver driver) .

Question 60.
What are the types of resultsets in JDBC3.0? How you can retrieve information on the resultset?
Answer:
ScroUableResultSet and ResultSet. We can retrieve information of resultset by using java.sql.ResultSetMetaData interface. You can get the instance by calling the method getMetaData( ) on the ResulSet object.

Question 61.
Can java objects be stored in the database? How?
Answer:
Yes. We can store java objects, BY using setObject( ),setBlob( ) and setClob( ) methods in PreparedStatement

Question 62.
What do u mean by isolation level?
Answer:
Isolation means that the business logic can proceed without consideration for the other activities of the system.

Question 63.
How do u set the isolation level?
Answer:
By using set transaction isolation(int level) in java.sql.Connection interface. level MEAnswer:-
static final int TRANSACTION_READ_UNCOMMITTED //cannot prevent any reads.
static final int TRANSACTION_READ_COMMITTED //prevents dirty reads static final int TRANSACTION_REPEATABLE_READ //prevents dirty reads & non-repeatable read.
static final int TRANSACTION_SERIALIZABLE //prevents dirty reads, non-repeatable read & phantom read.
These are the static final fields in java.sql.Connection interface.

Question 64.
What is a dirty read?
Answer:
A Dirty read allows a row changed by one transaction to be read by another transaction before any change in the row has been committed. This problem can be solved by setting the transaction isolation level to TRANSACTION_READ_ COMMITTED

Question 65.
What is a non-repeatable read?
Answer:
A non-repeatable read is where one transaction reads a row, a second transaction alters or deletes the row, and the first transaction re-reads the row, getting different values the second time. This problem can be solved by setting the transaction isolation level to TRANSACTION_REPEATABLE_READ

Question 66.
What is phantom read?
Answer:
A phantom read is where one transaction reads all rows that satisfy a WHERE condition, a second transaction inserts a row that satisfies that WHERE condition and the first transaction re-reads for the same condition, retrieving the additional ‘phantom’ row in the second read This problem can be solved by setting the transaction isolation level to TRANSACTION_SERIALIZABLE

Question 67.
What is the difference between java.sql.Statement & java.sql. PreparedStatement?
Answer:
write the appropriate situations to use these statements?

Question 68.
How to retrieve the information about the database?
Answer:
We can retrieve the info about the database by using interface java.sql. DatabaseMetaData we can get this object by using getMetaData( ) method in Connection interface.

Question 69.
What are the Different types of exceptions in jdbc?
Answer:
BatchUpdateException
DataTruncation
SQLException
SQLWarning

Question 70.
How to execute no of queries at one go?
Answer:
By using a batch update (ie throw addBAtch( ) and executeBatch( )) in java.sql-Statement interface,or by using procedures.

Question 71.
What are the advantages of the connection pool?
Answer:
Performance

Question 72.
In which interface the methods commit( ) & rollback( ) are defined ?
Answer:
java.sql.Connection interface

Question 73.
How to store images in a database?
Answer:
Using binary streams (i.e. getBinaryStream( ), setBinaryStream( )). But it is not visible in the database; it is stored in form of bytes, to make it visible we have to use anyone frontend tool.

Question 74.
How to check the null value in JDBC?
Answer:
By using the method was null( ) in ResultSet, it returns a boolean value. Returns, whether the last column read, had a value of SQL NULL.
Note that you must first call one of the getXXX( ) methods on a column to try to read its value and then call the method was null to see if the value read was SQL NULL.

Question 75.
Give one example of a static Synchronized method in JDBC API?
Answer:
get connection( ) method in DriverManager class. Which is used to get the object of the Connection interface.

Question 76.
What is a Connection?
Answer:
Connection is an interface that is used to make a connection between client and Database (i.e. opening a session with a particular database).

Question 77.
What is the difference between execute( ), executeUpdate( ) and executeQuery( ) ? Where we will use them?
Answer:
execute( ) method returns a boolean value (ie if the given query returns a resultset then it returns true else false), so depending upon the return value we can get the ResultSet object (getResultsetO)or we can know how many rows have been affected by our query (getUpdateCount( ))-That is we can use this method for Fetching queries and Non-Fetching queries. Fetching queries are the queries that are used to fetch the records from the database (i.e. which returns a resultset) ex: Select * from emp.

Non-Fetching queries are the queries that are used to update,insert, create or
delete the records from the database
Ex: update emp set sal=10000 where empno=7809.
execute Update( ) method is used for non-fetching queries, which returns an int value. executeQuery( ) method is used for fetching queries which return ResulSet object.Which contains methods to fetch the values.

Question 78.
What is the difference between JDBC and ODBC?
Answer:
a) OBDC is for Microsoft and JDBC is for Java applications.
b) ODBC can’t be directly used with Java because it uses a C interface.
c) ODBC makes use of pointers that have been removed totally from Java.
d) ODBC mixes simple and advanced features together and has complex options for simple queries. But JDBC is designed to keep things simple while allowing advanced capabilities when required.
e) ODBC requires manual installation of the ODBC driver manager and driver on all client machines. JDBC drivers are written in Java and JDBC code is automatically installable, secure, and portable on all platforms.
f) JDBC API is a natural Java interface and is built on ODBC. JDBC retains some of the basic features of ODBC.

Question 79.
What are the steps involved in making a connection with a database or how do you connect to a database?
Answer:
a) Loading the driver: To load the driver, Class.forName() method is used. Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
When the driver is loaded, it registers itself with the java.sql.DriverManager class as an available database driver.

b) Making a connection with database: To open a connection to a given database, DriverManager.getConnection() method is used.
Connection con = DriverManager.getConnection (“jdbc:odbc:somedb”. “user”, “password”);

c) Executing SQL statements: To execute a SQL query, java.sql.Statement class is used.
createStatement() method of Connection to obtain a new Statement object. Statement stmt = con.createStatement();

d) A query that returns data can be executed using the executeQuery() method of Statement. This method executes the statement and returns a java.sql. ResultSet that encapsulates the retrieved data:
ResultSet rs = stmt.executeQuery(“SELECT * FROM some table”);

e) Process the results: ResultSet returns one row at a time. next( ) method of ResultSet object can be called to move to the next row. The getString() and getObject() methods are used for retrieving column values:

while(rs.next( ) ) {
String event = rs.getString(“event”);
object count = (integer) rs.getobject(“count”);
}

Question 80.
What type of driver did you use in the project?
Answer:
JDBC-ODBC Bridge driver (is a driver that uses native(C language) libraries and makes calls to an existing ODBC driver to access a database engine).

Question 81.
What are the types of statements in JDBC?
Answer:
Statement: To be used createStatementO method for executing single SQL statement
PreparedStatement: To be used preparedStatement( ) method for executing same SQL statement over and over
CallableStatement: To be used prepareCall( ) method for multiple SQL statements over and over

Question 82.
What is stored procedure?
Answer:
A stored procedure is a group of SQL statements that forms a logical unit and performs a particular task. Stored Procedures are used to encapsulate a set of operations or queries to execute on the database. Stored procedures can be compiled and executed with different parameters and results and may have any combination of input/output parameters.

Question 83.
What’s the JDBC 2.0 API?
Answer:
The JDBC 2.0 API is the latest update of the JDBC API. It contains many new features, including scrollable result sets and the new SQL: 1999 (formerly SQL
3) data types. There are two parts to the JDBC 2.0 API: the JDBC 2.0 core API (the java.sql package), which is included in the Java TM 2 SDK, Standard Edition the JDBC 2.0 Optional Package API (the javax.sql package), which is available separately or as part of the Java 2 SDK, Enterprise Edition.

Question 84.
Does the JDBC-ODBC Bridge support the new features in the JDBC 2.0 API?
Answer:
No, the JDBC-ODBC Bridge that is included in the Java 2 Platform initial release does not support the new features in the JDBC 2.0 API. However, Sun and Merant are working to produce a new version of the Bridge that does support the new features. Note that we do not recommend using the Bridge except for experimental purposes or when you have no other driver available.

Question 85.
Can the JDBC-ODBC Bridge be used with applets?
Answer:
Use of the JDBC-ODBC bridge from an untrusted applet running in a browser, such as Netscape Navigator, isn’t allowed. The JDBC-ODBC bridge doesn’t allow untrusted code to call it for security reasons. This is good because it means that an untrusted applet that is downloaded by the browser can’t circumvent Java security by calling ODBC. Remember that ODBC is native code, so once ODBC is called, the Java programming language can’t guarantee that a security violation won’t occur. On the other hand, Pure Java JDBC drivers work well with applets.

They are fully downloadable and do not require any client-side configuration. Finally, we would like to note that it is possible to use the JDBC-ODBC bridge with applets that will be run in applet viewer since applet viewer assumes that applets are trusted. It is also possible to use the JDBC-ODBC bridge with applets that are run in the HotJavaTM browser (available from Java Software) since Hot Java provides an option to turn off applet security. In general, it is dangerous to turn applet security off, but it may be appropriate in certain controlled situations, such as for applets that will only be used in a secure intranet environment. Remember to exercise caution if you choose this option, and use an all-Java JDBC driver whenever possible to avoid security problems.

Question 86.
How do I start debugging problems related to the JDBC API?
Answer:
A good way to find out what JDBC calls are doing is to enable JDBC tracing. The JDBC trace contains a detailed listing of the activity occurring in the system that is related to JDBC operations.

If you use the DriverManager facility to establish your database connection, you use the DriverManager.setLogWriter method to enable tracing of JDBC operations. If you use a DataSource object to get a connection, you use the DataSource. setLogWriter method to enable tracing. (For pooled connections, you use the ConnectionPoolDataSource.setLogWriter method, and form connections that can participate in distributed transactions, you use the XADataSource.setLogWriter method.)

Question 87.
How can I use the JDBC API to access a desktop database like Microsoft Access over the network?
Answer:
Most desktop databases currently require a JDBC solution that uses ODBC underneath. This is because the vendors of these database products haven’t implemented all Java JDBC drivers.

The best approach is to use a commercial JDBC driver that supports ODBC and the database you want to use. See the JDBC driver’s page for a first of available JDBC drivers.

The JDBC-ODBC bridge from Sun’s Java Software does not provide network access to desktop databases by itself. The JDBC-ODBC bridge loads ODBC as a local DLL, and typical ODBC drivers for desktop databases like Access aren’t networked. The JDBC-ODBC bridge can be used together with the RMI-JDBC bridge, however, to access a desktop database like Access over the net. This RMI-JDBC-ODBC solution is free.

Question 88.
Does the JDK include the JDBC API and the JDBC-ODBC Bridge?
Answer:
Yes, the JDK 1.1 and the Java 2 SDK, Standard Edition (formerly known as the JDK 1.2), contain both the JDBC API and the JDBC-ODBC Bridge. The Java 2 SDK, Standard Edition, contains the JDBC 2.0 core API, which is the latest version. It does not include the JDBC 2.0 Optional Package, which is part of the Java 2 SDK, Enterprise Edition, or which you can download separately.
Note that the version of the JDBC API and the JDBC-ODBC Bridge provided for separate download on the JDBC download page are only for use with the JDK 1.0.2.

Question 89.
Are there any ODBC drivers that do not work with the JDBC-ODBC Bridge?
Answer:
Most ODBC 2.0 drivers should work with the Bridge. Since there is some variation in functionality between ODBC drivers, the functionality of the bridge may be affected. The bridge works with popular PC databases, such as Microsoft Access and FoxPro.

Question 90.
Does the JDBC-ODBC Bridge work with Microsoft J++?
Answer:
No, J++ does not support the JDBC-ODBC bridge since it doesn’t implement the Java Native Interface (JNI). Any all-Java JDBC driver should work with J++, however.

Question 91.
What causes the “No suitable driver” error?
Answer:
“No suitable driver” is an error that usually occurs during a call to the DriverManager.getConnection method. The cause can be failing to load the appropriate JDBC drivers before calling the getConnection method, or it can be specifying an invalid JDBC URL—one that isn’t recognized by your JDBC driver. Your best bet is to check the documentation for your JDBC driver or contact your JDBC driver vendor if you suspect that the URL you are specifying is not being recognized by your JDBC driver.

In addition, when you are using the JDBC-ODBC Bridge, this error can occur if one or more of the shared libraries needed by the Bridge cannot be loaded. If you think this is the cause, check your configuration to be sure that the shared libraries are accessible to the Bridge.

Question 92.
Why isn’t the java.sql.DriverManager class being found?
Answer:
This problem can be caused by running a JDBC applet in a browser that supports the JDK 1.0.2, such as Netscape Navigator 3.0. The JDK 1.0.2 does not contain the JDBC API, so the DriverManager class typically isn’t found by the Java virtual machine running in the browser.
Here’s a solution that doesn’t require any additional configuration of your web clients. Remember that classes in java.* packages cannot be downloaded by most browsers for security reasons. Because of this, many vendors of all-Java JDBC drivers supply versions of the java.sql.* classes that have been renamed to jdbc. sql.*, along with a version of their driver that uses these modified classes.

If you import jdbc.sql.* in your applet code instead of java.sql.*, and add the jdbc.sql.* classes provided by your JDBC driver vendor to your applet’s codebase, then all of the JDBC classes needed by the applet can be downloaded by the browser at run time, including the DriverManager class.

This solution will allow your applet to work in any client browser that supports the JDK 1.0.2. Your applet will also work in browsers that support JDK 1.1, although you may want to switch to the JDK 1.1 classes for performance reasons. Also, keep in mind that the solution outlined here is just an example and that other solutions are possible.

Question 93.
Why doesn’t calling the method Class.forName load my JDBC driver?
Answer:
There is a bug in the JDK 1.1.x that can cause the method Class.forName to fail. A workaround is to explicitly call the method DriverManager.register driver(new YourDriverClassO). The exact problem in the JDK is a race condition in the class loader that prevents the static section of code in the driver class from executing and registering the driver with the DriverManager.

Question 94.
Why do the java.sql and java. math packages fail to download java.* packages? Is there a workaround?
Answer:
For security reasons, browsers will not download java.* packages. In order to use the JDBC API with browsers that have not been upgraded to JDK1.1 or beyond, we recommend that the java.sql and java. math packages are renamed jdbc. sql amkjdbc.math. Most vendors supplying JDBC technology-enabled drivers that are written purely in the Java programming language already provide versions of these renamed packages. When JDK 1.1 support has been added to your browser, you should convert your applets back to java.* package names.

Question 95.
Why is the precision of java. math.BigDecimal limited to 18 digits in the JDK 1.0.2 add-on version of the JDBC API?
Answer:
In JDK 1.1, java. math.BigInteger is implemented in C. It supports a precision of thousands of digits. The same is true for BigDecigmal.
The version of Biglnteger provided with the JDK 1.0.2 add-on version of the JDBC API is a simplified version written in the Java programming language, and it is limited to 18 digits. Because the implementation of BigDecimal is based on Biglnteger, it also is limited to this precision.
In the JDBC 2.0 API, you can use a new version of the method ResultSet.getBigDecimal that does not take a scale parameter and returns a BigDecimal with full precision.

Question 96.
Can the JDBC API be added to JDK 1.0.2?
Answer:
Yes. Download the JDBC 1.22 API from the JDBC download page and follow the installation instructions in the release notes. If you are using any version of the JDK from 1.1 on, the JDBC API is already included, and you should not download the JDBC 1.22 API.

Question 97.
How do I retrieve a whole row of data at once, instead of calling an individual ResultSet?getXXX method for each column?
Answer:
The ResultSet.getXXX methods are the only way to retrieve data from a ResultSet object, which means that you have to make a method call for each column of a row. It is unlikely that this is the cause of a performance problem, however, because it is difficult to see how a column could be fetched without at least the cost of a function call in any scenario. We welcome input from developers on this issue.

Question 98.
Why does the ODBC driver manager return ‘Data source name not found and no default driver specified Vendor: O’
Answer:
This type of error occurs during an attempt to connect to a database with the bridge. First, note that the error is coming from the ODBC driver manager.
This indicates that the bridge-which is a normal ODBC client-has successfully called ODBC, so the problem isn’t due to native libraries not being present. In this case, it appears that the error is due to the fact that an ODBC DSN (data source name) needs to be configured on the
client machine. Developers often forget to do this, thinking that the bridge will magically find the DSN they configured on their remote server machine.

Question 99.
Are all the required JDBC drivers to establish connectivity to my database part of the JDK?
Answer:
No. There aren’t any JDBC technology-enabled drivers bundled with the JDK 1.1.x or Java 2 Platform releases other than the JDBC-ODBC Bridge. So, developers need to get a driver and install it before they can connect to a database. We are considering bundling JDBC technology-enabled drivers in the future.

Question 100.
Is the JDBC-ODBC Bridge multi-threaded?
Answer:
No. The JDBC-ODBC Bridge does not support concurrent access from different threads. The JDBC-ODBC Bridge uses synchronized methods to serialize all of the calls that it makes to ODBC. Multi-threaded Java programs may use the Bridge, but they won’t get the advantages of multi-threading. In addition, deadlocks can occur between locks held in the database and the semaphore used by the Bridge. We are thinking about removing the synchronized methods in the future. They were added originally to make things simple for folks writing Java programs that use a single-threaded ODBC driver.

Question 101.
Does the JDBC-ODBC Bridge support multiple concurrent open statements per connection?
Answer:
No. You can open only one Statement object per connection when you are using the JDBC-ODBC Bridge.

Question 102.
Does the JDBC-ODBC Bridge developed by Morant and Sun support result sets that contain Japanese Characters (DBCS)?
Answer:
Yes, but we haven’t tested this ourselves. The version of the Bridge in the Java 2 SDK, Standard Edition, and Java 2 SDK, Enterprise Edition, also supports a new charSet Connection property for specifying the character encoding used by the underlying DBMS.

Question 103.
Why can’t I invoke the ResultSet methods after last and beforeFirst when the method next works?
Answer:
You are probably using a driver implemented for the JDBC 1.0 API. You need to upgrade to a JDBC 2.0 driver that implements scrollable result sets. Also, be sure that your code has created scrollable result sets and that the DBMS you are using supports them.

Question 104.
How can I retrieve a String or other object type without creating a new object each time?
Answer:
Creating and garbage collecting potentially large numbers of objects (millions) unnecessarily can really hurt performance. It may be better to provide a way to retrieve data like strings using the JDBC API without always allocating a new object.
We are studying this issue to see if it is an area in which the JDBC API should be improved. Stay tuned, and please send us any comments you have on this question.

Question 105.
There is a method to get column count in the JDBC API. Is there a similar method to find the number of rows in a result set?
Answer:
No, but it is easy to find the number of rows. If you are using a scrollable result set, rs, you can call the methods rs. last and then rs.grow to find out how many rows rs has. If the result is not scrollable, you can either count the rows by iterating through the result set or get the number of rows by submitting a query with a COUNT column in the SELECT clause.

Question 106.
I would like to download the JDBC-ODBC Bridge for the Java 2 SDK, Standard Edition (formerly JDK 1.2). I’m a beginner with the JDBC API, and I would like to start with the Bridge. How do I do it?
Answer:
The JDBC-ODBC Bridge is bundled with the Java 2 SDK, Standard Edition, so there is no need to download it separately.

Question 107.
If I use the JDBC API, do I have to use ODBC underneath?
Answer:
No, this is just one of many possible solutions. We recommend using a pure Java JDBC technology-enabled driver, type 3 or 4, in order to get all of the benefits of the Java programming language and the JDBC API.

Question 108.
Once I have the Java 2 SDK, Standard Edition, from Sun, what else do I need to connect to a database?
Answer:
You still need to get and install a JDBC technology-enabled driver that supports the database that you are using. There are many drivers available from a variety of sources. You can also try using the JDBC-ODBC Bridge if you have ODBC connectivity set lip already. The Bridge comes with the Java 2 SDK, Standard Edition, and Enterprise Edition, and it doesn’t require any extra setup itself. The Bridge is a normal ODBC client. Note, however, that you should use the JDBC- ODBC Bridge only for experimental prototyping or when you have no other driver available.

Question 109.
What is a JDBC Driver?
Answer:
A JDBC driver is the set of classes that implement the JDBC interfaces for a particular database.
There are four different types of JDBC drivers:
A Type 1 driver is a JDBC-ODBC bridge driver; this type of driver enables a client to connect to an ODBC database via Java calls and JDBC—neither the database nor middle tier needs to be Java compliant. However, ODBC binary code must be installed on each client machine that uses this driver.

A Type 2 driver converts JDBC calls into calls for a specific database. This driver is referred to as a native-API, partly Java driver. As with the Type 1 driver, some binary code may be required on the client machine, which means this type of driver is not suitable for downloading over a network to a client.
A Type 3 driver is a JDBC-Net pure Java driver, which translates JDBC calls into a database-independent net protocol. Vendors of database middleware products can implement this type of driver into their products to provide interoperability with the greatest number of database servers.

Finally, a Type 4 driver, or, native protocol, pure Java driver converts JDBC calls into the network protocol used by the database directly. A Type 4 driver requires no client software

so it’s ideal for deployment to browsers at runtime. Each of these driver types has its own optimal usage scenarios and will affect the way you deploy a given Java application.
For example, because Type 4 drivers are 100% Java, use Java sockets to connect to the database, and require no client-side data access code, they are ideal for applets or other download situations inside a firewall.

Question 110.
What are the Oracle’s JDBC Drivers?
Answer:
Oracle provides both Type 2 and Type 4 drivers. All Oracle JDBC drivers support the full JDBC specification, but in addition, they support the extended capabilities of the Oracle database. For example, the JDBC specification doesn’t support LOB data, but the Oracle OCI8 JDBC driver does. Oracle’s implementation of the Type 2 JDBC driver is referred to as the Oracle “OCI driver,” and the version of this driver that supports an Oracle 7 database is the OCI7 driver and the OCI8 supports Oracle 8.

These drivers are platform-specific; for example, the Windows NT and Windows 95 version of the driver (oci805jdbc.dll.) is implemented as a dynamic link library (DLL) in C. As mentioned previously, Type 2 drivers may require client code. In the case of the OCI8 driver, the clients must have Oracle’s Net*8 and all other dependent files loaded.

A common way to implement Oracle OCI drivers is to use Oracle Application Server with the JWeb cartridge on the middle tier and deploy the client presentation logic as an applet; the interaction with the Oracle database is conducted from the middle tier only, with just the results sent to the client applet as pure HTML or Java and HTML.

All Oracle drivers are compliant with the Java Development Kit JDK 1.0 and 1.1.x and support the JDBC 1.22 standard. In addition, all Oracle JDBC drivers support data types such as RAW and LONG RAW, ROWID, and RECURSOR, which are supported in Oracle databases but not part of the JDBC standard. Oracle’s drivers also support execution of PL/SQL stored procedures and anonymous blocks (for dynamic execution), and include capabilities such as row pre-fetching, execution batching, and defining query columns to reduce the network round trips to the Oracle database.

In addition, the OCI driver for Oracle8 supports oracle data types CLOB, BLOB, NCLOB, and BFILE. The screenshot shows an example of one of the classes in the Oracle JDBC, as part of the class hierarchy from which it descends, as displayed in Oracle’s JDeveloper integrated development environment. As you can see, the OraclePreparedStatement class inherits from the java.sql.PreparedStatement class, which in turn inherits from the java.sql.Statement.

Oracle also provides a Type 4 JDBC driver, referred to as the Oracle “thin” driver. This driver includes its own implementation of a TCP/IP version of Oracle’s Net8 written entirely in Java, so it is platform-independent, can be downloaded to a browser at runtime, and does not require any Oracle software on the client-side. This driver requires a TCP/IP listener on the server-side, and the client connection string uses the TCP/IP port address, not the TNSNAMES entry for the database name.

1. Add JDBC classes to your Java application or applet class by adding the following statement to your Java source code:
import java.sql.*;
To use the extended capabilities of the Oracle database, you must also import the Oracle JDBC driver. The statement in Java source looks like this: import oracle.JDBC.driver.*

2. Load the JDBC driver by including the following statement in your class. Class.forName(“oracle.JDBC.driver.OracleDriver”);
You can load the driver from your class-initialization routine.

3. Obtain a connection to an Oracle database by calling the getConnectionO method of the JDBC DriverManager class. When you call this method you need to specify the connection information for the database in the form of a URL. The form the URL will take depends on the driver used. For example, to use the pure Java Type 4 Oracle driver (the thin driver) to connect to an Oracle7 database, the URL would read: jdbc:oracle:thin@database_name:port_no:SID
To connect to an Oracle8 database using the OCI driver, the URL would be
more like: ‘
jdbc:oracle:oci8@database_name
To specify the database for use with an OCI driver, you can use either a SQL*Net name-value pair or, if you’re using an Oracle Name server, you can use the name from the DNS name. ora file. (Both of these strings would conclude with the login information as well—specifically the user name and password— but we’ve eliminated that from this example.)
The preliminary driver and database-connection issues are now taken care of, there are still several other things your Java source code must include in order
for the compiled code to submit queries to the database and process results.
4. Create a Statement object by calling the create statement( ) method of the Connection object you created in the previous step. The following statement creates a Statement object stmt:
Statement stmt = conn.createStatement 0;
5. Once the Statement object exists (in code), the application can then include code to execute a SQL query by calling the executeQuery( ) method of the Statement object.The execute query( ) method returns the result of the query in the ResultSet object. The following statement executes a query: ResultSet rset = stmt.executeQuery (SELECT name from emp where empno = 7900);
6. Finally, call the next( ) method of a ResultObject to retrieve a row and display it. Use a loop if the query returns more then one row from the database. For example, the following statements get the name of an employee from the ResultSet object and display it in java.awt text control placed on the GUI. rset.next( );
enameTxtb.setText = ((String)rset.getString(l));

Question 111.
What is Annotation in Java?
Answer:
An annotation, in the java programming language, is a special form of syntactic metadata that can be added to Java Source Code.
Classes, methods, variables parameters, and packages may be annotated.
Unlike Java doc tags, Java annotations are reflective, in that they are embedded in class files generated by the compiler and may be retained by the Java VM to make them retrievable at run-time. Annotation is basically to attach metadata to a method, class, or package. Metadata is used by the compiler to perform some basic compile-time checking.

Question 112.
What is the difference between PreparedStatement and Statement in java? Answer:

• A statement is parsed and executed each time its call is sent to the database.
• A prepared statement may be parsed once and executed repeatedly with different parameters.

There are four steps for the execution of the query:

1. Query is parsed
2. The query is compiled.
3. The query is optimized.
4. The query is executed.

In the case of the statement, the above four steps are performed every time. And in the case of a prepared statement, the above three steps are performed once.

Question 113.
What is CallableStatement? How you can call the stored procedure to pass IN parameter?
Answer:
CallableStatement in java is used for calling database stored procedures. Example: Adding Employee details in DB whenever a new Employee is joining. Employee Information: Employee Id, Name, and joining Date.
Code:

package com.mytest.jdbc;
import java.sql.CallableStatement;
import java.sql.DriverManager; import java.sql.connection;
import j ava.sql.SQLException;
public class AddEmployeeExample {
private static final string DB_DRIVER = “oracle.jdbc.driver.
OracleDriver”;
private static final string DB_CONNECTXON = “jdbc:oracle:thin:@
1ocalhost:1521:MYTEST”;
private static final String DB_USER = “my_user”;
private static final string DB_PASSWORD = "my_password”;
public static void main(string[ ] argv) {
try
{
addEmployeelnfo( );
}-catch (SQLExcepti on e) {
system.out.println(e.getMessage ( ));
}
}
private static void addEmployeelnfo( ) throws SQLException {
Connection dbconnection = null;
CallableStatement CallableStatement = null;
string insertEmployeeStoreProc = “{call insertEmployeeC?,?,?)}”; try {
dbconnection = getOBConnection( );
CallableStatement = dbconnection.preparecall(insertEmployeeStoreProc);
callablestatement.setlnt(l, 377602);
CallableStatement.setstring(2, “Nishant Singh”);
callableStatement.setDate(3, getcurrentDate( ));
// execute insert store procedure
cal1ablestatement.executeupdate( );
} catch (SQLException exp) {
System.out.println(exp.getMessage( ));
} finally {
if (callablestatement != null) {
cal 1ablestatement.close( );
}
if (dbconnection != null) {
dbconnection.close( );
}
}
}
private static connection getDBConnection( ) {
Connection dbconnection = null;
try {
class.forName(DB_DRIVER);
} catch (classNotFoundException e) {
System.out.println(e.getMessage ( ));
}
try {
dbconnection = DriverManager.getConnectionC DB_CONNECTION, DB_USER,DB_ PASSWORD);
return dbconnection;
} catch (SQLException e) {
System.out.println(e.getMessage( ));
}
return dbconnection;
}
private static java.sql.Date getCurrentDate( ) {
java.util.Date today = new java.util.Date( );
return new java.sql.Date(today.getTime( ));
}
}

Question 114.
Using Callable Statement how can you pass OUT Parameters, explain with example? •
Answer:
Take the example of Employee, where you want the details of Employee by Id.
Code: Use the same code structure get DB Connection etc.

//getDBUSERByUserid is a stored procedure
String getEmplnfoByEmpldSql = “{call getEmplnfoByEmpld(?,?,?,?)}”; 
cal1ablestatement = dbconnection.prepareCal1(getEmplnfoByEmpldSql);
 callablestatement.setlnt(l, 377602);
callableStatement.register0utParameter(2, java.sql.Types.VARCHAR);
callablestatement.registeroutParameter(B, java.sql.Types.DATE);
execute getEmplnfoByEmpld store procedure
cal 1 abl eStatement. executeltpdate ( );
string empName = cal1ableStatement.getstring(2);
string joiningDate = callab!eStatement.getDate(3);

Question 115.
How can you determine the SGA site?
Answer:
Database – JDBC (java.sql)
Connecting to a Database
This example uses the JDBC-ODBC bridge to connect to a database called “my database”.

try {
class.forName(“sun.j dbc.odbc.JdbcodbcDriver”);
String url = “jdbc:odbc:mydatabase”;
Connection con = DriverManager.getConnection(url, “login”, “password”);
} catch (classNotFoundException e) {
} catch (SQLException e) {
}

Creating a Table
This example creates a table called “table” with three columns: COL_A which holds strings, COL_B which holds integers, and COL_C which holds floating-point numbers.

try {
Statement stmt = con.createStatement( );
stmt.execute update(“CREATE TABLE mytable (COL^A VARCHAR(100), COL_B
INTEGER, COI C FLOAT)”);
} catch (SQLException e) {
}

Entering a New Row into a Table
This example enters a row containing a string, an integer, and a floating-point number into the table called “table”.

try {
Statement stmt = connection.createStatement( );
stmt.executeupdate(“iNSERT INTO mytable VALUES (‘Patrick chan’, 12B, 1.23)”);
connection.close( );
} catch (SQLException e) {
}

Getting All Rows from a Table
This example retrieves all the rows from a table called “table”. A row in “mytable” consists of a string, integer, and floating-point number.

try {
statement stmt = connection.createstatement( );
// Get data using colunm names.
ResultSet rs = stmt.executeQuery(“SELECT * FROM mytable”);
while (rs.next( )) {
string s = rs.getstring(“C0L_JA”);
int i = rs.getlnt(“COL_B”);
float f = rs.getFloat(“COL_C”);
processes, i, f);
}

Get data using column numbers.

\rs = stmt.executeQuery(“SELECT * FROM mytable");
while (rs.nextO) {
string s = rs.getString(l);
int i = rs.getlnt(2);
float f = rs.getFloat(3);
process(s, i, f);
}

Getting Particular Rows from a Table
This example retrieves all rows from a table called “mytable” whose column COL_A equals “Patrick Chan”. A row in “mytable” consists of a string, integer, and floating-point number.

try {
Statement stmt = connection.createstatement( );
ResultSet rs = stmt.executeQuery(“SELECT * FROM mytable WHERE COL_A = ‘Patrick chan”’);
rs.next( );
String s = rs.getString(“C0L_A”);
int i = rs.getlnt(“COL_B”);
float f = rs.getFloat(“coL_c”);
process (s, i, f) ;
} catch (SQLException e) {
}

Updating a Row of Data in a Table
This example updates a row in a table called “mytable”. In particular, for all rows whose column COL_B equals 123, column COL_A is set to “John Doe”.

try {
Statement stmt = connection.createstatementO;
int numupdated = stmt.executeUpdate(“uPDATE mytable SET COi—A = ‘John
Doe’ WHERE COi B = 123”);
connection, close( );
} catch (SQLException e) {
}

Using a Prepared Statement

A prepared statement should be used in cases where a particular SQL statement is used frequently. The prepared statement is more expensive to set up but executes faster than a statement. This example demonstrates a prepared statement for getting all rows from a table called “table” whose column COL_A equals “Patrick Chan”. This example also demonstrates a prepared statement for updating data in the table. In particular, for all rows whose column COL_B equals 123, column COL_A is set to “John Doe”.

try {
// Retrieving rows from the database.
PreparedStatement stmt = connection.preparestatement(“SELECT * FROM mytable WHERE COL_A = ?”);
int colunm = 1;
stmt.setStringCcolunm, “Patrick Chan”);
ResultSet rs = stmt.executeQuery( );
// updating the database.
stmt = connection.prepareStatement(“UPDATE mytable SET coi A = ? WHERE
COI_B = ?");
colunm = 1;
stmt.setStringCcolunm, “john Doe”);
colunm = 2;
stmt.setlnt(colunm, 123);
int numupdated = stmt.executeupdate( );
} catch (SQLException e) {
}

Question 116.
How to Interact with DB?
Answer:
Generally, every DB vendor provides a User Interface through which we can easily execute SQL queries and gets the result (For example Oracle Query Manager for Oracle, and TOAD (www.quest.com) tool common to all the databases). And these tools will help DB developers to create databases. But as a programmer we want to interact with the DB dynamically to execute some SQL queries from our application (Any application like C, C++, JAVA, etc), and for this requirement, DB vendors provide some Native Libraries (Vendor Specific) using this we can interact with the DB i.e. If you want to execute some queries on Oracle DB, oracle vendor provides an OCI (Oracle Call Interface) Libraries to perform the same.

Question 117.
What is ODBC?
Answer:
ODBC (Open Database Connectivity) is an ISV (Independent software vendor product) composes of native API to connect to different databases via a single API called ODBC.
Open Database Connectivity (ODBC) is an SQL-oriented application programming interface developed in collaboration with IBM and some other database vendors. ODBC comes with Microsoft products and with all databases on Windows OS.
ODBC Architecture

Advantages

• Single API (Protocol) is used to interact with any DB
• Switching from one DB to another is easy
• Doesn’t require any modifications in the Application when you want to shift from one DB to other

Question 118.
How to Make Updates to Updatable Result Sets?
Answer:
Another new feature in the JDBC 2.0 API is the ability to update rows in a result set using methods in the Java programming language rather than having to send an SQL command. But before you can take advantage of this capability, you need to create a ResultSet object that is updatable. In order to do this, you supply the ResultSet constant CONCUR_UPDATABLE to the createStatement method.
E.g.
Connection con = DriverManager.getConnection(“jdbc:mySubprotocol:mySubNa me”);
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCURJLJPDATABLE);
ResultSet uprs = (“SELECT COF_NAME, PRICE FROM COFFEES”);

Question 119.
What is ACID?
Answer:
ACID is related to transactions. It is an acronym for Atomic, Consistent, Isolation, and Durable. The transaction must be following the above four properties to be a better one.

Atomic: It means a transaction must execute all or nothing at all.

Consistent: Consistency is a transactional characteristic that must be enforced by both the transactional system and the application developer
Isolation: Transaction must be allowed to run itself without the interference of the other processor transactions.

Durable: Durability means that all the data changes made by the transaction must be written in some type of physical storage before the transaction is successfully completed. This ensures that transactions are not lost even if the system crashes.

Question 120.
How JDBC API does help us in achieving loose coupling between Java Program and JDBC Drivers API?
Answer:
JDBC API uses Java Reflection API to achieve loose coupling between java programs and JDBC Drivers. If you look at a simple JDBC example, you will notice that all the programming is done in terms of JDBC API and Driver comes into the picture only when it’s loaded through reflection using the Class.forName( ) method.

I think this is one of the best examples of using Reflection in core java classes to make sure that our application doesn’t work directly with Drivers API and that makes it very easy to move from one database to another. Please read more at JDBC Example.

Question 121.
What is JDBC Connection? Explain steps to get a Database connection in a simple java program.
Answer:
JDBC Connection is like a Session created with the database server. You can also think Connection is like a Socket connection from the database server.
Creating a JDBC Connection is very easy and requires two steps:

1. Register and Load the Driver: Using Class.forName( ), the Driver class is registered to the DriverManager and loaded in the memory.
2. Use DriverManager to get the Connection object: We get connection object fromDriverManager.getConnection( ) by passing Database URL String, username and password as argument.

connection con = null;
try{
// load the Driver class
Class.forName(“com.mysql.jdbc.Driver”);
// create -the connection now
con = DriverManager.getconnection(“jdbc:mysql://local host:3306/ userDB”,’’pankaj”, “pankajl23”);
}catch (SQLException e) {
System.out.println(“check database is UP and configs are correct”);
e.printStackTrace( );
}catch (classNotFoundException e) {
System.out.println(“Piease include 3DBC MySQL jar in classpath”);
e.printStackTrace( );
}

Question 122.
What is the use of the JDBC DriverManager class?
Answer:
JDBC DriverManager is the factory class through which we get the Database Connection object. When we load the JDBC Driver class, it registers itself to the DriverManager, you can look up the JDBC Driver classes source code to check this.
Then when we call DriverManager.get a connection( ) method by passing the database configuration details, DriverManager uses the registered drivers to get the Connection and return it to the caller program.

Question 123.
How to get the Database server details in the java program?
Answer:
We can use the DatabaseMetaData object to get the database server details. When the database connection is created successfully, we can get the metadata object by calling the get metadata( ) method. There are so many methods in DatabaseMetaData that we can use to get the database product name, its version, and configuration details.

DatabaseMetaData metaData = con.getMetaData( );
String dbProduct = metaData.getDatabaseProductName( );

Question 124.
What is JDBC Statement?
Answer:
JDBC API Statement is used to execute SQL queries in the database. We can create the Statement object by calling Connection to get statements( ) method. We can use Statement to execute static SQL queries by passing query through different execute methods such as execute( ), execute query( ), execute update( ) etc.

Since the query is generated in the java program, if the user input is not properly validated it can lead to an SQL injection issue, more details can be found in SQL Injection Example.

By default, only one ResultSet object per Statement object can be open at the same time. Therefore, if we want to work with multiple ResultSet objects, then each must have been generated by different Statement objects. All execute( ) methods in the Statement interface implicitly close a statement’s current ResultSet object if an open one exists.

Question 125.
What is the difference between execute, executeQuery, executeUpdate?
Answer:
Statement execute(String query) is used to execute an SQL query and it returns TRUE if the result is a ResultSet such as running Select queries. The output is FALSE when there is no ResultSet object such as running Insert or Update queries. We can use getResultSet( ) to get the ResultSet and getUpdateCountO method to retrieve the update count.

Statement executeQuery(String query) is used to execute Select queries and returns the ResultSet. ResultSet returned is never null even if there are no records matching the query. When executing select queries we should use the executeQuery method so that if someone tries to execute an insert/update statement it will throw java.sql. SQLException with message “executeQuery method can not be used for update”. Statement executeUpdate(String query) is used to execute Insert/Update/Delete (DML) statements or DDL statements that returns nothing. The output is int and equals the row count for SQL Data Manipulation Language (DML) statements. For DDL statements, the output is 0.

You should use execute( ) method only when you are not sure about the type of statement else use the executeQuery or executeUpdate method.

Question 126.
What is JDBC PreparedStatement?
Answer:
JDBC PreparedStatement object represents a precompiled SQL statement. We can use its setter method to set the variables for the query.
Since PreparedStatement is precompiled, it can then be used to efficiently execute this statement multiple times. PreparedStatement is a better choice than Statement because it automatically escapes the special characters and avoids SQL injection attacks.

Question 127.
How to set NULL values in JDBC PreparedStatement?
Answer:
We can use the PreparedStatement setNullO method to bind the null variable to a parameter. The setNull method takes index and SQL Types as argument, for example
ps.setNull(10, java.sql.Types.INTEGER);.

Question 128.
What is the use of the getGeneratedKeys( ) method in the Statement?
Answer:
Sometimes a table can have auto-generated keys used to insert the unique column value for the primary key. We can use the Statement getGeneratedKeys( ) method to get the value of this auto-generated key.

Question 129.
What causes No suitable driver error?
Answer:
“No suitable driver” occurs during a call to the DriverManager. getConnection method may be of any of the following reasons:

• Due to failing to load the appropriate JDBC drivers before calling the getConnection method.
• It can be specifying an invalid JDBC URL, one that is not recognized by the JDBC driver.
• This error can occur if one or more of the shared libraries needed by the bridge cannot be loaded

Question 130.
What does setAutoCommit do?
Answer:
setAutoCommit( ) invokes the commit state query to the database. To perform batch updation we use the setAutoCommit( ) which enable us to execute more than one statement together, which in result minimize the database call and send all statement in one batch.
setAutoCommit( ) allowed us to commit the transaction commit state manually the default values of the setAutoCommit( ) is true.

Question 131.
Why Prepared Statements are faster?
Answer:
Prepared execution is faster than direct execution for statements executed more than three or four times because the statement is compiled only once. Prepared statements and JDBC drivers are linked with each other. We can bind drivers with columns by triggering the query into the database. When we execute Connection. prepareStatement( ), all the columns bindings take place, in order to reduce the time.

Question 132.
How can we store and retrieve images from the database?
Answer:
By using the PreparedStaement interface, we can store and retrieve images. Example to store the image in Oracle database:
You can store images in the database in java with the help of the PreparedStatement interface.
The setBinaryStream() method of PreparedStatement is used to set Binary information into the parameter index.
Signature of setBinaryStream method
The syntax of setBinaryStream( ) method is given below:

1. public void setBinaryStream(int paramlndexJnputStream stream) throws SQLException
2. Public void set Binary Stream (int paramlndexJnputStream stream,long length)throws SQL Exception

For storing images into the database, BLOB (Binary Large Object) data type is used in the table. For example:

1. CREATE TABLE “IMITABLE”
2. («NAME» VARCHAR2(4000),
3. «PHOTO» BLOB
4. )
5. /

Let’s write the jdbc code to store the image in the database. Here we are using d:\\d. jpg for the location of image. You can change it according to the image location.

import java.sql. ;
import java.io.*;
public class insertlmage {
public static void main(String[ ] args) {
try{
cl ass.forName(“oracle.jdbc.driver.OracleDriver”);
connection con=DriverManager.getConnection( “jdbc:oracle:thin:@1ocalhos t:1521:xe",’’system”,’’oracle”);


PreparedStatement ps=con.prepareStatement(“insert into imgtable values(?,?)”);
ps.setString(l,”sonoo”);
FileinputStream fin=new FilelnputStream(«d:\\g. jpg»);
ps. setBi naryStream,(2, fin, fin. avai 1 abl e( ));
int i=ps.executeupdate();
System.out.println(i+” records affected”);
con.clo se( );
}catch (Exception e) {e.printStackTrace( );}
}
}

If you see the table, the record is stored in the database but the image will not be shown. To do so, you need to retrieve the image from the database which we are covering on the next page.

Question 133.
Differentiate between TYPE_SCROLL_INSENSITIVE and TYPE_SCROLL_ SENSITIVE.
Answer:

• We will get a scrollable ResultSet object if we specify either one of the ResultSet constants.
• The difference between the two depends on, whether a resultset is showing fv changes or not.
• This difference depends on certain methods which are called to detect changes or not.
• The resultset TYPE_SCROLL_INSENSITI\7E does not show the change to it but the ResultSet srs = TYPEJSCROLLJSENSITIVE will show the change.

The following code explains the difference:

statement stmt = con.createstatement (Resultset.TYPE_SCROLL_
INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
Resultset srs = stmt.executeQuery (“SELECT COF_NAME, PRICE FROM COFFEES”);
srs.afterLast( );
while (srs.previous( ))
{
String name = srs.getString(“C0F_NAME”);
float price 1= srs.getFloat(“PRlCE”);
System.out.println(name + “ “ + pricel);
}

Question 134.
Name the new features added in JDBC 4.9
Answer:

• Auto-loading by JDBC driver class.
• Enhanced Connection management.
• Rowland SQL enabled.
• DataSet is implemented by SQL by using Annotations.
• Enhancements of SQL exception handling
• Supporting SQL XML files.

Question 135.
Briefly tell about the JDBC Architecture.
Answer:
The JDBC Architecture consists of two layers:

1. The JDBC API
2. The JDBC Driver API

♦ The JDBC API provides the application-JDBC Manager connection.
♦ The JDBC Driver API supports the JDBC Manager-to-Driver Connection.
♦ The JDBC API interacts with a driver manager, a database-specific driver for providing transparent connectivity for the heterogeneous databases.
♦ The JDBC driver manager authenticates that the correct driver has been used to access each data source.
♦ The driver manager supports multiple concurrent drivers connected to multiple heterogeneous databases.

Question 136.
Describe how the JDBC application works.
Answer:
A JDBC application may be divided into two layers:

• Driver layer
• Application layer
• The Driver layer consists of DriverManager class & the JDBC drivers.
• The Application layer begins after putting a request to the DriverManager for the connection.
• An appropriate driver is chosen and used for establishing the connection.
• This connection is linked to the application layer.
• The application needs the connection for creating the Statement kind of objects by which the results are obtained.

Question 137.
Explain how to Make Updates to the Updatable ResultSets.
Answer:

• The JDBC 2.0 API can update rows in a ResultSet using the methods in Java rather than using a SQL command.
• But before doing that, we create a ResultSet object which is updatable.
• For doing this, w.e give the ResultSet CONCUR_UPDATABLE in the createStatement method.

Code:
connection con = DriverManager.getconnection(“jdbc:mySubprotocol:mySubN ame”);
statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet uprs = stmt.executeQuery(“SELECT COF_NAME, PRICE “);