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~)
- 内部ログを各種のロギング･ライブラリから選択して出力可能。
リンク
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