Advanced Java Class

1. Advanced Java Class Java Database Connectivity (JDBC)

2. Question from yesterday • Please talk a little about “security.policy” • java –Djava.security.policy=policyfilename • It will look for a default file in java.home/lib/security/java.policy • Or you can supply your own: • java –Djava.security.policy=policyfilename • Example: my RMISecurity.policy file: grant { permission java.net.SocketPermission "*:1024-65535","connect,accept"; }; • Also, you can use the policytool program that comes with the JDK to write your own security file. • See java.security.Permissions class for more documentation

3. Policy for Assignment 1a • As written in an email yesterday, your WebCrawler will only be tested on html pages that are xhtml pages. • In other words, you may assume that the links will be well-formed.

4. PostGresQL Syntax • You need to know a little PostGresQL syntax to set up your DB and use JDBC • Types: see p. 623, figure 9-6 • Also in postgresQL: money, text (among others) • Documentation: http://www.postgresql.org • Look at \h and \? for command syntax • At the command line: • psql • pg_dump <database_name> • man psql

5. Common Table Commands • CREATE TABLE table_name ( column_name1 column_type1, column_name2 column_type2, etc.); • Can also specify a DEFAULT value, or other constraints like NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, etc. • \dt (shows tables) • \d table_name (describes that table) • DROP TABLE table_name; • ALTER TABLE table_name RENAME TO new_table_name;

6. Common Column Commands • ALTER TABLE table_name • ADD column_name column_type [constraints]; • ALTER column_name SET DEFAULT value; • ALTER column_name DROP DEFAULT; • RENAME old_column_name TO new_column_name;

7. Common Row Commands • INSERT INTO table_name values (42, 3, ‘foo’) • INSERT INTO table_name (col2_name, col3_name) values (3, ‘foo’); • UPDATE table_name SET col = expression [WHERE condition]; • DELETE FROM table_name [WHERE condition];

8. Common “\” Commands • \? Shows all “\” commands • \h shows help menu • \h COMMAND_NAME shows help for a specific command • \q quits psql program

9. Driver Types • JDBC-ODBC bridge (inefficient) • Most efficient driver if all classes using db are on the db host • Most efficient driver if not all classes using db are on the db host • pure Java, and therefore platform independent • A driver for your first project is linked from the assignment page. • For many databases, there may be only one choice, not all four.

10. Basic use of java.sql • Load driver class • Get connection • Get statement • Ask statement to execute sql string • Process results if needed • Close Statement and Connection

11. Optional arguments for getting Statement • Scrollable? • TYPE_FORWARD_ONLY [Default] (note error in text: TYPE_READ_ONLY) • TYPE_SCROLL_INSENSITIVE [ignores changes made to ResultSet by other code] • TYPE_SCROLL_SENSITIVE • Concurrency? • CONCUR_READ_ONLY [rs can’t change while processing it] • CONCUR_UPDATABLE [not allowed by some DB drivers]

12. Execution of SQL by Statement • int Statement.executeUpdate(String sql); • Returns number rows affected • Good for INSERT, UPDATE, and DELETE • ResultSet Statement.executeQuery(String sql); • Good for SELECT • Good if only one ResultSet is returned • boolean execute(String sql); • Returns true if a ResultSet(s) was returned • Good if more than one ResultSet might be returned • Very rare to do this.

13. Processing Result Sets • Move to appropriate row • beforeFirst(), next()) • Process values at that row • get values (methods are named by type) • update values [local], then updateRow() [db] • deleteRow() • insertRow()

14. Advanced Efficiency Options • Prepared Statements [use if many sql statements vary only in the literal values] – SEE NEXT SLIDE • Stored Procedures • Creating them varies from db to db – not covered in this class • Can use them in Java via CallableStatement • Can produce more than one ResultSet • Use fewer Connections • If single threaded program, open a Connection and keep it, rather than closing it and opening a new one constantly • Connection Pooling is provided by DataSource objects for J2EE

15. Prepared Statements – Very Useful! • PreparedStatement ps = Connection.prepareStatement( “INSERT INTO my_table (col_a, col_b) VALUES (?, ?)” ); • ps.setString(1, “foo”); ps.setInt(2, 512); • ps.executeUpdate() or ps.executeQuery(); • (note: indexing starts from 1)

16. Good Database Programming • Program layers (i.e. Model-View-Persistence) • Define mapping between Java Objects and Data Elements • Utilize optimizations and special powers of db • Program transactions

17. Program Layers (and not just in JDBC) • See p. 613, figure 9-1 • Conceptually clearer • Layers of security • Scalability • Abstraction of implementations

18. Define mapping between Java Objects and Data Elements • Relationships between Relational Database Concepts and Object Oriented Programming Concepts • See figure 9-2 • Mappings (see 9-4 and 9-5) • One-to-one (use same ids) • One-to-many (give each of the many a reference to the one, using a foreign key) • Many-to-many (make another table just for the mappings – this table will have 2 foreign keys)

19. Utilize optimizations and special powers of DB • serial “types” • DB can check for uniqueness of primary key • Foreign key constraints signal error for impossible values • Set up dependency rules for deletes and modifications (i.e. DB will automatically nix employee records when a department is deleted if the foreign key is defined with “cascade delete” • WARNING: if you depend on these too much, you may have difficulty switching Databases.

20. Program For Transactions • Goal: • Atomic [all or nothing] • Consistent [same each time you run it under same conditions] • Isolated [independent of other transactions] • (several possible levels of isolation) • Durable [permanent once commited]

21. Program For Transactions • Implementation • Connection.setAutoCommit(false) [true by default] • Connection.commit() or Connection.rollback() • Note: can also rollback to Savepoints within a transaction, as of Java 1.4

22. Group Database Task • Draw and Entity Relationship diagram (see example on board) to show the structure of a DB for an online shopping application. • Show Foreign and Primary Keys • Show column names, SQL types, any restraints • Classes in Application: • Customer: Has name and address. Has one or more accounts. • Account: has owner, balance, credit rating, value of last statement, and date of last payment. • Order: Knows what account it’s owned by. Also has date opened, date closed, status, and total value. Has one or more Items, and associates them with the quantity ordered. • Item: Has a part number, picture (image file), unit cost. • Inventory: list of the items available for sale. Associates each Item with a quantity in stock.