anyline 數(shù)據(jù)庫(kù)操作

AnylineService(配合AnylineDao)提供了常用的數(shù)據(jù)庫(kù)操作接口，其中insert,update,delete,execute比較簡(jiǎn)單也容易理解而select操作相對(duì)靈活的多，靈活性主要體現(xiàn)在其參數(shù)ConfigStore的構(gòu)造方式上，通過(guò)ConfigStore可以實(shí)現(xiàn)非常復(fù)雜的查詢操作

public DataSet selects(String src, ConfigStore configs, String ... conditions);

在實(shí)際開(kāi)發(fā)過(guò)程中，通常是用BaseController繼承tAnylineControllerAnylineController中已經(jīng)注入AnylineService serive，并重載了大量config函數(shù)用來(lái)自動(dòng)構(gòu)造ConfigStore

更詳細(xì)的操作:AnylineService 與config()

AnylineService

AnylineService用來(lái)完成大部分的數(shù)據(jù)庫(kù)操作為保持版本兼容query與select功能完全一致

/**
     * 按條件查詢
     * @param src           數(shù)據(jù)源(表或自定義SQL或SELECT語(yǔ)句) src          數(shù)據(jù)源(表或自定義SQL或SELECT語(yǔ)句)
     * @param configs       封裝來(lái)自于http的查詢條件 configs      封裝來(lái)自于http的查詢條件
     * @param conditions    固定查詢條件   conditions 固定查詢條件
     *          原生SQL(AND GROUP ORDER)
     *          {原生}
     *          [+]CD:1
     *          [+]CD:
     *          [+]CD:null
     *          [+]CD:NULL
     *          
     * @return return
     */
    public DataSet querys(String src, ConfigStore configs, String ... conditions);
    public DataSet querys(String src, String ... conditions);
    public DataSet querys(String src, int fr, int to, String ... conditions);
    public DataRow query(String src, ConfigStore configs, String ... conditions);
    public DataRow query(String src, String ... conditions);
 
    //實(shí)現(xiàn)與query相同的功能
    public DataSet selects(String src, ConfigStore configs, String ... conditions);
    public DataSet selects(String src, String ... conditions);
    public DataSet selects(String src, int fr, int to, String ... conditions);
    public DataRow select(String src, ConfigStore configs, String ... conditions);
    public DataRow select(String src, String ... conditions);
    /**
     * 如果二級(jí)緩存開(kāi)啟 會(huì)從二級(jí)緩存中提取數(shù)據(jù)
     * @param cache 對(duì)應(yīng)ehcache緩存配置文件 中的cache.name
     * @param src src
     * @param configs configs
     * @param conditions conditions
     * @return return
     */
    public DataSet caches(String cache, String src, ConfigStore configs, String ... conditions);
    public DataSet caches(String cache, String src, String ... conditions);
    public DataSet caches(String cache, String src, int fr, int to, String ... conditions);
    /**
     * 只用一級(jí)緩存 忽略二級(jí)緩存
     * @param cache cache
     * @param src src
     * @param configs configs
     * @param conditions conditions
     * @return return
     */
    public DataSet cacheL1(String cache, String src, ConfigStore configs, String ... conditions);
    public DataSet cacheL1(String cache, String src, String ... conditions);
    public DataSet cacheL1(String cache, String src, int fr, int to, String ... conditions);
 
      
    public DataRow next(DataRow row, String column, SQL.ORDER_TYPE order, ConfigStore configs, String ... conditions);
    public DataRow next(DataRow row, String column, SQL.ORDER_TYPE order, String ... conditions);
    public DataRow next(DataRow row, SQL.ORDER_TYPE order, String ... conditions);
    public DataRow next(DataRow row, ConfigStore configs, String ... conditions);
    public DataRow next(DataRow row, String ... conditions);
     
    public DataRow prev(DataRow row, String column, SQL.ORDER_TYPE order, ConfigStore configs, String ... conditions);
    public DataRow prev(DataRow row, String column, SQL.ORDER_TYPE order, String ... conditions);
    public DataRow prev(DataRow row, SQL.ORDER_TYPE order, String ... conditions);
    public DataRow prev(DataRow row, ConfigStore configs, String ... conditions);
    public DataRow prev(DataRow row, String ... conditions);
 
    public DataRow cache(String cache, String src, ConfigStore configs, String ... conditions);
    public DataRow cache(String cache, String src, String ... conditions);
     
 
    /**
     * 刪除緩存 參數(shù)保持與查詢參數(shù)完全一致
     * @param channel channel
     * @param src src
     * @param configs configs
     * @param conditions conditions
     * @return return
     */
    public boolean removeCache(String channel, String src, ConfigStore configs, String ... conditions);
    public boolean removeCache(String channel, String src, String ... conditions);
    public boolean removeCache(String channel, String src, int fr, int to, String ... conditions);
    /**
     * 清空緩存
     * @param channel channel
     * @return return
     */
    public boolean clearCache(String channel);
      
    /** 
     * 是否存在 
     * @param src  src
     * @param configs  configs
     * @param conditions  conditions
     * @return return
     */
    public boolean exists(String src, ConfigStore configs, String ... conditions); 
    public boolean exists(String src, String ... conditions); 
    public boolean exists(String src, DataRow row);
    public boolean exists(DataRow row);
     
    public int count(String src, ConfigStore configs, String ... conditions);
    public int count(String src, String ... conditions);
     
      
      
    /** 
     * 更新記錄 
     * @param columns     需要更新的列 
     * @param dest     表 
     * @param data data
     * @return return
     */
    public int update(String dest, Object data, String ... columns);
    public int update(Object data, String ... columns);
    public int update(String dest, ConfigStore configs, String ... conditions);
     
    public int update(boolean sync, String dest, Object data, String ... columns);
    public int update(boolean sync, Object data, String ... columns);
    /** 
     * 保存(insert|update) 
     * @param data  data
     * @param checkPriamry  checkPriamry
     * @param columns  columns
     * @param dest 表 
     * @return return
     */
    public int save(String dest, Object data, boolean checkPriamry, String ... columns); 
    public int save(Object data, boolean checkPriamry, String ... columns); 
    public int save(Object data, String ... columns); 
    public int save(String dest, Object data, String ... columns); 
//
    public int save(boolean sync, String dest, Object data, boolean checkPriamry, String ... columns);
    public int save(boolean sync, Object data, boolean checkPriamry, String ... columns);
    public int save(boolean sync, Object data, String ... columns);
    public int save(boolean sync, String dest, Object data, String ... columns); 
  
  
    public int insert(String dest, Object data, boolean checkPriamry, String ... columns); 
    public int insert(Object data, boolean checkPriamry, String ... columns); 
    public int insert(Object data, String ... columns); 
    public int insert(String dest, Object data, String ... columns); 
 
 
    /**
     * 異步插入
     * @param dest dest
     * @param data data
     * @param checkPriamry checkPriamry
     * @param columns columns
     * @return return
     */
    public int batchInsert(String dest, Object data, boolean checkPriamry, String ... columns);
    public int batchInsert(Object data, boolean checkPriamry, String ... columns);
    public int batchInsert(Object data, String ... columns);
    public int batchInsert(String dest, Object data, String ... columns); 
    /** 
     * save insert區(qū)別 
     * 操作單個(gè)對(duì)象時(shí)沒(méi)有區(qū)別 
     * 在操作集合時(shí)區(qū)別: 
     * save會(huì)循環(huán)操作數(shù)據(jù)庫(kù)每次都會(huì)判斷insert|update 
     * save 集合中的數(shù)據(jù)可以是不同的表不同的結(jié)構(gòu)  
     * insert 集合中的數(shù)據(jù)必須保存到相同的表,結(jié)構(gòu)必須相同 
     * insert 將一次性插入多條數(shù)據(jù)整個(gè)過(guò)程有可能只操作一次數(shù)據(jù)庫(kù)  并 不考慮update情況 對(duì)于大批量數(shù)據(jù)來(lái)說(shuō) 性能是主要優(yōu)勢(shì) 
     *  
     */
      
    /** 
     * 執(zhí)行 
     * @param src  src
     * @param configs  configs
     * @param conditions  conditions
     * @return return
     */
    public int execute(String src, ConfigStore configs, String ... conditions); 
    public int execute(String src, String ... conditions); 
    /** 
     * 執(zhí)行存儲(chǔ)過(guò)程 
     * @param procedure  procedure
     * @param inputs  inputs
     * @return return
     */
    public boolean executeProcedure(String procedure, String... inputs); 
    public boolean executeProcedure(Procedure procedure); 
    /** 
     * 根據(jù)存儲(chǔ)過(guò)程查詢 
     * @param procedure  procedure
     * @param inputs  inputs
     * @return return
     */
    public DataSet queryProcedure(String procedure, String ... inputs); 
    public DataSet query(Procedure procedure); 
     
    public DataSet selectProcedure(String procedure, String ... inputs); 
    public DataSet select(Procedure procedure); 
      
    /**
     * 刪除 根據(jù)主鍵刪除
     * @param dest dest
     * @param data data
     * @return return
     */
    public int delete(String dest, Object data); 
    public int delete(Object data);
     
    public int delete(String table, String key, Collection<Object> values);
    public int delete(String table, String key, String ... values);

關(guān)于更新部分屬性(列)、忽略部分屬性(列)

添加到DataRow中但不需要參與更新(插入)
row.put("-NAME", "ZH");

添加了空值,  默認(rèn)情況下不參與更新(插入) 如果需要強(qiáng)制參與更新(插入)
row.put("+NAME",null);

如果這樣指定了更新(插入)列，則只會(huì)更新(插入)指定的列，其他列都不會(huì)參與更新(插入)
service.save(row,"NAME");

強(qiáng)制更新(插入)NAME,忽略CODE，其他列不受影響，按默認(rèn)情況處理
service.save(row,"+NAME","-CODE");

插入所有列，更新所有值發(fā)生過(guò)變化的列
service.save(row);

關(guān)于自動(dòng)檢測(cè)表結(jié)構(gòu)

在執(zhí)行數(shù)據(jù)庫(kù)操作時(shí)，許多參數(shù)是以String形式獲取到的,無(wú)法參數(shù)識(shí)別數(shù)據(jù)類型，如url中的參數(shù)

還有一些數(shù)據(jù)類型在Java中沒(méi)有對(duì)應(yīng)關(guān)系,如xml/josn/幾何圖形等

而有些數(shù)據(jù)庫(kù)在執(zhí)行SQL時(shí)會(huì)執(zhí)行強(qiáng)類型檢測(cè)。

不像我們平時(shí)用的MySQL在執(zhí)行時(shí)會(huì)進(jìn)行隱式轉(zhuǎn)換，無(wú)論什么類型只要能轉(zhuǎn)換成功就可以執(zhí)行。

而PostgreSQL則要求jdbc參數(shù)與表結(jié)構(gòu)對(duì)應(yīng)，如果在varchar列中執(zhí)行int類型會(huì)失敗。

要求開(kāi)發(fā)人員在編碼過(guò)程中記住表數(shù)據(jù)類型，或進(jìn)行類型轉(zhuǎn)換顯示不合理，何況表結(jié)構(gòu)有可能會(huì)變動(dòng)。

可以開(kāi)啟表結(jié)構(gòu)自動(dòng)檢測(cè)，在執(zhí)行SQL前把參數(shù)轉(zhuǎn)換成與表結(jié)構(gòu)對(duì)應(yīng)的類型

這樣就可以像MySQL一樣隨意了

ConfigTable.IS_AUTO_CHECK_METADATA = true;可以參考anyline-simple-jdbc-postgresql

前端未提交數(shù)據(jù)情況下清空一列值

默認(rèn)情況下前端不傳值時(shí)，執(zhí)行update時(shí)并不更新這一列
如
DataRow row = entity("ID:id","REMARK:remark","NAME:name");
service.save(row);
這時(shí)如果沒(méi)有提交remark參數(shù)值，或remark值為空時(shí)，并不更新REMARK列
但有些情況下是前端需要清空一個(gè)屬性值，有以下種方式
1.提交一個(gè)"NULL"的值
2.指定必須更新的列
DataRow row = entity("ID:id","+REMARK:remark");
3.顯示指定需要更新的列
service.update("HR_USER", row, "REMARK","NAME");

查詢時(shí)設(shè)置主鍵

如果沒(méi)有修改配置文件，默認(rèn)以ID作為每個(gè)表的主鍵，也可以通過(guò)配置文件中的DEFAULT_PRIMARY_KEY來(lái)修改默認(rèn)主鍵
但總有一些數(shù)據(jù)庫(kù)設(shè)計(jì)表時(shí)沒(méi)有主建
而有一些查詢過(guò)程需要用到主鍵，比如MSSQL分頁(yè)查詢時(shí)會(huì)默認(rèn)根據(jù)主鍵來(lái)排序 ROW_NUMBER OVER(ORDER BY ID)
這時(shí)如果需要根據(jù)其他列來(lái)排序可以在查詢時(shí)指定

service.querys("HR_USER<AGE>", condition(true));

這樣在分頁(yè)時(shí)就以AGE作為排序依據(jù)

save與insert區(qū)別

save將根據(jù)主鍵是否有值來(lái)決定執(zhí)行update還是insert

對(duì)于DataRow參數(shù)來(lái)說(shuō),save調(diào)用的insert與直接調(diào)用insert沒(méi)有太大區(qū)別, 只是save多了一次判斷

區(qū)別主要在于DataSet參數(shù)：save會(huì)循環(huán)操作數(shù)據(jù)庫(kù)每次都會(huì)判斷insert|update save 集合中的數(shù)據(jù)可以是不同的表不同的結(jié)構(gòu)  insert 集合中的數(shù)據(jù)必須保存到相同的表,結(jié)構(gòu)必須相同 insert 將一次性插入多條數(shù)據(jù)整個(gè)過(guò)程有可能只操作一次數(shù)據(jù)庫(kù)  并 不考慮update情況 對(duì)于大批量數(shù)據(jù)來(lái)說(shuō) 性能是主要優(yōu)勢(shì) 

為什么有時(shí)update sql不執(zhí)行

如果DataRow的值沒(méi)有變化，則執(zhí)行service.update時(shí)不會(huì)執(zhí)行更新SQL

需要清空內(nèi)容的列沒(méi)有更新

默認(rèn)情況下內(nèi)容為空的列(“”或null) 不會(huì)出現(xiàn)在UPDATE SQL中，

可以通過(guò)以下方式強(qiáng)制更新指定列
1.指定更新列
service.update(row,"ID","NM");

2.設(shè)置成約定的值NULL(大寫(xiě))
row.put("NM","NULL");//可以從前臺(tái)頁(yè)面輸入

3.構(gòu)造DataRow時(shí)指定
row = entityRow("+NM:nm","+CODE:code");

4.指定更新全部列
row.addAllUpdateColumns()

5.統(tǒng)一修改配置文件

<!-- 是否更新NULL列 -->
<property key="IS_UPDATE_NULL_COLUMN">true</property>
<!-- 是否更新空列 -->
<property key="IS_UPDATE_EMPTY_COLUMN">true</property>

其中4、5需要注意如果DataRow中有表中沒(méi)有的列(如DataRow是從視圖中查出來(lái)的)，會(huì)導(dǎo)致SQL異常
這就需要人工將表中沒(méi)有的列remove后再執(zhí)行更新

condition()

AnylineController中提供了condition()用來(lái)接收客戶端傳值，并賦值給SQL,協(xié)助其構(gòu)造查詢條件，condition函數(shù)返回org.anyline.jdbc.config.ConfigStore

condition()主要用來(lái):

1.通過(guò)HttpRequest構(gòu)造ConfigStore用來(lái)實(shí)現(xiàn)復(fù)雜的數(shù)據(jù)庫(kù)查詢條件

2.自動(dòng)封裝分頁(yè)參數(shù)分頁(yè)參數(shù)

比較簡(jiǎn)單config(true)表示需要分頁(yè) config(10)表示分頁(yè)并顯式指定一頁(yè)10行

由于歷史版本的兼容condition,parseConfig,config三者實(shí)現(xiàn)的是完全一致的功能。

condition參數(shù)格式參考:約定格式

IN條件下多種參數(shù)格式的接收

對(duì)于標(biāo)準(zhǔn)的url格式 /list?id=1&id=2

以及標(biāo)準(zhǔn)的json格式 {id:[1,2]}

可以通過(guò)condition("ID:[id]")的形式接收

對(duì)于非標(biāo)準(zhǔn)格式如 /list?id=1,2

可以通過(guò)condition("ID:[split(id)]")的形式接收

最終都是生成SQL  WHERE ID IN(1,2)

關(guān)于幾種OR條件查詢的情況

//以下三種格式,只有cd取值成功時(shí)，條件才生效
 
//當(dāng)cd=1,id=2時(shí) WHERE CODE = 1 OR CODE =2
//當(dāng)cd=null,id=2時(shí) 條件不生效
//當(dāng)cd=1,id=null時(shí) WHERE CODE = 1
service.querys("HR_USER", condition("CODE:cd|id"));
 
//當(dāng)cd=1時(shí) WHERE CODE =1 OR CODE = 9
//當(dāng)cd=null時(shí) 條件不生效
service.querys("HR_USER", condition("CODE:cd|{9}"));
 
 
//當(dāng)cd=1時(shí) WHERE CODE =1 OR CODE IS NULL
//當(dāng)cd=null時(shí) 條件不生效
service.querys("HR_USER", condition("CODE:cd|{NULL}"));

//當(dāng)type=1,dept=null時(shí) WHERE TYPE_CODE = 1
//當(dāng)type=1,dept=2時(shí) WHERE TYPE_CODE =1 OR DEPT_ID =2 
//當(dāng)type=null,dept=2時(shí) WHERE DEPT_ID = 2
service.querys("HR_USER", condition("TYPE_CODE:type|DEPT_ID:dept"));

//依次取c1,c2的值，如果c1取值成功則忽略c2,如果都失敗則取默認(rèn)值9
service.querys("HR_USER", condition("CODE:c1:c2:{9}"));

關(guān)于or

OR條件構(gòu)造相對(duì)復(fù)雜

condition("NM:nm|NM:name"); 生成SQL WHERE NM ='z' OR NM = 'zh'

condition("NM:nm|name|user");

中有第一個(gè)nm取值成功當(dāng)前條件才生效，如果nm取值失敗后面的name,user也忽略，當(dāng)前條件無(wú)效

經(jīng)常會(huì)這樣用:

condition("NM:nm|{1}"); 生成SQL  WHERE NM = 'zh' OR NM = 1

condition("NM:nm|{NULL}"); 生成SQL  WHERE NM = 'zh' OR NM IS NULL

如果是在java中生成OR條件

condition("NM:nm","AGE:20").or("SORT","1") 生成SQL WHERE NM = 'zh' AND AGE=20 OR SORT = 1

condition("NM:nm","AGE:20").ors("SORT","1") 生成SQL WHERE (NM = 'zh' AND AGE=20) OR SORT = 1 //表示將在此之前的所有條件合成一組后與當(dāng)前條件OR

關(guān)于IN條件

IN一般需要提供一個(gè)數(shù)組賦值，接收時(shí)通過(guò)[key]的形式接收

service.query("HR_USER", condition("CODE:[code]"))
http://www.anyline.org?code=1&code=2&code=3
對(duì)應(yīng)生成SQL
SELECT * FROM HR_USER WHERE CODE IN(1,2,3)

或

service.query("HR_USER", condition().addConditions("CODE", new ArrayList()));

關(guān)于必須條件

以“+”開(kāi)頭表示當(dāng)前條件必須拼接

如condition("+NM:nm")

如果客戶端提供了nm值，如http://localhost?nm=zh 則正常生成SQL: 

SELECT * FROM TAB WHERE NM = 'zh'

如果沒(méi)有提供nm值,如http://localhost或 http://local?nm= 則生成SQL: 

SELECT * FROM TAB WHERE NM IS NULL

以"++"開(kāi)頭表示當(dāng)前條件必須傳值，否則整個(gè)SQL不執(zhí)行

如果客戶端提供了nm值，如http://localhost?nm=zh 同樣正常生成SQL: 

SELECT * FROM TAB WHERE NM = 'zh'

如果沒(méi)有提供nm值,如http://localhost或 http://local?nm= 則整個(gè)SQL不執(zhí)行，并返回長(zhǎng)度為零的DataSet 同時(shí)會(huì)輸入SQL日志[validate:false]

關(guān)于默認(rèn)值

在接收http參數(shù)時(shí)，如果沒(méi)有指定值則取其他值

service.querys("HR_USER",condition("NM:name:nm:user"));
如果未提供name值或name值為空，則依次根據(jù)nm,user值
 
http://www.anyline.org/user/l?name=zh&nm=zhang&user=z
生成對(duì)應(yīng)SQL
SELECT * FROM HR_USER WHERE NM='zh'
 
http://www.anyline.org/user/l?name=&nm=zhang
生成對(duì)應(yīng)SQL
SELECT * FROM HR_USER WHERE NM='zhang'

也可以通過(guò)常量賦值

service.querys("HR_USER",condition("NM:name:nm:user:{zz}"));
如果通過(guò)name,nm,user都未取到值則取常量值z(mì)z
 
http://www.anyline.org/user/l?name=zh&nm=zhang&user=z
生成對(duì)應(yīng)SQL
SELECT * FROM HR_USER WHERE NM='zh'
 
http://www.anyline.org/user/l
生成對(duì)應(yīng)SQL
SELECT * FROM HR_USER WHERE NM='zz'

關(guān)于like

http://127.0.0.1?nm=zh

service.querys("HR_USER",condition("NM:%nm%"));
生成對(duì)應(yīng)SQL
SELECT * FROM HR_USER WHERE NM LIKE '%zh%'

service.querys("HR_USER",condition("NM:%nm"));
生成對(duì)應(yīng)SQL
SELECT * FROM HR_USER WHERE NM LIKE '%zh'

service.querys("HR_USER",condition("NM:nm%"));
生成對(duì)應(yīng)SQL
SELECT * FROM HR_USER WHERE NM LIKE 'zh%'

addCondition()

condition()用來(lái)接收前端參數(shù)，如果參數(shù)值不是來(lái)自前端面是在java中構(gòu)造的。需要通過(guò)org.anyline.jdbc.config.ConfigStore提供的addCondition函數(shù)用來(lái)動(dòng)態(tài)添加查詢條件

ConfigStore一般通過(guò)condition()返回，如:

service.query("HR_USER", condition("NM:nm").addCondition(...));
addCondition提供了多個(gè)重載，常用以下3個(gè)
//一般會(huì)生成key=value的查詢條件
public ConfigStore addCondition(String key, Object value);
 
//通過(guò)compare來(lái)指定比較符號(hào)如于小，小于，等于,between等參考o(jì)rg.anyline.jdbc.config.db.SQL.COMPARE_TYPE
//需要注意的是，如果需要構(gòu)造in,not int, between條件，應(yīng)該通過(guò)集合形式傳參
public ConfigStore addCondition(COMPARE_TYPE compare, String key, Object value);
 
//如果value是一個(gè)集合則生成IN條件,否則與addCondition作用相同
public ConfigStore addConditions(String key, Object value);
 
另外還有 
public ConfigStore addCondition(Config config);
public ConfigStore addCondition(String key, Object value, boolean overCondition, boolean overValue);
public ConfigStore addCondition(COMPARE_TYPE compare, String key, Object value, boolean overCondition, boolean overValue);
  
//這里同時(shí)提供key與var用來(lái)對(duì)應(yīng)自定義SQL查詢條件中的id與標(biāo)簽體中的變量
public ConfigStore addCondition(String key, String var, Object value);
如
<condition id="USER_AGE">AGE>{AGE_FR} AND AGE<{AGE_TO}</condition>//注意標(biāo)簽體中XML符號(hào)應(yīng)該轉(zhuǎn)義
addCondition("USER_AGE","AGE_FR","10").addCondition("USER_AGE","AGE_TO","20");
 
生成對(duì)應(yīng)的SQL:WHERE AGE>10 AND AGE<20
 
COMPARE_TYPE枚舉
public static enum COMPARE_TYPE{
    EQUAL           {public int getCode(){return 10;} public String getSql(){return " = ?";}                public String getName(){return "等于";}},
    GREAT           {public int getCode(){return 20;} public String getSql(){return " > ?";}                 public String getName(){return "大于";}},
    GREAT_EQUAL     {public int getCode(){return 21;} public String getSql(){return " >= ?";}                public String getName(){return "大于等于";}},
    LESS            {public int getCode(){return 30;} public String getSql(){return " < ?";}                 public String getName(){return "小于";}},
    LESS_EQUAL      {public int getCode(){return 31;} public String getSql(){return " <= ?";}                public String getName(){return "小于等于";}},
    IN              {public int getCode(){return 40;} public String getSql(){return " IN ";}                public String getName(){return "in";}},
    LIKE            {public int getCode(){return 50;} public String getSql(){return " LIKE ";}              public String getName(){return "%like%";}},
    LIKE_PREFIX     {public int getCode(){return 51;} public String getSql(){return " LIKE ";}              public String getName(){return "%like";}},
    LIKE_SUBFIX     {public int getCode(){return 52;} public String getSql(){return " LIKE ";}              public String getName(){return "like%";}},
    BETWEEN         {public int getCode(){return 80;} public String getSql(){return " BETWEEN ? AND ? ";}   public String getName(){return "區(qū)間";}},
    NOT_EQUAL       {public int getCode(){return 110;} public String getSql(){return " != ?";}              public String getName(){return "不等于";}},
    NOT_IN          {public int getCode(){return 140;} public String getSql(){return " NOT IN ";}           public String getName(){return "不包含";}};
    public abstract String getName();
    public abstract String getSql();
    public abstract int getCode();
}

自定義SQL

針對(duì)一些視圖無(wú)法實(shí)現(xiàn)的復(fù)雜的查詢，如果是常用的可以創(chuàng)建數(shù)據(jù)函數(shù)或存儲(chǔ)過(guò)程。

臨時(shí)的可以自定義SQL來(lái)實(shí)現(xiàn)

SQL文件的根目錄通過(guò)anyline-config.xml指定 <property key="SQL_STORE_DIR">/WEB-INF/classes/sql</property>

文件格式:

<?xml version="1.0" encoding="UTF-8"?>
<sqls>
    <sql id="ROLE_LIST">
        <title>用戶角色列表</title>
        <text>
            SELECT 
                M.ID        AS ID, M.NM     AS NM,
                CASE WHEN F.ID IS NULL THEN 0 ELSE 1 END AS CHK
             FROM pw_role AS M
             LEFT JOIN PW_USER_ROLE AS F 
                ON M.ID = F.ROLE_ID AND PW_USER_ID = {PW_USER_ID}  <!--condition("PW_USER_ID:usr")或addCondition("PW_USER_ID",1001) -->
            WHERE DEPT IN({DEPT})
                    AND GROUP_ID = ${GROUP_ID} 
                    AND ${CONDITION_001}
        </text>
        <condition id="TEL">
        M.TEL = ? 
        <!-- condition("TEL:tel")或addCondition("TEL","15800000000") -->
        </condition>
        <condition id="AGE">
        M.AGE >= {AGE_MIN} AND M.AGE < {AGE_MAX} 
         <!--condition("AGE.AGE_MIN:min","AGE.AGE_MAX:max") -->
         <!--或addCondition("AGE.AGE_MIN","10").addCondition("AGE.AGE_MAX","20") -->
        </condition>
        <condition id="FLAG" required="true">
         <!--required表示當(dāng)前條件必須，如沒(méi)有值則默認(rèn)取null -->
        M.FLAG = ?
        </condition>
        <condition id="CODE">
        M.AGE IN({CODE})
        </condition>
        <condition id="STATUS" static="true">
         <!--static表示當(dāng)前條件是靜態(tài)條件不需要?jiǎng)討B(tài)賦值 -->
        M.DATA_STATUS IS NOT NULL
        </condition>
        <condition id="TYPE" static="true" test="USER_TYPE=2">
         <!--test中是一個(gè)ognl表達(dá)式，表達(dá)示返回true時(shí)當(dāng)前條件有效, -->
         <!--condition("TYPE.USER_TYPE:type")或addCondition("TYPE.USER_TYPE","01") -->
        M.DATA_STATUS IS NOT NULL
        </condition>
        <condition id="NM">
         <!--condition("NM:nm")或addCondition(NM","zh") 注意這里賦值時(shí)不需要通配符%  -->    
        M.NM LIKE '%?%' 或者M(jìn).NM LIKE '%{NM}%'
        </condition>
        <group>
        M.NM
        </group>
        <order>
        M.NM ASC 
        </order>
    </sql>
</sqls>

{id} 或者 :id 格式用來(lái)標(biāo)記占位符，在最終生成的SQL中通過(guò)?替換 ${id}

也可以通過(guò)${id} 或者 ::id 格式用來(lái)標(biāo)記占位符，但最終生成SQL時(shí)，不會(huì)用?替換，而是直接用sql.replace("${id}",value)的形式生成

java中調(diào)用SQL時(shí)，通過(guò)condition("id:key")或addCondition("id","value")的形式賦值

如果當(dāng)前文件path=/WEB-INF/classes/sql/hr/user/stat.xml, 則java中調(diào)用通過(guò)service.querys("hr.user.stat:ROLE_LIST",condition("id:key"));的方式調(diào)用

多數(shù)據(jù)源

同一個(gè)應(yīng)用中操作多個(gè)不同的數(shù)據(jù)庫(kù)
啟動(dòng)類上添加注解

@Import(org.anyboot.jdbc.ds.DynamicDataSourceRegister.class)

spring多數(shù)據(jù)源配置

在XML中配置

<bean id="ds_base" class="com.alibaba.druid.pool.DruidDataSource">
    <!-- 配置初始化大小、最小、最大 -->
    <property name="initialSize" value="5" />
    <property name="minIdle" value="20" />
    <property name="maxActive" value="100" />
    <!-- 配置獲取連接等待超時(shí)的時(shí)間 -->
    <property name="maxWait" value="60000" />
    <!-- 配置間隔多久才進(jìn)行一次檢測(cè)，檢測(cè)需要關(guān)閉的空閑連接，單位是毫秒 -->
    <property name="timeBetweenEvictionRunsMillis" value="60000" />
    <!-- 配置一個(gè)連接在池中最小生存的時(shí)間，單位是毫秒 -->
    <property name="minEvictableIdleTimeMillis" value="300000" />
    <property name="validationQuery" value="SELECT 0" />
    <property name="testWhileIdle" value="true" />
    <property name="testOnBorrow" value="false" />
    <property name="testOnReturn" value="false" />
    <!-- 打開(kāi)PSCache，并且指定每個(gè)連接上PSCache的大小 -->
    <property name="poolPreparedStatements" value="true" />
    <property name="maxPoolPreparedStatementPerConnectionSize" value="20" />
    <!-- 對(duì)于長(zhǎng)時(shí)間不使用的連接強(qiáng)制關(guān)閉 -->
    <property name="removeAbandoned" value="true" />
    <property name="removeAbandonedTimeout" value="120" />
    <!-- 配置監(jiān)控統(tǒng)計(jì)攔截的filters -->
    <property name="filters" value="stat" />
</bean>
<bean id="ds_sso" parent="ds_base" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
    <!-- 基本屬性 url、user、password -->
    <property name="url" value="jdbc:mysql://127.0.0.1:3306/SSO?useUnicode=true&amp;characterEncoding=UTF8" />
    <property name="username" value="root" />
    <property name="password" value="root" />
</bean>
<bean id="ds_api" parent="ds_base" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
    <!-- 基本屬性 url、user、password -->
    <property name="url" value="jdbc:mysql://127.0.0.1:3306/API?useUnicode=true&amp;characterEncoding=UTF8" />
    <property name="username" value="root" />
    <property name="password" value="root" />
</bean>
<bean id="ds" class="org.anyline.config.db.ds.DynamicDataSource">  
    <property name="targetDataSources">  
        <map key-type="java.lang.String">
            <!-- 指定lookupKey和與之對(duì)應(yīng)的數(shù)據(jù)源 -->
            <entry key="ds_sso" value-ref="ds_sso"></entry>  
            <entry key="ds_api" value-ref="ds_api"></entry>  
        </map>  
    </property>  
    <!-- 這里可以指定默認(rèn)的數(shù)據(jù)源 -->
    <property name="defaultTargetDataSource" ref="ds_sso" />  
</bean>
<!-- JDBC模板 -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    <property name="dataSource" ref="ds" />
</bean>

JAVA中三種方式調(diào)用

1.DataSourceHolder.setDataSource(String dataSource, boolean auto)

dataSource:對(duì)應(yīng)配置文件中<entry key="ds_sso" value-ref="ds_sso"></entry>的key

auto:執(zhí)行一次SQL后，是否自動(dòng)還原為本次setDataSource執(zhí)行之前的數(shù)據(jù)源

2.在AnylineService.query等方法指定數(shù)據(jù)源

service.query("<ds_api>表名");

需要注意的是，通過(guò)這種方式指定數(shù)據(jù)源，僅對(duì)本次操作有效，執(zhí)行完本次操作后，數(shù)據(jù)源還原為上次設(shè)置的數(shù)據(jù)源

3.通過(guò)注解或攔截器執(zhí)行DataSourceHolder.setDataSource

動(dòng)態(tài)注冊(cè)多數(shù)據(jù)源

在系統(tǒng)啟動(dòng)后通過(guò)java注冊(cè)數(shù)據(jù)源

還是通過(guò)DataSourceHolder.addDataSource(String key, DataSource ds)添加新的數(shù)據(jù)源

DruidDataSource ds = new DruidDataSource();ds.setDriverClassName("com.mysql.jdbc.Driver");ds.setUrl("jdbc:mysql://ip:port/api?useUnicode=true&amp;characterEncoding=UTF8");ds.setUsername("root");ds.setPassword("root");//或者這樣 如果要設(shè)置更多參數(shù) 放到map里String url = "jdbc:mysql://127.0.0.1:3306/sso?useUnicode=true&characterEncoding=UTF8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";DataSourceHolder.reg("sso", "com.zaxxer.hikari.HikariDataSource", "com.mysql.cj.jdbc.Driver", url, "root", "root");

...更多參數(shù)設(shè)置

DataSourceHolder.addDataSource("api", ds);

調(diào)用時(shí)與XML配置的數(shù)據(jù)源調(diào)用方式相同

需要注意的是，添加數(shù)據(jù)源時(shí)如果key重復(fù)，會(huì)拋出異常

springboot多數(shù)據(jù)源配置

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver
spring.datasource.driver=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/anyline?useUnicode=true&characterEncoding=UTF8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.datasource.user=root
spring.datasource.user-name=root
spring.datasource.userName=root
spring.datasource.password=root
#spring.datasource.password=root
spring.datasource.primary.initial-size=10
spring.datasource.primary.max-idle=50
spring.datasource.primary.maxPoolSize=200
spring.datasource.primary.max-wait=3000
spring.datasource.primary.min-idle=50
spring.datasource.primary.validation-query=SELECT 1
spring.datasource.primary.test-on-borrow=true
spring.datasource.primary.test-while-idle=true
spring.datasource.primary.max-lifetime=600000
spring.datasource.primary.time-between-eviction-runs-millis=100000
 
#其他數(shù)據(jù)源
spring.datasource.list=crm,erp
 
spring.datasource.crm.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.crm.url=jdbc:mysql://127.0.0.1:3306/crm?useUnicode=true&characterEncoding=UTF8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.datasource.crm.username=root
spring.datasource.crm.password=root
 
spring.datasource.erp.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.erp.url=jdbc:mysql://127.0.0.1:3306/erp?useUnicode=true&characterEncoding=UTF8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.datasource.erp.username=root
spring.datasource.erp.password=root

切換數(shù)據(jù)源

//用<>表示數(shù)據(jù)源,執(zhí)行完成后會(huì)自動(dòng)切換回默認(rèn)數(shù)據(jù)源
        service.query("<crm>crm_customer");
 
        service.query("HR_DEPARTMENT"); //這里查的還是默認(rèn)數(shù)據(jù)源
 
        service.query("<erp>mm_material");
        try {
            //動(dòng)態(tài)注冊(cè)一個(gè)數(shù)據(jù)源
            //數(shù)據(jù)要設(shè)置更多參數(shù) 放到map里
            String url = "jdbc:mysql://127.0.0.1:3306/sso?useUnicode=true&characterEncoding=UTF8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
            DataSourceHolder.reg("sso", "com.zaxxer.hikari.HikariDataSource", "com.mysql.cj.jdbc.Driver", url, "root", "root");
        }catch (Exception e){
            e.printStackTrace();
        }
        service.query("<sso>sso_user");
        //固定數(shù)據(jù)源
        DataSourceHolder.setDataSource("crm");
        service.query("crm_customer"); //這一行執(zhí)行完成后，數(shù)據(jù)源還是crm
        service.query("crm_customer"); //這里的數(shù)據(jù)源還是crm
        //切換回默認(rèn)數(shù)據(jù)源
        DataSourceHolder.setDefaultDataSource();
        service.query("HR_DEPARTMENT");

緩存集成

約定格式

以上SQL在實(shí)際運(yùn)行中以占位符？生成，類似CODE > '0'的條件實(shí)際是CODE > ?,java中通過(guò) preapreStatement賦值，最終執(zhí)行結(jié)果與數(shù)據(jù)類型有關(guān)

忽略:表示合成SQL時(shí)不拼接當(dāng)前查詢條件

不執(zhí)行:表示整個(gè)SQL不執(zhí)行,querys返回長(zhǎng)度為0的DataSet,query返回null

"+"開(kāi)頭表示必須條件，如果沒(méi)有值傳則生成CODE IS NULL的條件(僅"="時(shí)有效，其他IN,>時(shí)，當(dāng)前條件忽略)

“++”開(kāi)頭時(shí)，如果沒(méi)有傳值則整個(gè)SQL不執(zhí)行，返回長(zhǎng)度為零的DataSet

多表關(guān)聯(lián)查詢

首先Java代碼中不建議使用多表查詢。正常情況下應(yīng)該由數(shù)據(jù)庫(kù)負(fù)責(zé)人來(lái)提供相關(guān)的視圖或存儲(chǔ)過(guò)程。

常用格式如下，其中查詢條件與其他查詢格式一致

set = service.querys(TableBuilder.init().setTable("CRM_TENANT AS M")
            .left("CRM_USER AS U","M.USER_ID = U.ID")
            .build());
 
    set = service.querys(TableBuilder.init("SD_ORDER AS M")
            .left("CRM_USER AS U","M.USER_ID = U.ID")
            .left("CRM_TENANT AS T","M.TENANT_ID = T.ID")
            .build());
 
 
    set = service.querys(TableBuilder.init("SD_ORDER(M.ID,U.NM,T.NM AS TENANT_NM) AS M")
            .left("CRM_USER AS U","M.USER_ID = U.ID")
            .left("CRM_TENANT AS T","M.TENANT_ID = T.ID")
            .build()
        ,condition(true,"M.ID:od","TENANT_ID:tt"));

service.querys(TableBuilder.init()
.setTable("HR_USR(U.ID AS USER_ID,D.ID AS DEPT_ID)").setAlias("U")
.left("HR_DEPT D","U.DEPT_ID = D.ID").build()
, condition(true,"A.NM:%nm%"));

存儲(chǔ)過(guò)程

//存儲(chǔ)過(guò)程定義
CREATE PROCEDURE USER_REG (
         IN _account                                 varchar(10)                         ,                
         IN _password                              vachar(50)                           ,
         INOUT _status_                          int                                          ,
         OUT id_                                         varchar(50)                         ,       
         OUT msg_                                    varchar(50)     
)
 
 
Procedure proc = new ProcedureImpl("過(guò)程名稱");
//輸入?yún)?shù)
proc.addInput("root"); //輸入?yún)?shù)值
proc.addInput("000000");
 
 
//注冊(cè)輸出參數(shù),根據(jù)輸出參數(shù)個(gè)數(shù)調(diào)用
proc.regOutput("1"); //如果參數(shù)既是輸入又是輸出,需要調(diào)用regOutput同時(shí)指定默認(rèn)值
proc.regOutput();
proc.regOutput();
 
//有返回值的存儲(chǔ)過(guò)程,在執(zhí)行之前調(diào)用一次
proc.regReturn();

 
//執(zhí)行存儲(chǔ)過(guò)程 這里只接收存儲(chǔ)過(guò)程是否執(zhí)行成功,并不接收?qǐng)?zhí)行存儲(chǔ)過(guò)程返回結(jié)果,執(zhí)行結(jié)果需要接收輸出參數(shù)結(jié)果
 boolean rtn = service.executeProcedure(proc);
 
//接收輸出參數(shù)與返回值，result中先保存返回值，再依次保存輸出參數(shù)
List<Object> list = proc.getResult();
 
 
//查詢存儲(chǔ)過(guò)程
DataSet set = service.queryProcedure(proc);
//也可以同時(shí)指定輸入?yún)?shù)值
DataSet set = service.queryProcedure(proc, "1","2");

如果通過(guò)query接收存儲(chǔ)過(guò)程的結(jié)果集，需在在過(guò)程中返回結(jié)果集而不是返回一個(gè)值
以SQL Server為例
 
應(yīng)該是這樣：
ALTER PROCEDURE [dbo].[PRO_TEST]
@a int
AS
BEGIN
    SELECT * FROM ABM120T
END
 
 
而不是這樣：
這樣返回的應(yīng)該通過(guò)execute(Procedure proc)執(zhí)行,然后通過(guò)proc.getResult()獲取返回值
ALTER PROCEDURE [dbo].[PRO_TEST]
@a int
AS
BEGIN
    RETURN 0;
END

常用數(shù)據(jù)庫(kù)操作

service.query("HR_USER");
//SELECT * FROM HR_USER
 
service.query("HR_USER(ID,NM)");
//SELECT ID,NM FROM HR_USER
 
service.query("HR_USER","ORDER BY ID"); 
//SELECT * FROM HR_USER ORDER BY ID
 
service.query("CRM_USRE(DISTINCT AGE)");
//SELECT DISTINCT AGE FROM HR_USER
 
service.query("HR_USER(ROLE_ID,COUNT(ID) AS QTY)", "GROUP BY ROLE_ID");
//SELECT ROLE_ID,COUNT(ID) AS QTY FROM HR_USER ORDER BY ROLE_ID
 
service.qurey("HR_USER(ID,NM,{CASE WHEN AGE >20 THEND 1 ELSE 2 END AS AGE_SORT})");//如果列很復(fù)雜用{}區(qū)分開(kāi)
//SELECT ID,NM, CASE WHEN AGE >20 THEND 1 ELSE 2 END AS AGE_SORT FROM HR_USER
 
//多表查詢
service.querys(TableBuilder.init().setTable("CRM_TENANT AS M")
      .left("HR_USER AS U","M.USER_ID = U.ID")
      .build());
service.querys(TableBuilder.init("SD_ORDER AS M")
      .left("HR_USER AS U","M.USER_ID = U.ID")
      .left("CRM_TENANT AS T","M.TENANT_ID = T.ID")
       .build());
service.querys(TableBuilder.init("SD_ORDER(M.ID,U.NM,T.NM AS TENANT_NM) AS M")
       .left("HR_USER AS U","M.USER_ID = U.ID")
       .left("CRM_TENANT AS T","M.TENANT_ID = T.ID")
       .build()
       ,condition(true,"M.ID:od","TENANT_ID:tt"));