2008年度 生物情報科学実験I Java基礎

第4回 8月8日 応用編：データベースアプリケーション

リレーショナルデータベース

データベースって何？

データが含まれていれば、それはテキストファイルでも、画像ファイルでも、データベースと呼んでいいでしょう。iTunesでも音楽データを管理するのにデータベースを使っていますし、アーティスト-アルバム-楽曲というフォルダ構成も、データベースといってもいいかもしれません。

既にファイルの読み書きの方法を知っていればデータベースの作成は原理的に可能です。実際、世の中のデータベース管理システム(DBMS: Database Management System)は、突き詰めれば、ファイルの読み書きで実装されています。

ただし、ディスク上のファイルは一次元配列でしかありません。その一本の配列にのようにデータを書き込めば、データを壊すことなく保存でき、再び取り出せるようになるのか。また、新しいデータの追加できるようにデータの配置を管理するなど、単にディスクにデータを保存するといっても、さまざまな工夫が必要です。さらに、データの検索を早くするために、HashMap, TreeMapなどの索引構造を、ディスク上に作成する必要があります（索引のためのデータ構造としてはB+-treeを使うのが一般的です）。

ですから、単純なフォルダやファイルというよりも、コンピューターから高速にデータを検索、更新しやすい形で整理・保存されているものを、一般にデータベースということが多いようです。そして、データベースを作成、更新、検索するためのシステム、ソフトウェアをデータベース管理システム(DBMS)と言います。

リレーショナルデータベース

今回の演習で扱うデータベースは、リレーショナルデータベース（Relational Database, 関係データベース)というもので、表（テーブル)形式でデータを持つデータベースです。

staff table

id name email
1 Taro L. Saito xxx@xxx.xxxxx
2 Yoichiro Nakatani yyy@xxx.xxxxx
3 Jun Yoshimura zzz@xxx.xxxxx

lecture table

person_id name
1 情報生命科学演習
1 生物情報科学実習
2 生物情報科学実習
3 生物情報科学実習

このテーブルデータは、講義を担当しているstaffのリストと、それぞれが担当している講義lectureを表にしたものです。

このようなテーブルデータを作るために、リレーショナルデータベースシステムに SQL言語を用いて、上記のフォーマットのテーブルを作りなさい、行データを追加しなさいという指示を出すことになります。

SQL

SQL (エスキューエル。海外ではsequel(シークェル)と呼ぶ人が多い）は、問い合わせ言語(query language)と言われ、データベースからデータを取り出す命令を記述できます。

今回の演習ではSQLite http://www.sqlite.org/ を使うので、こちらのSQLの構文マニュアル(http://www.sqlite.org/lang.html )を参考にしてください。

テーブルの作成 (create table)

上記のテーブル形式のデータを作成するには、create table文を使います。 

create table staff (
  id integer primary key not null,
  name text,
  email text
);

create table文の書式は以下の通りです 

create table テーブル名 (
  (カラム名) (データ型名）(型の制約...)? ,
  ...
);

Javaのクラス定義の仕方と似ていますが、使えるデータ型の種類がデータベースシステム毎に決まっています。通常、いろいろなシステムに共通して使えるのは以下のデータ型です。 

integer   整数
real　　　浮動小数点数 
text　　　文字列
datetime  日時を表す型

id integerの後ろについているprimary key (主キー)という指定は、その列の値(id)は、重複してはいけない、ということを意味します。すなわち、テーブル中に同じidの値を持ったデータが存在してはいけない、という意味です。primary keyは、テーブル中から、一意に行を特定するために使われます。

not nullは、空っぽのデータをその列に入れてはいけないという指示です。

lectureテーブルを作るSQLは、以下のようになります。 

create table lecture (
  person_id integer not null,
  name text  
);

lectureテーブルでは、person_idは重複があってもOKです。

データの挿入　(insert)

テーブルを作成後、insert文を使い、データを挿入していきます。 

insert into staff values('1', 'Taro L. Saito', 'xxx@xxx.xxxxx');
insert into staff values('2', 'Yoichiro Nakatani', 'yyy@xxx.xxxxx');
insert into staff values('3', 'Jun Yoshimura', 'zzz@xxx.xxxxx');

insert into lecture values('1', '情報生命科学演習');
insert into lecture values('1', '生物情報科学演習');
insert into lecture values('2', '生物情報科学演習');
insert into lecture values('3', '生物情報科学演習');

insert文の書式は以下の通り 

insert into テーブル名 values('列データ', ...)

データの検索　(select)

データベースの検索にはselect文を使います。 

select カラム名, ... from テーブル名,... 
where 検索条件 
order by 順序指定 
limit 取得する件数 offset スキップする件数

where, order by, limit offsetなどは、オプションです。

staffテーブル中の全部のデータを検索

以下は、sqliteデータベースでSQLを実行した例 

sqlite> select * from staff;
1|Taro L. Saito|xxx@xxx.xxxxx
2|Yoichiro Nakatani|yyy@xxx.xxxxx
3|Jun Yoshimura|zzz@xxx.xxxxx

staffテーブル中の、特定の列だけを検索 

sqlite> select id,email from staff;
1|xxx@xxx.xxxxx
2|yyy@xxx.xxxxx
3|zzz@xxx.xxxxx

条件検索 (where)

 

sqlite> select * from staff where id = 3;
3|Jun Yoshimura|zzz@xxx.xxxxx

where文を使うと条件を指定してテーブルデータを検索できます。

条件検索 (and, or)

where節中でand, orを使うと複雑な条件を記述できます。 

sqlite> select * from staff where name = 'Taro L. Saito' and email = 'xxx@xxx.xxxxx';
1|Taro L. Saito|xxx@xxx.xxxxx

行数のカウント (count)

 

sqlite> select count(*) from staff;
3

sqlite> select count(*) from staff where id = 3;
1

並び替え (order by 昇順)

 

sqlite> select * from staff order by name;
3|Jun Yoshimura|zzz@xxx.xxxxx
1|Taro L. Saito|xxx@xxx.xxxxx
2|Yoichiro Nakatani|yyy@xxx.xxxxx

並び替え (order by 降順)

descをつけると、降順になります。 

sqlite> select * from staff order by name desc;
2|Yoichiro Nakatani|yyy@xxx.xxxxx
1|Taro L. Saito|xxx@xxx.xxxxx
3|Jun Yoshimura|zzz@xxx.xxxxx

limit, offset 指定

データ数が大きくなると、検索結果を10個に限定したり、先頭からいくつかデータをスキップしたいという場合があります。 

sqlite> select * from staff limit 2;
1|Taro L. Saito|xxx@xxx.xxxxx
2|Yoichiro Nakatani|yyy@xxx.xxxxx

sqlite> select * from staff limit 2 offset 2;
3|Jun Yoshimura|zzz@xxx.xxxxx

テーブルの結合 (join)

from部分に二つ以上テーブルを書きます 

sqlite> select staff.id, staff.name, lecture.name from staff, lecture where id = person_id;
1|Taro L. Saito|情報生命科学演習
1|Taro L. Saito|生物情報科学演習
2|Yoichiro Nakatani|生物情報科学演習
3|Jun Yoshimura|生物情報科学演習

left join（左側のテーブルデータの各行に対して、右側で指定されたテーブルデータを条件に基づいて結合していく）を使って書くこともできる。 

sqlite> select staff.id, staff.name, lecture.name from staff left join lecture on id = person_id;
1|Taro L. Saito|情報生命科学演習
1|Taro L. Saito|生物情報科学演習
2|Yoichiro Nakatani|生物情報科学演習
3|Jun Yoshimura|生物情報科学演習

集約 (group by)

上のjoinで得られた結果に対して、staff.idが同じ行を、ひとつの行にまとめる. (group by staff.id)

sqlite> select staff.id, staff.name, group_concat(lecture.name, ',') from staff left join lecture on id = person_id group by staff.id;
1|Taro L. Saito|情報生命科学演習,生物情報科学演習
2|Yoichiro Nakatani|生物情報科学演習
3|Jun Yoshimura|生物情報科学演習

group_concat(lecture.name, ',') は、集約演算で、文字列のリストを、','でつないで結合する、という意味。

集約演算 (aggregate function)

group_concat以外にも、avg, count, min, max, sumなどで、複数のデータを集約することができます。group byを使って複数行を１つにまとめるときは、まとめられる行には集約演算を使わなくてはなりません。 

select 集約演算(列名) from ..　group by グループを決めるための列

exam_score table

id gender class score
1 female A 50
2 male A 30
3 male B 80
4 female B 70
5 female C 90
6 male C 75

クラス毎の試験の平均点を求めるSQL 

sqlite> select class, avg(score) from exam_score group by class;
A|40.0
B|75.0
C|82.5

平均、最小、最大、idのリストを同時に計算 

sqlite> select class, avg(score), min(score), max(score), group_concat(id) from
exam_score group by class;
A|40.0|30|50|1,2
B|75.0|70|80|3,4
C|82.5|75|90|5,6

他の観点から集約

性別ごと 

sqlite> select gender, avg(score) from exam_score group by gender;
female|70.0
male|61.6666666666667

各クラスの性別ごとに集約 (group byのあとに2つ以上つなげる） 

sqlite> select class, gender, avg(score) from exam_score group by gender, class;
A|female|50.0
B|female|70.0
C|female|90.0
A|male|30.0
B|male|80.0
C|male|75.0

行の更新 (update)

行データの一部分を更新 

sqlite> insert into staff values('4', 'Atsushi Sasaki', 'aaa@bbb.ccc');
sqlite> select * from staff where id = '4';
4|Atsushi Sasaki|aaa@bbb.ccc

sqlite> update staff set email = 'zzz@xxx.xxx' where id = '4';
sqlite> select * from staff where id = '4';
4|Atsushi Sasaki|zzz@xxx.xxx
sqlite>

setのあとには、複数の値の設定をカンマでつなげて記述できる。

テーブルの削除 

drop table if exists (テーブル名);

SQLの検索結果を使って、テーブルにデータを挿入 

insert select (テーブル名) (select文）

データベースを使ってみよう

準備 SQLiteのインストール

コマンドラインで扱えるSQLiteのバイナリをダウンロード

SQLiteを起動する 

sqlite3 (作成したいデータベースファイル名、あるいは既存のデータベースファイル名)

で起動できます。ファイル名を入力しないとデータベースはメモリ上に作成されるだけで、sqlite終了後にはデータは消えてしまいます。 

> sqlite3 staff.db
SQLite version 3.5.1
Enter ".help" for instructions
sqlite>

sqliteの使い方を調べる 

sqlite> .help
.bail ON|OFF           Stop after hitting an error.  Default OFF
.databases             List names and files of attached databases
.dump ?TABLE? ...      Dump the database in an SQL text format
.echo ON|OFF           Turn command echo on or off
.exit                  Exit this program
.explain ON|OFF        Turn output mode suitable for EXPLAIN on or off.
.header(s) ON|OFF      Turn display of headers on or off
.help                  Show this message
.import FILE TABLE     Import data from FILE into TABLE
.indices TABLE         Show names of all indices on TABLE
.mode MODE ?TABLE?     Set output mode where MODE is one of:
                         csv      Comma-separated values
                         column   Left-aligned columns.  (See .width)
                         html     HTML <table> code
                         insert   SQL insert statements for TABLE
                         line     One value per line
                         list     Values delimited by .separator string
                         tabs     Tab-separated values
                         tcl      TCL list elements
.nullvalue STRING      Print STRING in place of NULL values
.output FILENAME       Send output to FILENAME
.output stdout         Send output to the screen
.prompt MAIN CONTINUE  Replace the standard prompts
.quit                  Exit this program
.read FILENAME         Execute SQL in FILENAME
.schema ?TABLE?        Show the CREATE statements
.separator STRING      Change separator used by output mode and .import
.show                  Show the current values for various settings
.tables ?PATTERN?      List names of tables matching a LIKE pattern
.timeout MS            Try opening locked tables for MS milliseconds
.width NUM NUM ...     Set column widths for "column" mode

SQL文を入力 

sqlite> create table staff (id integer primary key not null,  name text, email text);
sqltie> insert into staff values('1', 'Taro L. Saito', 'xxx@xxx.xxxxxx');
sqlite>

データベースの内容を確認 

sqlite> select * from staff;
1|Taro L. Saito|xxx@xxx.xxxxxx
sqlite>

sqliteを終了する 

sqlite> .quit

テーブルを確認 

sqlite> .tables
staff
sqlite>

テーブルのスキーマ（データ構造)を確認 

sqlite> .schema
CREATE TABLE staff (id integer primary key not null, name text, email text);
sqlite>

ファイルに書かれているSQLを実行 

sqlite> .read score.sql

score.sql 

drop table if exists exam_score;
create table exam_score (
  id integer primary key autoincrement not null, 
  gender text,
  class text,
  score integer
);

drop table if exists exam_score;

は、exam_scoreテーブルが存在していたら、テーブルを消す、という意味。

tab区切りデータをテーブルに読み込む

score.tab 

1	female	A	50
2	male	A	30
3	male	B	80
4	female	B	70
5	female	C	90
6	male	C	75

読み込む

.separatorでタブを区切り文字に指定。.importでテーブルに、ファイルからデータを読み込む。 

.separator \t
.import score.tab exam_score

SQLite JDBC

SQLite JDBC (http://www.xerial.org/trac/Xerial/wiki/SQLiteJDBC )は、JavaプログラムからSQLiteデータベースシステムを使うためのツールです。

SQLite JDBCライブリのダウンロード

ダウンロードサイト から最新版のライブラリ(jarファイル)をダウンロードします。2008年8月現在の最新版は、

http://www.xerial.org/maven/repository/artifact/org/xerial/sqlite-jdbc/3.6.0/sqlite-jdbc-3.6.0.jar

です。これを、プロジェクト内に保存します。

クラスパスの設定

外部ライブラリを使用するためには、jarファイルをclasspathに追加する必要があります。プロジェクトフォルダ内の sqlite-jdbc-3.6.0.jar ファイルの上で右クリック - Build Path - Add to Build Pathを選択し、ライブラリをクラスパスに追加します。

追加後

SQLiteAccessSample.java 

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class SQLiteAccessSample
{
  public static void main(String[] args) throws ClassNotFoundException
  {
    // SQLiteのJDBCドライバの読み込み
　　// SQLite特融のコードはこの１行のみ
    Class.forName("org.sqlite.JDBC");
    
    Connection connection = null;
    try
    {
      // sample.dbというSQLiteデータベースファイルに接続
      connection = DriverManager.getConnection("jdbc:sqlite:sample.db");
      
      // SQL文を準備
      Statement statement = connection.createStatement();
      // 検索・更新がなかなか終わらないときは30秒で打ち切る設定
      statement.setQueryTimeout(30);  

      // personというテーブルが存在していたら消す      
      statement.executeUpdate("drop table if exists person");

      // 新しいpersonテーブルを作成
      statement.executeUpdate("create table person (id integer, name string)");
      // personテーブルにデータを追加
      statement.executeUpdate("insert into person values(1, 'leo')");
      statement.executeUpdate("insert into person values(2, 'yui')");

      // personテーブルの全ての行を取得するクエリ。ResultSetは検索結果のiteratorの役割
      ResultSet rs = statement.executeQuery("select * from person");
      while(rs.next())				      
      {
        // 検索結果の各行のデータを取り出す
        System.out.println("name = " + rs.getString("name"));
        System.out.println("id = " + rs.getInt("id"));
      }
    }
    catch(SQLException e)
    {
      // if the error message is "out of memory", 
      // it probably means no database file is found
      System.err.println(e.getMessage());
    }
    finally
    {
      try
      {
      　// connectionを忘れずに閉じる
        if(connection != null)
          connection.close();
      }
      catch(SQLException e)
      {
        // connection close failed.
        System.err.println(e);
      }
    }
  }
}

レポート課題9 データベースでピボット演算 (集計)

1からNまでの数(int)を、Collectionに追加するとき、addの効率、contains（特定の要素が含まれているかどうかを調べる）、Collections.sort の性能を調べたい。調べた結果はExcel、あるいはOpenOffice Calkなどを用いてグラフにし、結果について考察せよ。

提出方法

実験対象のCollection

Nを動かす: scalabilityの調査　

データベースで集計

テーブルのサンプル

時間計測の仕方

StopWatchクラス（後述)を使う。 

StopWatch stopWatch = new StopWatch();

double elapsedTime = 0;

stopWatch.reset(); //計測開始
// 何か演算を行う
elapsedTime = stopWatch.getElapsedTime(); // 秒(sec.)単位で実行時間が計測できる

// データベースにelapsedTimeを記録

stopWatch.reset(); //計測開始(StopWatchをリセット）
// 何か演算を行う
elapsedTime = stopWatch.getElapsedTime(); // 計測

// データベースにelapsedTimeを記録

...

StopWatch.java 

public class StopWatch
{

    private long initialSystemTIme;
    private long lastSystemTime;

    public StopWatch()
    {
        reset();
    }

    /**
     * Gets the elapsed time since this instance is created in seconds.
     * 
     * @return the elapsed time in seconds.
     */
    public double getElapsedTime()
    {
        lastSystemTime = System.currentTimeMillis();
        long diff = lastSystemTime - initialSystemTIme;
        return diff / 1000.0;
    }

    /**
     * Gets the interval time since the last call of
     * {@link StopWatch#getEleapsedTime()} or
     * {@link StopWatch#getIntervalTime()}
     * 
     * @return the interval time in seconds
     */
    public double getIntervalTime()
    {
        long now = System.currentTimeMillis();
        long diff = now - lastSystemTime;
        lastSystemTime = now;
        return diff / 1000.0;
    }

    /**
     * Reset the stop watch. The subsequent calls to
     * {@link StopWatch#getEleapsedTime()} or
     * {@link StopWatch#getIntervalTime()} will measure the time intervals
     * beginning from this method call.
     */
    public void reset()
    {
        initialSystemTIme = System.currentTimeMillis();
        lastSystemTime = initialSystemTIme;
    }

}

チャレンジ課題4 Twitter Clientを作ろう

注意

わりと実装に時間がかかると思います。

Twitterって？

Twitter (http://twitter.com) は、Web上で皆が140文字以内のメッセージを残していく、というごく単純なサービスです。著名人も多く参加していて、そういう人たちの会話も見ることができます。

課題

Twitterのpublic timelineのXMLデータを取得し、会話の内容、発言した人、発言日時などの情報を抜き出し、それをデータベースに保存する。そして、そのデータベースの内容を手軽に閲覧するためのGUIアプリケーションを作成しなさい。

Twitter Web API

Public Timeline （最新の、世界中のみなの一言を取得する）

XMLデータの解析

public_timelineのXMLデータは1行に1タグという形なので、Readerで読み取って、正規表現で解釈するのでも十分ですが、正確にデータを取得したい場合は、

定期的にTweetsを更新

public timelineから定期的にデータを取得するには、スレッドによる並列処理が必要になるかもしれません。