查詢(Query)

2018-12-24 22:21 更新

本節(jié)主要介紹YMP框架v2版本中新增的特性,輔助開發(fā)人員像寫Java代碼一樣編寫SQL語句,在一定程度上替代傳統(tǒng)字符串拼接的模式,再配合數(shù)據(jù)實(shí)體的字段常量一起使用,這樣做的好處就是降低字符串拼接過程中出錯的機(jī)率,一些特定問題編譯期間就能發(fā)現(xiàn),因?yàn)镴ava代碼就是SQL語句!

基礎(chǔ)參數(shù)對象
  • Fields:字段名稱集合對象,用于輔助拼接數(shù)據(jù)表字段名稱,支持前綴、別名等;

    示例代碼:

    // 創(chuàng)建Fields對象
    Fields _fields = Fields.create("username", "pwd", "age");
    // 帶前綴和別名
    _fields.add("u", "sex", "s");
    // 帶前綴
    _fields = Fields.create().add("u", "id").add(_fields);
    // 標(biāo)記集合中的字段為排除的
    _fields.excluded(true);
    // 判斷是否存在排除標(biāo)記
    _fields.isExcluded();
    // 輸出
    System.out.println(_fields.fields());
    

    執(zhí)行結(jié)果:

    [u.id, username, pwd, age, u.sex s]
    
  • Params:參數(shù)集合對象,主要用于存儲替換SQL語句中?號占位符;

    示例代碼:

    // 創(chuàng)建Params對象,任何類型參數(shù)
    Params _params = Params.create("p1", 2, false, 0.1).add("param");
    // 
    _params = Params.create().add("paramN").add(_params);
    // 輸出
    System.out.println(_params.params());
    

    執(zhí)行結(jié)果:

    [paramN, p1, 2, false, 0.1, param]
    
  • Pages:分頁參數(shù)對象;

    示例代碼:

    // 查詢每1頁, 默認(rèn)每頁20條記錄
    Page.create(1);
    // 查詢第1頁, 每頁10條記錄
    Page.create(1).pageSize(10);
    // 查詢第1頁, 每頁10條記錄, 不統(tǒng)計總記錄數(shù)
    Page.create(1).pageSize(10).count(false);
    
  • Cond:條件參數(shù)對象,用于生成SQL條件和存儲條件參數(shù);

    示例代碼:

    生成如下SQL條件:

    • (username like ? and age >= ?) or (sex = ? and age < ?)
    Cond _cond = Cond.create()
        .bracketBegin().like("username").param("%ymp%").and().gtEq("age").param(20).bracketEnd()
        .or()
        .bracketBegin().eq("sex").param("F").and().lt("age").param(18).bracketEnd();
    
    System.out.println("SQL: " + _cond.toString());
    System.out.println("參數(shù): " + _cond.params().params());
    

    執(zhí)行結(jié)果:

    SQL: ( username LIKE ? AND age >= ? )  OR  ( sex = ? AND age < ? ) 
    參數(shù): [%ymp%, 20, F, 18]
    
  • OrderBy:排序?qū)ο?,用于生成SQL條件中的Order By語句;

    示例代碼:

    OrderBy _orderBy = OrderBy.create().asc("age").desc("u", "birthday");
    //
    System.out.println(_orderBy.toSQL());
    

    執(zhí)行結(jié)果:

    ORDER BY age, u.birthday DESC
    
  • GroupBy:分組對象,用于生成SQL條件中的Group By語句;

    示例代碼:

    GroupBy _groupBy = GroupBy.create(Fields.create().add("u", "sex").add("dept"))
        .having(Cond.create().lt("age").param(18));
    
    System.out.println("SQL: " + _groupBy.toString());
    System.out.println("參數(shù): " + _groupBy.having().params().params());
    

    執(zhí)行結(jié)果:

    SQL: GROUP BY u.sex, dept HAVING age < ?
    參數(shù): [18]
    
  • Where:Where語句對象,用于生成SQL語句中的Where子句;

    示例代碼:

    Cond _cond = Cond.create()
            .like("username").param("%ymp%")
            .and().gtEq("age").param(20);
    
    OrderBy _orderBy = OrderBy.create().asc("age").desc("u", "birthday");
    
    GroupBy _groupBy = GroupBy.create(Fields.create().add("u", "sex").add("dept"));
    
    Where _where = Where.create(_cond).groupBy(_groupBy).orderDesc("username");
    
    _where.orderBy().orderBy(_orderBy);
    //
    System.out.println("SQL: " + _where.toString());
    System.out.println("參數(shù): " + _where.getParams().params());
    

    執(zhí)行結(jié)果:(為方便閱讀,此處美化了SQL的輸出格式:P)

    SQL: WHERE
             username LIKE ?
         AND age >= ?
         GROUP BY
             u.sex,
             dept
         ORDER BY
             username DESC,
             age,
             u.birthday DESC
    參數(shù): [%ymp%, 20]
    
  • Join:連接語句對象,用于生成SQL語句中的Join子句,支持left、right和inner連接;

    示例代碼:

    Join _join = Join.inner("user_ext").alias("ue")
        .on(Cond.create().opt("ue", "uid", Cond.OPT.EQ, "u", "id"));
    
    System.out.println(_join);
    

    執(zhí)行結(jié)果:

    INNER JOIN user_ext ue ON ue.uid = u.id
    
  • Union:聯(lián)合語句對象,用于將多個Select查詢結(jié)果合并;

    示例代碼:

    Select _select = Select.create("user").where(Where.create(Cond.create().eq("dept").param("IT")))
            .union(Union.create(
                    Select.create("user").where(Where.create(Cond.create().lt("age").param(18)))));
    //
    System.out.println("SQL: " + _select.toString());
    System.out.println("參數(shù): " + _select.getParams().params());
    

    執(zhí)行結(jié)果:

    SQL: SELECT  *  FROM user WHERE dept = ?  UNION SELECT  *  FROM user WHERE age < ?   
    參數(shù): [IT, 18]
    
Select:查詢語句對象
示例代碼:

    Cond _cond = Cond.create()
            .like("u", "username").param("%ymp%")
            .and().gtEq("u", "age").param(20);
    //
    GroupBy _groupBy = GroupBy.create(Fields.create().add("u", "sex").add("u", "dept"));
    //
    Where _where = Where.create(_cond).groupBy(_groupBy).orderDesc("u", "username");
    //
    Join _join = Join.inner("user_ext").alias("ue")
            .on(Cond.create().opt("ue", "uid", Cond.OPT.EQ, "u", "id"));
    //
    Select _select = Select.create(User.class, "u")
            .field("u", "username").field("ue", "money")
            .where(_where)
            .join(_join)
            .distinct();
    //
    System.out.println("SQL: " + _select.toString());
    System.out.println("參數(shù): " + _select.getParams().params());

執(zhí)行結(jié)果:(為方便閱讀,此處美化了SQL的輸出格式:P)

    SQL: SELECT DISTINCT
                u.username,
                ue.money
            FROM
                USER u
            INNER JOIN user_ext ue ON ue.uid = u.id
            WHERE
                u.username LIKE ?
            AND u.age >= ?
            GROUP BY
                u.sex,
                u.dept
            ORDER BY
                u.username DESC 
    參數(shù): [%ymp%, 20]
Insert:插入語句對象
示例代碼:

    Insert _insert = Insert.create(User.class)
            .field(User.FIELDS.ID).param("123456")
            .field(User.FIELDS.AGE).param(18)
            .field(User.FIELDS.USER_NAME).param("suninformation");
    //
    System.out.println("SQL: " + _insert.toString());
    System.out.println("參數(shù): " + _insert.params().params());

執(zhí)行結(jié)果:

    SQL: INSERT INTO user (id, age, username) VALUES (?, ?, ?)
    參數(shù): [123456, 18, suninformation]
Update:更新語句對象
示例代碼:

    Update _update = Update.create(User.class)
            .field(User.FIELDS.PWD).param("xxxx")
            .field(User.FIELDS.AGE).param(20)
            .where(Where.create(
                    Cond.create().eq(User.FIELDS.ID).param("123456")));
    //
    System.out.println("SQL: " + _update.toString());
    System.out.println("參數(shù): " + _update.getParams().params());

執(zhí)行結(jié)果:

    SQL: UPDATE user SET pwd = ?, age = ? WHERE id = ? 
    參數(shù): [xxxx, 20, 123456]
Delete:刪除語句對象
示例代碼:

    Delete _delete = Delete.create(User.class)
            .where(Where.create(
                    Cond.create().eq(User.FIELDS.ID).param("123456")));
    //
    System.out.println("SQL: " + _delete.toString());
    System.out.println("參數(shù): " + _delete.getParams().params());

執(zhí)行結(jié)果:

    SQL: DELETE  FROM user WHERE id = ? 
    參數(shù): [123456]
SQL:自定義SQL語句

同時也用于ISession會話接口參數(shù)封裝;

示例代碼:

// 自定義SQL語句
SQL _sql = SQL.create("select * from user where age > ? and username like ?").param(18).param("%ymp%");
// 執(zhí)行
session.find(_sql, IResultSetHandler.ARRAY);

// 或封裝語句對象
SQL.create(_select);
SQL.create(_insert);
SQL.create(_update);
SQL.create(_delete);
BatchSQL:批量SQL語句對象

主要用于ISession會話對批量操作的參數(shù)封裝;

示例代碼:

// 定義批操作
BatchSQL _sqls = BatchSQL.create("INSERT INTO user (id, age, username) VALUES (?, ?, ?)")
        .addParameter(Params.create("xxxx", 18, "user0"))
        .addParameter(Params.create("xxx1", 20, "user1"))
        .addParameter(Params.create("xxxN", 20, "userN"))
        .addSQL("DELETE  FROM user WHERE age > 30")
        .addSQL("DELETE  FROM user WHERE age < 18");
// 執(zhí)行
session.executeForUpdate(_sqls);
EntitySQL:實(shí)體參數(shù)封裝對象

主要用于ISession會話的參數(shù)封裝;

示例代碼:

session.find(EntitySQL.create(User.class)
            .field(Fields.create(User.FIELDS.ID, User.FIELDS.USER_NAME)
                    .excluded(true)));
以上內(nèi)容是否對您有幫助:
在線筆記
App下載
App下載

掃描二維碼

下載編程獅App

公眾號
微信公眾號

編程獅公眾號