ODBC - Andrew.cmu.edu | studyslide.com

ODBC - Andrew.cmu.edu

Transcript ODBC - Andrew.cmu.edu

RMI and JDBC
• Some Database terminology
• Connecting a remote object to a database
Gary Alperson helped developed these slides and the
JDBC/RMI example.
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
Definitions from Database Systems
by Connolly, Begg, and Strachan
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
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
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
SQL
• Data Definition Language (DDL)
– Create tables
– Modify tables
– Delete (drop) tables
• Data Manipulation Language (DML)
– Insert data
– Update data
– Select data
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
From the broker table, select the contents of
the last name attribute
Query
Results
lname
SELECT lname
FROM broker;
Smith
Jones
Reynolds
Chang
Smith
Thompson
Frendun
SQL is not case sensitive. Key SQL words are capitalized
and line breaks are inserted by convention.
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
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
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
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
that each customer is associated 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
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
SELECT *
FROM customer, broker
WHERE broker.b_id = 1;
Results
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.
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
John
1
2
1 LeParc
Wilson
1 Anstince Devon
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.
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
•Click on the desired driver (MSAccess)
•Click on the Finish button
•Enter a Data Source Name
•Click on the Select button
•Locate the desired file or directory
•Click OK
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
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;
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
•Since the String is an SQL statement, it uses = and <> rather than == and !=
JDBC and RMI
• Create an ODBC data source that will allow
us to connect to an Access database (see above)
• Create a server class that registers an object with
the rmi registry.
• The remote object must have an interface on the
client and the server (needed to create the stub for
marshalling)
• The client accesses the remote object and calls its methods.
The remote methods access the database with SQL statements.
// On the server side
DBServer.java
import java.rmi.Naming;
public class DBServer {
Remote object
public DBServer() {
try {
StockDB rc = new StockDBImpl();
Naming.rebind("stocks", rc);
Register
} catch(Exception e) {
With rmi registry
System.out.println("Trouble: " + e);
}
}
public static void main(String args[]) {
new DBServer();
System.out.println("Server Active");
}
}
Provide an interface for the client and the server
// On the client and the server
StockDB.java
//This is the interface to the remote object SockDBImpl
import java.sql.*;
public interface StockDB extends java.rmi.Remote {
public String getCustomers() throws java.rmi.RemoteException;
public String getStocks() throws java.rmi.RemoteException;
public String getPortfolio() throws java.rmi.RemoteException;
}
The remote object connects to
JDBC
//
//
import java.util.*;
import java.sql.*;
import java.io.*;
DBImpl2.java
The remote object
public class StockDBImpl2 extends
java.rmi.server.UnicastRemoteObject
implements StockDB {
private Connection con;
private Statement s;
//The constructor runs the inherited constructor
public StockDBImpl2() throws java.rmi.RemoteException {
super();
}
// This private method is called to connect to the database.
private void connectToDB() throws SQLException {
DriverManager.registerDriver(new sun.jdbc.odbc.JdbcOdbcDriver());
con = DriverManager.getConnection("jdbc:odbc:stocks");
}
// This private method is called to disconnect from the database.
private void disconnectFromDB() throws SQLException{
con.close();
}
private String getXML(ResultSet rs, ResultSetMetaData rsm)
throws SQLException {
// Currently, this method only returns a non-xml String
// representing the rows of the result set
String answer;
answer = "";
while(rs.next()) {
for(int col = 1; col <= rsm.getColumnCount(); col++) {
answer += rs.getString(col);
}
}
return answer;
}
public String getCustomers()
throws java.rmi.RemoteException {
try {
connectToDB();
Statement s = con.createStatement();
ResultSet rs;
ResultSetMetaData rsm;
rs = s.executeQuery("select * from customer");
rsm = rs.getMetaData();
String answer = getXML(rs,rsm);
rs.close();
s.close();
disconnectFromDB();
return answer;
}
catch(SQLException sq) {
System.out.println("SQLException: "+sq);
}
return null;
}
public String getStocks()
throws java.rmi.RemoteException {
return null;
}
public String getPortfolio()
throws java.rmi.RemoteException {
return null;
}
}
The client makes requests on the
remote object
//
import java.io.*;
import java.rmi.*;
import java.net.*;
import java.sql.*;
public class StockDBClient {
StockDBClient.java
public static StockDB stockDataBase;
public static void main(String[] args) {
try {
stockDataBase =
(StockDB)Naming.lookup(
"rmi://mccarthy.heinz.cmu.edu/stocks");
String xml = stockDataBase.getCustomers();
System.out.println(xml);
xml = stockDataBase.getPortfolio();
System.out.println(xml);
xml = stockDataBase.getStocks();
System.out.println(xml);
}
catch (MalformedURLException murle) {
System.out.println();
System.out.println("MalformedURLException");
System.out.println(murle);
}
catch (RemoteException re) {
System.out.println();
System.out.println("RemoteException");
System.out.println(re);
}
catch (NotBoundException nbe) {
System.out.println();
System.out.println("NotBoundException");
System.out.println(nbe);
}
catch (java.lang.ArithmeticException ae) {
System.out.println();
System.out.println("ArithmeticException");
System.out.println(ae);
}
catch(IOException e) {
System.out.println();
System.out.println("IOException");
System.out.println(e);
}
}
}
Example Database Schema
There are three tables. Both customer and stocks have a oneto-many relationship with portfolios
customer stocks portfolio
id
lname
fname
symbol
id
company symbol
price
num_shares
Customer
---------------------------------------------------------------|
id
|
lname
|
fname
|
---------------------------------------------------------------|
1 | Jones
| Robert
|
---------------------------------------------------------------|
2 | Smith
| Elaine
|
---------------------------------------------------------------|
3 | Chan
| Jane
|
---------------------------------------------------------------|
4 | Morales
| Hector
|
---------------------------------------------------------------|
5 | Schwartz
| Michael
|
----------------------------------------------------------------
Stocks
---------------------------------------------------------| symbol
| company
|
price
|
--------------------------------------------------------| COMS
| 3Com Corporation | 12.9375 |
--------------------------------------------------------------------| IBM
| International Business Machines | 96.625 |
--------------------------------------------------------------------| INTC
| Intel Corporation | 34.125 |
--------------------------------------------------------------------| ORCL
| Oracle Corporation | 26.4375 |
--------------------------------------------------------------------| SE
| 7Eleven Inc.
| 8.0625 |
---------------------------------------------------------------------
Portfolio
---------------------------------------------------------------|
id
|
symbol
|
num_shares
|
---------------------------------------------------------------|
1 | COMS
|
1250 |
---------------------------------------------------------------|
1 | INTC
|
300 |
---------------------------------------------------------------|
1 | ORCL
|
450 |
---------------------------------------------------------------|
2 | COMS
|
750 |
---------------------------------------------------------------|
2 | IBM
|
900 |
---------------------------------------------------------------|
2 | INTC
|
600 |
---------------------------------------------------------------|
3 | COMS
|
3232 |
---------------------------------------------------------------|
3 | IBM
|
1000 |
---------------------------------------------------------------|
3 | SE
|
200 |
---------------------------------------------------------------|
4 | COMS
|
333 |
---------------------------------------------------------------|
4 | IBM
|
250 |
---------------------------------------------------------------|
4 | INTC
|
400 |
---------------------------------------------------------------|
5 | COMS
|
750 |
---------------------------------------------------------------|
5 | IBM
|
250 |
---------------------------------------------------------------|
5 | ORCL
|
1000 |
---------------------------------------------------------------|
5 | SE
|
900 |
----------------------------------------------------------------

ODBC - Andrew.cmu.edu

Transcript ODBC - Andrew.cmu.edu

Directory