Databases and Java

This lecture provides an introduction to databases and their use with the Java language.

[Useful resource: Andersson DB book and also Writing JDBC Applications with MySQL]

Question: what's wrong with files for storing data such as (download logs from jGuru):

20020901_00.33.12 201927  j@infonomicon.com true
20020901_01.57.16 23349  m@web.de false
20020901_02.51.58 201927  a@attbi.com false
...

Can use grep / awk to find stuff and can easily read from shell. But, is hard to pull back into a program and can't do anything but simple queries. For example, no way to do merged queries.

Database concepts

Accessing db from cmd-line

Command line tool is SQL shell:

mysql -p -h sql parrt

Which tries to connect to the mysql database on host sql using database called parrt.

Tables

Tables are just rows of data. Each row has multiple columns with different types. For example, here is a table called consumption:

CREATE TABLE consumption (
  programmer VARCHAR(100) NOT NULL,
  day ENUM('Mon','Tue','Wed','Thu','Fri') NOT NULL,
  cups INTEGER
);
Programmer Day Cups
Ito Mon 1
Chu Mon 2
Parr Tue 8

Long

Tue 2
Chen Tue 3
Chen Wed 2
Wiryoma Thu 3
Tjun Thu 1
Witchel Fri 9
Zheng Fri 3
Yang Fri 4

Note that you can have duplicates but it's not a good idea here.

Do a show tables:

mysql> show tables;
+----------------+
| Tables_in_demo |
+----------------+
| consumption    |
+----------------+

and can ask for actual table back:

mysql> show create table consumption;
...
consumption | CREATE TABLE `consumption` (
  `programmer` varchar(100) NOT NULL default '',
  `day` enum('Mon','Tue','Wed','Thu','Fri') NOT NULL default 'Mon',
  `cups` int(11) default NULL
) TYPE=MyISAM
...

Inserting and updating data

INSERT INTO consumption (programmer,day,cups) VALUES ('Ito','Mon',1);
INSERT INTO consumption VALUES ('Chu','Mon',2);
INSERT INTO consumption VALUES ('Parr','Tue',8);

simple queries

mysql> select * from consumption;
+------------+-----+------+
| programmer | day | cups |
+------------+-----+------+
| Ito        | Mon |    1 |
| Chu        | Mon |    2 |
| Parr       | Tue |    8 |
+------------+-----+------+
mysql> SELECT cups FROM consumption where programmer='Parr';
+------+
| cups |
+------+
|    8 |
+------+
mysql> SELECT SUM(cups) FROM consumption WHERE day='Mon';
+-----------+
| SUM(cups) |
+-----------+
|         3 |
+-----------+

Column data types

Strings (TEXT type)

See http://www.mysql.com/doc/en/String_syntax.html but more importantly http://www.mysql.com/doc/en/String_types.html for type info.

You can use TEXT type for large strings. VARCHAR is good for up to 255 in MySQL. Note that this stuff is always database-dependent! CHAR pads with spaces. TEXT is case insensitive in MySQL.

ENUM

Non-standard type but very useful. (mysql doc: enumerated explicitly in the column specification at table creation time). For example,

create table resource_category (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    category VARCHAR NOT NULL,    
    PRIMARY KEY (id)
);

then you must fill at database creation time. Or with enum:

create table resource_category (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    category ENUM('course', 'software', 'article') NOT NULL,
    PRIMARY KEY (id)
);

NULL

Used to store "nothing" in a column. For example, if someone has no phone number, the column value for that row should be NULL.

INSERT INTO Person (phone) VALUES (NULL);

DATETIME

http://www.mysql.com/doc/en/Date_and_time_types.html

Basically "YYYY-MM-DD hh:mm:ss"; e.g., "2002-09-29 00:00:00". The zero values you might want to use are:

DATETIME '0000-00-00 00:00:00'
DATE	'0000-00-00'

If you create a table like:

create table users (
  created DATETIME not null,
  name VARCHAR(50)
);

then you can insert values like this:

insert into users values ('2005-10-26', 'Ter');

Relationships and foreign keys

Storing a row of data per key is easy. How do you handle multiple values though for each row? For example, multiple phone numbers per person or all the FAQ entries associated with a FAQ descriptor? Have to use another table and a foreign key.

First, look at storing multiple phone numbers per user:

create table users (
	id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
	name VARCHAR(50),
	PRIMARY KEY (id)
);

create table phones (
	id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
	phone VARCHAR(20),
	userid SMALLINT UNSIGNED NOT NULL REFERENCES users(id),
	PRIMARY KEY (id)
);

Graphically:

Each phones table entry points back to the user that "owns" this record. Notice that the user does not point at the phones it has; rather, the phones point back at their "owners".

Then insert some data:

insert into users (name) values ('Ter');
insert into users (name) values ('Tom');

insert into phones (phone,userid) values ('x5707',1);
insert into phones (phone,userid) values ('x6530',1);
insert into phones (phone,userid) values ('555-4321',2);
insert into phones (phone,userid) values ('555-1212',2);
insert into phones (phone,userid) values ('911',2);

MySQL will show the following tables:

mysql> select * from users;
+----+------+
| id | name |
+----+------+
|  1 | Ter  | 
|  2 | Tom  | 
+----+------+
2 rows in set (0.00 sec)

mysql> select * from phones;
+----+----------+--------+
| id | phone    | userid |
+----+----------+--------+
|  1 | x5707    |      1 | 
|  2 | x6530    |      1 | 
|  3 | 555-4321 |      2 | 
|  4 | 555-1212 |      2 | 
|  5 | 911      |      2 | 
+----+----------+--------+
5 rows in set (0.00 sec)

So, how can you get the list of phones per user? You might be tempted to try the following query to get data from multiple tables:

mysql> select name,phone from users,phones;
+------+----------+
| name | phone    |
+------+----------+
| Ter  | x5707    | 
| Tom  | x5707    | 
| Ter  | x6530    | 
| Tom  | x6530    | 
| Ter  | 555-4321 | 
| Tom  | 555-4321 | 
| Ter  | 555-1212 | 
| Tom  | 555-1212 | 
| Ter  | 911      | 
| Tom  | 911      | 
+------+----------+

Ooops! Gives outer product, but want inner product.

mysql> select name,phone from users inner join phones on users.id=phones.userid; 
+------+----------+
| name | phone    |
+------+----------+
| Ter  | x5707    | 
| Ter  | x6530    | 
| Tom  | 555-4321 | 
| Tom  | 555-1212 | 
| Tom  | 911      | 
+------+----------+
5 rows in set (0.00 sec)

And then can grab phones for just one user:

mysql> select name,phone from users inner join phones on users.id=phones.userid where name='Tom';
+------+----------+
| name | phone    |
+------+----------+
| Tom  | 555-4321 | 
| Tom  | 555-1212 | 
| Tom  | 911      | 
+------+----------+
3 rows in set (0.00 sec)

Here's another variation where a Person has multiple roles and vacation days.

As a 3rd example, consider building tables to manage FAQ entries. You want to define the FAQs (Dogs, Cats, ...) and then have entries within those FAQs.

CREATE TABLE FAQ (
    id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    name CHAR(60) NOT NULL, -- e.g., Dogs, Birds, Cats
    PRIMARY KEY (id)
);

CREATE TABLE ENTRY (
    id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    created DATETIME NOT NULL,
    question VARCHAR(255) NOT NULL,
    answer TEXT NOT NULL,
    faqid SMALLINT UNSIGNED NOT NULL REFERENCES FAQ(id),
    PRIMARY KEY (id)
);

INSERT INTO FAQ (name) VALUES ('Dogs');
INSERT INTO FAQ (name) VALUES ('Cats');
INSERT INTO ENTRY (created,question,answer,faqid)
  VALUES ('2002-09-30','What is a terrier?','A small dog',1);
INSERT INTO ENTRY (created,question,answer,faqid)
  VALUES ('2002-09-30','Why are some dogs smaller than cats?','a mystery!',1);
INSERT INTO ENTRY (created,question,answer,faqid)
  VALUES ('2002-09-30','Why are cats better than dogs?','they do not bark',2);

Then you might do queries like:

mysql> select * from FAQ;
+----+------+
| id | name |
+----+------+
|  1 | Dogs |
|  2 | Cats |
+----+------+
mysql> select * from ENTRY;
+----+---------------------+--------------------------------------+------------------+-------+
| id | created             | question                             | answer           | faqid |
+----+---------------------+--------------------------------------+------------------+-------+
|  1 | 2002-09-30 00:00:00 | What is a terrier?                   | A small dog      |     1 |
|  2 | 2002-09-30 00:00:00 | Why are some dogs smaller than cats? | a mystery!       |     1 |
|  3 | 2002-09-30 00:00:00 | Why are cats better than dogs?       | they do not bark |     2 |
+----+---------------------+--------------------------------------+------------------+-------+

To JOIN the tables to find all "Dogs" FAQ entries do this:

mysql> SELECT question from FAQ,ENTRY
    ->   WHERE ENTRY.faqid=FAQ.id AND
    ->         FAQ.name='Dogs';
+--------------------------------------+
| question                             |
+--------------------------------------+
| What is a terrier?                   |
| Why are some dogs smaller than cats? |
+--------------------------------------+

Same as

SELECT question from FAQ INNER JOIN ENTRY ON ENTRY.faqid=FAQ.id
  WHERE FAQ.name='Dogs';

Add more data then do a full join.

INSERT INTO ENTRY (created,question,answer,faqid)
  VALUES ('2002-09-30','What is a bulldog?','An ugly dog',1);
INSERT INTO ENTRY (created,question,answer,faqid)
  VALUES ('2002-09-30','Are cats Human?','No they just act like it',2);

Just referring to comma-separated tables does a JOIN that duplicates data like a cross product in math:

mysql> select * from FAQ,ENTRY;
+----+------+----+---------------------+--------------------------------------+--------------------------+-------+
| id | name | id | created             | question       | answer                   | faqid |
+----+------+----+---------------------+--------------------------------------+--------------------------+-------+
|  1 | Dogs |  1 | 2002-09-30 00:00:00 | What is a terrier?        | A small dog              |     1 |
|  2 | Cats |  1 | 2002-09-30 00:00:00 | What is a terrier?       | A small dog              |     1 |
|  1 | Dogs |  2 | 2002-09-30 00:00:00 | Why are some dogs smaller than cats? | a mystery!               |     1 |
|  2 | Cats |  2 | 2002-09-30 00:00:00 | Why are some dogs smaller than cats? | a mystery!               |     1 |
|  1 | Dogs |  3 | 2002-09-30 00:00:00 | Why are cats better than dogs?       | they do not bark         |     2 |
|  2 | Cats |  3 | 2002-09-30 00:00:00 | Why are cats better than dogs?       | they do not bark         |     2 |
|  1 | Dogs |  4 | 2002-09-30 00:00:00 | What is a bulldog?        | An ugly dog              |     1 |
|  2 | Cats |  4 | 2002-09-30 00:00:00 | What is a bulldog?       | An ugly dog              |     1 |
|  1 | Dogs |  5 | 2002-09-30 00:00:00 | Are cats Human?       | No they just act like it |     2 |
|  2 | Cats |  5 | 2002-09-30 00:00:00 | Are cats Human?       | No they just act like it |     2 |
+----+------+----+---------------------+--------------------------------------+--------------------------+-------+
10 rows in set (0.00 sec)

Every row of FAQ is paired up with every row of ENTRY so there are n*m rows in the join for |FAQ|=n and |ENTRY|=m.

NATURAL JOIN matches up id's and combines tables but it is not always what you want (matches up id column):

mysql> select * from FAQ NATURAL JOIN ENTRY;
+----+------+---------------------+--------------------------------------+-------------+-------+
| id | name | created             | question  | answer      | faqid |
+----+------+---------------------+--------------------------------------+-------------+-------+
|  1 | Dogs | 2002-09-30 00:00:00 | What is a terrier?  | A small dog |     1 |
|  2 | Cats | 2002-09-30 00:00:00 | Why are some dogs smaller than cats? | a mystery!  |     1 |
+----+------+---------------------+--------------------------------------+-------------+-------+
2 rows in set (0.00 sec)

What you want is:

mysql> select FAQ.id,name,faqid,ENTRY.id,question,answer
  from FAQ INNER JOIN ENTRY on FAQ.id=ENTRY.faqid;
+----+------+-------+----+--------------------------------------+--------------------------+
| id | name | faqid | id | question                             | answer                   |
+----+------+-------+----+--------------------------------------+--------------------------+
|  1 | Dogs |     1 |  1 | What is a terrier?                   | A small dog              |
|  1 | Dogs |     1 |  2 | Why are some dogs smaller than cats? | a mystery!               |
|  2 | Cats |     2 |  3 | Why are cats better than dogs?       | they do not bark         |
|  1 | Dogs |     1 |  4 | What is a bulldog?                   | An ugly dog              |
|  2 | Cats |     2 |  5 | Are cats Human?                      | No they just act like it |
+----+------+-------+----+--------------------------------------+--------------------------+

Indices

Indices are used to prevent linear walk of data. Makes find rows and sorting much faster. Multi-column indices make find data with

SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

much faster as it can grab the data from the index. If two single-column indices, finds row(s) and fetches data.

Create an index:

CREATE [UNIQUE|FULLTEXT] INDEX index_name
       ON tbl_name (col_name[(length)],... )

such as:

create index person_name_index on person (name);

or a multi-column index.

create index person_name_email_index on person (name,email);

Can use portion of column to reduce mem usage w/o much loss of speed.

MySQL issues

Auto increment columns

Often you want to know the ID of the row you just entered. MySQL provides the AUTO_INCREMENT modifier so you don't have to track your own unique IDs (via db sequences usually)--you just insert rows. But, you need to know what that ID is to fill in your Java object in memory usually. Assume following table:

// from mysql doc
CREATE TABLE person (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name CHAR(60) NOT NULL,
    PRIMARY KEY (id)
);

If you cast one of the JDBC objects to a specific mysql object, it will tell you the id used to do the insert.

You can also use another query. See the mysql doc for SELECT LAST_INSERT_ID();. Basically, if you do

SELECT LAST_INSERT_ID();

it will return a result set with the ID such as:

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)
Storing large items

A BLOB is a binary large object. You could use it to store email attachments or images etc...

How can I insert images into a Mysql database?

Transactions and locking

Transaction: do operation then commit concept. Very useful for multi-stage operations to ensure consistent data in database. For example, update customer account and delete from inventory in one operation: must be atomic like with threads updating Java memory. If any part of a transaction fails, then you can rollback changes. If all is well, you can commit. Transactions in MySQL start with BEGIN; and end with COMMIT or ROLLBACK.

If you LOCK a table, MySQL allows one write at a time (atomically). All reads wait until all updates are done then reads.

From mysql doc:

mysql> LOCK TABLES trans READ, customer WRITE;
mysql> SELECT SUM(value) FROM trans WHERE customer_id=some_id;
mysql> UPDATE customer SET total_value=sum_from_previous_statement
    ->        WHERE customer_id=some_id;
mysql> UNLOCK TABLES;

Mysql doc: "Without LOCK TABLES, there is a chance that another thread might insert a new row in the trans table between execution of the SELECT and UPDATE statements."

An additional problem. With some applications you need to support extended locking. For example, to update a person's user record, you must read then wait a long time for them to hit submit and then must update the database. You do not want someone reading the same record and updating it in between. This is the classic race condition associated with "test and set" operations. You would need to lock tables to ensure proper atomic update and do application locks so duplication operations don't start up.

"Cooperative" Application and object level locks

MySQL provides a nice mechanism for handling the 'edit person record' problem. Use

SELECT GET_LOCK("lock_faq_77",10); -- timeout=10seconds
...
SELECT RELEASE_LOCK("lock_faq_77");

Note you can halt commencement of duplication operations by checking lock:

SELECT IS_FREE_LOCK("lock_faq_77");

Mysql doc seems to imply only one lock per client connection. This stinks. One way around it is to add a lock id and lock_created column to each row. Here is some good info on that: http://www.bitmechanic.com/mail-archives/mysql/current/1000.html

To lock use these queries:

SELECT lockid,whenlocked FROM yourtable WHERE ID=IDtoLock;
UPDATE yourtable SET LOCKID=aLockIDManagedByYourApp,
  WHENLOCKED='currentTimestamp'
  WHERE EID=IDtoLock;

To unlock just set the lock id to NULL. Your application must manage the lock ids and associate a lock id with a user in their session data. They must be able to perform multiple operations.

Using databases from Java

connecting

MySQL doc: http://mmmysql.sourceforge.net/doc/mm.doc/c106.htm

  1. Load a JDBC driver for desired data source. A data source is any database you have access to through a JDBC driver. In the case of ODBC, the data source must be registered with the ODBC driver manager from the Control Panel. With any other driver you setup the access via vendor-specific procedures.
  2. Connect to a data source. Once your driver is loaded, you can connect to the specific data source through a URL of the form jdbc:subprotocol:name.

  1. Execute SQL statements. You use SQL commands from Java to get results and then use Java to display or manipulate that data.

  1. Close the connection. When you are done, you close the connection to the database, freeing the resources.

Embodied by:

import java.sql.*;

public class Test {
    // Go to http://www.mysql.com/doc/en/Java.html to get JDBC driver
    // Or directly to get the driver here:
    // http://www.mysql.com/downloads/api-jdbc-stable.html
    // or just see /home/public/cs601 for the jar:
    //   mysql-connector-java-3.0.9-stable-bin.jar
    public static void main(String[] args) throws SQLException {
        String username  = "parrt";
        String password  = "parrt";
        String db  = "parrt";
        try {
            // load driver
            Class.forName("com.mysql.jdbc.Driver").newInstance();
        }
        catch (Exception e) {
            System.err.println("Can't find driver");
            System.exit(1);
        }
        // format "jdbc:mysql://[hostname][:port]/[dbname]"
        String urlString = "jdbc:mysql://sql.cs.usfca.edu/"+db;
        Connection con = DriverManager.getConnection(urlString,
                                                     username,
                                                     password);

	// do your work here

        con.close();
    }
}

I compile with:

$ javac -classpath .:/home/public/cs601/mysql-connector-java-3.0.9-stable-bin.jar Test.java

executing SQL statements

First you must create a statement to use:

Statement stmt = con.createStatement();

select statements

ResultSet result = stmt.executeQuery (
  "SELECT programmer, cups " + 
  "FROM consumption ORDER BY cups DESC;");

System.out.println("Programmer\tCups");
while ( result.next() ) {
  String programmer = result.getString("programmer");
  int cups = result.getInt("cups");
  System.out.println(programmer+"\t"+cups);
}
stmt.close();

I execute with:

$ java -cp .:/home/public/cs601/mysql-connector-java-3.0.9-stable-bin.jar Test
Programmer      Cups
Parr    8
Chu     2
Ito     1

insert and update statements

Statement istmt = con.createStatement();
int n = istmt.executeUpdate(
          "INSERT INTO consumption VALUES ('Acedo','Fri',8)");
System.out.println("Inserted "+n+" records");

prepared statements

Normally used for efficiency, but MySQL doesn't pre-compile. Nonetheless there are some useful features. can use like a template, don't have to build string and also used to store big values like images. Don't have to escape special char.

PreparedStatement prep = con.prepareStatement(
    "INSERT into consumption (programmer,cups) values (?, ?)");
prep.setString(1, "Jim");
prep.setInt(2, 8);
if (prep.executeUpdate () != 1) {
    throw new Exception ("Bad Update");
}

Database connection pools

Fast way to talk to the database since connection is very expensive.

http://www.jguru.com/faq/view.jsp?EID=17020

Java Object to DB Mapping

Serialization?

Strings for properties example.

Standard tables

concept: Entities with attributes

Entities mapped to standard tables that "inherit" from:

CREATE TABLE RESOURCE (
        EID INTEGER NOT NULL,
        LOCKID INTEGER,
        WHENLOCKED TIMESTAMP,
        CREATED TIMESTAMP,
        MODIFIED TIMESTAMP,
        AUTHOR INTEGER,
...
);
EID LOCKID WHENLOCKED CREATED MODIFIED AUTHOR
... ... ... ... ... ...

Single-valued attributes

Just fields of table

CREATE TABLE FAQ (     
        EID INTEGER NOT NULL,
        LOCKID INTEGER,
        WHENLOCKED TIMESTAMP,
        CREATED TIMESTAMP,
        MODIFIED TIMESTAMP,
        AUTHOR INTEGER,
        QUESTION LONG VARCHAR,
        ANSWER LONG VARCHAR,     
...
);
EID ... AUTHOR QUESTION ANSWER
n ... a What is JDBC? ...

Multi-valued attributes

Each attribute maps to separate table mapping EID<->attribute(s).

CREATE TABLE FAQ_TOPIC (
        EID INTEGER NOT NULL,
        TOPIC INTEGER NOT NULL,
        PRIMARY KEY (EID,TOPIC)
);
EID TOPIC
... ...

Indices

Index for every column of entity tables. Dual column index for each multi-valued attribute table.

Database manager

Single thing to change, portability, simplicity, replace with proxy/stub, logging, does the OO <-> DB mapping.

Can do standard queries.

Taking Advantage of constraints

Caching of data

Save answer to each query for a while?

Cache whole database in memory and then reload occasionally?

Caching of pages

Don't recompute pages that don't change much. Need to refresh intelligently, however.