Java Runtime と JDBC ドライバーだけで動作する O/R マッピング・ライブラリ Lightsleep の紹介

Lightsleepは、軽量のO/R (Object-Relational)マッピング･ライブラリで、Java 8以降で使用できます。

更新履歴

• 2017-01-14 - 3-1-15. SELECT COUNT(*)の例、SQLの例 (PostgrSQL, Oracle, SQL Server) を追加
• 2017-01-29 - version 1.7.0対応 (SQLite対応)
• 2017-02-11 - version 1.8.0対応 (@ColumnType, @ColumnTypeProperty追加)
• 2017-02-18 - build.gradleの例のSQL ServerのJDBCドライバの記述内容を修正
• 2017-02-26 - version 1.8.2対応
• 2017-04-16 - ダウンロードをリンクに変更
• 2017-06-05 - version 1.9.0対応 (DB2対応)
• 2017-09-09 - version 2.0.0対応, Groovyでの記述例を追加
• 2017-11-14 - version 2.1.0対応 (複数JDBC URL対応)
• 2017-12-06 - Jndiで複数データソースを指定するlightsleep.propertiesの記述例を追加など
• 2018-10-07 - version 3.0.0対応 (DateTime API型に対応)
• 2019-07-17 - version 3.1.0対応 (FROM句サブクエリ, UNION SQL作成に対応)
• 2019-09-25 - version 3.2.0対応 (MariaDB対応)

特徴

• Java 8で追加された機能(関数型インタフェース、Optionalクラス)を使用したAPI 。
• SQLを構築する際のメソッド名がSQLの予約語と同じため、直観的に理解しやすい。
• J2EEが不要(Java RuntimeとJDBCドライバー以外に依存するライブラリがない)なため、適用範囲が広い。
• XMLファイル等によるテーブルとJavaクラスとのマッピング用の定義ファイルは不要。
• ライブラリがコンパクトなため学習が容易。
• 各種のDBMSに同時に接続可能。(バージョン2.1.0~)
• 各種のコネクション･プール･ライブラリを同時に使用可能。(バージョン2.1.0~)
• 内部ログを各種のロギング･ライブラリから選択して出力可能。

リンク

• GitHub README
• GitHub ダウンロード

build.gradle

記述例:

build.gradle

apply plugin: 'java-library'

repositories {
    jcenter()
}

dependencies {
    compile 'org.lightsleep:lightsleep:3.2.0'
}

ロギング･ライブラリ、JDBCドライバー、コネクション･プール･ライブラリを追加した場合の記述例:

build.gradle

apply plugin: 'java-library'

repositories {
    jcenter()
}

dependencies {
    compile 'org.lightsleep:lightsleep:3.2.0'

    // ロギング･ライブラリを使用する場合
    runtimeOnly 'log4j:log4j:1.2.17'                            // Log4j
    runtimeOnly 'org.apache.logging.log4j:log4j-core:2.12.1'    // Log4j2
    runtimeOnly 'ch.qos.logback:logback-classic:1.2.3'          // LogBack

    // 以下のいずれかまたは他のJDBCドライバー
    runtimeOnly 'com.ibm.db2.jcc:db2jcc4:db2jcc4'               // DB2
    runtimeOnly 'org.mariadb.jdbc:mariadb-java-client:2.4.4'    // MariaDB
    runtimeOnly 'mysql:mysql-connector-java:8.0.17'             // MySQL
    runtimeOnly 'com.oracle.ojdbc:ojdbc8:19.3.0.0'              // Oracle
    runtimeOnly 'org.postgresql:postgresql:42.2.8'              // PostgreSQL
    runtimeOnly 'org.xerial:sqlite-jdbc:3.28.0'                 // SQLite
    runtimeOnly 'com.microsoft.sqlserver:mssql-jdbc:7.4.1.jre8' // SQLServer

    // コネクション･プール･ライブラリを使用する場合
    runtimeOnly 'com.mchange:c3p0:0.9.5.4'                      // C3p0
    runtimeOnly 'org.apache.commons:commons-dbcp2:2.7.0'        // Dbcp
    runtimeOnly 'com.zaxxer:HikariCP:3.4.1'                     // HikariCP
    runtimeOnly 'org.apache.tomcat:tomcat-jdbc:9.0.26'          // TomcatCP
}

使用方法

1. エンティティ･クラスの作成

テータベースの各テーブルに対応するエンティティ･クラス(データの入れ物)を定義します。

定義例:

Contact.java

package org.lightsleep.example.entity;

import java.sql.Date;
import org.lightsleep.entity.*;

public class Contact {
    @Key
    public int       id;
    public String    firstName;
    public String    lastName;
    public LocalDate birthday;
}

Phone.java

package org.lightsleep.example.entity;

import org.lightsleep.entity.*;

public class Phone {
    @Key
    public int    contactId;
    public short  childIndex;
    public String label;
    public String content;
}

Contact.groovy

package org.lightsleep.example.entity

import java.sql.Date
import org.lightsleep.entity.*

class Contact {
    @Key
    int       id
    String    firstName
    String    lastName
    LocalDate birthday
}

Phone.groovy

package org.lightsleep.example.entity

import org.lightsleep.entity.*

class Phone {
    @Key
    int    contactId
    short  childIndex
    String label
    String content
}

上記例では、publicフィールドを定義していますが、privateフィールドとpublic getter/setterメソッドの組み合わせても OK です。getterメソッドは、getプレフィックス以外にisまたはプレフィックスなしでも可能です。またsetterメソッドは、setプレフィックス付きまたはプレフィックスなしに対応しています。

Java

private int id;
public int getId() {return id;}
public void setId(int id) {this.id = id;}

Groovy

int id
int getId() {return id}
void setId(int id) {this.id = id}

クラス名と関連するテーブル名が異なる場合は、@Tableをクラスに付けます。

Java

import org.lightsleep.entity.*;

@Table("CONTACTS")
public class Contact {

Groovy

import org.lightsleep.entity.*

@Table('CONTACTS')
public class Contact {

プライマリ･キーのカラムに対応するフィールドには@Keyを付けます(複数可)。

カラム名とフィールド名が異なる場合は、@Columnをフィールドに付けます。

Java

import org.lightsleep.entity.*;
    ...
    @Column("LAST_NAME")
    public String lastName;

Groovy

import org.lightsleep.entity.*
    ...
    @Column('LAST_NAME')
    String lastName

カラムの型とフィールドの型が異なる種類の場合は、@ColumnTypeをフィールドに付けます。

Java

import org.lightsleep.entity.*;
    ...
    @ColumnType(Long.class)
    public LocalDate birthday;

Groovy

import org.lightsleep.entity.*
    ...
    @ColumnType(Long)
    LocalDate birthday

非staticなフィールドは、自動的にカラムと対応付けされるため、カラムと関連しないフィールドには@NonColumnを付けます。

Java

import java.util.ArrayList;
import java.util.List;
import org.lightsleep.entity.*;
    ...
    @NonColumn
    public List<Phone> phones = new ArrayList<>();

Groovy

import org.lightsleep.entity.*
    ...
    @NonColumn
    List<Phone> phones = []

SQLにフィールドの値ではなく式を指定する場合は、@Select, @Insert, @Updateを付けます。

またSQLに使用しない事を指定する場合は、@NonSelect, @NonInsert, @NonUpdateを付けます。

Java

import java.time.LocalDateTime;
import org.lightsleep.entity.*;
    ...
    @Insert("0")
    @Update("{updateCount}+1")
    public int updateCount;

    @Insert("CURRENT_TIMESTAMP")
    @NonUpdate
    public LocalDateTime createdTime;

    @Insert("CURRENT_TIMESTAMP")
    @Update("CURRENT_TIMESTAMP")
    public LocalDateTime updatedTime;

Groovy

import java.time.LocalDateTime
import org.lightsleep.entity.*
    ...
    @Insert('0')
    @Update('{updateCount}+1')
    int updateCount

    @Insert('CURRENT_TIMESTAMP')
    @NonUpdate
    LocalDateTime createdTime

    @Insert('CURRENT_TIMESTAMP')
    @Update('CURRENT_TIMESTAMP')
    LocalDateTime updatedTime

以下のアノテーションがあります。

アノテーション名	指定する内容	指定する対象
@Table	テーブル名	クラス
@Key	プライマリ･キーに対応	フィールド
@Column	カラム名	フィールド
@ColumnType	カラムの型	フィールド
@NonColumn	カラムに関連しない	フィールド
@NonSelect	SELECT SQLに使用しない	フィールド
@NonInsert	INSERT SQLに使用しない	フィールド
@NonUpdate	UPDATE SQLに使用しない	フィールド
@Select	SELECT SQLで使用する式	フィールド
@Insert	INSERT SQLで使用する式	フィールド
@Update	UPDATE SQLで使用する式	フィールド
@KeyProperty	プライマリ･キーに対応	クラス
@ColumnProperty	カラム名	クラス
@ColumnTypeProperty	カラムの型	クラス
@NonColumnProperty	カラムに関連しない	クラス
@NonSelectProperty	SELECT SQLに使用しない	クラス
@NonInsertProperty	INSERT SQLに使用しない	クラス
@NonUpdateProperty	UPDATE SQLに使用しない	クラス
@SelectProperty	SELECT SQLで使用する式	クラス
@InsertProperty	INSERT SQLで使用する式	クラス
@UpdateProperty	UPDATE SQLで使用する式	クラス

@XxxxxPropertyは、スーパークラスで定義されているフィールドに対して指定する場合に使用します。
同一アノテーションを1つのクラスに複数指定できます。

2. lightsleep.propertiesの定義

定義例:

lightsleep.properties

Logger      = Log4j2
#Database   = PostgreSQL  ※ バージョン2.1.0で廃止しました
ConnectionSupplier = Dbcp
url         = jdbc:postgresql://postgresqlserver/example
username    = example
password    = _example_
initialSize = 10
maxTotal    = 100

Loggerは、ログを出力する方法の指定で、以下から選択します。

指定内容	使用するロギング･ライブラリ	ログ･レベル	定義ファイル
Jdk	Java Runtime	－	logging.propertiesに定義
Log4j	Log4j 1.x.x	－	log4j.propertiesまたはlog4j.xmlに定義
Log4j2	Log4j 2.x.x	－	log4j2.xmlに定義
SLF4J	SLF4J	－	対象とするロギング･ライブラリ実装に依存
Std$Out$Trace	System.outに出力	trace	－
Std$Out$Debug	同上	debug	－
Std$Out$Info	同上	info	－
Std$Out$Warn	同上	warn	－
Std$Out$Error	同上	error	－
Std$Out$Fatal	同上	fatal	－
Std$Err$Trace	System.errに出力	trace	－
Std$Err$Debug	同上	debug	－
Std$Err$Info	同上	info	－
Std$Err$Warn	同上	warn	－
Std$Err$Error	同上	error	－
Std$Err$Fatal	同上	fatal	－

指定がない場合は、Std$Out$Infoが選択されます。

Databaseは、対象とするDBMSの指定で、以下から選択します。

上記以外のDBMSを使用する場合は、指定しないかStandardを指定します。

※バージョン2.1.0で廃止しました。JDBC URLの内容から自動的に選択するようにしました。

ConnectionSupplierは、使用するコネクション･サプライヤ(コネクション･プール等)の指定で以下から選択します。

指定内容	コネクション･サプライヤ
C3p0	c3p0
Dbcp	Apache Commons DBCP
HikariCP	HikariCP
TomcatCP	Tomcat JDBC Connection Pool
Jndi	Java Naming and Directory Interface (JNDI) (Tomcat の場合)
Jdbc	DriverManager#getConnection(String url, Properties info) メソッド

上記lightsleep.propertiesの定義例のConnectionSupplierより下(url~maxTotal)は、各コネクション･サプライヤに渡す定義内容です。

定義例(Std$Out$Info/DB2/Jdbc):

lightsleep.properties

Logger    = Std$Out$Info
#Database = DB2  ※ バージョン2.1.0で廃止しました
ConnectionSupplier = Jdbc
url       = jdbc:db2://db2:50000/example

定義例(Jdk/MySQL/C3p0):

lightsleep.properties

Logger    = Jdk
#Database = MySQL  ※ バージョン2.1.0で廃止しました
ConnectionSupplier = C3p0
url       = jdbc:mysql://mysql:3306/example
user      = example
password  = _example_

url, user, password以外の定義は、c3p0.propertiesまたはc3p0-config.xmlに記述します。

定義例(Log4j/Oracle/Dbcp):

lightsleep.properties

Logger      = Log4j
#Database   = Oracle  ※ バージョン2.1.0で廃止しました
ConnectionSupplier = Dbcp
url         = jdbc:oracle:thin:@oracle:1521:example
username    = example
password    = _example_
initialSize = 10
maxTotal    = 100
   ...

定義例(Log4j2/PostgreSQL/HikariCP):

lightsleep.properties

Logger          = Log4j2
#Database       = PostgreSQL  ※ バージョン2.1.0で廃止しました
ConnectionSupplier = HikariCP
jdbcUrl         = jdbc:postgresql://postgresql:5432/example
username        = example
password        = _example_
minimumIdle     = 10
maximumPoolSize = 100
   ...

定義例(SLF4J/SQLServer/TomcatCP):

lightsleep.properties

Logger      = SLF4J
#Database   = SQLServer  ※ バージョン2.1.0で廃止しました
ConnectionSupplier = TomcatCP
url         = jdbc:sqlserver://sqlserver:1433;database=example
username    = example
password    = _example_
initialSize = 10
maxActive   = 100
   ...

定義例(Log4j/MySQL/Jndi):

lightsleep.properties

Logger             = Log4j
#Database           = MySQL  ※ バージョン2.1.0で廃止しました
ConnectionSupplier = Jndi
dataSource         = jdbc/example

複数のJDBC URLの定義例1

(バージョン2.1.0~)

lightsleep.properties

# 複数のJDBC URLを指定する場合
Logger      = Log4j2
ConnectionSupplier = Dbcp
urls        = jdbc:postgresql://postgresql:5432/example,\
              jdbc:postgresql://postgresql:5433/example
user        = example
password    = _example_
initialSize = 10
maxTotal    = 100

複数のJDBC URLの定義例2

(バージョン2.1.0~)

lightsleep.properties

# 複数のDBMSを使用する場合(URL内でユーザーとパスワードを指定)
Logger = Log4j2
ConnectionSupplier = Dbcp
urls = \
    jdbc:db2://db2:50000/example:user=example;password=_example_;,\
    jdbc:mariadb://mariadb:3306/example?user=example&password=_example_,\
    jdbc:mysql://mysql:3306/example?user=example&password=_example_,\
    jdbc:oracle:thin:example/_example_@oracle:1521:example,\
    jdbc:postgresql://postgresql:5432/example?user=example&password=_example_,\
    jdbc:sqlite:C:/sqlite/example,\
    jdbc:sqlserver://sqlserver:1433;database=example;user=example;password=_example_,\
      <空行>
initialSize = 10
maxTotal    = 100

複数のJDBC URLの定義例3

(バージョン2.1.0~)

lightsleep.properties

# URL毎にコネクション･サプライヤを指定する場合
Logger = Log4j2
urls = \
    [  Jdbc  ]jdbc:db2://db2:50000/example:user=example;password=_example_;,\
    [  C3p0  ]jdbc:mariadb://mariadb:3306/example?user=example&password=_example_,\
    [  Dbcp  ]jdbc:mysql://mysql:3306/example?user=example&password=_example_,\
    [HikariCP]jdbc:oracle:thin:example/_example_@oracle:1521:example,\
    [TomcatCP]jdbc:postgresql://postgresql:5432/example?user=example&password=_example_,\
    [  Jdbc  ]jdbc:sqlite:C:/sqlite/example,\
    [  C3p0  ]jdbc:sqlserver://sqlserver:1433;database=example;user=example;password=_example_,\
      <空行>
# Dbcp, HikariCP, TomcatCP
initialSize = 10

# Dbcp
maxTotal    = 10

# TomcatCP
maxActive   = 10

# HikariCP
minimumIdle     = 10
maximumPoolSize = 10

複数のデータソースの定義例4

(バージョン2.1.0~) (2017-12-06追記)

dataSource/dataSourcesの定義の場合、url/urlsと異なりデータベース･ハンドラを識別するキーワードが含まれない(場合もある)ため、最初に取得したConnectionオブジェクトのDatabaseMetaDataからURLを取得して判定しています。 (バージョン2.1.1~)

lightsleep.properties

# Jndiを使用する場合
Logger = Log4j2
ConnectionSupplier = Jndi
dataSources = \
    jdbc/db2_example,\
    jdbc/mariadb_example,\
    jdbc/mysql_example,\
    jdbc/oracle_example,\
    postgresql_example,\
    sqlite_example,\
    sqlserver_example,\
      <空行>
#   ↑"jdbc/"はあってもなくても可

3. データベース･アクセス

Transaction.executeメソッドの実行が1つのトランザクションの実行に相当します。
トランザクションの内容は、引数transaction(ラムダ式) で定義します。
ラムダ式は、Transaction.executeBodyメソッドの内容に相当し、このメソッドの引数は、Connectionです。

Java

Contact contact = new Contact(1, "Akane", "Apple");

// トランザクション例
Transaction.execute(conn -> {
    // トランザクション開始
    new Sql<>(Contact.class).connection(conn)
        .insert(contact);
   ...
    // トランザクション終了
});

Groovy

def contact = new Contact(1, 'Akane', 'Apple')

// トランザクション例
Transaction.execute {
    // トランザクション開始
    new Sql<>(Contact).connection(it)
        .insert(contact)
    ...
    // トランザクション終了
}

複数のJDBC URL(またはデータソース)をlightsleep.propertiesに定義した場合は、どのURL(またはデータソース)に対してトランザクションの実行を行うかを指定する必要があります。ConnectionSupplier.findメソッドは、引数の文字列配列(可変引数)のすべてが含まれるURL(またはデータソース)を検索します。
複数見つかった場合または見つからない場合は例外がスローされます。(バージョン2.1.0~)

lightsleep.properties

urls = jdbc:postgresql://postgresql:5432/example1,\
       jdbc:postgresql://postgresql:5432/example2,\
       jdbc:postgresql://postgresql:5433/example1,\
       jdbc:postgresql://postgresql:5433/example2

Java

public static final ConnectionSupplier supplier1 = ConnectionSupplier.find("5432", "example1");
public static final ConnectionSupplier supplier2 = ConnectionSupplier.find("5432", "example2");
public static final ConnectionSupplier supplier3 = ConnectionSupplier.find("5433", "example1");
public static final ConnectionSupplier supplier4 = ConnectionSupplier.find("5433", "example2");
    ...

Contact contact = new Contact(1, "Akane", "Apple");

// トランザクション例
Transaction.execute(supplier1, conn -> {
    // トランザクション開始
    new Sql<>(Contact.class).connection(conn)
        .insert(contact);
   ...
    // トランザクション終了
});

Groovy

static final supplier1 = ConnectionSupplier.find('5432', 'example1')
static final supplier2 = ConnectionSupplier.find('5432', 'example1')
static final supplier3 = ConnectionSupplier.find('5433', 'example1')
static final supplier4 = ConnectionSupplier.find('5433', 'example1')
    ...

def contact = new Contact(1, 'Akane', 'Apple')

// トランザクション例
Transaction.execute(supplier1) {
    // トランザクション開始
    new Sql<>(Contact).connection(it)
        .insert(contact)
    ...
    // トランザクション終了
}

トランザクション中に例外がスローされた場合は、Transaction.rollbackメソッドが実行され、
そうでなければTransaction.commitメソッドが実行されます。

以下JavaおよびGroovyでの記述例と生成されるSQLです。生成されるSQLは、見やすくするため改行を入れてありますが、実際には改行はありません。

3-1. SELECT

3-1-1. SELECT 1行/式条件

Java

Transaction.execute(conn -> {
    Optional<Contact> contactOpt = new Sql<>(Contact.class)
        .where("{id}={}", 1)
        .connection(conn)
        .select();
});

Groovy

Transaction.execute {
    def contactOpt = new Sql<>(Contact)
        .where('{id}={}', 1)
        .connection(it)
        .select()
}

SQL

SELECT id, firstName, lastName, birthday, updateCount, createdTime, updatedTime
  FROM Contact
  WHERE id=1

3-1-2. SELECT 1行/エンティティ条件

Java

Contact contact = new Contact();
contact.id = 1;
Transaction.execute(conn -> {
    Optional<Contact> contactOpt = new Sql<>(Contact.class)
        .where(contact)
        .connection(conn)
        .select();
});

Groovy

def contact = new Contact()
contact.id = 1
Transaction.execute {
    def contactOpt = new Sql<>(Contact)
        .where(contact)
        .connection(it)
        .select()
}

SQL

SELECT id, firstName, lastName, birthday, updateCount, createdTime, updatedTime
  FROM Contact
  WHERE id=1

3-1-3. SELECT 複数行/式条件

Java

List<Contact> contacts = new ArrayList<Contact>();
Transaction.execute(conn ->
    new Sql<>(Contact.class)
        .where("{lastName}={}", "Apple")
        .connection(conn)
        .select(contacts::add)
);

Groovy

List<Contact> contacts = []
Transaction.execute {
    new Sql<>(Contact)
        .where('{lastName}={}', 'Apple')
        .connection(it)
        .select({contacts << it})
}

SQL

SELECT id, firstName, lastName, birthday, updateCount, createdTime, updatedTime
  FROM Contact
  WHERE lastName='Apple'

3-1-4. SELECT サブクエリ条件

Java

List<Contact> contacts = new ArrayList<Contact>();
Transaction.execute(conn ->
    new Sql<>(Contact.class, "C")
        .where("EXISTS",
            new Sql<>(Phone.class, "P")
                .where("{P.contactId}={C.id}")
        )
        .connection(conn)
        .select(contacts::add)
);

Groovy

List<Contact> contacts = []
Transaction.execute {
    new Sql<>(Contact, 'C')
        .where('EXISTS',
            new Sql<>(Phone, 'P')
                .where('{P.contactId}={C.id}')
        )
        .connection(it)
        .select({contacts << it})
}

SQL

SELECT C.id C_id, C.firstName C_firstName, C.lastName C_lastName, C.birthday C_birthday,
       C.updateCount C_updateCount, C.createdTime C_createdTime, C.updatedTime C_updatedTime
  FROM Contact C
  WHERE EXISTS (SELECT * FROM Phone P WHERE P.contactId=C.id)

3-1-5. SELECT 式条件/AND

Java

List<Contact> contacts = new ArrayList<Contact>();
Transaction.execute(conn ->
    new Sql<>(Contact.class)
        .where("{lastName}={}", "Apple")
        .and  ("{firstName}={}", "Akane")
        .connection(conn)
        .select(contacts::add)
);

Groovy

List<Contact> contacts = []
Transaction.execute {
    new Sql<>(Contact)
        .where('{lastName}={}', 'Apple')
        .and  ('{firstName}={}', 'Akane')
        .connection(it)
        .select({contacts << it})
}

SQL

SELECT id, firstName, lastName, birthday, updateCount, createdTime, updatedTime
  FROM Contact
  WHERE lastName='Apple' AND firstName='Akane'

3-1-6. SELECT 式条件/OR

Java

List<Contact> contacts = new ArrayList<Contact>();
Transaction.execute(conn ->
    new Sql<>(Contact.class)
        .where("{lastName}={}", "Apple")
        .or   ("{lastName}={}", "Orange")
        .connection(conn)
        .select(contacts::add)
);

Groovy

List<Contact> contacts = []
Transaction.execute {
    new Sql<>(Contact)
        .where('{lastName}={}', 'Apple')
        .or   ('{lastName}={}', 'Orange')
        .connection(it)
        .select({contacts << it})
}

SQL

SELECT id, firstName, lastName, birthday, updateCount, createdTime, updatedTime
  FROM Contact
  WHERE lastName='Apple' OR lastName='Orange'

3-1-7. SELECT 式条件/(A AND B) OR (C AND D)

Java

List<Contact> contacts = new ArrayList<Contact>();
Transaction.execute(conn ->
    new Sql<>(Contact.class)
        .where(Condition
            .of ("{lastName}={}", "Apple")
            .and("{firstName}={}", "Akane")
        )
        .or(Condition
            .of ("{lastName}={}", "Orange")
            .and("{firstName}={}", "Setoka")
        )
        .connection(conn)
        .select(contacts::add)
);

Groovy

List<Contact> contacts = []
Transaction.execute(conn ->
    new Sql<>(Contact)
        .where(Condition
            .of ('{lastName}={}', 'Apple')
            .and('{firstName}={}', 'Akane')
        )
        .or(Condition
            .of ('{lastName}={}', 'Orange')
            .and('{firstName}={}', 'Setoka')
        )
        .connection(it)
        .select({contacts << it})
);

SQL

SELECT id, firstName, lastName, birthday, updateCount, createdTime, updatedTime
  FROM Contact
  WHERE lastName='Apple' AND firstName='Akane'
    OR lastName='Orange' AND firstName='Setoka'

3-1-8. SELECT カラムの選択

Java

List<Contact> contacts = new ArrayList<Contact>();
Transaction.execute(conn ->
    new Sql<>(Contact.class)
        .where("{lastName}={}", "Apple")
        .columns("lastName", "firstName")
        .connection(conn)
        .select(contacts::add)
);

Groovy

List<Contact> contacts = []
Transaction.execute {
    new Sql<>(Contact)
        .where('{lastName}={}', 'Apple')
        .columns('lastName', 'firstName')
        .connection(it)
        .select({contacts << it})
}

SQL

SELECT firstName, lastName FROM Contact WHERE lastName='Apple'

3-1-9. SELECT GROUP BY, HAVING

Java

List<Contact> contacts = new ArrayList<Contact>();
Transaction.execute(conn ->
    new Sql<>(Contact.class, "C")
        .columns("lastName")
        .groupBy("{lastName}")
        .having("COUNT({lastName})>=2")
        .connection(conn)
        .select(contacts::add)
);

Groovy

List<Contact> contacts = []
Transaction.execute {
    new Sql<>(Contact, 'C')
        .columns('lastName')
        .groupBy('{lastName}')
        .having('COUNT({lastName})>=2')
        .connection(it)
        .select({contacts << it})
}

SQL

SELECT MIN(C.lastName) C_lastName
  FROM Contact C
  GROUP BY C.lastName
  HAVING COUNT(C.lastName)>=2

3-1-10. SELECT ORDER BY, OFFSET, LIMIT

Java

List<Contact> contacts = new ArrayList<Contact>();
Transaction.execute(conn ->
    new Sql<>(Contact.class)
        .orderBy("{lastName}")
        .orderBy("{firstName}")
        .orderBy("{id}")
        .offset(10).limit(5)
        .connection(conn)
        .select(contacts::add)
);

Groovy

List<Contact> contacts = []
Transaction.execute {
    new Sql<>(Contact)
        .orderBy('{lastName}')
        .orderBy('{firstName}')
        .orderBy('{id}')
        .offset(10).limit(5)
        .connection(it)
        .select({contacts << it})
}

SQL

-- DB2, MariaDB, MySQL, PostgreSQL, SQLite
SELECT id, firstName, lastName, birthday, updateCount, createdTime, updatedTime
  FROM Contact
  ORDER BY lastName ASC, firstName ASC, id ASC
  LIMIT 5 OFFSET 10

SQL

-- Oracle, SQLServer(取得時に行をスキップする)
SELECT id, firstName, lastName, birthday, updateCount, createdTime, updatedTime
  FROM Contact
  ORDER BY lastName ASC, firstName ASC, id ASC

3-1-11. SELECT FOR UPDATE

Java

Transaction.execute(conn -> {
    Optional<Contact> contactOpt = new Sql<>(Contact.class)
        .where("{id}={}", 1)
        .forUpdate()
        .connection(conn)
        .select();
});

Groovy

Transaction.execute {
    def contactOpt = new Sql<>(Contact)
        .where('{id}={}', 1)
        .forUpdate()
        .connection(it)
        .select()
}

SQL

-- DB2
SELECT id, firstName, lastName, birthday, updateCount, createdTime, updatedTime
  FROM Contact
  WHERE id=1
  FOR UPDATE WITH RS

SQL

-- MariaDB, MySQL, Oracle, PostgreSQL, SQLite
SELECT id, firstName, lastName, birthday, updateCount, createdTime, updatedTime
  FROM Contact
  WHERE id=1
  FOR UPDATE

SQL

-- SQLite
-- SQLiteはFOR UPDATE をサポートしていないので、UnsupportedOperationExceptionがスローされます。

SQL

-- SQLServer
SELECT id, firstName, lastName, birthday, updateCount, createdTime, updatedTime
  FROM Contact
  WITH (ROWLOCK,UPDLOCK)
  WHERE id=1

3-1-12. SELECT 内部結合

Java

List<Contact> contacts = new ArrayList<>();
List<Phone> phones = new ArrayList<>();
Transaction.execute(conn ->
    new Sql<>(Contact.class, "C")
        .innerJoin(Phone.class, "P", "{P.contactId}={C.id}")
        .where("{C.id}={}", 1)
        .connection(conn)
        .<Phone>select(contacts::add, phones::add)
);

Groovy

List<Contact> contacts = []
List<Phone> phones = []
Transaction.execute {
    new Sql<>(Contact, 'C')
        .innerJoin(Phone, 'P', '{P.contactId}={C.id}')
        .where('{C.id}={}', 1)
        .connection(it)
        .select({contacts << it}, {phones << it})
}

SQL

SELECT C.id C_id, C.lastName C_lastName, C.firstName C_firstName, C.birthday C_birthday,
       C.updateCount C_updateCount, C.createdTime C_createdTime, C.updatedTime C_updatedTime,
       P.contactId P_contactId, P.childIndex P_childIndex, P.label P_label, P.content P_content
  FROM Contact C
  INNER JOIN Phone P ON P.contactId=C.id WHERE C.id=1

3-1-13. SELECT 左外部結合

Java

List<Contact> contacts = new ArrayList<>();
List<Phone> phones = new ArrayList<>();
Transaction.execute(conn ->
    new Sql<>(Contact.class, "C")
        .leftJoin(Phone.class, "P", "{P.contactId}={C.id}")
        .where("{C.lastName}={}", "Apple")
        .connection(conn)
        .<Phone>select(contacts::add, phones::add)
);

Groovy

List<Contact> contacts = []
List<Phone> phones = []
Transaction.execute {
    new Sql<>(Contact, 'C')
        .leftJoin(Phone, 'P', '{P.contactId}={C.id}')
        .where('{C.lastName}={}', 'Apple')
        .connection(it)
        .select({contacts << it}, {phones << it})
}

SQL

SELECT C.id C_id, C.lastName C_lastName, C.firstName C_firstName, C.birthday C_birthday,
       C.updateCount C_updateCount, C.createdTime C_createdTime, C.updatedTime C_updatedTime,
       P.contactId P_contactId, P.childIndex P_childIndex, P.label P_label, P.content P_content
  FROM Contact C
  LEFT OUTER JOIN Phone P ON P.contactId=C.id
  WHERE C.lastName='Apple'

3-1-14. SELECT 右外部結合

Java

List<Contact> contacts = new ArrayList<>();
List<Phone> phones = new ArrayList<>();
Transaction.execute(conn ->
    new Sql<>(Contact.class, "C")
        .rightJoin(Phone.class, "P", "{P.contactId}={C.id}")
        .where("{P.label}={}", "Main")
        .connection(conn)
        .<Phone>select(contacts::add, phones::add)
);

Groovy

List<Contact> contacts = []
List<Phone> phones = []
Transaction.execute {
    new Sql<>(Contact, 'C')
        .rightJoin(Phone, 'P', '{P.contactId}={C.id}')
        .where('{P.label}={}', 'Main')
        .connection(it)
        .select({contacts << it}, {phones << it})
}

SQL

-- SQLite では、RIGHT OUTER JOIN が未サポートのため、例外がスローされます。
SELECT C.id C_id, C.lastName C_lastName, C.firstName C_firstName, C.birthday C_birthday,
       C.updateCount C_updateCount, C.createdTime C_createdTime, C.updatedTime C_updatedTime,
       P.contactId P_contactId, P.childIndex P_childIndex, P.label P_label, P.content P_content
  FROM Contact C
  RIGHT OUTER JOIN Phone P ON P.contactId=C.id
  WHERE P.label='Main'

3-1-15. SELECT COUNT(*)

Java

int[] rowCount = new int[1];
Transaction.execute(conn ->
    count[0] = new Sql<>(Contact.class)
        .where("lastName={}", "Apple")
        .connection(conn)
        .selectCount()
);

Groovy

def rowCount = 0
Transaction.execute {
    count = new Sql<>(Contact)
        .where('lastName={}', 'Apple')
        .connection(it)
        .selectCount()
}

SQL

SELECT COUNT(*) FROM Contact WHERE lastName='Apple'

3-2. INSERT

3-2-1. INSERT 1行

Java

Transaction.execute(conn ->
    new Sql<>(Contact.class)
        .connection(conn)
        .insert(new Contact(1, "Akane", "Apple", 2001, 1, 1))

Groovy

Transaction.execute {
    new Sql<>(Contact)
        .connection(it)
       .insert(new Contact(1, "Akane", "Apple", 2001, 1, 1))
}

SQL

-- DB2, MariaDB, MySQL, Oracle, PostgreSQL
INSERT INTO Contact
  (id, firstName, lastName, birthday, updateCount, createdTime, updatedTime)
  VALUES
  (1, 'Apple', 'Akane', DATE'2001-01-01', 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)

SQL

-- SQLite
INSERT INTO Contact
  (id, firstName, lastName, birthday, updateCount, createdTime, updatedTime)
  VALUES
  (1, 'Apple', 'Akane', '2001-01-01', 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)

SQL

-- SQLServer
INSERT INTO Contact
  (id, firstName, lastName, birthday, updateCount, createdTime, updatedTime)
  VALUES
  (1, 'Apple', 'Akane', CAST('2001-01-01' AS DATE), 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)

3-2-2. INSERT 複数行

Java

Transaction.execute(conn ->
    new Sql<>(Contact.class)
        .connection(conn)
        .insert(Arrays.asList(
            new Contact(2, "Yukari", "Apple", 2001, 1, 2),
            new Contact(3, "Azusa", "Apple", 2001, 1, 3)
        ))

Groovy

Transaction.execute {
    new Sql<>(Contact)
        .connection(it)
        .insert([
            new Contact(2, "Yukari", "Apple", 2001, 1, 2),
            new Contact(3, "Azusa", "Apple", 2001, 1, 3)
        ])
}

SQL

-- DB2, MariaDB, MySQL, Oracle, PostgreSQL
INSERT INTO Contact
  (id, firstName, lastName, birthday, updateCount, createdTime, updatedTime)
  VALUES
  (2, 'Apple', 'Yukari', DATE'2001-01-02', 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
INSERT INTO Contact
  (id, firstName, lastName, birthday, updateCount, createdTime, updatedTime)
  VALUES
  (3, 'Apple', 'Azusa', DATE'2001-01-03', 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)

SQL

-- SQLite
INSERT INTO Contact (id, firstName, lastName, birthday, updateCount, createdTime, updatedTime)
  VALUES
  (2, 'Apple', 'Yukari', '2001-01-02', 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
INSERT INTO Contact (id, firstName, lastName, birthday, updateCount, createdTime, updatedTime)
  VALUES
  (3, 'Apple', 'Azusa', '2001-01-03', 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)

SQL

-- SQLServer
INSERT INTO Contact
  (id, firstName, lastName, birthday, updateCount, createdTime, updatedTime)
  VALUES
  (2, 'Apple', 'Yukari', CAST('2001-01-02' AS DATE), 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
INSERT INTO Contact
  (id, firstName, lastName, birthday, updateCount, createdTime, updatedTime)
  VALUES
  (3, 'Apple', 'Azusa', CAST('2001-01-03' AS DATE), 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)

3-3. UPDATE

3-3-1. UPDATE 1行

Java

Transaction.execute(conn ->
    new Sql<>(Contact.class)
        .where("{id}={}", 1)
        .connection(conn)
        .select()
        .ifPresent(contact -> {
            contact.firstName = "Akiyo";
            new Sql<>(Contact.class)
                .connection(conn)
                .update(contact);
        })
);

Groovy

Transaction.execute {
    new Sql<>(Contact)
        .where('{id}={}', 1)
        .connection(it)
        .select()
        .ifPresent {Contact contact ->
            contact.firstName = 'Akiyo'
            new Sql<>(Contact)
                .connection(it)
                .update(contact)
        }
}

SQL

-- DB2, MariaDB, MySQL, Oracle, PostgreSQL
SELECT id, firstName, lastName, birthday, updateCount, createdTime, updatedTime
  FROM Contact WHERE id=1
UPDATE Contact SET
  lastName='Apple', firstName='Akiyo', birthday=DATE'2001-01-01',
  updateCount=updateCount+1, updatedTime=CURRENT_TIMESTAMP
  WHERE id=1

SQL

-- SQLite
SELECT id, firstName, lastName, birthday, updateCount, createdTime, updatedTime
  FROM Contact WHERE id=1
UPDATE Contact SET
  lastName='Apple', firstName='Akiyo', birthday='2001-01-01',
  updateCount=updateCount+1, updatedTime=CURRENT_TIMESTAMP
  WHERE id=1

SQL

-- SQLServer
SELECT id, firstName, lastName, birthday, updateCount, createdTime, updatedTime
  FROM Contact WHERE id=1
UPDATE Contact SET
  lastName='Apple', firstName='Akiyo', birthday=CAST('2001-01-01' AS DATE),
  updateCount=updateCount+1, updatedTime=CURRENT_TIMESTAMP
  WHERE id=1

3-3-2. UPDATE 複数行

Java

Transaction.execute(conn -> {
    List<Contact> contacts = new ArrayList<>();
    new Sql<>(Contact.class)
        .where("{lastName}={}", "Apple")
        .connection(conn)
        .select(contact -> {
            contact.lastName = "Apfel";
            contacts.add(contact);
        });
    new Sql<>(Contact.class)
        .connection(conn)
        .update(contacts);
});

Groovy

Transaction.execute {
    List<Contact> contacts = []
    new Sql<>(Contact)
        .where('{lastName}={}', 'Apple')
        .connection(it)
        .select({Contact contact ->
            contact.lastName = 'Apfel'
            contacts << contact
        })
    new Sql<>(Contact)
        .connection(it)
        .update(contacts)
}

SQL

-- DB2, MariaDB, MySQL, Oracle, PostgreSQL
SELECT id, firstName, lastName, birthday, updateCount, createdTime, updatedTime
  FROM Contact WHERE lastName='Apple'
UPDATE Contact SET
  firstName='Akiyo', lastName='Apfel', birthday=DATE'2001-01-01',
  updateCount=updateCount+1, updatedTime=CURRENT_TIMESTAMP
  WHERE id=1
UPDATE Contact SET
  firstName='Yukari', lastName='Apfel', birthday=DATE'2001-01-02',
  updateCount=updateCount+1, updatedTime=CURRENT_TIMESTAMP
  WHERE id=2
UPDATE Contact SET
  firstName='Azusa', lastName='Apfel', birthday=DATE'2001-01-03',
  updateCount=updateCount+1, updatedTime=CURRENT_TIMESTAMP
  WHERE id=3

SQL

-- SQLite
SELECT id, firstName, lastName, birthday, updateCount, createdTime, updatedTime
 FROM Contact WHERE lastName='Apple'
UPDATE Contact SET
  firstName='Akiyo', lastName='Apfel', birthday='2001-01-01',
  updateCount=updateCount+1, updatedTime=CURRENT_TIMESTAMP
  WHERE id=1
UPDATE Contact SET
  firstName='Yukari', lastName='Apfel', birthday='2001-01-02',
  updateCount=updateCount+1, updatedTime=CURRENT_TIMESTAMP
  WHERE id=2
UPDATE Contact SET
  firstName='Azusa', lastName='Apfel', birthday='2001-01-03',
  updateCount=updateCount+1, updatedTime=CURRENT_TIMESTAMP
  WHERE id=3

SQL

-- SQLServer
SELECT id, firstName, lastName, birthday, updateCount, createdTime, updatedTime
  FROM Contact WHERE lastName='Apple'
UPDATE Contact SET
  firstName='Akiyo', lastName='Apfel', birthday=CAST('2001-01-01' AS DATE),
  updateCount=updateCount+1, updatedTime=CURRENT_TIMESTAMP
  WHERE id=1
UPDATE Contact SET
  firstName='Yukari', lastName='Apfel', birthday=CAST('2001-01-02' AS DATE),
  updateCount=updateCount+1, updatedTime=CURRENT_TIMESTAMP
  WHERE id=2
UPDATE Contact SET
  firstName='Azusa', lastName='Apfel', birthday=CAST('2001-01-03' AS DATE),
  updateCount=updateCount+1, updatedTime=CURRENT_TIMESTAMP
  WHERE id=3

3-3-3. UPDATE 指定条件, カラム選択

Java

Contact contact = new Contact();
contact.lastName = "Pomme";
Transaction.execute(conn ->
    new Sql<>(Contact.class)
        .where("{lastName}={}", "Apfel")
        .columns("lastName")
        .connection(conn)
        .update(contact)
);

Groovy

def contact = new Contact()
contact.lastName = 'Pomme'
Transaction.execute {
    new Sql<>(Contact)
        .where('{lastName}={}', 'Apfel')
        .columns('lastName')
        .connection(it)
        .update(contact)
}

SQL

UPDATE Contact SET lastName='Pomme' WHERE lastName='Apfel'

3-3-4. UPDATE 全行

Java

Contact contact = new Contact();
Transaction.execute(conn ->
    new Sql<>(Contact.class)
        .where(Condition.ALL)
        .columns("birthday")
        .connection(conn)
        .update(contact)
);

Groovy

def contact = new Contact()
Transaction.execute {
    new Sql<>(Contact)
        .where(Condition.ALL)
        .columns('birthday')
        .connection(it)
        .update(contact)
}

SQL

UPDATE Contact SET birthday=NULL

3-4. DELETE

3-4-1. DELETE 1行

Java

Transaction.execute(conn ->
    new Sql<>(Contact.class)
        .where("{id}={}", 1)
        .connection(conn)
        .select()
        .ifPresent(contact ->
            new Sql<>(Contact.class)
                .connection(conn)
                .delete(contact))
);

Groovy

Transaction.execute {
    new Sql<>(Contact)
        .where('{id}={}', 1)
        .connection(it)
        .select()
        .ifPresent {contact ->
            new Sql<>(Contact)
                .connection(it)
                .delete(contact)
        }
}

SQL

SELECT id, firstName, lastName, birthday, updateCount, createdTime, updatedTime
  FROM Contact WHERE id=1
DELETE FROM Contact WHERE id=1

3-4-2. DELETE 複数行

Java

Transaction.execute(conn -> {
    List<Contact> contacts = new ArrayList<>();
    new Sql<>(Contact.class)
        .where("{lastName}={}", "Pomme")
        .connection(conn)
        .select(contacts::add);
    new Sql<>(Contact.class)
        .connection(conn)
        .delete(contacts);
});

Groovy

Transaction.execute {
    List<Contact> contacts = []
    new Sql<>(Contact)
        .where('{lastName}={}', 'Pomme')
        .connection(it)
        .select({contacts << it})
    new Sql<>(Contact)
        .connection(it)
        .delete(contacts)
}

SQL

SELECT id, firstName, lastName, birthday, updateCount, createdTime, updatedTime
  FROM Contact WHERE lastName='Pomme'
DELETE FROM Contact WHERE id=2
DELETE FROM Contact WHERE id=3

3-4-3. DELETE 指定条件

Java

Transaction.execute(conn ->
    new Sql<>(Contact.class)
        .where("{lastName}={}", "Orange")
        .connection(conn)
        .delete()
);

Groovy

Transaction.execute {
    new Sql<>(Contact)
        .where('{lastName}={}', 'Orange')
        .connection(it)
        .delete()
}

SQL

DELETE FROM Contact WHERE lastName='Orange'

3-4-4. DELETE 全行

Java

Transaction.execute(conn ->
    new Sql<>(Phone.class)
        .where(Condition.ALL)
        .connection(conn)
        .delete()
);

Groovy

Transaction.execute {
    new Sql<>(Phone)
        .where(Condition.ALL)
        .connection(it)
        .delete()
}

SQL

DELETE FROM Phone