• 让天下没有难学的技术
    多数学员都来自推荐,这就是口碑的力量

java开发之Java ORM 框架推荐

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 语法等价替换,包括:数学计算、函数调用、比较与逻辑计算表达式,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>

最新版本请访问 ObjectiveSQL,ObjSqlSpringBoot

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 为应用系统的开发提供便捷的开发体验。【关注尚硅谷,轻松学IT】

4.1 命名约定

4.1.1 类名与表名

缺省情况下,ObjectiveSQL 以驼峰与下划线的形式对Java 元素与数据库元素进行互相转换,示例如下:

1)Java 定义如下:

class Member {

private String memberNo;

private String name;

}

2)数据库表定义如下:

create table members (

member_no varchar not null,

name varchar

);

类名:Member 在数据库中对应的名称为 members,而字段名memberNo 对应的列名为 member_no,而字段名name 没有任何变化

4.1.1 关联对象

1)Java 定义如下:

class Member {

private String memberNo;

private String name;

@Relation(relationType = RelationType.HAS_MANY)

private List<Order> orders;

}

class Order {

private String no;

private Long memberId;

@Relation(relationType = RelationType.BELONGS_TO)

private Member member;

}

2)数据库表定义如下:

create table members (

member_no varchar not null,

name varchar

);

create table members (

member_no varchar not null,

member_id int(10) not null,

name varchar

);

通过上面的结构定义,可以看出几个关键特征:

  • 用于承载HAS_MANY 关联对象的实例变量members 是由类型转换成复数,而BELONGS_TO 与HAS_ONE则为单数
  • Order 类中存在一个外键对应的实例变量memberId,同时在表中也存在一个member_id与其对应
  • 其它规则与类与表转换的规则一致

注意:所有类名在转换为复杂时,遵循英文的规律,例如:person 对应 pepole

4.2 领域模型定义

@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.3 数据查询

Member.countAll();

Member.count(“name = ?”, “braisdom”);

Member.queryByPrimaryKey(1);

Member.queryFirst(“id > ?”, 1);

Member.query(“id > ?”, 1);

Member.queryAll();

4.4 数据更新

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.5 事务

4.5.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.5.2 手动事务管理

// Transaction executing manually

Databases.executeTransactionally(((connection, sqlExecutor) -> {

Member.update(1L, newMember, true);

Member.update(“name = ?”, “name = ?”, newName, oldName);

return null;

}));

4.6 关联对象查询

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.7 分页查询

// 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.8 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.9 Validation

ObjectiveSQL Validation 内部集成了Jakarta Bean Validation

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

4.9.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.9.2 创建对象时 `validate`

Member newMember = new Member()

.setNo(“100000”)

.setName(“Pamela”)

.setGender(1)

.setMobile(“15011112222”);

Member.create(newMember);

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

4.10 自定义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`

6 高级使用

6.1 日志集成

由于 ObjectiveSQL 无法决定应用系统使用哪一个日志框架,所以ObjectiveSQL 并未集成任何第三方日志框架,确认使用JDK 自身的日志框架,如果应用系统需要使用自身的日志框架,并在系统启动完成后注入ObjectiveSQL,请按下列方式集成(以Slf4j 为例)。

6.1.1 LoggerFactory 扩展实现

public class ObjLoggerFactoryImpl implements LoggerFactory {

private class ObjLoggerImpl implements Logger {

private final org.slf4j.Logger logger;

public ObjLoggerImpl(org.slf4j.Logger logger) {

this.logger = logger;

}

@Override

public void debug(long elapsedTime, String sql, Object[] params) {

logger.debug(createLogContent(elapsedTime, sql, params));

}

@Override

public void info(long elapsedTime, String sql, Object[] params) {

logger.info(createLogContent(elapsedTime, sql, params));

}

@Override

public void error(String message, Throwable throwable) {

logger.error(message, throwable);

}

private String createLogContent(long elapsedTime, String sql, Object[] params) {

String[] paramStrings = Arrays.stream(params)

.map(param -> String.valueOf(param)).toArray(String[]::new);

String paramString = String.join(“,”, paramStrings);

return String.format(“[%dms] %s, with: [%s]”,

elapsedTime, sql, String.join(“,”,

paramString.length() > 100 ? StringUtil

.truncate(paramString, 99) : paramString));

}

}

@Override

public Logger create(Class<?> clazz) {

org.slf4j.Logger logger = org.slf4j.LoggerFactory.getLogger(clazz);

return new ObjLoggerImpl(logger);

}

}

6.1.1 普通应用程序注入方式

public class Application {

public static void main(String[] args) {

Databases.installLoggerFactory(new ObjLoggerFactoryImpl());

// others

}

}

6.1.2 Spring Boot 应用程序注入方式

@SpringBootApplication

@EnableAutoConfiguration

public class Application {

public static void main(String[] args) {

SpringApplication springApplication = new SpringApplication(Application.class);

springApplication.addListeners(new ApplicationListener<ApplicationReadyEvent>() {

@Override

public void onApplicationEvent(ApplicationReadyEvent event) {

Databases.installLoggerFactory(new ObjLoggerFactoryImpl());

}

});

springApplication.run(args);

}

}

6.2 基于SQL 语句的对象缓存

应用系统中对时间性不强的数据会进行数据缓存,通常会将数据缓存至Redis 中,针对些特性,可以扩展ObjectiveSQL 的 SQLExecutor 接口轻易实现。

6.1.1 SQLExecutor 扩展实现

public class CacheableSQLExecutor<T> extends DefaultSQLExecutor<T> {

private static final List<Class<? extends Serializable>> CACHEABLE_CLASSES =

Arrays.asList(new Class[]{Member.class});

private static final Integer CACHED_OBJECT_EXPIRED = 60;

private static final String KEY_SHA = “SHA”;

private Jedis jedis = new Jedis(“localhost”, 6379);

private MessageDigest messageDigest;

public CacheableSQLExecutor() {

try {

messageDigest = MessageDigest.getInstance(KEY_SHA);

} catch (NoSuchAlgorithmException e) {

throw new IllegalArgumentException(e.getMessage(), e);

}

}

@Override

public List<T> query(Connection connection, String sql,

TableRowAdapter tableRowAdapter, Object… params)

throws SQLException {

Class<?> domainClass = tableRowAdapter.getDomainModelClass();

if (CACHEABLE_CLASSES.contains(domainClass)) {

if(!Serializable.class.isAssignableFrom(domainClass)) {

throw new IllegalArgumentException(String

.format(“The %s cannot be serialized”));

}

messageDigest.update(sql.getBytes());

String hashedSqlId = new BigInteger(messageDigest.digest()).toString(64);

byte[] rawObjects = jedis.get(hashedSqlId.getBytes());

if (rawObjects != null) {

return (List<T>) SerializationUtils.deserialize(rawObjects);

} else {

List<T> objects = super.query(connection, sql, tableRowAdapter, params);

byte[] encodedObjects = SerializationUtils.serialize(objects);

SetParams expiredParams = SetParams.setParams().ex(CACHED_OBJECT_EXPIRED);

jedis.set(hashedSqlId.getBytes(), encodedObjects, expiredParams);

return objects;

}

}

return super.query(connection, sql, tableRowAdapter, params);

}

}

6.1.1 注入方式

public class Application {

public static void main(String[] args) {

Databases.installSqlExecutor(new CacheableSQLExecutor());

// others

}

}

Spring Boot 的注入方式去 LogFactory 的注入方式相同

6.3 ColumnTransition 扩展

ColumnTransition 是ObjectiveSQL 对外提供的一种数据类型转的扩展接口,该接口的详细定义请参考:ColumnTransition.java ,以日期形式为例,介绍ColumnTransition 的扩展方式。

public class SqlDateTimeTransition<T> implements ColumnTransition<T> {

@Override

public Object sinking(DatabaseMetaData databaseMetaData, T object,

TableRowAdapter tableRowDescriptor,

String fieldName, FieldValue fieldValue)

throws SQLException {

String databaseName = databaseMetaData.getDatabaseProductName();

if (fieldValue != null && fieldValue.getValue() != null) {

if (SQLite.equals(databaseName) || Oracle.equals(databaseName)) {

return fieldValue;

} else if (PostgreSQL.equals(databaseName)) {

if (fieldValue.getValue() instanceof Timestamp) {

return fieldValue.getValue();

} else if (fieldValue.getValue() instanceof Long) {

Instant value = Instant.ofEpochMilli((Long) fieldValue.getValue());

return Timestamp.from(value);

} else {

return Timestamp.valueOf(String.valueOf(fieldValue.getValue()));

}

} else {

return fieldValue;

}

}

return null;

}

@Override

public Object rising(DatabaseMetaData databaseMetaData,

ResultSetMetaData resultSetMetaData,

T object, TableRowAdapter tableRowDescriptor,

String columnName, Object columnValue) throws SQLException {

String databaseName = databaseMetaData.getDatabaseProductName();

try {

if (columnValue != null) {

if (SQLite.equals(databaseName)) {

Instant value = Instant

.ofEpochMilli(Long.valueOf(String.valueOf(columnValue)))

return Timestamp.from(value);

} else {

return columnValue;

}

}

} catch (DateTimeParseException ex) {

String message = String.format(“Invalid raw DataTime of ‘%s’ from database: %s”,

columnName, columnValue);

throw new IllegalArgumentException(message, ex);

}

return null;

}

}

sinking 方法是将Java 中的值,转换为数据库所能接受的值,rising则为将数据库中的值,转换为Java 所能接受的值。

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注