embedded sql host language (record-oriented) dbms (set-oriented) 1. query 3. process a tuple at a...
TRANSCRIPT
![Page 1: Embedded SQL Host Language (record-oriented) DBMS (set-oriented) 1. Query 3. Process a tuple at a time 4. Close Cursor 2. Evaluate query. Provide cursor](https://reader034.vdocuments.us/reader034/viewer/2022051821/56649dda5503460f94acfe5d/html5/thumbnails/1.jpg)
Embedded SQL
Host Language(record-oriented)
DBMS
(set-oriented)
1. Query
3. Process a tuple at a time
4. Close Cursor
2. Evaluate query. Provide cursor to query result.
1
![Page 2: Embedded SQL Host Language (record-oriented) DBMS (set-oriented) 1. Query 3. Process a tuple at a time 4. Close Cursor 2. Evaluate query. Provide cursor](https://reader034.vdocuments.us/reader034/viewer/2022051821/56649dda5503460f94acfe5d/html5/thumbnails/2.jpg)
JDBC
Java DataBase Connectivity Standard to access databases using Java Approach:
Create a connection to the databaseCreate a statement to be executed by the
databaseSet parameters of the statement (optional)Execute the statement; return ResultSet (aka
cursor)Read tuples from ResultSet
JDBC is not restricted to SQL!
2
![Page 3: Embedded SQL Host Language (record-oriented) DBMS (set-oriented) 1. Query 3. Process a tuple at a time 4. Close Cursor 2. Evaluate query. Provide cursor](https://reader034.vdocuments.us/reader034/viewer/2022051821/56649dda5503460f94acfe5d/html5/thumbnails/3.jpg)
Create Connection to the DB
Connection conn = DriverManager.getConnection( urlDB, username, password)
• Need JDBC driver (provided by DBMS)• urlDB: Identifies the database uniquely
• N.B. one server could provide multiple DBs.• Username, Password: as usual• Other settings provided by configuration
•e.g., buffer pool, app heap, TA level, ...3
![Page 4: Embedded SQL Host Language (record-oriented) DBMS (set-oriented) 1. Query 3. Process a tuple at a time 4. Close Cursor 2. Evaluate query. Provide cursor](https://reader034.vdocuments.us/reader034/viewer/2022051821/56649dda5503460f94acfe5d/html5/thumbnails/4.jpg)
Output the names of all Profs
Statement s = conn.createStatement();ResultSet r;
s.execute(„SELECT name FROM professor“);r = s.getResultSet();while (r.next()) { output(r.getString(1));}r.close();
4
![Page 5: Embedded SQL Host Language (record-oriented) DBMS (set-oriented) 1. Query 3. Process a tuple at a time 4. Close Cursor 2. Evaluate query. Provide cursor](https://reader034.vdocuments.us/reader034/viewer/2022051821/56649dda5503460f94acfe5d/html5/thumbnails/5.jpg)
Parameterized Queries
PreparedStatement s = conn.prepareStatement( „SELECT name FROM prof WHERE level = ?“);ResultSet r;...
s.setString(1, „AP“);r = s.executeQuery();while (r.next()) ...
5
![Page 6: Embedded SQL Host Language (record-oriented) DBMS (set-oriented) 1. Query 3. Process a tuple at a time 4. Close Cursor 2. Evaluate query. Provide cursor](https://reader034.vdocuments.us/reader034/viewer/2022051821/56649dda5503460f94acfe5d/html5/thumbnails/6.jpg)
Tipps and TricksConnection Pooling
Create several connections to the database Grab an unused connection before accessing
DB Execute statement using that connection
Why? Do not block the database with heavy queries
Rule of thumb: 5 – 10 connections (too many connections will hurt performance and
avail.)6
![Page 7: Embedded SQL Host Language (record-oriented) DBMS (set-oriented) 1. Query 3. Process a tuple at a time 4. Close Cursor 2. Evaluate query. Provide cursor](https://reader034.vdocuments.us/reader034/viewer/2022051821/56649dda5503460f94acfe5d/html5/thumbnails/7.jpg)
Tipps and Tricks Cursor Caching
Use PreparedStatements!
Example: insert into professor(name, level) values(?,?)
Why? Avoid overhead (optimizer) for every call
Disadvantage? Optimizer has no statistics
7
![Page 8: Embedded SQL Host Language (record-oriented) DBMS (set-oriented) 1. Query 3. Process a tuple at a time 4. Close Cursor 2. Evaluate query. Provide cursor](https://reader034.vdocuments.us/reader034/viewer/2022051821/56649dda5503460f94acfe5d/html5/thumbnails/8.jpg)
JDBC Summary Simple protocol to send messages to the
databaseDatabase is typically deployed as a server!
SQL Syntax not checked at compile time!!! For Java, those are just strings
(Type) Safety of parameters checked at running time
All JDBC Statements raise SQLExceptionsShould be caught!
New Standard: SQLJ 8
![Page 9: Embedded SQL Host Language (record-oriented) DBMS (set-oriented) 1. Query 3. Process a tuple at a time 4. Close Cursor 2. Evaluate query. Provide cursor](https://reader034.vdocuments.us/reader034/viewer/2022051821/56649dda5503460f94acfe5d/html5/thumbnails/9.jpg)
SQL J SQL embedded in Java Use preprocessor at compile time for type
safety, SQL syntax Annotate SQL statements with #sql Iterator (Cursor) similar to JDBC
#sql iterator ProfIterator(String name, String level);ProfIterator myProfs;#sql myProfs = { SELECT name, level FROM Professor };while (myProfs.next()) { System.out.println(myProfs.name() + myProfs.level());}
9
![Page 10: Embedded SQL Host Language (record-oriented) DBMS (set-oriented) 1. Query 3. Process a tuple at a time 4. Close Cursor 2. Evaluate query. Provide cursor](https://reader034.vdocuments.us/reader034/viewer/2022051821/56649dda5503460f94acfe5d/html5/thumbnails/10.jpg)
Object-Relational Mapping (e.g., Hibernate) With JDBC and SQL-J, programmers wear two
hatsObject-oriented programming with JavaDatabase programming with SQLTwo languages, two data models, two type
systems, ...Duplicate work for logging, caching, error handling,
security
10
![Page 11: Embedded SQL Host Language (record-oriented) DBMS (set-oriented) 1. Query 3. Process a tuple at a time 4. Close Cursor 2. Evaluate query. Provide cursor](https://reader034.vdocuments.us/reader034/viewer/2022051821/56649dda5503460f94acfe5d/html5/thumbnails/11.jpg)
Java/C# SQL Java/C#
Traditional Multi-tier Architecture
11
Outgoing message (XML/JSON)
REST communication
XML Java/C# XML
Java/C#, JavaScrip
t
application logic
SQL database (queries, updates)
Incoming message (XML/JSON)
security, caching,consistency,...
security, caching,consistency,...
security, caching,consistency,...
Problem: Every layer reinvents the wheel!!!• security, caching, consistency, error handling, data model, ...• huge overheads during development (technology jungle)• huge overheads during deployment (configuration)• huge overheads during operation (RPCs, duplicate work)
![Page 12: Embedded SQL Host Language (record-oriented) DBMS (set-oriented) 1. Query 3. Process a tuple at a time 4. Close Cursor 2. Evaluate query. Provide cursor](https://reader034.vdocuments.us/reader034/viewer/2022051821/56649dda5503460f94acfe5d/html5/thumbnails/12.jpg)
Object-Relational Mapping (e.g., Hibernate) With JDBC and SQL-J, programmers wear two hats
Object-oriented programming with JavaDatabase programming with SQLTwo languages, two data models, two type systems, ...Duplicate work for logging, caching, error handling,
security
Idea: Automate the database programmingDDL: generate „create table“ from XML, annotationsQueries: generate „select“ from getters and settersMake everything look like Java
Idea applicable to relational and XML! Please, do not use in project! We learn the bare bones
here!!! 12
![Page 13: Embedded SQL Host Language (record-oriented) DBMS (set-oriented) 1. Query 3. Process a tuple at a time 4. Close Cursor 2. Evaluate query. Provide cursor](https://reader034.vdocuments.us/reader034/viewer/2022051821/56649dda5503460f94acfe5d/html5/thumbnails/13.jpg)
13
XML Mapping to generic structures
<purchaseOrder><lineItem>…..</lineItem><lineItem>…..</lineItem>
</purchaseOrder>
<book><author>…</author><title>….</title>…..
</book>
Class DomNode{
public String getNodeName();public String getNodeValue();public void setNodeValue(nodeValue);public short getNodeType();
}
Mappings
![Page 14: Embedded SQL Host Language (record-oriented) DBMS (set-oriented) 1. Query 3. Process a tuple at a time 4. Close Cursor 2. Evaluate query. Provide cursor](https://reader034.vdocuments.us/reader034/viewer/2022051821/56649dda5503460f94acfe5d/html5/thumbnails/14.jpg)
14
Mapping to non-generic structures
<purchaseOrder><lineItem>…..</lineItem><lineItem>…..</lineItem>
</purchaseOrder>
<book><author>…</author><title>….</title>…..
</book>
Class PurchaseOrder {
public List getLineItems();
……..}
Mappings
Class Book { public List getAuthor();
public String getTitle();……
}
![Page 15: Embedded SQL Host Language (record-oriented) DBMS (set-oriented) 1. Query 3. Process a tuple at a time 4. Close Cursor 2. Evaluate query. Provide cursor](https://reader034.vdocuments.us/reader034/viewer/2022051821/56649dda5503460f94acfe5d/html5/thumbnails/15.jpg)
Other Approaches New programming languages
e.g., Ruby, XQuery, etc. integrate app scripting and database programmingaddress additional impedance mismatch with Web
PL/SQL (stored procedures)bring application logic to database: „;“, „while“,
blocks, ... rather than database logic to applicationhuge performance advantages
LINQ (language integrated queries)provide a super-data model at application layer (mother of all Hibernates) 15