mybatis(3)

Tboy_

已于 2022-06-16 10:28:13 修改

阅读量90

点赞数

文章标签： java

于 2022-06-05 13:54:56 首次发布

本文链接：https://blog.csdn.net/tboy_/article/details/125131053

版权

一.set标签

 <!set:可以帮我们生成关键字 set 并且可以去除最后一个逗号
  配合if标签一起用，一般用在修改语句
-->
 <update id="update" parameterType="com.tym.entity.User">
        update tbl_user02
        <set>
            <if test="name!=null and name!=''">
                name=#{name}
            </if>
            <if test="pwd!=null">
                pwd=#{pwd}
            </if>
            <if test="email!=null and email!=''">
                email=#{email}
            </if>
        </set>
     where id=#{id}
    </update>

二.foreach标签（循环标签）

public interface UserDao {

    public List<User> plFind(Integer [] id);
    public int deletes(Integer[] id);
    public int inserts(List<User> list );
    
}

（1）查询

<!--
        数组：collection=array  如果你使用的为集合那么就用collection=list
        collection:类型
        item:数组中每个元素赋值的变量名
        open: 以谁开始
        close:以谁结束
        separator:分割符
 -->
<select id="plFind" resultType="com.tym.entity.User">
        select * from tbl_user02 where id in
      <foreach collection="array" item="id" open="(" close=")" separator=",">
          #{id}
      </foreach>
    </select>

@Test
    public void testPlFind()throws Exception{
        Reader reader = Resources.getResourceAsReader("mybatis.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
        SqlSession session = factory.openSession();
        UserDao userDao = session.getMapper(UserDao.class);
        Integer [] ids={2,4};
        List<User> users = userDao.plFind(ids);
        System.out.println(users);
        session.close();
    }

(2)删除

<delete id="deletes" parameterType="int">
        delete from tbl_user02 where id in
      <foreach collection="array" item="id" open="(" close=")" separator=",">
          #{id}
      </foreach>
    </delete>

@Test
    public void testDeletes()throws Exception{
        Reader reader = Resources.getResourceAsReader("mybatis.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
        SqlSession session = factory.openSession();
        UserDao userDao = session.getMapper(UserDao.class);
        Integer [] ids={28,19};
        int deletes = userDao.deletes(ids);
        System.out.println(deletes);
        session.commit();
        session.close();
    }

（3）添加

<insert id="inserts" parameterType="com.tym.entity.User">
        insert into tbl_user02 values
       <foreach collection="list" item="ist" separator=",">
           (null,#{ist.name},#{ist.pwd},#{ist.email})
       </foreach>
    </insert>

 @Test
    public void testInserts()throws Exception{
        Reader reader = Resources.getResourceAsReader("mybatis.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
        SqlSession session = factory.openSession();
        UserDao userDao = session.getMapper(UserDao.class);
        List<User> list = new ArrayList<>();
        list.add(new User("张三",321,"098"));
        list.add(new User("李四",654,"890"));
        int inserts = userDao.inserts(list);
        session.commit();
        session.close();
    }

三.sql片段

    <sql id="find">
        t_id,t_name,t_age,class_id,c_id,c_name
    </sql>

    <select id="findByClass" resultMap="StudentMapper">
     select <include refid="find"/> from s_student s,c_class c where s.class_id=c.c_id and t_id=#{id}
    </select>

四. mybatis映射文件处理特殊字符.

<!--
       第一种：转义标签 &nbsp; &lt;  
       第二种: <![CDATA[sql]]>
    -->
    <select id="tsFind" resultMap="StudentMapper">
        <![CDATA[select * from  s_student where t_age<#{max} and t_age>#{min}]]>
    </select>

五. mybatis模糊查询

（1）使用字符串函数完成拼接 concat

 <select id="mhFind" resultType="com.tym.entity.User">
        select * from tbl_user02 where name like concat('%',#{name},'%')
    </select>

(2) 使用${}

<select id="mhFind" resultType="com.tym.entity.User">
        select * from tbl_user02 where name like '%${name}%'
    </select>

注意：${}实际上是字符串拼接，它不能防止sql注入，而#{}它是预编译，它可以防止sql注入问题

六.连表查询

1.第一种

（1）实体类

package com.tym.entity;

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

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Student {
    private Integer id;
    private String name;
    private int age;
    private Integer classId;
    //学生所属的班级
    private Clas clas;
}

@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
    private Integer id;
    private String name;
    private Integer pwd;
    private String email;

    public User(String name, Integer pwd, String email) {
        this.name = name;
        this.pwd = pwd;
        this.email = email;
    }
}

（2）.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.tym.dao.StudentDao">
    <sql id="find">
        t_id,t_name,t_age,class_id,c_id,c_name
    </sql>

    <select id="findByClass" resultMap="StudentMapper">
     select <include refid="find"/> from s_student s,c_class c where s.class_id=c.c_id and t_id=#{id}
    </select>

    <resultMap id="StudentMapper" type="com.tym.entity.Student">
        <id column="t_id" property="id"/>
        <result column="t_name" property="name"/>
        <result column="t_age" property="age"/>
        <result column="class_id" property="classId"/>
       <!--association: 表示一的一方
                 property: 它表示属性名
                 javaType: 该属性名对应的数据类型
            -->
        <association property="clas" javaType="com.tym.entity.Clas">
            <id column="c_id" property="id"/>
            <result column="c_name" property="name"/>
        </association>
    </resultMap>

<mapper/>

2.第二种

 返回类型就用map封装

public Map lbFind(Integer id);

<select id="lbFind" resultType="java.util.Map">
    select <include refid="find"/> from s_student s,c_class c where s.class_id=c.c_id and s.t_id=#{id}
    </select>

 @Test
    public void testLbFind()throws Exception{
        Reader reader = Resources.getResourceAsReader("mybatis.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
        SqlSession session = factory.openSession();
        StudentDao studentDao = session.getMapper(StudentDao.class);
        Map map = studentDao.lbFind(1);
        System.out.println(map);
        session.close();
    }

七.分页插件pagehelper

1.引入pagehelper jar包

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

2.在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>
 <!-- pagehelperl拦截器-->
    <plugins>
        <plugin interceptor="com.github.pagehelper.PageInterceptor"></plugin>
    </plugins>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC" />
            <!--数据源的配置:name的值固定  value的值要根据客户自己修改-->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver" />
                <property name="url"    value="jdbc:mysql://localhost:3306/mybatis?serverTimezone=Asia/Shanghai" />
                <property name="username" value="root" />
                <property name="password" value="001024" />
            </dataSource>
        </environment>
    </environments>
    <!--注册映射文件-->
    <mappers>
        <!--resource:引用资源文中的映射文件 url:网络上的映射文件-->
        <mapper resource="mapper/StudentMapper.xml" />
    </mappers>
</configuration>

3.使用pagehelper

@Test
    public void testFindAll()throws Exception{
        Reader reader = Resources.getResourceAsReader("mybatis.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
        SqlSession session = factory.openSession();
        StudentDao studentDao = session.getMapper(StudentDao.class);
        //pagNum:显示第几页  pagSize：本页显示几条
        PageHelper.startPage(1,2);
        List<Student> all = studentDao.findAll();
        //把查询到的结果封装到Pageinfo中
        PageInfo<Student> info = new PageInfo(all);
        //总条数
        System.out.println(info.getTotal());
        //总页数
        System.out.println(info.getPages());
        //当前页码对应的数据
        System.out.println(info.getList());
        //System.out.println(all);
        session.close();
    }

八.mybatis的代码生成器--generator

作用：根据表帮你生成实体类，和dao和xml映射文件。简单的增删改查。

1.引入依赖jar包

<dependency>
            <groupId>org.mybatis.generator</groupId>
            <artifactId>mybatis-generator-core</artifactId>
            <version>1.4.0</version>
</dependency>

2.generator的配置文件

<?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>
    <!--找到mysql驱动jar包-->
    <classPathEntry location="D:\田亚\学习礼包\repMaven\repMaven\mysql\mysql-connector-java\8.0.20\mysql-connector-java-8.0.20.jar" />

    <context id="DB2Tables" targetRuntime="MyBatis3">
    <！-- 去除注释-->
        <commentGenerator>
            <property name="suppressAllComments" value="true"/>
        </commentGenerator>
        <jdbcConnection driverClass="com.mysql.cj.jdbc.Driver"
                        connectionURL="jdbc:mysql://localhost:3306/mybatis?serverTimezone=Asia/Shanghai"
                        userId="root"
                        password="001024">
        </jdbcConnection>

        <javaTypeResolver >
            <property name="forceBigDecimals" value="false" />
        </javaTypeResolver>

        <!--实体类配置-->
        <javaModelGenerator targetPackage="com.tym.entity" targetProject=".\src\main\java">
            <property name="enableSubPackages" value="true" />
            <property name="trimStrings" value="true" />
        </javaModelGenerator>

        <!--映射文件配置-->
        <sqlMapGenerator targetPackage="mapper"  targetProject=".\src\main\resources">
            <property name="enableSubPackages" value="true" />
        </sqlMapGenerator>

        <!--dao数据访问层配置-->
        <javaClientGenerator type="XMLMAPPER" targetPackage="com.tym.dao"  targetProject=".\src\main\java">
            <property name="enableSubPackages" value="true" />
        </javaClientGenerator>

        <!--数据库表和实体的映射关系
              schema:数据库名称
              tableName: 表名
              domainObjectName:实体类名

              enableUpdateByExample:是否生成复杂的修改操作
        -->
        <table schema="mybatis" tableName="tbl_user02" domainObjectName="User"
        enableUpdateByExample="false" enableSelectByExample="false"
               enableDeleteByExample="false" enableCountByExample="false"
        >

        </table>

    </context>
</generatorConfiguration>

3.运行配置文件

 @Test
    public void test1()throws Exception{
        List<String> warnings = new ArrayList<String>();
        boolean overwrite = true;
        File configFile = new File("generator.xml");
        ConfigurationParser cp = new ConfigurationParser(warnings);
        Configuration config = cp.parseConfiguration(configFile);
        DefaultShellCallback callback = new DefaultShellCallback(overwrite);
        MyBatisGenerator myBatisGenerator = new MyBatisGenerator(config, callback, warnings);
        myBatisGenerator.generate(null);
    }

4.去除英文注释

在generator.xml中

<！-- 去除注释-->
        <commentGenerator>
            <property name="suppressAllComments" value="true"/>
        </commentGenerator>

九.mybatis缓存

好处：使用缓存减少和数据库的交互次数，提高执行效率

mybatis支持两种缓存

1.一级缓存基于：SqlSession级别的缓存。默认开启的，不能关闭。

@Test
    public void testSelect()throws Exception{
        Reader reader = Resources.getResourceAsReader("mybatis.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
        SqlSession session = factory.openSession();
        //第一次查询，向数据库查询发送sql语句把查询的结果放入缓存中
        UserMapper mapper = session.getMapper(UserMapper.class);
        User user = mapper.selectByPrimaryKey(1l);
        System.out.println(user);
        //同一个Sqlsession缓存可以命中，
        User user1 = mapper.selectByPrimaryKey(1l);
        System.out.println(user1);
        session.close();

       //开启新的sqlsession
        SqlSession session1 = factory.openSession();
        UserMapper mapper1 = session1.getMapper(UserMapper.class);
        User user2 = mapper1.selectByPrimaryKey(1l);
        System.out.println(user2);
    }

2.二级缓存基于：SqlSessionFactory级别的缓存，它可以做到多个SqlSession共享数据。默认它是关闭。需要手动开启。

（1）开启二级缓存

    <settings>
        <!--开启二级缓存-->
        <setting name="cacheEnabled" value="true"/>
    </settings>

（2）在映射文件中使用二级缓存

（3）实体一定要实现序列化接口

查询顺序: 二级缓存一级缓存数据库。