DemoQuery

public class Config
{
	public static final Provider	PROVIDER = new OracleProvider();

	public static final SQL		SQL	 = new SQL(PROVIDER);

	private static final DataBase	DB	 = new OracleClient("orcl", "test", "TEST");

	public static SQLKit getSQLKit()
	{
		try
		{
			return DB.getSQLKit();
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			return null;
		}
	}
}

public class COMP extends AbstractTable
{
	@NameMeta(name = "COMP_ID")
	@DataMeta(alias = "compId")
	@PrimaryKeyMeta(ordinal = 1)
	public Column	COMP_ID;

	@NameMeta(name = "COM_NAME")
	@DataMeta(alias = "compName")
	public Column	COM_NAME;
}

@EntityMeta(entity = COMP.class)
public class Company
{
	@DataMeta(alias = "compId")
	private String					id;

	@DataMeta(alias = "compName")
	private String					name;

	@OneToManyMeta(model = Department.class, key = DEPT.FK_DEPT, referred = false)
	private Collection<Department>	departments;

	/* 省略了部分getter与setter */
}

public class DEPT extends AbstractTable
{
	@ForeignKeyMeta
	public static final String	FK_DEPT	= "FK_DEPT";

	@NameMeta(name = "COMP_ID")
	@DataMeta(alias = "compId")
	@PrimaryKeyMeta(ordinal = 1)
	public Column	COMP_ID;

	@NameMeta(name = "DEPT_ID")
	@DataMeta(alias = "deptId")
	@PrimaryKeyMeta(ordinal = 2)
	public Column	DEPT_ID;

	@NameMeta(name = "DEPT_NAME")
	@DataMeta(alias = "deptName")
	public Column	DEPT_NAME;

	@ForeignKeyMeta
	public ForeignKey FK_DEPT(COMP ref)
	{
		return foreignKey(ref, COMP_ID);
	}
}

@EntityMeta(entity = DEPT.class)
public class Department
{
	@ManyToOneMeta(model = Company.class, key = DEPT.FK_DEPT)
	private Company				company;

	@DataMeta(alias = "compId", serialize = false)
	private String				compId;

	private String				compName;

	@DataMeta(alias = "deptId")
	private String				id;

	@DataMeta(alias = "deptName")
	private String				name;

	@OneToManyMeta(model = Staff.class, key = STAF.FK_STAF, referred = false)
	private Collection<Staff>	staffs;

	/* 省略了部分getter与setter */
}

public class STAF extends AbstractTable
{
	@ForeignKeyMeta
	public static final String	FK_STAF	= "FK_STAF";

	@NameMeta(name = "COMP_ID")
	@DataMeta(alias = "compId")
	@PrimaryKeyMeta(ordinal = 1)
	public Column	COMP_ID;

	@NameMeta(name = "DEPT_ID")
	@DataMeta(alias = "deptId")
	@PrimaryKeyMeta(ordinal = 2)
	public Column	DEPT_ID;

	@NameMeta(name = "STAF_ID")
	@DataMeta(alias = "stafId")
	@PrimaryKeyMeta(ordinal = 3)
	public Column	STAF_ID;

	@NameMeta(name = "STAF_NAME")
	@DataMeta(alias = "stafName")
	public Column	STAF_NAME;

	@NameMeta(name = "STAF_SALARY")
	@DataMeta(alias = "stafSalary")
	public Column	STAF_SALARY;

	@ForeignKeyMeta
	public ForeignKey FK_STAF(DEPT ref)
	{
		return foreignKey(ref, COMP_ID, DEPT_ID);
	}
}

@EntityMeta(entity = STAF.class)
public class Staff
{
	@ManyToOneMeta(model = Department.class, key = STAF.FK_STAF)
	private Department	department;

	@DataMeta(alias = "compId", serialize = false)
	private String		compId;

	@DataMeta(alias = "deptId", serialize = false)
	private String		deptId;

	@DataMeta(alias = "stafId")
	private String		id;

	@DataMeta(alias = "stafName")
	private String		name;

	@DataMeta(alias = "stafSalary")
	private Double		salary;

	/* 省略了部分getter与setter */
}

执行查询

Company company = Entitys.selectObject(Config.getSQLKit(), Config.SQL, Company.class,
		  new JSON().attr("compId", "1"));

System.out.println(company);

输出

Company: id=1, name=one co. ltd.
[Departments]
  Depart: id=11, name=dept. 11, compId=1, compName=one co. ltd.
  [Staffs]
    Staff: id=a11, name=helen, salary=219.5
    Staff: id=a12, name=john, salary=265.98
  Depart: id=12, name=dept. 12, compId=1, compName=one co. ltd.
  [Staffs]
    Staff: id=a21, name=mike, salary=375.4

预定义视图

public class DemoPreclaredView extends PredeclaredView
{
  public COMP  c;

  public DEPT  d;

  @Override
  public JSON parameters()
  {
    return null;
  }

  @Override
  protected Select select(SQL sql)
  {
    return sql.from(c = sql.view(COMP.class).as("c"))
        .innerJoin(d = sql.view(DEPT.class).as("d"), d.FK_DEPT(c)) //
        .select(c.COMP_ID.as("compId"), //
                c.COM_NAME.as("compName"), //
                d.DEPT_ID.as("id"), //
                d.DEPT_NAME.as("name"));
  }
}

复用预定义视图，生成相似的查询语句

DemoPreclaredView v = Config.SQL.view(DemoPreclaredView.class);

println(v.select().where(v.c.COMP_ID.eq(Config.SQL.param("compId"))));

println(v.select().where(v.d.DEPT_ID.eq(Config.SQL.param("deptId"))).orderBy(v.d.DEPT_NAME));

输出

SELECT "c"."COMP_ID"   "compId",
       "c"."COM_NAME"  "compName",
       "d"."DEPT_ID"   "id",
       "d"."DEPT_NAME" "name"
  FROM "COMP" "c"
 INNER JOIN "DEPT" "d"
    ON "d"."COMP_ID" = "c"."COMP_ID"
 WHERE "c"."COMP_ID" = ?compId?
 
SELECT "c"."COMP_ID"   "compId",
       "c"."COM_NAME"  "compName",
       "d"."DEPT_ID"   "id",
       "d"."DEPT_NAME" "name"
  FROM "COMP" "c"
 INNER JOIN "DEPT" "d"
    ON "d"."COMP_ID" = "c"."COMP_ID"
 WHERE "d"."DEPT_ID" = ?deptId?
 ORDER BY "d"."DEPT_NAME" ASC