PetrN
March 14, 2024, 1:06pm
1
on @Entity class called “ParentEntity” which has
@SQLRestriction("xyz IS NOT NULL")private Set<ChildEntity> children = new HashSet<>();
when I use to find the parent and its childrenrepository.findBySomeKey(key)
and ( or1_0.xyz IS NOT NULL )
when I use entityGraph like so:var eg = getEntityGraphForNodes("children"); CriteriaBuilder criteriaBuilder = this.getEntityManager().getCriteriaBuilder(); CriteriaQuery<ParentEntity> criteriaQuery = criteriaBuilder.createQuery(this.getPojoClass()); Root<ParentEntity> root = criteriaQuery.from(this.getPojoClass()); Predicate predicate = criteriaBuilder.equal(root.get("someKey"), value); criteriaQuery.where(predicate); return this.getEntityManager() .createQuery(criteriaQuery) .setHint("javax.persistence.fetchgraph", eg) .getSingleResult();
then I get following SQL condition:left join child or1_0 on ure1_0.id=or1_0.user_id and (or1_0.xyz IS NOT NULL) and (or1_0.xyz IS NOT NULL)
There is a similar issue discussed on github:
opened 10:10PM - 09 Feb 24 UTC
closed 07:03AM - 26 Feb 24 UTC
I found an error while fetching with an `@EntityGraph` a list of entity and thei… r child.
The issue is the repetition of the where clause of the child entity in the wrong place of the query.
When I put the `@SQLRestriction` or the deprecated `@Where` annotation on a child entity and I fetch it from the father I can see 2 issues:
1. the condition is placed in the "ON" statement of the join and not in the where condition
2. the condition is repeated two times
This is the query I would expect to see:
```SQL
select
fe1_0.id,
fe1_0.father_field_1,
fe1_0.father_field_2,
fe1_0.father_flag_exist,
se1_0.father_id,
se1_0.id,
se1_0.son_field_1,
se1_0.son_flag_exist
from
father fe1_0
left join
son se1_0
on fe1_0.id=se1_0.father_id
where
(
fe1_0.father_flag_exist=FALSE
)
and fe1_0.father_field_1 is not null
and (se1_0.son_flag_exist=FALSE)
```
this is the query generated:
```SQL
select
fe1_0.id,
fe1_0.father_field_1,
fe1_0.father_field_2,
fe1_0.father_flag_exist,
se1_0.father_id,
se1_0.id,
se1_0.son_field_1,
se1_0.son_flag_exist
from
father fe1_0
left join
son se1_0
on fe1_0.id=se1_0.father_id
and (se1_0.son_flag_exist=FALSE)
and (se1_0.son_flag_exist=FALSE)
where
(
fe1_0.father_flag_exist=FALSE
)
and fe1_0.father_field_1 is not null
```
To reproduce the error I leave here two entities, the associated repository, a junit test and the application.yml.
I used springboot 3.2.2 and springboot 2.7.18 and the issue is still the same.
```java
@Data
@EqualsAndHashCode(exclude = "sonEntities")
@ToString
@AllArgsConstructor
@NoArgsConstructor
@Builder
@Entity
@Table(name = "FATHER")
@SQLRestriction(value = "father_flag_exist=FALSE")
public class FatherEntity {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(name = "father_field_1")
private String fatherField1;
@Column(name = "father_field_2")
private String fatherField2;
@Column(name = "father_flag_exist")
private Boolean fatherFlagExist;
@OneToMany(mappedBy = "fatherEntity")
@ToString.Exclude
private Set<SonEntity> sonEntities;
}
```
```java
@Data
@EqualsAndHashCode(exclude = "fatherEntity")
@ToString
@AllArgsConstructor
@NoArgsConstructor
@Builder
@Entity
@Table(name = "SON")
@SQLRestriction(value = "son_flag_exist=FALSE")
public class SonEntity {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(name = "son_field_1")
private String sonField1;
@Column(name = "son_flag_exist")
private Boolean sonFlagExist;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "father_id", referencedColumnName = "id")
@ToString.Exclude
private FatherEntity fatherEntity;
}
```
```java
@Repository
public interface FatherRepository extends JpaRepository<FatherEntity, Long> {
@EntityGraph(type = EntityGraph.EntityGraphType.FETCH, attributePaths = "sonEntities")
List<FatherEntity> findByFatherField1IsNotNull();
List<FatherEntity> findFatherByFatherField1IsNotNull();
}
```
```java
@SpringBootTest
@Slf4j
class RepositoryTest {
@Autowired
private FatherRepository fatherRepository;
@Test
void repositoryFetch(){
log.info("START TEST");
List<FatherEntity> fatherEntityList1 = fatherRepository.findByFatherField1IsNotNull();
log.info("Result from first query: \n{}", fatherEntityList1);
List<FatherEntity> fatherEntityList2 = fatherRepository.findFatherByFatherField1IsNotNull();
log.info("Result from second query: \n{}", fatherEntityList2);
log.info("END");
}
}
```
And this is the application.yml with the configuration:
```yml
spring:
application:
name: test-app
datasource:
url: jdbc:h2:mem:mydb
driverClassName: org.h2.Driver
jpa:
show-sql: false
properties:
hibernate:
format_sql: true
logging:
level:
org:
hibernate:
SQL: debug
orm:
jdbc:
bind: trace
```
1 Like
beikov
March 14, 2024, 3:09pm
2
Could be a bug. Please try to create a reproducer with our test case template and if you are able to reproduce the issue, create a bug ticket in our issue tracker and attach that reproducer.
PetrN
March 15, 2024, 6:41am
3
1 Like