Transcript Advanced Java Class

Advanced Java Class
Java Database Connectivity
(JDBC)
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
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.
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
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;
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;
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];
Common “\” Commands
• \? Shows all “\” commands
• \h shows help menu
• \h COMMAND_NAME shows help for a
specific command
• \q quits psql program
Driver Types
1. JDBC-ODBC bridge (inefficient)
2. Most efficient driver if all classes using db are
on the db host
3. Most efficient driver if not all classes using db
are on the db host
4. 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.
Basic use of java.sql
1.
2.
3.
4.
5.
6.
Load driver class
Get connection
Get statement
Ask statement to execute sql string
Process results if needed
Close Statement and Connection
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]
Execution of SQL by Statement
•
int Statement.executeUpdate(String sql);
–
–
•
ResultSet Statement.executeQuery(String sql);
–
–
•
Returns number rows affected
Good for INSERT, UPDATE, and DELETE
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.
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()
Advanced Efficiency Options
1.
Prepared Statements [use if many sql statements vary
only in the literal values] – SEE NEXT SLIDE
2.
Stored Procedures
–
–
–
3.
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
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)
Good Database Programming
•
•
•
•
Program layers (i.e. Model-ViewPersistence)
Define mapping between Java Objects
and Data Elements
Utilize optimizations and special powers
of db
Program transactions
Program Layers (and not just in JDBC)
•
See p. 613, figure 9-1
•
Conceptually clearer
•
Layers of security
•
Scalability
•
Abstraction of implementations
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)
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.
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]
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
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.