c4se記：さっちゃんですよ☆

.｡oO(さっちゃんですよヾ(〃l _ l)ﾉﾞ☆)

.｡oO(此のblogは、主に音樂と考察と Programming に分類されますよ。ヾ(〃l _ l)ﾉﾞ♬♪♡)

音樂は SoundCloud に公開中です。

考察は現在は主に Scrapbox で公表中です。

Programming は GitHub で開發中です。

トップ > Programming > Java, C# からMySQLへの接続に関する発展的な補足。RAII, class, ORM

Java, C# からMySQLへの接続に関する発展的な補足。RAII, class, ORM

Programming Java C# SQL

二通のmailを同級生へ書いた。もったいないので此所へ転載する。

第一のmail 「帰った(〃l _ l)?? Java大丈夫?」

覚えるしかないところもありますので、少し書いておきます。JavaやC#では、DBへのSQL実行には、以下の三つのstepを踏みます。

  1. connectionStringを基にConnectionを得る。
  2. SQL文実行機を得る (JavaではStatement、C#ではMySqlCommand)。
  3. SQL文実行機にSQL文を渡し、実行する。結果を得る。

必要であれば、この後に結果を使います。Java + MySqlでの基本的なcodeを挙げます。

import java.sql.*;
String URL = "jdbc:mysql://localhost:3306/jv21?user=root&password=&useUnicode=true&characterEncoding=UTF-8";
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection(URL);
Statement statement = connection.createStatement();
String sql = "select id, name, price from items";
ResultSet result = statement.executeQuery(sql);
while (result.next()) {
  // 実際には此處で、<a href="http://docs.oracle.com/javase/6/docs/api/java/util/ArrayList.html">List<int></a>やList<String>等に格納する必要が有る。
  int id = result.getInt("id");
  String name = result.getString("name");
  int price = result.getInt("price");
}
result.close();
String name = "item名";
int price = 100;
sql = "insert into items(name, price) values ('" + name + "', " + price + ")";
statement.executeUpdate(sql);
statement.close();
connection.close();

授業で習った、select, insert, update, deleteのJava + MySqlは、以上の如き処理の筈です。実際には細かい部分に問題が有って、

  1. 明確なSQL injection脆弱性が有る。
  2. MySqlへの接続用URLや、Connectionを得る処理は、tableに関らずapplication中常に同一の処理であり、冗長である。classへ分けると好いだろう。
  3. CRUD (select, insert, update, delete)の処理は、同一tableであればほぼ同一の処理であり、冗長である。table毎にclassへ分けるのが好いだろう。
  4. ResultSet, Statement, Connectionを閉じ損ねる事が有る。try {} finally {}句を使い、finally句中で閉じれば、JavaのRuntime自体のerrorでない限り閉じ損ねる事は無い。
  5. ResultSet, Statement, Connectionを閉じ忘れる事が有る。try {} finally {}句ではなく、RAII (Resource Acquisition Is Initialization)を実践すると好いだろう。C#ではusing句、Javaではtry-with-resources Statementにて実現できる筈である。

と云う所が気になりますが、これらは発展的問題ですから、授業で習った通りの事ができれば先ずは第一歩でしょう。第一歩には第二歩が続くものですが。

第二のmail 「Java, C#からMySQLへの接続に関する発展的な補足。RAII, class, ORM」

先程送ったメールに就いて、発展的な補足を少しする。
RAIIとはResource Acquisition Is Initializationの略で、resourceの確保と破棄を、變數の寿命と結び附ける方式を謂う。resouceとは、applicationが「確保」しなければならない何らかのものを考える概念である。多くはfileのlockやglobal變數のlock等を考える時に使い、今回はdatabaseへの接続である。明示的にcloseしなければならないものの事を謂うとも考えてよい。
resouceを確実に開放する爲に、通常は try {} finally {} を使う。

import java.util.*;
import java.sql.*;

List<int> ids = new ArrayList<int>;
List<int> years = new ArrayList<int>;
List<int> months = new ArrayList<int>;
List<int> dates = new ArrayList<int>;
List<String> names = new ArrayList<String>;
List<int> prices = new ArrayList<int>;

String URL = "jdbc:mysql://localhost:3306/jv21?user=root&password=&useUnicode=true&characterEncoding=UTF-8";
Class.forName("com.mysql.jdbc.Driver");
String sql = "select id, inoputdate, name, price from items";
Calendar calendar = Calendar.getInstance(TimeZone.getTimeZone("JST"));
try {
    Connection connection = DriverManager.getConnection(URL);
    Statement statement = connection.createStatement();
    ResultSet result = statement.executeQuery(sql);
    while (result.next()) {
        ids.add(result.getInt("id"));
        calendar.setTime(result.getDate("inputdate"));
        years.add(calendar.get(Calendar.YEAR));
        months.add(calendar.get(Calendar.MONTH) + 1);
        dates.add(calendar.get(Calendar.DATE));
        names.add(result.getString("name"));
        prices.add(result.getInt("price"));
    }
} finally {
    result.close();
    statement.close();
    connection.close();
}

finally句にて、resourceを閉じている。但し二つの問題が有る。

  1. finally句を書くのがうざい。
  2. resouceを閉じる順番を間違える可能性が有る。一つに、或るresourceが他のresourceに依存している場合、順番を間違えればcrashするかもしれない。二つに、dead lockの原因と成る。

RAIIで此の問題を軽減する。Javaではresource附きtry句を使える。

import java.util.*;
import java.sql.*;

List<int> ids = new ArrayList<int>;
List<int> years = new ArrayList<int>;
List<int> months = new ArrayList<int>;
List<int> dates = new ArrayList<int>;
List<String> names = new ArrayList<String>;
List<int> prices = new ArrayList<int>;

String URL = "jdbc:mysql://localhost:3306/jv21?user=root&password=&useUnicode=true&characterEncoding=UTF-8";
Class.forName("com.mysql.jdbc.Driver");
String sql = "select id, inoputdate, name, price from items";
Calendar calendar = Calendar.getInstance(TimeZone.getTimeZone("JST"));
try (Connection connection = DriverManager.getConnection(URL);
        Statement statement = connection.createStatement();
        ResultSet result = statement.executeQuery(sql)) {
    while (result.next()) {
        ids.add(result.getInt("id"));
        calendar.setTime(result.getDate("inputdate"));
        years.add(calendar.get(Calendar.YEAR));
        months.add(calendar.get(Calendar.MONTH) + 1);
        dates.add(calendar.get(Calendar.DATE));
        names.add(result.getString("name"));
        prices.add(result.getInt("price"));
    }
}

try (resource變數の宣言) { resourceを使う処理 } と書く。注意として、resource附きtry句は、try句と違いcatch句もfinally句も書けない、例外処理の try {} catch () {} finally {} 句とは見た目以外別物である。
C#では同様にusing句を使う。using句も、using文とは別物である。

// using MySql.Data.MySqlClient; が必要。

var ids = new List<int>();
var inputdates = new List<DateTime>();
var names = new List<string>();
var prices = new List<int>();

string connectionString = string.Format(
    "server={0};user id={1};password={2};database={3};charset=utf8;sqlservermode=true;",
    "locathost", "root", "", "jv21"
    );
using (var connection = new MySqlConnection(connectionString))
{
    connection.Open();
    var commend = connection.CreateCommand();
    command.CommandText = "select id, inputdate, name, price from items";
    using (var reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            ids.Add(reader.GetInt("id");
            inputdates.Add(reader.GetDateTime("inputdate");
            names.Add(reader.GetString("names");
            prices.Add(reader.GetInt("prices");
        }
    }
}

connectionとreaderを明示的に閉じる必要が無くなる。
此の様なRAIIを使ったcodeにも、未だ別種の問題を簡単に見付ける事が出来る。dataの各columnが、別のListへ分離している爲に、管理がややこしいことだ。此の為には構造體struct(構造体)、或いはclassを使うと良い。Javaには構造體は無いが、C#には有る cf. struct (C#) cf. Java VS. C# : struct 。

public struct Item {
    int id;
    DateTime inputdate;
    string name;
    int price;
}

var items = new List<Item>();

string connectionString = string.Format(
    "server={0};user id={1};password={2};database={3};charset=utf8;sqlservermode=true;",
    "locathost", "root", "", "jv21"
    );
using (var connection = new MySqlConnection(connectionString))
{
    connection.Open();
    var commend = connection.CreateCommand();
    command.CommandText = "select id, inputdate, name, price from items";
    using (var reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            var item = new Item();
            item.id = reader.GetInt("id");
            item.inputdate = reader.GetDateTime("inputdate");
            item.name = reader.GetString("names");
            itam.price = reader.GetInt("prices");
            items.Add(item);
        }
    }
}

構造體とは此の様な、fieldを纏めた「型」である。型に関する詳細な議論は、HaskellやOcaml、Coq等に触れれば得られるが、此所では展開しない。一般にOOPは数学的でないとされるが、OOPを計算modelとして扱える理論も存在する cf. 五十嵐淳「オブジェクト計算：Featherweight Java」 2008 。
此所で、items表の一覧を得る作業は、再利用するものであるから、此れを手続きに纏める。

public struct Item {
    int id;
    DateTime inputdate;
    string name;
    int price;
}

public List<Item> ListItems()
{
    var items = new List<Item>();
    string connectionString = string.Format(
        "server={0};user id={1};password={2};database={3};charset=utf8;sqlservermode=true;",
        "locathost", "root", "", "jv21"
        );
    using (var connection = new MySqlConnection(connectionString))
    {
        connection.Open();
        var commend = connection.CreateCommand();
        command.CommandText = "select id, inputdate, name, price from items";
        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                var item = new Item();
                item.id = reader.GetInt("id");
                item.inputdate = reader.GetDateTime("inputdate");
                item.name = reader.GetString("names");
                itam.price = reader.GetInt("prices");
                items.Add(item);
            }
        }
    }
    return items;
}

var items = ListItemns();

構造體と手続きを別に持つ必要は無いので、classへ纏める。

public class DBConfig
{
    private static readonly string connectionString = string.Format(
        "server={0};user id={1};password={2};database={3};charset=utf8;sqlservermode=true;",
        "locathost", "root", "", "jv21"
        );

    public MySqlConnection GetConnection()
    {
        return new MySqlConnection(connectionString);
    }
}

public class Item
{
    public static List<Item> List()
    {
        var items = new List<Item>();
        using (var connection = new DBConfig().GetConnection())
        {
            connection.Open();
            var commend = connection.CreateCommand();
            command.CommandText = "select id, inputdate, name, price from items";
            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    items.Add(new Item
                    {
                        ID = reader.GetInt("id");
                        InputDate = reader.GetDateTime("inputdate");
                        Name = reader.GetString("names");
                        Price = reader.GetInt("prices");
                    });
                }
            }
        }
        return items;
    }

    public int ID;
    public DateTime InputDate;
    public string Name;
    public int Price;
}

var items = Item.List();

classへの変化はこのようにdataを抽象化する過程としても捉えることができる。他の説明もあり、Matzが變數scopeの進化として、動的scope、lexical scopeと変化してゆく先にclassを据える議論をしているのを、読んだことがある。又、JavaScriptやio languageに見るprototype base OOPと、JavaやC#が属するclass base OOPとの大きな違いがある上に、classの考えは言語毎に如実に違い、更にOOPの概念すらも一つではない。 cf. オブジェクト指向の概念の発明者は誰ですか？ - Smalltalkのtは小文字です (此れがいつも私が君への話に持ちだそうとして失敗している記事だ。)
此のcodeに残る典型的な問題は、

  1. SQL where句の条件等を明示的に文字列操作せねばならず、柔軟に指定し辛い点。
  2. 表のscheme定義を明示的にclassへ反映せねばならず、database上のschemeとC#code上のclass定義が乖離する可能性が高い点。

である。前者を解決するのが、JavaではHibernate等、C#ではNHibernate等であり、前者と後者共に解決するのが、代表的にはRubyに於けるActiveRecordであり、C#に於いてはLINQを通したdatabaseへの接続や、Castle ActiveRecord等である。總稱してORM, O/R Mapper (Object-relational mapping) と呼ぶ。 cf. O/R Mapperを支える技術 | 日本Ruby会議2011(7月16日～18日) cf. Hibernate - Wikipedia cf. Active Record - Wikipedia
私見として、objectの永続化は、もはやORMの主要な観念では無いと考える。植物から動物への進化をみればそれは運動性の進化と見えるが、さらに人間と云う段階を考えれば、植物もふくめて精神性の進化と観察できるように、本質という概念は段階性と切り離せない。 cf. 段階 #memo - c4se記：さっちゃんですよ☆
SQL injection脆弱性に関しては、MySql.Data.MySqlClientの場合、MySqlCommand.Parameters.AddWithValueを使う事。