/**
     * 先创建测试表
     * @throws Exception Exception
     */
    @Test
    public void init() throws Exception {
        init("FI_USER");
        init("HR_USER");
        init("MM_USER");
    }
    public void init(String name) throws Exception {
        Table table = ServiceProxy.metadata().table(name, false);
        if(null != table){
            ServiceProxy.ddl().drop(table);
        }
        table = new Table(name);
        table.addColumn("ID", "BIGINT").setPrimary(true).setAutoIncrement(true);
        table.addColumn("CODE", "VARCHAR(32)");
        table.addColumn("NAME", "VARCHAR(10)");
        table.addColumn("TYPE_CODE", "VARCHAR(10)");
        table.addColumn("LVL", "INT");
        table.addColumn("REMARK", "VARCHAR(100)");
        ServiceProxy.ddl().create(table);
    }
    @Test
    public void table1(){
        ServiceProxy.querys("FI_USER");
        //SELECT * FROM FI_USER
    }
    @Test
    public void table2(){
        ServiceProxy.querys("FI_USER(ID, CODE AS USER_CODE)");
        //SELECT ID, CODE AS USER_CODE FROM FI_USER
    }
    @Test
    public void sql(){
        ServiceProxy.querys("SELECT * FROM FI_USER");
    }
    @Test
    public void builder1(){
        RunPrepare prepare = TableBuilder.init("FI_USER").build();
        ServiceProxy.querys(prepare);
        //SELECT * FROM FI_USER
    }
    @Test
    public void builder2(){
        //表名(列,列)
        RunPrepare prepare = TableBuilder.init("FI_USER(ID AS USER_ID, CODE)").build();
        ServiceProxy.querys(prepare);
        //SELECT  ID AS USER_ID, CODE FROM FI_USER
    }
    @Test
    public void builder3(){
        //表名(列,列) AS 表别名
        RunPrepare prepare = TableBuilder.init("FI_USER(ID AS USER_ID, CODE) AS M").build();
        ServiceProxy.querys(prepare);
        //SELECT  ID AS USER_ID, CODE FROM FI_USER AS M
    }
    @Test
    public void builder_join1(){
        RunPrepare prepare = TableBuilder.init("FI_USER AS FI").left("HR_USER AS HR", "FI.ID = HR.ID").build();
        ServiceProxy.querys(prepare);
        // SELECT  * FROM FI_USER AS FI
        // LEFT JOIN HR_USER AS HR ON FI.ID = HR.ID
    }

    /**
     * 指定查询列
     * 可以在表名名指定
     */
    @Test
    public void builder_join2(){
        RunPrepare prepare = TableBuilder.init("FI_USER(FI.ID AS FI_ID, HR.ID AS HR_ID) AS FI")
                .left("HR_USER AS HR", "FI.ID = HR.ID")
                .build();
        ServiceProxy.querys(prepare);
        //SQL
        SELECT
            FI.ID AS FI_ID, HR.ID AS HR_ID
        FROM FI_USER AS FI
        LEFT JOIN HR_USER AS HR ON FI.ID = HR.ID
        
    }
    /**
     * 指定查询列
     * 可以在表名名指定
     */
    @Test
    public void builder_join2_condition(){
        RunPrepare prepare = TableBuilder.init("FI_USER(FI.ID AS FI_ID, HR.ID AS HR_ID) AS FI")
            .left("HR_USER AS HR", "FI.ID = HR.ID")
            .build();
        ServiceProxy.querys(prepare, "FI.ID:1");
        //SQL
         SELECT
            FI.ID AS FI_ID, HR.ID AS HR_ID
        FROM FI_USER AS FI
        LEFT JOIN HR_USER AS HR ON FI.ID = HR.ID
        WHERE FI.ID = ?
        
    }
    /**
     * 指定查询列
     * 也可以单独指定
     */
    @Test
    public void builder_join3(){
        RunPrepare prepare = TableBuilder.init("FI_USER AS FI")
                .left("HR_USER AS HR", "FI.ID = HR.ID")
                .columns("FI.ID AS FI_ID", "HR.ID AS HR_ID")
                .build();
        ServiceProxy.querys(prepare);
        //SQL
        SELECT
            FI.ID AS FI_ID, HR.ID AS HR_ID
        FROM FI_USER AS FI
        LEFT JOIN HR_USER AS HR ON FI.ID = HR.ID
        
    }
    @Test
    public void builder_inner1(){
        //子查询
        RunPrepare inner_hr = TableBuilder.init("HR_USER(ID AS HR_ID, CODE AS HR_CODE) AS HR").build();

        RunPrepare master = TableBuilder.init("FI_USER(M.ID AS FI_ID, HRS.HR_CODE) AS M")   //()内指定的是最外层的查询列名，放在主表名容易误解，可以addColumns()单独指定
            .left("HRS", inner_hr, "HRS.HR_ID = M.ID", "HRS.HR_CODE = M.CODE")    //主表的表名列名要用原名 这里的子查的表名列名注意用 别名 HRS是当前子查询的别名
            //.columns("M.ID AS ID1", "M.ID AS ID2", "HR.HR_ID AS ID3")                            //设置查询列名，注意是追加不会覆盖  覆盖用setColumns()
            .build();
        ServiceProxy.querys(master);
        //SQL 注意区分内外层 别名
        SELECT
            M.ID AS FI_ID, HRS.HR_CODE
        FROM FI_USER AS M
        LEFT JOIN (
            SELECT
                ID AS HR_ID, CODE AS HR_CODE
            FROM HR_USER AS HR
        ) AS HRS ON (HRS.HR_ID = M.ID AND HRS.HR_CODE = M.CODE)
        
    }

    @Test
    public void builder_inner_condition(){
        //子查询
        ConfigStore configs = new DefaultConfigStore();
        configs.and("ID", ""); //空条件忽略
        configs.and("CODE=1");
        configs.and("LVL", "2");
        RunPrepare inner_fi = TableBuilder.init("FI_USER(ID AS FI_ID, CODE AS FI_CODE, 'FI' AS BIZ_TYPE_CODE) AS FI").condition(configs).build();

        configs = new DefaultConfigStore();
        configs.and("ID", "");//空条件忽略
        configs.and("CODE=10");
        configs.and("LVL", "20");
        RunPrepare inner_hr = TableBuilder.init("HR_USER(ID AS HR_ID, CODE AS HR_CODE) AS HR").condition(configs).build();

        RunPrepare group_mm = TableBuilder.init("HR_USER(TYPE_CODE, LVL, MAX(ID) AS MAX_ID) AS MM").build().group("TYPE_CODE", "LVL").having("MAX(ID) > 10");


        RunPrepare master = TableBuilder.init("FIS", inner_fi)   //主表也用一个子查询
            .left("HRS", inner_hr, "HRS.HR_ID = FIS.FI_ID", "HRS.HR_CODE = FIS.FI_CODE")                  //主表的表名列名要用原名 这里的子查的表名列名注意用 别名
            .left("MMS", group_mm, "MMS.MAX_ID = FIS.FI_ID")
            .setColumns("FIS.FI_ID AS FI_IDS","1 AS STATIC_VALUE", "FIS.BIZ_TYPE_CODE") //注意里这里要用外层别名
            .build();
        ServiceProxy.querys(master, "HRS.HR_ID > 3", "HRS.HR_CODE:30");
        //SQL
            SELECT
                FIS.FI_ID AS FI_IDS, 1 AS STATIC_VALUE, FIS.BIZ_TYPE_CODE
            FROM (
                SELECT
                    ID AS FI_ID, CODE AS FI_CODE, 'FI' AS BIZ_TYPE_CODE
                FROM FI_USER AS FI
                WHERE (CODE=1 AND FI.LVL = ?)
            ) AS FIS
            LEFT JOIN (
                SELECT
                    ID AS HR_ID, CODE AS HR_CODE
                FROM HR_USER AS HR
                WHERE (CODE=10 AND HR.LVL = ?)
            ) AS HRS ON (HRS.HR_ID = FIS.FI_ID AND HRS.HR_CODE = FIS.FI_CODE)
            LEFT JOIN (
                SELECT
                    TYPE_CODE, LVL, MAX(ID) AS MAX_ID
                FROM HR_USER AS MM
                    GROUP BY TYPE_CODE, LVL HAVING MAX(ID) > 10
            ) AS MMS ON MMS.MAX_ID = FIS.FI_ID
            WHERE (HRS.HR_ID > 3 AND HRS.HR_CODE = ?)

            param0=2(java.lang.String)
            param1=20(java.lang.String)
            param2=30(java.lang.String)
        
    }