Skip to content

ObjectiveSQL 开发指南

Jump to bottom
Braisdom edited this page Dec 3, 2020 · 10 revisions

1. 前言

ObjectiveSQL 是一个Java ORM 框架，它不仅是Active Record 模式在Java 中的应用，同时还针对复杂SQL 编程提供近乎完美的解决方案，使得Java 代码与SQL 语句有机的结合，改变了传统SQL 的编程模型（以字符串拼接为主的编程模型）。

ObjectiveSQL 项目分为两部分：一部分是运行期Maven 依赖 objective-sql 或 objsql-springboot，主要实现了基础的ORM 特性和SQL 编程模型，另一部分是IntelliJ IDEA 插件，兼容Java 运算符重载和动态代码提示。

ObjectiveSQL 主要解决：

  • 动态代码生成：基于领域模型（Domain Model），自动生成简单SQL 编程代码，使应用系统开发只关注自身的业务特性，提升开发效率
  • 可编程SQL：将SQL 中的控制原语、谓词、函数以及过程化逻辑等抽象为Java 中的高级类型，与Java 融为一体，使得SQL 成为真正过程化、逻辑型编程语言，可封装、可复用以及单元测试
  • 表达式语法一致性：Java 语法与SQL 语法等价替换，包括：数学计算、函数调用、比较与逻辑计算表达式

2. 依赖安装

2.1 IntelliJ IDEA 插件安装

Preferences/Settings -> Plugins -> Search with "ObjectiveSql" in market -> Install

2.2 Maven 集成

独立应用程序，请将下列代码添加至dependencies：

<!-- In standalone -->
<dependency>
    <groupId>com.github.braisdom</groupId>
    <artifactId>objective-sql</artifactId>
    <version>{objsql.version}</version>
</dependency>

Spring Boot 集成项目，请将下列代码添加至dependencies：

<!-- In Spring Boot, you need add spring-jdbc dependency before -->
<dependency>
  <groupId>com.github.braisdom</groupId>
  <artifactId>objsql-springboot</artifactId>
  <version>{objsql.version}</version>
</dependency>

2.3 Maven Compiler 参数配置

请将下列代码添加至pom.xml 中的 <build> / <plugins> 结点下：

<plugin>
    <groupId>org.apache.maven.plugins</groupId>
    <artifactId>maven-compiler-plugin</artifactId>
    <version>3.8.0</version>
    <configuration>
        <source>8</source>
        <target>8</target>
        <encoding>UTF-8</encoding>
        <compilerArgs>
            <arg>-Xplugin:JavaOO</arg>
        </compilerArgs>
        <annotationProcessorPaths>
            <path>
                <groupId>com.github.braisdom</groupId>
                <artifactId>objective-sql</artifactId>
                <version>${objsql.version}</version>
            </path>
        </annotationProcessorPaths>
    </configuration>
</plugin>

3 数据库连接注入

3.1 独立应用系统

以MySQL 为例，基于 ConnectionFactory 构造数据连接获取逻辑，并将其注入Databases。

private static class MySQLConnectionFactory implements ConnectionFactory {

    @Override
    public Connection getConnection(String dataSourceName) throws SQLException {
        try {
            String url = "jdbc:mysql://localhost:4406/objective_sql";
            String user = "root";
            String password = "******";
            return DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            throw e;
        } catch (Exception e) {
            throw new IllegalStateException(e.getMessage(), e);
        }
    }
}

Databases.installConnectionFactory(new MySQLConnectionFactory());

getConnection 方法中的的 dataSourceName 参数仅在多数据源的场景下使用，getConnection 方法可以根据不同的 dataSourceName 返回不同的数据库连接，其它场景下可以忽略该参数。

3.2 集成Spring Boot

应用系统基于Spring Boot 框架开发时，无需手动注入数据源，请按下列方法进行配置即可：

spring:
  profiles:
    name: objective-sql-example
    active: development

  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:4406/objective_sql
    username: root
    password: ******
    hikari:
      idle-timeout: 10000
      maximum-pool-size: 10
      minimum-idle: 5
      pool-name: Master
    
    # Configurations for multiple databases
    extensions:
      # The name of data source, which will match with @DomainModel definition 
      slave:
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:4406/objective_sql
        username: root
        password: ******
        hikari:
          idle-timeout: 10000
          maximum-pool-size: 10
          minimum-idle: 5
          pool-name: Slave

其中 extensions 标记仅当多数据源时需要配置，而slave 作为数据源名称，应该与DomainModel 中定义的数据源名称匹配，或者通过DomainModelDescriptro 中动态数据源名称匹配。

4 简单SQL 编程指南

ObjectiveSQL 提供的简单SQL 编程主要针对单表的相关SQL 使用，通过动态生成的Java API 为应用系统的开发提供便捷的开发体验。

4.1 领域模型定义

@DomainModel
public class Member {
    @Size(min = 5, max = 20)
    private String no;
    
    @Queryable
    private String name;
    private Integer gender;
    private String mobile;
  
    @Transient
    private String otherInfo;

    @Relation(relationType = RelationType.HAS_MANY)
    private List<Order> orders;
}

ObjectiveSQL 会根据上述模型定义，自动生成基础的SQL 编程相关方法和SQL 抽象模型定义

4.2 数据查询

Member.countAll();
Member.count("name = ?", "braisdom");

Member.queryByPrimaryKey(1);
Member.queryFirst("id > ?", 1);
Member.query("id > ?", 1);
Member.queryAll();

4.3 数据更新

Member.create(newMember);
Member.create(newMember, true); // Create a member without validating
Member.create(Member.newInstanceFrom(memberHash));
Member.create(new Member[]{newMember1, newMember2, newMember3}, false);

Member.update(1L, newMember, true); // Update a member by primary key and skip validationg
Member.update("name = ?", "name = ?", newName, oldName);

Member.destroy(1L); // Delete a member by primary key
Member.destroy("name = ?", "Mary");

4.4 事务

4.4.1 基于Annotation 的事务
// The method will be executed in a database thransaction
@Transactional
public static void makeOrder(Order order, OrderLine... orderLines) throws SQLException {
  Order.create(order, false);
  OrderLine.create(orderLines, false);
}
4.4.2 手动事务管理
// Transaction executing manually
Databases.executeTransactionally(((connection, sqlExecutor) -> {
  	Member.update(1L, newMember, true);
	  Member.update("name = ?", "name = ?", newName, oldName);
    return null;
}));

4.5 关联对象查询

Member.queryAll(Member.HAS_MANY_ORDERS);
Member.queryFirst("id > ?", Member.HAS_MANY_ORDERS, 1);
Member.query("id > ?", Member.HAS_MANY_ORDERS, 1);

Member.queryByPrimaryKey(1, Member.HAS_MANY_ORDERS);
Member.queryByName("braisdom", Member.HAS_MANY_ORDERS);

上述代码中的 Member.HAS_MANY_ORDERS 属性为ObjectiveSQL 自动生成，在特殊情况下，可以基于 com.github.braisdom.objsql.relation.Relationship 自定义关联关系的构建逻辑。

4.6 分页查询

// Create a Page instance with current page and page size
Page page = Page.create(0, 10);
PagedList<Member> members = Member.pagedQueryAll(page, Member.HAS_MANY_ORDERS);
PagedList<Member> members = Member.pagedQuery(page, "name = ?", "braisdom");

4.7 Query 接口编程

Query query = Member.createQuery();
query.project("name").groupBy("name").having("COUNT(*) > 0").orderBy("name DESC");

List<Member> members = query.execute(Member.HAS_MANY_ORDERS);

// Paged querying with querying dynamically
Paginator paginator = Databases.getPaginator();
Page page = Page.create(0, 10);
PagedList<Member> pagedMembers = paginator
  					.paginate(page, query, Member.class, Member.HAS_MANY_ORDERS);

针对SQL 中的分组和排序，需要通过Query 接口完成，同时Query 接口也可以进行分页和关联对象查询。

4.8 Validation

ObjectiveSQL Validation 内部集成了Jakarta Bean Validation

详细使用方法请参考：https://beanvalidation.org/

4.8.1 手工调用 validate 方法：
Member newMember = new Member()
        .setNo("100")
        .setName("Pamela")
        .setGender(1)
        .setMobile("15011112222");

// Violations occurred in field 'no'
Validator.Violation[] violations = newMember.validate();
4.8.2 创建对象时 validate:
Member newMember = new Member()
        .setNo("100000")
        .setName("Pamela")
        .setGender(1)
        .setMobile("15011112222");

Member.create(newMember); 
Member.create(newMember, true); // Skip validation

4.9 自定义SQL

Member.execute("DELETE FROM members WHERE name = ?", "Pamela");

5 复杂SQL 编程指南

ObjectiveSQL 提供的复杂SQL 编程，其实是对SQL 语法的一种抽象和建模，以Java API 形式进行互相作用，使得复杂SQL 不再以字符串的形式出现在Java 中，从而实现动态化SQL 变得清晰易理解，不同的业务系统也可以基于ObjectiveSQL 对自身业务的再抽象和建模，实现SQL 逻辑的复用。

5.1 JOIN 查询

5.1.1 隐式 Join
Member.Table member = Member.asTable();
Order.Table order = Order.asTable();

Select select = new Select();

select.project(member.no, member.name, count().as("order_count"))
        .from(member, order)
        .where(member.id.eq(order.memberId))
        .groupBy(member.no, member.name);

List<Member> members = select.execute(Member.class);
SELECT `T0`.`NO` , `T0`.`name` , COUNT(*) AS `order_count`
FROM `members` AS `T0`, `orders` AS `T1`
WHERE (`T0`.`id` = `T1`.`member_id` )
GROUP BY `T0`.`NO` , `T0`.`name`
5.1.2 显式Join
Member.Table member = Member.asTable();
Order.Table order = Order.asTable();

Select select = new Select();

select.project(member.no, member.name, count().as("order_count"))
        .from(member)
        .leftOuterJoin(order, order.memberId.eq(member.id))
        .groupBy(member.no, member.name);

List<Member> members = select.execute(Member.class);
SELECT `T0`.`NO` , `T0`.`name` , COUNT(*) AS `order_count`
FROM `members` AS `T0`
LEFT OUTER JOIN `orders` AS `T1` ON (`T1`.`member_id` = `T0`.`id` )
GROUP BY `T0`.`NO` , `T0`.`name`

5.2 分页查询

Member.Table member = Member.asTable();
Order.Table order = Order.asTable();

Paginator<Member> paginator = Databases.getPaginator();
Page page = Page.create(0, 20);

Select select = new Select();

select.project(member.no, member.name, count().as("order_count"))
        .from(member, order)
        .where(member.id.eq(order.memberId))
        .groupBy(member.no, member.name);

PagedList<Member> members = paginator.paginate(page, select, Member.class);
-- Counting SQL
SELECT COUNT(*) AS count_
FROM (
	SELECT
		`T0`.`NO`,
		`T0`.`name`,
		COUNT(*) AS `order_count`
	FROM `members` AS `T0`, `orders` AS `T1`
	WHERE (`T0`.`id` = `T1`.`member_id`)
	GROUP BY `T0`.`NO`, `T0`.`name`
) T
-- Querying SQL
SELECT `T0`.`NO`, `T0`.`name`, COUNT(*) AS `order_count`
FROM `members` AS `T0`, `orders` AS `T1`
WHERE (`T0`.`id` = `T1`.`member_id`)
GROUP BY `T0`.`NO`, `T0`.`name`
LIMIT 0, 20

5.3 复杂表达式查询

ObjectiveSQL 通过运算符重域技术使得Expression 也可以参与各类运算符计算，从而使得Java 代码变得简单易懂，而不是通过各类运算符方法进行计算。ObjectiveSQL 表达式计算时并不能够与SQL 表达完匹配，默认情况下所有表达式均可以进行算术运算，在IntelliJ IDEA 中并不能给出完整的提醒，例如：JoinExpression 也可以进行算术运算，此时在IntelliJ IDEA 中并不会出现语法错误的提醒，但在实现运算过程中会抛出 UnsupportedArithmeticalException，该异常为RuntimeException 的子类。

Order.Table orderTable = Order.asTable();
Select select = new Select();

select.project((sum(orderTable.amount) / sum(orderTable.quantity) * 100).as("unit_amount"))
        .from(orderTable)
        .where(orderTable.quantity > 30 &&
                orderTable.salesAt.between("2020-05-01 00:00:00", "2020-05-02 23:59:59"))
        .groupBy(orderTable.memberId);

List<Order> orders = select.execute(Order.class);
SELECT ((((SUM(`T0`.`amount` ) / SUM(`T0`.`quantity` ) )) * 100)) AS unit_amount
FROM `orders` AS `T0`
WHERE ((`T0`.`quantity` > 30)
	AND `T0`.`sales_at` BETWEEN '2020-05-01 00:00:00' AND '2020-05-02 23:59:59' )
GROUP BY `T0`.`member_id`

5.4 动态查询

所谓动态查询，实际上就是表达式的构建过程跟随着参数的有无而变化，基于这种使用场景，ObjectiveSQL 设计了一个永真的逻辑表达式EternalExpression ，永真表达式是程序上的一种巧妙设计，使得代码逻辑变得更清晰，即使所有参数均未赋值，整个表达式也会存在一个永的表达，确保最终SQL 语句的正常。

String[] filteredNo = {"202000001", "202000002", "202000003"};
int filteredQuantity = 0;

Order.Table orderTable = Order.asTable();
Select select = new Select();
LogicalExpression eternalExpression = new EternalExpression();

if(filteredNo.length > 0) {
    eternalExpression = eternalExpression.and(orderTable.no.in(filteredNo));
}

if(filteredQuantity != 0) {
    eternalExpression = eternalExpression.and(orderTable > filteredQuantity);
}

select.project((sum(orderTable.amount) / sum(orderTable.quantity) * 100).as("unit_amount"))
        .from(orderTable)
        .where(eternalExpression)
        .groupBy(orderTable.memberId);

List<Order> orders = select.execute(Order.class);
SELECT ((((SUM(`T0`.`amount` ) / SUM(`T0`.`quantity` ) )) * 100)) AS unit_amount
FROM `orders` AS `T0`
WHERE ((1 = 1) AND `T0`.`NO` IN ('202000001', '202000002', '202000003') )
GROUP BY `T0`.`member_id`
Clone this wiki locally