SpringBoot Data JPA 关联表查询的方法

springboot data jpa实现一对多、多对一关联表查询

开发环境

idea 2017.1
java1.8
springboot 2.0
mysql 5.x

功能需求

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

表结构

SpringBoot Data JPA 关联表查询的方法

tb_shop

SpringBoot Data JPA 关联表查询的方法

tb_store

实体类，通过注解实现

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()方法；

									}

商品类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()方法；

									}

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);

									}

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);

									  }

									}

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";

									  }

									}