关联子查询

QuickDAO持子查询功能,您可以将Condition接口对象作为子查询参数传入.

实体类信息

  1. public class Product {
  2.     @Id(strategy = IdStrategy.IdGenerator)
  3.     private long id;
  5.     @Comment("商品名称")
  6.     private String name;
  8.     @Comment("商品类别")
  9.     private String type;
  11.     @Comment("商品价格")
  12.     private int price;
  14.     @TableField(createdAt = true)
  15.     private Date publishTime;
  16. }

where子查询

  1. //select count(1) from product where price < (select avg(price) from product)
  2. long count = dao.query(Product.class)
  3.                 .addSubQuery("price","<",dao.query(Product.class).addColumn("avg(price)"))
  4.                 .execute()
  5.                 .count();

having子查询

  1. //select count(type) count from product group by type having count(type) > (select 1 from dual)
  2. Condition havingCondition = dao.query("dual")
  3.                 .addColumn("1");
  4. long count = (long) dao.query(Product.class)
  5.         .groupBy("type")
  6.         .having("count(type)",">",havingCondition)
  7.         .addColumn("count(type) count")
  8.         .execute()
  9.         .getSingleColumn(Long.class);

from子查询

  1. //select type avgPrice from (select type, avg(price) avgPrice from product group by type) where avgPrice >= 2000
  2. Condition<Product> fromCondition = dao.query(Product.class)
  3.                 .groupBy("type")
  4.                 .addColumn("type")
  5.                 .addColumn("avg(price) avgPrice");
  6. JSONArray array = dao.query(fromCondition)
  7.                .addQuery("avgPrice",">=",2000)
  8.                .addColumn("type","avgPrice")
  9.                .execute()
  10.                .getArray();

select子查询

  1. //select (select name from dual) nameAlias from product 
  2. Condition selectCondition = dao.query("dual")
  3.                 .addColumn("name");
  4. List<String> productNameList = dao.query(Product.class)
  5.                 .addColumn(selectCondition,"nameAlias")
  6.                 .execute()
  7.                 .getSingleColumnList(String.class);

exist子查询

  1. //select name from product where exists (select id from product where price >= 5000)
  2. List<String> productNameList = dao.query(Product.class)
  3.                 .addExistSubQuery(
  4.                         dao.query(Product.class)
  5.                                 .addQuery("price",">=",5000)
  6.                                 .addColumn("id")
  7.                 )
  8.                 .addColumn("name")
  9.                 .execute()
  10.                 .getSingleColumnList(String.class);