JDBC、データベース - Javaのまとめサイト

Javaのまとめサイト
JDBC、データベースのまとめ資料。

- Java -言語についてのまとめ
- Java -クラス設計についてのまとめ
- Java -サーブレット、JSPについてのまとめ
- Java -JDBC、データベースについてのまとめ
- シスコ CCNA対策はこちら
- ITILファンデーション対策はこちら

■oracleクライアント設定(サーバホストは別にある)
b21WS15 / b21WS15
service name: xe
hostname: 192.168.21.210

■JDBC設定
TomcatHOME\common\lib つまり C:\java\tomcat5.5\common\lib にJDBCドライバをコピー
Oracle社HPからDLもしくはオラクルクライアントにインスコ時に入っているものを使う、以下にある
C:\Oracle\product\10.1.0\Client_1\jdbc\lib\ojdbc14.jar　(オラクル9以降、以前はojdbc12.jar)

■接続コード
"jdbc:oracle:thin:@IPアドレス:ポート:SID接続文字列","ユーザID","パスワード"
thin :thinドライバ、タイプ4で使いやすいらしい、他にはOCI Driverがある
"jdbc:oracle:oci8:@（tnsnames.oraに登録されているデータベースサービス名）","ユーザID","パスワード"
"jdbc:mysql://サーバ名/データベース名","ユーザID","パスワード"

接続はトライで、処理が終われば必ず切断すること、ファイナリーでも必ず切断(オラクルサーバへのプールが減るので)
rs.getString(列数or列名)の返りは表形式、re.nextで1行目に持っていく
stmt = cn.createStatement();
rs = stmt.executeQuery("select sysdate from dual");
if(rs.next()){
msg = rs.getString(1);
}

public void doGet(){
String msg;
Connection cn = null; //コネクションクラス
Statement stmt = null; //ステートメントクラス
ResultSet rs = null; //リザルトセットクラス

try{
Class.forName("oracle.jdbc.driver.OracleDriver"); //JDBCドライバの読み込み
cn = DriverManager.getConnection(
"jdbc:oracle:thin:@192.168.21.210:1521:xe",
"b21ws15","b21ws15");

stmt = cn.createStatement();
rs = stmt.executeQuery("select sysdate from dual");
if(rs.next()){
msg = rs.getString(1);
}else{
msg = "取得失敗…";
}

cn.close(); //ＤＢ切断
}catch(Exception e){
msg = "接続失敗・・・";
}finally{
try{
if(rs != null) {
rs.close();
}
if(stmt != null) {
stmt.close();
}
if(cn != null) {
cn.close();
}
}catch(Exception e){
System.out.println(e.getMessage());
}
}
response.setContentType("text/html; charset=Windows-31J");
PrintWriter out = response.getWriter();
out.println("<html><body>");
out.println(msg);
out.println("</body></html>");
}

■接続情報の独自クラス作成　javautil.DBconnect
DB接続情報を一元管理する
package javautil;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBConnect {
private static final String ORA_JDBC = "oracle.jdbc.driver.OracleDriver";
private static final String ORA_URL = "jdbc:oracle:thin:@192.168.21.210:1521:xe";
private static final String ORA_USER = "b21ws15";
private static final String ORA_PASSWORD = "b21ws15";

public static Connection doConnect() throws ClassNotFoundException, SQLException {
Class.forName(DBConnect.ORA_JDBC);
Connection cn = DriverManager.getConnection(
DBConnect.ORA_URL,
DBConnect.ORA_USER,
DBConnect.ORA_PASSWORD
);
return cn;
}
}

import javautil.DBConnect;
doGet(){
try{
Connection cn = DBConnect.doConnect();
Statement stmt = cn.createStatement();
ResultSet rs = stmt.executeQuery("select sysdate from dual");
if(rs.next()){
String sysdate = rs.getString(1);
}
cn.close();
}
}

■cmd
cd c:\a ドライブ変更
cd a 相対パス
cd \ ルート
cd .. 一つ上
cd ..\.. 二つ上

dir ディレクトリ表示
ファイル候補表示 Tab
コマンド履歴 ↑
ファイル名（ディレクトリも可）の補完　Control＋Fキー
ディレクトリの補完　Control＋Dキー
* 任意の複数文字を指定する
? 任意の一文字を指定する
help ヘルプコマンド
cls　クリア

WINOSは大文字小文字を判断しない

■Oracle
--設定
b21WS15 / b21WS15
service name: xe
hostname: 192.168.21.210
--DB接続
cmd
>sqlplus b21ws15@xe(ユーザ名@DB名)
>b21ws14(パスワード)
--DB切断
>exit
--ファイル実行
>@ファイル名(B2-1serv:\02DB\TableScript)
cmdへファイルをDrag&Dropで入力できる
--DML
>select * from 商品マスタ;

↑で命令履歴表示
null値はもっとも大きな値
集計関数はNULLを無視する
文字列は'で囲む
※取得したいカラムは全てGroupBy句に入れる(集計関数に使われるカラムを除く)
※集計関数の条件はhaving句を使用する(where句は元のデータに対するもの)、having句には集計関数が入る
SQLワイルドカード:複数文字%,1文字_
WINワイルドカード:複数文字*,1文字?
エスケープ文字はescape句で設定。like句の後ろにつく。escape '\'
日付と文字列の変換が必要To_char関数とTo_date関数を使用する
to_char(column,'yyyy/mm/dd hh24:mi:ss')
to_date('2009/02/21','yyyy/mm/dd')
横幅を大きくするset line 150
縦を大きくするset pages 30
※unionはdistinctが掛かる、全取得はunion all
※inner join onは両方のテーブルにあるものだけ、どちらかだけにあるのは無くなるイメージ
※left outer join onは左側のテーブルにしかないものも表示、左に右の該当部をくっつけるイメージ
※right outer join onは右側のテーブルにしかないものも表示、右に左の該当部をくっつけるイメージ
※full outer join onは全ての行を表示
trancateは高速だがロールバックできない,deleteはロールバックできる

■バッチファイル作成 ora_lobin.bat
オラクルへのログインバッチ
@echo on
echo オラクルに接続するバッチ
sqlplus b21ws15/b21ws15@xe
set line 150
set pages 30

■DML
問題11　4月の売上ID 4/1-5/1でいいのか？4/30まで？
select distinct 商品ID from 売上データ
where 処理日 between TO_DATE('00-04-01','yy-mm-dd') and TO_DATE('00-04-30','yy-mm-dd');

select distinct 商品ID from 売上データ
where 処理日 between '00-04-01' and '00-04-30';

悩むよりは月初だけ考えて手を抜く案
日付>='2009-05-01' and 日付<'2009-06-01'
ワイルドカードを使う案
日付 like '2009-05%'
Oracleだとこれが使えるかも？
first_day関数とlast_day関数を使う
日付 between first_day('2009-05-01') and last_day('2009-05-01')

select 顧客ID,顧客名 from 旧顧客マスタ
union
select 顧客ID,顧客名 from 顧客マスタ

select 顧客ID,顧客名,'old' as 区分 from 旧顧客マスタ
union all
select 顧客ID,顧客名,'new' as 区分 from 顧客マスタ;

insert into 商品マスタ(商品名,グループ名) values ('ビデオカメラ','周辺機器');

delete

■テーブル結合4種
1対1、1対多の結合を行う
１対多でも行数が増えるが、多対多は全組合せになり行数が比例関数的に増えてしまう
多対多になるのはテーブル設計が悪い
1)inner join 両方あるものを表示、欠けがでる
2)left outer join どちらかは欠けがなく表示
3)right outer join どちらかは欠けがなく表示
4)full outer join 欠けがなく表示

select URIID,NAME,UKYAKUID from URIAGE left outer join TORIHIKI
on (URIAGE.UKYAKUID = TORIHIKI.ID)

select URIID,NAME,UKYAKUID from TORIHIKI right outer join URIAGE
on (URIAGE.UKYAKUID = TORIHIKI.ID)

select URIID,NAME,UKYAKUID from TORIHIKI full outer join URIAGE
on (URIAGE.UKYAKUID = TORIHIKI.ID)

select 伝票番号,行番号,処理日,商品名,担当者名,顧客名,数量 from 売上データ
left outer join 商品マスタ on 売上データ.商品ID = 商品マスタ.商品ID
left outer join 担当者マスタ on 売上データ.担当者ID = 担当者マスタ.担当者ID
left outer join 顧客マスタ on 売上データ.顧客ID = 顧客マスタ.顧客ID
order by 伝票番号,行番号

■多対多、1対多の行数
社員と職域、社員と手当、それぞれ結合しても多の行数(1x多)になる
多x多の行数になる、多対多の結合は行わないこと
-- データ挿入：社員
INSERT INTO employee VALUES (1, '田中');
INSERT INTO employee VALUES (2, '鈴木');
INSERT INTO employee VALUES (3, '佐藤');

-- データ挿入：職域
INSERT INTO joblevel VALUES (1, '部長');
INSERT INTO joblevel VALUES (1, '課長');
INSERT INTO joblevel VALUES (2, '平');

-- データ挿入：手当
INSERT INTO compensation VALUES (1, '住宅');
INSERT INTO compensation VALUES (1, '交通');
INSERT INTO compensation VALUES (2, '平');

-- １対多
select * from EMPLOYEE
inner join JOBLEVEL on EMPLOYEE.ID = JOBLEVEL.ID

-- 多対多
select * from EMPLOYEE
inner join JOBLEVEL on EMPLOYEE.ID = JOBLEVEL.ID
inner join COMPENSATION on EMPLOYEE.ID = COMPENSATION.ID

■CSE
Pドライブ>toolからCSEをDL
メニュー>DB
DBMS：オラクルを選択
ユーザ名：b21ws15
パスワード：b21ws15
接続文字列:xe

DBエクスプローラでテーブルをダブルクリックで選択できる
プロパティでフィールドをダブルクリックで選択できる
！、Ctrl+Enterで実行
選択したSQL分の一部分だけの実行もできる
行削除やセルの更新も操作でできる

■テーブル設計、制約
CONSTRAINT pk_uriage PRIMARY KEY (伝票番号, 行番号),
CONSTRAINT fk_shohin FOREIGN KEY (商品ID) REFERENCES 商品マスタ(商品ID)
CONSTRAINT ck_gender CHECK (性別=0 OR 性別=1)
制約は列に指定しても、うしろで指定してもどちらでもよい
CONSTRAINTのキー名を省略するとオラクルが勝手につける
主キーにはNULLは入らない
ユニークキーにはNULLを入れてもよい
NOT NULLの未入力はエラー

■シーケンス
シーケンスはオブジェクトとして表とは別に作成する
デクリメントはできないので修正はDROPして作成し直す
DUALはダミー表
nextvalはインクリした後の値
currvalは現在地
create sequence testseq
select testseq.nextval from dual
select testseq.currval from dual
insert into URIAGE (URIID,URIDATE,UKYAKUID) values (testseq.nextval,'09-11-16','101')
drop sequence testseq

■ビュー
頻繁に行われるような問い合わせをスキーマに格納しておく
集約等をしなければパフォーマンスには問題がない。次を使うとパフォーマンスが落ちる。OLAP 関数（RANK, DENSE_RANK, ROW_NUMBER 等）、集約関数（AVG, COUNT, SUM, MIN, MAX）、集合演算子（ UNION, INTERSECT, EXCEPT 等）
ビューは検索だけでなく、データの追加、更新、削除も行える※使用しない方が良い？

create view 売上ビュー as
select 伝票番号,行番号,処理日,商品名,担当者名,顧客名,数量 from 売上データ
left outer join 商品マスタ on 売上データ.商品ID = 商品マスタ.商品ID
left outer join 担当者マスタ on 売上データ.担当者ID = 担当者マスタ.担当者ID
left outer join 顧客マスタ on 売上データ.顧客ID = 顧客マスタ.顧客ID
order by 伝票番号,行番号
select * from 売上ビュー

■正規化
２回以上記録しない、主キーから連想されるデータのみで構成する
第一正規化繰り返しを排除
第二正規化主キーの一部に従属する項目を分離
第三正規化主キー以外のキーに従属する項目を分離、計算で出る項目を削除

■稼動率
平均故障間隔MTBF：稼働している時間
平均修理時間MTTR：修理している時間
稼動率=MTBF/(MTBF+MTTR)
並列システムの稼働率　axb
直列システムの稼働率 1-(1-a)(1-b)

■要件定義/外部設計/内部設計
http://www3.alpha-net.ne.jp/users/design1/takeda/SystemDesign/SystemDesign.htm

java webアプリケーションプログラミングサーブレット JSP データベース DB ゲーム script ダウンロード sun 入門講座 vm eclipse アプレットジャバザハットコンチェブーシクラスメソッドコンストラクタ実装スプリクトアプレットオブジェクト呼び出し初歩例外リシュモンコンパス入門プログラミング言語スウォッチ変数 bufferedreader stringbuffer stringtokenizer extends inputstream hashmap javaworld synchronized applet class equals iterator tostring boolean resultset applets constructor javadoc specified implements arraylist interface hashtable arrays documentation jfilechooser 遅い exceptions vector tutorial import