Download - DB Programming
![Page 1: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/html5/thumbnails/1.jpg)
1
DB Programming
Database SystemsPresented by Rubi Boim
![Page 2: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/html5/thumbnails/2.jpg)
2
Agenda Project Details
Basic Oracle Usage
Little More Complex Oracle stuff..
JDBC
Coding Tips
![Page 3: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/html5/thumbnails/3.jpg)
3
Agenda Project Details
Basic Oracle Usage
Little More Complex Oracle stuff..
JDBC
Coding Tips
![Page 4: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/html5/thumbnails/4.jpg)
4
Oracle Data TypesThere are 3 main groups of types: Character Numeric Date http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/dataty
pe.htm
![Page 5: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/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: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/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: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/html5/thumbnails/7.jpg)
7
Oracle Data Types – Numeric Number Example
![Page 8: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/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: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/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: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/html5/thumbnails/10.jpg)
10
Define Foreign keys
![Page 11: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/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: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/html5/thumbnails/12.jpg)
12
Agenda Project Details
Basic Oracle Usage
Little More Complex Oracle stuff..
JDBC
Coding Tips
![Page 13: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/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: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/html5/thumbnails/14.jpg)
14
Index - HowTo
![Page 15: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/html5/thumbnails/15.jpg)
15
“AutoNumber”
How do you know how to assign an ID??
ID NAME1 Rubi2 Tova3 Itay4 Dvir… …
![Page 16: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/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: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/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: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/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: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/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: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/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 INSERTBEFORE UPDATE AFTER UPDATEBEFORE DELETE AFTER DELETE
![Page 21: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/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: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/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: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/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: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/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_testBEFORE INSERT ON testFOR EACH ROW
BEGINSELECT TEST_SEQ.NEXTVALINTO :NEW.idFROM dual;END;
![Page 25: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/html5/thumbnails/25.jpg)
25
“Complex” Oracle Stuff Demo..
- Create index
- Create “Autonumber”:- Create Sequence- Create Trigger
![Page 26: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/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: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/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: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/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: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/html5/thumbnails/29.jpg)
29
Oracle’s Rownum – Example 1SELECT *FROM studentsWHERE ROWNUM > 1
What NOT to do…
![Page 30: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/html5/thumbnails/30.jpg)
30
Oracle’s Rownum – Example 2SELECT *FROM studentsWHERE ROWNUM < 10ORDER BY students.name
What NOT to do…
![Page 31: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/html5/thumbnails/31.jpg)
31
Oracle’s Rownum – Example 3SELECT * FROM( SELECT *
FROM studentsORDER BY students.name )
WHERE ROWNUM < 10
This will work…
![Page 32: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/html5/thumbnails/32.jpg)
32
Oracle’s Rownum – Example 4SELECT * FROM( SELECT *
FROM studentsORDER BY students.name )
WHERE ROWNUM >= 10 ANDROWNUM < 20
What NOT to do…
![Page 33: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/html5/thumbnails/33.jpg)
33
Oracle’s Rownum – Example 5SELECT * FROM( SELECT a.*, ROWNUM rnum FROM
(SELECT *FROM studentsORDER BY students.name) a
)WHERE rnum >= 10 AND
rnum < 20
Will work but we can do better (y)…
![Page 34: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/html5/thumbnails/34.jpg)
34
Oracle’s Rownum – Example 6SELECT * FROM( SELECT a.*, ROWNUM rnum FROM
(SELECT *FROM studentsORDER BY students.name) aWHERE ROWNUM < 20
)WHERE rnum >= 10
That’s the way…
![Page 35: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/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: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/html5/thumbnails/36.jpg)
36
Little More Complex Oracle Stuff - Demo Demo..
- create Sequence - create Trigger (for autonumber) - limiting the results
![Page 37: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/html5/thumbnails/37.jpg)
37
Agenda Project Details
Basic Oracle Usage
Little More Complex Oracle stuff..
JDBC
Coding Tips
![Page 38: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/html5/thumbnails/38.jpg)
During the last episode…
Application
DB infrastructure
DB driver
transport
DB engine
Storage
![Page 39: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/html5/thumbnails/39.jpg)
Concepts vs APIsConcepts APIs/Language
ConnectionConnection poolingError HandlingFetching resultsRowsetPrepared statementsBatch processing
ODBCJDBCOCI/OCCIADO.NET
X
![Page 40: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/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: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/html5/thumbnails/41.jpg)
41
JDBC JDBC is a standard interface for
connecting to relational databases from Java
![Page 42: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/html5/thumbnails/42.jpg)
42
How to execute SQL using JDBC
![Page 43: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/html5/thumbnails/43.jpg)
43
JDBC Oracle Driver Thin Client driver
written in java
OCI Driverwritten in java & c. must be installed
ODBC Bridge(too general..)
![Page 44: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/html5/thumbnails/44.jpg)
44
JDBC Oracle Driver Thin vs OCI
![Page 45: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/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: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/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: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/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: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/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: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/html5/thumbnails/49.jpg)
49
Opening a Connection Demo..
![Page 50: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/html5/thumbnails/50.jpg)
50
Creating a Statement Created from the connection object
Statement stmt = conn.createStatement();
![Page 51: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/html5/thumbnails/51.jpg)
51
Using a StatementThree different methods: executeQuery(String) for SELECT statements
returns ResultSet
executeUpdate(String) for DML/DDLreturns int
execute(String) for any SQL statementreturns boolean
![Page 52: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/html5/thumbnails/52.jpg)
52
executeQuery & ResultSetResultSet: 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: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/html5/thumbnails/53.jpg)
53
executeQuery & ResultSet Initially the cursor is positioned before the
first rowstmt = conn.createStatement();rs = stmt.executeQuery(
"SELECT * FROM employees");while (rs.next() == true)
System.out.println(rs.getString(“field”));
Demo..
![Page 54: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/html5/thumbnails/54.jpg)
54
executeUpdate Again, via the statement
Execute DDL or DML
Returns Int for DML, 0 for DDL
![Page 55: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/html5/thumbnails/55.jpg)
55
executeUpdatestmt=conn.createStatement();result=stmt.executeUpdate(
"DELETE FROM demo");
Demo..
![Page 56: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/html5/thumbnails/56.jpg)
56
execute Executes any command for the DB
Returns boolean (success/failure)
Not sure you’ll need it..
![Page 57: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/html5/thumbnails/57.jpg)
57
Closing Connections Important! So don’t forget..
ResultSet.close() Statement.close() Connection.close()
![Page 58: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/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: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/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: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/html5/thumbnails/60.jpg)
60
PreparedStatement Prevents reparsing of SQL statements
Used for statements executed more than once
Saves time
Nicer code
![Page 61: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/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: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/html5/thumbnails/62.jpg)
62
PreparedStatement - examplePreparedStatement 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: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/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: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/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: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/html5/thumbnails/65.jpg)
65
Agenda Project Details
Basic Oracle Usage
Little More Complex Oracle stuff..
JDBC
Coding Tips
![Page 66: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/html5/thumbnails/66.jpg)
Layering Separate the GUI!
Separate the DB!
Use classes to describe entities
Use interfaces!
![Page 67: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/html5/thumbnails/67.jpg)
Layering
DB Logic GUI
Interface
Interface
DataClass
DataClass
![Page 68: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/html5/thumbnails/68.jpg)
Reuse & Encapsulation Identify main processes
Abstract implementation
Reuse..
NO COPY PASTE CODE
![Page 69: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/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: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/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: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/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: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/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: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/html5/thumbnails/73.jpg)
Good questions… Managing Database Connections
Managing Security
Managing Threads
Error handling
![Page 74: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/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 NAME1 Yonni2 Tova3 Dvir
![Page 75: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/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: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/html5/thumbnails/76.jpg)
76
Retrieving the AutoNumber Generated
Demo.. (I.E. there is an example code )
![Page 77: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/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: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/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: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/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: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/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: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/html5/thumbnails/81.jpg)
81
Connection Pooling – example Example of what might it look..MyConn conn = cManager.poolConn();conn.getJDBCConn.executeQuery(..);
conn.returnConnection(); ORcManager.returnConn(conn)
Implement it your own way, but be sure to use “synchronized”
![Page 82: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/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: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/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: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/html5/thumbnails/84.jpg)
84
Database Design
Don’t forget to normalize the DB Use the right types
ID NumberCD_NAME NVARCHAR(50)ARTIST_NAME NVARCHAR(50)GENRE NVARCHAR(50)YEAR DATE
![Page 85: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/html5/thumbnails/85.jpg)
Usability “non-refreshed” windows “Hangs” windows
“Please wait…Your query may take a couple of minutes…”
![Page 86: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/html5/thumbnails/86.jpg)
Usability II
![Page 87: DB Programming](https://reader035.vdocuments.us/reader035/viewer/2022062520/56815e31550346895dcc8de9/html5/thumbnails/87.jpg)
Thank you