JPA多表关联查询

JPA多表映射一般来说有两种方法，旧传统，Hibernate的时候是采用的对象关联的方式，但是那种太out了也太杂乱，还要考虑懒加载这东西。

另外一种是跟mybatis多表关联一样，搞多一个新对象来做映射，达到多表关联的目的。

以下是JPA的一个实操例子，仅供参考

public interface QueueRepository extends JpaRepository<Queue, String>{

    @Query(value="select max(d.balance) as balance,sum(1)  as sums,max(d.balance)*sum(1) as score ,d.device_id as device_id ,d.device_name as device_name \r\n" + 
            "from  queue q \r\n" + 
            "left join device d on q.equipment_id=d.device_id \r\n" + 
            "where d.state=1 and d.`type`=1 \r\n" + 
            "and to_days(q.booking_time) = to_days(now()) \r\n" + 
            "group by d.device_id,d.device_name \r\n" + 
            "order by max(d.balance)*sum(1) asc \r\n" 
            ,nativeQuery=true)
    List<QueueDevice> getQueueByDevice();

    /* 这是我们的加权负载排队算法
    select max(d.balance) as balance,sum(1)  as sums,max(d.balance)*sum(1) as score ,d.device_id as device_id ,d.device_name as device_name 
    from  queue q
    left join device d on q.equipment_id=d.device_id
    where d.state=1 and d.`type`=1
    and to_days(q.booking_time) = to_days(now())
    group by d.device_id,d.device_name
    order by max(d.balance)*sum(1) asc
    ;*/
}

通过代码可以看到，我是把两个表，device设备和queue队列进行关联，从而计算出一个加权队列，再映射到新对象上去，新对象一定要是interface，且有对应参数的构造方法。例如：

• select user，映射为String getUser();
• select user_name，映射为String getUser_Name()，注意并不采用驼峰命名的方式。
• 所以最好返回的时候设置为select username，则可以成功映射为String getUserName();

//命名方式1
public interface QueueDevice {
    String getBalance();
    String getSums();
    String getScore();
    String getDevice_id();
    String getDevice_name();
}
//命名方式2
public interface QueueDevice {
    String getBalance();
    String getSums();
    String getScore();
    String getDeviceId();
    String getDeviceName();
}

接着就可以调用了。

@Service
public class QueueServiceImpl implements QueueService{

    @Autowired
    QueueRepository queueRepository;

    @Override
    public List<QueueDevice> getQueueByDevice() {
        return queueRepository.getQueueByDevice();
    }
}

命名方式1获取出来的对象信息如下：

[{
balance: "12",
device_id: "TC001",
device_name: "骨密度",
score: "12.0",
sums: "1"},
{
balance: "7",
device_Id: "TC002",
device_Name: "氨糖",
score: "14.0",
sums: "2"}
]

命名方式2获取出来的对象信息如下：

[{
        "balance": "12",
        "deviceId": "TC001",
        "deviceName": "骨密度",
        "score": "12.0",
        "sums": "1"
    },
    {
        "balance": "7",
        "deviceId": "TC002",
        "deviceName": "氨糖",
        "score": "14.0",
        "sums": "2"
    }
]