Summary of ORM "uroboroSQL" that can be used in enterprise Java

This is an introduction of Java's DB persistence library "uroboroSQL", which the author himself is involved in as a committer.

https://github.com/future-architect/uroborosql

Introduction

In enterprise system development, it is still often made in Java, and due to the characteristics of the system, RDB is often used.

Focus on products such as Amazon Aurora and Cloud Spanner I think that the reason why they gather is that they have the characteristic of overturning the CAP theorem that they want to use both transactions and SQL, although they want to scale out with parallel distributed DB as the times change.

I posted an article on December 24th last year Try to load CockroachDB on Christmas Eve, and this CockroachDB is also such an ideal. It's a product that pursues, and the need to teach the benefits of NoSQL with RDB is no longer a dream that engineers long for: sparkles:

History of Java and RDB

Systems made in Java in the early 2000s often used the JDBC API as is, but after that, Hibernate, iBatis (currently MyBatis), S2Dao of the Seaser project, etc., and OR mappers ( ORM) has been developed and is now in use.

After that, JPA (Java Persistence API) 1.0 was formulated as a Java standard persistence framework in 2006, JPA 2.0 in 2009, JPA 2.1 in 2013, and Java SE can also be used, but Java EE It's a situation where it continues to evolve with the EJB of.

For the latest library comparison, the article Discussion on Java Persistence Framework 2017 (1) is very helpful (sorry). However, the uroboro SQL that I will introduce is not included: cry :).

What is uroboroSQL

uroboroSQL is one of the DB persistence libraries in Java, and basically adopts an approach that supplements the lack of SQL with Java rather than generating SQL from Java.

Of course, it is difficult to write SQL one by one with INSERT / UPDATE / DELETE of one record, so we also provide API as ORM.

Characteristic function

Equipped with REPL function that is convenient during development

You can try it immediately without building when developing with 2Way-SQL.

Coverage report

It is possible to aggregate conditional branches of SQL statements and perform coverage reports.

Other features

• As of December 23, 2017 As of v0.5.0, the latest version

uroboroSQL code sample

Now, to understand the library, it's quick to see what the implementation will look like when you use it. So, I have summarized the frequently used implementations as a sample.

By the way, at the time of writing, it may be more abundant than the official documentation: sweat_smile:

Connect

SqlConfig config = UroboroSQL.builder("jdbc:h2:mem:test;DB_CLOSE_DELAY=-1", "sa", "sa").build();

transaction

try (SqlAgent agent = config.agent()) {
  agent.required(() -> {
    // insert/update/delete
  });
}

2way-SQL

department/select_department.sql

SELECT /* _SQL_ID_ */
    DEPT.DEPT_NO    AS  DEPT_NO
,   DEPT.DEPT_NAME  AS  DEPT_NAME
FROM
    DEPARTMENT  DEPT
WHERE
    1               =   1
/*IF SF.isNotEmpty(dept_no)*/
AND DEPT.DEPT_NO    =      /*dept_no*/1
/*END*/
/*IF SF.isNotEmpty(dept_name)*/
AND DEPT.DEPT_NAME  LIKE   '%' || /*dept_name*/'' || '%'
/*END*/

department/insert_department.sql

INSERT /* _SQL_ID_ */
INTO
    DEPARTMENT
(
    DEPT_NO
,   DEPT_NAME
) VALUES (
    /*dept_no*/1
,   /*dept_name*/'sample'
)

department/update_department.sql

UPDATE /* _SQL_ID_ */
    DEPARTMENT  DEPT
SET
    DEPT.DEPT_NAME      =   /*dept_name*/'sample'
    DEPT.LOCK_VERSION   =   DEPT.LOCK_VERSION   +   1
WHERE
    DEPT.DEPT_NO        =   /*dept_no*/1
AND DEPT.LOCK_VERSION   =   /*lock_version*/0

department/delete_department.sql

DELETE /* _SQL_ID_ */
FROM
    DEPARTMENT  DEPT
WHERE
    DEPT.DEPT_NO    =   /*dept_no*/1

With the same grammar as S2Dao etc., you can describe branches by comment notation in SQL.

SELECT

try (SqlAgent agent = config.agent()) {
  List<Map<String, Object>> deptList =
    agent.query("department/select_department")
      .param("dept_name", "retail")
      .collect();
}

SELECT (Stream acquisition, Map type)

try (SqlAgent agent = config.agent()) {
  Stream<Map<String, Object>> depts =
    agent.query("department/select_department")
      .param("dept_name", "retail")
      .stream();
}

SELECT (Stream acquisition, model type)

try (SqlAgent agent = config.agent()) {
  Stream<Department> depts =
    agent.query("department/select_department")
      .param("dept_name", "retail")
      .stream(Department.class);
}

SELECT (get one, Map type, exception if not get)

try (SqlAgent agent = config.agent()) {
  Map<String, Object> dept =
    agent.query("department/select_department")
      .param("dept_no", 1001)
      .first();
}

SELECT (get one, model type, exception if not get)

try (SqlAgent agent = config.agent()) {
  Department dept =
    agent.query("department/select_department")
      .param("dept_no", 1001)
      .first(Department.class);
}

SELECT (Get 1 item, Map type, Optional)

try (SqlAgent agent = config.agent()) {
  Map<String, Object> dept =
    agent.query("department/select_department")
      .param("dept_no", 1001)
      .findFirst()
      .orElse(null);
}

SELECT (Get 1 item, Model type, Optional)

try (SqlAgent agent = config.agent()) {
  Department dept =
    agent.query("department/select_department")
      .param("dept_no", 1001)
      .findFirst(Department.class)
      .orElse(null);
}

INSERT/UPDATE/DELETE

try (SqlAgent agent = config.agent()) {
  agent.required(() -> {
    // insert
    agent.update("department/insert_department")
      .param("dept_no", 1001)
      .param("dept_name", "sales")
      .count();
    // update
    agent.update("department/update_department")
      .param("dept_no", 1001)
      .param("dept_name", "HR")
      .count();
    // delete
    agent.update("department/delete_department")
      .param("dept_no", 1001)
      .count();
  });
}

INSERT / UPDATE / DELETE (batch execution)

List<Map<String, Object>> inputList = new ArrayList<>();
//Omission

try (SqlAgent agent = config.agent()) {
  agent.required(() -> {
    agent.batch("department/insert_department")
      .paramStream(inputList.stream())
      .count();
  });
}

DAO interface

It is assumed that you have the following model class.

@Table(name = "DEPARTMENT")
public class Department {
  private int deptNo;
  private String deptName;

  @Version
  private int lockVersion = 0;

  //Omitted getter/setter
}

The field with @Version is recognized by uroboroSQL as version information for optimistic lock, +1 is added in the SET clause at the time of UPDATE, SQL is issued in addition to the search condition of the WHERE clause, and the number of updates is 0. Raises ʻOptimisticLockException`.

SELECT (primary key search)

try (SqlAgent agent = config.agent()) {
  Department dept =
      agent.find(Department.class, 1001).orElse(null);
}

As of v0.5.0, only single table primary key search is available in the DAO interface, but in the latest version upgrade, it will be possible to specify search conditions equivalent to the WHERE clause in a single table.

INSERT

try (SqlAgent agent = config.agent()) {
  Department hrDept = new Department();
  hrDept.setDeptNo(1002);
  hrDept.setDeptName("HR");
  agent.insert(hrDept);
}

UPDATE

try (SqlAgent agent = config.agent()) {
  agent.required(() -> {
    Department dept =
        agent.find(Department.class, 1001).orElseThrow(Exception::new);
    dept.setDeptName("Human Resources");
    agent.update(dept);
  });
}

DELETE

try (SqlAgent agent = config.agent()) {
  agent.required(() -> {
    Department dept =
        agent.find(Department.class, 1001).orElseThrow(Exception::new);
    agent.delete(dept);
  });
}

Reference: uroboroSQL documentation, tools, samples

--uroboroSQL Japanese documentation - https://future-architect.github.io/uroborosql-doc/ --Introduction of uroboroSQL (OSC2017 Nagoya) #oscnagoya - https://www.slideshare.net/KenichiHoshi1/uroborosql-osc2017-nagoya-oscnagoya --uroboroSQL source generator - https://github.com/shout-star/uroborosql-generator --uroboroSQL sample CLI application - https://github.com/future-architect/uroborosql-sample --uroboroSQL sample web application (with Spring Boot) - https://github.com/shout-star/uroborosql-springboot-demo