anyline 数据库操作

AnylineService(配合AnylineDao)提供了常用的数据库操作接口，其中insert,update,delete,execute比较简单也容易理解而select操作相对灵活的多，灵活性主要体现在其参数ConfigStore的构造方式上，通过ConfigStore可以实现非常复杂的查询操作

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

在实际开发过程中，通常是用BaseController继承tAnylineControllerAnylineController中已经注入AnylineService serive，并重载了大量config函数用来自动构造ConfigStore

更详细的操作:AnylineService 与config()

AnylineService

AnylineService用来完成大部分的数据库操作为保持版本兼容query与select功能完全一致

/**
     * 按条件查询
     * @param src           数据源(表或自定义SQL或SELECT语句) src          数据源(表或自定义SQL或SELECT语句)
     * @param configs       封装来自于http的查询条件 configs      封装来自于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);
 
    //实现与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);
    /**
     * 如果二级缓存开启 会从二级缓存中提取数据
     * @param cache 对应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);
    /**
     * 只用一级缓存 忽略二级缓存
     * @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);
     
 
    /**
     * 删除缓存 参数保持与查询参数完全一致
     * @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区别 
     * 操作单个对象时没有区别 
     * 在操作集合时区别: 
     * save会循环操作数据库每次都会判断insert|update 
     * save 集合中的数据可以是不同的表不同的结构  
     * insert 集合中的数据必须保存到相同的表,结构必须相同 
     * insert 将一次性插入多条数据整个过程有可能只操作一次数据库  并 不考虑update情况 对于大批量数据来说 性能是主要优势 
     *  
     */
      
    /** 
     * 执行 
     * @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); 
    /** 
     * 执行存储过程 
     * @param procedure  procedure
     * @param inputs  inputs
     * @return return
     */
    public boolean executeProcedure(String procedure, String... inputs); 
    public boolean executeProcedure(Procedure procedure); 
    /** 
     * 根据存储过程查询 
     * @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); 
      
    /**
     * 删除 根据主键删除
     * @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);

关于更新部分属性(列)、忽略部分属性(列)

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

添加了空值,  默认情况下不参与更新(插入) 如果需要强制参与更新(插入)
row.put("+NAME",null);

如果这样指定了更新(插入)列，则只会更新(插入)指定的列，其他列都不会参与更新(插入)
service.save(row,"NAME");

强制更新(插入)NAME,忽略CODE，其他列不受影响，按默认情况处理
service.save(row,"+NAME","-CODE");

插入所有列，更新所有值发生过变化的列
service.save(row);

关于自动检测表结构

在执行数据库操作时，许多参数是以String形式获取到的,无法参数识别数据类型，如url中的参数

还有一些数据类型在Java中没有对应关系,如xml/josn/几何图形等

而有些数据库在执行SQL时会执行强类型检测。

不像我们平时用的MySQL在执行时会进行隐式转换，无论什么类型只要能转换成功就可以执行。

而PostgreSQL则要求jdbc参数与表结构对应，如果在varchar列中执行int类型会失败。

要求开发人员在编码过程中记住表数据类型，或进行类型转换显示不合理，何况表结构有可能会变动。

可以开启表结构自动检测，在执行SQL前把参数转换成与表结构对应的类型

这样就可以像MySQL一样随意了

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

前端未提交数据情况下清空一列值

默认情况下前端不传值时，执行update时并不更新这一列
如
DataRow row = entity("ID:id","REMARK:remark","NAME:name");
service.save(row);
这时如果没有提交remark参数值，或remark值为空时，并不更新REMARK列
但有些情况下是前端需要清空一个属性值，有以下种方式
1.提交一个"NULL"的值
2.指定必须更新的列
DataRow row = entity("ID:id","+REMARK:remark");
3.显示指定需要更新的列
service.update("HR_USER", row, "REMARK","NAME");

查询时设置主键

如果没有修改配置文件，默认以ID作为每个表的主键，也可以通过配置文件中的DEFAULT_PRIMARY_KEY来修改默认主键
但总有一些数据库设计表时没有主建
而有一些查询过程需要用到主键，比如MSSQL分页查询时会默认根据主键来排序 ROW_NUMBER OVER(ORDER BY ID)
这时如果需要根据其他列来排序可以在查询时指定

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

这样在分页时就以AGE作为排序依据

save与insert区别

save将根据主键是否有值来决定执行update还是insert

对于DataRow参数来说,save调用的insert与直接调用insert没有太大区别, 只是save多了一次判断

区别主要在于DataSet参数：save会循环操作数据库每次都会判断insert|update save 集合中的数据可以是不同的表不同的结构  insert 集合中的数据必须保存到相同的表,结构必须相同 insert 将一次性插入多条数据整个过程有可能只操作一次数据库  并 不考虑update情况 对于大批量数据来说 性能是主要优势 

为什么有时update sql不执行

如果DataRow的值没有变化，则执行service.update时不会执行更新SQL

需要清空内容的列没有更新

默认情况下内容为空的列(“”或null) 不会出现在UPDATE SQL中，

可以通过以下方式强制更新指定列
1.指定更新列
service.update(row,"ID","NM");

2.设置成约定的值NULL(大写)
row.put("NM","NULL");//可以从前台页面输入

3.构造DataRow时指定
row = entityRow("+NM:nm","+CODE:code");

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

5.统一修改配置文件

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

其中4、5需要注意如果DataRow中有表中没有的列(如DataRow是从视图中查出来的)，会导致SQL异常
这就需要人工将表中没有的列remove后再执行更新

condition()

AnylineController中提供了condition()用来接收客户端传值，并赋值给SQL,协助其构造查询条件，condition函数返回org.anyline.jdbc.config.ConfigStore

condition()主要用来:

1.通过HttpRequest构造ConfigStore用来实现复杂的数据库查询条件

2.自动封装分页参数分页参数

比较简单config(true)表示需要分页 config(10)表示分页并显式指定一页10行

由于历史版本的兼容condition,parseConfig,config三者实现的是完全一致的功能。

condition参数格式参考:约定格式

IN条件下多种参数格式的接收

对于标准的url格式 /list?id=1&id=2

以及标准的json格式 {id:[1,2]}

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

对于非标准格式如 /list?id=1,2

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

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

关于几种OR条件查询的情况

//以下三种格式,只有cd取值成功时，条件才生效
 
//当cd=1,id=2时 WHERE CODE = 1 OR CODE =2
//当cd=null,id=2时 条件不生效
//当cd=1,id=null时 WHERE CODE = 1
service.querys("HR_USER", condition("CODE:cd|id"));
 
//当cd=1时 WHERE CODE =1 OR CODE = 9
//当cd=null时 条件不生效
service.querys("HR_USER", condition("CODE:cd|{9}"));
 
 
//当cd=1时 WHERE CODE =1 OR CODE IS NULL
//当cd=null时 条件不生效
service.querys("HR_USER", condition("CODE:cd|{NULL}"));

//当type=1,dept=null时 WHERE TYPE_CODE = 1
//当type=1,dept=2时 WHERE TYPE_CODE =1 OR DEPT_ID =2 
//当type=null,dept=2时 WHERE DEPT_ID = 2
service.querys("HR_USER", condition("TYPE_CODE:type|DEPT_ID:dept"));

//依次取c1,c2的值，如果c1取值成功则忽略c2,如果都失败则取默认值9
service.querys("HR_USER", condition("CODE:c1:c2:{9}"));

关于or

OR条件构造相对复杂

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

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

中有第一个nm取值成功当前条件才生效，如果nm取值失败后面的name,user也忽略，当前条件无效

经常会这样用:

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 //表示将在此之前的所有条件合成一组后与当前条件OR

关于IN条件

IN一般需要提供一个数组赋值，接收时通过[key]的形式接收

service.query("HR_USER", condition("CODE:[code]"))
http://www.anyline.org?code=1&code=2&code=3
对应生成SQL
SELECT * FROM HR_USER WHERE CODE IN(1,2,3)

或

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

关于必须条件

以“+”开头表示当前条件必须拼接

如condition("+NM:nm")

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

SELECT * FROM TAB WHERE NM = 'zh'

如果没有提供nm值,如http://localhost或 http://local?nm= 则生成SQL: 

SELECT * FROM TAB WHERE NM IS NULL

以"++"开头表示当前条件必须传值，否则整个SQL不执行

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

SELECT * FROM TAB WHERE NM = 'zh'

如果没有提供nm值,如http://localhost或 http://local?nm= 则整个SQL不执行，并返回长度为零的DataSet 同时会输入SQL日志[validate:false]

关于默认值

在接收http参数时，如果没有指定值则取其他值

service.querys("HR_USER",condition("NM:name:nm:user"));
如果未提供name值或name值为空，则依次根据nm,user值
 
http://www.anyline.org/user/l?name=zh&nm=zhang&user=z
生成对应SQL
SELECT * FROM HR_USER WHERE NM='zh'
 
http://www.anyline.org/user/l?name=&nm=zhang
生成对应SQL
SELECT * FROM HR_USER WHERE NM='zhang'

也可以通过常量赋值

service.querys("HR_USER",condition("NM:name:nm:user:{zz}"));
如果通过name,nm,user都未取到值则取常量值zz
 
http://www.anyline.org/user/l?name=zh&nm=zhang&user=z
生成对应SQL
SELECT * FROM HR_USER WHERE NM='zh'
 
http://www.anyline.org/user/l
生成对应SQL
SELECT * FROM HR_USER WHERE NM='zz'

关于like

http://127.0.0.1?nm=zh

service.querys("HR_USER",condition("NM:%nm%"));
生成对应SQL
SELECT * FROM HR_USER WHERE NM LIKE '%zh%'

service.querys("HR_USER",condition("NM:%nm"));
生成对应SQL
SELECT * FROM HR_USER WHERE NM LIKE '%zh'

service.querys("HR_USER",condition("NM:nm%"));
生成对应SQL
SELECT * FROM HR_USER WHERE NM LIKE 'zh%'

addCondition()

condition()用来接收前端参数，如果参数值不是来自前端面是在java中构造的。需要通过org.anyline.jdbc.config.ConfigStore提供的addCondition函数用来动态添加查询条件

ConfigStore一般通过condition()返回，如:

service.query("HR_USER", condition("NM:nm").addCondition(...));
addCondition提供了多个重载，常用以下3个
//一般会生成key=value的查询条件
public ConfigStore addCondition(String key, Object value);
 
//通过compare来指定比较符号如于小，小于，等于,between等参考org.anyline.jdbc.config.db.SQL.COMPARE_TYPE
//需要注意的是，如果需要构造in,not int, between条件，应该通过集合形式传参
public ConfigStore addCondition(COMPARE_TYPE compare, String key, Object value);
 
//如果value是一个集合则生成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);
  
//这里同时提供key与var用来对应自定义SQL查询条件中的id与标签体中的变量
public ConfigStore addCondition(String key, String var, Object value);
如
<condition id="USER_AGE">AGE>{AGE_FR} AND AGE<{AGE_TO}</condition>//注意标签体中XML符号应该转义
addCondition("USER_AGE","AGE_FR","10").addCondition("USER_AGE","AGE_TO","20");
 
生成对应的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 "区间";}},
    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

针对一些视图无法实现的复杂的查询，如果是常用的可以创建数据函数或存储过程。

临时的可以自定义SQL来实现

SQL文件的根目录通过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表示当前条件必须，如没有值则默认取null -->
        M.FLAG = ?
        </condition>
        <condition id="CODE">
        M.AGE IN({CODE})
        </condition>
        <condition id="STATUS" static="true">
         <!--static表示当前条件是静态条件不需要动态赋值 -->
        M.DATA_STATUS IS NOT NULL
        </condition>
        <condition id="TYPE" static="true" test="USER_TYPE=2">
         <!--test中是一个ognl表达式，表达示返回true时当前条件有效, -->
         <!--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") 注意这里赋值时不需要通配符%  -->    
        M.NM LIKE '%?%' 或者M.NM LIKE '%{NM}%'
        </condition>
        <group>
        M.NM
        </group>
        <order>
        M.NM ASC 
        </order>
    </sql>
</sqls>

{id} 或者 :id 格式用来标记占位符，在最终生成的SQL中通过?替换 ${id}

也可以通过${id} 或者 ::id 格式用来标记占位符，但最终生成SQL时，不会用?替换，而是直接用sql.replace("${id}",value)的形式生成

java中调用SQL时，通过condition("id:key")或addCondition("id","value")的形式赋值

如果当前文件path=/WEB-INF/classes/sql/hr/user/stat.xml, 则java中调用通过service.querys("hr.user.stat:ROLE_LIST",condition("id:key"));的方式调用

多数据源

同一个应用中操作多个不同的数据库
启动类上添加注解

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

spring多数据源配置

在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" />
    <!-- 配置获取连接等待超时的时间 -->
    <property name="maxWait" value="60000" />
    <!-- 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 -->
    <property name="timeBetweenEvictionRunsMillis" value="60000" />
    <!-- 配置一个连接在池中最小生存的时间，单位是毫秒 -->
    <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" />
    <!-- 打开PSCache，并且指定每个连接上PSCache的大小 -->
    <property name="poolPreparedStatements" value="true" />
    <property name="maxPoolPreparedStatementPerConnectionSize" value="20" />
    <!-- 对于长时间不使用的连接强制关闭 -->
    <property name="removeAbandoned" value="true" />
    <property name="removeAbandonedTimeout" value="120" />
    <!-- 配置监控统计拦截的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和与之对应的数据源 -->
            <entry key="ds_sso" value-ref="ds_sso"></entry>  
            <entry key="ds_api" value-ref="ds_api"></entry>  
        </map>  
    </property>  
    <!-- 这里可以指定默认的数据源 -->
    <property name="defaultTargetDataSource" ref="ds_sso" />  
</bean>
<!-- JDBC模板 -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    <property name="dataSource" ref="ds" />
</bean>

JAVA中三种方式调用

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

dataSource:对应配置文件中<entry key="ds_sso" value-ref="ds_sso"></entry>的key

auto:执行一次SQL后，是否自动还原为本次setDataSource执行之前的数据源

2.在AnylineService.query等方法指定数据源

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

需要注意的是，通过这种方式指定数据源，仅对本次操作有效，执行完本次操作后，数据源还原为上次设置的数据源

3.通过注解或拦截器执行DataSourceHolder.setDataSource

动态注册多数据源

在系统启动后通过java注册数据源

还是通过DataSourceHolder.addDataSource(String key, DataSource ds)添加新的数据源

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");//或者这样 如果要设置更多参数 放到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");

...更多参数设置

DataSourceHolder.addDataSource("api", ds);

调用时与XML配置的数据源调用方式相同

需要注意的是，添加数据源时如果key重复，会抛出异常

springboot多数据源配置

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
 
#其他数据源
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

切换数据源

//用<>表示数据源,执行完成后会自动切换回默认数据源
        service.query("<crm>crm_customer");
 
        service.query("HR_DEPARTMENT"); //这里查的还是默认数据源
 
        service.query("<erp>mm_material");
        try {
            //动态注册一个数据源
            //数据要设置更多参数 放到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");
        //固定数据源
        DataSourceHolder.setDataSource("crm");
        service.query("crm_customer"); //这一行执行完成后，数据源还是crm
        service.query("crm_customer"); //这里的数据源还是crm
        //切换回默认数据源
        DataSourceHolder.setDefaultDataSource();
        service.query("HR_DEPARTMENT");

缓存集成

约定格式

以上SQL在实际运行中以占位符？生成，类似CODE > '0'的条件实际是CODE > ?,java中通过 preapreStatement赋值，最终执行结果与数据类型有关

忽略:表示合成SQL时不拼接当前查询条件

不执行:表示整个SQL不执行,querys返回长度为0的DataSet,query返回null

"+"开头表示必须条件，如果没有值传则生成CODE IS NULL的条件(仅"="时有效，其他IN,>时，当前条件忽略)

“++”开头时，如果没有传值则整个SQL不执行，返回长度为零的DataSet

多表关联查询

首先Java代码中不建议使用多表查询。正常情况下应该由数据库负责人来提供相关的视图或存储过程。

常用格式如下，其中查询条件与其他查询格式一致

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%"));

存储过程

//存储过程定义
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("过程名称");
//输入参数
proc.addInput("root"); //输入参数值
proc.addInput("000000");
 
 
//注册输出参数,根据输出参数个数调用
proc.regOutput("1"); //如果参数既是输入又是输出,需要调用regOutput同时指定默认值
proc.regOutput();
proc.regOutput();
 
//有返回值的存储过程,在执行之前调用一次
proc.regReturn();

 
//执行存储过程 这里只接收存储过程是否执行成功,并不接收执行存储过程返回结果,执行结果需要接收输出参数结果
 boolean rtn = service.executeProcedure(proc);
 
//接收输出参数与返回值，result中先保存返回值，再依次保存输出参数
List<Object> list = proc.getResult();
 
 
//查询存储过程
DataSet set = service.queryProcedure(proc);
//也可以同时指定输入参数值
DataSet set = service.queryProcedure(proc, "1","2");

如果通过query接收存储过程的结果集，需在在过程中返回结果集而不是返回一个值
以SQL Server为例
 
应该是这样：
ALTER PROCEDURE [dbo].[PRO_TEST]
@a int
AS
BEGIN
    SELECT * FROM ABM120T
END
 
 
而不是这样：
这样返回的应该通过execute(Procedure proc)执行,然后通过proc.getResult()获取返回值
ALTER PROCEDURE [dbo].[PRO_TEST]
@a int
AS
BEGIN
    RETURN 0;
END

常用数据库操作

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})");//如果列很复杂用{}区分开
//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"));