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

概要

方針

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

試した環境

• MySQL 5.5
• Debian 7.5
• Groovy 2.4.5
• Java 1.7.0_40

ソース

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作成

database作成

create database db;

スクリプト実行

groovy db.groovy

出力

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	同上	テーブル削除もやってくれる

参考