使用QueryDsl 需要注意的事项:
使用场景:
当一个部门表中需要存入一个上级部门名称时,使用自连接的写法如下:
定义一个Q类,tableAlias -->表的别名(特别重要,如果不使用别名,那么上级部门名称一直存入的时当前部门名称)
public class QDepartment extends EnhancedRelationalPathBase<QDepartment> {
public static final QDepartment DEPARTMENT = new QDepartment();
public final NumberPath<Integer> ID = createIntegerCol("ID").asPrimaryKey().build();
public final StringPath DEPT_NO = createStringCol("DEPT_NO").build();
public final StringPath DEPT_NAME = createStringCol("DEPT_NAME").build();
public final NumberPath<Integer> PARENT_DEPT_ID = createIntegerCol("PARENT_DEPT_ID").build();
public final StringPath SEQUENCE = createStringCol("SEQUENCE").build();
public final StringPath DEPT_ASSIGNEE_ID = createStringCol("DEPT_ASSIGNEE_ID").build();
public final StringPath ROOT_DEPARTMENT_ID = createStringCol("ROOT_DEPARTMENT_ID").build();
public final BooleanPath DELETED = createBooleanCol("DELETED").build();
public QDepartment() {
super(QDepartment.class, "AO_67ABB4_DEPARTMENT");
}
public QDepartment(String tableAlias){
super(QDepartment.class, "AO_67ABB4_DEPARTMENT",tableAlias);
}
}
QueryDsl 的部门表自连接 的写法如下:
public List<DepartmentBean> findAllDeptByDeptNameLikeInDept(String name) {
//设置表别名
QDepartment qDepartment = new QDepartment("qDepartment");
QDepartment qDepartment1 = new QDepartment("qDepartment1");
return databaseAccessor.run(connection -> {
return connection.query().select(Projections.bean(DepartmentBean.class,
qDepartment.DEPT_NAME.as("deptName"),
qDepartment.ID.as("id"),
qDepartment.PARENT_DEPT_ID.as("parentDeptId"),
qDepartment.DEPT_NO.as("deptNo"),
qDepartment1.DEPT_NAME.as("parentDeptName")
))
.from(qDepartment).leftJoin(qDepartment1).on(qDepartment.PARENT_DEPT_ID.eq(qDepartment1.ID)).where(
qDepartment.DEPT_NAME.upper().like("%" + name.toUpperCase().replaceAll("_", "\\\\" + "_") + "%")
.and(qDepartment.DELETED.isFalse())
).limit(10).fetch();
}) ;
}