Spring Data JPA @Query not working (findAll + filter works)

2021-02-22 22:28 jandost imported from Stackoverflow

I have a custom @Query which returns 0 results when I try to filter data using HQL (should return 1 result)

@Query("SELECT ...
FROM ...
WHERE ...")
Set<SomeObject> find...(@Param("param") String param,...)

but when I use repository.findAll() and filter the data using streams in Java I get the result I need. FYI I filter the data using exactly the same conditions as in the query.

Any ideas why this happens?

See also questions close to this topic

Java JPA: How to use multiple DB-users?

I think about using JPA for a webapp. But every user should have its own DB-user/schema. Data of every single user should be private and hidden from all others.

As far I see I need to give JPA a single DB connection (including a user).

Is it possible to use different DB-users in one application?
Hibernate and Criteria Api generates wrong Join condition
I got following tables. Lets ignore the fact that the relation is done wrong here. I cannot change that. Each company can have multiple employes and each employe belongs to only one company.

Table: Company

ID EMPLOYE_ID

10 100

Table: Employe

ID NAME

100 (Same as EMPLOYE_ID) John

Now i want to create a relation @OneToMany between Company -> Employe . My entities look as follow
```
class Company {
  @Id
  @Column(name = "id", unique = true, nullable = false)
  private String id;

  @OneToMany(fetch = FetchType.LAZY)
  @JoinColumn(name = "EMPLOYE_ID", referencedColumnName = "ID")
  private Set<Employe> employees;
  
}
```
No matter if i try to create a uniderectional, or biderection relationship by adding also @ManyToOne on my Employe class, when using Criteria api to select all Company entities and their Employes i always end up with a wrong generated SQL query at the point where it joines the tables. The above relation for example creates following:
```
FROM company company0
INNER JOIN employe employe0 ON company0.id = employe0.employe_id
```
I tried several approaches, but i end up almost with the same error. It tries either to access a column which does not exist on the table, or joins wrong columns (e.g. id = id). Or by the following exception

Caused by: org.hibernate.MappingException: Repeated column in mapping for entity: com.Employe column: id (should be mapped with insert="false" update="false")"}}

What is a simple approach to create a bidrectional relation with the above table structure?
Named query in spring repository gives error on mysql DATE function - (', <expression>, FUNCTION or identifier expected, got '('
I have the following query in a spring boot repository:
```
    @Query("SELECT " +
            "    new com.vw.asa.Response.Dashboard.Charts.OverallStatus(gm.status, COUNT(gm)) " +
            "FROM " +
            "    GenMeasures gm " +
            "WHERE gm.status IN ('OPEN', 'RESOLVED', 'CLOSED', 'IN_PROGRESS', 'RISK_ACCEPTED') AND gm.dueDate < DATE(NOW()) OR gm.dueDate IS NULL " +
            "GROUP BY " +
            "    gm.status")
    List<OverallStatus> getOverallStatus();
```
Running this query in the equivalent manner in MySQL works fine, however - when writing this query here, it returns the error: <expression>, <operator>, GROUP, HAVING or ORDER expected, got '(' at the ( after the DATE function.

This error also occurred when I placed the ( after the WHERE in an effort to group parts of the clause. What is causing this error?

EDIT

Removing the gm.dueDate < DATE(NOW()) OR piece of the clause seems to remove the error. Why does it have a problem with the DATE() function?

ID	EMPLOYE_ID
10	100

ID	NAME
100 (Same as EMPLOYE_ID)	John

JPA Annotations to relate each other

I am designing a microservice application with spring boot,jpa and h2.I have 3 entities Employee,Department and Organization in one of my service.I already annotated relationship for the List and List in Organization and Department table against private Department department; and private Organization organization; in Employee table as shown below.But I'm getting below exception.Could you please tell me how to resolve it.

@Data
@Entity
class Employee{
    public Employee(String name,int age,String email,String designation) {
        this.name=name;
        this.age=age;
        this.email=email;
        this.designation=designation;
        
        
    }
    @Id
    private long id;
    private String name;
    private int age;
    private String email;
    private String designation;
    @ManyToOne
    @JoinColumn(name = "departmentId")
    private Department department;
    @ManyToOne
    @JoinColumn(name = "organizationId")
    private Organization organization;
}

@Data
@Entity
class Department{
    public Department(long organizationId,String name) {
        this.organizationId=organizationId;
        this.name=name;
    }
    
    @Id
    private long id;
    private Long organizationId;
    private String name;
    @OneToMany(mappedBy = "department")
    private List<Employee> employees = new ArrayList<>();
}

@Data
@Entity
class Organization{
    public Organization(String name,String address) {
        this.name=name;
        this.address=address;
    }
    
    @Id 
    @GeneratedValue
    private long id;
    
    private String name;
    private String address;
    @OneToMany(mappedBy = "department")
    private List<Employee> employees = new ArrayList<>();
    @OneToMany(mappedBy = "organization")
    private List<Department> departments = new ArrayList<>();

}

Exception

Caused by: org.hibernate.AnnotationException: mappedBy reference an unknown target entity property: com.example.demo.Department.organization in com.example.demo.Organization.departments
    at org.hibernate.cfg.annotations.CollectionBinder.bindStarToManySecondPass(CollectionBinder.java:848) ~[hibernate-core-5.4.28.Final.jar:5.4.28.Final]
    at org.hibernate.cfg.annotations.CollectionBinder$1.secondPass(CollectionBinder.java:799) ~[hibernate-core-5.4.28.Final.jar:5.4.28.Final]
    at org.hibernate.cfg.CollectionSecondPass.doSecondPass(CollectionSecondPass.java:53) ~[hibernate-core-5.4.28.Final.jar:5.4.28.Final]
    at org.hibernate.boot.internal.InFlightMetadataCollectorImpl.processSecondPasses(InFlightMetadataCollectorImpl.java:1693) ~[hibernate-core-5.4.28.Final.jar:5.4.28.Final]
    at org.hibernate.boot.internal.InFlightMetadataCollectorImpl.processSecondPasses(InFlightMetadataCollectorImpl.java:1661) ~[hibernate-core-5.4.28.Final.jar:5.4.28.Final]
    at org.hibernate.boot.model.process.spi.MetadataBuildingProcess.complete(MetadataBuildingProcess.java:295) ~[hibernate-core-5.4.28.Final.jar:5.4.28.Final]
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.metadata(EntityManagerFactoryBuilderImpl.java:1224) ~[hibernate-core-5.4.28.Final.jar:5.4.28.Final]
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:1255) ~[hibernate-core-5.4.28.Final.jar:5.4.28.Final]
    at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:58) ~[spring-orm-5.2.13.RELEASE.jar:5.2.13.RELEASE]
    at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:365) ~[spring-orm-5.2.13.RELEASE.jar:5.2.13.RELEASE]
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:391) ~[spring-orm-5.2.13.RELEASE.jar:5.2.13.RELEASE]
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:378) ~[spring-orm-5.2.13.RELEASE.jar:5.2.13.RELEASE]
    at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.afterPropertiesSet(LocalContainerEntityManagerFactoryBean.java:341) ~[spring-orm-5.2.13.RELEASE.jar:5.2.13.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1858) ~[spring-beans-5.2.13.RELEASE.jar:5.2.13.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1795) ~[spring-beans-5.2.13.RELEASE.jar:5.2.13.RELEASE]
    ... 22 common frames omitted

JPA and Hibernate One To One Shared Primary Key Uni-directional Mapping in Spring Boot

I want to have one-to-one uni-directional mapping with 2 child entities using shared primary key. Below are model classes

public class Template implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "templatekey")
    Integer templateKey;

    @Column(name = "templateid", unique = true)
    String templateId;
    
    @OneToOne(cascade = CascadeType.ALL, optional = false)
    @PrimaryKeyJoinColumn(name = "templatekey", referencedColumnName = "templatekey")
    InstantOfferNoEsp instantOfferNoEsp;

    @OneToOne(cascade = CascadeType.ALL, optional = false)
    @PrimaryKeyJoinColumn(name = "templatekey", referencedColumnName = "templatekey")
    Mobile mobile;

     //constructor , setter and getters

}

Child 1 :

public class Mobile implements Serializable {
   
    private static final long serialVersionUID = 1L;

    @Id
    @Column(name = "templatekey")
    Integer templateKey;
    
    String mobileNewUrl;


    //constructor , setter and getters

}

Child 2:

public class InstantOfferNoEsp {

    @Id
    @Column(name = "templatekey")
    Integer templateKey;

    String offerCodeType;

    String headerUrl;
 
    //constructor , setter and getters
}

I want templateKey as PK in all tables. and I am calling templateRepository.save(template); to save all entities at once but its not working and getting ids for this class must be manually assigned before calling save() error.

Any suggestions would be of great help. Thank you.

Kotlin data class with spring jpa

I have read the spring guide with kotlin and its says data class for JPA is not recommended

but i am quite confused after seeing some tutorials and video using data class for JPA

did spring find a way to deal with data class in new versions?

Spring Data. Delete child entity on deleting one of the parents

I need to delete my child entity if one of the parent are being removed.

Right now child entity will be removed if both parents are removed and I need to change the behaviour.

For example if you have:

class Parent1 {
    @Id
    Long id;
    
    @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
    Set<Child> children = new HashSet();
}

class Parent2 {
    @Id
    Long id;
    
    @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    Set<Child> children = new HashSet();
}

class Child {
    @Id
    Long id;
    
    @ManyToOne
    Parent1 p1;

    @ManyToOne(fetch = FetchType.EAGER)
    Parent2 p2;
}

I tried to use some combinations with orphanRemoval, but it didn't work. Do you have any other suggestions?

Why is there no such feature as join fetch all lazy entities by default?
While designing an application I have faced some questions related to the lazy/eager fetching and all the stuff around.

Basically, as far I as understand, doing eager fetching is not a good thing and it is better to do a lazy load most of the time. Here we are running into an issue with lazy loading in the same transaction. We cannot reopen a new transaction to load the lazy entities.

One of the, I'd say, latest (or the most modern?) solutions is to use @NamedEntityGraph with @EntityGraph that are using JOIN FETCH to join the specified entities in one query.

So the question is - why is there no way to do a join fetch by default for all the lazy entities? I mean, now I have to add @NamedEntityGraph for every model where I have to lazy load the entities and add @EntityGraph to tell to use the NamedEntityGraph that I specified. If I add the entities to the model that means that at some point I am going to use them (despite the fact I mark them as lazy). What is the problem not to join fetch all the lazy entities by default? That would just simplify the code, I believe.

To my mind came a few answers:
1. There is actually such a thing (but I haven't looked for it correctly)
2. It is not good for performance even to join fetch all the entities (Though eager fetching is not good as well? But If I have the entities in my model it means that I will use them at some point so why not to join fetch all of them?)
3. It is just no implemented at the moment.
To add more context - I use a few layers where some of them are repository and service. I want the repository and service to only work with models. But I have also got the DTO projections of the models and assemblers to map the models to DTO and back. I don't want to tie the DTO mapping to the service (which would actually solve the issue with lazy loading as we would use the same transaction, I believe). So I use assembler in controllers to map the models, that service returns, to the DTO and return those DTOs to the client.
Null check to an list before apply java steam to it
I have a below code
```
item.getSubCategory().stream().map(category -> {
              return subCategoriesList.add(new SubCategoryViewModel(
                      category.get(String.format("_%s",categoryProperties[0])).toString(),
                      category.get(categoryProperties[2]).toString(),
                      category.get(categoryProperties[3]).toString()));
            }).collect(Collectors.toList());
```
The item.getSubCategory() is a list of category model. Now if null appears on the getSubCategory we get an null pointer exception and can't apply steam to it. Is there a better way to handle null check to the list before applying stream to it.

I don't want to use IF statement to check null on getSubCategory. Is there any better way in java steam API ?

Rewriting using streams in Java8

I need some help with how I would rewrite this code using streams in Java. I am new to coding and could use some assistance. Thank you for your time. I have this class called thisDoesStuff that uses the doControlMethod.

 public void thisDoesStuff() {
        MealType previousMealType = null;
        int count = 0;
        int total = 0;
        int min = 0;
        int max = 0;
        int median = 0;
        double mean = 0;
        int index;
        for (int i = 0; i < mealList.size(); i++) {
            if (mealList.get(i) != null) {
                previousMealType = mealList.get(i).getMealType();
                if (mealList.get(i).getMealType() != previousMealType && previousMealType != null) {
                    System.out.printf("%-9s %9s %9s %9s %9s %9s \n", previousMealType.getPrettyPrint(), total,
                            String.format("%" +
                                    ".2f",
                            mean), min, max, median);
                }
                min = mealList.get(i).getCalories();
                count++;
                total += mealList.get(i).getCalories();
                mean = (double) total / count;
                if (max < mealList.get(i).getCalories()) {
                    max = mealList.get(i).getCalories();
                }
                index = count / 2;
                median = mealList.get(index).getCalories();

            }
            System.out.printf("%-9s %9s %9s %9s %9s %9s \n", previousMealType.getPrettyPrint(), total, String.format(
                    "%.2f",
                    mean), min,
                    max, median);
        }


    }

How could I simplify this with Java stream library?

for (Issue issue : issues) {
     if (issue.getSubtasks().spliterator().estimateSize() > 0) {
         alreadyCreated = false;
         for (Subtask subtask : issue.getSubtasks()) {
            if (subtask.getSummary().contains("sometext")) {
                alreadyCreated = true;
                break;
            }
         }
         if (!alreadyCreated) {
            ids.add(issue.getKey());
         }
    } else {
         ids.add(issue.getKey());
    }
}

I'm not so expert with Java stream API, but I'm pretty sure there's some way to simplify the code above with using lambda expressions. Would be a great help to understand it even better!

Some notes: Issues and getSubtasks() are returning back Iterable<> types.

How to remove non Ascii characters(non keyboard special charecters) from a text in hive
I want to remove non Ascii characters from my text and replace it with ''.

I have some invalid characters in my table that I'm trying to remove. But I ran into a problem with one of them.

Example:
```
123Abh¿½ï¿½ï¿½ï¿½ï¿½v streeÁÉÍÓt
```
Expected output:
```
123Abh street
```
As of now I'm using
```
regex_replace('123Abh¿½ï¿½ï¿½ï¿½ï¿½v streeÁÉÍÓt','[^[:print:]],'')
```
but this is not working, any suggestions?
Return data only from one Dataset out of multiple Datasets JOINS

I have 3 tables

Table : A, B, C

Input : A JOIN B A JOIN C

Output : If i even get a single record from A JOIN B , i should completely get data from that JOIN and the second join should not be considered In such case if i get 0 records from A JOIN B then We need to extract data from A JOIN C

Related questions

Spring Data JPA @Query not working (findAll + filter works)

See also questions close to this topic