Data Java ECommerce Martin Cooke 2003 Plan Issues
- Slides: 34
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) 9/19/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? 9/19/2021 transactionally safe? Java E-Commerce © Martin Cooke, 2003 4
persistent? portable? structure? available? concurrent access? secure? 9/19/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? 9/19/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 9/19/2021 Java E-Commerce © Martin Cooke, 2003 7
persistent? portable? structure? available? concurrent access? secure? 9/19/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? 9/19/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 9/19/2021 Java E-Commerce © Martin Cooke, 2003 10
persistent? portable? structure? available? concurrent access? secure? 9/19/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 9/19/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 9/19/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 … 9/19/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 ) 9/19/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; 9/19/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); 9/19/2021 Java E-Commerce © Martin Cooke, 2003 18
SELECT * FROM student; 9/19/2021 Java E-Commerce © Martin Cooke, 2003 19
UPDATE student SET name = ‘Gill Bloggs’ WHERE id = 4; 9/19/2021 Java E-Commerce © Martin Cooke, 2003 20
JOIN Find all modules taken by Bob Black 9/19/2021 Java E-Commerce © Martin Cooke, 2003 21
Other SQL DELETE 9/19/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 9/19/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; 9/19/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] 9/19/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 9/19/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) 9/19/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 9/19/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 9/19/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 9/19/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 9/19/2021 Java E-Commerce © Martin Cooke, 2003 33
Online documents An online SQL interpreter http: //www. dcs. napier. ac. uk/~andrew/sql/ 9/19/2021 Java E-Commerce © Martin Cooke, 2003 34
- Cooke ratio formula
- Clive cooke
- Henrietta cooke
- Cooke triplet
- Guglielmo marconi ne icat etti
- Dr. joe massey
- Emer cooke efpia
- Ecommerce legal issues
- E commerce security and fraud issues and protections
- Java ecommerce
- Java ecommerce framework
- Ecommerce business plan template free
- Ecommerce business plan examples
- Wearewaes
- Lexical issues in java with example
- Concurrency issues in java
- Java import java.util.*
- Import java.util.*
- Import javax.swing.*
- How to import java.util.scanner
- Import java.io.file
- Java import java.util.*
- Import java.util.random;
- Import java.io.* in java
- Import java.util.*;
- Java import java.io.*
- Perbedaan swing dan awt
- Import java.awt.* import java.awt.event.*
- Java interpreter
- Ejb remote vs local
- Data link layer design issues
- Five key issues of data gathering
- Five key issues of data gathering
- Big data privacy issues in public social media
- Data link