SpringBoot Data JPA 关联表查询

SpringBoot Data JPA实现 一对多、多对一关联表查询

开发环境

IDEA 2017.1
Java1.8
SpringBoot 2.0
MySQL 5.X

功能需求

通过关联关系查询商店Store中所有的商品Shop，商店对商品一对多，商品对商店多对一,外键 store_id存在于多的一方。使用数据库的内连接语句。

表结构

tb_shop

• 实体类，通过注解实现
  1.商店类Store.java

package com.gaolei.Entity;


import javax.persistence.*;
import java.util.HashSet;
import java.util.Set;

/**
 * Created by GaoLei on 2018/6/25.
 */
@Entity
@Table(name = "tb_store")
public class Store {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private  Integer id;//商铺号

    private String name;//商铺姓名

    private String address;//商铺地址

    private int tel ;//商铺联系

    private String info;//商铺信息

    @OneToMany(cascade = CascadeType.ALL,mappedBy = "store")
    private Set<Shop> shops  = new HashSet<Shop>();
   // 省略set()和get()方法；
}

2. 商品类Shop.java

package com.gaolei.Entity;

import javax.persistence.*;
import java.util.HashSet;
import java.util.Set;

/**
 * Created by GaoLei on 2018/6/25.
 */
@Entity
@Table(name = "tb_shop")
public class Shop {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id ; //商品id

    private String name;//商品名

    private int price;// 商品价格

    private int num;//商品数量

    private String info;//商品信息

    @ManyToOne
    @JoinColumn(name = "store_id")//外键
    private  Store store;
   // 省略set()和get()方法；
}

3. StoreDao.java

CrudRepository 接口继承于 Repository 接口，并新增了简单的增、删、查等方法。其中封装好了很多的方法，这里不再概述，自行百度，这里通过自定义HQL语句完成复杂的操作。

package com.gaolei.Dao;

import com.gaolei.Entity.Store;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * Created by GaoLei on 2018/6/25.
 */
@Repository
public interface  StoreDao extends CrudRepository<Store,Integer> {

  
   //此方法通过内连接查询店铺id=?中的所有商品
    @Query("select distinct s from Store s inner join s.shops where s.id = ?1")
    List<Store> findByShopList(Integer id);
}

4. StoreService.java

通过@Autowired注入StoreDao来实现方法

package com.gaolei.Service;

import com.gaolei.Dao.StoreDao;
import com.gaolei.Entity.Shop;
import com.gaolei.Entity.Store;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

/**
 * Created by GaoLei on 2018/6/25.
 */
@Controller
@Transactional
public class StoreService {
    @Autowired
    private StoreDao storeDao;
    /**
     * 展示商店商品
     * */
    public List<Store> findByShopList(Integer id){
        return  storeDao.findByShopList(id);
    }
}

5. StoreAction.java

实现具体数据操作操作

 package com.gaolei.Action;

import com.gaolei.Entity.Shop;
import com.gaolei.Entity.Store;
import com.gaolei.Service.ShopService;
import com.gaolei.Service.StoreService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.List;


/**
 * Created by GaoLei on 2018/6/26.
 */
@Controller
@RequestMapping("/store")
public class StoreAction {

    @Autowired
    private StoreService storeService;

  /**
     * Store_shop_menu展示店铺商品
     * */
    @RequestMapping("showShop")
    public  String  showShop(HttpServletResponse response ,HttpServletRequest request,Model model){
        String id  = request.getParameter("store_id");
        //通过HQL语句拿到id=?的商铺，并拿到该店铺下所有的商品
        List<Store> list = storeService.findByShopList(Integer.valueOf(id));
        //返回的为一个Store集合，Store类和Shop类为一对多，Store下的shops为List<Shop>。
        List<Shop> shopList = new ArrayList<Shop>();
//循环遍历拿到每一个shop,添加到一个新的List<Shop>中，用于将数据在前台展示。
        for (Store store:list){
                System.out.println(store.getName());
            for (Shop shop: store.getShops()) {
                System.out.println(shop.getName());
                shopList.add(shop);
            }
        }
        model.addAttribute("list",shopList);
        return "admin/showShop";
    }

}

7. 前台页面跳转

   
   查看的店铺

省略前端代码，主要的是@Query("****************")中语句使用，配合数据库的各种连接能实现复杂的操作。