Spring Data Jpa to get data which doesn't have entity representation

user_4282957 0
Viewed 90

I'm trying to find the best way to map my data on ORM. I have a query which gets me data from MySQL database which look like

SELECT d.ID AS Id,
    equipment.EQUIP_ID,
    equipment.EQUIP_REFERENCE
FROM
    tbl_devices d
        INNER JOIN
   tbl_equipment equipment ON equipment.EQUIP_ID = d.DEV_ID

What would be the most optimal way to get these data with Spring boot and Spring data??

Should I use @Query annotation and execute this or somehow create entities for Equipment and Devices tables and then use JPQL/HQL to join tables in a query, but then how should I map the result??

Thanks in advance.

spring-data-jpa spring-boot orm sql java
2 Answers

You can use JdbcTemplate (import from org.springframework.jdbc.core.JdbcTemplate) to run the SQL statement above you wrote. After you can create a pojo to map result into it via BeanPropertyRowMapper. For example:

final String sql = "SELECT d.ID AS Id, equipment.EQUIP_ID, equipment.EQUIP_REFERENCE FROM tbl_devices d INNER JOIN tbl_equipment equipment ON equipment.EQUIP_ID = d.DEV_ID";
YourPojo result = jdbcTemplate.query(
    sql,
    new BeanPropertyRowMapper<>(YourPojo.class)
);

Pojo class maybe like following:

@Data
public class YourPojo {
    private Long id;
    private Long equipId;
    private Your_type equip_reference_name;
}
user_73883630

A quick and dirty solution would be to use projections.

First, you create a projection interface:

public interface DeviceDetails {

    Long getId();

    Long getEquipId();

    String getEquipReference();

}

You then modify the query to match column aliases with the projection properties:

SELECT d.ID AS id, 
    equipment.EQUIP_ID as equipId
    equipment.EQUIP_REFERENCE As equipReference
...

Finally, you put the query method in a repository of your choice:

@Query(value =..., nativeQuery = true) 
List<DeviceDetails> findDeviceDetails();
user_10928180
Related