Transcript Use the

Accessing the Database with Servlets
Copyright © 2004, Oracle. All rights reserved.
Objectives
After completing this lesson, you should be able to do
the following:
• Load and register a JDBC driver
• Connect to an Oracle database by using data
sources
• Navigate in a ResultSet
• Use PreparedStatement
•
5-2
Create a pool of connections
Copyright © 2004, Oracle. All rights reserved.
Review of JDBC
•
•
5-3
JDBC is a standard interface for connecting to
relational databases from Java.
The JDBC classes and interfaces are in the
java.sql package.
Copyright © 2004, Oracle. All rights reserved.
Querying in JDBC
Connect
Query
Process results
Close
5-4
import java.sql.*;
DriverManager.registerDriver(…)
Connection conn =
DriverManager.getConnection(
"jdbc:oracle:thin …
Statement stmt =
conn.createStatement ();
ResultSet rset =
stmt.executeQuery (
"select * from EMPLOYEES");
while (rset.next ())
System.out.println(
rset.getString (2));
rset.close();
stmt.close();
conn.close();
Copyright © 2004, Oracle. All rights reserved.
JDBC and Servlets
•
There are three ways to use JDBC in a servlet:
– Register the JDBC driver within the servlet by hard
coding the driver name in either the servlet or in a
properties file.
– Use the JDBC driver from the data-sources.xml
file that is provided with Oracle Application Server
10g.
– Use a properties file to store connection details.
•
In all cases, optimize the connection:
– Initialize the database connection in the servlet’s
init() method (or retrieve from a pool).
– Close the database connection in the destroy()
method (or return to a pool).
5-5
Copyright © 2004, Oracle. All rights reserved.
Synchronizing Shared Resources
•
•
•
Reuse PreparedStatement objects.
Sharing Statement objects may not be
thread safe.
Use a synchronized block.
PreparedStatement ps = …
…
synchronized (ps) {
ps.clearParameters();
ps.setInt(1,3);
ps.setDouble(2, 3.14);
ps.executeUpdate();
}
5-6
Copyright © 2004, Oracle. All rights reserved.
Transaction Handling
Initialize Connection in the servlet’s init() method.
•
Problems with transactions:
– The Connection object in the servlet’s init()
method is shared.
– The commit() method depends on the Connection
object.
•
Solutions for transactions:
– Create a new Connection object.
– Use the synchronized keyword.
– Use the SingleThreadModel interface.
– Use session tracking.
5-7
Copyright © 2004, Oracle. All rights reserved.
Connection Pooling
Pooled Connection objects are used and released by
servlet instances. A connection pool performs the
following tasks:
• Preallocates database connections
• Manages available connections
• Allocates new connections
• Closes connections that are no longer in use
5-9
Copyright © 2004, Oracle. All rights reserved.
Data Sources
•
Data sources provide logical mappings of
databases:
– Developer uses the logical representation of a
database.
– Deployer maps to the physical data sources.
•
J2EE applications use published DataSource
objects by:
– Looking up the published name via Java Naming
and Directory Interface (JNDI).
– Using JDBC Connection methods to connect to the
database.
•
•
5-10
Data sources are published in the JNDI tree.
Data sources come in different varieties.
Copyright © 2004, Oracle. All rights reserved.
Data Source Definition
•
Global data sources are defined in the
OC4J_HOME/config/data-sources.xml file.
– You specify each data source by using an XML tag.
– Attributes specify values for the data source.
•
Application-specific data sources: Use the <datasources> tag in the application.xml file.
OC4J_HOME/applications
myapp
application.xml
data-sources.xml
5-11
Points to
Copyright © 2004, Oracle. All rights reserved.
data-sources.xml: Example
<data-source
class="com.evermind.sql.DriverManagerDataSource"
connection-driver="oracle.jdbc.driver.OracleDriver"
name="OracleDS"
location="jdbc/OracleCoreDS"
xa-location="jdbc/xa/OracleDS"
ejb-location="jdbc/OracleDS"
min-connections="5"
max-connections="25"
username="oe"
password="oe"
url="jdbc:oracle:thin:@<host>:<port>:<SID>"
/>
5-12
Copyright © 2004, Oracle. All rights reserved.
Using Data Sources
To use a defined data source in a servlet:
1. Use the lookup method of the
javax.naming.Context class to retrieve the
named data source class.
2. Create the connection.
try {
Context ic = new InitialContext();
DataSource ds =
(DataSource)ic.lookup("jdbc/OracleDS");
Connection conn = ds.getConnection();
} catch (SQLException se) { … }
catch (NamingException ne) {… }
5-13
Copyright © 2004, Oracle. All rights reserved.
Summary
In this lesson, you should have learned how to:
• Create a servlet to connect to the database by
using JDBC
• Load and register a JDBC driver
• Connect to an Oracle database by using data
sources
• Navigate in a ResultSet
• Use PreparedStatement
•
5-14
Improve database performance by using
connection pooling
Copyright © 2004, Oracle. All rights reserved.
Practice 5-1: Overview
This practice covers the following topics:
• Connecting to the database by using JDBC
• Retrieving database information and formatting it
for output in a servlet
5-15
Copyright © 2004, Oracle. All rights reserved.