1. Introduction

1.1 What is SQLtoy-ORM

Sqltoy-orm is more suitable for project ORM framework than Hibernate +myBatis. It has the convenience of Hibernate adding, deleting and changing, but also has more flexible and elegant custom SQL query function than myBatis. Supports the following databases:

• Oracle runs from Oracle 11g to oracle 19C
• Db2 9.5+, starting with 10.5 is recommended
• Mysql supports versions 5.6, 5.7, and 8.0
• Postgresql supports 9.5 and later versions
• Sqlserver supports versions 2008 to 2019. 2012 or later is recommended
• sqlite
• Sybase_iq Supports version 15.4 or later. Version 16 is recommended
• Elasticsearch supports only query and version 5.7+. You are advised to use version 7.3 or later
• clickhouse
• Mongodb (query only)

1.2 Whether to repeat the wheel or not, I just want to say five characteristics first:

• Fundamentally put an end to SQL injection problems, SQL support to write notes, SQL file dynamic update detection, SQL change will automatically overload when developing
• The most intuitive SQL writing mode, when the query conditions a little more complex when it will reflect the value of the late change maintenance is particularly prominent
• Extremely powerful cache translation query: the clever combination of cache reduces query statement table association, greatly simplifies SQL and improves performance.
• The most powerful paging queries: Many people are learning for the first time about fast paging, the very clever processing of paging optimization, and extreme optimization on count statements.
• Cross-database function dialect replacement, such as: ISNULL/IFNULL/NVL, substr/substring, and other different databases

Of course, this is only the five features of SQLTOY, as well as column conversion (commonly known as data rotation), multi-level grouping summary, unified tree structure table (such as organization) query, sub-database sub-table sharding, take random records, take top records, modify and return records, slow SQL reminder and other functions that are suitable for project applications. When you truly understand the huge advantages of the above features, you will have confidence in sqlToy-ORM created by the Chinese!

Sqltoy-orm comes from my personal experience of countless projects summary and thinking, especially on the performance optimization of continuous mining, as for whether the wheel is not important, I hope to help you!

2. Quick description of features

2.1 The most elegant and intuitive SQL writing mode

• Sqltoy (at a glance to understand the meaning of SQL, after the adjustment is also very convenient,copy to the database client to do a little can be executed)

• #[order_id=:orderId] = if(:orderId<>null) sql.append(order_id=:orderId); If one of the parameters in #[] is null, it will be deleted

• Support multi-layer nesting: e.g. #[and t.order_id=:orderId #[and t.order_type=:orderType]]

• Conditional retain # [@ the if (: param > = xx | | : param < = xx1) SQL statement] this @ the if () highly flexible mode, provide special complex scenarios with master key

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

• Mybatis (mybatis)

  select * from sqltoy_device_order_info t and t.ORDER_ID=#{orderId} and t.ORGAN_ID in
  
  #{order_id}
  
  
  
  and t.STAFF_ID in
  
  #{staff_id}
  
  
  
  and t.TRANS_DATE>=#{beginDate} and t.TRANS_DATE<#{endDate}

2.2 Natural prevention of SQL injection, execution process:

• Assume the following SQL statement

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

• Java calling procedure

  sqlToyLazyDao.findBySql(sql, new String[] { “authedOrganIds”,”beginDate”, “endDate”}, new Object[] { 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>=?

• Pst.set (index,value) sets the conditional value. There is no condition that is concatenated directly as a string as part of SQL

2.3 The most powerful paging query

2.3.1 Description of paging features

• 1, fast paging :@fast() to achieve a single page of data and then associated query, greatly improve the speed.
• Page optimizer: Page optimize turns two paging queries into 1.3~1.5 queries with casses
• Select count(1) from (SQL); select count(1) from (SQL); Select count(1) from ‘where’ (select count(1) from ‘where’)
• With t1 as (),t2 as @fast(select * from table1) select * from XXX Select count(1) from table1 with t1 as () select count(1) from table1, With t1 as @fast(select * from table1) select * from t1,count (1) from table1

2.3.1 Paging SQL example

<! < SQL id="sqltoy_fastPage"> -- Paging optimizer, through the cache to achieve consistent query conditions in a certain period of time to cache the total number of records, so that no need to query the total number of records --> <! -- alive-max: indicates the maximum number of records stored under different query conditions. Alive-seconds: > <page-optimize alive-max="100" alive-seconds="120" /> <value> <! *,t2.ORGAN_NAME -- @fast(); [CDATA[select t1.*,t2.ORGAN_NAME -- @fast() 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> <! Custom count-SQL is provided for extreme performance optimization in very special cases --> <! -- <count-sql></count-sql> --> </sql>Copy the code

2.3.3 Paging Java code calls

Public void findPageByEntity() {PaginationModel pageModel = new PaginationModel(); / / Public void findPageByEntity() {PaginationModel pageModel = new PaginationModel(); StaffInfoVO staffVO = new StaffInfoVO(); // Pass the parameter staffvo.setstaffname (" Chen ") as a query condition; / / / / using the paging optimizer first call: to perform the count and take record query twice PaginationModel result = sqlToyLazyDao. FindPageBySql (pageModel, "sqltoy_fastPage", staffVO); System.err.println(JSON.toJSONString(result)); // Set pagemodel.setPageno (2) to the second page; result = sqlToyLazyDao.findPageBySql(pageModel, "sqltoy_fastPage", staffVO); System.err.println(JSON.toJSONString(result)); Public void findPageByParams() {public void findPageByParams() {public void findPageByParams(); PageNo = 1 PaginationModel pageModel = new PaginationModel(); String[] paramNames=new String[]{"staffName"}; Object[] paramValues=new Object[]{" Chen "}; PaginationModel result = sqlToyLazyDao.findPageBySql(pageModel, "sqltoy_fastPage",paramNames,paramValues,StaffInfoVO.class); System.err.println(JSON.toJSONString(result)); }Copy the code

2.4 The most clever cache application, will be multiple table associated query as far as possible into a single table (see the following SQL, if no cache translation need to associate how many tables? How long should the SQL be? How hard to maintain?

• 1, through the cache translation: the code into the name, avoid associated query, greatly simplify SQL and improve query efficiency

• 2. Fuzzy matching of cache names: Obtain accurate codes as conditions to avoid associated fuzzy query like

2.4 Most cross-database

• 1, provide hibernate similar properties of the object operation, automatically generate the corresponding database dialect.

• 2, provide the most commonly used: paging, take top, take random records and other queries, to avoid the different database writing method.

• 3, provides a tree structure table of the standard drill query, instead of the previous recursive query, a way to adapt to all databases.

• 4, SQLTOY provides a large number of algorithms based on the auxiliary implementation, to the greatest extent with the algorithm instead of the previous SQL, the realization of cross-database

• 5, SQLToy provides function replacement functions, such as oracle statements can be executed in mysql or SQLServer (SQL loading will be replaced by mysql functions), to the greatest extent to achieve the code productization. Default: SubStr, Trim, Instr, Concat \ Nvl function; Can see org. Sagacity. Sqltoy. Plugins. The function. The Nvl code implementation

  <! -- Automatic replacement of cross-database functions (optional), suitable for cross-database software products such as mysql development, Oracle deployment -->Copy the code

2.5 Provide column and column conversion (data rotation), avoid writing complex SQL or stored procedures, use algorithms to solve the high requirements of SQL, while implementing database independent (whether mysql or SQL Server)

<! --> < SQL id="sys_unpvoitSearch"> <value> <! [CDATA[ SELECT TRANS_DATE, sum(TOTAL_AMOUNT) TOTAL_AMOUNT, sum(PERSON_AMOUNT) PERSON_AMOUNT, sum(COMPANY_AMOUNT) COMPANY_AMOUNT FROM sys_unpivot_data group by TRANS_DATE ]]> </value> <! <unpivot Columns ="TOTAL_AMOUNT: total amount,PERSON_AMOUNT: personal amount,COMPANY_AMOUNT: Enterprise amount" values-as-column="TRANS_AMOUNT" labels-as-column="AMOUNT_TYPE" /> </sql> <! - transfer line column test - > < SQL id = "sys_pvoitSearch" > < value > <! [CDATA[ select t.TRANS_DATE,t.TRANS_CHANNEL,TRANS_CODE,sum(t.TRANS_AMT) TRANS_AMT from sys_summary_case t group by t.TRANS_DATE,t.TRANS_CHANNEL,TRANS_CODE order by t.TRANS_DATE,t.TRANS_CHANNEL,TRANS_CODE ]]> </value> <pivot category-columns="TRANS_CHANNEL,TRANS_CODE" start-column="TRANS_AMT" default-value="0" default-type="decimal" end-column="TRANS_AMT" group-columns="TRANS_DATE" /> </sql>Copy the code

2.6 Provide group summary averaging algorithm (use algorithm instead of SQL to avoid cross-database syntax inconsistency)

<! < SQL id="sys_summarySearch"> <! <sharding-datasource strategy="multiDataSource" /> <value> <! [CDATA[ select t.TRANS_CHANNEL,t.TRANS_CODE,sum( t.TRANS_AMT ) from sys_summary_case t group by t.TRANS_CHANNEL,t.TRANS_CODE ]]> </value> <! -- reverse indicates that the summary information is displayed (e.g. line 1 is the summary value, lines 2, 3, and 4 are detailed, and vice versa, lines 1, 2, and 3 are not detailed, <summary columns="2" reverse="true" sum-site="left" radix-size="2"> <global sum-label=" total "label-column="0" / > <! <group sum-label=" subtotal/average "label-column="0" group-column="0" average-label=" average" /> </summary> </ SQL >Copy the code

2.7 Sub-database sub-table

2.7.1 Querying Database and Table Information (Both database and table policies can be used simultaneously)

SQL see showcase projects: com/sagframe sqltoy/showcase/sqltoy - showcase. SQL. The XML file Sharding strategy configuration see: SRC/main/resources/spring/spring - sqltoy - sharding. XML configuration <! < SQL datasource strategy="hashBalanceDBSharding" params="userId" /> <value> <! [CDATA[select * from sqltoy_user_log t -- userId where t. us_id =:userId #[and t.log_date>=:beginDate] #[and t.log_date<=:endDate] ]]> </value> </sql> <! < SQL id=" sqltoY_15d_table_sharding_case "> <sharding-table tables=" sqltoY_trans_info_15d" strategy="historyTableStrategy" params="beginDate" /> <value> <! [CDATA[ select * from sqltoy_trans_info_15d t where t.trans_date>=:beginDate #[and t.trans_date<=:endDate] ]]> </value> </sql>Copy the code

2.7.2 Operation database and table (VO objects are automatically generated by QuickVo tool from the database, and customized annotations will not be overwritten)

@sharding implements the policy configuration of the sub-library and sub-table through annotations on the object

Also see: com. Sagframe. Sqltoy. Showcase. ShardingCaseServiceTest for demonstration

package com.sagframe.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; /** * @project sqltoy-showcase * @author zhongxuchen * @version 1.0.0 Table: Sqltoy_user_log,Remark: user log table */ /* * DB is configured,table is configured, * Policy name is the same as the bean definition name in Spring, and fields indicate which fields to use as the value of the object. This can be one or more fields * maxConcurrents: Optional MaxWaitSeconds: Optional configuration, @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(); }}Copy the code

2.8 Five non-database related primary key generation strategies

The primary key policy contains the following database independent primary key policies in addition to the sequence\identity that comes with the database. Automatically generated in VO objects through quickvo configuration.Copy the code

2.8.1 shortNanoTime specifies the 22-bit ordered security ID in the format of 13 current milliseconds, 6 nanoseconds, and 3 host IDS

2.8.2 nanoTimeId 26-bit ordered security ID format: 15-bit yyMMddHHmmssSSS+ 6-bit nanosecond + 2-bit (thread ID + random number)+ 3-bit host ID

2.8.3 uuid: 32-bit uuid

2.8.4 SnowflakeId ID of the snowflake algorithm

2.8.5 redisId Primary key of the rule generated based on Redis

According to the object attribute value, the orderly ID is generated. For example, the order type is purchase 😛 Sales :S, trade type: I Domestic trade; O foreign trade; The order number generation rule is as follows :1 bit order type +1 bit trade type +yyMMdd+3 bit flow (if more than 3 bits are extended automatically) the order number SI191120001 will be generated

2.9 Elastic Native query support

2.10 ElasticSearch – SQL Plug-in Mode SQL mode is supported

3. Integration description

• See SQLToy-Showcase and SQLtoy-starter-Showcase under Trunk

• Sqltoy-showcase is to demonstrate the integration of Springboot and SQLtoy based on XML configuration mode. Most of the functions are demonstrated in this project, where tools/ Quickvo directory is to use the database to generate POJO configuration examples (specific VO or other configuration can be modified according to actual situation).

• Sqltoy-starter showcase: Demonstrates boot-starter mode integration without XML configuration

  package com.sagframe.sqltoy;

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

  / * *

  • @author zhongxuchen

  */ @SpringBootApplication @ComponentScan(basePackages = { “com.sagframe.sqltoy” }) @EnableTransactionManagement public class SqlToyApplication {

  / * *

  • @param args

  */ public static void main(String[] args) { SpringApplication.run(SqlToyApplication.class, args); }}

Application. properties Sqltoy partial configuration

## sqltoy configures the path of the # SQL. Conforming to split (also optional in principle, If only object manipulation, but it is not recommended) spring. Sqltoy. SqlResourcesDir = / com/sagframe/sqltoy/showcase # translation cache configuration (optional configuration) Spring. Sqltoy. TranslateConfig = classpath: sqltoy - translate. Whether XML # debug mode, the debug mode will print execute SQL and parameter information (optional configuration) Spring. Sqltoy. Debug = true # used to set the default datasource (optional configuration, configuration is automatically injected) spring. Sqltoy. # defaultDataSource = datasource provides unified field: createBy CreateTime updateBy updateTime (null) assignment (optional) Spring. Sqltoy. UnifyFieldsHandler = com. Sagframe. Sqltoy. Plugins. # SqlToyUnifyFieldsHandler SQL implementation of how long it will take more than the log output (optional configuration: 30 seconds by default). Used to monitor what slow SQL spring. Sqltoy. PrintSqlTimeoutMillis = 30000Copy the code

Cache the translation configuration file sqltoy-translate.xml

<? The XML version = "1.0" encoding = "utf-8"? > <sagacity xmlns="http://www.sagframe.com/schema/sqltoy-translate" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.sagframe.com/schema/sqltoy-translate http://www.sagframe.com/schema/sqltoy/sqltoy-translate.xsd"> <! > < cache-store path="./sqltoy-showcase/translateCaches"> <! - based on SQL query directly for cache - > < SQL - translate cache = "dictKeyName datasource" = "datasource" > the < 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> <! --> < SQL -- translate cache="staffIdName" datasource=" datasource "> < SQL > <! [CDATA[ select STAFF_ID,STAFF_NAME,STATUS from SQLTOY_STAFF_INFO ]]> </sql> </sql-translate> </cache-translates> <! <cache-update-checkers> <! -- SQL based cache update detection, interval of seconds, can be set in segments, or directly set an array such as 60, < sqL-checker check-frequency="30" datasource=" datasource "> < SQL ><! [CDATA[ --#not_debug#-- select distinct 'staffIdName' cacheName,null cache_type from SQLTOY_STAFF_INFO t1 where T1. UPDATE_TIME >=:lastUpdateTime -- Data dictionary key and name cache detection Union all SELECT DISTINCT 'dictKeyName' cacheName, t2.dict_type cache_type from SQLTOY_DICT_DETAIL t2 where t2.UPDATE_TIME >=:lastUpdateTime ]]></sql> </sql-checker> </cache-update-checkers> </sagacity>Copy the code

• Actual business development use, direct use of SqlToyCRUDService can be conventional operations, avoid simple object operation write service, in addition for complex logic write service directly by calling SQLTOY provided: SqlToyLazyDao completes database interaction!

  @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("[email protected]"); staffInfo.setEntryDate(LocalDate.now()); staffInfo.setStatus(1); staffInfo.setOrganId("C0001"); staffInfo.setPhoto(ShowCaseUtils.getBytes(ShowCaseUtils.getFileInputStream("classpath:/mock/staff_photo.jpg"))); staffInfo.setCountry("86"); sqlToyCRUDService.save(staffInfo); }Copy the code

  }

Sqltoy SQL Key Description

4.1 SQLTOY SQL simplest rule #[] Symmetry symbol

• Is #[] equal to if? True: remove #[] as a block of code, false: remove #[and] as part of the execution.

• #[] supports nesting, such as #[t.status=:status #[and t.createdate >=:createDate]] executes if(NULL) logic from inside out first

• Filters conditional value preprocessing (EQ) and to-date (to-date) are only used in normal filters

  <! Format: yyyY-MM-DD or first_day: the first day of the month; Last_day: last_year_day: last_year_day: last_year_day -> <to-date params="" format="yyyyMMdd" increment-days="1" /> <! --> <to-number params="" data-type="decimal" /> <! <split data-type="string" params="staffAuthOrgs" split-sign=","/> <! Lte params="" value="" /> <! <lt params="" value="" /> <! <gte params="" value="" /> <! --> --> <gt params="" value="" /> <! Replace params="" regex="" value="" IS-first ="false" /> <! -- Primary excludes, when a parameter is not null --> <primary param="orderId" Excludes ="organIds" /> <! <exclusive param="" compare-type="eq" compare-values="" set-params="" set-value="" / > <! <cache-arg cache-name="" cache-type="" param="" cache-mapping-indexes="" alias-name=""/> <! < in-arg params=""/> </filters> <! -- Cache translation, can be multiple, ${value} =[${value} specifies the value of the key used in our work, and it can be inferred from the data in our interviews. Cache-type ="POST_TYPE" columns="POST_TYPE" cache-indexs="1" uncached-template=" /> <! -- Simplest usage: <secure-mask columns="" type="tel"/> --> <secure-mask columns="" type="name" head-size="3" tail-size="4" Mask - code = "* * * * *" mask - rate = "50" / > <! -- depots strategy - > < sharding - a datasource strategy = "" / > <! - table strategy - > < sharding - table tables = "strategy" = "" params =" "/ > <! -- Paging optimization, caching the total number of paging records for the same query criteria, Alive-max: alive-seconds: > <page-optimize alive-max="100" The alive - seconds = "600" / > <! -- date format - > < date - format columns = "" format =" MM - dd yyyy - HH: MM: ss "/ > <! - digital format - > < 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.J OIN_DATE > : beginDate] # [and t.S TAFF_NAME like: staffName] -- whether virtual employee @ the if () doing logic # [@ the if (: isVirtual = = true | | : isVirtual = = 0) and t.IS_VIRTUAL=1] ) t1,sys_organ_info t2 where t1.ORGAN_ID=t2.ORGAN_ID ]]> </value> <! - provide acme paging custom writing SQL - > < count - SQL > <! [CDATA []] > < / count - SQL > <! -- Summarize and average, implement complex SQL through algorithms, <summary columns="" radix-size="2" reverse="false" sum-site="left"> <global sum-label="" label-column=""  /> <group sum-label="" label-column="" group-column="" /> </summary> <! --> <link sign="," column="" /> <! -- Row turn column (mutually exclusive with unpivot), --> <pivot category-columns="" group-columns="" start-column="" end-column="" default-value="0" /> <! Column - turned - > < unpivot columns = "values" - as - column = "" / >Copy the code

5. Sqltoy key code description

• Sqltoy-orm consists of the following parts: BaseDaoSupport: A basic Dao that provides developers with Dao inheritance and integrates all operations on the database. SqlToyLazyDao: A Dao for quick use by developers. It is equivalent to a Dao written by developers. It is used in simple scenarios where developers can write services without writing Dao. SqltoyCRUDService: simple Service encapsulation, some simple object add, delete and change developers write Service is also a simple call Dao, for this scenario to provide a simple function of the Service call, the developer’s own Service used to encapsulate relatively complex business logic. DialectFactory: database DialectFactory class, sqltoy calls implementation encapsulation of different databases based on the currently connected dialect. SqlToyContext: SqlToy context configuration, is the core configuration and switch area of the entire framework, Spring configuration is mainly to configure SqlToyContext. EntityManager: encapsulated in SqlToyContext, used to host POJO objects and establish relationships between objects and database tables. Sqltoy scans loaded objects through the SqlToyEntity annotation. ScriptLoader: SQL configuration file load parser wrapped in SqlToyContext. SQL files are named strictly according to the *.sql.xml rule. TranslateManager: cache translation manager, used to load cache translation XML configuration files and cache implementation classes, SQLToy provides the interface and provides a default ehCache-based implementation, Cache translation is best to use ehCache local cache (or EhCache RMI mode distributed cache), which is the most efficient, while redis distributed cache IO overhead is too high, cache translation is a high frequency call, Generally, stable data such as employees, organizations, data dictionaries, product categories, and regions that do not change frequently are cached. ShardingStragety: ShardingStragety policy manager. After 4.x, policy manager does not need to be explicitly defined.
• Sqltoy: From the entry point of BaseDaoSupport(or SqlToyDaoSupport), you can see all of the functionality provided by SQLToy, and from LinkDaoSupport, you can see how sqlToy functions are organized in different categories. From DialectFactory you enter the implementation entry for the different database dialects. You can trace and see the implementation logic of a specific database. You’ll see oracle, mysql, etc., pagination, fetching random records, fast pagination encapsulation, etc. EntityManager: You will find out how to scan poJOs and construct models, knowing that the database will actually interact with the corresponding SQL by manipulating the POJOs. ParallelUtils is a class that allows you to see how collections can be grouped into different tables in different libraries and scheduled in parallel for batch operations. SqlToyContext: SqlTOY configuration context. This class allows you to see the full SQLTOY view. PageOptimizeCacheImpl: You can see how page optimization works by default.