Data Java ECommerce Martin Cooke 2003 Plan Issues

Data Java E-Commerce © Martin Cooke, 2003

Plan • • Issues in data-handling Relational database and SQL refresher XML vs relational databases Next lecture: Java database connectivity API (JDBC) 6/7/2021 Java E-Commerce © Martin Cooke, 2003 2

Issues in data handling on the web Java E-Commerce © Martin Cooke, 2003

persistent? portable? structure? available? concurrent access? secure? 6/7/2021 transactionally safe? Java E-Commerce © Martin Cooke, 2003 4

persistent? portable? structure? available? concurrent access? secure? 6/7/2021 transactionally safe? • Granting immortality to your data instances • Persistence = data living beyond the immediate lifecycle of the processes manipulating it • A relational database is the primary tool for persistence, and has been for decades • Can be handled automatically using an appropriate ‘container’ (see EJBs) Java E-Commerce © Martin Cooke, 2003 5

persistent? portable? structure? Free text Most of the web Tabular Vast amounts of existing data (forced into tabular form? ) Treestructured Marked up content eg XML Tedious to handle relationally Arbitrary object Images, sound files Tricky to handle relationally Object-relational or object database available? concurrent access? secure? 6/7/2021 transactionally safe? Java E-Commerce © Martin Cooke, 2003 6

persistent? portable? structure? Free text Most of the web Tabular Vast amounts of existing data (forced into tabular form? ) Treestructured Marked up content eg XML Tedious to handle relationally Arbitrary object Images, sound files Tricky to handle relationally Object-relational or object database available? concurrent access? secure? transactionally safe? Our focus in this lecture is on tabular data 6/7/2021 Java E-Commerce © Martin Cooke, 2003 7

persistent? portable? structure? available? concurrent access? secure? 6/7/2021 transactionally safe? • Vast volumes of data must be … searched rapidly … available 24 x 7 (eg credit card checking) … accessible by many concurrent users • Rely on optimised database engines which have been honed over the years Java E-Commerce © Martin Cooke, 2003 8

persistent? portable? structure? available? concurrent access? secure? 6/7/2021 transactionally safe? Modification • Dirty reads and writes • Pessimistic vs optimistic concurrency Efficiency • How to manage multiple connections? – Expensive to create – Limited in number for typical databases Java E-Commerce © Martin Cooke, 2003 9

persistent? portable? structure? available? concurrent access? secure? transactionally safe? • To maintain consistency, it often makes sense to carry out a group of changes (a transaction) to data – credit card db and order db • If one statement in the transaction fails, data can be left in an inconsistent state • must be able to unwind (rollback) the transaction 6/7/2021 Java E-Commerce © Martin Cooke, 2003 10

persistent? portable? structure? available? concurrent access? secure? 6/7/2021 transactionally safe? • Just how secure are files left lying around the place? • Again, rely on existing secure solution in relational database • Introduces its own layer of authentication Java E-Commerce © Martin Cooke, 2003 11

persistent? portable? structure? • Java may be portable, but what about its dependence on existing databases? available? • Ideally, no vendor tie-in concurrent access? secure? transactionally safe? • avoid legacy data problems in future • Tricky in practice since industry standard (SQL) is not as standard as it seems 6/7/2021 Java E-Commerce © Martin Cooke, 2003 12

Relational database and SQL refresher Java E-Commerce © Martin Cooke, 2003

(Relational) data model student module lecturer 6/7/2021 Java E-Commerce © Martin Cooke, 2003 14

id name 1 Java 2 Databases … student id name SSID 1 B Black 12456541 2 J Brown 97625516 … 6/7/2021 stud/mod module sid mid 1 1 1 2 2 1 Java E-Commerce © Martin Cooke, 2003 lecturer 15

SQL CREATE command student id name SSID 1 B Black 12456541 2 J Brown 97625516 stud/mod module lecturer … CREATE TABLE student ( id INT, NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR(4), TEXT, SSID INT DEFAULT 0 ) 6/7/2021 Java E-Commerce © Martin Cooke, 2003 16

SELECT statement id name 1 Java 2 Databases … student id name SSID 1 B Black 12456541 2 J Brown 97625516 … stud/mod module sid mid 1 1 1 2 2 1 lecturer SELECT module. name FROM module, student, stud. Mod SELECT name FROM student WHERE module. id = mid WHERE id = 2; AND student. name = ‘B Black’ AND student. id = stud. Mod. sid; 6/7/2021 Java E-Commerce © Martin Cooke, 2003 17

CREATE and INSERT CREATE TABLE student ( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, SSID INT DEFAULT 0 ); INSERT INTO student VALUES(1, 'Joe Bloggs', 12345678); INSERT INTO student VALUES(20, 'Bob Black', null); INSERT INTO student VALUES(4, 'Gill Brown', 23267656); 6/7/2021 Java E-Commerce © Martin Cooke, 2003 18

SELECT * FROM student; 6/7/2021 Java E-Commerce © Martin Cooke, 2003 19

UPDATE student SET name = ‘Gill Bloggs’ WHERE id = 4; 6/7/2021 Java E-Commerce © Martin Cooke, 2003 20

JOIN Find all modules taken by Bob Black 6/7/2021 Java E-Commerce © Martin Cooke, 2003 21

Other SQL DELETE 6/7/2021 Deletes rows from a table DELETE FROM student WHERE name=‘fred’; Java E-Commerce © Martin Cooke, 2003 22

Other SQL DELETE Deletes rows from a table DELETE FROM student WHERE name=‘fred’; DROP Permanently remove database or table from the system 6/7/2021 DROP DATABASE studs; DROP TABLE student; Java E-Commerce © Martin Cooke, 2003 23

Other SQL DELETE Deletes rows from a table DELETE FROM student WHERE name=‘fred’; DROP Permanently remove database or table from the system DROP DATABASE studs; DROP TABLE student; DESCRIBE Give information about a table or column DESCRIBE student; 6/7/2021 Java E-Commerce © Martin Cooke, 2003 24

Other SQL DELETE Deletes rows from a table DELETE FROM student WHERE name=‘fred’; DROP Permanently remove database or table from the system DROP DATABASE studs; DROP TABLE student; DESCRIBE Give information about a table or column DESCRIBE student; SHOW Display information about system SHOW DATABASES SHOW TABLES [FROM db] 6/7/2021 Java E-Commerce © Martin Cooke, 2003 25

Datatypes SQL 2 • BIT, TINYINT, SMALLINT, INTEGER, BIGINT, REAL, FLOAT, DOUBLE, DECIMAL, NUMERIC, CHAR, VARCHAR, LONGVARCHAR, DATE, TIMESTAMP, BINARY, VARBINARY, LONGVARBINARY • emphasis on – Primitive types – Storage size SQL 3 • BLOB, CLOB, ARRAY, REF, STRUCT 6/7/2021 Java E-Commerce © Martin Cooke, 2003 26

More on datatypes • Different database engines support their own types internally and loosely translate to an SQL 2 type • Big mismatch between rich Java type possibilities and SQL datatypes • Care needed, though JDBC helps (later) 6/7/2021 Java E-Commerce © Martin Cooke, 2003 27

Relational data vs XML Choices and integrations Java E-Commerce © Martin Cooke, 2003

Persistence is not the issue • Need to consider questions of data representation and persistence separately • Can store/retrieve XML as a string on a relational database 6/7/2021 Java E-Commerce © Martin Cooke, 2003 29

Which to use? pros cons XML Good for complex data Storage-hungry Reasonably good fit to Slow to build and search object-based software documents Relational data Good for tabular data Optimised for memory and speed, especially for multi-factor searches 6/7/2021 Tricky for tree-structured data Very tricky for arbitrary objects: needs transformation Java E-Commerce © Martin Cooke, 2003 30

Complementary aspects • Store XML together with some search terms • Convert SQL to XML • Represent SQL query in XML 6/7/2021 Java E-Commerce © Martin Cooke, 2003 31

Resources Java E-Commerce © Martin Cooke, 2003

Book • Yarger, Reese and King (1999) My. SQL and m. SQL, O’Reilly, 1 -56592 -434 -7 6/7/2021 Java E-Commerce © Martin Cooke, 2003 33

Online documents An online SQL interpreter http: //www. dcs. napier. ac. uk/~andrew/sql/ 6/7/2021 Java E-Commerce © Martin Cooke, 2003 34