JDBC Basics

03/19/2007
Agenda
What is JDBC?
Step By Step Usage of JDBC API
DataSource & Connection Pooling
JDBC Basics
Transaction
Prepared and Callable Statements
What is JDBC?
Standard Java API for accessing
relational database
– Hides database specific details from
application
What is JDBC?
Part of Java SE (J2SE)
– Java SE 6 has JDBC 4

---

03/19/2007
JDBC API
JDBC Driver
?
Defines a set of Java Interfaces, which are
implemented by vendor-specific JDBC Drivers
?
Database specific implemention of
– Applications use this set of Java interfaces for
JDBC interfaces
performing database operations - portability
– Every database server has corresponding
?
Majority of JDBC API is located in java.sql
JDBC driver(s)
package
?
You can see the list of available drivers
– DriverManager, Connection, ResultSet,
DatabaseMetaData, ResultSetMetaData,
from
PreparedStatement, CallableStatement and Types
– http://industry.java.sun.com/products/jdbc/
?
Other advanced functionality exists in the
drivers
javax.sql package
– DataSource
Database URL
?
Used to make a connection to the database
– Can contain server, port, protocol etc…
?
jdbc:subprotocol_name:driver_dependant_databasena
me
Step By Step Usage
– Oracle thin driver
jdbc:oracle:thin:@machinename:1521:dbname
of JDBC API
– Derby
jdbc:derby ://localhost:1527/sample
– Pointbase
jdbc:pointbase:server://localhost/sample

---

03/19/2007
Steps of Using JDBC
1. Load DB-Specific Database
Driver
1.Load DB-specific JDBC driver
2.Get a Connection object
?
To manually load the database driver and
register it with the DriverManager, load its
3.Get a Statement object
class file
4.Execute queries and/or updates
– Class.forName(<database-driver>)
5.Read results
try {
6.Read Meta-data (optional step)
// This loads an instance of the Pointbase DB Driver.
7.Close Statement and Connection
// The driver has to be in the classpath.
Class.forName("org.apache.derby.jdbc.ClientDriver");
objects
}catch (ClassNotFoundException cnfe){
System.out.println("" + cnfe);
}
2. Get a Connection Object
DriverManager & Connection
?
DriverManager class is responsible for selecting
the database and and creating the database
?
java.sql.DriverManager
connection
• getConnection(String url, String user, String password)
– Using DataSource is a prefered means of
throws SQLException
getting a conection object (we will talk about
this later)
?
java.sql.Connection
• Statement createStatement() throws SQLException
?
Create the database connection as follows:
• void close() throws SQLException
try {
• void setAutoCommit(boolean b) throws SQLException
Connection connection =
• void commit() throws SQLException
DriverManager.getConnection("jdbc:derby://localhost:1527/sampl
e", “app"," app ");
• void rollback() throws SQLException
} catch(SQLException sqle) {
System.out.println("" + sqle);
}

---

03/19/2007
3. Get a Statement Object
4. Executing Query or Update
?
Create a Statement Object from
?
From the Statement object, the 2 most used
Connection object
commands are
• java.sql.Statement
– (a) QUERY (SELECT)
• ResultSet rs = statement.executeQuery("select * from
– ResultSet executeQuery(string sql)
customer_tbl");
– int executeUpdate(String sql)
• Example:
Statement statement = connection.createStatement();
– (b) ACTION COMMAND (UPDATE/DELETE)
• int iReturnValue = statement.executeUpdate("update
?
The same Statement object can be
manufacture_tbl set name = ‘IBM' where mfr_num =
used for many, unrelated queries
19985678");
5. Reading Results
5. Reading Results (Continued)
?
Loop through ResultSet retrieving
?
Once you have the ResultSet, you can easily
information
retrieve the data by looping through it
– java.sql.ResultSet
while (rs.next()){
? boolean next()
// Wrong this will generate an error
? xxx getXxx(int columnNumber)
String value0 = rs.getString(0);
? xxx getXxx(String columnName)
? void close()
// Correct!
String value1 = rs.getString(1);
?
The iterator is initialized to a position before
int value2 = rs.getInt(2);
the first row
int value3 = rs.getInt(“ADDR_LN1");
– You must call next() once to move it to the first row
}

---

03/19/2007
6. Read ResultSet MetaData and
5. Reading Results (Continued)
DatabaseMetaData (Optional)
?
When retrieving data from the
?
Once you have the ResultSet or
ResultSet, use the appropriate
Connection objects, you can obtain the
getXXX() method
Meta Data about the database or the
query
• getString()
• getInt()
?
This gives valuable information about the
• getDouble()
data that you are retrieving or the
• getObject()
database that you are using
?
There is an appropriate getXXX method
– ResultSetMetaData rsMeta = rs.getMetaData();
of each java.sql.Types datatype
– DatabaseMetaData dbmetadata =
connection.getMetaData();
• There are approximately 150 methods in the
DatabaseMetaData class.
ResultSetMetaData Example
ResultSetMetaData meta = rs.getMetaData();
//Return the column count
int iColumnCount = meta.getColumnCount();
for (int i =1 ; i <= iColumnCount ; i++){
DataSource &
System.out.println(“Column Name: " + meta.getColumnName(i));
System.out.println(“Column Type" + meta.getColumnType(i));
Connection Pooling
System.out.println("Display Size: " +
meta.getColumnDisplaySize(i) );
System.out.println("Precision: " + meta.getPrecision(i));
System.out.println(“Scale: " + meta.getScale(i) );
}

---

03/19/2007
javax.sql.DataSource Interface
Sub-Topics
and DataSource Object
?
DataSource interface and DataSource
?
Driver vendor implements the interface
object
?
DataSource object is the factory for creating
?
Properties of a DataSource object
database connections
?
JNDI registration of a DataSource
object
?
DataSource object that implements
Connection pooling
?
Retrieval of DataSource object (within
your application)
javax.sql.DataSource Interface
and DataSource Object
Properties of DataSource Object
?
A DataSource object has properties that can be
?
Three types of possible implementations
modified when necessary – these are defined in
a container's configuration file
– Basic implementation: produces standard
Connection object
– location of the database server
– Connection pooling implementation: produces a
– name of the database
Connection object that will automatically participate
– network protocol to use to communicate
in connection pooling
with the server
– Distributed transaction implementation: produces a
?
The benefit is that because the data source's
Connection object that may be used for distributed
properties can be changed, any code accessing
transactions and almost always participates in
that data source does not need to be changed
connection pooling
?
In the Sun Java System Application Server, a
data source is called a JDBC resource

---

03/19/2007
Where Are Properties of a
DataSource (JDBC Resource) Definition in
DataSource Defined?
Sun Java System App Server's domain.xml
<resources>
?
In container's configuration file
<jdbc-resource enabled="true" jndi-name="jdbc/BookDB" object-
type="user" pool-name="PointBasePool"/>
?
In Sun Java System App Server, they are
<jdbc-connection-pool connection-validation-method="auto-commit"
defined in
datasource-classname="com.pointbase.xa.xaDataSource" fail-all-
connections="false" idle-timeout-in-seconds="300" is-connection-
– <J2EE_HOME>/domains/domain1/config/domain.
validation-required="false" is-isolation-level-guaranteed="true" max-
xml
pool-size="32" max-wait-time-in-millis="60000" name="PointBasePool"
pool-resize-quantity="2" res-type="javax.sql.XADataSource" steady-
?
In Tomcat, they are defined in server.xml
pool-size="8">
<property name="DatabaseName"
– <TOMCAT_HOME>/conf/server.xml
value="jdbc:pointbase:server://localhost:9092/sun-appserv-samples"/>
<property name="Password" value="pbPublic"/>
<property name="User" value="pbPublic"/>
</jdbc-connection-pool>
</resources>
DataSource Definition in Sun
DataSource Definition in Sun
Java System App Server
Java System App Server
27
28

---

03/19/2007
JNDI Registration of a
JNDI Registration of a DataSource
DataSource Object
(JDBC Resource) Object
?
A driver that is accessed via a DataSource
?
The JNDI name of a JDBC resource is
object does not register itself with the
expected in the java:comp/env/jdbc
DriverManager
subcontext
?
Rather, a DataSource object is registered to
– For example, the JNDI name for the resource of
JNDI naming service by the container and
a BookDB database could be
then retrieved by a client though a lookup
java:comp/env/jdbc/BookDB
operation
?
Because all resource JNDI names are in the
?
With a basic implementation, the connection
obtained through a DataSource object is
java:comp/env subcontext, when you
identical to a connection obtained through
specify the JNDI name of a JDBC resource
the DriverManager facility
enter only jdbc/name. For example, for a
payroll database, specify jdbc/BookDB
Connection Pooling &
Why Connection Pooling?
DataSource
?
Database connection is an expensive
and limited resource
?
DataSource objects that implement
connection pooling also produce a
– Using connection pooling, a smaller number
of connections are shared by a larger number
connection to the particular data source
of clients
that the DataSource class represents
?
Creating and destroying database
?
The connection object that the
connections are expensive operations
getConnection method returns is a
– Using connection pooling, a set of
handle to a PooledConnection object
connections are pre-created and are
rather than being a physical connection
available as needed basis cutting down on
the overhead of creating and destroying
– The application code works the same way
database connections

---

03/19/2007
Example: PointBasePool
Retrieval and Usage of a
DataSource Object
?
Application perform JNDI lookup operation
?
The Sun Java Application Server 8 is
to retrieve DataSource object
distributed with a connection pool
named PointBasePool, which handles
?
DataSource object is then used to retrieve a
connections to the PointBase database
Connection object
server
?
In the application's web.xml, information on
external resource, DataSource object in this
?
Under Sun Java Application Server,
case, is provided
each DataSource object is associated
?
For Sun Java System App server, the
with a connection pool
mapping of external resource and JNDI
name is provided
– This provides further flexibility
Example: Retrieval of
JNDI Resource Information in
DataSource Object via JNDI
bookstore1's web.xml
?
BookDBAO.java in bookstore1 application
public class BookDBAO {
private ArrayList books;
Connection con;
private boolean conFree = true;
<resource-ref>
<res-ref-name>jdbc/BookDB</res-ref-name>
public BookDBAO() throws Exception {
try {
<res-type>javax.sql.DataSource</res-type>
Context initCtx = new InitialContext();
<res-auth>Container</res-auth>
Context envCtx = (Context) initCtx.lookup("java:comp/env");
DataSource ds = (DataSource)
<res-sharing-scope>Shareable</res-sharing-scope>
envCtx.lookup("jdbc/BookDB");
</resource-ref>
con = ds.getConnection();
} catch (Exception ex) {
throw new Exception("Couldn't open connection to database: " +
ex.getMessage());
}
}

---

03/19/2007
JNDI and Resource Mapping in
bookstore1's sun-web.xml
<sun-web-app>
Transaction
<context-root>/bookstore1</context-root>
<resource-ref>
<res-ref-name>jdbc/BookDB</res-ref-name>
<jndi-name>jdbc/BookDB</jndi-name>
</resource-ref>
</sun-web-app>
Transaction
JDBC Transaction Methods
?
setAutoCommit()
?
One of the main benefits to using a
PreparedStatement is executing the
– If set true, every executed statement is
statements in a transactional manner
committed immediately
?
The committing of each statement when it is
?
commit()
first executed is very time consuming
– Relevant only if setAutoCommit(false)
?
By setting AutoCommit to false, the
– Commit operations performed since the
developer can update the database more
opening of a Connection or last commit()
then once and then commit the entire
transaction as a whole
or rollback() calls
?
rollback()
?
Also, if each statement is dependant on the
other, the entire transaction can be rolled
– Relevant only if setAutoCommit(false)
back and the user notified.
– Cancels all operations performed

---