Transcript Use Cases

Java Database
Connectivity (JDBC)
Francisco Pajaro
Saul Acosta
Nahum Quezada
Manuel Rubio
Lecture Outline













The JDBC API
The JDBC Driver Interface
The Most Common Use of JDBC
Structured Query Language
How to Construct SQL Statements
The JDBC-ODBC Bridge
Register your Database as an ODBC Data Source
Things to Do in your Java Program
Load the Drivers
Make the connection
Create JDBC Statements
Execute the SQL Statement
Retrieve Values from the Result Set
JDBC Introduction
 • JDBC provides a standard library for accessing
 relational databases
 API standardizes
• Way to establish connection to database
• Approach to initiating queries
• Method to create stored (parameterized) queries
• The data structure of query result (table)
– Determining the number of columns
– Looking up metadata, etc.
 API does not standardize SQL syntax
• JDBC is not embedded SQL
 JDBC classes are in the java.sql package
• Note: JDBC is not officially an acronym;
unofficially, “Java DataBase Connectivity” is
commonly used





The JDBC API is expressed as a series of abstract Java
interfaces that allow an application programmer to open
connections to particular databases, execute SQL statements,
and process the results. The most important interfaces are:
java.sql.DriverManager which handles loading of drivers and
provides support for creating new database connections
java.sql.Connection which represents a connection to a
particular database
java.sql.Statement which acts as a container for executing a
SQL statement on a given connection
java.sql.ResultSet which controls access to the row results of a
given Statement
The JDBC Driver Interface
• JDBC consists of two parts:
– JDBC API, a purely
Java-based API
– JDBC Driver Manager,which
communicates with
vendor-specific drivers that
perform the real communication
with the database.
• Point: translation to vendor
format is performed on
the client
– No changes needed
to server
– Driver (translator) needed
on client
Application in Java
Application in
Java
Sybase driver
DriverManager
mSQL driver
Informix driver
The Most Common Use of
JDBC

“Perhaps called the Intranet scenario. For example, a company the
most common use of these Java applications will be within a
company or on an "Intranet," so this might be called the Intranet
scenario. For example, a company might implement all of its
corporate applications in Java using GUI building tools that generate
Java code for forms based on corporate data schemas. These
applications would access corporate database servers on a local or
wide area network. However, Java applications could also access
databases through the Internet.” The JDBC Guide
Structured Query Language
SQL Keyword
Description
SELECT
Select fields from one or more tables
FROM
Tables from which to get fields
WHERE
Criteria for selection that determine
the rows to be retrieved
GROUP BY
How to group records
HAVING
Used by the GROUP BY clause to
specify criteria for grouping records
in the results
ORDER BY
Criteria for ordering of records
JDBC Data Types
Executing a SELECT query

First create a Statement object
Statement stmt = conn.createStatement();

Then create a query string. For example
String query = "SELECT * FROM table";

Execute the query
ResultSet rs = stmt.executeQuery(query);

The ResultSet object returned contains
the results of the query.
Getting query results (1)

Column names and number of columns. If
rs is a ResultSet object then number of
columns in table is
ResultSetMetaData rsmd = rs.getMetaData();
int numberOfColumns = rsmd.getColumnCount();

The name of column i (i=1,2,3,...) is
String colName = rsmd.getColumnName(i)
Getting query results (2)

Iterating over the rows of a table
while (rs.next())
{
for (int i = 1; i <= numberOfColumns; i++)
{
String columnValue = rs.getString(i);
// do something with columnValue
}
}
There are lots of get methods for retriving column values as
integers, doubles, etc.
Executing a simple command

First create a Statement object
Statement stmt = conn.createStatement();

Then create a command string. For
example
String cmd = "DROP TABLE IF EXISTS test";

Execute the command
stmt.execute(cmd);
The JDBC-ODBC Bridge


To connect to an MS Access Database you will use
JDBC-ODBC bridge, which is included as a
standard part of the JDK. You connect using the
ODBC Administrator, to connect to the appropriate
data source name.
Because ODBC has been around for quite a while
(longer than the Java language), ODBC drivers are
rather ubiquitous. This makes this type of JDBC
driver a good choice for learning how to connect
Java programs to databases. The extra level of
indirection, however, can result in a performance
penalty as the JDBC is transferred into ODBC,
which is then transferred into the database-specific
protocol.
ODBC
Client
Front-end
Application
ODBC
DB
Server
Started as a PC-standard. Now it’s an industry standard.
JDBC-ODBC Bridge





A layer between the Java front-end application
and the database server
Client talks to JDBC
JDBC communicates with underlying ODBC
ODBC communicates with database server
Results passed in reverse order to above
Necessary classes




Connection: Establishes a connection with the
SQL database driver manager
Statement: Allows developer to frame fixed SQL
queries
PreparedStatement: Allows developer to frame
SQL queries with parameters
ResultSet: Used to collect the results of an SQL
query
What is a data source?
A data source is a source of data and
the connection information needed to
access that data.
Examples:
 Microsoft Access
 Microsoft SQL Server
 Oracle
 spreadsheet
 text file
Examples of connection information:
Server location
 Database name
 Logon ID
 password
 options that describe how to connect to
the data source

To connect to these data
sources, you must do the
following:
 Install the appropriate ODBC driver on the
computer that contains the data source
Microsoft SQL Server
 Define a data source name (DSN) by
using either the ODBC Data Source
Administrator spreadsheet
How to register your Database as
an ODBC Data Source?
 Open up your ODBC Data Sources Administrator.
 Click on the System DSN tab and click on the “Add” button.
 The “Create a New Data Source” dialog will appear
 Select the driver and click on “Finish”.
 The “ODBC Microsoft Access Setup” dialog will appear.
 In the “Data Source Name” box, enter a name.
 Click the Select Directory button.
 Locate the data base.
 Click on “Ok” to exit the dialog.
 And finally, you should now be able to
access your data source using ODBC.
Things to Do in your Java
Program
Load the Drivers
 Establish the Connection
 Create JDBC Statement
 Execute the SQL Statement
 Retrieve Values from the Result Set

Load the Drivers
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 JDBCODBC Bridge driver, the following code will
load it:
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Overview of Database Querying
with JDBC
Connect
Query
Process
results
Close
Key Classes and Methods in
java.sql
DriverManager
getConnection()
Connection
createStatement()
Statement
executeQuery()
ResultSet
getMetaData()
next()
getString()
ResultSetMetaDa
ta
getColumnCount()
Stage 1: Connect
Connect
Register the driver
Query
Connect to the database
Process
results
Close
Make the connection
 The
second step in establishing a connection is to
have the appropriate driver connect to the DBMS. The
following lines of code illustrates the general idea:
String dataSourceName = "mdbTEST";
String dbURL = "jdbc:odbc:" + dataSourceName;
Connection con = DriverManager.getConnection
(dbURL, "admin","students");
Stage 2: Query
Connect
Query
Create a statement
Process
results
Query the database
Close
Create JDBC Statements


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 .
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 s = con.createStatement( );
Execute the SQL Statement

At this point s exists, but it does not have an SQL
statement to pass on to the DBMS. We need to supply
that to the method we use to execute s . For example, in
the following code fragment, we supply executeQuery
with the SQL statement from the example above:
s.execute("CREATE TABLE Students
(ID_Number integer,
FirstName VARCHAR(35),
LastName VARCHAR(35),
EMail VARCHAR(35),
University VARCHAR(35))");
ResultSet ResultSet = s.executeQuery(query);
Stage 3: Process the Results
Connect
Query
Step through the results
Process
results
Close
Assign results to Java
variables
Retrieve Values from the Result Set


We now show how you send the SELECT statements
from a program written in the Java programming
language and how you get the results we showed.
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 and assigning the results of our
earlier query to it:
String query = "SELECT * FROM Students";
ResultSet ResultSet = s.executeQuery(query);
Inserting/Updating/Deleting
Records


Statement.executeUpdate
Executes an SQL INSERT, UPDATE or DELETE
statement.

Parameters:
 sql - an SQL INSERT, UPDATE or DELETE statement
or an SQL statement that returns nothing
 Returns: Either the row count for INSERT, UPDATE or
DELETE statements, or 0 for SQL statements that return
nothing
s.execute("INSERT INTO Students VALUES
(890124532 ,
' Manuel ',
' Rubio ',
' [email protected]',
' UTEP')");
Inserting/Updating/Deleting
Records

s.execute("UPDATE Students
SET
FirstName = ' Alonso '
WHERE LastName = 'Rubio '");
s.execute("DELETE FROM Students
WHERE LastName = ' Pajaro'")
Stage 4: Close
Connect
Query
Close the result set
Process
results
Close the statement
Close
Close the connection
How to Close the Connection
1. Close the ResultSet object.
ResultSet.close();
2. Close the Statement object.
s.close();
3. Close the connection (not necessary for a
server-side driver).
con.close();