Embedded SQL Embedded OQL (Jasmine ODQL) Chapter 21 + 28. 8 in third edition Chapter 29. 7 + Appendix E in fourth edition Article: SQLJ: It’s Javalicious! Chapter 8 in ”Principles of Database Systems with Internet and Java™ Applications” Excerpt from ”Database Systems the complete book” Jasmine On-line Documentation ODMG 3. 0 / ODMG 2. 0 (Chapter 26 in third edition / Chapter 27 in fourth edition ) nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 1
What is embedded SQL? • Host Language – Any programming language • Database – An SQL database (relational database) • Embedded SQL: – Helps the host language communicate with the database via SQL nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 2
Why embedded SQL? • Advanced logic • Database Interface/Application nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 3
Static vs. Dynamic • Static embedded SQL – Compiled in advance Faster at run-time – Limited functionality • Dynamic embedded SQL – Not compiled in advance Slower – Full functionality Everything that can be achieved with static embedded SQL, can also be achieved with dynamic embedded SQL nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 4
Standard embedded SQL vs. embedded SQL for Java • Standard embedded SQL – Static – Dynamic • Embedded SQL for Java – Static (i. e. SQLj) – Dynamic (i. e. JDBC) nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 5
Standard embedded SQL • Defined for a number of ”older” languages: – – – ADA C COBOL FORTRAN PASCAL PL/1 • ISO standard nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 6
Standard embedded SQL Basics All SQL-commands start with EXEC SQL And are terminated with the appropriate command-terminator (t ex ; ) based on the host language nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 7
Standard embedded SQL Example (singleton select) … EXEC SQL CONNECT my. DB; EXEC SQL SELECT name, address INTO : lname, : laddress FROM my. Table WHERE id=53; … nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 8
Standard embedded SQL Example Begin EXEC SQL DECLARE mycursor CURSOR FOR SELECT name, pet FROM Person, Pet WHERE Person. name = Pet. name; . . . EXEC SQL OPEN mycursor; . . . EXEC SQL FETCH mycursor INTO : lname, : lpet; While SQLCODE = 0 Do Begin Writeln(lname, lpet); EXEC SQL FETCH mycursor INTO : lname, : lpet; End; . . . EXEC SQL CLOSE mycursor; End. nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 9
Standard embedded SQL Exempel VAR query : CHAR(1000); . . . query : = ’DELETE FROM Employee WHERE enumber = ’ 0025’; EXEC SQL PREPARE statement FROM : query; EXEC SQL EXECUTE statement; query : = ’DELETE FROM Employee WHERE enumber = ’ 0026’; EXEC SQL EXECUTE IMMEDIATE : query; tablename : = ’my. Table’; query : = ’DELETE FROM : tablename; EXEC SQL EXECUTE IMMEDIATE : query; nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 10
Java embedded SQL General • • Java program JDBC driver (JDBC-ODBC bridge + ODBC driver) Database nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 11
Java embedded SQL Arcitecture Java Application JDBC driver manager JDBC/ODBC bridge JDBC Driver (DBMS Specific) ODBC Driver DBMS nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 12
JDBC sequence 1. Import Packages 2. Register JDBC Driver 3. Open a Connection to the database 4. Create a Statement 5. Execute an SQL command receive a Result Set (if there is one) 6. Process/Work with the Result Set (back to point 4) 7. Close the Result Set and the Statement 8. Close the Connection nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 13
1. Import Packages //Import packages import java. sql. *; //JDBC packages //other packages import java. util. *; … java. sql package specification: http: //java. sun. com/j 2 se/1. 5/docs/api/index. html nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 14
2. Register JDBC Driver //Load IBM DB 2 driver Class. for. Name (”com. ibm. db 2. jcc. DB 2 Driver”); //Load JDBC-ODBC driver bridge Class. for. Name(”sun. jdbc. odbc. Jdbc. Odbc. Driver”); // Load Oracle driver Driver. Manager. register. Driver (new oracle. jdbc. driver. Oracle. Driver()); nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 15
3. Open a Connection to the database String String user. ID = ”dbuser”; password = ”abc 123”; ODBCURL = ”jdbc: odbc: mydb”; ORACLEURL = ”jdbc: oracle: mydb”; DB 2 URL = ”jdbc: db 2: mydb”; Connection con 1 = Driver. Manager. get. Connection (ORACLEURL, user. ID, password); Connection con 2 = Driver. Manager. get. Connection (ODBCURL, user. ID, password); Connection con 3 = Driver. Manager. get. Connection (DB 2 URL, user. ID, password); con 3. set. Auto. Commit(true); nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 16
3. Open a Connection to the database, cont. The URL (”database address”) is compised of three parts: jdbc: databasetype/ODBC: databasename/ODBC-aliases can be defined in the ODBC Manager (”Data Sources (ODBC)” in the Administrative Tools in the Control Panel or by running odbcad 32. exe found in system 32): nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 17
4. Create a Statement Prepared. Statement pstmt 1 = con 1. prepare. Statement ("SELECT hotelname, rating FROM hotel WHERE city = ? "); pstmt 1. set. String(1, ”Hawai”); Prepared. Statement pstmt 2 = con 1. prepare. Statement (”UPDATE event SET place = ? Price = ? WHERE activity = ? "); pstmt 2. set. String(1, ”beach”); pstmt 2. set. Int(2, 100); pstmt 2. set. String(3, ”swimming”); nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 18
5. Execute an SQL command receive a Result Set (if there is one) //execute the prepared statement pstmt 1 and store the result in a result set Result. Set rs = pstmt 1. execute. Query(); //execute the prepared statement pstmt 2. execute. Update(); nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 19
6. Process/Work with the Result Set // Print a list of the hotels that were returned by the query System. out. println(”Hotels in Hawai: ”); while (rs. next()) { System. out. println (rs. get. String(”hotelname”)); } //Alternatively rs. get. String(1) nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 20
7. Close the Result Set the Statement 8. Close the Connection // close the result set, statements, and the connections rs. close(); pstmt 1. close(); pstmt 2. close(); con 1. close(); con 2. close(); con 3. close(); nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 21
Data types It is often necessary to map the database data types to the Java data types/classes: • SQL integer can be mapped to Java int • SQL number, real etc can be mapped to Java real • SQL varchar, string etc can be mapped to Java String • SQL date, timestamp etc can be mapped to Java java. sql. Date, java. sql. Timestamp nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 22
SQLJ uses and extends the JDBC infrastructure. It uses contexts in order to distinguish connections All SQLJ database commands start with #sql It creates profiles that contain information about the SQL commands (so that they can be handled as static commands) SQLJ Java packages: sqlj. runtime nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 23
SQLJ architecture nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 24
SQLJ sequence 1. Import Packages 2. (Define iterator) 3. Register JDBC Driver 4. Open a Connection to the database 5. Set Default. Context / other Context 6. Execute an SQL command receive the result (if there is any) 7. Process/Work with the result (cursor) (back to point 6) 8. Close the cursor 9. Close the Connection/Context nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 25
1. Import Packages //Import packages import sqlj. runtime. *; import sqlj. runtime. ref. *; import java. sql. *; //JDBC packages import java. util. *; sqlj package specification: ftp: //ftp. calweb. com/business/sqlj/SQLJ-P 0. pdf http: //www. ifis. mu-luebeck. de/lehre/ss 99/prakt/informixjdbc-doc/sqlj/doc/runtime/javadoc/packages. html nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 26
2. (Define iterator) #sql iterator Hotel_Cursor (String hotelname, Integer rooms) ; #sql iterator String 2_Cursor (String, String); nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 27
3. Register JDBC Driver //Load IBM DB 2 driver Class. for. Name (”com. ibm. db 2. jcc. DB 2 Driver”); //Load JDBC-ODBC driver bridge Class. for. Name(”sun. jdbc. odbc. Jdbc. Odbc. Driver”); // Load Oracle driver Driver. Manager. register. Driver (new oracle. jdbc. driver. Oracle. Driver()); nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 28
4. Open a Connection to the database String String user. ID = ”dbuser”; password = ”abc 123”; ODBCURL = ”jdbc: odbc: mydb”; ORACLEURL = ” jdbc: oracle: mydb”; DB 2 URL = ”jdbc: db 2: mydb”; Connection con 1 = Driver. Manager. get. Connection (ORACLEURL, user. ID, password); Connection con 2 = Driver. Manager. get. Connection (ODBCURL, user. ID, password); Connection con 3 = Driver. Manager. get. Connection (DB 2 URL, user. ID, password); con 3. set. Auto. Commit(true); nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 29
5. Set Default. Context / other Context // Set the default context so SQLJ can automatically pick up the connection Default. Context ctx = new Default. Context (con 3); Default. Context. set. Default. Context (ctx); nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 30
6. Execute an SQL command receive the result (if there is any) int rooms = 500; String name = ”Grand"; String city = ”Madrid”; #sql { INSERT INTO hotel (hotelname, rooms, city) VALUES (: name, : rooms, : city) }; nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 31
6. Execute an SQL command receive the result (if there is any) String city = ”Rome”; Hotel_Cursor hotelcursor; #sql hotelcursor = {SELECT hotelname, rooms FROM hotel WHERE city = : city }; String 2_Cursor hotel 200 cursor; #sql hotel 200 cursor = {SELECT hotelname, city FROM hotel WHERE rooms >= 200 }; nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 32
7. Process/Work with the result (cursor) // Print a list of the hotels that were returned by the first query System. out. println(”Hotels in Rome: ”); while (hotelcursor. next()) { System. out. print(hotelcursor. hotelname()); System. out. print(” ”); System. out. println(hotelcursor. rooms()); } nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 33
7. Process/Work with the result (cursor) // Print a list of the hotels that were returned by the second query String s 1; String s 2; System. out. println(”Hotels with 200 or more rooms: ”); while (true) { #sql { FETCH : hotel 200 cursor INTO : s 1, : s 2 }; if (hotel 200 cursor. end. Fetch()) break; System. out. println (s 1 + "t” + s 2); } nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 34
8. Close the cursor 9. Close the Connection/Context // close the cursors and the connection/context hotelcursor. close(); hotel 200 cursor. close(); ctx. close(); //this will close the connection con 3 as well ctx. close(false); //keep the connection alive con 3. close(); //close only the connection nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 35
More information • JDBC (SUN): http: //java. sun. com/products/jdbc/index. html • SQLJ: http: //www. sqlj. org/ nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 36
OQL, ODQL & embedded OQL, ODQL • Hard to define a border • Depends on the OODBMS • compare SQL – Stored Procedure – Embedded SQL nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 37
Embedded OQL/ODQL • OO programming language – Java – C++ – … • OODBMS – – Jasmine Objectivity Fast. Objects … • Classes – Database specific classes, or – JDO Classes and JDO enhanced database classes • Query Language – Database specific query language, or – JDOQL nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 38
Embedded OQL/ODQL Architecture Java Application JDO classes JDO Enhanced Database Java Classes OODBMS-specific classes OODBMS nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 39
OQL (example) select e. ssn, count(e. teaches) from e in Instructor where e. dept. name = "D 1" and count(e. teaches) > 0 select Instructor from Instructor where Instructor. dept. name = "D 1” and Instructor. teaches. count() > 0 nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 40
Embedded OQL (C++) #include … … %for each v in select e. ssn, count(e. teaches) from e in Instructor where e. dept. name = "D 1" and count(e. teaches) > 0 do %print v; nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 41
Jasmine ODQL default. CF …; … Instructor set vs, v; vs = select Instructor from Instructors where Instructor. dept. name == "D 1” and Instructor. teaches. count() > 0; scan (vs, v) { v. ssn. print(); v. teaches. count(). print(); }; end; nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 42
Embedded Jasmine ODQL i Java import jp. jasmine. japi. *; … db = new Database(); … ODQLStatement odql = db. get. ODQLStatement(); odql. default. CF("my. CF"); odql. execute("Instructor set vs; "); odql. execute("vs = select Instructor from Instructor where Instructor. dept. name == "D 1" and Instructor. teaches. count() > 0; "); DBCollection instructors = (DBCollection) odql. get. Var(“vs"); Enumeration instructors. Enum = instructors. elements(); nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 43
Embedded Jasmine ODQL i Java odql. execute("Instructor v; "); while (instructors. Enum. has. More. Elements()) { DBObject v = (DBObject) instructors. Enum. next. Element(); String ssn = (String) v. get. Property(“ssn"); System. out. println(ssn); odql. define. Var("amount", new Integer(0)); odql. set. Var("v", v); odql. execute("amount = v. teaches. count(); "); System. out. println(odql. get. Int. Var("amount")); } nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 44
Jasmine ODQL (Object Definition and Query Language) is an integrated object language that provides facilities for object definition, object manipulation and object query. ODQL statements can either be embedded and compiled in a host language, or interpreted using the supplied ODQL interpreter nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 45
Jasmine ODQL (structure) default. CF database name Variable declaration Program code All commands must be terminated with ; end; nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 46
Jasmine ODQL if (condition) {program code}; else {program code}; (common constructs) while (condition) {program code}; scan (set, element variable) {program code}; nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 47
Jasmine ODQL (useful methods) Set methods: User defined methods: unique() has. Element(element) has. Same. Elements(set) union(set) count() … Can be defined for a class! nikos dimitrakas For example: age() can be a method defined for the class Person and it calculates the person’s age based on the date of birth. IS 4/2 i 1242/2 i 4042 spring 2007 48
Jasmine ODQL (example) default. CF my. CF; Person set ps, p; Job set js, j; Integer totalsalary; /*All people that live in Stockholm and are over 18*/ ps = select Person from Person where Person. age() > 18 and Person. homeaddress. city == "Stockholm"; nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 49
Jasmine ODQL (example cont. ) scan (ps, p) { totalsalary = 0; p. name. print(); p. is. Rich(). print(); p. jobs. count(). print(); js = p. jobs; scan (js, j) { totalsalary = totalsalary + j. salary; j. description. print(); j. salary. print(); }; totalsalary. print(); }; end; nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 50
Jasmine Java support classes (J-API) • Database – Manages database sessions and transactions. • DBObject – Supports instance-level operations, such as manipulating properties and invoking methods. • DBClass – Supports class-level operations, such as manipulating properties and invoking methods. • DBCollection – Supports Jasmine collections. • ODQLStatement – Supports executing ODQL statements and manipulating ODQL variables. nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 51
Jasmine J-API sequence 1. Import Packages 2. Open a Connection to the database 2 a. Start a session and a transaction 3. Get an ODQL Statement object 3 a. Set the default Class Family 4. Execute ODQL statements or other commands through the ODQL statement object 5. Work with data in Java (back to 4) 6. End the transaction, session and connection nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 52
1. Import Packages import jp. jasmine. japi. *; nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 53
2. Open a Connection to the database 2 a. Start a session and a transaction db = new Database(); db. start. Session(); db. start. Transaction(); nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 54
3. Get an ODQL Statement object 3 a. Set the default Class Family ODQLStatement odql = db. get. ODQLStatement(); odql. default. CF("CAStore"); nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 55
4. Execute ODQL statements or other commands through the ODQL statement object 5. Work with data in Java odql. execute("Customer set vs; "); odql. execute("vs = select Customer from Customer where Customer. orders. count() > 2; "); DBCollection customers = (DBCollection) odql. get. Var("vs"); Enumeration customers. Enum = customers. elements(); while (customers. Enum. has. More. Elements()) { DBObject v = (DBObject) customers. Enum. next. Element(); String name = (String) v. get. Property("name"); System. out. println(name); } nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 56
6. End the transaction, session and connection db. end. Transaction(); db. end. Session(); db. close(); nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 57
More information • OODBMS documentation – Jasmine: Jasmine On-Line Documentation • Jasmine J-API – Programmer’s Guide for Java Proxies (Jp) C: JasmineDocODBjp. pdf nikos dimitrakas IS 4/2 i 1242/2 i 4042 spring 2007 58
Скачать презентацию Embedded SQL Embedded OQL Jasmine ODQL Chapter 21
dbf5472de5cc612fce0f10055f644524.ppt