GroovyスクリプトからMySQLアクセス(Hibernate)

# 概要
## 方針
- MySQLの読み書きをGroovyスクリプトのみで実行する
- テーブル定義もスクリプト内に書く

## 試した環境
- MySQL 5.5
- Debian 7.5
- Groovy 2.4.5
- Java 1.7.0_40

# ソース

```groovy:db.groovy
import javax.persistence.*
import org.hibernate.cfg.*

// javax.transaction jta.jar added manually to ivy repo
@Grapes([
    @Grab(group='org.hibernate', module='hibernate-annotations', version='3.5.6-Final'),
    @Grab(group='org.slf4j', module='slf4j-simple', version='1.7.13'),
    @Grab(group='mysql', module='mysql-connector-java', version='5.1.29'),
    @Grab(group='javassist', module='javassist', version='3.12.1.GA'),
])

@Entity
@Table(name='twitter_account_info')
class TwitterAccountInfo {
    @Id
    //@GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = 'id')
    long number

    @Column(name = 'account_name')
    String accountName

    // ツイート数
    @Column(name = 'update_count')
    long updateCount

    // for println
    String toString() {
        "id: $number\naccount_name: $accountName\nupdate_count: $updateCount"
    }
}

def hibProps = [
    "hibernate.dialect": "org.hibernate.dialect.MySQL5InnoDBDialect",
    "hibernate.connection.driver_class": "com.mysql.jdbc.Driver",
    "hibernate.connection.url": "jdbc:mysql://localhost:3306/db",
    "hibernate.connection.username": "root",
    "hibernate.connection.password": "root",
    "hibernate.connection.pool_size": "1",
    "hibernate.connection.autocommit": "true",
    "hibernate.cache.provider_class": "org.hibernate.cache.NoCacheProvider",
    //"hibernate.hbm2ddl.auto": "create",
    "hibernate.hbm2ddl.auto": "update",
    "hibernate.show_sql": "true",
    "hibernate.transaction.factory_class": "org.hibernate.transaction.JDBCTransactionFactory",
    "hibernate.current_session_context_class": "thread"
]

def hibEntities = [
    TwitterAccountInfo,
    //AnotherEntity
]

def configureHibernate(props, entities) {
    def config = new AnnotationConfiguration()
    props.each { k, v -> config.setProperty(k, v) }
    entities.each { config.addAnnotatedClass(it) }
    return config
}

def factory = configureHibernate(hibProps, hibEntities).buildSessionFactory()

// store
def session = factory.currentSession
def tx = session.beginTransaction()
session.save(new TwitterAccountInfo(number:1, accountName:'Lacia', updateCount:1))
session.save(new TwitterAccountInfo(number:2, accountName:'Snowdrop', updateCount:3))
session.save(new TwitterAccountInfo(number:4, accountName:'Methode', updateCount:5))
tx.commit()

// find
session = factory.currentSession
tx = session.beginTransaction()
def info = session.createCriteria(TwitterAccountInfo.class).list()
//def info = session.createSQLQuery("select * from twitter_account_info").list()
tx.commit()

// display
println 'Found ' + info.size() + ' accounts:'
info.each { println it }
```

## 実行

DB作成

```mysql:database作成
create database db;
```

```bash:スクリプト実行
groovy db.groovy
```

## 出力

```bash
Hibernate: insert into twitter_account_info (account_name, update_count, id) values (?, ?, ?)
Hibernate: insert into twitter_account_info (account_name, update_count, id) values (?, ?, ?)
Hibernate: insert into twitter_account_info (account_name, update_count, id) values (?, ?, ?)
Hibernate: select this_.id as id0_0_, this_.account_name as account2_0_0_, this_.update_count as update3_0_0_ from twitter_account_info this_
Found 3 accounts:
id: 1
account_name: Lacia
update_count: 1
id: 2
account_name: Snowdrop
update_count: 3
id: 4
account_name: Methode
update_count: 5
```

## 補足
- hibernate使いました
- hibernateのオプションhibernate.hbm2ddl.autoはこんな感じ。

|値|用途|備考|
|---|---|---|
|none|何も弄らない|テーブル最初からあればこちら|
|update|テーブルが無ければEntityから作る|今回使いました|
|create|DB定義を別途用意する|sqlファイルを置いておくとテーブル作ってくれる|
|create-drop|同上|テーブル削除もやってくれる|

# 参考

http://groovy.jmiguel.eu/groovy.codehaus.org/Using+Hibernate+with+Groovy.html

http://trasis.jp/blog/lab/2008/09/hibernate-jpa%E7%92%B0%E5%A2%83%E6%A7%8B%E7%AF%89.html