JPA多表关联动态查询（自定义sql语句）

项目需求，查询需求数据需要多表链接——>根据多种条件筛选查询到的数据，在网上查了很多资料最终选择这个字符串拼接查询

类似如此动态查询

以下是本人项目中使用总结：

实体类

/**
 * 订单表
 */
@Entity
@Table(name = "signedorder")
@Getter
@Setter
@NoArgsConstructor
@EntityListeners(AuditingEntityListener.class)
public class SignedOrder {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column
    private Integer id;//id
    @CreatedDate
    @Column(updatable = false)
    private Date createTime;//创建时间
    @LastModifiedDate
    @Column
    private Date lastModifiedTime;//修改时间
    @ManyToOne(fetch = FetchType.EAGER, cascade = {
            CascadeType.MERGE, CascadeType.PERSIST, CascadeType.REFRESH
    })
    @JoinTable(name = "staff_signedorder", joinColumns = @JoinColumn(name =
            "signedorder_id"), inverseJoinColumns = @JoinColumn(name = "staff_id"))
    private Staff staff;//所属用户
    @JoinColumn(name = "industry_id")
    private Integer industryId;//行业Id
}

/**
 * 用户表
 */
@Entity
@Table(name = "staff")
@Getter
@Setter
@NoArgsConstructor
public class Staff {
 
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Integer id;//id
    @Column(name = "name", length = 25)
    private String name;//姓名
    @JoinColumn(name = "city_id")
    private Integer cityId;//城市id

/**
 * 城市表
 */
@Entity
@Table(name = "city")
@Getter
@Setter
@NoArgsConstructor
@Accessors(chain = true)
public class City {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Integer id;//id
    @Column(name = "name", length = 50)
    private String name;//名称
}
//行业表和城市表一致，就不展示了

注解解释

实体类中相关注解解释：

@Entity： 对实体注释。任何Hibernate映射对象都要有这个注释

@Table： 声明此对象映射到数据库的数据表，该注释不是必须的，如果没有则系统使用默认值(实体的短类名)

@Getter、 @Setter、 @NoArgsConstructor：lombok提供注解，get、set方法及无参构造

@EntityListeners(AuditingEntityListener.class)：加上此注解，时间注解@LastModifiedDate 和 @CreatedDate才可以生效

@Id： 声明此属性为主键

@GeneratedValue(strategy = GenerationType.IDENTITY)：指定主键，

​ TABLE：使用一个特定的数据库表格来保存主键；

​ IDENTITY：主键由数据库自动生成（主要是自动增长型）；

​ SEQUENCR：根据底层数据库的序列来生成主键，条件是数据库支持序列；

​ AUTO：主键由程序控制

​ @CreatedDate(updatable = false)：创建时间时间字段，在insert的时候，会设置值；update时时间不变

​ @LastModifiedDate：修改时间段，update时会修改值

​ @ManyToOne(fetch = FetchType.EAGER, cascade = {CascadeType.MERGE, CascadeType.PERSIST, CascadeType.REFRESH})： 多对一，

​ FetchType.EAGER:立即加载, 获取关联实体；

​ CascadeType.MERGE: 级联更新；

​ CascadeType.PERSIST:级联新建；

​ CascadeType.REFRESH:级联刷新

​ @JoinTable: JoinColumn：保存关联关系的外键的字段;inverseJoinColumns：保存关系的另外一个外键字

​ @Column：用来标识实体类中属性与数据表中字段的对应关系

测试类

@RunWith(SpringRunner.class)
@SpringBootTest
public class SprinBootMarketingsystemApplicationTests {
    @PersistenceContext//jpa的数据库操作类
    private EntityManager entityManger;

    @Test
    public void queryDb(){
       //给参数赋值
        Integer cityId = 1;
        Integer industryId = 2;
        Integer staffId = 16;
        Date startTime = DateUtil.parse("1970-01-01");//字符串时间转换为date类型
        Date endTime = Calendar.getInstance().getTime();//获取系统当前时间装换为date类型
        
        //创建SQL语句主体
        StringBuffer stringBuffer = new StringBuffer("\tSELECT\n" +
                "\tcount( * ) count,\n" +
                "\tci.NAME cityName\n" +
                "\tFROM\n" +
                "\tsignedorder s\n" +
                "\tLEFT JOIN staff_signedorder t ON s.id = t.signedorder_id\n" +
                "\tLEFT JOIN staff sta ON t.staff_id = sta.id\n" +
                "\tLEFT JOIN city ci ON sta.city_id = ci.id\n" +
                "\tWHERE\n" +
                "\t1 = 1");

         Map<String,Object> map =  new HashMap<>();
         //拼接动态参数
        if(industryId != null){
            
             /*第一种给参数赋值方式
             1代表传进来的参数顺序,给参数赋值nativeQuery.setParameter(1, industryId);
             stringBuffer.append(" and s.industryId = ?1");*/
             //industryId代表传进来的参数名称,给参数赋值nativeQuery.setParameter("industryId", industryId);
             stringBuffer.append(" and s.industry_id = :industryId");
             map.put("industryId",industryId);
        }
        if(cityId != null){
            stringBuffer.append(" and ci.id = :cityId");
            map.put("cityId",cityId);
        }
        if(staffId != null){
            stringBuffer.append(" and sta.id = :staffId");
            map.put("staffId",staffId);
        }
        if(startTime!=null && endTime!=null){
            //使用这种赋值方式，时间类型需要给三个参数，参数名称，参数值，特定映射的类型TemporalType.DATE
            //nativeQuery.setParameter("create_time", startTime,TemporalType.DATE);
            stringBuffer.append( " and s.create_time BETWEEN :startTime and :endTime ");
            map.put("startTime",startTime);
            map.put("endTime",endTime);
        }
        Query nativeQuery = entityManger.createNativeQuery(stringBuffer.toString());
        for (String key : map.keySet()) {
            nativeQuery.setParameter(key, map.get(key));
        }

        //三种接受返回结果方式(第一种方式)
        /*nativeQuery.unwrap(SQLQuery.class).setResultTransformer(Transformers.TO_LIST);
        List resultList1 = nativeQuery.getResultList();
        for (Object o : resultList1) {
            System.out.println(o.toString());
        }*/
       //第二种方式和第一种方式相似
        /*nativeQuery.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List<Map<String, Object>> resultList = nativeQuery.getResultList();
        for (Map<String, Object> map1 :resultList
        ) {
            System.out.println(map1);
        }*/
        //第三种方式：实体类接受
        nativeQuery.unwrap(SQLQuery.class).setResultTransformer(Transformers.aliasToBean(TestVo.class));
        List<TestVo> resultList = nativeQuery.getResultList();
        for (TestVo svo:resultList
        ) {
            System.out.println(svo.toString());
        }
    }

打印结果

• 第一种方式打印结果

• 第二种方式打印结果

• 第三种方式打印结果

TestVo实体接收类

@Data
public class TestVo {
    private String cityName;//城市名字
    private BigInteger count;//签单数量(必须使用BigInteger类型接受)
}