|
|
|
What is JDBC?
|
Short for
J
ava
D
ata
b
ase
C
onnectivity, a Java API that enables Java programs
to execute SQL statements. This allows Java programs
to interact with any SQL-compliant database. Since
nearly all relational database management systems
(DBMSs) support SQL, and because Java itself runs on
most platforms, JDBC makes it possible to write a
single database application that can run on
different platforms and interact with different
DBMSs.
JDBC is similar to ODBC, but is designed
specifically for Java programs, whereas ODBC is
language-independent.
|
What are the components of JDBC
|
JDBC Components—Connection Pools, Data
Sources, and MultiPools
|
How to you load the drivers
|
Class.forName() method is used in JDBC to load the
JDBC drivers dynamically
|
What does Class.forname() do
|
Class.forName() method is used in JDBC to load the
JDBC drivers dynamically
|
What are the different types of JDBC drivers
|
-
A
JDBC-ODBC bridge
provides JDBC API access via one or more ODBC
drivers. Note that some ODBC native code and in
many cases native database client code must be
loaded on each client machine that uses this
type of driver. Hence, this kind of driver is
generally most appropriate when automatic
installation and downloading of a Java
technology application is not important.
-
A
native-API partly Java technology-enabled
driver
converts JDBC calls into calls on the client API
for Oracle, Sybase, Informix, DB2, or other
DBMS. Note that, like the bridge driver, this
style of driver requires that some binary code
be loaded on each client machine.
-
A
net-protocol fully Java technology-enabled
driver
translates JDBC API calls into a
DBMS-independent net protocol which is then
translated to a DBMS protocol by a server. This
net server middleware is able to connect all of
its Java technology-based clients to many
different databases. The specific protocol used
depends on the vendor. In general, this is the
most flexible JDBC API alternative. It is likely
that all vendors of this solution will provide
products suitable for Intranet use. In order for
these products to also support Internet access
they must handle the additional requirements for
security, access through firewalls, etc., that
the Web imposes. Several vendors are adding JDBC
technology-based drivers to their existing
database middleware products.
-
A
native-protocol fully Java technology-enabled
driver
converts JDBC technology calls into the network
protocol used by DBMSs directly. This allows a
direct call from the client machine to the DBMS
server and is a practical solution for Intranet
access. Since many of these protocols are
proprietary the database vendors themselves will
be the primary source for this style of driver.
Several database vendors have these in progress.
|
What are the pros and cons of all the 4 drivers
|
Type 1: JDBC-ODBC Bridge
The type 1 driver, JDBC-ODBC Bridge, translates all
JDBC calls into ODBC (Open DataBase Connectivity)
calls and sends them to the ODBC driver. As such,
the ODBC driver, as well as, in many cases, the
client database code, must be present on the client
machine. Figure 1 shows a typical JDBC-ODBC Bridge
environment.
Pros
The JDBC-ODBC Bridge allows access to almost any
database, since the database's ODBC drivers are
already available. Type 1 drivers may be useful for
those companies that have an ODBC driver already
installed on client machines.
Cons
The performance is degraded since the JDBC call goes
through the bridge to the ODBC driver, then to the
native database connectivity interface. The result
comes back through the reverse process. Considering
the performance issue, type 1 drivers may not be
suitable for large-scale applications.
The ODBC driver and native connectivity interface
must already be installed on the client machine.
Thus any advantage of using Java applets in an
intranet environment is lost, since the deployment
problems of traditional applications remain.
Type 2: Native-API/partly Java driver
JDBC driver type 2 -- the native-API/partly Java
driver -- converts JDBC calls into database-specific
calls for databases such as SQL Server, Informix,
Oracle, or Sybase. The type 2 driver communicates
directly with the database server; therefore it
requires that some binary code be present on the
client machine.
Pros
Type 2 drivers typically offer significantly better
performance than the JDBC-ODBC Bridge.
Cons
The vendor database library needs to be loaded on
each client machine. Consequently, type 2 drivers
cannot be used for the Internet. Type 2 drivers show
lower performance than type 3 and type 4 drivers.
Type 3: Net-protocol/all-Java driver
JDBC driver type 3 -- the net-protocol/all-Java
driver -- follows a three-tiered approach whereby
the JDBC database requests are passed through the
network to the middle-tier server. The middle-tier
server then translates the request (directly or
indirectly) to the database-specific
native-connectivity interface to further the request
to the database server. If the middle-tier server is
written in Java, it can use a type 1 or type 2 JDBC
driver to do this.
Pros
The net-protocol/all-Java driver is server-based,
so there is no need for any vendor database
library to be present on client machines. Further,
there are many opportunities to optimize
portability, performance, and scalability.
Moreover, the net protocol can be designed to make
the client JDBC driver very small and fast to
load. Additionally, a type 3 driver typically
provides support for features such as caching
(connections, query results, and so on), load
balancing, and advanced system administration such
as logging and auditing.
Cons
Type 3 drivers require database-specific coding
to be done in the middle tier. Additionally,
traversing the recordset may take longer, since
the data comes through the backend server.
Type 4: Native-protocol/all-Java driver
The native-protocol/all-Java driver (JDBC driver
type 4) converts JDBC calls into the
vendor-specific database management system (DBMS)
protocol so that client applications can
communicate directly with the database server.
Level 4 drivers are completely implemented in Java
to achieve platform independence and eliminate
deployment administration issues.
Pros
Since type 4 JDBC drivers don't have to translate
database requests to ODBC or a native connectivity
interface or to pass the request on to another
server, performance is typically quite good.
Moreover, the native-protocol/all-Java driver
boasts better performance than types 1 and 2.
Also, there's no need to install special software
on the client or server. Further, these drivers
can be downloaded dynamically.
Cons
With type 4 drivers, the user needs a different
driver for each database.
|
How to you establish a connection
|
Loading Drivers
Class.forName("Driver");
Getting connection
Connection con = DriverManager.getConnection(url,
"myLogin", "myPassword");
|
What are different types of statements in JDBC
|
java.sql.Statement - Top most interface which
provides basic methods useful for executing SELECT,
INSERT, UPDATE and DELETE SQL statements.
java.sql.PreparedStatement - An enhanced verion of
java.sql.Statement which allows precompiled queries
with parameters. It is more efficient to use
java.sql.PreparedStatement if you have to specify
parameters to your SQL queries.
java.sql.CallableStatement - Allows you to execute
stored procedures within a RDBMS which supports
stored procedures (MySQL doesn't support stored
procedures at the moment).
|
When do we used prepared statements
|
If you want to execute a Statement object many
times, it will normally reduce execution time to use
a PreparedStatement object instead.
The main feature of a PreparedStatement object is
that, unlike a Statement object, it is given an SQL
statement when it is created. 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.
Although PreparedStatement objects can be used for
SQL statements with no parameters, you will probably
use them most often for SQL statements that take
parameters. The advantage of using SQL statements
that take parameters is that you can use the same
statement and supply it with different values each
time you execute it. You will see an example of this
in the following sections.
|
How do you create JDBC statements
|
Connection con = null;
Statement st = null;
// Obtain connection here
st = con.createStatement();
ResultSet rs = null;
rs = st.executeQuery("SELECT * FROM
users");
int recordsUpdated;
recordsUpdated = st.executeUpdate("DELETE FROM
users WHERE user_id = 1");
|
How do you retrieve data from a result set
|
|
Example:
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(SELECT COF_NAME,
PRICE FROM COFFEES”);
while (rs .next() )
{
//Iam assuming there are 3 columns in the table.
System.out.println ( rs.getString(1));
System.out.println(rs.getString(2));
System.out.println(rs.getString(3));
}
//don’t forget to close the resultset,
statement & connection
rs.close(); //First
stmt.close(); //Second
con.close(); //Last
System.out.println(”You are done”);
|
What is a stored procedure
|
A stored procedure is a group of SQL statements that
form a logical unit and perform a particular task.
Stored procedures are used to encapsulate a set of
operations or queries to execute on a database
server. For example, operations on an employee
database (hire, fire, promote, lookup) could be
coded as stored procedures executed by application
code. Stored procedures can be compiled and executed
with different parameters and results, and they may
have any combination of input, output, and
input/output parameters.
|
What are the tasks of JDBC
|
Following are the tasks of JDBC
-
Load the JDBC drivers
-
Register the drivers
-
Specify a database
-
Open a connection to database
-
Submit a query to database
-
Gets the results
|
What do you mean by batch updates
|
if you want to execute a set of statements, i.e. SQL
statements at a time then we use batch update
statement.
resultset=pst.batchUpdate();
|
Why do we need batch updates
|
Let's say there are 100 records need to be insert.
If we execute normal statemets the no of
transactions will be 100 (in terms of connection
making to DB). using batch updates we can add 100
rec to batch and the no of transactions will be only
one in this case. This will reduce the burdon on db,
which is very costly in terms of resources.
|
How do you call a stored procedure from java
|
You can call a stored procedure using Callable
statements
CallableStatement cs = con.prepareCall("{call
StoredProc}");
ResultSet rs = cs.executeQuery();
|
What packages are being used by JDBC
|
Following packages are used in JDBC
java.sql
javax.sql
|
Explain how to get the resultset of Stored
procedure
|
CallableStatement cstmt;
ResultSet rs;
int i;
String s;
...
cstmt.execute();// Call the stored procedure
1
rs = cstmt.getResultSet();// Get the first result set
2
while (rs.next())
{
// Position the cursor
3
i =
rs.getInt(1);
// Retrieve current result set value
System.out.println("Value from first result set
= " + i); // Print the value
}
cstmt.getMoreResults(); // Point to the second
result set
4a
// and close the first result set
rs = cstmt.getResultSet(); // Get the second result
set
4b
while (rs.next())
{
// Position the cursor
4c
s =
rs.getString(1); // Retrieve current
result set value
System.out.println("Value from second result
set = " + s);
// Print the value
}
rs.close();
// Close the result set
cstmt.close();
// Close the statement
|
What do we use setAutoCommit() for
|
The DML operations by default are committed. If we
wish to
avoid the commit by default, setAutoCommit(false)
has to be called on the Connection object.
Once the statements are executed, commit() has to be
called on the Connection object explicitly.
|
Difference between Resultset and Rowset
|
RowSet
The interface that adds support to the JDBC API for
the JavaBeansTM component model. A rowset, which can
be used as a JavaBeans component in a visual Bean
development environment, can be created and
configured at design time and executed at run time.
The RowSet interface provides a set of JavaBeans
properties that allow a RowSet instance to be
configured to connect to a JDBC data source and read
some data from the data source. A group of setter
methods (setInt, setBytes, setString, and so on)
provide a way to pass input parameters to a rowset's
command property. This command is the SQL query the
rowset uses when it gets its data from a relational
database, which is generally the case.
The RowSet interface supports JavaBeans events,
allowing other components in an application to be
notified when an event occurs on a rowset, such as a
change in its value.
The RowSet interface is unique in that it is
intended to be implemented using the rest of the
JDBC API. In other words, a RowSet implementation is
a layer of software that executes "on top"
of a JDBC driver. Implementations of the RowSet
interface can be provided by anyone, including JDBC
driver vendors who want to provide a RowSet
implementation as part of their JDBC products.
A RowSet object may make a connection with a data
source and maintain that connection throughout its
life cycle, in which case it is called a connected
rowset. A rowset may also make a connection with a
data source, get data from it, and then close the
connection. Such a rowset is called a disconnected
rowset. A disconnected rowset may make changes to
its data while it is disconnected and then send the
changes back to the original source of the data, but
it must reestablish a connection to do so.
ResultSet
A table of data representing a database result set,
which is usually generated by executing a statement
that queries the database.
A ResultSet object maintains a cursor pointing to
its current row of data. Initially the cursor is
positioned before the first row. The next method
moves the cursor to the next row, and because it
returns false when there are no more rows in the
ResultSet object, it can be used in a while loop to
iterate through the result set.
A default ResultSet object is not updatable and has
a cursor that moves forward only. Thus, you can
iterate through it only once and only from the first
row to the last row. It is possible to produce
ResultSet objects that are scrollable and/or
updatable. The following code fragment, in which con
is a valid Connection object, illustrates how to
make a result set that is scrollable and insensitive
to updates by others, and that is updatable. See
ResultSet fields for other options.
|
How do we retrieve warning
|
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
getWarnings 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();
}
}
|
How many statements can we create with one
connection
|
There is no such limit on number of statements to be
created
|
How to Make Updates to Updatable Result Sets
|
Usinf JDBC 2.0 API we have the ability to update
rows in a result set.
For this we need to create a ResultSet object that
is updatable.
For this, we pass the ResultSet constant
CONCUR_UPDATABLE to the createStatement method.
Connection con =
DriverManager.getConnection(url,
"myLogin", "myPassword");
Statement stmt =
con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet uprs =
stmt.executeQuery("SELECT
NAME, SALARY FROM EMPLOYEES");
|
How can you move the cursor in scrollable result
sets
|
In JDBC 2.0 API we have the ability to move a result
set’s cursor backward as well as forward.
We can also move the cursor to a particular row and
check the position of the cursor.
Statement stmt =
con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet srs = stmt.executeQuery(”SELECT
NAME, SALARY FROM EMPLOYEES”);
The first argument is one of three constants added
to the ResultSet API to indicate the type of a
ResultSet object: TYPE_FORWARD_ONLY,
TYPE_SCROLL_INSENSITIVE , and TYPE_SCROLL_SENSITIVE.
The second argument is one of two ResultSet
constants for specifying whether a result set is
read-only or updatable: CONCUR_READ_ONLY and
CONCUR_UPDATABLE.
Make sure that when you specify a type, you must
also specify whether it is read-only or updatable.
Specifying the constant TYPE_FORWARD_ONLY creates a
nonscrollable result set, that is, one in which the
cursor moves only forward. If you do not specify any
constants for the type and updatability of a
ResultSet object, you will automatically get one
that is TYPE_FORWARD_ONLY and CONCUR_READ_ONLY.
|
What’s the difference between
TYPE_SCROLL_INSENSITIVE , and TYPE_SCROLL_SENSITIVE
|
You will get a scrollable ResultSet object if you
specify one of these ResultSet constants.The
difference between the two has to do with whether a
result set reflects changes that are made to it
while it is open and whether certain methods can be
called to detect these changes. Generally speaking,
a result set that is TYPE_SCROLL_INSENSITIVE does
not reflect changes made while it is still open and
one that is TYPE_SCROLL_SENSITIVE does. All three
types of result sets will make changes visible if
they are closed and then reopened:
Statement stmt =
con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet srs =
stmt.executeQuery("SELECT
NAME, SALARY FROM PERSON");
srs.afterLast();
while (srs.previous())
{
String name =
srs.getString("NAME");
float salary =
srs.getFloat("SALARY");
System.out.println(name + "
" + salary);
}
|
How do you insert images in Database using JDBC
|
We can store images in the databse using the BLOB
datatype where in the image is stored as a byte
stream
|
What is Metadata
|
It is information about one of two things: Database
information (java.sql.DatabaseMetaData), or
Information about a specific ResultSet
(java.sql.ResultSetMetaData). Use DatabaseMetaData
to find information about your database, such as its
capabilities and structure. Use ResultSetMetaData to
find information about the results of an SQL query,
such as size and types of columns
|
What is a data source
|
A DataSource class brings another level of
abstraction than directly using a connection object.
Data source can be referenced by JNDI. Data Source
may point to RDBMS, file System , any DBMS etc.
|