有两个实体Person和Address。人与地址之间存在1:M关系。(假定某人有临时和永久地址)。
Person类的关键属性是:
地址类的主要属性是:
以下是Person和Address类的描述符代码片段:
public RelationalDescriptor buildPersonDescriptor() {
RelationalDescriptor descriptor = new RelationalDescriptor();
descriptor.setJavaClass(Person.class);
descriptor.addTableName("PERSON");
descriptor.addPrimaryKeyFieldName("PERSON.PID");
// RelationalDescriptor properties.
descriptor.useSoftCacheWeakIdentityMap();
descriptor.setIdentityMapSize(100);
descriptor.useRemoteSoftCacheWeakIdentityMap();
descriptor.setRemoteIdentityMapSize(100);
descriptor.setSequenceNumberFieldName("PERSON.PID");
descriptor.setSequenceNumberName("PERSON_SEQ");
descriptor.setAlias("person");
// Query manager.
descriptor.getDescriptorQueryManager().checkCacheForDoesExist();
descriptor.getDescriptorQueryManager().setAdditionalJoinExpression(new ExpressionBuilder().get("gender").equal('N'));
// Query manager.
// Mappings.
DirectToFieldMapping pIDMapping = new DirectToFieldMapping();
pIDMapping.setAttributeName("personId");
pIDMapping.setFieldName("PERSON.PID");
descriptor.addMapping(pIDMapping);
DirectToFieldMapping genderMapping = new DirectToFieldMapping();
genderMapping.setAttributeName("gender");
genderMapping.setFieldName("PERSON.GENDER");
descriptor.addMapping(genderMapping);
OneToManyMapping addressMapping = new OneToManyMapping();
addressMapping.setAttributeName("address");
addressMapping.setReferenceClass(Address.class);
addressMapping.useTransparentCollection();
addressMapping.useCollectionClass(IndirectList.class);
addressMapping.addTargetForeignKeyFieldName("ADDRESS.PID", "PERSON.PID");
descriptor.addMapping(addressMapping);
return descriptor;
}
public RelationalDescriptor buildAddressDescriptor() {
RelationalDescriptor descriptor = new RelationalDescriptor();
descriptor.setJavaClass(com.tropics.application.products.domain.costingandpricing.SellingPriceAddOn.class);
descriptor.addTableName("ADDRESS");
descriptor.addPrimaryKeyFieldName("ADDRESS.AID");
// Descriptor properties.
descriptor.useSoftCacheWeakIdentityMap();
descriptor.setIdentityMapSize(100);
descriptor.useRemoteSoftCacheWeakIdentityMap();
descriptor.setRemoteIdentityMapSize(100);
descriptor.setSequenceNumberFieldName("ADDRESS.AID");
descriptor.setSequenceNumberName("ADDRESS_SEQ");
descriptor.setAlias("address");
// Query manager.
descriptor.getDescriptorQueryManager().checkCacheForDoesExist();
//Mappings
DirectToFieldMapping genderMapping = new DirectToFieldMapping();
genderMapping.setAttributeName("gender");
genderMapping.setFieldName("ADDRESS.GENDER");
descriptor.addMapping(genderMapping);
DirectToFieldMapping personIDMapping = new DirectToFieldMapping();
personIDMapping.setAttributeName("personId");
personIDMapping.setFieldName("ADDRESS.PID");
descriptor.addMapping(personIDMapping);
DirectToFieldMapping addressIDMapping = new DirectToFieldMapping();
addressIDMapping.setAttributeName("addressId");
addressIDMapping.setFieldName("ADDRESS.AID");
descriptor.addMapping(addressIDMapping);
}
以下是用于生成动态查询的代码片段:
ExpressionBuilder expBuilder = new ExpressionBuilder();
ReportQuery query = new ReportQuery(Person.class, expBuilder);
//Getting the MVSelling DetailsID and the number of Selling price add ons for each of them
query.addAttribute("personId", expBuilder.get("personId"));
query.addAttribute
("addressCounter", expBuilder.anyOfAllowingNone("address").get("addressId").count());
Expression addressExp = expBuilder.anyOfAllowingNone("address");
expBuilder.leftJoin(addressExp, addressExp.get("gender").equal('M'));
query.addNonFetchJoin(addressExp);
query.addGrouping("personId");
resultCollection = (Vector)clientSessionHolder.eclipselinkClientSession().executeQuery(query);
运行此程序时,根据日志生成的查询:
SELECT t0.PID, COUNT(t1.AID)
FROM PERSON t0 LEFT OUTER JOIN ADDRESS t1
ON (t1.PID = t0.PID)
LEFT OUTER JOIN ADDRESS t2
ON ((t2.PID = t0.PID)
AND (t2.gender = 'M'))
WHERE (t0.gender = 'M')) GROUP BY t0.PID ;
如何编写表达式在第一个join子句中添加性别条件(db中的char数据类型)并去掉第二个join子句?
预期的查询是:SELECT t0。PID,计数(t1。AID)从人t0在(t1)离开外部连接地址t1。PID = t0。PID和(t2.gender = 'M '),其中t0.gender = 'M '按t0分组。PID
您有两个单独的联接,因为您正在调用和使用expBuilder。anyOfAllowingNone(“地址”)在表达式中出现两次anyOfAllowingNone
告诉EclipseLink在关系上创建一个外部联接,并将其用作从该联接构建的表达式的基础。
试试吧
Expression addressExp = expBuilder.anyOfAllowingNone("address");
query.addAttribute("addressCounter", addressExp.get("addressId").count());
expBuilder.leftJoin(addressExp, addressExp.get("gender").equal('M'));
query.addNonFetchJoin(addressExp);
重复使用addressExp将导致连接只创建一次,其他路径将基于它而不是新的路径。
LEFT OUTER JOIN 左外连接 [ ] 需求:查询所有分类,如果该分类下没有商品,则不显示该分类 [ ] 实现: SELECT `goods`.`id`, `goods`.`title`, `goods`.`price`, `goods`.`cate_id`, `cate`.`id`, `cate`, `cate.title` F
MariaDB 用于返回条件中指定的左侧表中的所有行,并仅返回满足连接条件的其他表中的行。 也被称为。 语法: 图形表示如下: 注: 上图中,两个图形的左侧表(table1)和右侧表(table2)中间交叉蓝色部分,以及左侧表(table1)就是连接返回的结果集。 为了方便演示,我们需要创建两个表,并插入一些数据 - 插入数据 - 当前表中的行记录如下 - 当前表中的行记录如下 - 示例1 使用以
我开始学习JPA,并基于我在SQL Server中测试的以下本机SQL实现了一个使用JPA查询的示例: 根据上面的SQL,我构造了以下JPQL查询: 正如您所看到的,我仍然缺少原始查询中的条件。我的问题是,我怎样才能把它放入我的JPQL中?
我有两个联合查询,如下所示: 现在,我想在另一个查询中使用此联合。 当我运行它时,我得到ORA-00904:"P"."CUSTOMER_NO":无效的标识符。我需要将h1.customer_no加入到外部查询customer_no。 我看到过一些带有rank的查询,但我不太明白。如何将内部查询与外部查询连接起来? 提前感谢。
问题内容: 我有一个Hibernate的服务方法,例如:。securityId2由用户传递。每个SecurityContact与一个Contact有多对一的关系,因此Hibernate在运行此查询时会自动调用联接。但是,Hibernate始终运行的联接是内部联接,因此无法正常运行。有没有办法强迫Hibernate在内部生成左外部联接?这是SecurityContact类的代码: 问题答案: 尝试多
我需要从中选择所有行,如果选择位置子句匹配,则从中选择匹配这是我的外部与子查询,但它失败了。有人可以帮忙吗?