SSM系列——Mybatis多表操作，注解开发day7-1

Mybatis多表操作

1对1

我们要做的就是在我们需要查询的实体类中设置映射到外键实体类的字段
本文中，我以仓库和管理员作为1对1的关系，以reposid做为外键，那么在我们的主表实体类repository中，将原来的reposid改为private Admin admin进行映射

目录

1.编写数据库

admin表

字段

外键

数据

repository表

字段

数据

2.编写实体类

Admin

package com.example.mybatis2.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Admin {
    private Long id;
    private String name;
    private String password;
    private Integer reposid;
}

Repositroy

package com.example.mybatis2.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Repository {
    private Long id;
    private  Admin admin;
    private String name;
}

3.编写映射文件

adminMapper.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.example.mybatis2.mapper.AdminMapper">
</mapper>

repository.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.example.mybatis2.mapper.RepositoryMapper">
    <resultMap id="resultWrap" type="repos">
        <id column="reposid" property="id"></id>
        <result column="reposname" property="name"></result>
<!--        第一种方式配置-->
<!--        <result column="adminname" property="admin.name"></result>-->
<!--        <result column="uid" property="admin.id"></result>-->
<!--        <result column="reposid" property="admin.reposid"></result>-->
<!--        第二种方式配置，更推荐-->
        <association property="admin" javaType="admin">
            <id column="uid" property="id"></id>
            <result column="adminname" property="name"></result>
            <result column="reposid" property="reposid"></result>
        </association>
    </resultMap>
    <select id="find" resultMap="resultWrap">
        SELECT admin.id        uid,
               admin.name      adminname,
               repository.id   reposid,
               repository.name reposname,
               admin.reposid   reposid
        FROM admin,
             repository
        WHERE admin.reposid = repository.reposid
    </select>
</mapper>

4.编写核心配置文件

sqlMapperConfig.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>
    <properties resource="jdbc.properties"></properties>
    <typeAliases>
        <typeAlias type="com.example.mybatis2.entity.Admin" alias="admin"></typeAlias>
        <typeAlias type="com.example.mybatis2.entity.Repository" alias="repos"></typeAlias>
    </typeAliases>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driverClassName}"/>
                <property name="url"
                          value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="RepositoryMapper.xml"/>
        <mapper resource="AdminMapper.xml"></mapper>
    </mappers>
</configuration>

jdbc.properties

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/syf?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai&zeroDateTimeBehavior=CONVERT_TO_NULL&allowPublicKeyRetrieval=true
username=root
password=syf20020816

5. 测试

package com.example.mybatis2;

import com.example.mybatis2.entity.Repository;
import com.example.mybatis2.mapper.RepositoryMapper;
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.List;

public class Application {
    public static void main(String[] args) throws IOException {
        InputStream resource = Resources.getResourceAsStream("SqlMapperConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resource);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        RepositoryMapper mapper = sqlSession.getMapper(RepositoryMapper.class);
        List<Repository> repositories = mapper.find();
        repositories.forEach(System.out::println);


    }
}

1对多

和1对1实际上差不多，我们修改一下业务场景，想象一个管理员可以有多个仓库即可，所以 我们首先要去数据库里，把我们之前设定好的外键策略取消

1. 修改数据库

修改字段

取消admin表中的reposid字段即可

修改外键策略

我们需要将外键策略改成admin表为父表，repository为从表，以repository表中的reposid字段去关联到admin的id字段即可

2.在admin实体类中添加属性

我们需要在实体类中添加private List<repository> repositories;属性即可

3. 配置adminMapper.xml

我们像1对1那样进行字段映射即可
不同的是我们要配置集合信息
< collection >标签中的映射还是一样的

<collection property="repositories" ofType="repos"></collection>

后面测试，改方法我就不多说了

多对多

那么多对多和1对多也是相似，只是多对多变成了三张表
我们可以思考这样的业务场景，管理员拥有多个等级，等级越高拥有的仓库数量越多，这样就形成了，管理员admin表，仓库repository表，管理员等级adminlevel表

数据库设计

实体类

在实体类adminlevel中增加上描述admin和repository的即可，其实这些东西都和1对多一样

Mybatis注解开发

注解开发可以节省很多配置量，减少编写大量的Mapper映射文件

常见注解

1. Insert：新增
2. Update：更新
3. Delete：删除
4. Select：查询
5. Result：结果集封装
6. One：实现一对一结果集分装
7. Many：实现一对多结果集封装
8. Results：与Result一起使用，封装多个结果集

简单增删改查的改写

这里我们使用之前的环境（对user表进行增删改查的那个，不知道的看day5-2）

1.修改UserMapper接口

package com.example.mybatis.mapper;

import com.example.mybatis.entity.User;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.io.IOException;
import java.util.List;

public interface UserMapper {

    @Select("select * from user")
    List<User> findAll() throws IOException;

    @Select("select * from user where id = #{id},username=#{username},password=#{password}}}")
    List<User> findById(User user);

    //    List<User> findByIds(List<Long> ids);
    @Insert("insert into user value(#{id},#{username},#{password},#{createtime})")
    void insertSave(User user);

    @Delete("delete from user where id = #{id}")
    void deleteOne(Long id);

    @Update(" update user set username=#{username} password=#{password} where id = #{id}")
    void upOne(User user);
}

2.修改核心配置文件sqlMapperConfig,xml

以package标签代替原来的mapper标签

<?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>
    <properties resource="properties/jdbc.properties"></properties>
        <!--    自定义类型处理器-->
    <typeHandlers>
        <typeHandler handler="com.example.mybatis.handler.DateHandler"></typeHandler>
    </typeHandlers>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driverClassName}"/>
                <property name="url"
                          value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>

    <!--    注解开发，加载映射关系-->
    <mappers>
        <!--        指定所在包即可-->
        <package name="com.example.mybatis.mapper"/>
    </mappers>

</configuration>

现在进行测试即可

测试

    //查
    @Test
    public void test1() throws IOException {
        User user = new User();
        user.setUsername("wangwu");
        user.setPassword("lkjjgsa");
        //加载核心配置文件
        InputStream resource = Resources.getResourceAsStream("config/sqlMapperConfi.xml");
        //获得sqlSession工厂对象
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(resource);
        //获取sqlSession回话对象
        SqlSession sqlSession = factory.openSession();
        //执行操作
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> list = mapper.findAll();
        list.forEach(System.out::println);
        //释放sqlSession
        sqlSession.close();
    }

    //增
    @Test
    public void test4() throws IOException {
        User user = new User();
        user.setUsername("wangwu");
        user.setPassword("lkjjgsa");
        //加载核心配置文件
        InputStream resource = Resources.getResourceAsStream("config/sqlMapperConfi.xml");
        //获得sqlSession工厂对象
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(resource);
        //获取sqlSession回话对象
        SqlSession sqlSession = factory.openSession();
        //新增
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        mapper.insertSave(user);
        //提交事务
        sqlSession.commit();
        //执行操作
        List<User> list = mapper.findAll();
        list.forEach(System.out::println);
        //释放sqlSession
        sqlSession.close();
    }

测试成功

多表注解开发

实现复杂关系映射之前我们可以在映射文件中通过配置来实现，使用注解开发后，我们可以使用@Results注解，@Result注解，@One注解，@Many注解组合完成复杂关系的配置

注解	说明
@Results	代替的是标签该注解中可以使用单个@Result注解，也可以使用@Result集合。使用格式: @Results ({@Result () ，@Result () )）或@Results (@Result () )
@Result	代替了标签和标签，column:数据库的列名，property:需要装配的属性名，one:需要使用的@One注解(@Result (one=@One)0 ) )，many:需要使用的@Many注解(@Result (many=@many) ( ) )

示例代码（一对一）

数据库设计参照上面的多表操作

1. 编写实体类

admin实体类

package com.example.mybatis2.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Admin {
    private Long id;
    private String name;
    private String password;
    private int reposid;
    private Repository repository;
}

repository实体类

package com.example.mybatis2.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Repository {
    private Long id;
    private String name;
    private int reposid;
}

2.编写Mapper接口

adminMapper

package com.example.mybatis2.mapper;

import com.example.mybatis2.entity.Admin;
import com.example.mybatis2.entity.Repository;
import org.apache.ibatis.annotations.One;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface AdminMapper {


    @Select("select * from repository")
    @Results({
            @Result(column = "id", property = "id"),
            @Result(column = "name", property = "name"),
            @Result(column = "reposid",property = "reposid"),
            @Result(
                    property = "repository",
                    column = "reposid",
                    javaType = Repository.class,
                    one = @One(select = "com.example.mybatis2.mapper.RepositoryMapper.find")
            )
    })
    List<Admin> find();
}

repositoryMapper

package com.example.mybatis2.mapper;

import com.example.mybatis2.entity.Admin;
import com.example.mybatis2.entity.Repository;
import org.apache.ibatis.annotations.One;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface RepositoryMapper {

    @Select("select * from repository where reposid = #{reposid}")
    List<Repository> find(int reposid);
}

3.编写核心配置文件sqlMapperConfig.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>
    <properties resource="jdbc.properties"></properties>
    <typeAliases>
        <typeAlias type="com.example.mybatis2.entity.Admin" alias="admin"></typeAlias>
        <typeAlias type="com.example.mybatis2.entity.Repository" alias="repos"></typeAlias>
    </typeAliases>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driverClassName}"/>
                <property name="url"
                          value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <package name="com.example.mybatis2.mapper"/>
    </mappers>
</configuration>

4.测试

package com.example.mybatis2;

import com.example.mybatis2.entity.Admin;
import com.example.mybatis2.entity.Repository;
import com.example.mybatis2.mapper.AdminMapper;
import com.example.mybatis2.mapper.RepositoryMapper;
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.List;

public class Application {
    public static void main(String[] args) throws IOException {
        InputStream resource = Resources.getResourceAsStream("SqlMapperConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resource);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        AdminMapper mapper = sqlSession.getMapper(AdminMapper.class);
        List<Admin> admins = mapper.find();
        admins.forEach(System.out::println);
    }
}

一对多，多对多基本相似，也只是思维方式不同，使用注解不同