Query

This section mainly introduces the new features in the v2 version of the YMP framework, which assists developers in writing SQL statements like Java code, replacing the traditional string splicing mode to a certain extent, and then matching the fields of data entities. The advantage of using constants together is to reduce the probability of errors during string splicing. Some specific problems can be discovered during compilation, because Java code is a SQL statement!

Basic parameter object

• Fields: Field name collection object, used to assist in splicing data table field names, supporting prefixes, aliases, etc.;

  Sample code:

  // 创建Fields对象
  Fields _fields = Fields.create("username", "pwd", "age");
  // 带前缀和别名
  _fields.add("u", "sex", "s");
  // 带前缀
  _fields = Fields.create().add("u", "id").add(_fields);
  // 标记集合中的字段为排除的
  _fields.excluded(true);
  // 判断是否存在排除标记
  _fields.isExcluded();
  // 输出
  System.out.println(_fields.fields());

  Execution result:

  [u.id, username, pwd, age, u.sex s]

• Params: parameter collection object, mainly used to store and replace the ? placeholder in SQL statements;

  Sample code:

  // 创建Params对象，任何类型参数
  Params _params = Params.create("p1", 2, false, 0.1).add("param");
  // 
  _params = Params.create().add("paramN").add(_params);
  // 输出
  System.out.println(_params.params());

  Execution results:

  [paramN, p1, 2, false, 0.1, param]

• Pages: paging parameter object;

  Sample code:

  // 查询每1页, 默认每页20条记录
  Page.create(1);
  // 查询第1页, 每页10条记录
  Page.create(1).pageSize(10);
  // 查询第1页, 每页10条记录, 不统计总记录数
  Page.create(1).pageSize(10).count(false);

• Cond: condition parameter object, used to generate SQL conditions and store condition parameters;

  Sample code:

  Generate the following SQL conditions:

  • (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("参数: " + _cond.params().params());

  Execution result:

  SQL: ( username LIKE ? AND age >= ? )  OR  ( sex = ? AND age < ? ) 
  参数: [%ymp%, 20, F, 18]

• OrderBy: sort object, use Used to generate the Order By statement in the SQL condition;

  Sample code:

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

  Execution result:

  ORDER BY age, u.birthday DESC

• GroupBy: Grouping object, used to generate Group By statements in SQL conditions;

  Sample code:

  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("参数: " + _groupBy.having().params().params());

  Execution result:

  SQL: GROUP BY u.sex, dept HAVING age < ?
  参数: [18]

• Where: Where statement object, used to generate SQL statements Where clause;

  Sample code:

  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("参数: " + _where.getParams().params());

  Execution results: (For the convenience of reading, the SQL output format is beautified here: P)

  SQL: WHERE
           username LIKE ?
       AND age >= ?
       GROUP BY
           u.sex,
           dept
       ORDER BY
           username DESC,
           age,
           u.birthday DESC
  参数: [%ymp%, 20]

• Join: connection statement object, used to generate Join clauses in SQL statements, supporting left, right and inner Connection;

  Sample code:

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

  Execution result:

  INNER JOIN user_ext ue ON ue.uid = u.id

• Union: Union statement object, used to merge multiple Select query results;

  Sample code:

  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("参数: " + _select.getParams().params());

  Execution result:

  SQL: SELECT  *  FROM user WHERE dept = ?  UNION SELECT  *  FROM user WHERE age < ?   
  参数: [IT, 18]

##Select: query statement object

示例代码：

    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("参数: " + _select.getParams().params());

执行结果：（为方便阅读，此处美化了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 
    参数: [%ymp%, 20]

Insert: Insert statement object

示例代码：

    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("参数: " + _insert.params().params());

执行结果：

    SQL: INSERT INTO user (id, age, username) VALUES (?, ?, ?)
    参数: [123456, 18, suninformation]

示例代码：

    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("参数: " + _update.getParams().params());

执行结果：

    SQL: UPDATE user SET pwd = ?, age = ? WHERE id = ? 
    参数: [xxxx, 20, 123456]

示例代码：

    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("参数: " + _delete.getParams().params());

执行结果：

    SQL: DELETE  FROM user WHERE id = ? 
    参数: [123456]

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

// 或封装语句对象
SQL.create(_select);
SQL.create(_insert);
SQL.create(_update);
SQL.create(_delete);

// 定义批操作
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");
// 执行
session.executeForUpdate(_sqls);

session.find(EntitySQL.create(User.class)
            .field(Fields.create(User.FIELDS.ID, User.FIELDS.USER_NAME)
                    .excluded(true)));