关于查询对象字段映射的一篇小结

第二代ORM理论第一条：

我们可以定义一个查询对象用于映射查询语句，

具体分为以下五种情形：

示例

定义一个UserQuery类用于t_user表的数据查询：

public class AccountOr implements Or {
    private String username;
    private String email;
    private String mobile;
}

public class UserQuery extends PageQuery {

    private AccountOr accountOr;
    private Boolean valid;
    private boolean mobileNotNull;
   
    @NestedQueries({
            @NestedQuery(select = "user_id", from = "t_user_and_role"),
            @NestedQuery(select = "role_id", from = "t_role_and_perm", where = "perm_id"),
            @NestedQuery(select = "id", from = "t_perm")
    })
    private String permNameLike;

    public void setAccount(String account) {
        this.accountOr = AccountOr.builder().username(account).email(account).mobile(account).build();
    }
}

前端请求的参数为：

http://localhost:8080/user/?account=test&valid=true&permNameLike=list&mobileNotNull=true&sort=id,desc;create_time,asc&pageSize=10&pageNumber=5

基于UserQuery字段推导的SQL语句为：

SELECT * FROM t_user
WHERE (username = ? OR email = ? OR mobile = ?)
  AND valid = ?
  AND mobile IS NOT NULL
  AND id IN (
      SELECT user_id FROM t_user_and_role WHERE role_id IN (
          SELECT role_id FROM t_role_and_perm WHERE perm_id IN (
              SELECT id FROM t_perm WHERE perm_name LIKE ?))
  )
ORDER BY id desc, create_time asc
LIMIT 10 OFFSET 40

还有参数：

["test", "test", "test", true, "%list%"]

测试用例

于是，我们在数据访问层定义以下接口用于单表数据的动态查询：

public interface DataAccess<E extends Persistable<I>, I extends Serializable, Q> {
    List<E> query(Q query);
    long count(Q query);
}

详见：github

DOYToWin

关于查询对象字段映射的一篇小结

示例