springDataJpa多表级联查询（@ManyToOne @OneToOne）

忘是亡心i 2021-06-11 15:13 1400阅读 0赞

## 背景： ##

主要是记录自己遇到的坑，最后是通过注解解决的@ManyToOne @OneToOne,其实这个问题碰到过，今天又踩了

## 主要问题： ##

两个类要实现级联查询，比如Iteminformation 、Timesetting，类里面字段先不管，第一反应是建个VO类（主要是看到公司了有类似的建DTO类的代码，那是对一个有太多字段的类而只查询部分字段的临时类）

@Query("select distinct new cn.com.dto.SpxxDTO(t.dzsphm, t.kprq, t.fkjnfsDm, t.fkssswjgDm, t.zffsDm) from SpxxCMP t where t.spztDm in (:spztList)")
        List<SpxxDTO> findSpztDmInAndZffsDmIn(@Param("spztList") List<String> spztList);

所以就这样写了，如下

public class ItemInformationVO {
    	private Iteminformation iteminformation;
    	private Timesetting timesetting;
    
    	public ItemInformationVO() {
    		super();
    	}
    
    	public ItemInformationVO(Iteminformation iteminformation, Timesetting timesetting) {
    		this.iteminformation = iteminformation;
    		this.timesetting = timesetting;
    	}
    
        //getter setter
    }

在数据库接口类中新增接口如下：

@Repository
    public interface ItemInfomationRepository extends JpaRepository<Iteminformation, Integer>,JpaSpecificationExecutor<Iteminformation>,PagingAndSortingRepository<Iteminformation, Integer>{
    
    	@Query(value = "select new com.sssp.utils.DTO.ItemInformationVO(i,t) FROM Iteminformation i,Timesetting t where i.itemid = t.itemid")
    	public List<ItemInformationVO> findAllItemInformation1();
    
    }

看似挺好的呀，将查出来的对象建个对象，数据也能获取。

接下来我们试试@OneToOne注解：

@Cacheable
    @Table(name = "iteminformation")
    @Entity
    public class Iteminformation {
    	@Id
    	@Column(name = "itemid")
    	@GeneratedValue(strategy = GenerationType.IDENTITY)
    	private Integer itemid;
    	
    	@Column(name = "itemname")
    	private String itemname;
    	
    	@Column(name = "grade")
    	private String grade;
    	
    	@Column(name = "itemhead")
    	private String itemhead;
    	
    	@Column(name = "phone")
    	private String phone;
    	
    	@Column(name = "email")
    	private String email;
    	
    	@Column(name = "professional")
    	private String professional;
    	
    	@Column(name = "teammembers")
    	private String teammembers;
    
    	@OneToOne
    	@JoinColumn(name="timesetting",referencedColumnName="timesettingId")
    	private Timesetting timesetting;
    //以下省略
    }

@OneToOne

这里的@OneToOne指的是两个类对象是一对一的关系，比如一夫一妻制，当然也有@ManyToMany,@ManyToOne,比如人可以有很多本书，一本书也可以被很多人借一样

@JoinColumn(name="timesetting",referencedColumnName="timesettingId")

@JoinColumn注解指定表关系

name指定了外键的名称，referencedColumnName指定被链接的表的外键名称，这样就关联了起来。

@Cacheable
    @Table(name = "timesetting")
    @Entity
    public class Timesetting {
    	@Id
    	@Column(name = "timesettingId")
    	@GeneratedValue(strategy = GenerationType.IDENTITY)
    	private Integer timesettingId;
    	
    	@JsonFormat(pattern = "yyyy-MM-dd")
    	@Column(name = "starttime")
    	private Date starttime;
    	
    	@JsonFormat(pattern = "yyyy-MM-dd")
    	@Column(name = "endtime")
    	private Date endtime;
    	
    	@JsonFormat(pattern = "yyyy-MM-dd")
    	@Column(name = "midtime")
    	private Date midtime;
    	
    	@JsonFormat(pattern = "yyyy-MM-dd")
    	@Column(name = "finaltime")
    	private Date finaltime;
    
    //以下省略
    }

### 这样就能查出来了 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzk2ODYxMw_size_16_color_FFFFFF_t_70][]

###  有什么问题欢迎大家一起讨论，对你有所帮助也请您点个赞哦。 ###

JPA使用记录：

@Query(value = "select new cn.com.dto.xnhplsb.response.XnhResponseDTO(count(t.id),SUM(t.yjfe), SUM(CASE WHEN t.zsxmDm = '10201' or t.zsxmDm = '10210' then t.yjfe ELSE 0 END)," +
          "SUM(CASE WHEN t.zsxmDm = '10203' or t.zsxmDm = '10207' or t.zsxmDm = '10212' then t.yjfe ELSE 0 END))" +
          "from WtdsXnhTzmxCMP t where t.tzjlId =:tzjlId and t.jyjg=:jyjg")
    XnhResponseDTO findZjeByTzjlIdAndJyjg(@Param("tzjlId") String tzjlId, @Param("jyjg") String jyjg);

XnhResponseDTO中只有配置相应的构造函数既可以了

public XnhResponseDTO(double zje, double yangLJhej, double yiLJehj) { //台账明细总金额计算用
            this.yiLJehj = new BigDecimal(yiLJehj);
            this.yangLJhej = new BigDecimal(yangLJhej);
            this.zje = new BigDecimal(zje);
        }

这样就可以实现多表查询

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzk2ODYxMw_size_16_color_FFFFFF_t_70]: /images/20210518/e0552a89c2404103b3c308b5226ec735.png