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.policypolicyfilename
• It will look for a default file in
  java.home/lib/security/java.policy
• Or you can supply your own
• java Djava.security.policypolicyfilename
• 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 ltdatabase_namegt
• 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 cant 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 its 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.