ObjectiveSQL Tutorial

• 1 Preface
• 2 Dependencies installation
  • 2.1 IntelliJ IDEA plug-in installation
  • 2.2 Maven integration
  • 2.3 Maven compiler configration
• 3 Database connection injection
  • 3.1 In standalone
  • 3.2 In Spring Boot
• 4 Simple SQL programming guide
  • 4.1 Definition of domain model
  • 4.2 Querying
  • 4.3 Persistence
  • 4.4 Transaction
    • 4.4.1 Transaction based on annotation
    • 4.4.2 Transaction manually
  • 4.5 Relation querying
  • 4.6 Paged querying
  • 4.7 Customized query with Query interface
  • 4.8 Validation
    • 4.8.1 validate manually
    • 4.8.2 validate on object creating
  • 4.9 Customized SQL querying
• 5 Complex SQL programming guide
  • 5.1 JOIN
    • 5.1.1 Implicit join
    • 5.1.2 Explicit join
  • 5.2 Paged querying
  • 5.3 Querying with complex expression
  • 5.4 Querying with dynamic arguments
• 6 Advanced usage
  • 6.1 LoggerFactory injection
    • 6.1.1 LoggerFactory implementation
    • 6.1.1 Injecting in standalone
    • 6.1.2 Injecting Spring Boot
  • 6.2 Cache objects with SQL
    • 6.1.1 SQLExecutor implementation
    • 6.1.1 Injecting
  • 6.3 ColumnTransition extension

1 Preface

ObjectiveSQL is an ORM framework, which is not only the best practice of "Active Record" pattern in Java, but also provides a perfect solution for complex SQL programming. It makes Java codes and SQL statements organic combination, changes the traditional SQL programming model (strings programming model).

The Objective-SQL project contains two parts: one is the dependency of maven objective-sql or objsql-springboot , with the basic ORM features and SQL programming model, and the other is the IntelliJ IDEA plug-in, compatible with Java operator overloading and code completion.

Features:

• Dynamical codes generations: with one annotation your Class has a fully featured SQL programming capability
• Java API of SQL: abstracting and modeling the elements of the SQL language, integrating SQL and Java. Easy to encapsulating, programmed and unit testing
• Expression syntax consistency: Java syntax and SQL syntax equivalent replacement, including: arithmetic, function calls, compared with the logic calculation expression

2 Dependencies installation

2.1 IntelliJ IDEA plug-in installation

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

2.2 Maven integration

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

<!-- 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 configration

<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>

The latest versions of the ObjectiveSQL and ObjSqlSpringBoot can be found in Maven Central.

3 Database connection injection

3.1 In standalone

Injecting the ConnectionFactory into the ObjectiveSQL, based on MySQL

privatestaticclassMySQLConnectionFactoryimplementsConnectionFactory {
@OverridepublicConnectiongetConnection(StringdataSourceName) throwsSQLException {
try {
Stringurl = "jdbc:mysql://localhost:4406/objective_sql";
Stringuser = "root";
Stringpassword = "******";
returnDriverManager.getConnection(url, user, password);
} catch (SQLExceptione) {
throwe;
} catch (Exceptione) {
thrownewIllegalStateException(e.getMessage(), e);
}
}
}

Databases.installConnectionFactory(new MySQLConnectionFactory());

The dataSourceName of method getConnecition is for multiple data sources, you can ignore it if one data source. You don't forget to inject it into Databases.

3.2 In Spring Boot

spring:
profiles:
name: objective-sql-exampleactive: developmentdatasource:
driver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://localhost:4406/objective_sqlusername: rootpassword: ******hikari:
idle-timeout: 10000maximum-pool-size: 10minimum-idle: 5pool-name: Master# Configurations for multiple databasesextensions:
# The name of data source, which will match with @DomainModel definition slave:
driver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://localhost:4406/objective_sqlusername: rootpassword: ******hikari:
idle-timeout: 10000maximum-pool-size: 10minimum-idle: 5pool-name: Slave

The tag extensions is used in multiple data sources, and the tag slave is name of other data source, which is match with definition in domain model

4 Simple SQL programming guide

4.1 Definition of domain model

@DomainModelpublicclassMember {
@Size(min = 5, max = 20)
privateStringno;
@QueryableprivateStringname;
privateIntegergender;
privateStringmobile;
@TransientprivateStringotherInfo;
@Relation(relationType = RelationType.HAS_MANY)
privateList<Order> orders;
}

4.2 Querying

Member.countAll();
Member.count("name = ?", "braisdom");
Member.queryByPrimaryKey(1);
Member.queryFirst("id > ?", 1);
Member.query("id > ?", 1);
Member.queryAll();

4.3 Persistence

Member.create(newMember);
Member.create(newMember, true); // Create a member without validatingMember.create(Member.newInstanceFrom(memberHash));
Member.create(newMember[]{newMember1, newMember2, newMember3}, false);
Member.update(1L, newMember, true); // Update a member by primary key and skip validationgMember.update("name = ?", "name = ?", newName, oldName);
Member.destroy(1L); // Delete a member by primary keyMember.destroy("name = ?", "Mary");

4.4 Transaction

4.4.1 Transaction based on annotation

// The method will be executed in a database thransaction@TransactionalpublicstaticvoidmakeOrder(Orderorder, OrderLine... orderLines) throwsSQLException {
Order.create(order, false);
OrderLine.create(orderLines, false);
}

4.4.2 Transaction manually

// Transaction executing manuallyDatabases.executeTransactionally(((connection, sqlExecutor) -> {
Member.update(1L, newMember, true);
Member.update("name = ?", "name = ?", newName, oldName);
returnnull;
}));

4.5 Relation querying

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);

4.6 Paged querying

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

4.7 Customized query with Query interface

Queryquery = 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 dynamicallyPaginatorpaginator = Databases.getPaginator();
Pagepage = Page.create(0, 10);
PagedList<Member> pagedMembers = paginator
.paginate(page, query, Member.class, Member.HAS_MANY_ORDERS);

4.8 Validation

The Jakarta Bean Validation is integrated into ObjectiveSQL

See more：https://beanvalidation.org/

4.8.1 validate manually

MembernewMember = newMember()
.setNo("100")
.setName("Pamela")
.setGender(1)
.setMobile("15011112222");
// Violations occurred in field 'no'Validator.Violation[] violations = newMember.validate();

4.8.2 validate on object creating

MembernewMember = newMember()
.setNo("100000")
.setName("Pamela")
.setGender(1)
.setMobile("15011112222");
Member.create(newMember); Member.create(newMember, true); // Skip validation

4.9 Customized SQL querying

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

5 Complex SQL programming guide

5.1 JOIN

5.1.1 Implicit join

Member.Tablemember = Member.asTable();
Order.Tableorder = Order.asTable();
Selectselect = newSelect();
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 Explicit join

Member.Tablemember = Member.asTable();
Order.Tableorder = Order.asTable();
Selectselect = newSelect();
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 Paged querying

Member.Tablemember = Member.asTable();
Order.Tableorder = Order.asTable();
Paginator<Member> paginator = Databases.getPaginator();
Pagepage = Page.create(0, 20);
Selectselect = newSelect();
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 SQLSELECTCOUNT(*) 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 SQLSELECT`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`LIMIT0, 20

5.3 Querying with complex expression

Order.TableorderTable = Order.asTable();
Selectselect = newSelect();
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 Querying with dynamic arguments

String[] filteredNo = {"202000001", "202000002", "202000003"};
intfilteredQuantity = 0;
Order.TableorderTable = Order.asTable();
Selectselect = newSelect();
LogicalExpressioneternalExpression = newEternalExpression();
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 Advanced usage

6.1 LoggerFactory injection

6.1.1 LoggerFactory implementation

publicclassObjLoggerFactoryImplimplementsLoggerFactory {
privateclassObjLoggerImplimplementsLogger {
privatefinalorg.slf4j.Loggerlogger;
publicObjLoggerImpl(org.slf4j.Loggerlogger) {
this.logger = logger;
}
@Overridepublicvoiddebug(longelapsedTime, Stringsql, Object[] params) {
logger.debug(createLogContent(elapsedTime, sql, params));
}
@Overridepublicvoidinfo(longelapsedTime, Stringsql, Object[] params) {
logger.info(createLogContent(elapsedTime, sql, params));
}
@Overridepublicvoiderror(Stringmessage, Throwablethrowable) {
logger.error(message, throwable);
}
privateStringcreateLogContent(longelapsedTime, Stringsql, Object[] params) {
String[] paramStrings = Arrays.stream(params)
.map(param -> String.valueOf(param)).toArray(String[]::new);
StringparamString = String.join(",", paramStrings);
returnString.format("[%dms] %s, with: [%s]",
elapsedTime, sql, String.join(",",
paramString.length() > 100 ? StringUtil
.truncate(paramString, 99) : paramString));
}
}
@OverridepublicLoggercreate(Class<?> clazz) {
org.slf4j.Loggerlogger = org.slf4j.LoggerFactory.getLogger(clazz);
returnnewObjLoggerImpl(logger);
}
}

6.1.1 Injecting in standalone

publicclassApplication {
publicstaticvoidmain(String[] args) {
Databases.installLoggerFactory(newObjLoggerFactoryImpl());
// others
}
}

6.1.2 Injecting Spring Boot

@SpringBootApplication@EnableAutoConfigurationpublicclassApplication {
publicstaticvoidmain(String[] args) {
SpringApplicationspringApplication = newSpringApplication(Application.class);
springApplication.addListeners(newApplicationListener<ApplicationReadyEvent>() {
@OverridepublicvoidonApplicationEvent(ApplicationReadyEventevent) {
Databases.installLoggerFactory(newObjLoggerFactoryImpl());
}
});
springApplication.run(args);
}
}

6.2 Cache objects with SQL

6.1.1 SQLExecutor implementation

publicclassCacheableSQLExecutor<T> extendsDefaultSQLExecutor<T> {
privatestaticfinalList<Class<? extendsSerializable>> CACHEABLE_CLASSES =
Arrays.asList(newClass[]{Member.class});
privatestaticfinalIntegerCACHED_OBJECT_EXPIRED = 60;
privatestaticfinalStringKEY_SHA = "SHA";
privateJedisjedis = newJedis("localhost", 6379);
privateMessageDigestmessageDigest;
publicCacheableSQLExecutor() {
try {
messageDigest = MessageDigest.getInstance(KEY_SHA);
} catch (NoSuchAlgorithmExceptione) {
thrownewIllegalArgumentException(e.getMessage(), e);
}
}
@OverridepublicList<T> query(Connectionconnection, Stringsql,
TableRowAdaptertableRowAdapter, Object... params) throwsSQLException {
Class<?> domainClass = tableRowAdapter.getDomainModelClass();
if (CACHEABLE_CLASSES.contains(domainClass)) {
if(!Serializable.class.isAssignableFrom(domainClass)) {
thrownewIllegalArgumentException(String
.format("The %s cannot be serialized"));
}
messageDigest.update(sql.getBytes());
StringhashedSqlId = newBigInteger(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);
SetParamsexpiredParams = SetParams.setParams().ex(CACHED_OBJECT_EXPIRED);
jedis.set(hashedSqlId.getBytes(), encodedObjects, expiredParams);
returnobjects;
}
}
returnsuper.query(connection, sql, tableRowAdapter, params);
}
}

6.1.1 Injecting

publicclassApplication {
publicstaticvoidmain(String[] args) {
Databases.installSqlExecutor(newCacheableSQLExecutor());
// others
}
}

6.3 ColumnTransition extension

publicclassSqlDateTimeTransition<T> implementsColumnTransition<T> {
@OverridepublicObjectsinking(DatabaseMetaDatadatabaseMetaData, Tobject,
TableRowAdaptertableRowDescriptor, StringfieldName, FieldValuefieldValue) throwsSQLException {
StringdatabaseName = databaseMetaData.getDatabaseProductName();
if (fieldValue != null && fieldValue.getValue() != null) {
if (SQLite.equals(databaseName) || Oracle.equals(databaseName)) {
returnfieldValue;
} elseif (PostgreSQL.equals(databaseName)) {
if (fieldValue.getValue() instanceofTimestamp) {
returnfieldValue.getValue();
} elseif (fieldValue.getValue() instanceofLong) {
Instantvalue = Instant.ofEpochMilli((Long) fieldValue.getValue());
returnTimestamp.from(value);
} else {
returnTimestamp.valueOf(String.valueOf(fieldValue.getValue()));
}
} else {
returnfieldValue;
}
}
returnnull;
}
@OverridepublicObjectrising(DatabaseMetaDatadatabaseMetaData, ResultSetMetaDataresultSetMetaData,
Tobject, TableRowAdaptertableRowDescriptor, StringcolumnName, ObjectcolumnValue) throwsSQLException {
StringdatabaseName = databaseMetaData.getDatabaseProductName();
try {
if (columnValue != null) {
if (SQLite.equals(databaseName)) {
Instantvalue = Instant
.ofEpochMilli(Long.valueOf(String.valueOf(columnValue)))
returnTimestamp.from(value);
} else {
returncolumnValue;
}
}
} catch (DateTimeParseExceptionex) {
Stringmessage = String.format("Invalid raw DataTime of '%s' from database: %s",
columnName, columnValue);
thrownewIllegalArgumentException(message, ex);
}
returnnull;
}
}