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.

uroboroSQL

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.

asciicast

Coverage report

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

Other features

item uroboro SQL support
license MIT
System OSS
latest v0.5 (2017/12)
SQL externalization
DSL ×
Java 8<=
Supports Stream Lambda
Entity automatic generation
Correspondence to division value ○ (Both enumerator and constant class are acceptable)
Stored procedure call
ResultSet customization
Oracle
DB2 -
MySQL
PostgreSQL
MariaDB -
MS-SQL
H2
Derby
Sybase -
SQLite
Dependence commons-lang3,slf4,ognl,jline

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

Recommended Posts

Summary of ORM "uroboroSQL" that can be used in enterprise Java
Summary of css selectors that can be used with Nookogiri
Write a class that can be ordered in Java
Technology excerpt that can be used for creating EC sites in Java training
Convenient shortcut keys that can be used in Eclipse
List of devices that can be previewed in Swift UI
Java (super beginner edition) that can be understood in 180 seconds
Ruby on Rails 5 quick learning practice guide that can be used in the field Summary
Syntax and exception occurrence conditions that can be used when comparing with null in Java
[Java 8] Sorting method in alphabetical order and string length order that can be used in coding tests
A concise summary of Java 8 date / time APIs that are likely to be used frequently
Reference memo / In-memory LDAP server that can be embedded in Java
Static analysis tool that can be used on GitHub [Java version]
Note that system properties including JAXBContext cannot be used in Java11
SwiftUI View that can be used in combination with other frameworks
Summary of JDK that can be installed with Homebrew (as of November 2019)
How to make a key pair of ecdsa in a format that can be read by Java
Java file input / output processing that can be used through historical background
Library summary that seems to be often used in recent Android development (2019/11)
[Android Studio] Description that can be continuously input in SQLite Database [Java]
[Spring Boot] List of validation rules that can be used in the property file for error messages
Organize methods that can be used with StringUtils
[Ruby] Methods that can be used with strings
Basic functional interface that can be understood in 3 minutes
The story that the port can no longer be used in the Spring boot sample program
Stream processing of Java 8 can be omitted so far!
Personal summary of the guys often used in JUnit 4
About the matter that hidden_field can be used insanely
Summary of how to implement default arguments in Java
Summary of Java support 2018
[Spring Data JPA] Can And condition be used in the automatically implemented method of delete?
Introduction of jQuery-until it can be used (record executed in preparation for asynchronous communication) haml
[Java] It seems that `0 <hoge <10` cannot be written in the conditional expression of the ʻif` statement.
Create a page control that can be used with RecyclerView
Create a jar file that can be executed in Gradle
The case that @Autowired could not be used in JUnit5
Firebase-Realtime Database on Android that can be used with copy
Problems that can easily be mistaken for Java and JavaScript
[Question] Can nullif be used in the count function in JPQL?
Introduction to Rakefile that can be done in about 10 minutes
Things to be aware of when writing code in Java
Java 14 new features that could be used to write code
Whether options can be used due to different Java versions
[Java11] Stream Summary -Advantages of Stream-
[Java] Summary of regular expressions
[Java] Summary of operators (operator)
Summary of Java language basics
[Java] Summary of for statements
Summary of Java Math class
Implementation of gzip in java
[Java] Summary of control syntax
Implementation of tri-tree in Java
Summary of java error processing
[Java] Summary of design patterns
[Java] Summary of mathematical operations
Object-oriented design that can be used when you want to return a response in form format
Summary of "Design Patterns Learned in Java Language (Multithread Edition)" (Part 10)
Three reasons why wrapper classes should not be used in Java
Summary of "Design Patterns Learned in Java Language (Multithread Edition)" (Part 7)
Summary of "Design Patterns Learned in Java Language (Multithread Edition)" (Part 3)
Summary of "Design Patterns Learned in Java Language (Multithread Edition)" (Part 9)