EntityManager.createNativeQuery的使用

1.单表或多表关联查询

注意：sql的字段别名必须与Entity的属性名相同

Query query = entityManager.createNativeQuery(sql, clazz);

sql:

StringBuilder sb = new StringBuilder();
sb.append("    select           ");
sb.append("     t.id,           ");
sb.append("     t.year,         ");
sb.append("     t.month,        ");
sb.append("     t.port_zone portZone,           ");
sb.append("     t.port_code portCode,           ");
sb.append("     t.port_name_en portNameEn,          ");
sb.append("     t.port_name_cn portNameCn,          ");
sb.append("     t.load_port_code loadPortCode,          ");
sb.append("     t.load_port_name_en loadPortNameEn,         ");
sb.append("     t.load_port_name_cn loadPortNameCn,         ");
sb.append("     t.cargo_volume cargoVolume          ");
sb.append("    from ore_vessel_unload_volume t          ");

clazz:

package com.csg.sds.server.mulitjpa.domain.ods;

import lombok.Data;
import javax.persistence.Entity;
import javax.persistence.Id;
import java.io.Serializable;
import java.math.BigDecimal;

/**
 * 港口卸货量
 */
@Data
@Entity
public class VesselUnloadVolumeEntity implements Serializable {

    @Id
    private long id;

    private String year;

    private String month;

    private String portZone;

    private String portCode;

    private String portNameEn;

    private String portNameCn;

    private String loadPortCode;

    private String loadPortNameEn;

    private String loadPortNameCn;

    private BigDecimal cargoVolume;
}

2.单表查询：

注意：sql字段名要在Entity的属性名进行映射

Query query = entityManager.createNativeQuery(sql, clazz);

sql:

StringBuilder sb = new StringBuilder();
sb.append("    select           ");
sb.append("     t.id,           ");
sb.append("     t.year,         ");
sb.append("     t.month,        ");
sb.append("     t.port_zone,           ");
sb.append("     t.port_code,           ");
sb.append("     t.port_name_en,          ");
sb.append("     t.port_name_cn,          ");
sb.append("     t.load_port_code,          ");
sb.append("     t.load_port_name_en,         ");
sb.append("     t.load_port_name_cn,         ");
sb.append("     t.cargo_volume cargoVolume          ");
sb.append("    from ore_vessel_unload_volume t          ");

clazz:

package com.csg.sds.server.mulitjpa.domain.ods;

import lombok.Data;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import java.io.Serializable;
import java.math.BigDecimal;
import java.sql.Timestamp;

/**
 * 港口卸货量
 */
@Entity
@Data
@Table(name = "ore_vessel_unload_volume", schema = "shipping_ods", catalog = "")
public class VesselUnloadVolumeEntity implements Serializable {

    @Id
    @Column(name = "id")
    private long id;

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

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

    @Column(name = "port_zone")
    private String portZone;

    @Column(name = "port_code")
    private String portCode;

    @Column(name = "port_name_en")
    private String portNameEn;

    @Column(name = "port_name_cn")
    private String portNameCn;

    @Column(name = "load_port_code")
    private String loadPortCode;

    @Column(name = "load_port_name_en")
    private String loadPortNameEn;

    @Column(name = "load_port_name_cn")
    private String loadPortNameCn;

    @Column(name = "cargo_volume")
    private BigDecimal cargoVolume;
}