Transcript CS 195 Course Outline & Introduction to Java

CSC 411/511:
DBMS Design
Application Development
(JDBC)
Dr. Nan Wang
CSC411_L12_Oracle10g_JDBC
1
Contents
• Overview of JDBC technology
• JDBC drivers
• Seven basic steps in using JDBC
Dr. Nan Wang
CSC411_L12_Oracle10g_JDBC
2
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.
– JDBC classes are in the java.sql package
• Note: JDBC is not officially an acronym;
unofficially, “Java DataBase Connectivity” is
commonly used
Dr. Nan Wang
CSC411_L12_Oracle10g_JDBC
3
On-line Resources
• Sun’s JDBC Site
– http://java.sun.com/products/jdbc/
• JDBC Tutorial
– http://java.sun.com/docs/books/tutorial/jdbc/
• List of Available JDBC Drivers
– http://industry.java.sun.com/products/jdbc/drivers/
• API for java.sql
– http://java.sun.com/j2se/1.4/docs/api/java/sql/
package-summary.html
• SQLJ & JDBC Basic Samples
– http://technet.oracle.com/tech/java/sqlj_jdbc/index2.htm?Code&files/basi
c/basic.htm
Dr. Nan Wang
CSC411_L12_Oracle10g_JDBC
4
JDBC Drivers
• 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
Java Application
JDBC API
JDBC Driver Manager
JDBC Driver API
JDBC-ODBC
Bridge
Vendor Specific
ODBC Driver
Vendor Specific
JDBC Driver
Database
Database
Dr. Nan Wang
CSC411_L12_Oracle10g_JDBC
5
Tutorial
• http://docs.oracle.com/javase/tutorial/jdbc/basi
cs/gettingstarted.html#step11
• Install the latest version of the Java SE SDK
on your computer
• Install your database management system
(DBMS) if needed
• Install a JDBC driver from the vendor of your
database
• Install Apache
• Download the sample code
• Modify the build.xml file
• Modify the tutorial properties file
• Compile and package the samples
Dr. Nan Wang
CSC411_L12_Oracle10g_JDBC
6
Seven Basic Steps in Using JDBC
1.
2.
3.
4.
5.
6.
7.
Load the driver
Define the Connection URL
Establish the Connection
Create a Statement object
Execute a query
Process the results
Close the connection
Dr. Nan Wang
CSC411_L12_Oracle10g_JDBC
7
JDBC: Details of Process
1. Load the driver
try {
Class.forName("connect.microsoft.MicrosoftDriver");
Class.forName("oracle.jdbc.driver.OracleDriver");
Class.forName(“com.mysql.jdbc.Driver”);
} catch { ClassNotFoundException cnfe) {
System.out.println("Error loading driver: " cnfe);
}
Dr. Nan Wang
CSC411_L12_Oracle10g_JDBC
8
JDBC: Details of Process
2. Define the Connection URL
String host = "dbhost.yourcompany.com";
String dbName = "someName";
int port = 1234;
String oracleURL = "jdbc:oracle:thin:@" + host +
":" + port + ":" + dbName;
String sybaseURL = "jdbc:sybase:Tds:" + host +
":" + port + ":" +
"?SERVICENAME=" + dbName;
String mysqlURL = "jdbc:mysql:" + host +
":" + port + ":" +
"?SERVICENAME=" + dbName;
Dr. Nan Wang
CSC411_L12_Oracle10g_JDBC
9
JDBC: Details of Process (Continued)
3. Establish the Connection
String username = "jay_debesee";
String password = "secret";
Connection connection =
DriverManager.getConnection(oracleURL,
username,
password);
Optionally, look up information about the database
DatabaseMetaData dbMetaData =
connection.getMetaData();
String productName =
dbMetaData.getDatabaseProductName();
System.out.println("Database: " + productName);
String productVersion =
dbMetaData.getDatabaseProductVersion();
System.out.println("Version: " + productVersion);
Dr. Nan Wang
CSC411_L12_Oracle10g_JDBC
10
JDBC: Details of Process (Continued)
4. Create a Statement
A statement object is to send queries and commands to DB.
Statement statement =
connection.createStatement();
5. Execute a Query
String query =
"SELECT col1, col2, col3 FROM sometable";
ResultSet resultSet =
statement.executeQuery(query);
–
–
To modify the database, use executeUpdate, supplying
a string that uses UPDATE, INSERT, or DELETE
Use setQueryTimeout to specify a maximum delay to
wait for results
Dr. Nan Wang
CSC411_L12_Oracle10g_JDBC
11
JDBC: Details of Process (Continued)
6. Process the Result
while(resultSet.next()) {
System.out.println(resultSet.getString(1) + " "
+
resultSet.getString(2) + " "
+
resultSet.getString(3));
}
–
–
–
First column has index 1, not 0
ResultSet provides various getXxx methods that take a
column index or column name and returns the data
You can also access result meta data (column names, etc.)
7. Close the Connection
connection.close();
Since opening a connection is expensive, postpone this
step if additional database operations are expected
Dr. Nan Wang
CSC411_L12_Oracle10g_JDBC
12
Example Code
Dr. Nan Wang
CSC411_L12_Oracle10g_JDBC
13
Summary
•
You use the same Java syntax with all databases
–
•
Steps in using JDBC
1.
2.
3.
4.
5.
6.
7.
•
Translation to native format is done on the client via a
JDBC driver
Load the driver
Define the Connection URL
Establish the Connection
Create a Statement object
Execute a query
Process the results
Close the connection
Examples
Dr. Nan Wang
CSC411_L12_Oracle10g_JDBC
14
• PHP and MySQL
• http://www.w3schools.com/php/php_mysql_conne
ct.asp
• Python and MySQL
• http://www.tutorialspoint.com/python/python_data
base_access.htm
Dr. Nan Wang
CSC411_L12_Oracle10g_JDBC
15
CSC 411/511:
DBMS Design
Questions?
Dr. Nan Wang
CSC411_L12_Oracle10g_JDBC
16