Transcript Data Base and SQL - Personal.psu.edu

DataBases and
SQL
INFSY 547
Spring 2007
Course Wrap Up
• April 12: Complete Work on Servlets
Review of Team Projects
Close of Portfolio Work
• April 19: Review Form ideas, Team
Meetings and presentation
guidelines
• April 26: Project Due Date
Develop presentations
• May 3: Final Presentation and Party
Structured Query
Language (SQL)
 Used widely to interact with a variety of
relational data bases
 Oracle database management system
Relational Databases
Relational databases stores data in tables
Rows :
Columns :
Primary key:
Select stmts:
Queries:
represents a record
represents individual fields of
data
a column or group of columns in
a table with a unique value
specify which data to look
at in a table
define what will be selected from a
table or group of tables
Sample Relational Data
Base
StudentTable
studentId* studentFirstName
studentLastName
 One:Many
grade
 * indicates primary key
 minimally one table
course student
GradeTable
Relational Databases
– Foreign key: a column that is a primary key
in another table
– Referential Integrity: every foreign key
must appear as another
table’s primary key
SELECT title, editionNumber, copyright
FROM Titles WHERE copyright > 2000;
sql statement
Creating a Database with
SQL in Oracle
• Click on SQLPlus
Note:
1) first time password
is userid and new one
will be required.
2) if already on the
system, password
is as before
Now You are Ready to Enter Commands from the Command Line!
Creating a Database with
SQL in Oracle
• Create a table (create one of your choice)
CREATE TABLE EmployeeTable (
emp_id char (5),
emp-lName char (20),
emp_fName char (15),
emp_dept char (5),
PRIMARY KEY (emp_id));
Relational Databases
• Other SQL Statements:
INSERT INTO EmployeeTable (empId,
fName, lName, dept)
VALUES ( ‘101,‘Sue’, ‘Smith’,’MRKT’);
UPDATE EmployeeTable
SET lName = ‘Jones’
WHERE lName = ‘Smith’
and fName = ‘Sue’;
• Other SQL Statements:
DELETE FROM EmployeeTable
WHERE lastName = ‘Jones’
and firstName = ‘Sue’;
• List your table name(s):
SELECT table_name from user_tables;
SQL Statements
Statement
SELECT
INSERT
DELETE
UPDATE
COMMIT
ROLLBACK
GRANT
REVOKE
Purpose
Selects rows from a
table
Inserts rows into a table
Deletes rows from a
table
Updates rows in a table
Commits a transaction
Rolls back a transaction
Grants security rights
Revokes security rights
Work with your
table!
Granting Privileges
• GRANT SELECT ON EMPLOYEE TO
JTS1;
• GRANT UPDATE ON EMPLOYEE TO
JTS1;
• GRANT DELETE ON EMPLOYEE TO
JTS1;
• GRANT ALTER ON EMPLOYEE TO
JTS1;
Java Application
JDBC Driver Manager
JDBC-ODBC
Bridge
Vendor OBDC
Driver
Vendor JDBC
Driver
DataBASE
Vendor Drivers result in
best performance
DataBASE
Servlets and Java Server Pages, M. Hall and L.
Brown, Sun
For vendor JDBC
• Must be compiled with a special library file
– classes12.jar (comes with Oracle products or
download from my personal site)
– Place in the common/lib in the TomCat directory
http://www.personal.psu.edu/gjy1/infsy547/classes.jar
Java DataBase
Connectivity (JDBC)
 Connect to Oracle
 Build queries in servlets
 Display database information
Database Server
• Interpret SQL statements
• Optimize queries
• Prevent errors connected to
concurrent user access
• Detect and act upon deadlock
• Administer security
• Administer backup and recovery
Relational Middleware
• SQL API (Application Programming Interface)
– Facility to embed SQL code within
procedural languages
• Database Driver
– Software which format SQL statements
and send them to the server
• Protocol Stack
– Used for communicating between client
and server
Relational Middleware
Client
SQL
APL
Client
Driver
Stacks
Client
Server
Software
Database
Server
JDBC
•
Steps to be programmed to access a
relational database using Java
1. Load a driver compatible with the database
being used.
2. Establish a connection to the database
3. Associate an SQL statement with this
connection
4. Execute the SQL statement
5. SQL statement will produce a table stored in
a ResultSet object.
JDBC
6. When processing is complete, database
is closed
7. Connection to the database is closed; be
sure to do so in the program
JDBC
• The JBDC API is contained in the java.sql
package which contains:
– Eight interfaces
– Six classes
– Three exceptions
JDBC
• DriverManager Class
– Class manages JDBC drivers and provides
uniform interface for establishing connections
to databases
JDBC
 Load the driver by calling :
Class.forName(driverClassName);
Class.forName( "oracle.jdbc.driver.OracleDriver" );
• If the driver can’t be loaded, a
ClassNotFoundException is thrown.
JDBC
• If the connection can’t be made, an
SQLException is thrown.
JDBC
• A Connection (or session) interface must be
implemented
DriverManager.getConnection(String name,
String user, String password);
<variable name> =
DriverManager.getConnection("jdbc:oracle:thin:@146.186.8
4.66:1521:CLDB",“<user>",”<password");