spring boot mybatis一对一、一对多查询应用

这几天入门java，一直在看关联映射，琢磨了挺长时间，为了其他网友今后少踩坑。先总结一下

有三张表：user 、book、address

DROP TABLE IF EXISTS `book`;
CREATE TABLE `book` (
  `bookid` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `userid` int(11) DEFAULT NULL,
  `bookname` varchar(255) DEFAULT NULL,
  `totalpage` int(10) DEFAULT NULL,
  `price` int(11) DEFAULT NULL,
  PRIMARY KEY (`bookid`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `userid` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(25) NOT NULL DEFAULT '' COMMENT '用户名',
  `userpassword` varchar(255) DEFAULT NULL,
  `userage` varchar(255) DEFAULT NULL,
  `useremail` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`userid`)
) ENGINE=InnoDB AUTO_INCREMENT=107 DEFAULT CHARSET=utf8 COMMENT='管理员列表';

DROP TABLE IF EXISTS `address`;
CREATE TABLE `address` (
  `addressid` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `userid` int(11) DEFAULT NULL,
  `province` varchar(32) DEFAULT NULL,
  `city` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`addressid`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

user与book是一对多

user与address是一对一

新建完项目，我的目录结构是这样的。

 1、首先数据库连接及配置：

打开application.properties

server.port=8080
server.servlet.context-path=/demo
#数据库配置
spring.datasource.url=jdbc:mysql://localhost:3306/ldzy?useUnicode=true&characterEncoding=utf8&useSSL=false
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
#加载Mapper.xml路径
mybatis.mapper-locations=classpath:mapper/*Mapper.xml 
#设置config-location位置
mybatis.config-location=classpath:mybatis-config.xml
#设置type-aliases-package中指定实体扫描包类让mybatis自定扫描到自定义的路径
mybatis.type-aliases-package=com.dm.batis.bean

#热部署生效
spring.devtools.restart.enabled=true
#设置重启的目录
spring.devtools.restart.additional-paths=src/main/java
#classpath目录下的WEB-INF文件夹内容修改不重启
spring.devtools.restart.exclude=WEB-INF/**

mybatis-config.xml

<?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>
    <settings>
        <!-- Globally enables or disables any caches configured in any mapper under this configuration -->
        <setting name="cacheEnabled" value="true"/>
        <!-- Sets the number of seconds the driver will wait for a response from the database -->
        <setting name="defaultStatementTimeout" value="3000"/>
        <!-- Enables automatic mapping from classic database column names A_COLUMN to camel case classic Java property names aColumn -->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
        <!-- Allows JDBC support for generated keys. A compatible driver is required.
        This setting forces generated keys to be used if set to true,
         as some drivers deny compatibility but still work -->
        <setting name="useGeneratedKeys" value="true"/>
    </settings>
    <!-- Continue going here -->
</configuration>

2、新建user、book、address实体类

在com.dm.batis.bean创建

package com.dm.batis.bean;

import java.util.List;

public class User {
    private int userId;
    private String userName;
    private String userPassword;
    private int userAge;
    private String userEmail;
    private Address userAddress;//用户地址
    private List<Book> books;//喜欢的书

    public List<Book> getBooks() {
        return books;
    }

    public void setBooks(List<Book> books) {
        this.books = books;
    }

    public Address getAddress() {
        return userAddress;
    }

    public void setAddress(Address address) {
        this.userAddress = address;
    }

    public int getUserAge() {
        return userAge;
    }

    public void setUserAge(int userAge) {
        this.userAge = userAge;
    }

    public String getUserEmail() {
        return userEmail;
    }

    public void setUserEmail(String userEmail) {
        this.userEmail = userEmail;
    }

    public int getUserId() {
        return userId;
    }

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

    public String getUserName() {
        return userName;
    }

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

    public String getUserPassword() {
        return userPassword;
    }

    public void setUserPassword(String userPassword) {
        this.userPassword = userPassword;
    }
}

package com.dm.batis.bean;

public class Book
{
    private int bookId;
    private String bookName;
    private int totalPage;
    private int price;

    public int getBookId() {
        return bookId;
    }

    public void setBookId(int bookId) {
        this.bookId = bookId;
    }

    public String getBookName() {
        return bookName;
    }

    public void setBookName(String bookName) {
        this.bookName = bookName;
    }

    public int getTotalPage() {
        return totalPage;
    }

    public void setTotalPage(int totalPage) {
        this.totalPage = totalPage;
    }

    public int getPrice() {
        return price;
    }

    public void setPrice(int price) {
        this.price = price;
    }

}

package com.dm.batis.bean;

public class Address
{
    private int addressId;
    private String province;
    private String city;

    public int getAddressId() {
        return addressId;
    }

    public void setAddressId(int addressId) {
        this.addressId = addressId;
    }

    public String getProvince() {
        return province;
    }

    public void setProvince(String province) {
        this.province = province;
    }

    public String getCity() {
        return city;
    }

    public void setCity(String city) {
        this.city = city;
    }

    @Override
    public String toString()
    {
        return this.province+this.city;
    }
}

3、创建mapper.xml文件

resources目录下面建立目录mapper

里面新建三个配置文件UserMapper.xml,AddressMapper.xml,BookMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.dm.batis.mapper.UserMapper">
<!--    Association：处理单一的关联对象 处理单一属性的关联关系-->
<!--    property：注入给员工中哪个属性-->
<!--    select：发送哪一条sql语句-->
<!--    javaType：把sql语句查询出的结果集，封装给哪个类的对象（可以省略）-->
<!--    column：查询员工的结果集中，用哪个列的值作为条件去查询关联的对象column作为select查询条件的参数，但是这个条件一般都是外键-->
<!--OneToOne-->
    <resultMap id="UserBaseMap" type="User">
        <id column="userId" property="userId" />
        <result column="userName" property="userName"/>
        <result column="userPassword" property="userPassword"/>
        <result column="userAge" property="userAge"/>
        <result column="user_pwd" property="userPwd"/>
        <result column="userEmail" property="userEmail" />


        <!--        第二种方案  -->
        <association property="userAddress" column="UserId"  javaType="Address">
            <id property="addressId" column="addressId" />
            <result property="province" column="province" />
            <result property="city" column="city" />
        </association>

        <!--        第一种方案  -->
<!--        <association property="userAddress" javaType="Address"-->
<!--                     column="UserId" select="com.dm.batis.mapper.AddressMapper.getAddressById"></association>-->
    </resultMap>
    <!--        第一种方案  -->
<!--    <select id="UserAddressById" parameterType="int" resultMap="UserBaseMap">-->
<!--        SELECT * FROM user  WHERE userId=#{userId}-->
<!--    </select>-->
    <!-- 第二种方案 -->

    <select id="UserAddressById" parameterType="int" resultMap="UserBaseMap">
        SELECT * FROM user U
        inner JOIN address A ON U.userId=A.userId WHERE
        A.userId=#{userId}
    </select>




    <!--OneToMany-->
    <resultMap id="UserBookMap" type="User">
        <id column="userId" property="userId" />
        <result column="userName" property="userName"/>
        <result column="userPassword" property="userPassword"/>
        <result column="userAge" property="userAge"/>
        <result column="user_pwd" property="userPwd"/>
        <result column="userEmail" property="userEmail" />
        <!--第一种方案-->
<!--        <collection property="books" ofType="Book" column="userId"-->
<!--                   select="com.dm.batis.mapper.BookMapper.UserBookList"></collection>-->

        <collection property="books" ofType="Book" column="userId">
            <id property="bookId" column="bookId"/>
            <result property="bookName" column="bookName"/>
            <result property="totalPage" column="totalPage"/>
            <result property="price" column="price"/>
        </collection>

    </resultMap>


<!--    <select id="UserBookById" parameterType="int" resultMap="UserBookMap">-->
<!--        SELECT * FROM user  WHERE userId=#{userId}-->
<!--    </select>-->

    <select id="UserBookById" parameterType="int" resultMap="UserBookMap">
        SELECT * FROM user U
        inner JOIN book B ON U.userId=B.userId WHERE
        B.userId=#{userId}
    </select>

</mapper>

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >

<mapper namespace="com.dm.batis.mapper.BookMapper">
    <resultMap id="BookBaseMap" type="Book">
        <id property="bookId" column="bookId"/>
        <result property="bookName" column="bookName"/>
        <result property="totalPage" column="totalPage"/>
        <result property="price" column="price"/>
    </resultMap>

    <select id="UserBookList" parameterType="int" resultType="Book">
        SELECT * FROM book
        WHERE userid = #{userId}
    </select>

</mapper>

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.dm.batis.mapper.AddressMapper">
    <resultMap id="AddressBaseMap" type="Address">
        <id column="addressId" property="addressId" />
        <result column="province" property="province"/>
        <result column="city" property="city"/>
    </resultMap>

    <select id="getAddressById" parameterType="int" resultMap="AddressBaseMap">
        SELECT * FROM address
        WHERE userid = #{userId}
    </select>



</mapper>

4、创建mapper接口文件

在包目录下面建立目录mapper

里面新建三个配置文件UserMapper，AddressMapper，BookMapper

package com.dm.batis.mapper;

import com.dm.batis.bean.Book;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.mapping.FetchType;

import com.dm.batis.bean.User;

import java.util.List;

@Mapper
public interface UserMapper
{


//一对一
    public User UserAddressById(int userId);

//一对多
    public User UserBookById(int userId);

}

package com.dm.batis.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;


import com.dm.batis.bean.Book;
import org.springframework.data.repository.query.Param;

@Mapper
public interface BookMapper
{
    @Select("select * from book where userId = #{userId}")
    public List<Book> getBooksByUserId(int userId);



    public List<Book> UserBookList(int userId);

}

package com.dm.batis.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;


import com.dm.batis.bean.Address;
@Mapper
public interface AddressMapper
{
    @Select("select * from address where userId=#{userId}")
    public Address getAddressByUserId(int userId);


    public Address getAddressById(int userId);
}

5、在包目录下面建立目录controller

里面新建index

package com.dm.batis.controller;

import com.dm.batis.bean.User;
import com.dm.batis.mapper.BookMapper;
import com.dm.batis.mapper.UserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;


@RestController
public class Index {
    @Autowired
    private UserMapper userMapper;

    @Autowired
    private BookMapper bookMapper;

    @RequestMapping("/index")
    public User index(){
        User user = userMapper.UserAddressById(1);
        //User user=userMapper.UserBookById(1);

        return user;
    }
}

6、测试

一对一

一对多

至此演示结束，因本人也是才接触java学习，有不足之处，请见谅！！

欢迎加微信：598194221，共同探讨学习java交流心得！