sagacity-sqltoy Documentation

The sqltoy-orm module consists of several key architectural components:

• SqlToyDaoSupport: The base class for developers to extend; it integrates all database operation methods.
• LightDao: A shortcut DAO for use within services to focus on business logic.
• DialectFactory: A factory that selects the appropriate database dialect implementation (e.g., Oracle, MySQL) based on the current connection.
• SqlToyContext: The core configuration and exchange area of the framework; Spring configurations primarily target this context.
• EntityManager: Managed within SqlToyContext, it maps POJO objects to database tables using @SqlToyEntity annotations.
• ScriptLoader: A parser for SQL configuration files, which must follow the *.sql.xml naming convention.
• TranslateManager: Manages cache translation XML files and implementations. It defaults to a high-efficiency local Ehcache implementation.
• ShardingStrategy: Manages database sharding and partitioning. In version 4.x+, strategies are managed dynamically via Spring definitions.

SqlToy can reduce database load by replacing expensive JOIN operations with cache lookups.

1. Cache Translation (<translate />)

Converts database IDs/codes into human-readable names using a cache.

• cache: The name of the defined cache.
• cache-type: A category filter (e.g., for data dictionaries).
• columns: The SQL column names to be translated (comma-separated).
• cache-indexs: The column index in the cache data containing the name (default is 1).

2. Cache Arguments (<cache-arg />)

Allows you to perform a reverse lookup: take a name/value from the input and find its corresponding ID in the cache to use as a precise query condition.

• cache-name: The cache to search.
• param: The input parameter name.
• alias-name: The name of the resulting ID parameter to be used in the SQL.

<sql id="sqltoy_order_search">
	<translate cache="dictKeyName" cache-type="DEVICE_TYPE" columns="deviceTypeName" cache-indexs="1"/>
	<translate cache="staffIdName" columns="staffName,createName" />
	<filters>
		<!-- Reverse lookup: find ID from name to use in WHERE clause -->
		<cache-arg cache-name="staffIdNameCache" param="staffName" alias-name="staffIds"/>
	</filters>
	<value>
	<![CDATA[
	select 	ORDER_ID,
		DEVICE_TYPE,
		DEVICE_TYPE deviceTypeName,
		STAFF_ID,
		STAFF_ID staffName,
		ORGAN_ID,
		CREATE_BY,
		CREATE_BY createName
	from sqltoy_device_order_info t 
	where #[t.ORDER_ID=:orderId]
	      #[and t.STAFF_ID in (:staffIds)]
	]]>
</value>
</sql>

To integrate SqlToy into a Spring Boot application, follow these steps:

1. Configure Data Source: Ensure your Spring Boot project has a configured data source (e.g., using HikariCP via spring-boot-starter-jdbc).
2. Add Dependency: Include sagacity-sqltoy-spring-starter in your pom.xml.
3. Configure application.yml: Set up the sqltoy configuration block to define where your SQL XML files are located and enable debug mode if needed.
4. Implement Services: Use LightDao for basic CRUD operations and custom SQL for complex queries.

spring:
    datasource:
       name: dataSource
       type: com.zaxxer.hikari.HikariDataSource
       driver-class-name: com.mysql.cj.jdbc.Driver
       username: helloworld
       password: helloworld
       isAutoCommit: false
       url: jdbc:mysql://127.0.0.1:3306/helloworld?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8&useSSL=false&allowPublicKeyRetrieval=true
    sqltoy:
       # Optional: path to sql.xml files (supports multiple paths)
       sqlResourcesDir: classpath:com/sqltoy/helloworld
       # If true, prints executed SQL and auto-reloads SQL files on change
       debug: true

SqlToy provides several advanced pagination features to optimize performance:

1. Fast Pagination (@fast()): Implements a strategy where the system first fetches the single page of data and then performs associated queries, significantly increasing speed.
2. Page Optimizer (<page-optimize />): Reduces the number of queries from 2 to approximately 1.3–1.5 by caching the total record count for identical query conditions over a specified period.
3. Parallel Querying (parallel="true"): Executes the total count query and the data retrieval query simultaneously.
4. Smart Count SQL: Automatically optimizes the count(1) query by stripping ORDER BY clauses and intelligently determining the correct FROM clause.

Configuration Options for <page-optimize />:

• parallel: Boolean. Whether to query total count and page data in parallel.
• alive-max: Maximum number of different query condition sets to cache.
• alive-seconds: Duration (in seconds) that a cached record count remains valid.

<sql id="sqltoy_fastPage">
	<!-- parallel: parallel execution; alive-max: cache size; alive-seconds: cache TTL -->
	<page-optimize parallel="true" alive-max="100" alive-seconds="120" />
	<value><![CDATA[
	select t1.*,t2.ORGAN_NAME 
	-- @fast() fetches page first, then joins
	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>
</sql>

SqlToy supports sharding strategies for both databases and tables. You can apply these via XML configuration or Java annotations.

XML Configuration

Use <sharding-datasource> to distribute queries across multiple databases and <sharding-table> to distribute across multiple tables.

• strategy: The name of the sharding strategy (e.g., hashDataSource, realHisTable).
• params: The field(s) used as the sharding key.

Java Annotation Configuration

Use the @Sharding annotation on your VO (Value Object) classes to define sharding logic.

• db: Configures database sharding using the @Strategy annotation.
• table: Configures table sharding using the @Strategy annotation.
• fields: An array of field names used as the sharding criteria.
• maxConcurrents: (Optional) Maximum number of concurrent threads.
• maxWaitSeconds: (Optional) Maximum time to wait for a shard.

@Sharding(db = @Strategy(name = "hashBalanceDBSharding", fields = { "userId" }),
		// table = @Strategy(name = "hashBalanceSharding", fields = {"userId" }),
	maxConcurrents = 10, maxWaitSeconds = 1800)
@SqlToyEntity
public class UserLogVO extends AbstractUserLogVO {
    // ...
}

Use the <summary> tag to perform hierarchical grouping and summation (e.g., sub-totals and grand totals) on your result sets.

Key attributes:

• columns: A comma-separated list of columns to summarize.
• reverse: If true, the summary rows are placed at the top (e.g., Grand Total first) instead of the bottom.
• <global>: Defines the grand total row. Use sum-label for the label text and label-column to specify which column holds the label.
• <group>: Defines a sub-total row for a specific grouping. Use group-column to specify the grouping field, sum-label for the sub-total text, and label-column for the label position.

<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>
	<!-- reverse 是否反向 -->	
	<summary columns="sale_count,sale_quantity,total_amt" reverse="true">
		<!-- 层级顺序保持从高到低 -->
		<global sum-label="总计" label-column="fruit_name" />
		<!-- order-column: 分组排序列(对同分组进行排序)，order-with-sum:默认为true，order-way:desc/asc -->
		<group group-column="fruit_name" sum-label="小计" label-column="fruit_name" />
	</summary>
</sql>

You can transform rows into columns using the <pivot> tag within a <sql> configuration. This is useful for creating cross-tabulation reports where specific columns become horizontal headers.

Key attributes:

• start-column: The first column in the range to be rotated.
• end-column: The last column in the range to be rotated.
• group-columns: Columns used to group the rows (vertical axis).
• category-columns: Columns used to create the horizontal headers (horizontal axis).

<!-- 行转列 -->
<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>
	<!-- 行转列,将order_month作为分类横向标题，从sale_count列到total_amt 三个指标旋转成行 -->
	<pivot start-column="sale_count" end-column="total_amt" group-columns="fruit_name" category-columns="order_month" />
</sql>

SqlToy provides features to make SQL code portable across different database dialects (e.g., Oracle to MySQL).

1. Function Conversion

You can configure SqlToy to automatically replace specific functions during SQL loading.

• Set spring.sqltoy.functionConverts=default to enable default conversions for SubStr, Trim, Instr, Concat, and Nvl.
• You can also specify custom conversion classes: spring.sqltoy.functionConverts=default,com.yourpackage.Nvl.

2. Dialect-Specific SQL (sqlId + dialect mode)

If you need to write specific SQL for a particular database, use the naming convention sqlId_dialect. SqlToy will look for SQL in this order:

1. sqlId_dialect (e.g., sqltoy_showcase_mysql)
2. sqlId_dialect (e.g., sqltoy_showcase_pgdb)
3. sqlId (the default/fallback)

Example: If you call sqlId: sqltoy_showcase on a MySQL database, SqlToy will prioritize sqltoy_showcase_mysql.

# Enable default function auto-adaptation
spring.sqltoy.functionConverts=default

# Example of defining dialect-specific SQL in XML
<sql id="sqltoy_showcase">
	<value><![CDATA[ select * from sqltoy_user_log t where t.user_id=:userId ]]></value>
</sql>

<sql id="sqltoy_showcase_mysql">
	<value><![CDATA[ select * from sqltoy_user_log t where t.user_id=:userId ]]></value>
</sql>