database systems presented by rubi boim 1. project details basic oracle usage little more complex...
TRANSCRIPT
![Page 1: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/1.jpg)
1
DB Programming
Database SystemsPresented by Rubi Boim
![Page 2: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/2.jpg)
2
Agenda
Project Details
Basic Oracle Usage
Little More Complex Oracle stuff..
JDBC
Coding Tips
![Page 3: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/3.jpg)
3
Agenda
Project Details
Basic Oracle Usage
Little More Complex Oracle stuff..
JDBC
Coding Tips
![Page 4: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/4.jpg)
4
Oracle Data Types
There are 3 main groups of types: Character Numeric Date
http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/datatype.htm
![Page 5: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/5.jpg)
5
Oracle Data Types – Character
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)
![Page 6: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/6.jpg)
6
Oracle Data Types – Numeric
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
![Page 7: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/7.jpg)
7
Oracle Data Types – Numeric
Number Example
![Page 8: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/8.jpg)
8
Oracle Data Types – Date
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.')
![Page 9: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/9.jpg)
9
Define Foreign keys
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
![Page 10: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/10.jpg)
10
Define Foreign keys
![Page 11: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/11.jpg)
11
Basic oracle usage - Demo
Demo.. - create table (data types) - define primary key - define foreign keys (insert / delete data)
![Page 12: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/12.jpg)
12
Agenda
Project Details
Basic Oracle Usage
Little More Complex Oracle stuff..
JDBC
Coding Tips
![Page 13: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/13.jpg)
13
Index
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
![Page 14: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/14.jpg)
14
Index - HowTo
![Page 15: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/15.jpg)
15
“AutoNumber”
How do you know how to assign an ID??
ID NAME
1 Rubi
2 Tova
3 Itay
4 Dvir
… …
![Page 16: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/16.jpg)
16
“AutoNumber” – Algorithm?
Lock table
new_id = 1 + select max id from table
insert into table values(new_id, ”Rubi”);
Unlock table
![Page 17: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/17.jpg)
17
Sequence
Sequence - an object from which multiple users may generate unique integers
NEXTVAL() - incrementsthe sequence and returnsthe new value.
![Page 18: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/18.jpg)
18
Sequence – Insert example
If we defined the sequence as TEST_SEQ
INSERT INTO test values(TEST_SEQ.NEXTVAL, 'rubi')
Usually, sequence is defined astable_name +”_SEQ”
![Page 19: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/19.jpg)
19
Sequence – Can we do better?
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')
![Page 20: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/20.jpg)
20
Triggers
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
![Page 21: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/21.jpg)
21
Triggers – Statement Level
Occurs only once per Insert/Update/Delete
CREATE OR REPLACE TRIGGER <trigger_name>
<BEFORE | AFTER> <ACTION> ON <table_name>
BEGIN <trigger_code>
END;
![Page 22: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/22.jpg)
22
Triggers – Row Level
Occurs for each row
CREATE OR REPLACE TRIGGER <trigger_name>
<BEFORE | AFTER> <ACTION> ON <table_name>
FOR EACH ROW
BEGIN <trigger_code>
END;
![Page 23: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/23.jpg)
23
Triggers – Row Level – Example
You can not “just use the GUI” - you need to “code” the trigger”
After you press “ok” you can edit the code
![Page 24: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/24.jpg)
24
Triggers – Row Level – Example
Use “NEW” to refer to the row dual – simply a scratch-pad
select 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;
![Page 25: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/25.jpg)
25
“Complex” Oracle Stuff
Demo.. - Create index
- Create “Autonumber”:- Create Sequence- Create Trigger
![Page 26: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/26.jpg)
26
Limit the Results
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
![Page 27: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/27.jpg)
27
Oracle’s Rownum
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..
![Page 28: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/28.jpg)
28
Oracle’s Rownum – NOT THAT SIMPLE! Its assigned BEFORE sorting or
aggregation
ROWNUM value is incremented only after it is assigned
Read the previous lines 5 more times!
![Page 29: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/29.jpg)
29
Oracle’s Rownum – Example 1
SELECT *
FROM students
WHERE ROWNUM > 1
What NOT to do…
![Page 30: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/30.jpg)
30
Oracle’s Rownum – Example 2
SELECT *
FROM students
WHERE ROWNUM < 10
ORDER BY students.name
What NOT to do…
![Page 31: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/31.jpg)
31
Oracle’s Rownum – Example 3
SELECT * FROM
( SELECT *
FROM students
ORDER BY students.name )
WHERE ROWNUM < 10
This will work…
![Page 32: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/32.jpg)
32
Oracle’s Rownum – Example 4
SELECT * FROM
( SELECT *
FROM students
ORDER BY students.name )
WHERE ROWNUM >= 10 AND
ROWNUM < 20
What NOT to do…
![Page 33: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/33.jpg)
33
Oracle’s Rownum – Example 5
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)…
![Page 34: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/34.jpg)
34
Oracle’s Rownum – Example 6
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…
![Page 35: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/35.jpg)
35
Oracle’s Rownum – Final slide
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
![Page 36: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/36.jpg)
36
Little More Complex Oracle Stuff - Demo Demo..
- create Sequence - create Trigger (for autonumber) - limiting the results
![Page 37: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/37.jpg)
37
Agenda
Project Details
Basic Oracle Usage
Little More Complex Oracle stuff..
JDBC
Coding Tips
![Page 38: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/38.jpg)
During the last episode…
Application
DB infrastructure
DB driver
transport
DB engine
Storage
![Page 39: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/39.jpg)
Concepts vs APIs
Concepts APIs/Language
ConnectionConnection poolingError HandlingFetching resultsRowsetPrepared statementsBatch processing
ODBCJDBCOCI/OCCIADO.NET
X
![Page 40: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/40.jpg)
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
![Page 41: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/41.jpg)
41
JDBC
JDBC is a standard interface for connecting to relational databases from Java
![Page 42: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/42.jpg)
42
How to execute SQL using JDBC
![Page 43: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/43.jpg)
43
JDBC Oracle Driver
Thin Client driverwritten in java
OCI Driverwritten in java & c. must be installed
ODBC Bridge(too general..)
![Page 44: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/44.jpg)
44
JDBC Oracle Driver
Thin vs OCI
![Page 45: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/45.jpg)
45
Preparing the Environment 1
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
![Page 46: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/46.jpg)
46
Preparing the Environment 2
If you copy the jar file to the project directory, press “add JAR”. Otherwise, “Add external JAR”
![Page 47: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/47.jpg)
47
Preparing the Environment 3
import java.sql.* (JDBC API)
Register the driver in the code:Class.forName("oracle.jdbc.OracleDriver");
![Page 48: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/48.jpg)
48
Opening a Connection
Connection class - java.sql.Connection
use the DriverManager with JDBC URL
conn = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:XE",
“username",
“password");
![Page 49: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/49.jpg)
49
Opening a Connection
Demo..
![Page 50: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/50.jpg)
50
Creating a Statement
Created from the connection object
Statement stmt = conn.createStatement();
![Page 51: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/51.jpg)
51
Using a Statement
Three different methods: executeQuery(String) for SELECT statements
returns ResultSet
executeUpdate(String) for DML/DDLreturns int
execute(String) for any SQL statementreturns boolean
![Page 52: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/52.jpg)
52
executeQuery & ResultSet
ResultSet: Maintain a curser to its current row Provides methods for retrieving values:
getInt(), getDate(), getString()..
Fields can be identify by name or order:getXXX(“Name”)getXXX(2)
![Page 53: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/53.jpg)
53
executeQuery & ResultSet
Initially the cursor is positioned before the first row
stmt = conn.createStatement();
rs = stmt.executeQuery(
"SELECT * FROM employees");
while (rs.next() == true)
System.out.println(rs.getString(“field”));
Demo..
![Page 54: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/54.jpg)
54
executeUpdate
Again, via the statement
Execute DDL or DML
Returns Int for DML, 0 for DDL
![Page 55: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/55.jpg)
55
executeUpdate
stmt=conn.createStatement();
result=stmt.executeUpdate(
"DELETE FROM demo");
Demo..
![Page 56: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/56.jpg)
56
execute
Executes any command for the DB
Returns boolean (success/failure)
Not sure you’ll need it..
![Page 57: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/57.jpg)
57
Closing Connections
Important! So don’t forget..
ResultSet.close() Statement.close() Connection.close()
![Page 58: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/58.jpg)
58
Transactions
By default, connection are autocommit
Can be disabled by:conn.setAutoCommit(false)
Commit a transaction:conn.commit() Rollback a transaction: conn.rollback()
![Page 59: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/59.jpg)
59
Transactions – When to use?
In general, in any logic operation that involves more than one call:insert/update/remove into several tables
Inconsistent data is unacceptable!
Don’t forget to use!
![Page 60: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/60.jpg)
60
PreparedStatement
Prevents reparsing of SQL statements
Used for statements executed more than once
Saves time
Nicer code
![Page 61: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/61.jpg)
61
PreparedStatement - how
Specify a variable by “?”PreparedStatement pstmt = conn.prepareStatement(
"INSERT INTO demo(fname, lname) VALUES(?, ?)");
Supply values for the variables:pstmt.setXXX(index, value)
Execute the statementpstmt.executeUpdate();
![Page 62: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/62.jpg)
62
PreparedStatement - example
PreparedStatement pstmt = conn.prepareStatement(
"INSERT INTO demo(fname, lname) VALUES(?, ?)");
pstmt.setString(1, "Rubi");
pstmt.setString(2, "Boim”);
pstmt.executeUpdate();
pstmt.setString(1, “Tova");
pstmt.setString(2, “Milo”);
pstmt.executeUpdate();
Demo..
![Page 63: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/63.jpg)
63
Batch PreparedStatement
PreparedStatement can be slow for long calls
Batch together all the calls!
I.E. instead of 50,000 calls, do one call with 50,000 parameters
Improves performance dramatically!
![Page 64: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/64.jpg)
64
Batch PreparedStatement - how
Instead of pstmt.executeUpdate()do pstmt.addBatch()
After all statement are added to the batch: int[] = pstmt.executeBatch()
TIP: don’t batch too much together
Demo..
![Page 65: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/65.jpg)
65
Agenda
Project Details
Basic Oracle Usage
Little More Complex Oracle stuff..
JDBC
Coding Tips
![Page 66: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/66.jpg)
Layering
Separate the GUI!
Separate the DB!
Use classes to describe entities
Use interfaces!
![Page 67: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/67.jpg)
Layering
DB Logic GUI
Inte
rface
Inte
rface
DataClass
DataClass
![Page 68: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/68.jpg)
Reuse & Encapsulation
Identify main processes
Abstract implementation
Reuse..
NO COPY PASTE CODE
![Page 69: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/69.jpg)
69
Don’t create too many functions
Search for movies:searchMovieByName()searchMovieByDate()..
It’s the same query! just different “where” manipulate the “where” in the function:SearchMovie(searchOptions?)
Not so easy on some parameters..searchMovieByActors()searchMovieByActorsAndDate() any ideas?
![Page 70: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/70.jpg)
Configuration
Your program will have several (many) variables: - server address - textfile location - number of connections/threads - ….
Do not “hard code” them *.ini file, easy GUI, ….
![Page 71: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/71.jpg)
Schema
Well, you should be expert by now..
Primary Key - ALWAYS integer!
Use indexes to speed up (but not on every field)
![Page 72: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/72.jpg)
Testing
Obvious not?
Try installing / running your program on different computers
Connection drops
Validate user input (date, number, special chars..)
Your program should never fall!!
![Page 73: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/73.jpg)
Good questions…
Managing Database Connections
Managing Security
Managing Threads
Error handling
![Page 74: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/74.jpg)
74
How to insert with AutoNumber
Assuming you created a trigger similar to the one showed before..
Specify the exact fields in the “Insert”(I.E. neglect the “triggered” ones)
INSERT INTO test(name) VALUES(‘Rubi’);
ID NAME
1 Yonni
2 Tova
3 Dvir
![Page 75: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/75.jpg)
75
Retrieving the AutoNumber Generated
When calling “executeUpdate”, you can specify which fields you can “get back”
After executing, use getGeneratedKeys() to retrieve a resultset with the returned fields
stmt.executeUpdate("INSERT INTO demo(fname, lname)
VALUES('Rubi','Boim')",
new String[]{"ID"});
rs=stmt.getGeneratedKeys();
rs.next();
id=rs.getInt(1);
![Page 76: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/76.jpg)
76
Retrieving the AutoNumber Generated
Demo.. (I.E. there is an example code )
![Page 77: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/77.jpg)
77
How to insert Strings
In an SQL Query, strings are surrounded by ‘
But what if we want to insert the char ‘?
INSERT INTO test VALUES(‘It’s a test’);
Simply add another ‘ INSERT INTO test VALUES(‘It’’s a test’);
![Page 78: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/78.jpg)
78
How to insert Dates
Read the “to_date” manual..http://www.techonthenet.com/oracle/functions/to_date.php
stmt.executeUpdate(
"INSERT INTO demo(fname, lname, mydate) VALUES('Rubi',
'Boim',
to_date('13/12/2008', 'dd/mm/yyyy'))");
![Page 79: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/79.jpg)
79
Important Tip for Manipulating Data
What if tomorrow your switch to MySQL?? Create your own functions for adjusting types
(not just dates)
String fixDate(String old_date)
{ return “to_date(‘” + old_date + ”', 'dd/mm/yyyy')”}
stmt.executeUpdate(
"INSERT INTO demo(fname, lname, mydate) VALUES('Rubi', 'Boim',” + fixDate('13/12/2008‘) + ”)”);
![Page 80: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/80.jpg)
80
Connection Pooling
Opening a connection is “expensive”
Multi tasks requires multi connections
You should open a connection only when you need it (I.E. when a task asks for connection and there is no one available)
When the task is done, do not close the connection but returns it to the “manager” for future use
![Page 81: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/81.jpg)
81
Connection Pooling – example
Example of what might it look..MyConn conn = cManager.poolConn();
conn.getJDBCConn.executeQuery(..);
conn.returnConnection(); OR
cManager.returnConn(conn)
Implement it your own way, but be sure to use “synchronized”
![Page 82: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/82.jpg)
82
Thread Pooling
If you build your application correctly, the GUI should be separate from the “program”
Same concept as the Connection Pooling
More about it when we talk about the GUI
![Page 83: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/83.jpg)
Coding tips
The following next slides are EXAMPLES for what NOT-TO-DO in the project. Basically they are based on last years submissions, which were altered to express important points.
![Page 84: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/84.jpg)
84
Database Design
Don’t forget to normalize the DB Use the right types
ID Number
CD_NAME NVARCHAR(50)
ARTIST_NAME NVARCHAR(50)
GENRE NVARCHAR(50)
YEAR DATE
![Page 85: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/85.jpg)
Usability
“non-refreshed” windows “Hangs” windows
“Please wait…Your query may take a couple of minutes…”
![Page 86: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/86.jpg)
Usability II
![Page 87: Database Systems Presented by Rubi Boim 1. Project Details Basic Oracle Usage Little More Complex Oracle stuff.. JDBC Coding Tips 2](https://reader038.vdocuments.us/reader038/viewer/2022110213/56649ce45503460f949b01e7/html5/thumbnails/87.jpg)
Thank you