[Open Source] A Truly Intelligent Java ORM Framework Combining the Advantages of Hibernate and MyBatis

Open SourceProcess Management

2026-06-28 03:27 2 次浏览 • 0 次下载 0KB

[Open Source] A Truly Intelligent Java ORM Framework Combining the Advantages of Hibernate and MyBatis

1. Project Overview

What is sqltoy-orm?

sqltoy-orm is an ORM framework (built on Spring) that is more suitable for real-world projects than hibernate + myBatis (plus). It provides JPA-style object CRUD operations while offering query capabilities that are more intuitive, concise, and powerful than myBatis (plus). Its advantages become even more apparent as query complexity increases. It supports the following databases:

oracle11g+、db2(9.5+)、sqlserver2012+、postgresql9.5+、mysql5.6+(mariadb/innosql)
sqlite、H2
DM (Dameng Database), Kingbase
Elasticsearch supports query operations only. Supported versions are 5.7 and above; version 7.3 or later is recommended.
clickhouse、StarRocks、greenplum、impala(kudu)
oceanBase、polardb、guassdb、tidb
mongodb (querying only)
Other databases support JDBC-based SQL execution (queries and custom SQL execution)

2. Open Source License

Licensed under Apache-2.0

3. Screenshots

Technical Architecture

Architectural Principle: sqltoy-orm is one of the tools designed to assist developers. Its goal is to solve 99.5% of problems rather than cover 100% of all scenarios (in practice, based on experience with complex ERP projects and data analytics reporting projects, it currently achieves virtually 100% coverage).

4. Feature Overview

A truly intelligent ORM framework for Java! Supports mysql, oracle, postgresql, sqlserver, db2, dm, mongodb, elasticsearch, tidb, kingbase, oceanbase, guassdb, greenplum, StarRocks, impala(kudu), clickhouse, sqlite, h2, and polardb.

sqltoy-orm is an ORM framework for relational databases developed in Java. It combines the advantages of Hibernate's object-oriented operations and MyBatis's flexible queries, while being more closely aligned with real-world projects and developer needs. It supports databases such as oracle, mysql, postgresql, sqlserver, db2, sqlite, sybase iq, elasticsearch, and mongodb.

sqltoy-orm comes with a companion tool called quickvo, which helps generate POJO classes from database tables. Quickvo eliminates the shortcomings of hibernate-tools (such as modular configuration, primary key strategy configuration, cascading loads, and the logic for updates and deletions). It allows developers to flexibly control POJO generation through configuration files without worrying about their manual modifications to POJO classes being overwritten.

The biggest advantage of sqltoy is that it understands real-world projects and developers' needs. It aims to genuinely help developers simplify the many seemingly important but actually repetitive mechanical tasks involved in database interactions. At the same time, sqltoy abstracts numerous first-hand project best practices into tools that developers can use directly.

What exactly can sqltoy bring to you?

Unlike Hibernate's internal implementation of insert, delete, and update operations (including batch operations and cascading), does modifying individual object properties in Hibernate require loading the object first to prevent other fields from being set to null? Under high concurrency in large clusters, this can overwrite data modified by other users.
Arguably one of the most elegant ways to write dynamic SQL queries. The business logic of the SQL is immediately clear at a glance, making future maintenance easier and SQL optimization and adjustments more convenient.
Provides fast pagination queries that can perform pagination before joins, reducing the volume of data involved in joins.
A cache-based translation feature that greatly reduces table joins, makes SQL simpler and clearer, and improves performance.
Allows you to implement row-to-column and column-to-row transformations without requiring advanced SQL skills.
Provides multi-level grouping, aggregation, and average calculation capabilities (combining algorithms with SQL to complement each other's strengths in a simple and elegant way).
Allows pagination queries to be completed with as few as 1.45 queries. Does your pagination require two queries (one to count the total records and another to retrieve the actual records)?
Supports parallel query capabilities (parallel pagination queries are supported starting from version 4.17.13).
Provides database and table sharding functionality, laying the foundation for high-performance and distributed scenarios.
Maximizes cross-database compatibility by providing dynamic function replacement for different databases. This is particularly useful for software products, allowing a single SQL statement to work across different databases.
SQL statements support comments, so you can simply copy SQL directly from your client after debugging. The mechanism removes comments when loading SQL while preserving database-specific optimization hints in the /*+--hint--*/ format.

5. Tech Stack

1. Developed using the Java language

2. Overview of Key Features

2.1 Object Operations Similar to JPA, with Targeted Enhancements (Including Cascading)

Generate the corresponding POJO classes from database tables using the quickvo tool, then simply introduce SqlToyLazyDao, which comes with sqltoy, to complete all operations.

   StaffInfoVO staffInfo = new StaffInfoVO(); 
   //Save
   sqlToyLazyDao.save(staffInfo);
   //Delete
   sqlToyLazyDao.delete(new StaffInfoVO("S2007"));

   //public Long update(Serializable entity, String... forceUpdateProps);
   // Force an update of the photo property here; other properties with null values will be skipped automatically
   sqlToyLazyDao.update(staffInfo, "photo");

   //Deep update: update all fields regardless of whether their values are null
   sqlToyLazyDao.updateDeeply(staffInfo);

   List<StaffInfoVO> staffList = new ArrayList<StaffInfoVO>();
   StaffInfoVO staffInfo = new StaffInfoVO();
   StaffInfoVO staffInfo1 = new StaffInfoVO();
   staffList.add(staffInfo);
   staffList.add(staffInfo1);
   //Batch save or update
   sqlToyLazyDao.saveOrUpdateAll(staffList);
   //Batch save
   sqlToyLazyDao.saveAll(staffList);
   ...............
   sqlToyLazyDao.loadByIds(StaffInfoVO.class,"S2007")
   //Uniqueness validation
   sqlToyLazyDao.isUnique(staffInfo, "staffCode");

2.2 Supports Object-Based Queries in Code

The unified rule in sqltoy is that you can pass SQL statements directly in your code or use the corresponding sqlId from an XML file.

/**
 * @todo Pass parameters through an object to simplify the paramName[], paramValue[] parameter-passing approach
 * @param <T>
 * @param sqlOrNamedSql Can be a literal SQL statement or the corresponding sqlId in an XML file
 * @param entity        Pass parameters through an object and return results according to the object type
 */
 public <T extends Serializable> List<T> findBySql(final String sqlOrNamedSql, final T entity);

Single-table queries based on objects, with cached translation

public Page<StaffInfoVO> findStaff(Page<StaffInfoVO> pageModel, StaffInfoVO staffInfoVO) {
     // SQL can be written directly in code; complex SQL is recommended to be defined in XML
     // In single-table entity query scenarios, SQL fields can use Java class property names
     return findPageEntity(pageModel, StaffInfoVO.class, EntityQuery.create()
  .where("#[staffName like :staffName]#[and createTime>=:beginDate]#[and createTime<=:endDate]")
  .values(staffInfoVO));
}

Update or delete after object-based queries

// Demonstrate record updates using condition-based queries in code without writing direct SQL
public Long updateByQuery() {
     return sqlToyLazyDao.updateByQuery(StaffInfoVO.class,
    EntityUpdate.create().set("createBy", "S0001")
                     .where("staffName like ?").values("Zhang"));
}

// Delete records using condition-based queries in code without writing direct SQL
sqlToyLazyDao.deleteByQuery(StaffInfoVO.class, EntityQuery.create().where("status=?").values(0));

2.3 An Extremely Simple and Intuitive Way to Write SQL

The sqltoy approach (the purpose of the SQL is immediately clear at a glance, subsequent modifications and adjustments are very convenient, and you can simply copy it into a database client with minimal changes before executing it)
The conditional organization mechanism in sqltoy is very simple: #[order_id=:orderId] is equivalent to if(:orderId<>null) sql.append(order_id=:orderId); Any condition inside #[] is removed if even one of its parameters is null.
Supports multi-level nesting, such as: #[and t.order_id=:orderId #[and t.order_type=:orderType]]
Condition evaluation also supports the highly flexible @if() syntax, such as #[@if(:param>=xx ||:param<=xx1) sql statement], providing convenience for special and complex scenarios.

//1. Separate condition value processing from the actual SQL
//2. Preprocess and normalize condition values using common methods defined in filters (most values do not require additional processing)
<sql id="show_case">
<filters>
   <!-- If statusAry contains -1 (representing all), set statusAry to null so it is excluded from conditional filtering -->
   <eq params="statusAry" value="-1" />
</filters>
<value><![CDATA[
  select   *
  from sqltoy_device_order_info t 
  where #[t.status in (:statusAry)]
      #[and t.ORDER_ID=:orderId]
      #[and t.ORGAN_ID in (:authedOrganIds)]
      #[and t.STAFF_ID in (:staffIds)]
      #[and t.TRANS_DATE>=:beginAndEndDate[0]]
      #[and t.TRANS_DATE<:beginAndEndDate[1]]    
  ]]></value>
</sql>

Equivalent functionality using MyBatis

<select id="show_case" resultMap="BaseResultMap">
 select *
 from sqltoy_device_order_info t 
 <where>
     <if test="statusAry!=null">
  and t.status in
  <foreach collection="statusAry" item="status" separator="," open="(" close=")">  
            #{status}  
   </foreach>  
    </if>
    <if test="orderId!=null">
  and t.ORDER_ID=#{orderId}
    </if>
    <if test="authedOrganIds!=null">
  and t.ORGAN_ID in
  <foreach collection="authedOrganIds" item="organ_id" separator="," open="(" close=")">  
            #{order_id}  
   </foreach>  
    </if>
    <if test="staffIds!=null">
  and t.STAFF_ID in
  <foreach collection="staffIds" item="staff_id" separator="," open="(" close=")">  
            #{staff_id}  
   </foreach>  
    </if>
    <if test="beginDate!=null">
  and t.TRANS_DATE>=#{beginDate}
    </if>
    <if test="endDate!=null">
  and t.TRANS_DATE<#{endDate}
    </if>
</where>
</select>

2.4 Built-in SQL Injection Prevention, Execution Process:

Assume the SQL statement is as follows

select   *
from sqltoy_device_order_info t 
where #[t.ORGAN_ID in (:authedOrganIds)]
      #[and t.TRANS_DATE>=:beginDate]
      #[and t.TRANS_DATE<:endDate]

Java invocation process

sqlToyLazyDao.findBySql(sql, MapKit.keys("authedOrganIds","beginDate", "endDate").values(authedOrganIdAry,beginDate,null),
                          DeviceOrderInfoVO.class);

The final SQL executed looks like this:

select   *
from sqltoy_device_order_info t 
where t.ORDER_ID=?
      and t.ORGAN_ID in (?,?,?)
      and t.TRANS_DATE>=?

Then set the condition values using: pst.set(index,value)

2.5 Highly Optimized Pagination

2.5.1 Pagination Features

1. Fast Pagination: @fast() retrieves the data for a single page first and then performs the join queries, significantly improving performance.
2. Pagination Optimizer: page-optimize reduces pagination queries from two executions to approximately 1.3–1.5 executions (using caching to avoid repeatedly querying the total record count for identical query conditions within a specified period).
3. The pagination count query in sqltoy is not simply implemented as select count(1) from (original SQL). Instead, it intelligently determines whether it can be transformed into: select count(1) from 'the statement after FROM', and automatically removes the outermost ORDER BY clause.
4. sqltoy supports parallel queries using parallel="true", querying the total record count and the data for the current page simultaneously to significantly improve performance.
5. Even in highly exceptional cases, sqltoy provides optimized pagination handling. For example, when paginating complex queries such as: with t1 as (),t2 as @fast(select * from table1) select * from xxx, sqltoy's count query becomes: with t1 as () select count(1) from table1. If the query is: with t1 as @fast(select * from table1) select * from t1, the count SQL becomes: select count(1) from table1.

2.5.2 Pagination SQL Example

<!-- Fast pagination and pagination optimization demonstration -->
<sql id="sqltoy_fastPage">
  <!-- Pagination optimizer: caches the total record count for identical query conditions within a specified time period, eliminating the need to query the total count every time -->
  <!-- parallel: Whether to query the total record count and current page data in parallel. Cache optimization is disabled when alive-max=1 -->
  <!-- alive-max: Maximum number of total record counts stored for different query conditions; alive-seconds: How long a query condition's record count remains valid (e.g., 120 seconds; the count is queried again after this threshold) -->
  <page-optimize parallel="true" alive-max="100" alive-seconds="120" />
  <value>
    <![CDATA[
    select t1.*,t2.ORGAN_NAME 
    -- @fast() retrieves 10 records first (the actual number is determined by pageSize), then performs the join
    from @fast(select t.*
         from sqltoy_staff_info t
         where t.STATUS=1 
           #[and t.STAFF_NAME like :staffName] 
         order by t.ENTRY_DATE desc
          ) t1 
    left join sqltoy_organ_info t2 on  t1.organ_id=t2.ORGAN_ID
      ]]>
  </value>

  <!-- A custom count-sql is provided here for highly exceptional scenarios requiring extreme performance optimization -->
  <!-- <count-sql></count-sql> -->
</sql>

2.5.3 Java Code for Calling Pagination

/**
 *  Pass parameters using the object-based approach
 */
public void findPageByEntity() {
  StaffInfoVO staffVO = new StaffInfoVO();
  // Pass parameters as query conditions
  staffVO.setStaffName("Chen");
  // Uses the pagination optimizer
  // First call: Executes two queries, one for the count and one to retrieve records
        // Second call: Within the specified validity period, the count is retrieved from the cache, so only the query for the current page is executed
  Page result = sqlToyLazyDao.findPageBySql(new Page(), "sqltoy_fastPage", staffVO);
}

2.6 Clever Cache-Based Translation That Minimizes Multi-Table Joins

1. Cache-based translation: Converts codes into names, avoiding join queries, greatly simplifying SQL and improving query performance.
2. Fuzzy matching by cached names: Retrieves the exact code to use as a condition, avoiding fuzzy LIKE queries on joined tables.

// Supports cache translation using object property annotations
@Translate(cacheName = "dictKeyName", cacheType = "DEVICE_TYPE", keyField = "deviceType")
private String deviceTypeName;

@Translate(cacheName = "staffIdName", keyField = "staffId")
private String staffName;
<sql id="sqltoy_order_search">
  <!-- Cache translation for device types
        cache: The name of the specific cache definition.
        cache-type: Generally used for data dictionaries to provide a category-based filtering condition.
  columns: Field names queried in SQL. Multiple fields can be translated by separating them with commas.
  cache-indexs: The column corresponding to the cached data name. Defaults to the second column if omitted (zero-based indexing; 1 indicates the second column).
        For example, if the cached data structure is: key, name, fullName, the third column represents the full name.
  -->
  <translate cache="dictKeyName" cache-type="DEVICE_TYPE" columns="deviceTypeName" cache-indexs="1"/>
  <!-- Employee name translation. Multiple fields can be translated simultaneously using the same cache. -->
  <translate cache="staffIdName" columns="staffName,createName" />
  <filters>
    <!-- Reverse lookup using the cache to match names and retrieve IDs for precise queries. -->
    <cache-arg cache-name="staffIdNameCache" param="staffName" alias-name="staffIds"/>
  </filters>
  <value>
  <![CDATA[
  select   ORDER_ID,
    DEVICE_TYPE,
    DEVICE_TYPE deviceTypeName,-- Device category name
    STAFF_ID,
    STAFF_ID staffName, -- Employee name
    ORGAN_ID,
    CREATE_BY,
    CREATE_BY createName -- Creator name
  from sqltoy_device_order_info t 
  where #[t.ORDER_ID=:orderId]
        #[and t.STAFF_ID in (:staffIds)]
    ]]>
  </value>
</sql>

2.7 Parallel Queries

API Specifications

// parallQuery is intended for queries (do not use it during transaction operations). sqltoy provides powerful methods, but users must exercise sound judgment when deciding whether their use is appropriate.
/**
  * @TODO Execute parallel queries and return a one-dimensional List. The List contains one result object for each query. paramNames and paramValues are the combined condition parameters for all SQL statements.
  * @param parallQueryList
  * @param paramNames
  * @param paramValues
  */
public <T> List<QueryResult<T>> parallQuery(List<ParallQuery> parallQueryList, String[] paramNames,
			Object[] paramValues);

Usage Example

// Define parameters
String[] paramNames = new String[] { "userId", "defaultRoles", "deployId", "authObjType" };
Object[] paramValues = new Object[] { userId, defaultRoles, GlobalConstants.DEPLOY_ID,
    SagacityConstants.TempAuthObjType.GROUP };
// Execute 2 SQL queries simultaneously using parallel queries. The condition parameters are shared by both queries.
List<QueryResult<TreeModel>> list = super.parallQuery(
    Arrays.asList(
            ParallQuery.create().sql("webframe_searchAllModuleMenus").resultType(TreeModel.class),
        ParallQuery.create().sql("webframe_searchAllUserReports").resultType(TreeModel.class)),
    paramNames, paramValues);

2.8 Cross-Database Support

1. Provides Hibernate-like object operations and automatically generates the corresponding database dialect.
2. Provides commonly used queries such as pagination, TOP queries, and random record retrieval, avoiding the need to write different SQL for different databases.
3. Provides a standardized drill-down query method for tree-structured tables, replacing traditional recursive queries with a single approach compatible with all databases.
4. sqltoy provides numerous algorithm-based auxiliary implementations, replacing traditional SQL with algorithms to a considerable extent and enabling cross-database compatibility.
5. sqltoy provides function replacement capabilities. For example, Oracle statements can be executed on MySQL or SQL Server by replacing Oracle functions with their MySQL equivalents when SQL is loaded, significantly improving code portability and productization. Default functions: SubStr\Trim\Instr\Concat\Nvl. For implementation details, refer to the code in org.sagacity.sqltoy.plugins.function.Nvl.

 <!-- Automatic cross-database function replacement (optional), suitable for cross-database software products, such as developing with MySQL and deploying with Oracle -->
   <property name="functionConverts" value="default">
   <!-- You can also define and extend the configuration yourself as needed:
   <property name="functionConverts">
     <list>
       <value>org.sagacity.sqltoy.plugins.function.Nvl</value>
       <value>org.sagacity.sqltoy.plugins.function.SubStr</value>
       <value>org.sagacity.sqltoy.plugins.function.Now</value>
       <value>org.sagacity.sqltoy.plugins.function.Length</value>
     </list>
   </property> -->

6. Using the sqlId+dialect mode, you can write SQL for specific databases. sqltoy retrieves the actual SQL to execute based on the database type. The lookup order is: dialect_sqlId->sqlId_dialect->sqlId. For example, if the database is MySQL and sqlId is sqltoy_showcase, the actual SQL executed will be sqltoy_showcase_mysql.

<sql id="sqltoy_showcase">
    <value>
      <![CDATA[
      select * from sqltoy_user_log t 
      where t.user_id=:userId 
        ]]>
    </value>
  </sql>
        <!-- sqlId_database dialect (lowercase) -->
  <sql id="sqltoy_showcase_mysql">
    <value>
      <![CDATA[
      select * from sqltoy_user_log t 
      where t.user_id=:userId 
        ]]>
    </value>
  </sql>

2.9 Provides row-column transformation, grouping and aggregation, year-over-year and month-over-month comparisons, etc.

Fruit Sales Records Table

Category,Sales Month,Number of Sales,Sales Quantity (Tons),Sales Amount (10,000 Yuan) Apples,May 2019,12,2000,2400 Apples,April 2019,11,1900,2600 Apples,March 2019,13,2000,2500 Bananas,May 2019,10,2000,2000 Bananas,April 2019,12,2400,2700 Bananas,March 2019,13,2300,2700

2.9.1 Row-to-Column Transformation (Column-to-Row Transformation Is Also Supported)

<!-- Row-to-column transformation -->
<sql id="pivot_case">
  <value>
  <![CDATA[
  select t.fruit_name,t.order_month,t.sale_count,t.sale_quantity,t.total_amt 
  from sqltoy_fruit_order t
  order by t.fruit_name ,t.order_month
  ]]>
  </value>
  <!-- Row-to-column transformation: use order_month as horizontal category headers and pivot the three metrics from sale_count to total_amt into rows -->
  <pivot start-column="sale_count" end-column="total_amt"  group-columns="fruit_name" category-columns="order_month" />
</sql>

Effect

Category,March 2019,April 2019,May 2019 ,Count,Quantity,Total Amount,Count,Quantity,Total Amount,Count,Quantity,Total Amount Bananas,13,2300,2700,12,2400,2700,10,2000,2000 Apples,13,2000,2500,11,1900,2600,12,2000,2400

2.9.2 Grouping, Aggregation, and Average Calculation (Supports Arbitrary Hierarchy Levels)

<sql id="group_summary_case">
  <value>
    <![CDATA[
    select t.fruit_name,t.order_month,t.sale_count,t.sale_quantity,t.total_amt 
    from sqltoy_fruit_order t
    order by t.fruit_name ,t.order_month
    ]]>
  </value>
  <!-- Whether to reverse the order -->  
  <summary columns="sale_count,sale_quantity,total_amt" reverse="true">
    <!-- Maintain the hierarchy order from highest to lowest -->
    <global sum-label="Grand Total" label-column="fruit_name" />
    <group group-column="fruit_name" sum-label="Subtotal" label-column="fruit_name" />
  </summary>
</sql>

Effect

Category,Sales Month,Number of Sales,Sales Quantity (Tons),Sales Amount (10,000 Yuan),Grand Total

7,112,600,14,900,Subtotal

3,659,007,500,Apples,May 2019,12,2000,2400,Apples,April 2019,11,1900,2600,Apples,March 2019,13,2000,2500,Subtotal

3,567,007,400,Bananas,May 2019,10,2000,2000,Bananas,April 2019,12,2400,2700,Bananas,March 2019,13,2300,2700

2.9.3 Perform Column Pivoting First, Then Calculate Month-over-Month Changes

<!-- Column-to-column month-over-month comparison example -->
<sql id="cols_relative_case">
  <value>
  <![CDATA[
    select t.fruit_name,t.order_month,t.sale_count,t.sale_amt,t.total_amt 
    from sqltoy_fruit_order t
    order by t.fruit_name ,t.order_month
  ]]>
  </value>
  <!-- Data pivoting, converting rows into columns, displaying order_month as columns, with three metrics under each month -->
  <pivot start-column="sale_count" end-column="total_amt"  group-columns="fruit_name" category-columns="order_month" />
  <!-- Calculate month-over-month comparisons between columns -->
  <cols-chain-relative group-size="3" relative-indexs="1,2" start-column="1" format="#.00%" />
</sql>

Effect

Category2019 March2019 April2019 MayOrder CountQuantityChange vs. Previous MonthTotal AmountChange vs. Previous MonthOrder CountQuantityChange vs. Previous MonthTotal AmountChange vs. Previous MonthOrder CountQuantityChange vs. Previous MonthTotal AmountBanana132300

2700

1224004.30%27000.00%102000-16.70%2000-26.00%Apple132000

2500

111900-5.10%26004.00%1220005.20%2400-7.70%

2.10 Database and Table Sharding

2.10.1 Querying Sharded Databases and Tables (Database sharding and table sharding strategies can be used simultaneously)

 For SQL, refer to the quickstart project: com/sqltoy/quickstart/sqltoy-quickstart.sql.xml file
   <!-- Database sharding example -->
  <sql id="qstart_db_sharding_case">
    <sharding-datasource strategy="hashDataSource"
      params="userId" />
    <value>
      <![CDATA[
      select * from sqltoy_user_log t 
      -- userId is a required key field for database sharding
      where t.user_id=:userId 
      #[and t.log_date>=:beginDate]
      #[and t.log_date<=:endDate]
        ]]>
    </value>
  </sql>

  <!-- Table sharding example -->
  <sql id="qstart_sharding_table_case">
    <sharding-table tables="sqltoy_trans_info_15d"
      strategy="realHisTable" params="beginDate" />
    <value>
      <![CDATA[
      select * from sqltoy_trans_info_15d t 
      where t.trans_date>=:beginDate
      #[and t.trans_date<=:endDate]
        ]]>
    </value>
  </sql>

2.10.2 Operating on Sharded Databases and Tables (The vo objects are automatically generated from the database by the quickvo tool, and custom annotations will not be overwritten)

@Sharding Configures database and table sharding strategies through annotations on objects

For a demonstration, refer to: com.sqltoy.quickstart.ShardingSearchTest

package com.sqltoy.showcase.vo;
import java.time.LocalDate;
import java.time.LocalDateTime;
import org.sagacity.sqltoy.config.annotation.Sharding;
import org.sagacity.sqltoy.config.annotation.SqlToyEntity;
import org.sagacity.sqltoy.config.annotation.Strategy;
import com.sagframe.sqltoy.showcase.vo.base.AbstractUserLogVO;

/*
 * db is the database sharding strategy configuration, while table is the table sharding strategy configuration. Both can be configured simultaneously or independently.
 * The strategy name must match the bean definition name in Spring. fields specifies which field values of the object are used as the basis for determining the strategy. One or multiple fields can be specified.
 * maxConcurrents: Optional configuration specifying the maximum number of concurrent operations. maxWaitSeconds: Optional configuration specifying the maximum waiting time in seconds.
 */
@Sharding(db = @Strategy(name = "hashBalanceDBSharding", fields = { "userId" }),
    // table = @Strategy(name = "hashBalanceSharding", fields = {"userId" }),
    maxConcurrents = 10, maxWaitSeconds = 1800)
@SqlToyEntity
public class UserLogVO extends AbstractUserLogVO {

  private static final long serialVersionUID = 1296922598783858512L;

  /** default constructor */
  public UserLogVO() {
    super();
  }
}

2.11 Five Non-Database-Related Primary Key Generation Strategies (Extensible)

In addition to database-provided sequence\identity, the primary key strategies also include the following database-independent strategies. They are automatically generated in VO objects through quickvo configuration.

2.11.1 shortNanoTime: 22-digit ordered and secure ID, format: 13-digit current milliseconds + 6-digit nanoseconds + 3-digit host ID

2.11.2 nanoTimeId: 26-digit ordered and secure ID, format: 15-digit yyMMddHHmmssSSS + 6-digit nanoseconds + 2 digits (thread ID + random number) + 3-digit host ID

2.11.3 uuid: 32-character UUID

2.11.4 SnowflakeId: Snowflake algorithm ID

2.11.5 redisId: Rule-based primary key ID generated using Redis

Generates rule-based, ordered IDs based on object property values. For example: order type is P for Purchase and S for Sales; trade type is I for domestic trade and O for foreign trade. The order number generation rule is: 1-digit order type + 1-digit trade type + yyMMdd + 3-digit sequence number (automatically expands beyond 3 digits). The resulting order number will be: SI191120001

2.12 Support for Native Elasticsearch Queries

2.13 Support for the elasticsearch-sql Plugin Mode and SQL Mode

2.14 Automatic Reloading of SQL File Changes for Convenient Development and Debugging

2.15 Unified Assignment of Common Fields, Such as Creator, Creation Time, Modifier, and Modification Time

2.16 Provides Date and Number Formatting and Secure Data Masking for Query Results, Making Complex Tasks Simple

3. Integration Guide

3.1 Refer to quickstart under trunk and read readme.md to get started

package com.sqltoy.quickstart;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.transaction.annotation.EnableTransactionManagement;

/**
 * 
 * @project sqltoy-quickstart
 * @description Quickstart main program entry point
 * @author zhongxuchen
 * @version v1.0, Date: July 17, 2020
 * @modify July 17, 2020, modification description
 */
@SpringBootApplication
@ComponentScan(basePackages = { "com.sqltoy.config", "com.sqltoy.quickstart" })
@EnableTransactionManagement
public class SqlToyApplication {
  /**
   * @param args
   */
  public static void main(String[] args) {
    SpringApplication.run(SqlToyApplication.class, args);
  }
}

3.2 application.properties sqltoy Configuration

# sqltoy config
spring.sqltoy.sqlResourcesDir=classpath:com/sqltoy/quickstart
spring.sqltoy.translateConfig=classpath:sqltoy-translate.xml
spring.sqltoy.debug=true
#spring.sqltoy.reservedWords=status,sex_type
#dataSourceSelector: org.sagacity.sqltoy.plugins.datasource.impl.DefaultDataSourceSelector
#spring.sqltoy.defaultDataSource=dataSource
# Provides unified assignment of common fields (see quickstart source code for details)
spring.sqltoy.unifyFieldsHandler=com.sqltoy.plugins.SqlToyUnifyFieldsHandler
#spring.sqltoy.printSqlTimeoutMillis=200000

3.3 Cache Translation Configuration File sqltoy-translate.xml

<?xml version="1.0" encoding="UTF-8"?>
<sagacity
  xmlns="[http://www.sagframe.com/schema/sqltoy-translate\](http://www.sagframe.com/schema/sqltoy-translate\)"
  xmlns:xsi="[http://www.w3.org/2001/XMLSchema-instance\](http://www.w3.org/2001/XMLSchema-instance\)"
  xsi:schemaLocation="[http://www.sagframe.com/schema/sqltoy-translate](http://www.sagframe.com/schema/sqltoy-translate) [http://www.sagframe.com/schema/sqltoy/sqltoy-translate.xsd\](http://www.sagframe.com/schema/sqltoy/sqltoy-translate.xsd\)">
  <!-- The cache has a default expiration time of 1 hour, so only frequently accessed caches need to be checked promptly -->
  <cache-translates>
    <!-- Retrieve cache data directly through SQL queries -->
    <sql-translate cache="dictKeyName"
      datasource="dataSource">
      <sql>
      <![CDATA[
        select t.DICT_KEY,t.DICT_NAME,t.STATUS
        from SQLTOY_DICT_DETAIL t
            where t.DICT_TYPE=:dictType
            order by t.SHOW_INDEX
      ]]>
      </sql>
    </sql-translate>

    <!-- Cache of employee IDs and names -->
    <sql-translate cache="staffIdName"
      datasource="dataSource">
      <sql>
      <![CDATA[
        select STAFF_ID,STAFF_NAME,STATUS
        from SQLTOY_STAFF_INFO
      ]]>
      </sql>
    </sql-translate>
    <!-- Cache of organization IDs and names -->
    <sql-translate cache="organIdName"
      datasource="dataSource">
      <sql>
      <![CDATA[
        select ORGAN_ID,ORGAN_NAME from SQLTOY_ORGAN_INFO order by SHOW_INDEX
      ]]>
      </sql>
    </sql-translate>
  </cache-translates>

  <!-- Cache refresh checks, supporting multiple checks based on SQL, services, and REST services -->
  <cache-update-checkers>
    <!-- SQL-based cache update check -->
    <sql-increment-checker cache="organIdName"
      check-frequency="60" datasource="dataSource">
      <sql><![CDATA[
      --#not_debug#--
      select ORGAN_ID,ORGAN_NAME 
      from SQLTOY_ORGAN_INFO
      where UPDATE_TIME >=:lastUpdateTime
      ]]></sql>
    </sql-increment-checker>

    <!-- Incremental update: update the cache directly when changes are detected -->
    <sql-increment-checker cache="staffIdName"
      check-frequency="30" datasource="dataSource">
      <sql><![CDATA[
      --#not_debug#--
      select STAFF_ID,STAFF_NAME,STATUS
      from SQLTOY_STAFF_INFO
          where UPDATE_TIME >=:lastUpdateTime
      ]]></sql>
    </sql-increment-checker>

    <!-- Incremental update: the first column of query results with internal grouping is the group identifier -->
    <sql-increment-checker cache="dictKeyName"
      check-frequency="15" has-inside-group="true" datasource="dataSource">
      <sql><![CDATA[
      --#not_debug#--
      select t.DICT_TYPE,t.DICT_KEY,t.DICT_NAME,t.STATUS
      from SQLTOY_DICT_DETAIL t
          where t.UPDATE_TIME >=:lastUpdateTime
      ]]></sql>
    </sql-increment-checker>
  </cache-update-checkers>
</sagacity>

For actual business development, you can use SqlToyCRUDService directly to perform routine operations. This avoids writing your own service for simple object operations. For more complex logic, you can write your own service and interact with the database directly by calling SqlToyLazyDao provided by sqltoy!

@RunWith(SpringRunner.class)
@SpringBootTest(classes = SqlToyApplication.class)
public class CrudCaseServiceTest {
  @Autowired
  private SqlToyCRUDService sqlToyCRUDService;

  /**
   * Create an employee record
   */
  @Test
  public void saveStaffInfo() {
    StaffInfoVO staffInfo = new StaffInfoVO();
    staffInfo.setStaffId("S190715005");
    staffInfo.setStaffCode("S190715005");
    staffInfo.setStaffName("Test Employee 4");
    staffInfo.setSexType("M");
    staffInfo.setEmail("test3@aliyun.com");
    staffInfo.setEntryDate(LocalDate.now());
    staffInfo.setStatus(1);
    staffInfo.setOrganId("C0001");
    staffInfo.setPhoto(FileUtil.readAsBytes("classpath:/mock/staff_photo.jpg"));
    staffInfo.setCountry("86");
    sqlToyCRUDService.save(staffInfo);
  }
 }

4. Key Notes on sqltoy SQL

4.1 The Simplest sqltoy SQL Rule: #[] Paired Symbols

#[] is equivalent to if (any parameter in the enclosed statement is null): true, remove the entire #[] block; false, remove #[ and ] and include the enclosed SQL as part of the executed statement.
#[] supports nesting. For example, #[t.status=:status #[and t.createDate>=:createDate]] executes the if(null) logic from the innermost block outward.
Use filters for unified null-value preprocessing and conditional handling. Below is the complete list of condition filters and other functions provided by sqltoy. Don't be intimidated by the lengthy explanations; 99% of them are rarely needed. In normal use, filters typically only require eq and to-date.

<sql id="show_case">
  <!-- Use the logic in filters to convert query conditions to null; some logic also performs secondary conversions on parameters.
       By default, empty strings, empty collections, and empty arrays are all converted to null.
             parmas indicates that multiple parameters can be specified using commas; param indicates that only a single parameter is supported.
  -->  
  <filters>
    <!-- Equal to: for example, if the frontend passes -1 for the organization type, it is converted to null and excluded from condition filtering. -->
    <eq params="organType" value="-1" />
    <!-- Convert the condition value to null if it falls within a specified range. -->
    <between params="" start-value="0" end-value="9999" />

    <!-- Convert parameter values to date format. format can be a custom format such as yyyy-MM-dd, or one of the following:
     first_of_day: first day of the month; last_of_day: last day of the month; first_of_year: first day of the year; last_of_year: last day of the year. increment-unit defaults to days. -->
    <to-date params="" format="yyyyMMdd" increment-time="1" increment-unit="days"/>
    <!-- Convert parameters to numbers --> 
    <to-number params="" data-type="decimal" />
    <!-- Split a string passed from the frontend into an array -->
    <split data-type="string" params="staffAuthOrgs" split-sign=","/>
    <!-- Less than or equal to -->
    <lte params="" value=""  />
    <!-- Less than -->
    <lt  params=""  value="" />
    <!-- Greater than or equal to -->
    <gte params="" value=""  />
    <!-- Greater than -->
    <gt params="" value=""  />
    <!-- String replacement. By default, all matches are replaced according to the regular expression. When is-first is true, only the first match is replaced. -->
    <replace params="" regex="" value="" is-first="false" />
    <!-- Primary parameter: when a parameter is not null, all parameters specified in excludes are set to null. -->
    <primary param="orderId" excludes="organIds" />
    <!-- Exclusive parameter: when a parameter has a specified value, other parameters are assigned specific values. -->
    <exclusive param="" compare-type="eq" compare-values=""
      set-params="" set-value="" />
    <!-- Use cache-based fuzzy text matching to retrieve exact code values for precise queries -->  
    <cache-arg cache-name="" cache-type="" param="" cache-mapping-indexes="" alias-name=""/>
    <!-- Convert an array into an IN parameter condition and add single quotes -->
    <to-in-arg params=""/>
  </filters>

  <!-- Cache translation. Multiple entries are supported. uncached-template provides supplementary text when no match is found. ${value} represents the displayed key value. For example: key=[${value} is undefined. -->
  <translate cache="dictKeyName" cache-type="POST_TYPE" columns="POST_TYPE"
    cache-indexs="1" uncached-template=""/>

  <!-- Security masking: telephone numbers\names\addresses\card numbers -->
  <!-- Simplest usage: <secure-mask columns="" type="tel"/> -->
  <secure-mask columns="" type="name" head-size="3" tail-size="4"
    mask-code="*****" mask-rate="50" />
  <!-- Database sharding strategy -->
  <sharding-datasource strategy="" />
  <!-- Table sharding strategy -->
  <sharding-table tables="" strategy="" params="" />
  <!-- Pagination optimization: cache the total number of records for identical query conditions. alive-max: retain up to 100 different condition queries for the same SQL statement. alive-seconds: retention period for the total record count of identical paginated queries (in seconds). -->
  <page-optimize alive-max="100" alive-seconds="600" />
  <!-- Date formatting -->
  <date-format columns="" format="yyyy-MM-dd HH:mm:ss"/>
  <!-- Number formatting -->
        <number-format columns="" format=""/>
  <value>
  <![CDATA[
  select t1.*,t2.ORGAN_NAME from 
  @fast(select * from sys_staff_info t
      where #[t.sexType=:sexType]
      #[and t.JOIN_DATE>:beginDate]
      #[and t.STAFF_NAME like :staffName]
      -- Whether the employee is virtual; use @if() for logical evaluation.
      #[@if(:isVirtual==true||:isVirtual==0) and t.IS_VIRTUAL=1]
      ) t1,sys_organ_info t2
        where t1.ORGAN_ID=t2.ORGAN_ID
  ]]>  
  </value>

  <!-- Custom SQL for optimal pagination -->
  <count-sql><![CDATA[]]></count-sql>
  <!-- Aggregation and averaging: use algorithms to implement complex SQL while maintaining database independence -->
  <summary columns="" radix-size="2" reverse="false" sum-site="left">
    <global sum-label="" label-column="" />
    <group sum-label="" label-column="" group-column="" />
  </summary>
  <!-- Concatenate values in a column; equivalent to group_concat in MySQL and WMSYS.WM_CONCAT in Oracle -->
  <link sign="," column="" />
  <!-- Rows to columns (mutually exclusive with unpivot); implemented using database-independent algorithms -->
  <pivot category-columns="" group-columns="" start-column="" end-column=""
    default-value="0" />
  <!-- Columns to rows -->
  <unpivot columns="" values-as-column="" />
</sql>

5. Explanation of Key sqltoy Code

5.1 The main components of sqltoy-orm are divided into the following sections:

SqlToyDaoSupport: Provides a basic DAO for developers to inherit, integrating all methods for database operations.
SqlToyLazyDao: Provides developers with a convenient DAO interface, allowing them to focus exclusively on writing Service business logic. Developers can call lazyDao directly within their services.
SqltoyCRUDService: A simple Service wrapper that provides convenient object-based Service calls for the Controller layer. For example, simple operations such as save(pojo) do not require additional Service code.
DialectFactory: The database dialect factory. Based on the current database connection's dialect, sqltoy invokes the corresponding implementation for different databases.
SqlToyContext: The sqltoy context configuration. It serves as the core configuration and communication hub of the entire framework. The Spring configuration mainly involves configuring SqlToyContext.
EntityManager: Encapsulated within SqlToyContext, it manages POJO objects and establishes relationships between objects and database tables. sqltoy scans and loads objects through the SqlToyEntity annotation.
ScriptLoader: The SQL configuration file loader and parser, encapsulated within SqlToyContext. SQL files must strictly follow the *.sql.xml naming convention.
TranslateManager: The cache translation manager, responsible for loading XML configuration files and cache implementation classes for cached translations. sqltoy provides the interface and a default local caching implementation based on ehcache, which offers the highest performance. Distributed caches such as redis introduce excessive I/O overhead. Cache translation is a frequently invoked operation, and commonly cached data includes relatively stable information that changes infrequently, such as employees, organizations, data dictionaries, product categories, and regions.
ShardingStragety: The database and table sharding strategy manager. Since version 4.x, strategies no longer need to be explicitly defined. They only need to be configured through Spring, and sqltoy manages them dynamically when needed.

5.2 How to Quickly Understand sqltoy:

Start with SqlToyLazyDao as the entry point to explore all the features provided by sqltoy.
SqlToyDaoSupport contains the actual implementation of SqlToyLazyDao's functionality.
Starting from DialectFactory, you can access the implementation entry points for different database dialects and trace their specific implementation logic. You will find implementations for pagination, random record retrieval, and optimized pagination in Oracle, MySQL, and other databases.
EntityManager: Here you can find out how POJOs are scanned and constructed into models, and understand how database operations through POJOs are ultimately converted into corresponding SQL statements for execution.
ParallelUtils: The parallel execution engine for object-based database and table sharding. This class shows how collections are divided into groups targeting different databases and tables during batch sharding operations, and how these operations are scheduled for parallel execution.
SqlToyContext: The context for sqltoy configuration. This class provides an overview of the entire sqltoy framework.
PageOptimizeUtils: Shows the default implementation principles behind pagination optimization.

下载地址免费

资源直链https://gitee.com/sagacity/sagacity-sqltoy

去下载