sqltoy-ormORM 框架

       <!-- 跨數(shù)據(jù)庫函數(shù)自動替換(非必須項),適用于跨數(shù)據(jù)庫軟件產(chǎn)品,如mysql開發(fā)，oracle部署 -->
   <property name="functionConverts" value="default">
   <!-- 也可以這樣自行根據(jù)需要進行定義和擴展
   <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> -->
</bean>

        <!-- 列轉(zhuǎn)行測試 -->
	<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>
		<!-- 將指定的列變成行(這里3列變成了3行) -->
		<unpivot columns="TOTAL_AMOUNT:總金額,PERSON_AMOUNT:個人金額,COMPANY_AMOUNT:企業(yè)金額"
			values-as-column="TRANS_AMOUNT" labels-as-column="AMOUNT_TYPE" />
	</sql>

	<!-- 行轉(zhuǎn)列測試 -->
	<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>

	<!-- 匯總計算 (場景是sql先匯總，頁面上還需要對已有匯總再匯總的情況,如果用sql實現(xiàn)在跨數(shù)據(jù)庫的時候就存在問題)-->
	<sql id="sys_summarySearch">
		<!-- 數(shù)據(jù)源sharding，多庫將請求壓力分攤到多個數(shù)據(jù)庫節(jié)點上，支撐更多并發(fā)請求 -->	
		<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 表示將匯總信息在上面顯示(如第1行是匯總值，第2、3、4行為明細，反之，1、2、3行未明細，第4行為匯總)  -->
		<summary columns="2" reverse="true" sum-site="left" radix-size="2">
			<global sum-label="總計" label-column="0" />
                        <!-- 可以無限層級的分組下去-->
			<group sum-label="小計/平均" label-column="0" group-column="0" average-label="平均" />
		</summary>
	</sql>

        sql參見showcase項目:com/sagframe/sqltoy/showcase/sqltoy-showcase.sql.xml 文件
        sharding策略配置參見:src/main/resources/spring/spring-sqltoy-sharding.xml 配置
        <!-- 演示分庫 -->
	<sql id="sqltoy_db_sharding_case">
		<sharding-datasource
			strategy="hashBalanceDBSharding" params="userId" />
		<value>
			<![CDATA[
			select * from sqltoy_user_log t 
			-- userId 作為分庫關(guān)鍵字段屬于必備條件
			where t.user_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>
        

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:用戶日志表
 */
/*
 * db則是分庫策略配置,table 則是分表策略配置，可以同時配置也可以獨立配置
 * 策略name要跟spring中的bean定義name一致,fields表示要以對象的哪幾個字段值作為判斷依據(jù),可以一個或多個字段
 * maxConcurrents:可選配置，表示最大并行數(shù) maxWaitSeconds:可選配置，表示最大等待秒數(shù)
 */
@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();
	}
}