两个表：
CREATE TABLE `foo` (
`foo_id` bigint(20) not null auto_increment,
`name` varchar(32) not null,
`_deleted_` tinyint(1) default '0',
PRIMARY KEY (`foo_id`)
) ;
CREATE TABLE `bar` (
`bar_id` bigint(20) not null auto_increment,
`foo_id` bigint(20) not null,
`key` varchar(32) not null,
`value` varchar(125) not null,
`_deleted_` tinyint(1) default '0',
PRIMARY KEY (`bar_id`)
);
目录内容：
select * from foo;
+--------+-------+-----------+
| foo_id | name | _deleted_ |
+--------+-------+-----------+
| 1 | cat | 0 |
| 2 | dog | 0 |
| 3 | mouse | 0 |
| 4 | rat | 1 |
+--------+-------+-----------+
3 rows in set (0.00 sec)
select * from bar;
+--------+--------+-------+--------+-----------+
| bar_id | foo_id | key | value | _deleted_ |
+--------+--------+-------+--------+-----------+
| 1 | 1 | sound | meow | 0 |
| 2 | 1 | ears | pointy | 0 |
| 3 | 2 | sound | ruff | 0 |
| 4 | 2 | nose | long | 0 |
| 5 | 3 | sound | squeak | 0 |
| 6 | 3 | tail | long | 0 |
| 7 | 3 | legs | two | 1 |
+--------+--------+-------+--------+-----------+
6 rows in set (0.00 sec)
我要创建的查询：
select f.foo_id, f.name, b.key, b.value from foo f, bar b
where f.foo_id = b.foo_id and f._deleted_ = 0 and b._deleted_ = 0;
+--------+-------+-------+--------+
| foo_id | name | key | value |
+--------+-------+-------+--------+
| 1 | cat | sound | meow |
| 1 | cat | ears | pointy |
| 2 | dog | sound | ruff |
| 2 | dog | nose | long |
| 3 | mouse | sound | squeak |
| 3 | mouse | tail | long |
+--------+-------+-------+--------+
6 rows in set (0.01 sec)
福类：
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode(callSuper = false)
@Entity(name = "foo")
public class Foo {
@Id
@Column(name = "foo_id", nullable = false, unique = true, columnDefinition = "bigint(20)")
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long fooId;
private String name;
@Column(name = "_deleted_")
private Short deleted;
@OneToMany
@JoinTable(name="bar",
joinColumns=@JoinColumn(name="foo_id"))
private List<Bar> bars;
}
酒吧等级：
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode(callSuper = false)
@Entity(name = "bar")
public class Bar {
@Id
@Column(name = "bar_id", nullable = false, unique = true, columnDefinition = "bigint(20)")
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long barId;
private Long fooId;
private String key;
private String value;
@Column(name = "_deleted_")
private Short deleted;
}
尝试加入他们：
protected Stream<Foo> getFoosWithBars() {
return this.jpaApi.withTransaction(entityManager -> {
final CriteriaBuilder builder = entityManager.getCriteriaBuilder();
final CriteriaQuery<Foo> criteria = builder.createQuery(Foo.class);
Root<Foo> fromFoo = criteria.from(Foo.class);
Join<Foo, Bar> foobars = fromFoo.join("fooId");
List<Predicate> conditions = new ArrayList();
conditions.add(builder.notEqual(fromFoo.get("deleted"), 1));
# what goes here?
conditions.add(builder.notEqual(???Bar???.get("deleted"), 1));
TypedQuery<Foo> typedQuery = entityManager.createQuery(criteria
.select(fromFoo)
.where(conditions.toArray(new Predicate[] {})));
return typedQuery.getResultList().stream();
});
}
我坚信问题在于您的实体映射。 使用不正确的模型，您将很难生成正确的查询。
让我们看看 ddl 从初始代码中生成的内容：org.hibernate.DuplicateMappingException: Table [bar] contains physical column name [foo_id] referred to by multiple physical column names: [foo_id], [fooId]
让我们尝试更正它：
@Column(name = "foo_id")
private Long fooId;
现在生成以下 ddl：
create table foo (foo_id bigint(20) generated by default as identity,
_deleted_ smallint,
name varchar(255),
primary key (foo_id))
create table bar (bar_id bigint(20) generated by default as identity,
_deleted_ smallint,
foo_id bigint,
key varchar(255),
value varchar(255),
bars_bar_id bigint(20) not null,
primary key (bar_id))
问题所在
bars_bar_id是你@JoinTable的结果，会有问题。
在另一个答案中提出的查询，使用Join<Foo, Bar> foobars = (Join<Foo, Bar>) fromFoo.fetch("fooId");
失败并显示hibernate.jpa.criteria.BasicPathUsageException: Cannot join to attribute of basic type查看提示，说明您需要正确映射的关联才能进行联接
请注意，仅更改：
@Column(name = "foo_id")
private Long fooId;
自
@ManyToOne
@JoinColumn(name = "foo_id")
Foo foo;
是不够的：从 foo 到 bar 的任何 a 都会导致 SQL 中的 2 个连接(如前所述，意外字段上的 FKbars_bar_id)：
final CriteriaBuilder builder = em.getCriteriaBuilder();
final CriteriaQuery<Foo> criteria = builder.createQuery(Foo.class);
Root<Foo> fromFoo = criteria.from(Foo.class);
Join<Foo, Bar> foobars = (Join) fromFoo.fetch("bars");
select
foo0_.foo_id as foo_id1_2_0_,
bar2_.bar_id as bar_id1_1_1_,
foo0_._deleted_ as _deleted2_2_0_,
foo0_.name as name3_2_0_,
bar2_._deleted_ as _deleted2_1_1_,
bar2_.foo_id as foo_id3_1_1_,
bar2_.key as key4_1_1_,
bar2_.value as value5_1_1_,
bars1_.foo_id as foo_id3_1_0__,
bars1_.bars_bar_id as bars_bar6_1_0__
from
foo foo0_
inner join
bar bars1_
on foo0_.foo_id=bars1_.foo_id
inner join
bar bar2_
on bars1_.bars_bar_id=bar2_.bar_id
正确的映射
映射与 JPA 和 Hibernate 的@OneToMany关系的最佳方式
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode(callSuper = false)
@Entity(name = "foo")
public class Foo {
@Id
@Column(name = "foo_id", nullable = false, unique = true, columnDefinition = "bigint(20)")
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long fooId;
private String name;
@Column(name = "_deleted_")
private Short deleted;
@OneToMany(mappedBy = "foo")
private List<Bar> bars;
}
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode(callSuper = false)
@Entity(name = "bar")
public class Bar {
@Id
@Column(name = "bar_id", nullable = false, unique = true, columnDefinition = "bigint(20)")
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long barId;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "foo_id")
Foo foo;
private String key;
private String value;
@Column(name = "_deleted_")
private Short deleted;
}
条件查询
CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<Foo> criteria = builder.createQuery(Foo.class);
Root<Foo> fromFoo = criteria.from(Foo.class);
Join<Foo, Bar> foobars = (Join) fromFoo.fetch("bars");
List<Predicate> conditions = new ArrayList<>();
conditions.add(builder.equal(fromFoo.get("deleted"), 0));
conditions.add(builder.equal(foobars.get("deleted"), 0));
TypedQuery<Foo> typedQuery = entityManager.createQuery(
criteria.select(fromFoo)
.where(conditions.toArray(new Predicate[]{})));
生成的 SQL
select
foo0_.foo_id as foo_id1_2_0_,
bars1_.bar_id as bar_id1_1_1_,
foo0_._deleted_ as _deleted2_2_0_,
foo0_.name as name3_2_0_,
bars1_._deleted_ as _deleted2_1_1_,
bars1_.foo_id as foo_id5_1_1_,
bars1_.key as key3_1_1_,
bars1_.value as value4_1_1_,
bars1_.foo_id as foo_id5_1_0__,
bars1_.bar_id as bar_id1_1_0__
from
foo foo0_
inner join
bar bars1_
on foo0_.foo_id=bars1_.foo_id
where
foo0_._deleted_=0
and bars1_._deleted_=0
此 JPA 查询中缺少表的连接条件，并使用连接对象来匹配Bar对象的条件。看看这个查询，如果你有任何进一步的查询，告诉我。
protected Stream<Foo> getFoosWithBars() {
return this.jpaApi.withTransaction(entityManager -> {
final CriteriaBuilder builder = entityManager.getCriteriaBuilder();
final CriteriaQuery<Foo> criteria = builder.createQuery(Foo.class);
Root<Foo> fromFoo = criteria.from(Foo.class);
Join<Foo, Bar> foobars = (Join<Foo, Bar>) fromFoo.fetch("fooId");
List<Predicate> conditions = new ArrayList();
conditions.add(builder.equal(fromFoo.get("fooId"),foobars.get("fooId"))); // You are missing join Condition
conditions.add(builder.equal(fromFoo.get("deleted"), 0));
conditions.add(builder.equal(foobars.get("deleted"), 0));
TypedQuery<Pod> typedQuery = entityManager.createQuery(criteria.select(fromFoo)
.where(conditions.toArray(new Predicate[] {})));
return typedQuery.getResultList().stream();
});
}