How to use MySQL view's with Spring Data and eclipselink

2

In the development of my project I came across a select that will require a lot of application performance, since it consists in the use of two join's. Soon I came to mind creating a view in MySQL, but the problem is that I never used it consumed the same with eclipselink and Spring Data. Has anyone ever experienced this? I was not wanting to @query("minha_consulta") to fetch the data.

    
asked by anonymous 19.11.2014 / 20:32

2 answers

1

@utluiz, got a grade 10! I had to do the following, I created a view in my DB:

select c.id_customer, c.tenant_id, c.first_name, ds.name 
from viatgedb.customer c
join viatgedb.customer_service cs
on c.id_customer = cs.fk_customer 
join viatgedb.destination_requested ds on cs.id_customer_service = ds.fk_customer_service;

Soon after I did the JPA mapping (I'm working with the multi-tenant architecture):

@ReadOnly
@Entity
@Table(name="vw_open_services")
@Multitenant
@TenantDiscriminatorColumn(name = "tenant_id", discriminatorType = DiscriminatorType.INTEGER, contextProperty = PersistenceUnitProperties.MULTITENANT_PROPERTY_DEFAULT)
public class VwOpenService implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @Column(name="id_customer")
    private Long id;

    @Column(name="tenant_id", insertable=false, updatable=false)
    private Long tenantId;

    @Column(name="day_service")
    private String dayService;

    @Column(name="first_name")
    private String firstName;

    @Column(name="NAME")
    private String name;

    // Getter and Setter

And I've usually created a repository to bring my records with Spring Data:

@Repository
public interface OpenServiceRepository extends BaseRepository<VwOpenService, Long>{

    List<VwOpenService> findAll(); 

}

The result was as expected.

Thank you

    
20.11.2014 / 21:26
2

You can map a view as a common table using @Entity and @Table without any difficulties. I've done this a few times using Hibernate.

Just be careful not to use DDL generation, otherwise EclipseLink might try to execute a ALTER TABLE on view .

In addition, do not change the entity, that is, when you retrieve an object from the database, do not use the setters methods because JPA understands this as a change in the data and will try to make a UPDATE .

However, specifically in EclipseLink, I believe that the above two problems can be avoided by using the @ReadOnly in your organization.

Example:

@ReadOnly 
@Entity 
@Table(name = "VW_MINHAVIEW") 
public class ReadOnlyEntity { ... }
    
19.11.2014 / 21:23