框架--mybatis框架之表间的关联关系

mybatis框架之多表关联关系(基于注解方式)

表与表之间都有哪些关系?

  • 一对一关系: 一个人对应一个身份证
  • 一对多关系: 一个省份对应多个城市
  • 多对多关系: 一个用户可以有多种角色,一个角色可以有多个用户

代码示例

  • maven依赖
  <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
      <dependency>
          <groupId>mysql</groupId>
          <artifactId>mysql-connector-java</artifactId>
          <version>5.1.8</version>
      </dependency>

      <!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
      <dependency>
          <groupId>org.mybatis</groupId>
          <artifactId>mybatis</artifactId>
          <version>3.4.6</version>
      </dependency>

      <dependency>
          <groupId>log4j</groupId>
          <artifactId>log4j</artifactId>
          <version>1.2.17</version>
      </dependency>

一对一关系示例

  • person表结构
    在这里插入图片描述

  • Card表结构
    在这里插入图片描述

  • entity层的实体类

    • person表映射的实体类Person类:
    /*这是实现一对多关系的entity层
     * 这是数据库表Person表映射的实体类Person
     * 里面有对应数据库表中字段的属性值,
     * 以及为实现一对一关系,
     * 添加一个card对象属性,一个人对应一个卡
     * 并生成它们的set get 和tostring方法
     * */
    public class Person {
    private Integer pid;
    private String pname;
    //作为person的外键
    private Integer cid;
    //验证一对一关系,一个person对应一个卡
    private Card card;
    
    public Integer getPid() {
        return pid;
    }
    
    public void setPid(Integer pid) {
        this.pid = pid;
    }
    
    public String getPname() {
        return pname;
    }
    
    public void setPname(String pname) {
        this.pname = pname;
    }
    
    public Integer getCid() {
        return cid;
    }
    
    public void setCid(Integer cid) {
        this.cid = cid;
    }
    
    public Card getCard() {
        return card;
    }
    
    public void setCard(Card card) {
        this.card = card;
    }
    
    @Override
    public String toString() {
        return "Person{" +
                "pid=" + pid +
                ", pname='" + pname + '\'' +
                ", cid=" + cid +
                ", card=" + card +
                '}';
        }
    }
    
    
    • card表对应的实体类Card类
    /*这是实现一对多关系的entity层
     * 这是数据库表Card表映射的实体类Card
     * 里面有对应数据库表中字段的属性值,
     * 并生成它们的set get 和tostring方法
     * */
    public class Card {
    private Integer cid;
    private String cname;
    
    public Integer getCid() {
        return cid;
    }
    
    public void setCid(Integer cid) {
        this.cid = cid;
    }
    
    public String getCname() {
        return cname;
    }
    
    public void setCname(String cname) {
        this.cname = cname;
    }
    
    @Override
    public String toString() {
        return "Card{" +
                "cid=" + cid +
                ", cname='" + cname + '\'' +
                '}';
        }
    }
    
    
    • dao层的规范接口

    • PersonDao接口

      import com.wdhcr.onetoone.entity.Person;
      import org.apache.ibatis.annotations.One;
      import org.apache.ibatis.annotations.Result;
      import org.apache.ibatis.annotations.Results;
      import org.apache.ibatis.annotations.Select;
      
      public interface PersonDao {
       @Select("select * from person where pid = #{pid}")
         @Results({
          @Result(id = true ,column = "pid" ,property = "pid"),
          @Result(column = "pname" ,property = "pname"),
          //根据cid的 值去card表中查找对应的卡,返回回来
          @Result(column = "cid" ,property = "card" ,one = @One(select = "com.wdhcr.onetoone.dao.CardDao.getCardById"))
      })
          public Person getPersonById(int pid);
      }
      
      
      • CardDao接口
      import com.wdhcr.onetoone.entity.Card;
      import org.apache.ibatis.annotations.Select;
      
      public interface CardDao {
          @Select("select * from card where cid = #{cid}")
       public Card getCardById(int cid);
      }
      
      
  • mybatis框架的全局核心配置文件,注意修改你的库,和用户名密码,添加对应的接口

    <?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" 	"http://mybatis.org/dtd/mybatis-3-config.dtd">
    <configuration>
       <environments default="dev">
           <environment id="dev">
    <!--            设置事务类型-->
            <transactionManager type="JDBC"></transactionManager>
    <!--            设置数据源类型-->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/库"/>
                <property name="username" value="用户名"/>
                <property name="password" value="密码"/>
            </dataSource>
        </environment>
    </environments>
    <!--    注入你的映射文件-->
    <mappers>
       <!--一对一关系,使用注解方法-->
        <mapper class="com.wdhcr.onetoone.dao.CardDao"></mapper>
        <mapper class="com.wdhcr.onetoone.dao.PersonDao"></mapper>
       </mappers>
    </configuration>
    
  • 测试类代码

    import com.wdhcr.onetoone.dao.PersonDao;
    import com.wdhcr.onetoone.entity.Person;
    import org.apache.ibatis.io.Resources;
    import org.apache.ibatis.session.SqlSession;
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.apache.ibatis.session.SqlSessionFactoryBuilder;
    
    import java.io.IOException;
    import java.io.InputStream;
    
    public class Test {
       public static void main(String[] args) throws IOException {
        //加载全局配置文件
        InputStream resourceAsStream = Resources.getResourceAsStream("ConfigMapper.xml");
        //船舰sqlsession工厂
        SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsStream);
        //获取session
        SqlSession sqlSession = build.openSession();
        PersonDao mapper = sqlSession.getMapper(PersonDao.class);
        Person personById = mapper.getPersonById(1);
        //因为生成了tostring方法所以直接打印
        System.out.println(personById);
        }
    }
    
    

一对多关系

  • 表结构

    • Provinces省份表结构
      在这里插入图片描述
    • city表结构
      在这里插入图片描述
  • entity层的实体类

  • Provinces表映射的Provinces实体类

    import java.util.Set;
    /*这是实现一对多关系的entity层
    * 这是数据库表Provinces省份表映射的实体类Provinces
    * 里面有对应数据库表中字段的属性值,
    * 以及为实现一对多关系,一个省对应多个城市,
    * 添加一个类型为city的集合,存储省份里对应有的城市
    * 并生成它们的set get 和tostring方法
    * */
    public class Provinces {
    private Integer pid;
    private String pname;
    private Set<City> cities;
    
    public Integer getPid() {
        return pid;
    }
    
    public void setPid(Integer pid) {
        this.pid = pid;
    }
    
    public String getPname() {
        return pname;
    }
    
    public void setPname(String pname) {
        this.pname = pname;
    }
    
    public Set<City> getCities() {
        return cities;
    }
    
    public void setCities(Set<City> cities) {
        this.cities = cities;
    }
    
    @Override
    public String toString() {
        return "Provinces{" +
                "pid=" + pid +
                ", pname='" + pname + '\'' +
                ", cities=" + cities +
                '}';
       }
    }
    
    
    • city表映射的City实体类
    /*这是实现一对多关系的entity层
     * 这是数据库表city城市表映射的实体类City
     * 里面有对应数据库表中字段的属性值,
     * 并生成它们的set get 和tostring方法
     * */
    public class City {
    private Integer cid;
    private String cname;
    private Integer pid;
    
    public Integer getCid() {
        return cid;
    }
    
    public void setCid(Integer cid) {
        this.cid = cid;
    }
    
    public String getCname() {
        return cname;
    }
    
    public void setCname(String cname) {
        this.cname = cname;
    }
    
    public Integer getPid() {
        return pid;
    }
    
    public void setPid(Integer pid) {
        this.pid = pid;
    }
    
    @Override
    public String toString() {
        return "City{" +
                "cid=" + cid +
                ", cname='" + cname + '\'' +
                ", pid=" + pid +
                '}';
      }
    }
    
  • dao层的规范接口

    • ProvincesDao接口

      import com.wdhcr.onetomany.entity.Provinces;
      import org.apache.ibatis.annotations.Many;
      import org.apache.ibatis.annotations.Result;
      import org.apache.ibatis.annotations.Results;
      import org.apache.ibatis.annotations.Select;
      
      public interface ProvincesDao {
      
        @Select("select * from Provinces where pid = #{pid}")
        @Results({
      	        @Result(id = true,column = "pid" , property = "pid"),
      	        @Result(column = "pname" , property = "pname"),
      	      //根据pid去city表中找pid对应的所有城市,放在一个set集合中,返回回来
      	     @Result(column = "pid" ,property = "cities" ,many = @Many(select = "com.wdhcr.onetomany.dao.CityDao.getCityByid"))
        })
         public Provinces getProvincesById(int pid);
      }
      
    • CityDao接口

      import com.wdhcr.onetomany.entity.City;
      import org.apache.ibatis.annotations.Select;
      
      import java.util.Set;
      
      public interface CityDao {
          @Select("select * from city where pid = #{pid}")
          public Set<City> getCityByid(int pid);
      }
      
      
  • mybatis全局核心配置文件

    <?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
    <configuration>
        <environments default="dev">
           <environment id="dev">
    <!--            设置事务类型-->
                <transactionManager type="JDBC"></transactionManager>
    <!--            设置数据源类型-->
                <dataSource type="POOLED">
                    <property name="driver" value="com.mysql.jdbc.Driver"/>
                   <property name="url" value="jdbc:mysql://localhost:3306/库"/>
                  <property name="username" value="用户名"/>
                 <property name="password" value="密码"/>
    	        </dataSource>
    	    </environment>
      </environments>
    <!--    注入你的映射文件-->
    	    <mappers>
       
         <!--一对多-->
         <mapper class="com.wdhcr.onetomany.dao.ProvincesDao"></mapper>
         <mapper class="com.wdhcr.onetomany.dao.CityDao"></mapper>
    	   
      </mappers>
    </configuration>
    
  • 测试类代码

    import com.wdhcr.onetomany.dao.ProvincesDao;
    import com.wdhcr.onetomany.entity.City;
    import com.wdhcr.onetomany.entity.Provinces;
    import org.apache.ibatis.io.Resources;
    import org.apache.ibatis.session.SqlSession;
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.apache.ibatis.session.SqlSessionFactoryBuilder;
    
    import java.io.IOException;
    import java.io.InputStream;
    /*
    * 这是实现一对一关系的测试类
    * */
    public class Test {
    public static void main(String[] args) throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("ConfigMapper.xml");
        SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = build.openSession();
        ProvincesDao mapper = sqlSession.getMapper(ProvincesDao.class);
        Provinces provincesById = mapper.getProvincesById(1);
        System.out.println(provincesById.getPname());
        for (City city : provincesById.getCities()) {
            System.out.println(city);
           }
        }
    }
    

多对多关系

  • 表结构

    • useres表结构
      在这里插入图片描述
    • role表结构
      在这里插入图片描述
    • 中间表u_r结构
      在这里插入图片描述
  • entity层的实体类

    • useres表映射的Useres实体类

      import java.util.Set;
      
      public class Useres {
          private Integer uid;
          private String uname;
          //每个用户有多个角色
          private Set<Role> roles;
      
          public Integer getUid() {
              return uid;
          }
      
          public void setUid(Integer uid) {
              this.uid = uid;
          }
      
         public String getUname() {
             return uname;
          }
      
          public void setUname(String uname) {
              this.uname = uname;
          }
      
        public Set<Role> getRoles() {
             return roles;
          }
      
         public void setRoles(Set<Role> roles) {
             this.roles = roles;
          }
      
        @Override
         public String toString() {
            return "Useres{" +
                     "uid=" + uid +
                     ", uname='" + uname + '\'' +
                      ", roles=" + roles +
                      '}';
      	   }
      }
      
      • role表映射的实体类Role
      import java.util.Set;
      
      public class Role {
          private Integer rid;
          private String rname;
          //每个角色有多个用户
          private Set<Useres> useres;
      
         public Integer getRid() {
           return rid;
         }
      
         public void setRid(Integer rid) {
           this.rid = rid;
        }
      
         public String getRname() {
           return rname;
          }
      
         public void setRname(String rname) {
            this.rname = rname;
        }
      
         public Set<Useres> getUseres() {
            return useres;
         }
      
          public void setUseres(Set<Useres> useres) {
           this.useres = useres;
        }
      
        @Override
         public String toString() {
           return "Role{" +
      	            "rid=" + rid +
      	            ", rname='" + rname + '\'' +
      	          ", useres=" + useres +
      	           '}';
          }
      }
      
  • dao层的规范接口

    • RoleDao接口

      import com.wdhcr.manytomany.entity.Role;
      import org.apache.ibatis.annotations.Many;
      import org.apache.ibatis.annotations.Result;
      import org.apache.ibatis.annotations.Results;
      import org.apache.ibatis.annotations.Select;
      
      import java.util.Set;
      
      public interface RoleDao {
          //根据用户的id查询它所有的角色,以一个set集合的方式返回
          @Select("select * from role where rid in (select r_id from u_r where u_id = #{uid})")
          public Set<Role> getRoleById(int uid);
      
      
          //查询每个角色对应的用户有哪些
          @Select("select * from role where rid = #{rid}")
          @Results({
                  @Result(id = true,column = "rid",property = "rid"),
      	         @Result(column = "rname",property = "rname"),
               @Result(column = "rid",property = "useres",many = @Many(select = "com.wdhcr.manytomany.dao.UseresDao.getUseresByRid"))
        })
         public Role getRolebyCid(int cid);
      }
      
      
    • UseresDaos接口

      import com.wdhcr.manytomany.entity.Useres;
      import org.apache.ibatis.annotations.Many;
      import org.apache.ibatis.annotations.Result;
      import org.apache.ibatis.annotations.Results;
      import org.apache.ibatis.annotations.Select;
      
      import java.util.Set;
      
      public interface UseresDao {
      
          //查询指定用户对应的个字段的值,以及它的多个角色
          @Select("select * from useres where uid = #{uid}")
        @Results({
                  @Result(id = true,column = "uid" ,property = "uid"),
                 @Result(column = "uname",property = "uname"),
      	        @Result(column = "uid", property = "roles",many = @Many(select = "com.wdhcr.manytomany.dao.RoleDao.getRoleById"))
         })
         public Useres getUserById(int uid);
      
      
         //根据指定的jueseid查询都有哪些用户是个角色
      
         @Select("select * from useres where uid in (select u_id from u_r where r_id = #{cid})")
          public Set<Useres> getUseresByRid(int cid);
      }
      
  • 全局核心配置文件

      ```
      <?xml version="1.0" encoding="UTF-8"?>
      <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
      <configuration>
          <environments default="dev">
              <environment id="dev">
      <!--            设置事务类型-->
                  <transactionManager type="JDBC"></transactionManager>
      <!--            设置数据源类型-->
                <dataSource type="POOLED">
                    <property name="driver" value="com.mysql.jdbc.Driver"/>
     		         <property name="url" value="jdbc:mysql://localhost:3306/test"/>
                  <property name="username" value="root"/>
      	        <property name="password" value="123456"/>
              </dataSource>
    	  </environment>
    </environments>
      <!--    注入你的映射文件-->
      <mappers>
          <!--多对多关系-->
          <mapper class="com.wdhcr.manytomany.dao.RoleDao"></mapper>
       <mapper class="com.wdhcr.manytomany.dao.UseresDao"></mapper>
     </mappers>
    
    ```
  • 测试类代码

    import com.wdhcr.manytomany.dao.RoleDao;
    import com.wdhcr.manytomany.dao.UseresDao;
    import com.wdhcr.manytomany.entity.Role;
    import com.wdhcr.manytomany.entity.Useres;
    import org.apache.ibatis.io.Resources;
    import org.apache.ibatis.session.SqlSession;
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.apache.ibatis.session.SqlSessionFactoryBuilder;
    
    import java.io.IOException;
    import java.io.InputStream;
    import java.util.Set;
    
    /*
    * 这是实现多对多关系的测试类
    * */
    public class Test {
    public static void main(String[] args) throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("ConfigMapper.xml");
        SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = build.openSession();
        //一个用户对应多个角色
        UseresDao mapper = sqlSession.getMapper(UseresDao.class);
        Useres userById = mapper.getUserById(1);
        Set<Role> roles = userById.getRoles();
        System.out.println(userById.getUid()+"\t"+userById.getUname());
        for (Role role : roles) {
            System.out.println(role);
        }
    
        //一个角色对应多个用户
        RoleDao mapper1 = sqlSession.getMapper(RoleDao.class);
        Role rolebyCid = mapper1.getRolebyCid(2);
        Set<Useres> useres = rolebyCid.getUseres();
        System.out.println(rolebyCid.getRid()+"\t"+rolebyCid.getRname());
        for (Useres usere : useres) {
            System.out.println(useres);
           }
        }
    }
    
  • 好了,以上就是使用注解的方式实现一对一,一对多,多对多关系

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值