优化数据库查询：单表查询与关联查询的权衡-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_43189971/article/details/125450710

问题一：为什么要使用嵌套连接

解析：

现在查询一个user的id查询出所有信息.如果不考虑关联查询，我们会先根据user的id在user表中查询出name,age然后设置给User类的时候，再根据该user的id在user_orders表中查询出所有订单并设置给User类。这样的话，在底层最起码调用两次查询语句，得到需要的信息，然后再组装User对象。

这种关联的嵌套查询，有一个非常好的作用就是：可以重用select语句，通过简单的select语句之间的组合来构造复杂的对象。想如上的两个select完全可以独立使用。

问题二：多表查询的理解

1、单表查询更利于后续的维护。

在实际开发场景中，在代码初步开发阶段（如果摊上一个不太靠谱的产品），业务发生变动，某张表的结构发生变动，很可能整个join查询都变得不可用，复杂的关联查询，在修改时，基本等于推倒重来。

但是如果我们使用了单表查询，拆成上诉例子中的三个步骤，我们可能只需要修改其中的一个步骤即可，比较利于维护。

2、代码可复用性高

这个不用多说，join联表的SQL，基本不太可能被复用，但是拆分后的单表查询，比如上面例子中，我查询出tab数据，任何地方组装需要tab数据，我都不需要再次做相关查询，直接使用。

3、效率问题

join联表查询，小表驱动大表，通过索引字段进行关联。如果表记录比较少的话，效率还是OK的，有时效率超过单表查询。但是如果数据量上去，多表查询是笛卡尔乘积方式，需要检索的数据是几何倍上升的。另外多表查询索引设计上也考验开发者的功底，索引设计不合理，大数据量下的多表查询，很可能把数据库拖垮。

相比而言，拆分成单表查询+代码上组装，业务逻辑更清晰，优化更方便，单个表的索引设计上也更简单。用多几行代码，多几次数据库查询换取这些优点，还是很值得的。

4、减少冗余字段的查询

在很多业务中，我们可能对某条记录只需要查询一次，此时如何使用关联查询，则不可避免的需要重复地访问一部分数据，从而可能会加剧网络和内存的消耗。

5、缓存利用率更高

比如上面查询中的tag是不常变动的数据，缓存下来，每次查询就可以跳过第一条查询语句。而关联查询，任何一张表的数据变动都会引起缓存结果的失效，缓存利用率不会很高。

6、其他

数据库资源比较宝贵，很多系统的瓶颈就在数据库上，很多复杂的逻辑我们在Service做，不在数据库处理会更好。

在后续数据量上去，需要分库分表时，Join查询更不利于分库分表，目前MySQL的分布式中间件，跨库join表现不良。

单表查询+代码上组装相当于解耦，现在开发中，我们常常使用各种ORM框架，不知道你的联查orm给你搞成了什么样,你是很难直接优化。

以上理由，强烈推荐在今后的开发中，尽可能的使用单表查询+代码上组装的方式。

问题三：为什么mysql不建议执行超过3表以上的多表关联查询

https://cloud.tencent.com/developer/article/1443320

1.多表关联查询效率高点还是多次单表查询效率高
2.对关联查询进行分解

一：先配置全局关联 mybatis-config.xml

 <!--将mapper文件与核心配置文件进行关联-->
    <mappers>
      <!--  <mapper resource="com/huang/maven/mapper/UserMapper.xml"></mapper>
        <mapper resource="com/huang/maven/mapper/ActorMapper.xml"></mapper>
        <mapper resource="com/huang/maven/mapper/CardMapper.xml"></mapper>
        <mapper resource="com/huang/maven/mapper/OrderMapper.xml"></mapper>-->
        <package name="com.huang.maven.mapper"/>
    </mappers>

1. -一对一查询

UserMapper.java

 List<User> selectAllUser ();

UserMapper.xml


    <!--一对一  user和card -->
    <resultMap id="userOneToOneMap" type="user">
        <id column="id" property="id"/>
        <result column="username" property="username"/>
        <result column="birthday" property="birthday"/>
        <result column="sex" property="sex"/>
        <result column="address" property="address"/>

        <association property="card" column="id" javaType="card">
            <id column="cid" property="cid"/>
            <result column="cnumid" property="cnumid"/>
            <result column="uid" property="uid"/>
        </association>
    </resultMap>


    <select id="selectAllUser" resultMap="userOneToOneMap">
        select u.*, c.cid, c.cnumid, c.uid
        from user u,
             card c
        where u.id = c.uid
    </select>

Test类

    /*一对一*/
    @Test
    public   void  showInfo02() throws IOException {

        UserMapper userMapper = (UserMapper) MyBatisUtils.getMapper(UserMapper.class);
        List<User> userList = userMapper.selectAllUser();
        for (User u:userList){
            System.out.println(u);
        }

    }

2.一对多查询

UserMapper.java

List<User> selectUserOrderList();

UserMapper.xml



<resultMap id="userOneToMany" type="user">
        <id column="id" property="id"/>
        <result column="username" property="username"/>
        <result column="birthday" property="birthday"/>
        <result column="sex" property="sex"/>
        <result column="address" property="address"/>

        <collection column="id" property="ordersList" ofType="order">
            <id column="oid" property="oid"/>
            <result column="oname" property="oname"/>
            <result column="onum" property="onum"/>
            <result column="uid" property="uid"/>
        </collection>
    </resultMap>


    <select id="selectUserOrderList" resultMap="userOneToMany">
        select u.*, o.*
        from user u,
             `order` o
        where u.id = o.uid
    </select>

Test类

 /*一对多*/
    @Test
    public  void  showInfo03() throws  IOException{
        UserMapper userMapper = (UserMapper) MyBatisUtils.getMapper(UserMapper.class);
        List<User> userList = userMapper.selectUserOrderList();
        for (User u: userList){
            System.out.println(u);
        }
    }

3.嵌套一对一

UserMapper.java

List<User> selectQianOneToOne();

CardMapper.java

List<Card> selectCard (Integer uid);

UserMapper.xml

   <!--嵌套一对一-->

    

    <resultMap id="QianOneToone" type="user">
        <id column="id" property="id"/>
        <result column="username" property="username"/>
        <result column="birthday" property="birthday"/>
        <result column="sex" property="sex"/>
        <result column="address" property="address"/>
        <association property="card" column="id" select="com.huang.maven.mapper.CardMapper.selectCard" javaType="card"/>
    </resultMap>

    <select id="selectQianOneToOne" resultMap="QianOneToone">
        select *
        from user;
    </select>

CardMapper.xml

   <select id="selectCard" resultType="card">
        select * from card where uid= #{uid};
    </select>

Test类

  @Test
    public   void  showInfo08() throws IOException {
        UserMapper userMapper  = (UserMapper) MyBatisUtils.getMapper(UserMapper.class);
        List<User> userList = userMapper.selectQianOneToOne();
        for (User u : userList){
            System.out.println(u);
        }
    }

4.嵌套一对多查询

UserMapper.java

List<User> selectQianOneToMany();

OrderMapper.java

List<Order> selectOrder(Integer uid);

UserMapper.xml

    <!--user和order是一对多的关系-->
    <resultMap id="QianOneToMany" type="user">
        <id column="id" property="id"/>
        <result column="username" property="username"/>
        <result column="birthday" property="birthday"/>
        <result column="sex" property="sex"/>
        <result column="address" property="address"/>
        <collection property="ordersList" column="id" select="com.huang.maven.mapper.OrderMapper.selectOrder"
                    ofType="order"/>
    </resultMap>

    <select id="selectQianOneToMany" resultMap="QianOneToMany">
        select *
        from user;
    </select>

OrderMapper.xml

 <select id="selectOrder" resultType="order">
        select * from `order` where uid=#{uid}
    </select>

Test类

/*嵌套一对多*/
    @Test
    public  void  showInfo09() throws IOException{
        OrderMapper orderMapper = (OrderMapper) MyBatisUtils.getMapper(OrderMapper.class);
        List<Order> orderList = orderMapper.selectOrder(45);
        for (Order a: orderList) {
            System.out.println(a);
        }
    }

5.关系总结

5.1 < if >

UserMapper.java

    List<User> selectByNameAndSex(@Param("username") String username, @Param("sex") String sex);

UserMapper.xml

<select id="selectByNameAndSex" resultType="user">
        select *
        from user
        <where>
            <if test="username != null  and username != ''">
                and username = #{username}
            </if>

            <if test="sex != null  and sex != ''">
                and sex = #{sex}
            </if>
        </where>
    </select>

Test类

  @Test
    public   void  showInfo10() throws IOException {
        UserMapper userMapper  = (UserMapper) MyBatisUtils.getMapper(UserMapper.class);
        List<User> userList = userMapper.selectByNameAndSex("小二王","女");
        System.out.println(userList);
    }

5.2 < where >

UserMapper.java

List<User> selectBySex(@Param("username") String username, @Param("sex") String sex);

UserMapper.xml

	<!--choose用法-->
    <select id="selectBySex" resultType="user">
        select *
        from user
        <where>
            <choose>
                <when test="username != null and username != ''">
                    and username = #{username}
                </when>

                <when test="sex != null and sex != ''">
                    and sex = #{sex}
                </when>
                <otherwise>
                    1=1
                </otherwise>
            </choose>
        </where>
    </select>

Test类

@Test
    public   void  showInfo11() throws IOException {
        UserMapper userMapper  = (UserMapper) MyBatisUtils.getMapper(UserMapper.class);
        List<User> userList = userMapper.selectBySex("小二王","女");
        for (User u :userList) {
            System.out.println(u);
        }
    }

5.3 choose

UserMapper.java

    List<User> selectBySex(@Param("username") String username, @Param("sex") String sex);

UserMapper.xml

<!--choose用法-->
    <select id="selectBySex" resultType="user">
        select *
        from user
        <where>
            <choose>
                <when test="username != null and username != ''">
                    and username = #{username}
                </when>

                <when test="sex != null and sex != ''">
                    and sex = #{sex}
                </when>
                <otherwise>
                    1=1
                </otherwise>
            </choose>
        </where>
    </select>

Test类

 @Test
    public   void  showInfo11() throws IOException {
        UserMapper userMapper  = (UserMapper) MyBatisUtils.getMapper(UserMapper.class);
        List<User> userList = userMapper.selectBySex("小二王","女");
        for (User u :userList) {
            System.out.println(u);
        }
    }

5.4 < set >

UserMapper.java

    int   updateUser(User  user);

UserMapper.xml

 <!--set用法-->
    <update id="updateUser" parameterType="user">
        update `user`
        <set>
            <if test="username != null and username != ''">
                username =#{username},
            </if>
            <if test="sex != null and sex != ''">
                sex =#{sex},
            </if>
            <if test="birthday != null">
                birthday =#{birthday},
            </if>
            <if test="address != null and address != ''">
                address =#{address},
            </if>
        </set>
        where id = #{id}
    </update>

Test类


    @Test
    public   void  showInfo12() throws IOException {
        UserMapper userMapper  = (UserMapper) MyBatisUtils.getMapper(UserMapper.class);

        User user = new User();
        user.setId(76);
        user.setUsername("卢伟亮");
        user.setBirthday(new Date());
        user.setSex(null);
        user.setAddress(null);

        int num  = userMapper.updateUser(user);
        System.out.println(num);
        MyBatisUtils.commit();
    }

5.5 < trim >

UserMapper.java

    int   updateUserById(User  user);

UserMapper.xml

<update id="updateUserById" parameterType="user">
        update `user`
        <!--< trim prefix="" suffix="" prefixOverrides="" suffixOverrides="" >代替< where > 、< set >-->
        <trim prefix="set" suffixOverrides="," suffix="where id=#{id}">
            <if test="username != null and username != ''">
                username =#{username},
            </if>
            <if test="sex != null and sex != ''">
                sex =#{sex},
            </if>
            <if test="birthday != null">
                birthday =#{birthday},
            </if>
            <if test="address != null and address != ''">
                address =#{address},
            </if>
        </trim>
    </update>

Test类

 @Test
    public   void  showInfo13() throws IOException {
        UserMapper userMapper  = (UserMapper) MyBatisUtils.getMapper(UserMapper.class);

        User user = new User();
        user.setId(76);
        user.setUsername("卢小宝");
        user.setBirthday(new Date());
        user.setSex(null);
        user.setAddress(null);

        int num  = userMapper.updateUserById(user);
        System.out.println(num);
        MyBatisUtils.commit();
    }

5.6 < foreach >的批量查询

5.6.1 参数名了解

参数 描述 取值
collection 容器类型 list、array、map
open 起始符 (
close 结束符 )
separator 分隔符 ,
index 下标号 从0开始，依次递增
item 当前项 任意名称（循环中通过 #{任意名称} 表达式访问）

UserMapper.java

 /*foreach、批查詢，根据id的map集合*/
 List<User> selectByArrayMap(Map<String,Object> map);

UserMapper.xml

     <select id="selectByArrayMap" resultType="user">
            select  * from  user
            where id in
            <foreach collection="ids" open="(" separator="," item="itemk" close=")">
                #{itemk}
            </foreach>
        </select>

Test类

 @Test
    public   void  showInfo16() throws IOException {
        UserMapper userMapper  = (UserMapper) MyBatisUtils.getMapper(UserMapper.class);
        Map<String,Object> map = new HashMap<String, Object>();
        List<Integer> li = new ArrayList<Integer>();
        li.add(41);
        li.add(42);
        li.add(43);
        map.put("ids",li);
        List<User> userList = userMapper.selectByArrayMap(map);
        for (User u :userList) {
            System.out.println(u);
        }
    }

5.7 < foreach >** 的批量增加

UserMapper.java

 /*foreach、批增加，根据id的list集合*/
 int  addUser(List<User> userList);

UserMapper.xml

		<!--/*foreach、批增加，根据id的list集合*/-->
        <insert id="addUser" parameterType="list">
            insert into `user` (username, birthday, sex, address) values
            <foreach collection="list" item="u" separator=",">
                (#{u.username}, #{u.birthday}, #{u.sex}, #{u.address})
            </foreach>
        </insert>

Test类

 @Test
    public   void  showInfo17() throws IOException {
        UserMapper userMapper  = (UserMapper) MyBatisUtils.getMapper(UserMapper.class);
        List<User> userList = new ArrayList<User>();
        User u1 = new User();
        u1.setUsername("ll");
        u1.setAddress("kk");
        u1.setBirthday(new Date());
        u1.setSex("男");

        User u2 = new User();
        u2.setUsername("ll");
        u2.setAddress("kk");
        u2.setBirthday(new Date());
        u2.setSex("男");
        userList.add(u1);
        userList.add(u2);
        int num = userMapper.addUser(userList);
        System.out.println(num);
        MyBatisUtils.commit();
    }

5.8 < foreach >** 的批量删除

UserMapper.java

   /*foreach、批删除   ，根据id的list集合*/
    int deleteByUid(List<Integer> list);

UserMapper.xml

 <!--/*foreach、批删除   ，根据id的list集合*/-->
        <delete id="deleteByUid" parameterType="list">
            delete
            from `user`  where
            <foreach collection="list" open="id in (" close=")" separator="," item="ik">
                #{ik}
            </foreach>
        </delete>

Test类

  @Test
    public   void  showInfo18() throws IOException {
        UserMapper userMapper  = (UserMapper) MyBatisUtils.getMapper(UserMapper.class);
        List<Integer> li = new ArrayList<Integer>();
        li.add(41);
        li.add(42);
        li.add(43);
        int num = userMapper.deleteByUid(li);
        System.out.println(num);
        MyBatisUtils.commit();
    }

5.9分页查询

5.9.1 引入依赖

pom.xml中引入PageHelper依赖。

<dependency>
		<groupId>com.github.pagehelper</groupId>
		<artifactId>pagehelper</artifactId>
		<version>5.1.10</version>
</dependency>

5.9.2 配置MyBatis-config.xml

在MyBatis-config.xml中添加< plugins >。

<configuration>
  	<typeAliases></typeAliases>
  
    <plugins>
        <!-- com.github.pagehelper为PageHelper类所在包名 -->
        <plugin interceptor="com.github.pagehelper.PageInterceptor"></plugin>
    </plugins>
  
  	<environments>...</environments>
</configuration>

5.9.3PageInfo对象

PageInfo对象中包含了分页操作中的所有相关数据。

5.9.4 注意事项

只有在PageHelper.startPage()方法之后的第一个查询会有执行分页。
分页插件不支持带有“for update”的查询语句。
分页插件不支持“嵌套查询”，由于嵌套结果方式会导致结果集被折叠，所以无法保证分页结果数量正确。。

5.9.5 分页练习

UserMapper.java

   /*分页查询*/
    List<User> selectPageList();

UserMapper.xml

<!--分页查询-->
    <select id="selectPageList" resultType="User">
        select  * from user
    </select>

Test类

 /*分页查询*/
    @Test
    public void testPageInfo(){
        UserMapper userMapper  = (UserMapper) MyBatisUtils.getMapper(UserMapper.class);
        //pageNum 第几页 , pageSize 展现几条数据
        PageHelper.startPage(1, 4);
        for (User u: userMapper.selectPageList()) {
            System.out.println(u);
        }
    }

5.10 模糊查询

UserMapper.java

    //模糊查询
    List<User> selectLikeUsername(@Param("username") String username);

UserMapper.xml

   <!-- 另一种mybatis提供的函数concat() select  * from  `user` u  where  username  like  concat('%','${username}','%')-->
    <select id="selectLikeUsername" resultType="user">
        select  * from  user  u   where   username like '%${username}%'
    </select>

Test类

  /*模糊查询*/
    @Test
    public  void  showInfo1() throws IOException {
        InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory sf = new SqlSessionFactoryBuilder().build(is);
        SqlSession session = sf.openSession();
        UserMapper userMapper = session.getMapper(UserMapper.class);
        List<User> userList = userMapper.selectLikeUsername("强");
        for (User  u: userList){
            System.out.println(u);
        }
    }

6 注解开发 (难)—附带注解延迟加载

UserMapper.java

ic interface UserMapper {
    //查询
    @Select("select * from  `user`")
    List<User> selectAll();
    //模糊查询 根据用户名
    @Select("select * from user where username like concat('%',#{username},'%')")
    List<User> selectByLike(@Param("username") String username);
    //增加
    @Insert("insert into user(username,birthday,sex,address)values(#{username},#{birthday},#{sex},#{address})")
    Integer addUser(User user);
    //修改
    @Update("update user set username=#{username},birthday=#{birthday},sex=#{sex},address=#{address} where id =#{id}")
    Integer updateUser(User user);
    //删除
    @Delete("delete from user where id =#{id}")
    Integer deleteById(@Param("id") int id);
    //一对一的关系
    @Select("select * from  user")
    @Results({
            @Result(id = true,column = "id",property = "id"),
            @Result(column = "username",property = "username"),
            @Result(column = "birthday",property = "birthday"),
            @Result(column = "sex",property = "sex"),
            @Result(column = "address",property = "address"),
            @Result(one = @One(select = "com.qf.mapper.CardMapper.selectById",fetchType = FetchType.LAZY ),
                    property = "card", column = "id",javaType = Card.class)

    })
    List<User> selectOneToOne();

    //一对多



    @Select("select * from user")
    @Results({
            @Result(id = true,column = "id",property = "id"),
            @Result(column = "username",property = "username"),
            @Result(column = "birthday",property = "birthday"),
            @Result(column = "sex",property = "sex"),
            @Result(column = "address",property = "address"),
            @Result(many = @Many(select = "com.qf.mapper.OrderMapper.selectById",fetchType = FetchType.LAZY),property = "orderList",column = "id",javaType = List.class)

    })
    List<User> selectOneToMany();

OrderMapper

@Select("select * from  card  where uid =#{uid}")
    Card selectById(int uid);

CardMapper

@Select("select * from `order`  where uid =#{uid}")List<Order> selectById(int uid);

UserMapper.xml 注解sql没有这玩意

Test类

  @Test
    public  void showInfo() throws IOException {
        InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
        SqlSession session = factory.openSession();
        UserMapper userMapper = session.getMapper(UserMapper.class);
        List<User> userList = userMapper.selectAll();
        for (User u : userList) {
            System.out.println(u);
        }
    }

    @Test
    public  void showInfo01() throws IOException {
        InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
        SqlSession session = factory.openSession();
        UserMapper userMapper = session.getMapper(UserMapper.class);
        List<User> userList = userMapper.selectByLike("强");
        for (User u : userList) {
            System.out.println(u);
        }
    }

    @Test
    public  void showInfo02() throws IOException {
        InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
        SqlSession session = factory.openSession();
        UserMapper userMapper = session.getMapper(UserMapper.class);
        User user = new User();
        user.setSex("男");
        user.setAddress("中国");
        user.setBirthday(new Date());
        user.setUsername("卢伟亮");
        Integer num = userMapper.addUser(user);
        System.out.println(num);
        //提交事务
        session.commit();

    }

    @Test
    public  void showInfo03() throws IOException {
        InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
        SqlSession session = factory.openSession();
        UserMapper userMapper = session.getMapper(UserMapper.class);
        User user = new User();
        user.setSex("女");
        user.setAddress("中");
        user.setBirthday(new Date());
        user.setUsername("卢伟亮");
        user.setId(83);
        Integer num = userMapper.updateUser(user);
        System.out.println(num);
        //提交事务
        session.commit();
    }

    @Test
    public  void showInfo04() throws IOException {
        InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
        SqlSession session = factory.openSession();
        UserMapper userMapper = session.getMapper(UserMapper.class);
        /*Integer num = userMapper.deleteById(77);
        System.out.println(num);
        //提交事务
        session.commit();*/
    }

    @Test
    public  void showInfo05() throws IOException {
        InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
        SqlSession session = factory.openSession();
        UserMapper userMapper = session.getMapper(UserMapper.class);
        List<User> userList = userMapper.selectOneToOne();
        for (User u : userList) {
            System.out.println(u);
        }
    }

    @Test
    public  void showInfo06() throws IOException {
        InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
        SqlSession session = factory.openSession();
        UserMapper userMapper = session.getMapper(UserMapper.class);
        List<User> userList = userMapper.selectOneToMany();
        for (User u : userList) {
            System.out.println(u);
        }

7.mybatis逆向工程

7.1 工具类代码实现mybatis逆向工程

7.1.1 generator.properties

#这里要写自己的maven仓库中的，mysql-connector-java依赖对应的路径
#复制过来的时候注意是两条反斜杠
jdbc.driverLocation=D:\\java\\maven\\build\\MySQL\\MySQL-connector-java\\5.1.6\\mysql-connector-java-5.1.6.jar
jdbc.driverClass=com.mysql.jdbc.Driver
#mybatis-one数据库名,记得改
jdbc.connectionURL=jdbc:mysql:///mybatis-one
jdbc.userId=root
jdbc.password=1234

7.1.2 generatorConfig.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
        PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
        "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">

<generatorConfiguration>
    <!--导入属性配置-->
    <properties resource="generator.properties"></properties>

    <!--指定特定数据库的jdbc驱动jar包的位置-->
    <classPathEntry location="${jdbc.driverLocation}"/>

    <context id="default" targetRuntime="MyBatis3">

        <!-- optional，旨在创建class时，对注释进行控制 -->
        <commentGenerator>
            <property name="suppressDate" value="true"/>
            <property name="suppressAllComments" value="true"/>
        </commentGenerator>

        <!--jdbc的数据库连接 -->
        <jdbcConnection
                driverClass="${jdbc.driverClass}"
                connectionURL="${jdbc.connectionURL}"
                userId="${jdbc.userId}"
                password="${jdbc.password}">
        </jdbcConnection>


        <!-- 非必需，类型处理器，在数据库类型和java类型之间的转换控制-->
        <javaTypeResolver>
            <property name="forceBigDecimals" value="false"/>
        </javaTypeResolver>


        <!-- Model模型生成器,用来生成含有主键key的类，记录类 以及查询Example类
            targetPackage     指定生成的model生成所在的包名
            targetProject     指定在该项目下所在的路径
        -->
        <javaModelGenerator targetPackage="com.huang.entity"
                            targetProject="src/main/java">

            <!-- 是否允许子包，即targetPackage.schemaName.tableName -->
            <property name="enableSubPackages" value="false"/>
            <!-- 是否对model添加 构造函数 -->
            <property name="constructorBased" value="true"/>
            <!-- 是否对类CHAR类型的列的数据进行trim操作 -->
            <property name="trimStrings" value="true"/>
            <!-- 建立的Model对象是否 不可改变  即生成的Model对象不会有 setter方法，只有构造方法 -->
            <property name="immutable" value="false"/>
        </javaModelGenerator>

        <!--Mapper映射文件生成所在的目录 为每一个数据库的表生成对应的SqlMap文件 -->
        <sqlMapGenerator targetPackage="com.huang.mapper"
                         targetProject="src/main/java">
            <property name="enableSubPackages" value="false"/>
        </sqlMapGenerator>

        <!-- 客户端代码，生成易于使用的针对Model对象和XML配置文件 的代码
                type="ANNOTATEDMAPPER",生成Java Model 和基于注解的Mapper对象
                type="MIXEDMAPPER",生成基于注解的Java Model 和相应的Mapper对象
                type="XMLMAPPER",生成SQLMap XML文件和独立的Mapper接口
        -->
        <javaClientGenerator targetPackage="com.huang.mapper"
                             targetProject="src/main/java" type="XMLMAPPER">
            <property name="enableSubPackages" value="true"/>
        </javaClientGenerator>


        <table tableName="user" domainObjectName="User"
               enableCountByExample="false" enableUpdateByExample="false"
               enableDeleteByExample="false" enableSelectByExample="false"
               selectByExampleQueryId="false">
        </table>

        <table tableName="order" domainObjectName="order"
               enableCountByExample="false" enableUpdateByExample="false"
               enableDeleteByExample="false" enableSelectByExample="false"
               selectByExampleQueryId="false">
        </table>

    </context>
</generatorConfiguration>

7.1.3 pom.xml

<dependencies> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.4.5</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.6</version> </dependency> <dependency> <groupId>log4j</groupId> <artifactId>log4j</artifactId> <version>1.2.12</version> </dependency> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.10</version>
</dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.mybatis.generator</groupId> <artifactId>mybatis-generator-maven-plugin</artifactId> <version>1.3.2</version> <configuration> <verbose>true</verbose> <overwrite>true</overwrite> </configuration> </plugin> </plugins> </build>

7.1.4 配置

mybatis-generator:generate

7.1.5 .配置maven运行

在这里插入图片描述

7.2 第二种方式

第一步.idear连接数据库

在这里插入图片描述

第二步.

在这里插入图片描述

8.延迟加载（按需加载）

8.1理解

1.即懒加载，只加载当前表的信息，不加载关联表的信息（按需加载）
2.基本上一对多和多对多的场景不会使用立即加载：因为我们进行一对多或者多对多查询的时候不确定需不需要查询关联表的数据，但是使用立即加载一定会查询多个表数据出来，效率极低
3.多对一和一对一的时候，会采用立即加载（与上面一起理解）： 
4.注意：开启延迟加载后，如果不使用及联数据，则不会触发联查询操作，有利于加快查询速度，节省内存资源

8.1.1 一对多延迟加载

UserMapper.java

   //建立一对多的关系
    List<User> selectAllManyList();

OrderMapper.java

 //从表
 List<Order> selectOrder(int uid);

UserMapper.xml

 <resultMap id="oneToManyMap" type="user">
        <id column="id" property="id"/>
        <result column="username" property="username"/>
        <result column="birthday" property="birthday" />
        <result column="sex" property="sex" />
        <result column="address" property="address" />
        <!--建立一对一的关系-->
        <collection property="orderList" column="id"
                     select="com.qf.mapper.OrderMapper.selectOrder"
                     //fetchType="lazy" 加开启懒加载
                     ofType="order" fetchType="lazy"></collection>

    </resultMap>

    <select id="selectAllManyList" resultMap="oneToManyMap">
         select * from user
    </select>
    
    
    1. //fetchType="lazy" 加开启懒加载

OrderMapper.xml

<select id="selectOrder" resultType="com.qf.entity.Order">
        select  *  from   `order` where uid =#{uid}
    </select>

8.2 使用场景

在这里插入图片描述

9.立即加载(默认)

9.1理解

加载当前表信息的时候同时加载关联表的信息

9.2代码

UserMapper.java

   //建立一对一的关系,立即加载
    List<User> selectAllList();

CardMapper.java

  Card selectById(int uid);

UserMapper.xml

 <resultMap id="oneToOneMap" type="user">
        <id column="id" property="id"/>
        <result column="username" property="username"/>
        <result column="birthday" property="birthday" />
        <result column="sex" property="sex" />
        <result column="address" property="address" />
        <!--建立一对一的关系-->
        <association property="card" column="id"
                     select="com.qf.mapper.CardMapper.selectById"
                     javaType="card" fetchType="lazy"></association>
         
    </resultMap>

    <select id="selectAllList"  resultMap="oneToOneMap">
        select * from user 
    </select>

CardMapper.xml

  <select id="selectById" resultType="com.qf.entity.Card">
         select * from  card  where uid =#{uid}
    </select>

10 $符号的应用场景

${attribute} 属于字符串拼接SQL，而非预编译占位符，会有注入攻击问题，不建议在常规SQL中使用，常用于可解决动态生降序问题。

10.1 $符号参数绑定

public List<User> selectAllUsers1(User user); // ${name} ${id} 可获取user中的属性值
public List<User> selectAllUsers2(@Param("rule") String rule); //必须使用@Param否则会作为属性解析

<select id="selectAllUsers1" resultType="user">
	SELECT * FROM t_users 
    WHERE name = '${name}' or id = ${id} <!-- 拼接name和id，如果是字符类型需要用单引号：'${name}' -->
</select>
<select id="selectAllUsers2" resultType="user">
	SELECT * FROM t_users 
  	ORDER BY id ${rule} <!-- 拼接 asc | desc -->
</select>

User user = new User(....);
List<User> ulist1 = userDAO.selectAllUsers1(user); //调用时传入user对象

List<User> ulist2 = userDao.selectAllUsers2("desc"); //调用时传入asc | desc

10.2 $符号注入攻击

<select id="selectUsersByKeyword" resultType="user">
	SELECT * FROM t_user
  	WHERE name = '${name}' <!-- 会存在注入攻击  比如传入参数是 【String name = "tom' or '1'='1";】-->
</select>

注入攻击，拼接的内容，改变了原sql语义，被攻击！

ps：数据库表

/*
SQLyog 企业版 - MySQL GUI v8.14 
MySQL - 5.5.40 : Database - mybatis-one
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`mybatis-one` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_czech_ci */;

USE `mybatis-one`;

/*Table structure for table `actor` */

DROP TABLE IF EXISTS `actor`;

CREATE TABLE `actor` (
  `aid` int(11) NOT NULL AUTO_INCREMENT,
  `aname` varchar(20) COLLATE utf8_czech_ci DEFAULT NULL,
  PRIMARY KEY (`aid`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

/*Data for the table `actor` */

insert  into `actor`(`aid`,`aname`) values (1,'杨幂'),(2,'杨颖');

/*Table structure for table `card` */

DROP TABLE IF EXISTS `card`;

CREATE TABLE `card` (
  `cid` int(4) NOT NULL AUTO_INCREMENT,
  `cnumid` varchar(20) COLLATE utf8_czech_ci DEFAULT NULL,
  `uid` int(4) DEFAULT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

/*Data for the table `card` */

insert  into `card`(`cid`,`cnumid`,`uid`) values (1,'1234',45),(2,'6789',46);

/*Table structure for table `order` */

DROP TABLE IF EXISTS `order`;

CREATE TABLE `order` (
  `oid` int(11) NOT NULL AUTO_INCREMENT,
  `oname` varchar(20) COLLATE utf8_czech_ci DEFAULT NULL,
  `onum` int(11) DEFAULT NULL,
  `uid` int(11) DEFAULT NULL,
  PRIMARY KEY (`oid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

/*Data for the table `order` */

insert  into `order`(`oid`,`oname`,`onum`,`uid`) values (1,'坚果',20,45),(2,'书籍',30,46),(3,'衣服',1,45);

/*Table structure for table `role` */

DROP TABLE IF EXISTS `role`;

CREATE TABLE `role` (
  `rid` int(11) NOT NULL AUTO_INCREMENT,
  `rname` varchar(20) COLLATE utf8_czech_ci DEFAULT NULL,
  PRIMARY KEY (`rid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

/*Data for the table `role` */

insert  into `role`(`rid`,`rname`) values (1,'宫女'),(2,'皇后'),(3,'太监');

/*Table structure for table `u_r` */

DROP TABLE IF EXISTS `u_r`;

CREATE TABLE `u_r` (
  `u_r_id` int(11) NOT NULL AUTO_INCREMENT,
  `aid` int(11) DEFAULT NULL,
  `rid` int(11) DEFAULT NULL,
  PRIMARY KEY (`u_r_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

/*Data for the table `u_r` */

insert  into `u_r`(`u_r_id`,`aid`,`rid`) values (1,1,1),(2,1,2);

/*Table structure for table `user` */

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(32) NOT NULL COMMENT '用户名称',
  `birthday` datetime DEFAULT NULL COMMENT '生日',
  `sex` char(1) DEFAULT NULL COMMENT '性别',
  `address` varchar(256) DEFAULT NULL COMMENT '地址',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=77 DEFAULT CHARSET=utf8;

/*Data for the table `user` */

insert  into `user`(`id`,`username`,`birthday`,`sex`,`address`) values (41,'李东','2020-08-25 20:44:41','男','hh'),(42,'小二王','2018-03-02 15:09:37','女','北京金燕龙'),(43,'小二王','2018-03-04 11:34:34','女','北京金燕龙'),(45,'小黑','2018-03-04 12:04:06','男','北京金燕龙'),(46,'老王','2018-03-07 17:37:26','男','北京'),(76,'dfff','2020-08-22 21:23:26','男','ddd');

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;