1 / 87

DB Programming

DB Programming. Database Systems Presented by Rubi Boim. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips. Agenda. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips. Agenda. There are 3 main groups of types:

tien
Download Presentation

DB Programming

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. DB Programming Database Systems Presented by Rubi Boim

  2. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips Agenda

  3. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips Agenda

  4. There are 3 main groups of types: • Character • Numeric • Date • http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/datatype.htm Oracle Data Types

  5. Character set is established when you create the database (UTF-8,ANSI..). So ignore..  Char: Fixed length! Short value is padded Varchar2: variable-length Both types needs to define max length(4000 max. for more use BLOB) Oracle Data Types – Character

  6. Implemented by the “Number” data type A Number has two properties:- precision: Total number of digits- scale: Number of digits after the point(up to 38 digits) For floating point (if you need..): BINARY_FLOAT, BINARY_DOUBLE Oracle Data Types – Numeric

  7. Number Example Oracle Data Types – Numeric

  8. Implemented by the “Date” data type Stores “dates” and “times” Default format is DD-MON-YY Use the TO_DATE function for any other formatTO_DATE('13-AUG-66 12:56 A.M.','DD-MON-YY HH:MI A.M.') Oracle Data Types – Date

  9. Don’t forget to define the primary key on the other table.. What happens when you delete the “key record” from the “primary table”? - Restrict - Cascade - Set null Define Foreign keys

  10. Define Foreign keys

  11. Demo.. - create table (data types) - define primary key - define foreign keys (insert / delete data) Basic oracle usage - Demo

  12. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips Agenda

  13. Index improves the speed of operations on a table Can be created using one or more fields You will later learn more.. But don’t forget, its important Index

  14. Index - HowTo

  15. How do you know how to assign an ID?? “AutoNumber”

  16. Lock table new_id = 1 + select max id from table insert into table values(new_id, ”Rubi”); Unlock table “AutoNumber” – Algorithm?

  17. Sequence - an object from which multiple users may generate unique integers NEXTVAL() - incrementsthe sequence and returnsthe new value. Sequence

  18. If we defined the sequence as TEST_SEQ INSERT INTO test values(TEST_SEQ.NEXTVAL, 'rubi') Usually, sequence is defined astable_name +”_SEQ” Sequence – Insert example

  19. Why are we complicating things?? Do all DBMS support sequences? If we change a sequence name, we need to update all our queries Can we separate it from the query?INSERT INTO test(name) values('rubi') Sequence – Can we do better?

  20. A database trigger is procedural code that is automatically executed in response to certain events on a particular table Events: BEFORE INSERT AFTER INSERT BEFORE UPDATE AFTER UPDATE BEFORE DELETE AFTER DELETE Triggers

  21. Occurs only once per Insert/Update/Delete CREATE OR REPLACE TRIGGER <trigger_name> <BEFORE | AFTER> <ACTION> ON <table_name> BEGIN  <trigger_code> END; Triggers – Statement Level

  22. Occurs for each row CREATE OR REPLACE TRIGGER <trigger_name> <BEFORE | AFTER> <ACTION> ON <table_name> FOR EACH ROW BEGIN  <trigger_code> END; Triggers – Row Level

  23. You can not “just use the GUI” - you need to “code” the trigger” • After you press “ok” you can edit the code Triggers – Row Level – Example

  24. Use “NEW” to refer to the row • dual – simply a scratch-padselect max(12,54,2,75,142) from dual CREATE bi_test BEFORE INSERT ON test FOR EACH ROW BEGIN SELECT TEST_SEQ.NEXTVAL INTO :NEW.id FROM dual; END; Triggers – Row Level – Example

  25. Demo.. - Create index - Create “Autonumber”: - Create Sequence - Create Trigger “Complex” Oracle Stuff

  26. What if your query returns 1,000,000 results? How to return the TOP n results How to return the results from n to m Limit the Results

  27. Works only on Oracle..(mysql has “Limit”, sql-server has “Top”) ROWNUM is a pseudocolumn (not “real”) Each row is assigned with a number, starting with 1 We can select just the ones we want.. Oracle’s Rownum

  28. Its assigned BEFORE sorting or aggregation ROWNUM value is incremented only after it is assigned Read the previous lines 5 more times! Oracle’s Rownum – NOT THAT SIMPLE!

  29. SELECT * FROM students WHERE ROWNUM > 1 What NOT to do…  Oracle’s Rownum – Example 1

  30. SELECT * FROM students WHERE ROWNUM < 10 ORDER BY students.name What NOT to do…  Oracle’s Rownum – Example 2

  31. SELECT * FROM ( SELECT * FROM students ORDER BY students.name ) WHERE ROWNUM < 10 This will work… Oracle’s Rownum – Example 3

  32. SELECT * FROM ( SELECT * FROM students ORDER BY students.name ) WHERE ROWNUM >= 10 AND ROWNUM < 20 What NOT to do…  Oracle’s Rownum – Example 4

  33. SELECT * FROM ( SELECT a.*, ROWNUM rnum FROM ( SELECT * FROM students ORDER BY students.name ) a ) WHERE rnum >= 10 AND rnum < 20 Will work but we can do better (y)…  Oracle’s Rownum – Example 5

  34. SELECT * FROM ( SELECT a.*, ROWNUM rnum FROM ( SELECT * FROM students ORDER BY students.name ) a WHERE ROWNUM < 20 ) WHERE rnum >= 10 That’s the way…  Oracle’s Rownum – Example 6

  35. There is a big difference between > and < If you are using “example 6”, be sure the order by is unique (y?) btw, in MySQL its simply:select * from students order by name limit 10,20 Oracle’s Rownum – Final slide 

  36. Demo.. - create Sequence - create Trigger (for autonumber) - limiting the results Little More Complex Oracle Stuff - Demo

  37. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips Agenda

  38. During the last episode… Application DB infrastructure DB driver transport DB engine Storage

  39. Concepts vs APIs Concepts APIs/Language Connection Connection pooling Error Handling Fetching results Rowset Prepared statements Batch processing ODBC JDBC OCI/OCCI ADO.NET X

  40. ODBC – Open Database Connectivity API • Pros: • Cross platform and cross databases • Easy to use • Cons: • Too low level • We wont use it.. But its very very common

  41. JDBC is a standard interface for connecting to relational databases from Java JDBC

  42. How to execute SQL using JDBC

  43. Thin Client driverwritten in java OCI Driverwritten in java & c. must be installed ODBC Bridge(too general..) JDBC Oracle Driver

  44. Thin vs OCI JDBC Oracle Driver

  45. Download Oracle’s JDBC driver:http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/htdocs/jdbc_112010.html Can also be found at the course page Setup Eclipse: - add the jar “ojdbc6.jar” to the project Preparing the Environment 1

  46. Preparing the Environment 2 If you copy the jar file to the project directory, press “add JAR”. Otherwise, “Add external JAR”

  47. import java.sql.* (JDBC API) Register the driver in the code:Class.forName("oracle.jdbc.OracleDriver"); Preparing the Environment 3

  48. Connection class - java.sql.Connection use the DriverManager with JDBC URL conn = DriverManager.getConnection( "jdbc:oracle:thin:@localhost:1521:XE", “username", “password"); Opening a Connection

  49. Demo.. Opening a Connection

  50. Created from the connection object Statement stmt = conn.createStatement(); Creating a Statement

More Related