Introducing Lightsleep, an O / R mapping library that works only with Java Runtime and JDBC drivers

Lightsleep is a lightweight O / R (Object-Relational) mapping library available in Java 8 and above.

Change log

--2017-01-14 --3-1-15. Added SELECT COUNT () example and SQL example (PostgrSQL, Oracle, SQL Server) --2017-01-29- version 1.7.0 * compatible (SQLite compatible) --2017-02-11-* Version 1.8.0 * supported (@ColumnType, @ColumnTypeProperty added) --2017-02-18 --Corrected the description of the JDBC driver for SQL Server in the build.gradle example. --2017-02-26-* version 1.8.2 * compatible --2017-04-16 --Change download to link --2017-06-05-* version 1.9.0 * compatible (DB2 compatible) --2017-09-09-* version 2.0.0 * compatible, added description example in Groovy --2017-11-14-* version 2.1.0 * supported (multiple JDBC URLs supported) --2017-12-06 --Added a description example of lightsleep.properties that specifies multiple data sources in Jndi. --2018-10-07-* version 3.0.0 * supported (DateTime API type supported) --2019-07-17-* version 3.1.0 * supported (FROM clause subquery, UNION SQL creation supported) --2019-09-25-* version 3.2.0 * compatible (MariaDB compatible)

Feature

--API using features added in Java 8 (functional interface, Optional class). --Since the method name when constructing SQL is the same as the reserved word of SQL, it is easy to understand intuitively. --Since J2EE is not required (there is no library that depends on anything other than Java Runtime and JDBC driver), the scope of application is wide. --No definition file for mapping table and Java class by XML file etc. is required. --Since the library is compact, learning is easy. --You can connect to various DBMSs at the same time. ** (Version 2.1.0 ~) ** --Various connection pool libraries can be used at the same time. ** (Version 2.1.0 ~) ** --Internal logs can be selected and output from various logging libraries.

Link

• GitHub README - GitHub download

build.gradle

Description example:

build.gradle

apply plugin: 'java-library'

repositories {
    jcenter()
}

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

Description example when the logging library, JDBC driver, and connection pool library are added:

build.gradle

apply plugin: 'java-library'

repositories {
    jcenter()
}

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

    //When using the logging library
    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

    //One of the following or other JDBC drivers
    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

    //When using a connection pool library
    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
}

how to use

1. Creating an entity class

Define an entity class (a container for data) for each table in the data base.

Definition example:

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
}

In the above example, the public field is defined, but it is OK to combine the private field and the public getter / setter method. The getter method can be done with ʻis or no prefix in addition to the getprefix. Also, thesetter method supports with or without the set` prefix.

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}

If the class name and the associated table name are different, add @ Table to the class.

Java

import org.lightsleep.entity.*;

@Table("CONTACTS")
public class Contact {

Groovy

import org.lightsleep.entity.*

@Table('CONTACTS')
public class Contact {

Append @Key to the field that corresponds to the primary key column (s).

If the column name and field name are different, add @ Column to the field.

Java

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

Groovy

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

If the column type and the field type are different types, add @ColumnType to the field.

Java

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

Groovy

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

Non-static fields are automatically associated with columns, so add @NonColumn to fields that are not related to columns.

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 = []

If you want SQL to specify an expression instead of a field value, add @ Select, @ Insert, @Update. If you want to specify that it is not used for SQL, add @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

It has the following annotations.

Annotation name	Contents to be specified	Target to specify
@Table	table name	class
@Key	Supports primary key	field
@Column	Column name	field
@ColumnType	Column type	field
@NonColumn	Not related to columns	field
@NonSelect	Not used for SELECT SQL	field
@NonInsert	Not used for INSERT SQL	field
@NonUpdate	Not used for UPDATE SQL	field
@Select	Expressions used in SELECT SQL	field
@Insert	Expressions used in INSERT SQL	field
@Update	Expressions used in UPDATE SQL	field
@KeyProperty	Supports primary key	class
@ColumnProperty	Column name	class
@ColumnTypeProperty	Column type	class
@NonColumnProperty	Not related to columns	class
@NonSelectProperty	Not used for SELECT SQL	class
@NonInsertProperty	Not used for INSERT SQL	class
@NonUpdateProperty	Not used for UPDATE SQL	class
@SelectProperty	Expressions used in SELECT SQL	class
@InsertProperty	Expressions used in INSERT SQL	class
@UpdateProperty	Expressions used in UPDATE SQL	class

@XxxxxProperty is used to specify for fields defined in the superclass. You can specify the same annotation more than once in one class.

2. Definition of lightsleep.properties

Definition example:

lightsleep.properties

Logger      = Log4j2
#Database   =PostgreSQL * Version 2.1.Abolished at 0
ConnectionSupplier = Dbcp
url         = jdbc:postgresql://postgresqlserver/example
username    = example
password    = _example_
initialSize = 10
maxTotal    = 100

Logger is a specification of the method to output the log, and select from the following.

Specified content	Logging library to use	Log level	Definition file
Jdk	Java Runtime	－	logging.Defined in properties
Log4j	Log4j 1.x.x	－	log4j.properties or log4j.Defined in xml
Log4j2	Log4j 2.x.x	－	log4j2.Defined in xml
SLF4J	SLF4J	－	Depends on the target logging library implementation
Std$Out$Trace	System.Output to out	trace	－
Std$Out$Debug	Same as above	debug	－
Std$Out$Info	Same as above	info	－
Std$Out$Warn	Same as above	warn	－
Std$Out$Error	Same as above	error	－
Std$Out$Fatal	Same as above	fatal	－
Std$Err$Trace	System.Output to err	trace	－
Std$Err$Debug	Same as above	debug	－
Std$Err$Info	Same as above	info	－
Std$Err$Warn	Same as above	warn	－
Std$Err$Error	Same as above	error	－
Std$Err$Fatal	Same as above	fatal	－

If not specified, Std $ Out $ Info is selected.

~~ Database is the specification of the target DBMS and is selected from the following. ~~

~~ If you want to use a DBMS other than the above, do not specify it or specify Standard. ~~

• • Abolished in version 2.1.0. Changed to automatically select from the contents of JDBC URL. *

For ConnectionSupplier, select from the following by specifying the connection supplier (connection pool, etc.) to be used.

Specified content	Connection supplier
C3p0	c3p0
Dbcp	Apache Commons DBCP
HikariCP	HikariCP
TomcatCP	Tomcat JDBC Connection Pool
Jndi	Java Naming and Directory Interface (JNDI) (For tomcat)
Jdbc	DriverManager#getConnection(String url, Properties info)Method

Below the Connection Supplier (url ~ maxTotal) in the definition example of lightsleep.properties above is the definition content to be passed to each connection supplier.

Definition example (Std $ Out $ Info / DB2 / Jdbc):

lightsleep.properties

Logger    = Std$Out$Info
#Database =DB2 * Version 2.1.Abolished at 0
ConnectionSupplier = Jdbc
url       = jdbc:db2://db2:50000/example

Definition example (Jdk / MySQL / C3p0):

lightsleep.properties

Logger    = Jdk
#Database =MySQL * Version 2.1.Abolished at 0
ConnectionSupplier = C3p0
url       = jdbc:mysql://mysql:3306/example
user      = example
password  = _example_

Definitions other than url, user and password are described in c3p0.properties or c3p0-config.xml.

Definition example (Log4j / Oracle / Dbcp):

lightsleep.properties

Logger      = Log4j
#Database   =Oracle * Version 2.1.Abolished at 0
ConnectionSupplier = Dbcp
url         = jdbc:oracle:thin:@oracle:1521:example
username    = example
password    = _example_
initialSize = 10
maxTotal    = 100
   ...

Definition example (Log4j2 / PostgreSQL / HikariCP):

lightsleep.properties

Logger          = Log4j2
#Database       =PostgreSQL * Version 2.1.Abolished at 0
ConnectionSupplier = HikariCP
jdbcUrl         = jdbc:postgresql://postgresql:5432/example
username        = example
password        = _example_
minimumIdle     = 10
maximumPoolSize = 100
   ...

Definition example (SLF4J / SQLServer / TomcatCP):

lightsleep.properties

Logger      = SLF4J
#Database   =SQLServer * Version 2.1.Abolished at 0
ConnectionSupplier = TomcatCP
url         = jdbc:sqlserver://sqlserver:1433;database=example
username    = example
password    = _example_
initialSize = 10
maxActive   = 100
   ...

Definition example (Log4j / MySQL / Jndi):

lightsleep.properties

Logger             = Log4j
#Database           =MySQL * Version 2.1.Abolished at 0
ConnectionSupplier = Jndi
dataSource         = jdbc/example

Definition example of multiple JDBC URLs 1

** (Version 2.1.0 ~) **

lightsleep.properties

#When specifying multiple JDBC URLs
Logger      = Log4j2
ConnectionSupplier = Dbcp
urls        = jdbc:postgresql://postgresql:5432/example,\
              jdbc:postgresql://postgresql:5433/example
user        = example
password    = _example_
initialSize = 10
maxTotal    = 100

Definition example of multiple JDBC URLs 2

** (Version 2.1.0 ~) **

lightsleep.properties

#When using multiple DBMSs(Specify user and password in 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_,\
      <Blank line>
initialSize = 10
maxTotal    = 100

Definition example of multiple JDBC URLs 3

** (Version 2.1.0 ~) **

lightsleep.properties

#When specifying a connection supplier for each 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_,\
      <Blank line>
# Dbcp, HikariCP, TomcatCP
initialSize = 10

# Dbcp
maxTotal    = 10

# TomcatCP
maxActive   = 10

# HikariCP
minimumIdle     = 10
maximumPoolSize = 10

Definition example of multiple data sources 4

** (Version 2.1.0 ~) ** ** (2017-12-06 postscript) **

In the case of * dataSource / dataSources definition, unlike url / urls, the keyword that identifies the database handler is not included (in some cases), so the URL is obtained from DatabaseMetaData of the Connection object obtained first and judged. .. * ** (Version 2.1.1 ~) **

lightsleep.properties

#When using Jndi
Logger = Log4j2
ConnectionSupplier = Jndi
dataSources = \
    jdbc/db2_example,\
    jdbc/mariadb_example,\
    jdbc/mysql_example,\
    jdbc/oracle_example,\
    postgresql_example,\
    sqlite_example,\
    sqlserver_example,\
      <Blank line>
#   ↑"jdbc/"With or without

3. Database access

Executing the Transaction.execute method is equivalent to executing one transaction. The content of the transaction is defined by the argument transaction (lambda expression). The lambda expression corresponds to the contents of the Transaction.executeBody method, and the argument of this method is Connection.

Java

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

//Transaction example
Transaction.execute(conn -> {
    //Transaction start
    new Sql<>(Contact.class).connection(conn)
        .insert(contact);
   ...
    //End of transaction
});

Groovy

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

//Transaction example
Transaction.execute {
    //Transaction start
    new Sql<>(Contact).connection(it)
        .insert(contact)
    ...
    //End of transaction
}

If you have multiple JDBC URLs (or data sources) defined in lightsleep.properties, you need to specify which URL (or data source) to execute the transaction for. The ConnectionSupplier.find method finds a URL (or data source) that contains the entire string array (variadic) of its arguments. An exception will be thrown if more than one is found or not found. ** (Version 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 example
Transaction.execute(supplier1, conn -> {
    //Transaction start
    new Sql<>(Contact.class).connection(conn)
        .insert(contact);
   ...
    //End of transaction
});

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 example
Transaction.execute(supplier1) {
    //Transaction start
    new Sql<>(Contact).connection(it)
        .insert(contact)
    ...
    //End of transaction
}

If an exception is thrown during the transaction, the Transaction.rollback method is executed and Otherwise, the Transaction.commit method will be executed.

Below is a description example in Java and Groovy and the generated SQL. The generated SQL has line breaks for clarity, but in reality there are no line breaks.

3-1. SELECT

3-1-1. SELECT 1 row / expression condition

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 row / entity condition

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 multiple rows / expression conditions

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 subquery condition

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 expression condition / 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 expression condition / 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 expression condition / (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 SELECT column

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(Skip lines when retrieving)
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 does not support FOR UPDATE, so an UnsupportedOperationException is thrown.

SQL

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

3-1-12. SELECT inner join

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 left outer join

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 right outer join

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 throws an exception because RIGHT OUTER JOIN is not supported.
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 row

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 multiple rows

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 line

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 Multiple lines

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 specification condition, column selection

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 All lines

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 line

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 Multiple lines

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 specification condition

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 all lines

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