-
Notifications
You must be signed in to change notification settings - Fork 159
ObjectiveSQL 开发指南
Braisdom edited this page Dec 9, 2020
·
10 revisions
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 表达式。
Preferences/Settings -> Plugins -> Search with "ObjectiveSql" in market -> Install
独立应用程序，请将下列代码添加至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
请将下列代码添加至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>以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返回不同的数据库连接，其它场景下可以忽略该参数。
应用系统基于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中动态数据源名称匹配。
ObjectiveSQL 提供的简单SQL 编程主要针对单表的相关SQL 使用，通过动态生成的Java API 为应用系统的开发提供便捷的开发体验。
缺省情况下，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 没有任何变化
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
@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 抽象模型定义
Member.countAll();
Member.count("name = ?", "braisdom");
Member.queryByPrimaryKey(1);
Member.queryFirst("id > ?", 1);
Member.query("id > ?", 1);
Member.queryAll();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");// 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);
}// Transaction executing manually
Databases.executeTransactionally(((connection, sqlExecutor) -> {
Member.update(1L, newMember, true);
Member.update("name = ?", "name = ?", newName, oldName);
return null;
}));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自定义关联关系的构建逻辑。
// 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");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接口也可以进行分页和关联对象查询。
ObjectiveSQL Validation 内部集成了Jakarta Bean Validation
详细使用方法请参考：https://beanvalidation.org/
Member newMember = new Member()
.setNo("100")
.setName("Pamela")
.setGender(1)
.setMobile("15011112222");
// Violations occurred in field 'no'
Validator.Violation[] violations = newMember.validate();Member newMember = new Member()
.setNo("100000")
.setName("Pamela")
.setGender(1)
.setMobile("15011112222");
Member.create(newMember);
Member.create(newMember, true); // Skip validationMember.execute("DELETE FROM members WHERE name = ?", "Pamela");ObjectiveSQL 提供的复杂SQL 编程，其实是对SQL 语法的一种抽象和建模，以Java API 形式进行互相作用，使得复杂SQL 不再以字符串的形式出现在Java 中，从而实现动态化SQL 变得清晰易理解，不同的业务系统也可以基于ObjectiveSQL 对自身业务的再抽象和建模，实现SQL 逻辑的复用。
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`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`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, 20ObjectiveSQL 通过运算符重域技术使得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`所谓动态查询，实际上就是表达式的构建过程跟随着参数的有无而变化，基于这种使用场景，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`由于 ObjectiveSQL 无法决定应用系统使用哪一个日志框架，所以ObjectiveSQL 并未集成任何第三方日志框架，确认使用JDK 自身的日志框架，如果应用系统需要使用自身的日志框架，并在系统启动完成后注入ObjectiveSQL，请按下列方式集成（以Slf4j 为例）。
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);
}
}public class Application {
public static void main(String[] args) {
Databases.installLoggerFactory(new ObjLoggerFactoryImpl());
// others
}
}@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);
}
}应用系统中对时间性不强的数据会进行数据缓存，通常会将数据缓存至Redis 中，针对些特性，可以扩展ObjectiveSQL 的 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);
}
}public class Application {
public static void main(String[] args) {
Databases.installSqlExecutor(new CacheableSQLExecutor());
// others
}
}Spring Boot 的注入方式去 LogFactory 的注入方式相同
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 所能接受的值。