はじめに

Javaの学習を始めました。
こんなレベルから・・・？というくらい超基礎～から執筆します。
自身が復習できるような自身の備忘録を作成していきます。

データベース接続の仕組み

データベース(DB)

データを整理・蓄積したもの。DBとも呼ぶ。
Dataは資料。基礎事実。実験、観測、調査、統計学的に処理したりして得た数値情報。
baseは「基地」という意味があり、直訳すると「情報の基地」のようなもの

データを管理してくれるソフトウェアのこと。
そしていろいろな種類があり、どのDBを利用するかを決定した上で開発をすすめる。

Oracle
MySQL

などいろいろなDBソフトウェアがある。

JDBC

Java DataBase Connectivityの略
Javaとデータベースをつなげる役割をするもの(クラスやインターフェイスの集まり)

JDBC API
APIとはプログラムやサービスの機能を外部から使えるようにする仕組みや利用方法
JDKに含まれるパッケージなので、一般的なJava環境が整っていれば使用できる。
JDBC ドライバ
各DBの開発元が公開してくれている。各サイトのDLページなどからダウンロードできるようになっていたりする。

の２つで構築されている。

JDBCドライバの役割って？

各DBの違いを吸収してくれる役割を持っている。
DBごとの処理が異なってもJDBCドライバが違いを吸収するため、Javaのプログラムはほぼ変わらない。

SQL

Stuructured Query Languageの略
言語の１つ。データベースを操作するために使われる構造化問い合わせ言語。

流れ

DB・JDBCドライバーのインストール
SQLの操作
JDBCのプログラミング
Webアプリ連携

の順で進めていく

事前準備(DBおよびJDBCドライバのインストール)

本記事ではMySQLを使用して進める。

MySQLのインストール

MySQLとはよく使われるオープンソース(OSS)のデータベース
OSSとはOpen Source Softwareの略で、ソースコードが公開され、
無償で使うことや改変・再配布が可能なソフトウェア

今回はMySQL Community Serverを使用する。

▼手順はこちらのYoutubeを参考に行う

管理者アカウント「root」のパスワードを設定する必要がある。

JDBCドライバのダウンロード

ダウンロードするだけ。どれをダウンロードするかがちょっとわかりづらい。

▼手順はこちらのYoutubeを参考に行う

環境変数の設定

かなり慎重に作業する必要あり。間違った設定をすると他のプログラムに影響が出る可能性がある。

PATHの設定
MySQL内のbinフォルダの場所を指定する
CLASSPATHの設定
JDBCドライバーのファイルを指定する

その他用語など

JARファイル(Java ARchive)
クラスファイルなどを圧縮してまとめたファイル
「～.jar」

SQLってなに？

RDB（リレーショナルデータベース）を操作する標準言語
SQLはデータベース操作で1番使われている言語ということ。
つまりはデータベースを操作する言語はSQLだけではない。
XQuery/OQL/GraphQL/LINQなどいろいろある。

SQL基礎入門

SQLの実操作について深堀していく。

まず初めにSQLでは大文字・小文字は区別しない。
大文字を使うか、小文字を使うかは現場によって異なる。

データベースの中には細かいくくりとして「テーブル」と呼ばれるものがある。
イメージはエクセルの表のようなイメージで、情報がまとめられている。
縦軸を「カラム(列)」、横軸を「ロウ(行)」と呼ぶ。
1行分のデータを「レコード」と呼ぶこともある。

データ定義言語(DDL)

データベースやテーブルの作成変更を行うことができる言語。

CREATE(作成)
ALTER(変更)
DROP(削除)　など

データ操作言語(DML) ※特によく使う

テーブルの中のデータに干渉する言語。これが1番よく使う。

SELECT(取得)
INSERT(挿入)
UPDATE(更新)
DELETE(削除)　など

例えば、ログインの情報の取得や会員登録処理等はDMLを使用してテーブルへの追記等を行う。

データ制御言語(DCL)

どのユーザーがどのテーブルにアクセル出来るのか？など権限管理する言語。

GRANT(権限割当)
REVOKE(権限取消)　など

SQL操作をはじめてみる

実際の簡単な操作文から実施していく。

データベース・テーブルの作成

データベースの作成

CREATE DATABASE データベースの名前;

テーブルの作成

CREATE TABLE データベース名.表名(列名 データ型,~);

//例
CREATE TABLE sampledb.student(no int PRIMARY KEY,name varchar(10),score int);

MySQLのデータ型は、Javaと感覚が少し違うので、登録するとき調べたほうがいいかも。

「PRIMARY KEY」をデータ型の後ろにつけることで、データの一意性が担保されるようになる。一意性が担保されるとは同じデータは登録できなくなるということ。具体的には、会員番号などがそれにあたる。

データベースやテーブルの確認方法

コマンドプロンプトでMySQLを起動したのちに行うSQL内のコマンドの紹介

データベースの存在を確認

show databases;

テーブルの存在を確認

show tables in データベース名;

テーブルの列や行の情報を確認

desc データベース名.テーブル名;

テーブル操作をする前に

データベースを触る準備

use データベース名;

//これから触るデータベースにしてから作業スタートするように。

SQLファイルを一気に実行するやり方

source ~.sql;

cmd操作が主だが、SQLファイルにコマンドをまとめて記述することで
SQLファイルのコマンドを一気に実行する方法がある。
「~.sql」に記述されている内容がすべて読み込まれてコマンド実行される。

SQLでデータ操作(SELECT文)

テーブル内のデータを取得したいときに使う

データ取得

SELECT 列名 FROM 表名;
//列名は「,」で区切って複数指定可能

データの全取得

SELECT * FROM student;
//studentテーブルの情報をすべて取得

細かくデータ取得

SELECT no , score*0.5 FROM student
//studentテーブルの中の「no」行と「score」行に0.5の掛け算をした値を出力
//ここでの*は数値に挟まれているので計算式として扱われる。

昇順・降順を指定

SELECT 列名か* FROM テーブル名 ORDER BY 対象の列名 ASCかDESC;
//ASCか昇順、DESCが降順

取得件数を指定

SELECT 列名か* FROM テーブル名 LIMIT 件数;

WHEREで条件指定

SELECT 列名か* FROM テーブル名 WHERE 列名 IS NULL;
SELECT 列名か* FROM テーブル名 WHERE 列名 IS NOT NULL;
SELECT 列名か* FROM テーブル名 WHERE 列名 >= 10;
SELECT 列名か* FROM テーブル名 WHERE 列名 IN (A,B);
SELECT 列名か* FROM テーブル名 WHERE 列名 LIKE '検索文字%';
SELECT 列名か* FROM テーブル名 WHERE 列名 LIKE '%検索文字';
SELECT 列名か* FROM テーブル名 WHERE 列名 LIKE '%検索文字%';
SELECT 列名か* FROM テーブル名 WHERE 列名 LIKE '検索文字%' AND 列名 LIKE '%検索文字%';


//比較演算子や中身にデータがあるかどうかなど指定できる

JOINで連結、ASでテーブルの略称を定義

select player from game as gm join goal as go on gm.id=go.matchid
where stadium = 'National Stadium, Warsaw';

//asはテーブル名の別名を設定(省略もできる)、joinは関連するテーブルの追加on以下で共通項の設定をする。

JOINは2つ以上のテーブルも連結できる！

SELECT title, name FROM movie m 
JOIN casting c ON m.id=c.movieid
JOIN actor a ON a.id=c.actorid

WHERE 
yr=1962
AND
ord = 1;

JOINを使わなくてもWHEREでこんな書き方もできる

select　title, name
from　movie m, casting c, actor a
where
 c.movieid=m.id
 and c.actorid=a.id
 and ord=1
 and movieid in 
 (select movieid
from casting, actor
where actorid=actor.id
and name='Julie Andrews')

distinctで重複防止

SELECT distinct(player)
FROM game ga JOIN goal go ON go.matchid = ga.id 
WHERE (team1='GER' or team2='GRE')AND teamid <> 'GRE';

//同じ要件が複数重複している場合disinctで重複分をまとめてくれる

NULLを選択する場合は=は使えない

SELECT name FROM teacher
WHERE dept IS NULL;

COALESCEはNULL値を別の値に置き換える

SELECT name,COALESCE(mobile,'未登録') FROM teacher

列名で条件分岐も作れる

select
 name,
case when dept=1 then 'Sci'
when dept=2 then 'Sci' 
else 'Art' end
from
 teacher;

▼JOINの使い方

▼GROUP BYの使い方

SQLでデータ操作(INSERT文)

テーブル内のデータを挿入したいときに使う

データを挿入

INSERT INTO 表名(列名:省略可) VALUES(挿入したい値)

//例
INSERT INTO student VALUES(1,'山田',80);
「''」で囲ったものは文字列であるということを示している。

//例2
INSERT INTO student(no,name) VALUES(1,'山田');
列を指定して挿入することもできる

SQLでデータ操作(UPDATE文)

テーブル内のデータを更新したいときに使う

データを更新

UPDATE 表名 SET 列名 = 列値;
//列名 = 列値は「,」で複数記述できる

//例
UPDATE student SET score = score+5;

SQLでデータ操作(DELETE文)

テーブル内のデータを削除することができる

データの削除

DELETE FROM 表名;

//例
DELETE FROM student
studentテーブルのデータをすべて削除

SQLで条件指定(SELECT文)

SELECT文/UPDATE文/DELETE文は「WHERE句」を使うことで条件を指定できる。
また条件指定をするうえで演算子の知識もなければならない。

関係演算子 >,>=,<,<=,=,<>
(=はjavaの==と同義、<>はjavaの!=と同義)
論理演算子 AND,OR,NOT
特殊演算子 BETWEEN,LIKE

条件指定(SELECT文)

SELECT {*1列名} FROM 表名[WHERE 取得条件]
//列名は「,」で複数指定可

//例1
SELECT * FROM student WHERE 50<=score AND score<=80;

//例2
SELECT * FROM student WHERE score BETWEEN 50 AND 80;

//例3
SELECT * FROM student WHERE name LIKE '%原';
%は0文字以上の任意の文字列。「～原」を抜き出す。「原」もOK。

SQLで条件指定(UPDATE文)

条件指定(UPDATE文)

UPDATE 表名 SET 列名=値 WHERE 更新条件

//例
UPDATE student SET score=score+5 WHERE no=1;

SQLで条件指定(DELETE文)

条件指定(DELETE文)

DELETE FROM 表名 WHERE 更新条件

//例
DELETE FROM student WHERE no=1;

JDBCについて

Javaでデータベースを操作するにあたって、超重要項目のため詳しく記述していく。

JDBCをあつかう前に

JDBCドライバーを環境変数CLASSPATHに設定しておく必要がある。

JDBCの更新処理のプログラムの流れ

①DBに接続

DriverManagerクラスを扱う。
DriverManagerクラスはJDBCドライバーを管理するためのクラス。
getConnectionメソッドを使用するために使うようなイメージ

Connection con = Driver.Manager.getConnection(JDBCURL,username,pass);

//接続するDBを指定する文字列
//URL:jdbc:mysql://dbサーバー名(localhost)/DB名
//MySQLはURLの形だが、違うデータベースはそれぞれ指定の形で行う

//変数conを作る

②ステートメントを生成

DBとの接続情報を管理

Statement stmt = con.createStatement();

//変数conを使う
//変数stmtを作る

③SQLを実行

SQLを実行

int count=stmt.executeUpdate(SQL文);

//変数stmtを使う
//変数countには変更件数が入る

④リソースを解放

stmt.close();
con.close();

//作った変数を作った順と逆順でstmt⇒conの順番で閉じる

実際にコードを見てみよう

Java⇒SQLはこう書く

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

public class InsertStudent{

  public static void main(String[] args){

    //「①DBに接続」で使う引数を事前に設定
    final String URL = "jdbc:mysql://localhost/sampledb";
    final String USER = "root";
    final String PASS = "pass";

    //「③SQLを実行」で使う引数を事前に設定
    String sql = "INSERT INTO student VALUES(6,'Kobayashi',95)";

    //変数conとstmtを事前にnullで定義
    Connection con = null;
    Statement stmt = null;
    //try内で定義してしまうと、try内でしか使えないのでconとstmtがcloseできない
    

    try{
      //①DBに接続
      con = DriverManager.getConnection(URL, USER, PASS);
      
      //②ステートメントを生成
      stmt = con.createStatement();
      
      //③SQLを実行
      int count = stmt.executeUpdate(sql);      
      System.out.println( count + "件更新しました");
    } catch(Exception e){
     //tryの中でエラーが発生したときのエラー表示
      e.printStackTrace();
    } finally {
      try{
        //④リソースを解放
        //例外が発生しても必ず実行されるfinallyブロックに
        //nullだとcloseができないのでif文に入れている
        if(stmt != null) stmt.close();
        if(con != null) con.close();
      } catch(Exception e){
        e.printStackTrace();
      }
    }
  }
}

//conとstmtは必ずラストにcloseしなければならない

JDBCの検索処理のプログラムの流れ

SQLを実行

更新処理と検索処理ではSQLを実行するときのメソッドが異なる
更新の時は戻り値が数字、検索のときは検索結果が戻り値だから

ResultSet rs = stmt.executeQuery(SQL文);

//ResultSet検索した結果を保持している

検索結果の処理

while(rs.next()){
//getterメソッド呼び出しデータ型により記述内容が変わる
}


//nextメソッドは行(横軸)の移動、データがあれば戻り値はtrue、なければfalse

//gettterメソッドは列(縦軸)へのアクセス
//getXxx("列名"/列番号)列番号は1から始まる
//String name = rs.getString("name");

リソースの解放

検索処理についてもリソースの解放を行うこと

rs.close();
stmt.close();
con.close();

実際にコードを書いてみよう

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

public class SelectAllStudent{

  public static void main(String[] args){
  
    final String URL = "jdbc:mysql://localhost/sampledb";
    final String USER = "root";
    final String PASS = "pass";
    String sql = "SELECT * FROM student";
    
    Connection con = null;
    Statement stmt = null;
    ResultSet rs = null;

    try{
      //①DBに接続
      con = DriverManager.getConnection(URL, USER, PASS);
      //②ステートメントを生成
      stmt = con.createStatement();
      //③SQLを実行
      rs = stmt.executeQuery(sql);
      //④検索結果の処理
      System.out.println("+-------+-------+-------+");
      System.out.println("| no    | name  | score |");
      System.out.println("+-------+-------+-------+");
      while(rs.next()){
        System.out.print("| " + rs.getInt("no") + "\t");
        System.out.print("| " + rs.getString("name") + "\t");
        System.out.println("| " + rs.getInt("score") + "\t|");
      }
      System.out.println("+-------+-------+-------+");
    } catch(Exception e){
      e.printStackTrace();
    } finally {
      try{
        //⑤リソースの解放
        if(rs != null) rs.close();
        if(stmt != null) stmt.close();
        if(con != null) con.close();
      } catch(Exception e){
        e.printStackTrace();
      }
    }
  }
}

PreparedStatementの利用

Preparedは用意されたという意味
この方法はステートメントの段階でSQL文を引数に渡しておく方法。

事前にINパラメーターを指定したSQL文を記述し
setterメソッドでSQL文のINパラメーターに簡易的に
値を代入できるという手法

使用する手順

①INパラメーター入りSQL文を指定する
INパラメーターとは(?.?.?)のこと

INSERT INTO students VALUES(?.?.?)

②setterメソッドで？に値を設定

ps.setInt(1,7);
ps.setString(2,"藤原");

//データ型は第2引数に合わせる
//第1引数は?の位置を指定している

③SQLを実行

④リソースの解放
closeは必ずすること

実際に書いてみよう

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;

public class InsertStudent2{

  public static void main(String[] args){
  
    final String URL = "jdbc:mysql://localhost/sampledb";
    final String USER = "root";
    final String PASS = "pass";
    String sql = "INSERT INTO student VALUES(?, ?, ?)";
    String[] names = {"sato", "yamada", "kawata", "uchida"};
    int[] scores = {90, 85, 80, 75};
    
    Connection con = null;
    PreparedStatement ps = null;
    
    try{
      //①DBに接続
      con = DriverManager.getConnection(URL, USER, PASS);
      //②ステートメントを生成
      ps = con.prepareStatement(sql);
      //③SQLを実行
      for(int i = 0; i < names.length; i++){
        ps.setInt(1, i + 7);
        ps.setString(2, names[i]);
        ps.setInt(3, scores[i]);
        ps.executeUpdate();
      }
      System.out.println( names.length + "件更新しました");
    } catch(Exception e){
      e.printStackTrace();
    } finally {
      try{
        //リソースの解放
        if(ps != null) ps.close();
        if(con != null) con.close();
      } catch(Exception e){
        e.printStackTrace();
      }
    }
  }
}

Webアプリを絡めたキーワード

この辺りはキーワードの紹介のみで完結とする

MVCモデル

モデル・ビュー・コントローラーに分けて
相互に作用し合いアプリケーションを構築する仕組みのこと。
以前自身が勉強していたRuby on Railsでも使用されている形式でJavaでも同様の仕組みをとる。

MVCモデルの深堀は別記事でまとめているため、そちらを参照

JavaのMVCでのキーワード

Ruby on RailsのMVCモデルと大きく違うところはJavaのMVCモデルはそれぞれJavaの記法を守って作成するところ。
Ruby on RailsではMVCそれぞれで記載方法が異なっていたため、そこは大きな違いかと。

DAO
Data Accces Objectの略。
MVCモデルのモデルに当たる。
データベースとのやりとりの箇所のみ。
基本的にSQLがらみの記述はDAOにするイメージ。
サーブレット
MVCモデルのコントローラーに当たる。
JSP
Java Server Pagesの略
MVCモデルのビューに当たる。HTMLなどで記述する。

JavaのMVCのポイント

DAOとサーブレットはこれまでにも出てきたクラス同士をつなげる記法でつながりを持たせることができる。

サーブレットに記載する例

StuDAO sdao = new StuDAO;

//この記述で「StuDAO」という名前のDAOファイルと接続ができる。

DTO
Data Transfer Object
データを転送するために使用するクラス

DAO

StuDTO select(){...}

//テーブル内の情報をすべて取得するイメージ

Entitiyクラス
テーブルの1行分を転送するために使用するクラスのこと。DTOの中の１つのようなイメージ。
DTOにadd,get,sizeメソッドなどの、一覧から表示するようなメソッドを定義。
Entityにはテーブル内の項目（氏名、生徒番号）などの情報に基づいたset,getメソッドの定義が必要
DAOとDTOとEntity
すごくふんわりしたイメージ。
クラスの種類。３クラスとも必ず事前に定義が必要。
DAOはデータ取得
DTOはデータ転送（全件データ）
Entityはデータ転送のより詳細な指示（詳細データ）
DTOとEntityはDAOを補助するツールクラスみたいなイメージ
一覧情報をDTOで取得し、Entityで1行などの詳細情報を取得するイメージ。
EntityはBeansなどの変数で定義されることが多い。

応用編(ウェブアプリとの連携)

web作成については実作業として、作成する際に見返して再度組むようにする。

データベースを触るうえで便利なソフト

ここまでcmdなどのCUI上で作業してきたが、実はデータベースをGUIとして操作できるソフトが多数ある。
CUIに慣れていない私は圧倒的に操作しやすく感じたが、これに慣れるとコマンド使わなくなりそう・・・。

DBeaver

A5M2