Transcript 09_JDBC - Andrew.cmu.edu

Java’s JDBC
• Allows access to any ANSI SQL-2 DBMS
• Does its work in terms of SQL
• The JDBC has classes that represent:
database connections
SQL Statements
Result sets
database metadata
• Can be connected to ODBC
• We’ll review some database terminology
• And look over a simple stand alone JDBC Application
95-712 Object Oriented Programming
Java
Database Terminology
• Database:
A shared collection of logically related
data (and a description of this data) designed to meet the
information needs of an organization
• Relation: A table with columns and rows
• Attribute: A named column of a relation
• Tuple: A row in a relation
Gary Alperson helped developed these
slides and the JDBC example.
Definitions from Database Systems
by Connolly, Begg, and Strachan
95-712 Object Oriented Programming
Java
Sample Table
broker
b_id lname
1
2
3
4
5
6
7
Smith
Jones
Reynolds
Chang
Smith
Thompson
Frendun
fname
John
Hannah
Leon
Donna
Deborah
Daniel
Laura
95-712 Object Oriented Programming
Java
broker
b_id lname
1
2
3
4
5
6
7
Smith
Jones
Reynolds
Chang
Smith
Thompson
Frendun
fname
John
Hannah
Leon
Donna
Deborah
Daniel
Laura
Attribute
95-712 Object Oriented Programming
Java
broker
b_id lname
Tuple
1
2
3
4
5
6
7
Smith
Jones
Reynolds
Chang
Smith
Thompson
Frendun
fname
John
Hannah
Leon
Donna
Deborah
Daniel
Laura
95-712 Object Oriented Programming
Java
SQL
• Data Definition Language (DDL)
– Create tables
– Modify tables
– Delete (drop) tables
• Data Manipulation Language (DML)
– Insert data
– Update data
– Select data
95-712 Object Oriented Programming
Java
Select Statement
We will use this data for our examples
broker
b_id lname
1
2
3
4
5
6
7
Smith
Jones
Reynolds
Chang
Smith
Thompson
Frendun
fname
John
Hannah
Leon
Donna
Deborah
Daniel
Laura
95-712 Object Oriented Programming
Java
From the broker table, select the contents of
the last name attribute
Query
Results
lname
Smith
Jones
Reynolds
Chang
Smith
Thompson
Frendun
SELECT lname
FROM broker;
SQL is not case sensitive. Key SQL words are capitalized
and line breaks are inserted by convention.
95-712 Object Oriented Programming
Java
From the broker table, select all attributes
Query
Results
broker
SELECT *
FROM broker;
b_id lname
1
2
3
4
5
6
7
* Acts as a wildcard
95-712 Object Oriented Programming
Java
Smith
Jones
Reynolds
Chang
Smith
Thompson
Frendun
fname
John
Hannah
Leon
Donna
Deborah
Daniel
Laura
From the broker table, select all attributes
where the last name is Smith
Query
Results
broker
SELECT *
FROM broker
WHERE lname = ‘Smith’;
b_id lname
1 Smith
5 Smith
•Note that the string is enclosed by single quotes
•The contents of a string are case sensitive
95-712 Object Oriented Programming
Java
fname
John
Deborah
Use AND or OR to connect multiple where
clauses
Query
SELECT *
FROM broker
WHERE lname = ‘Smith’
AND fname = ‘John’;
Results
b_id lname
1 Smith
95-712 Object Oriented Programming
Java
fname
John
Example with two Tables
broker
b_id lname
1
2
3
4
5
6
7
Smith
Jones
Reynolds
Chang
Smith
Thompson
Frendun
fname
customer
customer
id b_id
John
Hannah
Leon
Donna
Deborah
Daniel
Laura
1
2
3
4
5
6
7
lname fname
1
1
2
2
2
3
4
LeParc Wilson
AnstinceDevon
Tabor Mark
Lenks Sandy
PhillipsonRichard
Kini
Raghu
Kim
David
One-to-many relationship
•Each broker may have many customers
•Each customer is only affiliated with one broker
•The b_id joins both
tables by identifying the unique broker
95-712 Object Oriented Programming
that each customer is associated
Java with
Cartesian Product
broker. broker. broker. id customer. broker. broker.
b_id
lname fname
b_id
lname fname
When you do a query on
multiple tables, SQL begins by
creating the Cartesian product,
which combines each tuple
from one relation from every
tuple of the other relation.
(Actual SQL implementations
are free to compute the resulting
table efficiently,i.e., the actual
Cartesian product may not
be generated at all.)
1
1
1
1
1
1
1
2
2
2
2
2
2
2
3
3
3
3
3
3
3
4
4
4
4
4
4
4
Smith
Smith
Smith
Smith
Smith
Smith
Smith
Jones
Jones
Jones
Jones
Jones
Jones
Jones
Reynolds
Reynolds
Reynolds
Reynolds
Reynolds
Reynolds
Reynolds
Chang
Chang
Chang
Chang
Chang
Chang
Chang
John
John
John
John
John
John
John
Hannah
Hannah
Hannah
Hannah
Hannah
Hannah
Hannah
Leon
Leon
Leon
Leon
Leon
Leon
Leon
Donna
Donna
Donna
Donna
Donna
Donna
Donna
95-712 Object Oriented Programming
Java
1
2
3
4
5
6
7
1
2
3
4
5
6
7
1
2
3
4
5
6
7
1
2
3
4
5
6
7
1
1
2
2
2
3
4
1
1
2
2
2
3
4
1
1
2
2
2
3
4
1
1
2
2
2
3
4
LeParc
Anstince
Tabor
Lenks
Phillipson
Kini
Kim
LeParc
Anstince
Tabor
Lenks
Phillipson
Kini
Kim
LeParc
Anstince
Tabor
Lenks
Phillipson
Kini
Kim
LeParc
Anstince
Tabor
Lenks
Phillipson
Kini
Kim
Wilson
Devon
Mark
Sandy
Richard
Raghu
David
Wilson
Devon
Mark
Sandy
Richard
Raghu
David
Wilson
Devon
Mark
Sandy
Richard
Raghu
David
Wilson
Devon
Mark
Sandy
Richard
Raghu
David
Query
Results
SELECT *
FROM customer, broker
WHERE broker.b_id = 1;
broker. broker. broker. id customer. broker. broker.
b_id
lname fname
b_id
lname fname
1
1
1
1
1
1
1
Smith
Smith
Smith
Smith
Smith
Smith
Smith
John
John
John
John
John
John
John
1
2
3
4
5
6
7
1
1
2
2
2
3
4
LeParc
Anstince
Tabor
Lenks
Phillipson
Kini
Kim
Wilson
Devon
Mark
Sandy
Richard
Raghu
David
SQL does not realize that the b_id in the customer table is the
same as the b_id in the broker table unless you join them in the
where clause.
95-712 Object Oriented Programming
Java
Cartesian Product
Query
SELECT *
FROM customer, broker
WHERE broker.b_id = 1
AND broker.b_id = customer.b_id;
Results
broker. broker. broker. id customer. broker. broker.
b_id
lname fname
b_id
lname fname
1 Smith
1 Smith
John
1
1 LeParc
Wilson
John
2
1 Anstince Devon
95-712 Object Oriented Programming
Java
ODBC
ODBC is a programming interface that
enables applications to access data in
database systems that use Structured Query
Language (SQL) as a data standard.
95-712 Object Oriented Programming
Java
Creating an ODBC Connection
•
•
•
•
•
Click on the Start button.
Choose Settings, Control Panel
Double-click on ODBC Data Sources
Choose the System DSN tab
Click Add
95-712 Object Oriented Programming
Java
•Click on the desired driver (MSAccess)
•Click on the Finish button
95-712 Object Oriented Programming
Java
•Enter a Data Source Name
•Click on the Select button
•Locate the desired file or directory
•Click OK
95-712 Object Oriented Programming
Java
SQL Query as a Java String
From both tables select the last names of all customers whose
broker’s last name is Smith but whose broker ID is not 1.
The SQL
SELECT customer.lname
FROM customer, broker
WHERE broker.lname = ‘Smith’
AND broker.b_id <> 1
AND broker.b_id = customer.b_id;
95-712 Object Oriented Programming
Java
Executing a query in Java
// Statement aStatement = statement got from connection
String last = “Smith”;
int nonID = 1;
String q = “SELECT customer.lname FROM customer, broker” +
“WHERE broker.lname = \’” + last + “\’ AND broker.b_id” +
“<>” + nonID + “AND broker.b_id = customer.b_id;”);
ResultSet rs = aStatement.executeQuery(q);
•The slash (\) is the escape character. It precedes the single quote to tell Java
to include that quote in the String
•The String last is outside of the double quotes, because it must be
concatonated with the String sent to the database, but it falls within the single
quotes so that SQL treats it as a string
•nonID does not go within single quotes since it is numeric
Object Oriented
•Since the String is an SQL 95-712
statement,
it usesProgramming
= and <> rather than == and !=
Java
A Simple Application
// This program makes use of a stock database
// and the primary JDBC classes (Connection, Statement,
// ResultSet and ResultSetMetaData)
import java.util.*;
import java.sql.*;
import java.io.*;
public class TestCoolStocksDB {
public static void main(String args[]) {
Connection con = null;
95-712 Object Oriented Programming
Java
Statement s = null;
ResultSet rs = null;
ResultSetMetaData rsm = null;
String answer = "";
try {
DriverManager.registerDriver(
new sun.jdbc.odbc.JdbcOdbcDriver());
con = DriverManager.getConnection("jdbc:odbc:CoolStocks");
s = con.createStatement();
rs = s.executeQuery("select * from customer");
rsm = rs.getMetaData();
95-712 Object Oriented Programming
Java
while(rs.next()) {
for(int col = 1; col <= rsm.getColumnCount(); col++)
answer += rs.getString(col);
}
con.close();
}
catch (SQLException sqle) {
System.err.println("Exception caught in main:" + sqle);
}
System.out.println(answer);
}
}
95-712 Object Oriented Programming
Java
A Simple JSP/JDBC Example
stocks.mdb database schema
There are three tables. Both customer and stocks have a oneto-many relationship with portfolios. The database stocks.mdb
was registered with the ODBC driver as “CoolStocks”
customer stocks portfolio
id
lname
fname
symbol
id
company symbol
95-712 Object
Oriented Programming
price
num_shares
Java
Register w/ODBC
Create an ODBC data source.
Click on the Start button.
Choose Settings, Control Panel
Double-click on ODBC Data Sources
Choose the System DSN tab
Click Add
Click on the desired driver (MSAccess)
Click on the Finish button
Enter a Data Source Name (I called my database CoolStocks
and that name appears in the java code below)
Click on the Select button
Locate the directory and file containing your database. This will be
the “stock.mdb” file created
by Microsoft Access.
95-712 Object Oriented Programming
Java
Click OK
A Simple JSP/JDBC Example
<TITLE>JSP JDBC Example 1</TITLE>
</HEAD>
<BODY>
<!– Adapted from James Goodwill’s Pure JSP 
<!-- Set the scripting language to java and -->
<!-- import the java.sql package -->
<%@ page language="java" import="java.sql.*" %>
<%@ page import= "java.io.*" %>
95-712 Object Oriented Programming
Java
<%
Connection con = null;
try {
// Load the Driver class file
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
// Make a connection to the ODBC datasource Movie Catalog
con = DriverManager.getConnection("jdbc:odbc:CoolStocks");
// Create the statement
Statement statement = con.createStatement();
// Use the created statement to SELECT the DATA
// FROM the customer Table.
ResultSet rs = statement.executeQuery("SELECT * " +
"FROM customer");
// Iterate over the95-712
ResultSet
Object Oriented Programming
Java
%>
<!-- Add an HTML table to format the results -->
<TABLE BORDER="1">
<TR>
<TH> Customer - ID</TH><TH>Last Name</TH>
<TH>First Name</TH>
<%
while ( rs.next() ) {
// get the id, convert to String
out.println("<TR>\n<TD>" + rs.getString("id") + "</TD>");
// get the last name
out.println("<TD>" + rs.getString("lname") + "</TD>");
// get the first name
out.println("<TD>" + rs.getString("fname") + "</TD>\n</TR");
}
95-712 Object Oriented Programming
Java
// Close the ResultSet
rs.close();
}
catch (IOException ioe) {
out.println(ioe.getMessage());
}
catch (SQLException sqle) {
out.println(sqle.getMessage());
}
catch (ClassNotFoundException cnfe) {
out.println(cnfe.getMessage());
}
catch (Exception e) {
out.println(e.getMessage());
}
95-712 Object Oriented Programming
Java
finally {
try {
if ( con != null ) {
// Close the connection no matter what
con.close();
}
}
catch (SQLException sqle) {
out.println(sqle.getMessage());
}
}
%>
</BODY>
</HTML>
95-712 Object Oriented Programming
Java
It Works!
95-712 Object Oriented Programming
Java