sql and jdbc
DESCRIPTION
SQL and JDBC. Peter Mork. Database Primer*. All data values are simple No arrays, pointers, vectors, complex types All data are stored as 2D tables/relations Contains 0 or more rows/tuples Contains 1 or more columns/attributes All operations defined logically Order of tuples is irrelevant - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: SQL and JDBC](https://reader034.vdocuments.us/reader034/viewer/2022051215/5681499a550346895db6dc5b/html5/thumbnails/1.jpg)
SQL and JDBC
Peter Mork
![Page 2: SQL and JDBC](https://reader034.vdocuments.us/reader034/viewer/2022051215/5681499a550346895db6dc5b/html5/thumbnails/2.jpg)
Database Primer*
All data values are simple No arrays, pointers, vectors, complex types
All data are stored as 2D tables/relations Contains 0 or more rows/tuples Contains 1 or more columns/attributes
All operations defined logically Order of tuples is irrelevant
Keys used to identify unique tuples* SQL Instant Reference by Martin Gruber
![Page 3: SQL and JDBC](https://reader034.vdocuments.us/reader034/viewer/2022051215/5681499a550346895db6dc5b/html5/thumbnails/3.jpg)
SQL: What is it?
Data Definition Language (DDL) CREATE relations, attributes, etc.
Data Manipulation Language (DML) INSERT, UPDATE or DELETE data
Data Control Language (DCL) GRANT, REVOKE privileges
Data Query Language (DQL) SELECT data from relations
![Page 4: SQL and JDBC](https://reader034.vdocuments.us/reader034/viewer/2022051215/5681499a550346895db6dc5b/html5/thumbnails/4.jpg)
CREATE-ing Relations
CREATE TABLE {name}
( {col1} {type1} [{cons1}],
{col2} {type2} [{cons2}],
...
);
col? = A name for the columntype? = The column’s data-typecons? = An optional constraint on the column
![Page 5: SQL and JDBC](https://reader034.vdocuments.us/reader034/viewer/2022051215/5681499a550346895db6dc5b/html5/thumbnails/5.jpg)
Data types
CHAR(len), VARCHAR(len): Strings of maximum length len
FLOAT, REAL: Approximate numbersINT, INTEGER: Exact integersDECIMAL, DEC: Exact decimalsDATE, TIME, TIMESTAMP:
Timestamp combines date and time
![Page 6: SQL and JDBC](https://reader034.vdocuments.us/reader034/viewer/2022051215/5681499a550346895db6dc5b/html5/thumbnails/6.jpg)
Constraints
NOT NULL: No missing data allowedUNIQUE: Every value is unique or
missingPRIMARY KEY: Every value is uniquePlus other more sophisticated
predicates
![Page 7: SQL and JDBC](https://reader034.vdocuments.us/reader034/viewer/2022051215/5681499a550346895db6dc5b/html5/thumbnails/7.jpg)
Example
CREATE TABLE students
( s_id INT PRIMARY KEY,
s_name VARCHAR(50) NOT NULL,
s_dob DATE
);
s_id s_name s_dob
![Page 8: SQL and JDBC](https://reader034.vdocuments.us/reader034/viewer/2022051215/5681499a550346895db6dc5b/html5/thumbnails/8.jpg)
INSERT-ing New Values
INSERT INTO {table}
[ ( {col-a}, {col-b}, … ) ]
VALUES ( {val-a}, {val-b}, … );
col-x = Optional column namesval-x = A value for that columnIf no column names are given, the order
in the CREATE statement is used.
![Page 9: SQL and JDBC](https://reader034.vdocuments.us/reader034/viewer/2022051215/5681499a550346895db6dc5b/html5/thumbnails/9.jpg)
Example
INSERT INTO students
VALUES ( 001, ‘Peter Mork’ );Since no column names were specified:
001 is s_id ‘Peter Mork’ is s_name
NULL is used for missing data
s_id s_name s_dob
1 Peter Mork NULL
![Page 10: SQL and JDBC](https://reader034.vdocuments.us/reader034/viewer/2022051215/5681499a550346895db6dc5b/html5/thumbnails/10.jpg)
DELETE-ing Values
DELETE FROM {table}
[ WHERE {predicate} ];Deletes all tuples from {table} that
match {predicate}Use a primary key to isolate one tupleExample:DELETE FROM students
WHERE s_id = 1;
![Page 11: SQL and JDBC](https://reader034.vdocuments.us/reader034/viewer/2022051215/5681499a550346895db6dc5b/html5/thumbnails/11.jpg)
SELECT-ing Results
SELECT {attr-list}
FROM {table-list}
[ WHERE {pred-list} ];
Logically: Computes cross-product of all tables Discards results that don’t match
predicates Returns listed attributes
![Page 12: SQL and JDBC](https://reader034.vdocuments.us/reader034/viewer/2022051215/5681499a550346895db6dc5b/html5/thumbnails/12.jpg)
Simple Example
SELECT s_name
FROM students
WHERE s_dob > ‘1975-1-1’;
This retrieves all students born since 1975.
![Page 13: SQL and JDBC](https://reader034.vdocuments.us/reader034/viewer/2022051215/5681499a550346895db6dc5b/html5/thumbnails/13.jpg)
SELECT Clause
An attribute list is either: * (indicating all columns) A list of unique attribute names:
Usually an attribute name will sufficeSometimes you need {table}.{attr}Can rename attributes using AS
Example:SELECT students.s_id AS id,
s_name, grades.grade
![Page 14: SQL and JDBC](https://reader034.vdocuments.us/reader034/viewer/2022051215/5681499a550346895db6dc5b/html5/thumbnails/14.jpg)
FROM Clause
A table list is a list of unique table names: Usually a table name will suffice Multiple occurrences of the same table
must be renamed using AS
Example:FROM students, final_grades AS grades
![Page 15: SQL and JDBC](https://reader034.vdocuments.us/reader034/viewer/2022051215/5681499a550346895db6dc5b/html5/thumbnails/15.jpg)
WHERE Clause
The predicate list contains Join predicates, which relate two
columns from different tables Select predicates, which relate a column
to a constant or another column in the same table
Example:WHERE grade >= 3.0
AND students.s_id = grades.s_id;
![Page 16: SQL and JDBC](https://reader034.vdocuments.us/reader034/viewer/2022051215/5681499a550346895db6dc5b/html5/thumbnails/16.jpg)
Full Example
SELECT students.s_id AS id,
s_name, grades.grade
FROM students,
final_grades as grades
WHERE grade >= 3.0
AND students.s_id =
grades.s_id;
![Page 17: SQL and JDBC](https://reader034.vdocuments.us/reader034/viewer/2022051215/5681499a550346895db6dc5b/html5/thumbnails/17.jpg)
Sample Data
s_id s_name s_dob
1 Alice NULL
2 Bob 1974-8-28
3 Cindy 1973-10-19
s_id course
grade
1 544 3.5
1 521 3.8
1 531 3.9
2 544 3.6
2 551 2.9
![Page 18: SQL and JDBC](https://reader034.vdocuments.us/reader034/viewer/2022051215/5681499a550346895db6dc5b/html5/thumbnails/18.jpg)
Cross-Product Results
students.s_id s_name s_dob grades.s_id course grade
1 Alice NULL 1 544 3.5
1 Alice NULL 1 521 3.8
1 Alice NULL 1 531 3.9
1 Alice NULL 2 544 3.6
1 Alice NULL 2 551 2.9
2 Bob 1974-8-28 1 544 3.5
2 Bob 1974-8-28 1 521 3.8
2 Bob 1974-8-28 1 531 3.9
2 Bob 1974-8-28 2 544 3.6
2 Bob 1974-8-28 2 551 2.9
3 Cindy 1973-10-19 1 544 3.5
3 Cindy 1973-10-19 1 521 3.8
3 Cindy 1973-10-19 1 531 3.9
3 Cindy 1973-10-19 2 544 3.6
3 Cindy 1973-10-19 2 551 2.9
![Page 19: SQL and JDBC](https://reader034.vdocuments.us/reader034/viewer/2022051215/5681499a550346895db6dc5b/html5/thumbnails/19.jpg)
Check Predicates
students.s_id s_name s_dob grades.s_id course grade
1 Alice NULL 1 544 3.5
1 Alice NULL 1 521 3.8
1 Alice NULL 1 531 3.9
1 Alice NULL 2 544 3.6
1 Alice NULL 2 551 2.9
2 Bob 1974-8-28 1 544 3.5
2 Bob 1974-8-28 1 521 3.8
2 Bob 1974-8-28 1 531 3.9
2 Bob 1974-8-28 2 544 3.6
2 Bob 1974-8-28 2 551 2.9
3 Cindy 1973-10-19 1 544 3.5
3 Cindy 1973-10-19 1 521 3.8
3 Cindy 1973-10-19 1 531 3.9
3 Cindy 1973-10-19 2 544 3.6
3 Cindy 1973-10-19 2 551 2.9
![Page 20: SQL and JDBC](https://reader034.vdocuments.us/reader034/viewer/2022051215/5681499a550346895db6dc5b/html5/thumbnails/20.jpg)
Final Result
id s_name Grade
1 Alice 3.5
1 Alice 3.8
1 Alice 3.9
2 Bob 3.6
![Page 21: SQL and JDBC](https://reader034.vdocuments.us/reader034/viewer/2022051215/5681499a550346895db6dc5b/html5/thumbnails/21.jpg)
Note
This is the logical order of operations. The database system will not choose such a brain-damaged approach.
Application developers/users do not need to know how to execute the query efficiently; access plans are chosen automatically by the database system.
![Page 22: SQL and JDBC](https://reader034.vdocuments.us/reader034/viewer/2022051215/5681499a550346895db6dc5b/html5/thumbnails/22.jpg)
Other capabilities
Grouping and aggregation Uses GROUP BY keyword Aggregation functions include:
COUNTSUMAVG
More sophisticated predicatesNested queries
![Page 23: SQL and JDBC](https://reader034.vdocuments.us/reader034/viewer/2022051215/5681499a550346895db6dc5b/html5/thumbnails/23.jpg)
JDBC: What is it?
API for database programsCollection of interfaces, and a few
key classesRelies on vendor-supplied drivers
(i.e., implementations of the interfaces)
![Page 24: SQL and JDBC](https://reader034.vdocuments.us/reader034/viewer/2022051215/5681499a550346895db6dc5b/html5/thumbnails/24.jpg)
Connectivity Protocols
JDBCDriver registered
with systemDownloaded (linked
at run-time by VM)Written in Javacan be linked to an
existing ODBC driver
ODBCDriver registered
with systemInstalled on host
machineWritten in Cde facto standard
![Page 25: SQL and JDBC](https://reader034.vdocuments.us/reader034/viewer/2022051215/5681499a550346895db6dc5b/html5/thumbnails/25.jpg)
JDBC Classes
Date, Time, Timestamp, Types Represent standard RDB types Mapped to Java type system
DriverManager/DriverPropertyInfo Used to initialize driver Analogous to the System class
![Page 26: SQL and JDBC](https://reader034.vdocuments.us/reader034/viewer/2022051215/5681499a550346895db6dc5b/html5/thumbnails/26.jpg)
JDBC Interfaces
Driver/Connection Used to communicate with database
Statement (Callable, Prepared) Used to package SQL
ResultSet Used to iterate through query result (cursor)
DatabaseMetadata/ResultSetMetaData Contains data about the data
![Page 27: SQL and JDBC](https://reader034.vdocuments.us/reader034/viewer/2022051215/5681499a550346895db6dc5b/html5/thumbnails/27.jpg)
Steps to manipulate DB
1. Load driver2. Connect to database3. Manipulate data4. Close database
![Page 28: SQL and JDBC](https://reader034.vdocuments.us/reader034/viewer/2022051215/5681499a550346895db6dc5b/html5/thumbnails/28.jpg)
1. Load driver
Explicitly:Class.forName(“driver name”)This creates a new instance of the
driverImplicitly:Update the Java system settings(See Java docs for more info)
![Page 29: SQL and JDBC](https://reader034.vdocuments.us/reader034/viewer/2022051215/5681499a550346895db6dc5b/html5/thumbnails/29.jpg)
2. Connect to database
getConnection(db)getConnection(db, uid, pwd)getConnection(db, info)
db = “jdbc:odbc:data-source-name”db = “jdbc:???://host:port/dbname”
![Page 30: SQL and JDBC](https://reader034.vdocuments.us/reader034/viewer/2022051215/5681499a550346895db6dc5b/html5/thumbnails/30.jpg)
Connection notes
Properties is a sub-class of HashTable
Used to package multiple parameters
close() closes a connection (step 4)isClosed() tests a connection’s status
![Page 31: SQL and JDBC](https://reader034.vdocuments.us/reader034/viewer/2022051215/5681499a550346895db6dc5b/html5/thumbnails/31.jpg)
3. Manipulate data
createStatement establishes a framework for executing queries
executeQuery returns a ResultSetexecuteUpdate returns an intexecute can return either, but is
usually used when there is no data to return
![Page 32: SQL and JDBC](https://reader034.vdocuments.us/reader034/viewer/2022051215/5681499a550346895db6dc5b/html5/thumbnails/32.jpg)
Which execute to execute?
DDL and DCL queries are generally run using execute()
DML queries are generally run using executeUpdate(); the return value represents the number of rows affected
DQL queries are generally run using executeQuery(); a collection of tuples is returned
![Page 33: SQL and JDBC](https://reader034.vdocuments.us/reader034/viewer/2022051215/5681499a550346895db6dc5b/html5/thumbnails/33.jpg)
ResultSet
A cursor that iterates through a collection of tuples
Forward only!Each Statement object can have at
most one active ResultSet
![Page 34: SQL and JDBC](https://reader034.vdocuments.us/reader034/viewer/2022051215/5681499a550346895db6dc5b/html5/thumbnails/34.jpg)
Metadata
Metadata lets you know what the database looks like
Information about the table names, field names, domains, etc.
Metadata exists for both the Database (effectively constant) and for each ResultSet (depends on the query)
![Page 35: SQL and JDBC](https://reader034.vdocuments.us/reader034/viewer/2022051215/5681499a550346895db6dc5b/html5/thumbnails/35.jpg)
Important Metadata methods
columnCount(): The number of fields in a ResultSet
columnType(i): The type (as listed in Types) of column i. Note that columns are 1-indexed, not 0.
The return value of columnType can be used to select the correct getXXX method to invoke on the ResultSet
![Page 36: SQL and JDBC](https://reader034.vdocuments.us/reader034/viewer/2022051215/5681499a550346895db6dc5b/html5/thumbnails/36.jpg)
Possible Uses of JDBC
Leverage Java’s GUI tools to allow users to visualize the contents of a database
Use Java to publish information stored in a database on the web
Create tools to aid the database programmer in designing queries
![Page 37: SQL and JDBC](https://reader034.vdocuments.us/reader034/viewer/2022051215/5681499a550346895db6dc5b/html5/thumbnails/37.jpg)
Observations
Java’s inherent speed problems can be offset by the power of an efficient database
Many databases have awkward user interfaces
Database queries are disk/network requests -- think multi-threading