Transcript TextForTheWebMod16-part5 - Coweb

Making Text for the Web
part 5
Barb Ericson
Georgia Institute of Technology
March 2006
Georgia Institute of Technology
Relational Databases
• What if you wanted to keep track of the
people in your pictures
– And more than one person could be in a
picture?
– You might want one table Person with
information about each person
– You might want another table Picture with
information about each Picture
– And another table to PicturePerson to link the
two together
• Which people are in what picture
Georgia Institute of Technology
Person Database
• In your
bookClasses
directory
– Is a Microsoft
Access
database
• Person.mdb
Georgia Institute of Technology
Where is Jennifer Guzdial?
• First look up the PersonID for Jennifer Guzdial
– She is number 5
• Now look in the PicturePerson table for that
PersonID to get the ids for the pictures
(PictureID)
– She is in picture 5 and picture 8
• Now use the PictureID to find the file name for
that picture
– jennySoccer.jpg
– MattJennyCorn.jpg
Georgia Institute of Technology
Getting Data from a Database
• Use SQL to get data from the database
– Structured Query Language
– Works with any relational database
• Use classes in java.sql
– DriverManager – to manage the driver
– Connection – to connect to the database
– Statement – to send a query to the database
– ResultSet – to hold the results of a query
Georgia Institute of Technology
Loading the Driver
• You first have to load the driver class
– Handles working with a particular kind of
database
– Use Class.forName(DriverClass);
• For an Access Database
Class.forName(sun.jdbc.odbc.JdbcOdbcDriver);
• For a MySQL Database
Class.forName(com.mysql.jdbc.Driver);
Georgia Institute of Technology
Creating a Connection
• Specifies the database to connect to
– And optionally the login and password to use
• Examples
Connection connection = DriverManager.getConnection(url, "login",
"password");
Connection connection = DriverManager.getConnection(url);
• Specifying the URL
– For an Access Database
• Use an ODBC to JDBC bridge
• jdbc:odbc:person
– For a MySQL Database
• Use JDBC to MySQL
• jdbc:mysql://localhost:3306/person
Georgia Institute of Technology
Access Database ODBC Name
• To connect to an
Access Database
– You will need to assign
an ODBC name
– Open the Control
Panel and then get to
the ODBC Datasource
Administrator Window
• In Administrative Tools
– Click on the User DSN
Tab
• Click on the Add button
Georgia Institute of Technology
Adding the Data Source Name
• Click on
– Diver do Microsoft
Access (*.mdb)
• Click on Finish
Georgia Institute of Technology
Mapping a DSN to a Database
• Add a Data Source
Name
– person
• Fill in a description
• Click on Select and
pick the database file
– person.mdb
Georgia Institute of Technology
Closing a Connection
• There is often a limit on
how many connections
there can be to a
database at a time
Free pool
– But many programs can be
connected at the same
time
• Most database drivers
pool the database
connections
– So you want to close the
connection after you finish
a query
• To release it back to the
free pool
connection.close();
Georgia Institute of Technology
DatabaseManager
import java.sql.*;
import java.util.*;
/**
* Class that handles the connection with the database
*/
public class DatabaseManager
{
////////////// fields ////////////////////////////
/**
* Constructor that takes the driver name and url
* @param driver the class that communicates with the
* database
* @param url the url of the database as a string
*/
public DatabaseManager(String driver, String url)
{
this.driverName = driver;
this.urlStr = url;
private String driverName;
private String urlStr;
// try the following
try {
// load the driver class
Class.forName(driver);
} catch (ClassNotFoundException ex) {
SimpleOutput.showError("Can't find the driver class " +
driver + ", check the classpath");
}
}
Georgia Institute of Technology
Testing the Connection
/**
* Method for testing the connection
*/
public void testConnection()
{
// try the following
try {
// open the connection to the database
Connection connection =
DriverManager.getConnection(this.urlStr);
// tell the user the connection was opened
System.out.println("Connection established");
// close the connection
connection.close();
// tell the user the connection was closed
System.out.println("The connection was closed");
} catch (SQLException ex) {
SimpleOutput.showError("Trouble with the " +
"database connection");
ex.printStackTrace();
}
}
Georgia Institute of Technology
Main to Test the Connection
/* main for testing */
public static void main(String[] args)
{
// create the database manager for an Access database
DatabaseManager dbManager =
new DatabaseManager("sun.jdbc.odbc.JdbcOdbcDriver",
"jdbc:odbc:person");
// create the database manager for a MySQL database
// new DatabaseManager("com.mysql.jdbc.Driver",
//
"jdbc:mysql://localhost:3306/person");
// test the connection
dbManager.testConnection();
}
Georgia Institute of Technology
Getting Data from the Database
• To query the database
– Use a select statement
– Specify the fields to be selected
– Specify the table to select from
Select fieldList From tableName [Where condition]
• Examples
– Select * From Person
• Will return all rows and fields (in order) in the Person table
– Select FirstName, Age From Person
• Will return all rows with first name and then age
– Select * from Person Where Age > 40
• Will return rows where the person age is greater than 40
Georgia Institute of Technology
Using a Statement
• Statements are used to send queries to the
database
• You need to create a statement
– Only one can be open on a connection at a time
– Statement statement = connection.createStatement();
• Execute the query
– ResultSet rs = statement.executeQuery(query);
• Process the result set
• Close the statement
– statement.close();
Georgia Institute of Technology
Processing the ResultSet
• The ResultSet is a temporary table of data
– We need to walk through each row of data
– The cursor starts off before the first row
• So you can use while (rs.next())
– Get the data for a column using
• getXXX(int colNum) where XXX is type of data
– Starting with 1 as the first column
Or
• getXXX(String colName)
– Using the name of the column for colName
– Close the result set
• rs.close();
Georgia Institute of Technology
testQuery Method
/**
* Method to test a query and print the results
* @param query the query to execute
* @param numCols the number of columns in the
result
*/
public void testQuery(String query, int numCols)
{
// try the following
try {
// open the connection to the database
Connection connection =
DriverManager.getConnection(this.urlStr);
// create a statement
Statement statement =
connection.createStatement();
// execute the query
ResultSet rs = statement.executeQuery(query);
// print out the results
while (rs.next())
{
for (int i = 1; i <= numCols; i++)
{
System.out.print(rs.getString(i) + ", ");
}
System.out.println();
}
// close everything
rs.close();
statement.close();
connection.close();
} catch (SQLException ex) {
SimpleOutput.showError("Trouble with the database
"+
urlStr);
ex.printStackTrace();
}
Georgia Institute of Technology
Main for testQuery
/* main for testing */
public static void main(String[] args)
{
// create the database manager for an Access database
DatabaseManager dbManager =
new DatabaseManager("sun.jdbc.odbc.JdbcOdbcDriver",
"jdbc:odbc:person");
// create the database manager for a MySQL database
// new DatabaseManager("com.mysql.jdbc.Driver",
//
"jdbc:mysql://localhost:3306/person");
// test a query
dbManager.testQuery("Select FirstName, Age from Person",2);
}
Georgia Institute of Technology
Narrowing the Query
/* main for testing */
public static void main(String[] args)
{
// create the database manager for an Access database
DatabaseManager dbManager =
new DatabaseManager("sun.jdbc.odbc.JdbcOdbcDriver",
"jdbc:odbc:person");
// create the database manager for a MySQL database
// new DatabaseManager("com.mysql.jdbc.Driver",
//
"jdbc:mysql://localhost:3306/person");
// test a query
dbManager.testQuery("Select FirstName, Age from Person " +
"Where Age > 40",2);
}
Georgia Institute of Technology
Exercise
• Modify the query to get the first name and
last name of the people under age 20
• Modify the query to get the PictureID of
the pictures that have PersonID of 3 in
them
• Modify the query to get the first names of
the people with a last name of Guzdial
Georgia Institute of Technology
Summary
• To get data from a Database
–
–
–
–
–
You need to load the driver
Know how to connect to the database
Create a connection
Create a statement
Use the statement to execute a query and get a result
set
• Using the SQL Select statement
– Process the result set
– Close the result set, statement and connection
• Closing the connection puts it back in the free pool of
connecitons
Georgia Institute of Technology