Transcript JDBC Drivers

Integrating Web Applications
With Databases
Think Possibility
Database Overview
• A database is a collection of logically related data. It is
usually managed bya a database management system
(DBMS).
• For an RDBMS, the language standard that supports
database operations is called Structured Query
Language (SQL).
Think Possibility
Database Overview
•
An RDBMS allows you to perform four fundamental
operations:
1.
2.
3.
4.
Think Possibility
Create a row in a table.
Retrieve one or more rows in a table.
Update some data in a table
Delete one or more rows in a table.
The JDBC API
• JDBC is the Java technology API for interacting with a
DBMS.
• The JDBC API includes interfaces that manage
connections to the DBMS, statements to perform
operations and result sets that encapsulate the result
of retrieval operations.
Think Possibility
Introduction to JDBC
• JDBC is a simple API for connecting from Java
applications to multiple databases.
• Lets you smoothly translate between the world of the
database, and the world of the Java application.
• The idea of a universal database access API is not a new
one. For example, Open Database Connectivity (ODBC)
was developed to create a single standard for database
access in the Windows environment.
• JDBC API aims to be as simple as possible while
providing developers with maximum flexibility.
Think Possibility
Understanding JDBC
Drivers
• To connect to a database,
you first need a JDBC Driver.
• JDBC Driver: set of classes
that interface with a specific
database engine.
Java Application
JDBC Driver Manager
JDBCODBC
Bridge
Vendor
Specific
JDBC Driver
Vendor
Specific
JDBC Driver
Database
Database
Think Possibility
Diagram Source: Marty Hall,
Core Web Programming
(Prentice Hall.)
JDBC Drivers
• JDBC drivers exist for every major database including:
Oracle, SQL Server, Sybase, and MySQL.
• For MySQL, we will be using the open source MySQL
Connector/J.
• http://www.mysql.com/downloads/api-jdbc.html.
Think Possibility
Installing the MySQL Driver
• To use the MySQL Connector/J Driver, you need to
download the complete distribution.
• Once you have extracted the distribution archive, you
can install the driver by placing mysql-connector-java[version]-bin.jar in your classpath, either by adding
the FULL path to it to your CLASSPATH enviornment
variable.
• To use the driver within Tomcat, copy the jar file above
to:
[TOMCAT_HOME]\webapps\ROOT\WEB-INF\lib
Think Possibility
Six Steps to Using JDBC
1.
2.
3.
4.
5.
6.
Load the JDBC Driver
Establish the Database Connection
Create a Statement Object
Execute a Query
Process the Results
Close the Connection
Think Possibility
1) Loading the JDBC Driver
• To use a JDBC driver, you must load the driver via the
Class.forName() method.
• In general, the code looks like this:
Class.forName("jdbc.DriverXYZ");
– where jbdc.DriverXYZ is the JDBC Driver you want to
load.
• If you are using a JDBC-ODBC Driver, your code will look
like this:
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Think Possibility
Loading the MySQL Driver
• With MySQL Connector/J, the name of this class is
com.mysql.jdbc.Driver. With this method, you could
use an external configuration file to supply the driver
class name and driver parameters to use when
connecting to a database.
try{
Class.forName("com.mysql.jdbc.Driver");
} catch(java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());}
• Class.forName() will throw a ClassNotFoundException if
your CLASSPATH is not set up properly.
• Hence, it's a good idea to surround the forName() with
a try/catch block.
Think Possibility
2) Establish the Connection
• Once you have loaded your JDBC driver, the next step is
to establish a database connection.
• The following line of code illustrates the basic idea:
Connection con = DriverManager.getConnection(url,
"myLogin", "myPassword");
Think Possibility
Creating a Connection URL
• The only difficulty in establishing a connection is
specifying the correct URL.
• In general, the URL has the following format:
jdbc:subprotocol:subname.
– JDBC indicates that this is a JDBC Connection (no
mystery there! )
– The subprotocol identifies the driver you want to
use.
– The subname identifies the database name/location.
Think Possibility
Example - ODBC
• If you are using the JDBC-ODBC Bridge driver, the JDBC
URL will start with jdbc:odbc: .
• The rest of the URL is generally your data source name
or database system.
String url = "jdbc:odbc:basketball";
Connection con = DriverManager.getConnection(url,
“admin", “adminpasswd");
Think Possibility
Example - MySQL
• Here's how you might connect to MySQL:
String url = "jdbc:mysql://localhost/basketball";
Connection con = DriverManager.getConnection(url);
• In this case, we are using the MySQL JDBC Driver to
connect to the basketball database, located on the
localhost machine.
• If this code executes successfully, we will have a
Connection object for communicating directly with the
database.
Think Possibility
3) Create a Statement Object
• The JDBC Statement object sends SQL statements to
the database.
• Statement objects are created from active Connection
objects.
• For example:
Statement stmt = con.createStatement();
• With a Statement object, you can issue SQL calls
directly to the database.
Think Possibility
4) Execute a Query
• executeQuery()
– Executes the SQL query and returns the data in a
table (ResultSet)
– The resulting table may be empty but never null
ResultSet results =stmt.executeQuery("SELECT a, b
FROM table");
• executeUpdate()
– Used to execute for INSERT, UPDATE, or DELETE SQL
statements
– The return is the number of rows that were affected
in the database
– Supports Data Definition Language (DDL) statements
CREATE TABLE, DROP TABLE and ALTER TABLE
Think Possibility
Useful Statement Methods
• getMaxRows/setMaxRows
– Determines the number of rows a ResultSet may
contain
– Unless explicitly set, the number of rows are
unlimited (return value of 0)
• getQueryTimeout/setQueryTimeout
– Specifies the amount of a time a driver will wait for
a STATEMENT to complete before throwing a
SQLException
Think Possibility
5) Process the Results
• A ResultSet contains the results of the SQL query.
• Useful Methods
• All methods can throw a SQLException
– close
• Releases the JDBC and database resources
• The result set is automatically closed when the
associated Statement object executes a new
query
– getMetaDataObject
• Returns a ResultSetMetaData object containing
information about the columns in the ResultSet
Think Possibility
ResultSet (Continued)
• Useful Methods
– next
• Attempts to move to the next row in the
ResultSet
– If successful true is returned; otherwise, false
– The first call to next positions the cursor a the
first row
Think Possibility
ResultSet (Continued)
• Useful Methods
– findColumn
• Returns the corresponding integer value
corresponding to the specified column name
• Column numbers in the result set do not
necessarily map to the same column numbers in
the database
– getXxx
• Returns the value from the column specified by
column name or column index as an Xxx Java type
• Returns 0 or null, if the value is a SQL NULL
• Legal getXxx types:
Think Possibility
double
byte
int
Date
String
float
short
long
6) Close the Connection
• To close the database connection:
– stmt.close();
– connection.close();
• Note: Some application servers, such as BEA WebLogic
maintain a pool of database connections.
– This is much more efficient, as applications do not
have the overhead of constantly opening and closing
database connections.
Think Possibility
Designing a Web Application
Using Database
• To build a database application you must design the
relationship between the Model objects and the
corresponding database representation.
• To design the Model elements of an application, you should
perform the following tasks:
– Design the domain objects of your application
– Design the database tables that map to the domain
objects
– Design the business services (the Model) to separate the
database code into classes using the DAO pattern.
Think Possibility
Case Study: The Basketball
League
• Domain objects represent the real-world business
entities of your application.
League
*
*
objectID
year
season
title
Registration
division
Think Possibility
Player
objectID
name
address
city
province
postal_code
Database Tables
• Each domain objects has a corresponding DB table.
<<DB Table>>
League
LID
year
season
title
*
<< DB Table>>
Registration
LID
PID
division
<< DB Table>>
ObjectIDs
table_name
ID_number
Think Possibility
*
<<DB Table>>
Player
objectID
name
address
city
province
postal_code
Example Data
Think Possibility
Data Access Object (DAO)
Pattern
• The DAO pattern makes it easier to maintain
applications that use databases by separating the
business logic from the data access logic.
• The DAO pattern permits the business logic and the
data access logic to change independently.
Think Possibility
DAO Pattern Example
Player
RegisterService
Registration
servlet
+getLeague
+getPlayer
+register
RegisterService
LeagueAdmin
servlet
Think Possibility
+getLeague
+createLeague
<<package-private>>
PlayerDAO
insert
League
<<package-private>>
LeagueDAO
retrieve
insert
<<package-private>>
ObjectIDDAO
getNextObjectID
Advantages of the DAO
Pattern
• Business logic and data access logic are now separate.
• The data access objects promote reuse and flexibility
in changing the system.
• Developers writing other servlets can reuse the same
data access code.
• This design makes it easy to change front-end
technologies.
• This design makes it easy to change back-end
technologies.
Think Possibility
Using a Connection Pool
• The DAO pattern hides the details of the data access
logic from the rest of the system. The DAO classes
require connections to access data in a DBMS.
• To develop a Web application that uses a connection
pool (CP) with the DAO pattern, consider the ff:
– Build or buy a CP subsystem
– Store the CP object in a global “name space”
– Design your DAOs to use the CP, retrieving it from
the name space
– Develop a servlet context listener to initialize the CP
and store it in the name space
Think Possibility
Connection Pool
Think Possibility