5 Persistent data storage relational databases Mark Dixon

  • Slides: 43
Download presentation
5 – Persistent data storage: relational databases Mark Dixon Page 1

5 – Persistent data storage: relational databases Mark Dixon Page 1

Questions: Session variables • Write a line of code to put 31 into a

Questions: Session variables • Write a line of code to put 31 into a session variable called score. Session. set. Attribute("score", 31); • Write code that adds 1 to a variable called g, when a session variable called i is over 25. if (session. get. Attribute("i") > 25){ g = g + 1; } Mark Dixon Page 2

Admin: SQL Book Gennick J (2006) SQL Pocket Guide (2 nd edition). O'Reilly. ISBN:

Admin: SQL Book Gennick J (2006) SQL Pocket Guide (2 nd edition). O'Reilly. ISBN: 0 -596 -52688 -1 Mark Dixon Page 3

Session Aims & Objectives • Aims – To introduce the fundamental ideas involved in

Session Aims & Objectives • Aims – To introduce the fundamental ideas involved in using relational databases for persistent data storage • Objectives, by end of this week’s sessions, you should be able to: – create a relational database table – create a web page (JSP) that displays data from a single table in a database – use SQL in your programs to create more complex record-sets Mark Dixon Page 4

Persistent Data Storage • So far – programs (web-pages) lose data when closed •

Persistent Data Storage • So far – programs (web-pages) lose data when closed • Not realistic – typically data stored to persistent storage device (e. g. hard disk, key drive, floppy disk, CD-RW) • Use either – flat files – database (relational, or object oriented) Mark Dixon Page 5

Example: People (analysis) SPECIFICATION • User Requirements – need to have access to people's

Example: People (analysis) SPECIFICATION • User Requirements – need to have access to people's details • Software Requirements – Functional: – Display list of people from a database – Non-functional should be viewable anywhere in the world Mark Dixon Page 6

Example: People (Database) • Information organised into – tables (e. g. person) – fields

Example: People (Database) • Information organised into – tables (e. g. person) – fields (e. g. phone) – records (e. g. 1 Dixon Mark 01752 232556 …) Person ID 1 2 3 Mark Dixon Surname Dixon Smith Jones Field Record Forenames Mark John Sally Phone 01752 232556 01752 111111 01752 888888 Gender Male Female Page 7

Questions: Music (Database) • How many fields? • How many records? 3 9 Track

Questions: Music (Database) • How many fields? • How many records? 3 9 Track Mark Dixon Track Title Artist Name Country Paranoid Black Sabbath UK Falling in Love Aerosmith US Pink Aerosmith US Love in an Elevator Aerosmith US Smooth Criminal Alien Ant Farm US Meaning of Life Disturbed US The Game Disturbed US Voices Disturbed US Down with the Sickness Disturbed US Page 8

DBMS • Database Management Systems (DBMS) provide facilities for: – creating and changing databases

DBMS • Database Management Systems (DBMS) provide facilities for: – creating and changing databases • add/remove records • add/remove fields • add/remove data – For example: • • • Mark Dixon Microsoft Access d. Base Borland Paradox My. SQL Microsoft SQL Server Oracle home/small business large scale Page 9

MS Access Mark Dixon Music database Page 10

MS Access Mark Dixon Music database Page 10

How to: Create Data Source • Start Menu • Control Panel • Administrative Tools

How to: Create Data Source • Start Menu • Control Panel • Administrative Tools • Data Sources (ODBC) • System DSN • Add Mark Dixon 64 bit Problem – No MS Access driver Solution: use C: WindowsSys. WOW 64odbcad 32. exe Page 11

How to: Create Data Source • Select MS Access Driver • Click Finish Mark

How to: Create Data Source • Select MS Access Driver • Click Finish Mark Dixon Page 12

How to: Create Data Source • Type Data Source Name • Click Select button

How to: Create Data Source • Type Data Source Name • Click Select button Mark Dixon Page 13

How to: Create Data Source • • • Locate Database Select Database Click OK

How to: Create Data Source • • • Locate Database Select Database Click OK Mark Dixon Page 14

JDBC • standard library access relational databases – – establish connection to database initiating

JDBC • standard library access relational databases – – establish connection to database initiating queries create stored (parameterised) queries The data structure of query result (table) • Determining the number of columns • Looking up metadata, etc. – located in java. sql package • Note: JDBC not officially an acronym – unofficially “Java Database Connectivity” Mark Dixon Page 15

People. js p Example: People <%@page import="java. sql. *"%> Import JDBC class <%@page content.

People. js p Example: People <%@page import="java. sql. *"%> Import JDBC class <%@page content. Type="text/html"%> <% Class. for. Name("sun. jdbc. odbc. Jdbc. Odbc. Driver"); Connection cn = Driver. Manager. get. Connection("jdbc: odbc: People. DB", ""); Statement st = cn. create. Statement(); Result. Set r = st. execute. Query("SELECT * FROM Person; "); String html = ""; while(r. next()){ html += r. get. String("Surname"); } cn. close(); %> <!DOCTYPE html> <head><title></head> <body> <%=html%> </body> </html> Mark Dixon Page 16

People. js p Example: People 1 <%@page import="java. sql. *"%> <%@page content. Type="text/html"%> <%

People. js p Example: People 1 <%@page import="java. sql. *"%> <%@page content. Type="text/html"%> <% load driver Class. for. Name("sun. jdbc. odbc. Jdbc. Odbc. Driver"); Connection cn = Driver. Manager. get. Connection("jdbc: odbc: People. DB", ""); Statement st = cn. create. Statement(); Result. Set r = st. execute. Query("SELECT * FROM Person; "); String html = ""; while(r. next()){ html += r. get. String("Surname"); } cn. close(); %> <!DOCTYPE html> <head><title></head> <body> <%=html%> </body> </html> Mark Dixon Page 17

People. js p Example: People 2 <%@page import="java. sql. *"%> <%@page content. Type="text/html"%> <%

People. js p Example: People 2 <%@page import="java. sql. *"%> <%@page content. Type="text/html"%> <% connection Class. for. Name("sun. jdbc. odbc. Jdbc. Odbc. Driver"); Connection cn = Driver. Manager. get. Connection("jdbc: odbc: People. DB", ""); Statement st = cn. create. Statement(); Result. Set r = st. execute. Query("SELECT * FROM Person; "); String html = ""; while(r. next()){ html += r. get. String("Surname"); } cn. close(); %> <!DOCTYPE html> <head><title></head> <body> <%=html%> </body> </html> Mark Dixon Page 18

People. js p Example: People 3 <%@page import="java. sql. *"%> <%@page content. Type="text/html"%> <%

People. js p Example: People 3 <%@page import="java. sql. *"%> <%@page content. Type="text/html"%> <% Class. for. Name("sun. jdbc. odbc. Jdbc. Odbc. Driver"); Connection cn = Driver. Manager. get. Connection("jdbc: odbc: People. DB", ""); Statement st = cn. create. Statement(); statement Result. Set r = st. execute. Query("SELECT * FROM Person; "); String html = ""; while(r. next()){ html += r. get. String("Surname"); } cn. close(); %> <!DOCTYPE html> <head><title></head> <body> <%=html%> </body> </html> Mark Dixon Page 19

People. js p Example: People 4 <%@page import="java. sql. *"%> <%@page content. Type="text/html"%> <%

People. js p Example: People 4 <%@page import="java. sql. *"%> <%@page content. Type="text/html"%> <% Class. for. Name("sun. jdbc. odbc. Jdbc. Odbc. Driver"); Connection cn = Driver. Manager. get. Connection("jdbc: odbc: People. DB", ""); Statement st = cn. create. Statement(); Result. Set r = st. execute. Query("SELECT * FROM Person; "); results String html = ""; while(r. next()){ html += r. get. String("Surname"); } cn. close(); %> <!DOCTYPE html> <head><title></head> <body> <%=html%> </body> </html> Mark Dixon Page 20

People. js p Example: People 5 <%@page import="java. sql. *"%> <%@page content. Type="text/html"%> <%

People. js p Example: People 5 <%@page import="java. sql. *"%> <%@page content. Type="text/html"%> <% Class. for. Name("sun. jdbc. odbc. Jdbc. Odbc. Driver"); Connection cn = Driver. Manager. get. Connection("jdbc: odbc: People. DB", ""); Statement st = cn. create. Statement(); Result. Set r = st. execute. Query("SELECT * FROM Person; "); String html = ""; while(r. next()){ process html += r. get. String("Surname"); } cn. close(); %> <!DOCTYPE html> <head><title></head> <body> <%=html%> </body> </html> Mark Dixon Page 21

People. js p Example: People 6 <%@page import="java. sql. *"%> <%@page content. Type="text/html"%> <%

People. js p Example: People 6 <%@page import="java. sql. *"%> <%@page content. Type="text/html"%> <% Class. for. Name("sun. jdbc. odbc. Jdbc. Odbc. Driver"); Connection cn = Driver. Manager. get. Connection("jdbc: odbc: People. DB", ""); Statement st = cn. create. Statement(); Result. Set r = st. execute. Query("SELECT * FROM Person; "); String html = ""; while(r. next()){ html += r. get. String("Surname"); } cn. close(); close %> connection <!DOCTYPE html> <head><title></head> <body> <%=html%> </body> </html> Mark Dixon Page 22

Embedding html in java <%@page import="java. sql. *"%> Need data on <%@page content. Type="text/html"%>

Embedding html in java <%@page import="java. sql. *"%> Need data on <%@page content. Type="text/html"%> separate lines <% Class. for. Name("sun. jdbc. odbc. Jdbc. Odbc. Driver"); Connection cn = Driver. Manager. get. Connection("jdbc: odbc: People. DB", ""); Statement st = cn. create. Statement(); html by hand Result. Set r = st. execute. Query("SELECT * FROM Person; "); put br tags between data String html = ""; while(r. next()){ html += r. get. String("Surname"); } cn. close(); %> <!DOCTYPE html> <head><title></head> <body> <%=html%> </body> </html> Mark Dixon Page 23

Embedding html in java <%@page import="java. sql. *"%> <%@page content. Type="text/html"%> <% Class. for.

Embedding html in java <%@page import="java. sql. *"%> <%@page content. Type="text/html"%> <% Class. for. Name("sun. jdbc. odbc. Jdbc. Odbc. Driver"); Connection cn = Driver. Manager. get. Connection("jdbc: odbc: People. DB", ""); Statement st = cn. create. Statement(); Result. Set r = st. execute. Query("SELECT * FROM Person; "); error – java does not String html = ; understand html while(r. next()){ html += r. get. String("Surname"); } cn. close(); %> <!DOCTYPE html> <head><title></head> <body> <%=html%> </body> </html> Mark Dixon Page 24

Embedding html in java <%@page import="java. sql. *"%> • Need double <%@page content. Type="text/html"%>

Embedding html in java <%@page import="java. sql. *"%> • Need double <%@page content. Type="text/html"%> quotes around <% tag Class. for. Name("sun. jdbc. odbc. Jdbc. Odbc. Driver"); • java Connection cn = Driver. Manager. get. Connection("jdbc: odbc: People. DB", "", sees ""); html as literal Statement st = cn. create. Statement(); Result. Set r = st. execute. Query("SELECT * FROM Person; "); string String html = " "; while(r. next()){ html += r. get. String("Surname"); } cn. close(); %> <!DOCTYPE html> <head><title></head> <body> runs, but in <%=html%> wrong place </body> </html> Mark Dixon Page 25

Embedding html in java <%@page import="java. sql. *"%> <%@page content. Type="text/html"%> <% Class. for.

Embedding html in java <%@page import="java. sql. *"%> <%@page content. Type="text/html"%> <% Class. for. Name("sun. jdbc. odbc. Jdbc. Odbc. Driver"); Connection cn = Driver. Manager. get. Connection("jdbc: odbc: People. DB", ""); Statement st = cn. create. Statement(); Result. Set r = st. execute. Query("SELECT * FROM Person; "); String html = ""; while(r. next()){ html += r. get. String("Surname"); } Move br tag inside loop. cn. close(); Which bit of code pulls data %> from database? <!DOCTYPE html> <head><title></head> <body> <%=html%> </body> </html> Mark Dixon Page 26

Embedding html in java <%@page import="java. sql. *"%> <%@page content. Type="text/html"%> <% Class. for.

Embedding html in java <%@page import="java. sql. *"%> <%@page content. Type="text/html"%> <% Class. for. Name("sun. jdbc. odbc. Jdbc. Odbc. Driver"); Connection cn = Driver. Manager. get. Connection("jdbc: odbc: People. DB", ""); Statement st = cn. create. Statement(); Move br tag inside loop Result. Set r = st. execute. Query("SELECT * FROM Person; "); after field data String html = ""; while(r. next()){ html += r. get. String("Surname") + " "; } cn. close(); %> <!DOCTYPE html> <head><title></head> <body> <%=html%> </body> </html> Mark Dixon Page 27

Embedding html in java • use view source – see what html has been

Embedding html in java • use view source – see what html has been created: Mark Dixon Page 28

Embedding html in java • html must be string (inside double quotes) • follows

Embedding html in java • html must be string (inside double quotes) • follows normal pattern for expressions: data s = s + "<b>" + r. get. String("Name") + "</b>" operator Mark Dixon operator Page 29

Embedding html in java (errors) s = s + "<i>" r. get. String("Gender") +

Embedding html in java (errors) s = s + "<i>" r. get. String("Gender") + "</i>" missing operator s = s + r. get. String("Height") + </i>" missing double quote s = s + <ul> + r. get. String("Height") html tag must be inside double quotes s = s + "<ul>" + r. get. String("<b>Height") looks for field in database called <b>Height Mark Dixon Page 30

Questions: HTML in java • Are these correct (assume variables and fields exist)? g

Questions: HTML in java • Are these correct (assume variables and fields exist)? g = g + r. get. String("Surname ") h = h + "<ol>" rs. get. String("Width") a = "<p>" + a + "</p>" html = html + "<img src=face. gif />" h = <table> + h + "</table>" Mark Dixon Page 31

Example: People v 2 • Display Surname of Male people: <%@page import="java. sql. *"%>

Example: People v 2 • Display Surname of Male people: <%@page import="java. sql. *"%> <%@page content. Type="text/html"%> <% Class. for. Name("sun. jdbc. odbc. Jdbc. Odbc. Driver"); Connection cn = Driver. Manager. get. Connection("jdbc: odbc: People. DB", ""); Statement st = cn. create. Statement(); Result. Set r = st. execute. Query("SELECT * FROM Person; "); String html = ""; while(r. next()){ if (r. get. Boolean("Gender")){ html += r. get. String("Surname") + " "; } } cn. close(); %> Mark Dixon Page 32

Example: People v 3 • Display Surname of Male people: <%@page import="java. sql. *"%>

Example: People v 3 • Display Surname of Male people: <%@page import="java. sql. *"%> <%@page content. Type="text/html"%> <% Class. for. Name("sun. jdbc. odbc. Jdbc. Odbc. Driver"); Connection cn = Driver. Manager. get. Connection("jdbc: odbc: People. DB", ""); Statement st = cn. create. Statement(); Result. Set r = st. execute. Query("SELECT * FROM Person WHERE Gender = TRUE; "); String html = ""; while(r. next()){ html += r. get. String("Surname") + " "; } cn. close(); %> SQL statement Mark Dixon Page 33

SQL: Queries • main purpose of databases: – get information back out: searching •

SQL: Queries • main purpose of databases: – get information back out: searching • Structured Query Language – dedicated to interacting with databases • 3 rd Generation Language (such as VB, C++) – code describes how to do task • 4 th Generation Language (such as SQL) – code describes what to do (not how to do it) Mark Dixon Page 34

SQL: SELECT statement • SELECT statement – used to get data – can be

SQL: SELECT statement • SELECT statement – used to get data – can be embedded in VB, via rs. Open: rs. Open("Person", cs) rs. Open("SELECT * FROM [Person]", cs) all fields Mark Dixon Page 35

SQL: WHERE & ORDER BY • WHERE clause – used to restrict data SELECT

SQL: WHERE & ORDER BY • WHERE clause – used to restrict data SELECT * FROM [People] WHERE [age]>=18; • ORDER BY clause – used to change order of data SELECT * FROM [People] ORDER BY [Surname]; Mark Dixon Page 36

SQL: strings (text data) • Possible confusion: SELECT * FROM Person WHERE Surname =

SQL: strings (text data) • Possible confusion: SELECT * FROM Person WHERE Surname = Smith this will look for field called Smith - gives error need single (SQL) quotes to signify literal text SELECT * FROM Person WHERE Surname = 'Smith' Mark Dixon Page 37

SQL & MS access queries • MS Access – Queries: select data from database

SQL & MS access queries • MS Access – Queries: select data from database – really SQL select statements – can use queries to test SQL code MS Access: People. mdb Mark Dixon Page 38

Questions: SQL • Create an SQL statement to extract Track Title of records by

Questions: SQL • Create an SQL statement to extract Track Title of records by Aerosmith Track Title Artist Name Country Paranoid Black Sabbath UK Falling in Love Aerosmith US Pink Aerosmith US Love in an Elevator Aerosmith US Smooth Criminal Alien Ant Farm US Meaning of Life Disturbed US The Game Disturbed US Voices Disturbed US Down with the Sickness Disturbed US SELECT [Track Title] FROM Track WHERE [Artist Name] = 'Aerosmith'; MS Access: Music. mdb Mark Dixon Page 39

Questions: SQL • Create an SQL statement to extract all fields of songs by

Questions: SQL • Create an SQL statement to extract all fields of songs by Disturbed, ordered by track name Track Title Artist Name Country Paranoid Black Sabbath UK Falling in Love Aerosmith US Pink Aerosmith US Love in an Elevator Aerosmith US Smooth Criminal Alien Ant Farm US Meaning of Life Disturbed US The Game Disturbed US Voices Disturbed US Down with the Sickness Disturbed US SELECT * FROM Track WHERE [Artist Name] = 'Disturbed' ORDER BY [Track Title]; MS Access: Music. mdb Mark Dixon Page 40

Example: People v 4 • User controls what is displayed: Mark Dixon Page 41

Example: People v 4 • User controls what is displayed: Mark Dixon Page 41

SQL: DISTINCT records Artist Name SELECT [Artist Name] FROM [Track]; Black Sabbath Aerosmith Alien

SQL: DISTINCT records Artist Name SELECT [Artist Name] FROM [Track]; Black Sabbath Aerosmith Alien Ant Farm Disturbed SELECT DISTINCT [Artist Name] FROM [Track]; Artist Name Black Sabbath Aerosmith Alien Ant Farm Disturbed Mark Dixon Page 42

Tutorial Exercise: People • • • Task 1: Create your own People database: –

Tutorial Exercise: People • • • Task 1: Create your own People database: – Open MS Access – Create a new database file – Create a new table – Create fields – Enter data Task 2: Get the People v 1 example (from the lecture) working. Task 3: Modify your page so that it displays phone number as well as the person's name. Task 4: Modify your page so that it displays the data in an html table. Task 5: Modify your page so that records for males are displayed in blue, and records for females are displayed in red. Task 6: Modify your page as per version 4. You will need to: – Add a form to the page, and three submit buttons – In your code, detect when a button has been pressed (have a look at previous weeks) Task 7: Modify your page so that the user can type a letter, and only names starting with that letter are displayed. Task 8: Modify your page so that the user can type a series of numerical digits and only phone numbers containing those digits are displayed. Task 9: Modify your code so that the user can order the data by surname, or email address (You may want to use a Query String) Mark Dixon Page 43