java - Hibernate - 使用多对一注释从多个表中获取数据

Question

我有 3 个表，例如:

汽车

carid|carname|carPrice|
100|bmw|10L|
200|honda|5L|
..
..

汽车类型

carTypeid|Type|
1|suv|
2|xuv|
3|sedan|

汽车配置

carid|carTypeid|
100|3|
200|3|

我有所有 3 个表的实体。

下面我粘贴了连接表carconfig的代码:

@Entity
@Table(name = "CARCONFIG", uniqueConstraints = { 
        @UniqueConstraint(columnNames = { "carId" } )
})
public class Carconfig {

    @Id
    long carId;

    @OneToOne
    @JoinColumn(name = "carId", insertable = false, updatable = false)
    Car car;

    @ManyToOne(optional = false)
    @JoinColumn(name = "carTypeId")
    CarType carType;

    public Carconfig() {
        super();
    }

    public Carconfig(long carId, CarType carType) {
        super();
        this.carType = carType;
        this.carId = carId;
    }

    public Car getCar() {
        return car;
    }

    public void setCar(Car car) {
        this.car = car;
    }

    public CarType getCarType() {
        return carType;
    }

    public void setCarType(CarType carType) {
        this.carType = carType;
    }

    public long getCarId() {
        return carId;
    }

    public void setCarId(long carId) {
        this.carId = carId;
    }

}

我的问题是:

我想知道如何从表 car 和 carType 中获取数据并将其写入 csv。

下面是获取数据的代码。

public String downloadcars(CarForm form, Model model,HttpServletRequest request, HttpServletResponse response)throws IOException {
        PrintWriter pw = null;

        try {
            logger.debug("+++++++++++++++++++++++++++++++++++++++");
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-disposition","attachment; filename=car" + ".csv");
            response.setHeader("Expires", "0");
            response.setHeader("Cache-Control","must-revalidate, post-csheck=0, pre-check=0");
            response.setHeader("Pragma", "public");
            pw = response.getWriter();
            // Get, Write and flush the data.
            pw.println(" Id,Name,Price,Type");
            List<car> carFromDB = Collections.emptyList();



            for (Car car : carFromDB) {             
                pw.println(String.format("%s,%s,%s,%s,%s",
             // this is where i need help

            }
            pw.flush();

        } catch (Exception ex) {
            logger.error("Error while downloading car", ex);
            model.addAttribute("failureMsg","Error while downloading cars");
        }
        return null;
    }

Answer 1

您可以简单地使用 HQL 和 Join 查询来完成此操作，如下所示:

List<Object[]> carsWithDetails = session.createQuery(
    "select distinct car.carid, car.carname, car.carPrice, cartype.Type " +
    "from Car  car join car.carType cartype ")
    .list();

然后迭代 Object[] 数组的 list 以使用 Iterator 提取汽车详细信息:

Iterator<Object[]> it = carsWithDetails.iterator();
while(it.hasNext()){
  Object[] car = (Object[]) it.next();
  System.out.println("Car Details:  ");
  System.out.println("Id: "+car[0]);
  System.out.println("Name: "+car[1]);
  System.out.println("Price: "+car[2]);
  System.out.println("Type: "+car[3]);
}

当然，更改循环以满足您的需求，添加代码以写入 CSV 而不是 System.out.println 行。

它将取代这个循环:

for (Car car : carFromDB) {             
            pw.println(String.format("%s,%s,%s,%s,%s",
         // this is where i need help

        }

编辑:

如果您想联接所有三个表，您只需将联接引用join car.carConfig carConfig添加到您的查询中，如下所示:

List<Object[]> carsWithDetails = session.createQuery(
    "select distinct car.carid, car.carname, car.carPrice, cartype.Type " +
    "from Car  car join car.carType cartype join car.carConfig carConfig")
    .list();