springDataJpa聯表查詢之多對多

Mr_LiYYD發表於2019-08-21
Spring

springDataJpa聯表查詢之多對多

場景模擬

  • 一個使用者User可以有多個角色Role 一個角色可以有多個使用者

分析

  • 使用者表
  • 角色表
  • 使用者角色中間表 userId,roleId兩個欄位都是外來鍵引用

UserEntity

package cn.liyang.jpa.entity;

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

/**
 * @author liyang
 * @date 2019/8/21 17:44
 * @description:
 */
@Entity
@Table(name = "sys_user")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "user_id")
    private Long userId;
    @Column(name = "user_name")
    private String userName;
    @Column(name = "age")
    private Integer age;
    /*
    *一個使用者可以有多個角色
    *  多對多配置
    * */
    //targetEntity代表要連線的表的位元組碼檔案
    @ManyToMany (targetEntity = Role.class)
    //使用者與角色形成的中間表
    @JoinTable(name = "sys_user_role",   //中間表的名字
            //當前物件在中間表中的外來鍵配置name : 外來鍵在中間表中的欄位名字 referencedColumnName : 引用表的欄位名字
            joinColumns = {@JoinColumn(name = "sys_user_id", referencedColumnName = "user_id")} ,
            //目標連結串列物件的外來鍵配置
            inverseJoinColumns = {@JoinColumn(name = "sys_role_id",referencedColumnName = "role_id")}
    )
    private Set<Role> roles = new HashSet<>();

    @Override
    public String toString () {
        return "User{" + "userId=" + userId + ", userName='" + userName + '\'' + ", age=" + age + ", roles=" + roles + '}';
    }

    public Long getUserId () {
        return userId;
    }

    public void setUserId (Long userId) {
        this.userId = userId;
    }

    public String getUserName () {
        return userName;
    }

    public void setUserName (String userName) {
        this.userName = userName;
    }

    public Integer getAge () {
        return age;
    }

    public void setAge (Integer age) {
        this.age = age;
    }

    public Set<Role> getRoles () {
        return roles;
    }

    public void setRoles (Set<Role> roles) {
        this.roles = roles;
    }
}

RoleEntity

package cn.liyang.jpa.entity;

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

/**
 * @author liyang
 * @date 2019/8/21 17:31
 * @description:
 */
@Entity
@Table(name = "sye_role")
public class Role {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "role_id")
    private Long roleId;
    @Column(name = "role_name")
    private String name;

    public Set<User> getUsers () {
        return users;
    }

    public void setUsers (Set<User> users) {
        this.users = users;
    }

    //多對多表查詢
    @ManyToMany(targetEntity = User.class)
    @JoinTable(name = "sys_user_role",
            joinColumns = {@JoinColumn(name = "sys_role_id",referencedColumnName = "role_id")},
            inverseJoinColumns = {@JoinColumn(name = "sys_user_id",referencedColumnName = "user_id")}
    )
    private Set<User> users = new HashSet<>();

    @Override
    public String toString () {
        return "Role{" + "roleId=" + roleId + ", name='" + name + '\'' + ", users=" + users + '}';
    }

    public Long getRoleId () {
        return roleId;
    }

    public void setRoleId (Long roleId) {
        this.roleId = roleId;
    }

    public String getName () {
        return name;
    }

    public void setName (String name) {
        this.name = name;
    }
}

applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:aop="http://www.springframework.org/schema/aop"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:jdbc="http://www.springframework.org/schema/jdbc" xmlns:tx="http://www.springframework.org/schema/tx"
       xmlns:jpa="http://www.springframework.org/schema/data/jpa"
       xmlns:task="http://www.springframework.org/schema/task"
       xsi:schemaLocation="
http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd
http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd
http://www.springframework.org/schema/data/jpa
http://www.springframework.org/schema/data/jpa/spring-jpa.xsd">
    <!--資料來源-->
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="com.mysql.jdbc.Driver"/>
        <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/springdata"/>
        <property name="password" value="root"/>
        <property name="user" value="root"/>
    </bean>
    <!--entityManager實體工廠-->
    <bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
        <!--指定資料來源-->
        <property name="dataSource" ref="dataSource"/>
        <!--配對應的實體類-->
        <property name="packagesToScan" value="cn.liyang.jpa.entity"/>
        <!--配置供應商的介面卡-->
        <property name="jpaVendorAdapter">
            <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
                <property name="showSql" value="true"/>
                <property name="generateDdl" value="false"/>
                <property name="database" value="MYSQL"/>
            </bean>
        </property>
        <property name="jpaProperties">
            <props>
                <prop key="hibernate.hbm2ddl.auto">update</prop>
            </props>
        </property>
    </bean>
    <!--事務管理 配置事務-->
    <bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
        <property name="entityManagerFactory" ref="entityManagerFactory"/>
    </bean>
    <tx:advice id="txAvice" transaction-manager="transactionManager">
        <tx:attributes>
            <tx:method name="save*" propagation="REQUIRED"/>
            <tx:method name="insert*" propagation="REQUIRED"/>
            <tx:method name="update*" propagation="REQUIRED"/>
            <tx:method name="delete*" propagation="REQUIRED"/>
            <tx:method name="get*" read-only="true"/>
            <tx:method name="find*" read-only="true"/>
            <tx:method name="*" propagation="REQUIRED"/>
        </tx:attributes>
    </tx:advice>
    <aop:config>
        <aop:pointcut id="pointcut" expression="execution(* cn.liyang.jpa.service.*.*(..))"/>
        <aop:advisor advice-ref="txAvice" pointcut-ref="pointcut"/>
    </aop:config>
    <!--dao包掃描器-->
    <jpa:repositories base-package="cn.liyang.jpa.dao"
                      transaction-manager-ref="transactionManager"
                      entity-manager-factory-ref="entityManagerFactory"/>

</beans>

測試類ManyToMany

package cn.liyang.jpa;

import cn.liyang.jpa.dao.RoleDao;
import cn.liyang.jpa.dao.UserDao;
import cn.liyang.jpa.entity.Role;
import cn.liyang.jpa.entity.User;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.annotation.Rollback;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.transaction.annotation.Transactional;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class ManyToMany {
    @Autowired
    private UserDao userDao;
    @Autowired
    private RoleDao roleDao;
    @Test
    @Transactional  //開啟事務
    @Rollback(false)    //拒絕回滾
    public void testAdd(){//新建角色,新建使用者同時新增
        User user = new User();
        user.setAge( 18 );
        user.setUserName( "liyang02" );
        Role role = new Role();
        role.setName( "程式設計師02" );
        user.getRoles().add( role );
        userDao.save( user );
        roleDao.save( role );
    }
    @Test
    @Transactional  //開啟事務
    @Rollback(false)    //拒絕回滾
    public void testAdd02(){//查詢使用者,建立一個新的角色型別,併為該使用者新增這個角色
        User one = userDao.findOne( 2L );
        Role role = new Role();
        role.setName( "產品經理" );
        one.getRoles().add( role );
        roleDao.save( role );
    }
    @Test
    @Transactional  //開啟事務
    @Rollback(false)    //拒絕回滾
    public void testAdd03(){//查詢一條已經存在的使用者和一條角色,併為該使用者賦予這個角色
        Role role = roleDao.findOne( 2L );
        User user = userDao.findOne( 1L );
        user.getRoles().add( role );
    }
    @Test
    @Transactional  //開啟事務
    @Rollback(false)    //拒絕回滾
    public void testAdd04(){//新建一個使用者,併為該使用者新增多個角色
        Role role1 = roleDao.findOne( 1L );
        Role role2 = roleDao.findOne( 2L );
        Role role3 = roleDao.findOne( 3L );
        User user = new User();
        user.setUserName( "鄭愁予" );
        user.getRoles().add( role1 );
        user.getRoles().add( role2 );
        user.getRoles().add( role3 );
        userDao.save( user );//這是新建的使用者記得儲存
    }
}

總結

  • 核心程式碼,在使用者Entity中建立一個角色集合,在角色Entity中建立一個使用者集合

    private Set<User> users = new HashSet<>();

    private Set<Role> roles = new HashSet<>();

  • 要建立一箇中間表,表中新增外來鍵,需要指明新增外來鍵引用的是那一張表,哪一個欄位,這些資訊在集合屬性註解中來完成

     /*
 *一個使用者可以有多個角色
 *  多對多配置
 * */
 //targetEntity代表要連線的表的位元組碼檔案
 @ManyToMany (targetEntity = Role.class)
 //使用者與角色形成的中間表
 @JoinTable(name = "sys_user_role",   //中間表的名字
         //當前物件在中間表中的外來鍵配置name : 外來鍵在中間表中的欄位名字 referencedColumnName : 引用表的欄位名字
         joinColumns = {@JoinColumn(name = "sys_user_id", referencedColumnName = "user_id")} ,
         //目標連結串列物件的外來鍵配置
         inverseJoinColumns = {@JoinColumn(name = "sys_role_id",referencedColumnName = "role_id")}
 )

相關文章