mysql - Spring boot与@Query注解连接以从Mysql中获取键和值对中选定的列

Question

这里我将 spring-boot 与 JPA、Mysql 和 Restful Web 服务结合使用。我 需要从 3 个具有连接概念的表中获取数据。执行查询 我正在使用 @Query 注释。在获取数据时它只给出 值(value)观， 不过，我也期待着 key 。请找到以下代码 引用。提前致谢。 这是 Controller 类:

@Autowired
Active_DashBoard activedashBoard;

@RequestMapping(produces = "application/json", method = RequestMethod.GET, 
value = "/logo")
   public List<Dashboard> logo(){

      List<Dashboard> dd = new ArrayList<Dashboard>();
      dd = activedashBoard.getList();

      return dd;
}

Here is the Service method

@Autowired
DashboradupdatesRepository dashboard;

public List<Dashboard> getList( ){
    List<Dashboard> al = new ArrayList<Dashboard>();
    al = dashboard.DataListStatusnull();
    return al;
}

Implemented Repository extending JpaRepository<Dashboard, Long>

@Query("SELECT du.consumed_bitrate,ac.timezonevalue_utc,ac.channel_id,cu.name 
as customer_name,du.location,du.new_date,du.datetime,cu.logo as 
customer_logo,du.imei,du.activestatus,du.new_date,ch.name as channel_name 
FROM Dashboard as du JOIN AndroidDevicevsChannels as ac ON ac.device_id = 
du.imei JOIN ChannelInfo as ch ON ch.channel_id = ac.channel_id JOIN 
CustomerInfo as cu ON cu.customer_id = ac.customer_id WHERE  (du.activestatus 
= 0 OR internetspeed='0') ORDER BY cu.name,ch.channel_id,du.imei ")

List<Dashboard> DataListStatusnull();



And I have 3 bean classes, each bean class represents a table. Used @Entity, 
and @Table, @column annotations where required.

Even checked with @OnetoMany etc, but not worked.
Please suggest on this.




1 bean class is :

package com.example.demo.model;

import java.util.Date;

import javax.persistence.Column;
import javax.persistence.EmbeddedId;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "dashboard_updates")
public class Dashboard {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private String imei;
@Column(name = "internetspeed")
private String internetspeed;
private String datetime;
private int activestatus;

@Column(name = "streamstatus")
private String streamstatus;
private Date new_date;
private String channel_id;
private String ip_address;
private String location;

public String getDatetime() {
    return datetime;
}

public void setDatetime(String datetime) {
    this.datetime = datetime;
}

public int getActivestatus() {
    return activestatus;
}

public void setActivestatus(int activestatus) {
    this.activestatus = activestatus;
}

public String getStreamstatus() {
    return streamstatus;
}

public void setStreamstatus(String streamstatus) {
    this.streamstatus = streamstatus;
}

public Date getNew_date() {
    return new_date;
}

public void setNew_date(Date new_date) {
    this.new_date = new_date;
}

public String getChannel_id() {
    return channel_id;
}

public void setChannel_id(String channel_id) {
    this.channel_id = channel_id;
}

public String getIp_address() {
    return ip_address;
}

public void setIp_address(String ip_address) {
    this.ip_address = ip_address;
}

public String getLocation() {
    return location;
}

public void setLocation(String location) {
    this.location = location;
}

public String getGeo_lang() {
    return geo_lang;
}

public void setGeo_lang(String geo_lang) {
    this.geo_lang = geo_lang;
}

public String getGeo_lat() {
    return geo_lat;
}

public void setGeo_lat(String geo_lat) {
    this.geo_lat = geo_lat;
}

public String getLocation_history() {
    return location_history;
}

public void setLocation_history(String location_history) {
    this.location_history = location_history;
}

public String getIpaddress_history() {
    return ipaddress_history;
}

public void setIpaddress_history(String ipaddress_history) {
    this.ipaddress_history = ipaddress_history;
}

public String getConsumed_bitrate() {
    return consumed_bitrate;
}

public void setConsumed_bitrate(String consumed_bitrate) {
    this.consumed_bitrate = consumed_bitrate;
}

private String geo_lang;
private String geo_lat;
private String location_history;
private String ipaddress_history;
private String consumed_bitrate;

public String getInternetspeed() {
    return internetspeed;
}

public void setInternetspeed(String internetspeed) {
    this.internetspeed = internetspeed;
}

public String getImei() {
    return imei;
}

public void setImei(String imei) {
    this.imei = imei;
  }

 }

secound bean is : 

package com.example.demo.model;

import java.sql.Date;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;


@Entity
@Table(name = "customer_info")
public class CustomerInfo {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private int id;
private String customer_id;
private String name;
private String address1;
private String  address2;
private String country;
private String city;
private String state;
private String zip;
private String phone;
private String email;
private String logo;
//private Date created_date;
//private String created_date;
private int active_status;
private int delete_status;
public int getId() {
    return id;
}
public void setId(int id) {
    this.id = id;
}
public String getCustomer_id() {
    return customer_id;
}
public void setCustomer_id(String customer_id) {
    this.customer_id = customer_id;
}
public String getName() {
    return name;
}
public void setName(String name) {
    this.name = name;
}
public String getAddress1() {
    return address1;
}
public void setAddress1(String address1) {
    this.address1 = address1;
}
public String getAddress2() {
    return address2;
}
public void setAddress2(String address2) {
    this.address2 = address2;
}
public String getCountry() {
    return country;
}
public void setCountry(String country) {
    this.country = country;
}
public String getCity() {
    return city;
}
public void setCity(String city) {
    this.city = city;
}
public String getState() {
    return state;
}
public void setState(String state) {
    this.state = state;
}
public String getZip() {
    return zip;
}
public void setZip(String zip) {
    this.zip = zip;
}
public String getPhone() {
    return phone;
}
public void setPhone(String phone) {
    this.phone = phone;
}
public String getEmail() {
    return email;
}
public void setEmail(String email) {
    this.email = email;
}
public String getLogo() {
    return logo;
}
public void setLogo(String logo) {
    this.logo = logo;
}

 /* public String getCreated_date() {
    return created_date;
}
public void setCreated_date(String created_date) {
    this.created_date = created_date;
}*/
public int getActive_status() {
    return active_status;
}
public void setActive_status(int active_status) {
    this.active_status = active_status;
}
public int getDelete_status() {
    return delete_status;
}
public void setDelete_status(int delete_status) {
    this.delete_status = delete_status;
}
public int getCreated_by() {
    return created_by;
}
public void setCreated_by(int created_by) {
    this.created_by = created_by;
}
private int created_by;

}

3 bean is :

package com.example.demo.model;

import java.sql.Date;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "channel_info")
public class ChannelInfo {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private int id;
    private String channel_id;
private String name;
private String link;
private String audio_bitrate;
private String video_bitrate;
private String video_type;
private int ads_status;
private String logo;
private int active_status;
private String player_code;
private Date created_datetime;
private int delete_status;
public int getId() {
    return id;
}
public void setId(int id) {
    this.id = id;
}
public String getChannel_id() {
    return channel_id;
}
public void setChannel_id(String channel_id) {
    this.channel_id = channel_id;
}
public String getName() {
    return name;
}
public void setName(String name) {
    this.name = name;
}
public String getLink() {
    return link;
}
public void setLink(String link) {
    this.link = link;
}
public String getAudio_bitrate() {
    return audio_bitrate;
}
public void setAudio_bitrate(String audio_bitrate) {
    this.audio_bitrate = audio_bitrate;
}
public String getVideo_bitrate() {
    return video_bitrate;
}
public void setVideo_bitrate(String video_bitrate) {
    this.video_bitrate = video_bitrate;
}
public String getVideo_type() {
    return video_type;
}
public void setVideo_type(String video_type) {
    this.video_type = video_type;
}
public int getAds_status() {
    return ads_status;
}
public void setAds_status(int ads_status) {
    this.ads_status = ads_status;
}
public String getLogo() {
    return logo;
}
public void setLogo(String logo) {
    this.logo = logo;
}
public int getActive_status() {
    return active_status;
}
public void setActive_status(int active_status) {
    this.active_status = active_status;
}
public String getPlayer_code() {
    return player_code;
}
public void setPlayer_code(String player_code) {
    this.player_code = player_code;
}
public Date getCreated_datetime() {
    return created_datetime;
}
public void setCreated_datetime(Date created_datetime) {
    this.created_datetime = created_datetime;
}
public int getDelete_status() {
    return delete_status;
}
public void setDelete_status(int delete_status) {
    this.delete_status = delete_status;
}

}

the output is:


  [
    "",
    "5.5::2::+05:30",
    "CH_00090",
    "AKHUN KOLKATA",
    "",
    "2018-05-29T07:09:41.000+0000",
    "2018-05-29 18:05:30",
    "customer_logo_16032018131544.jpeg",
    "IB-AKHUN-TEST",
    1,
    "2018-05-29T07:09:41.000+0000",
    "AKHUNKOLKATA"
],
[
    "",
    "5.5::2::+05:30",
    "CH_00090",
    "AKHUN KOLKATA",
    "",
    "2018-03-19T05:21:57.000+0000",
    "2018-03-19 16:21:17",
    "customer_logo_16032018131544.jpeg",
    "TEST-KOLKATA",
    1,
    "2018-03-19T05:21:57.000+0000",
    "AKHUNKOLKATA"
]]

Answer 1

您可以遍历列表并检查您的实体以获取键和值。

例如您的仪表板实体将填充用于直接映射/连接的字段和值