CODEWITHSUNDEEP
hibernatespring-boot
Oleg Pavliv
Oleg Pavliv

Reputation: 21162

emulating DB view in spring-boot application

My entity is mapped to a DB table.

I need to re-map it to another table with a where condition. The easiest way would be to create a DB view.

create view person_main_asg as (
select * from person_history 
where asg_end_date is null or asg_end_date > CURRENT_TIMESTAMP
)

The problem is that I don't have permissions to create it.

So I need to solve the problem in my spring-boot application.

Googling around I found that I can add a filter to my entity definition.

@Entity
@FilterDef(name="person_history_main_asg")
@Filter(name="person_history_main_asg", condition = "asg_end_date is null or asg_end_date > CURRENT_TIMESTAMP) ")
@Table(name = "person_history")
public class Person {
///...
}

Now I need to enable my filter because it's disabled by default.

It turned out to be a difficult task.

One solution is to use AOP/AspectJ. It seems to me more a hack than an answer but the main problem is that it requires to modify VM arguments by adding javaagent:path/to/aspectjweaver.jar which I'm not allowed to do.

Another solution is to implement a repository which is currently defined as an interface

public interface PersonRepository  {

    Person findByFullLoginEndingWith(String login);

    List<Person> findByFirstNameContainingIgnoreCase(String firstName);
    List<Person> findByLastNameContainingIgnoreCase(String lastName);
}

So the implementation is supposed to be 

public interface PersonRepositoryCustom  {
    Person findByFullLoginEndingWith(String login);

    List<Person> findByFirstNameContainingIgnoreCase(String firstName);
    List<Person> findByLastNameContainingIgnoreCase(String lastName);
}

public interface PersonRepository extends Repository<Person, String>, JpaSpecificationExecutor<Person>, PersonRepositoryCustom {
}

public class PersonRepositoryImpl implements PersonRepositoryCustom {

    @PersistenceContext
    private EntityManager entityManager;

    @Autowired
    private PersonRepository personRepository;

    public Person findOne(Specification<Person> spec) {
        Filter filter = (Filter)entityManager.unwrap(Session.class).enableFilter("person_history_main_asg");
        Person result = personRepository.findOne(spec);
        return result;
    }

    public Person findOne(String id) {
       //...
    }

    public List<Person> findAll() {
       //...
    }

    public List<Person> findAll(Specification<Person> spec) {
       //...
    }

    //...
}

I have several problems with this solution (in addition that it is bulk and heavy).

EntityManager causes the following error: No qualifying bean of type [javax.persistence.EntityManagerFactory] is defined: expected single matching bean but found 4

Also

@Autowired
private PersonRepository personRepository;

causes stackoverflow because it's an instance of PersonRepositoryImpl which is autowired but I need to access the default implementation by Springboot.

So how I can emulate a DB view in my application?

EDIT:

Currently my entity is mapped to another table. And my entity repository is defined as 

public interface PersonRepository extends Repository<Person, String>, JpaSpecificationExecutor<Person> {

        T findOne(ID id);
        List<T> findAll();

        Person findByFullLoginEndingWith(String login);

        List<Person> findByFirstNameContainingIgnoreCase(String firstName);
        List<Person> findByLastNameContainingIgnoreCase(String lastName);
}

Upvotes: 1

Views: 778

Answers (1)

M. Deinum
M. Deinum

Reputation: 124526

You don't need an implementation, you don't need filters, you don't need a view. Just define a query method and annotate it with @Query. 

public interface PersonRepository extends JpaRepository {

    Person findByFullLoginEndingWith(String login);

    List<Person> findByFirstNameContainingIgnoreCase(String firstName);
    List<Person> findByLastNameContainingIgnoreCase(String lastName);

    @Query(value="select * from person_history where asg_end_date is null or asg_end_date > CURRENT_TIMESTAMP", nativeQuery=true)
    List<Person> findPersonMain();
}

That is all you need, no custom implementation etc. as Spring Data JPA will know what to do with that. Currently it is a native query but you can probably rewrite it to a JPQL one.

=====================================================================

EDITED BY Oleg Pavliv:

The solution is to use @Where instead of @Filter (taken from the thread of comments).

@Entity
@Where(clause = "asg_end_date is null or asg_end_date > GETDATE()) ")
@Table(name = "person_history")
public class Person {
///...
}

Upvotes: 1

Related Questions