<dependency>
     <groupId> com.sagframe </groupId>
     <artifactId> sagacity-sqltoy-spring-starter </artifactId>
     <!--  The solon adaptation version<artifactId>sagacity sqltoy solon plugin</artifactId>-->
     <!--  The version number of jdk8 is 5.6.10. jre8 -->
     <version> 5.6.10 </version>
 </dependency>

    //Three steps: 1. Quickvo generates pojo; 2. Complete yml configuration; 3. Inject dao into service (no need to customize various dao)
    @Autowired
    LightDao  lightDao ;
    StaffInfoVO  staffInfo  =  new  StaffInfoVO (); 
    //Save
    lightDao . save ( staffInfo );
    //Delete
    lightDao . delete ( new  StaffInfoVO ( "S2007" ));

    //public Long update(Serializable entity,  String... forceUpdateProps);
    //Here, the photo attribute is forced to be modified. If it is null, it will be skipped automatically
    lightDao . update ( staffInfo ,  "photo" );

    //Deep modification, regardless of whether all fields are null
    lightDao . 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 modify
    lightDao . saveOrUpdateAll ( staffList );
    //Batch save
    lightDao . saveAll ( staffList );
    ...............
    lightDao . loadByIds ( StaffInfoVO . class , "S2007" )
    //Uniqueness verification
    lightDao . isUnique ( staffInfo ,  "staffCode" );

 /**
 *@ todo simplifies paramName [], paramValue [] mode parameter transfer through object
 * @param <T>
 *@ param sqlOrNamedSql can be specific sql or sqlId in the corresponding xml
 *@ param entity passes parameters through objects and returns results by object type
 */
  public  < T  extends  Serializable >  List < T >  find ( final  String  sqlOrNamedSql ,  final  T  entity );

 public  Page < StaffInfoVO >  findStaff ( Page < StaffInfoVO >  pageModel ,  StaffInfoVO  staffInfoVO )  {
      //SQL can be written directly in code. It is recommended to define complex SQL in xml
      //In the single table entity query scenario, sql fields can be written as attribute names of java classes
      return  findPageEntity ( pageModel ,  StaffInfoVO . class ,  EntityQuery . create ()
	 . where ( "#[staffName like :staffName]#[and createTime>=:beginDate]#[and createTime<=:endDate]" )
	 . values ( staffInfoVO ));
 }

 //The indirect sql mode in the demo code sets the conditional mode for record modification
 public  Long  updateByQuery ()  {
      return  lightDao . updateByQuery ( StaffInfoVO . class ,
		 EntityUpdate . create (). set ( "createBy" ,  "S0001" )
                      . where ( "staffName like ?" ). values ( Zhang ));
 }

 //Set the conditional mode to delete records in the indirect sql mode in the code
 lightDao . deleteByQuery ( StaffInfoVO . class ,  EntityQuery . create (). where ( "status=?" ). values ( zero ));

 //1. Condition value processing is separated from specific sql
 //2. Precede the condition value and process it regularly through the general method defined by filters (most of them do not need additional processing)
 <sql  id= "show_case" >
 <filters>
    <!--  As long as the parameter statusAry contains - 1 (representing all), set statusAry to null and not participate in condition retrieval -->
    <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]
 --If sqltoy's in exceeds 1000, it will be automatically cut into (t.field in (1~1000) or t.field in (1001~2000))
 #[and t.ORGAN_ID in (:authedOrganIds)]
 #[and t.STAFF_ID in (:staffIds)]
 #[and t.TRANS_DATE> =:beginAndEndDate[0]]
 #[and t.TRANS_DATE <:beginAndEndDate [1]]  
            #[and  (t.TECH_GROUP,t.PROD_GROUP)  in  (:techGroups,:prodGroups)]
            #[order  by  @value(:orderField)  @value(:orderWay)]  
	 ]] ></value>
 </sql>

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

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

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

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

 <!--  Quick Paging and Paging Optimization Demo -->
 <sql  id= "sqltoy_fastPage" >
	 <!--  The paging optimizer caches the total number of records in a certain period of time when the query conditions are consistent through caching, thus eliminating the need to query the total number of records every time -->
	 <!--  Parallel: Whether to query the total records and single page data in parallel, and turn off cache optimization when live max=1 -->
	 <!--  Alive max: the maximum number of total records stored for different query conditions; Alive seconds: the survival time of the query condition record quantity (for example, 120 seconds, if the threshold value is exceeded, the query will be repeated) -->
	 <page-optimize  parallel= "true"  alive-max= "100"  alive-seconds= "120"  />
	 <value> <! [CDATA[
 select t1.*,t2.ORGAN_NAME
 --@ fast() implements first to fetch 10 pieces by page (the specific number is determined by pageSize), and then to associate
 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>
	 <!--  Here, a custom count sql is provided for extremely special cases to achieve extreme performance optimization -->
	 <!-- < count-sql></count-sql> -->
 </sql>

 /**
 *Object based parameter transfer mode
 */
 public  void  findPageByEntity ()  {
	 StaffInfoVO  staffVO  =  new  StaffInfoVO ();
	 //Transfer parameters as query criteria
	 staffVO . setStaffName ( Chen );
	 //Paging optimizer used
	 //The first call: execute count and record fetching queries twice
         //The second call: within a specific time effective range, count will be obtained from the cache, and only the query of fetching single page records will be executed
	 Page  result  =  lightDao . findPage ( new  Page (),  "sqltoy_fastPage" ,  staffVO );
 }

 //Support object attribute annotation mode for cache translation
 @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 device type
 Cache: the name of the specific cache definition,
 Cache type: generally provides a classification condition filter for the data dictionary
 Columns: the name of the query field in sql. Multiple fields can be translated separated by commas
 Cache indexes: the column corresponding to the cache data name. If it is not filled in, it defaults to the second column (starting from 0, 1 means 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. If the same cache is used, several fields can be translated at the same time -->
	 <translate  cache= "staffIdName"  columns= "staffName,createName"  />
	 <filters>
		 <!--  Reverse the use of cache to match the ID by name for accurate query -->
		 <cache-arg  cache-name= "staffIdNameCache"  param= "staffName"  alias-name= "staffIds" />
	 </filters>
	 <value>
	 <! [CDATA[
 select 	ORDER_ID,
 DEVICE_TYPE,
 DEVICE_TYPE deviceTypeName, -- device classification 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>

 //ParalQuery is query oriented (not used in the process of transaction operation). sqltoy provides powerful methods, but whether it is properly used requires the user to make a reasonable judgment
 /**
 *@ TODO queries in parallel and returns a one-dimensional list. Several query lists contain several result objects. ParamNames and ParamValues are a collection of all sql conditional parameters
 * @param parallQueryList
 * @param paramNames
 * @param paramValues
 */
 public  < T >  List < QueryResult < T >>  parallQuery ( List < ParallQuery >  parallQueryList ,  String []  paramNames ,
			 Object []  paramValues );

 //Define parameters
 String []  paramNames  =  new  String []  {  "userId" ,  "defaultRoles" ,  "deployId" ,  "authObjType"  };
 Object []  paramValues  =  new  Object []  {  userId ,  defaultRoles ,  GlobalConstants . DEPLOY_ID ,
		 SagacityConstants . TempAuthObjType . GROUP  };
 //Use parallel queries to execute two SQL statements at the same time. The condition parameter is a collection of two 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 );
		

 #Enable the default function adaptation conversion function of sqltoy
 spring.sqltoy.functionConverts = default
 #For example, in the MySQL scenario, test other types of databases at the same time to verify that SQL is suitable for different databases, mainly for production software
 spring.sqltoy.redoDataSources[0]=pgdb
 #You can also customize functions to replace Nvl
 # spring.sqltoy.functionConverts=default,com.yourpackage.Nvl
 #Enable Nvl and Instr of the framework
 # spring.sqltoy.functionConverts=Nvl,Instr
 #Enable custom Nvl, Instr
 # spring.sqltoy.functionConverts=com.yourpackage. Nvl,com.yourpackage.Instr

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

 <!--  Row to column -->
 <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, with order_month as the horizontal title of classification, and three indicators from sale_count column to total_amt rotated into rows -->
	 <pivot  start-column= "sale_count"  end-column= "total_amt"	 group-columns= "fruit_name"  category-columns= "order_month"  />
 </sql>

 <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" >
		 <!--  The hierarchy order is kept from high to low -->
		 <global  sum-label= Total  label-column= "fruit_name"  />
                 <!--  Order column: group sort column (sort the same group), order with sum: default is true, order-way:desc/asc -->
		 <group  group-column= "fruit_name"  sum-label= Subtotal  label-column= "fruit_name"  />
	 </summary>
 </sql>

 <!--  Column to column ratio demonstration -->
 <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 rotation, row to column, and order_month are displayed in columns. There are three indicators below each month -->
	 <pivot  start-column= "sale_count"  end-column= "total_amt"	 group-columns= "fruit_name"  category-columns= "order_month"  />
	 <!--  Circular ratio calculation between columns -->
	 <cols-chain-relative  group-size= "3"  relative-indexs= "1,2"  start-column= "1"  format= "#.00%"  />
 </sql>

 <!--  Tree sorting, summary -->
 <sql  id= "treeTable_sort_sum" >
	 <value>
	 <! [CDATA[
 select t.area_code,t.pid_area,sale_cnt from sqltoy_area_sales t
 ]]>
	 </value>
	 <!--  Organize the upper and lower subordinate structure of the tree, and summarize the value of the bottom node to the parent node level by level, and arrange the same level in descending order -->
	 <tree-sort  id-column= "area_code"  pid-column= "pid_area"	 sum-columns= "sale_cnt"  level-order-column= "sale_cnt"  order-way= "desc" />
 </sql>

 For sql, see the quickstart project: com/sqltoy/quickstart/sqltoy-quickstart.sql.xml file
    <!--  Demo sub database -->
	 <sql  id= "qstart_db_sharding_case" >
		 <sharding-datasource  strategy= "hashDataSource"
			 params= "userId"  />
		 <value>
			 <! [CDATA[
 select * from sqltoy_user_log t
 --UserId is a prerequisite as a sub database key field
 where t.user_id=:userId
 #[and t.log_date> =:beginDate]
 #[and t.log_date < =:endDate]
 ]]>
		 </value>
	 </sql>

	 <!--  Demo sub table -->
	 <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>
        

 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 sub database policy configuration, and table is the sub table policy configuration, which can be configured simultaneously or independently
 *The policy name should be consistent with the bean definition name in spring. Fields means which field values of the object should be used as the basis for judgment. One or more fields can be used
 *MaxConcurrents: optional configuration, representing the maximum number of parallels maxWaitSeconds: optional configuration, representing the maximum number of seconds to wait
 */
 @Sharding ( db  =  @Strategy ( name  =  "hashBalanceDBSharding" ,  fields  =  {  "userId"  }),
		 // table = @Strategy(name = "hashBalanceSharding", fields = {"userId" }),
		 maxConcurrents  =  ten ,  maxWaitSeconds  =  one thousand and eight hundred )
 @SqlToyEntity
 public  class  UserLogVO  extends  AbstractUserLogVO  {
	
	 private  static  final  long  serialVersionUID  =  1296922598783858512L ;

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

 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
 * @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 );
	 }
 }

 # 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
 #Provide unified public field assignment (see quickstart for source code)
 spring.sqltoy.unifyFieldsHandler = com.sqltoy.plugins.SqlToyUnifyFieldsHandler
 #spring.sqltoy.printSqlTimeoutMillis=200000

 <? 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 " >
	 <!--  The cache has a default expiration time of 1 hour, so only frequent caches need to be detected in time -->
	 <cache-translates>
		 <!--  Get cache based on sql direct query -->
		 <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 ID and name -->
		 <sql-translate  cache= "staffIdName"
			 datasource= "dataSource" >
			 <sql>
			 <! [CDATA[
 select STAFF_ID,STAFF_NAME,STATUS
 from SQLTOY_STAFF_INFO
 ]]>
			 </sql>
		 </sql-translate>
		 <!--  Cache of organization number and name -->
		 <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 detection can provide multiple SQL, service, and rest based service detection -->
	 <cache-update-checkers>
		 <!--  SQL based cache update detection -->
		 <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. When changes are detected, the cache is directly updated -->
		 <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 classification is classification -->
		 <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>

 @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 ( one );
		 staffInfo . setOrganId ( "C0001" );
		 staffInfo . setPhoto ( FileUtil . readAsBytes ( "classpath:/mock/staff_photo.jpg" ));
		 staffInfo . setCountry ( "86" );
		 sqlToyCRUDService . save ( staffInfo );
	 }
  }