mybatis

## 建表
create table if not exists student(
    s_id int(11) not null auto_increment comment '学号',
    s_name varchar(5) not null  comment '姓名',
    s_age int(3) not null  comment '年龄',
    s_sex int(1) not null  comment '性别',
    primary key (s_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 comment '学生表';

#添加多行数据
insert into  student(s_id, s_name, s_age, s_sex) values
(default,'小兰',18,1),(default,'小白',20,0),(default,'小黑',25,0),(default,'小花',22,1),
(default,'张三',23,0),(default,'李四',20,0),(default,'翠花',19,1),(default,'大海',18,0);

<dependency>
      <group

注解
@Data
public class Student {

    private Integer sId;
    private String sName;
    private Integer sAge;
    private int sSex;

}

<?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>
        <!-- 开启驼峰 -->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

    <!-- 配置环境 environments = 可以有多个环境  我们只配置一个-->
    <environments default="development">
        <!-- 配置 -->
       <environment id="development">
            <!--事务默认使用JDBC-->
           <transactionManager type="JDBC"/>
            <!-- 数据源 -->
           <dataSource type="POOLED">
                <!-- 数据库驱动 -->
               <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
               <!-- 数据库url -->
               <property name="url" value="jdbc:mysql://localhost:3306/gold?characterEncoding=utf8&amp;useSSL=false&amp;allowPublicKeyRetrieval=true"/>
               <!-- 数据库用户名 -->
               <property name="username" value="root"/>
               <!-- 密码 -->
               <property name="password" value="123456"/>
           </dataSource>
       </environment>
    </environments>

      <!-- mapper.xml的具体位置 -->
    <mappers>
        <mapper resource="mapper/StudentMapper.xml"/>
    </mappers>
</configuration>

public interface StudentMapper {
    //查询全部
    public List<Student> allStudent();
}

<?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.xml -->
<mapper namespace="com.qc.mapper.StudentMapper">
    <!-- id:接口方法名称一样  resultType:数据库查询返回加过的类型  下面是sql语句 -->
    <select id="allStudent" resultType="com.qc.entity.Student">
    

           SqlSession sqlSession = sqlSessionFactory.openSession();
            //通过SqlSession.getMapper  传入mapper.class 拿到mapper对象
            StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
            //运行方法,获得数据
            List<Student> students = mapper.allStudent();
            //遍历
            students.forEach(System.out::println);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

* environment（环境变量）
	* transactionManager（事务管理器）
	* dataSource（数据源）

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/gold?characterEncoding=utf8&useSSL=false&allowPublicKeyRetrieval=true
username=root
password=123456

<properties resource="jdbc.properties"/>

<dataSource type="POOLED">
  <property name="driver" value="${driver}"/>
  <property name="url" value="${url}"/>
  <property name="username" value="${username}"/>
  <property name="password" value="${password}"/>
</dataSource>

<settings>
  <setting name="cacheEnabled" value="true"/>
  <setting name="lazyLoadingEnabled" value="true"/>
  <setting name="multipleResultSetsEnabled" value="true"/>
  <setting name="useColumnLabel" value="true"/>
  <setting name="useGeneratedKeys" value="false"/>
  <setting name="autoMappingBehavior" value="PARTIAL"/>
  <setting name="autoMappingUnknownColumnBehavior" value="WARNING"/>
  <setting name="defaultExecutorType" value="SIMPLE"/>
  <setting name="defaultStatementTimeout" value="25"/>
  <setting name="defaultFetchSize" value="100"/>
  <setting name="safeRowBoundsEnabled" value="false"/>
  <setting name="mapUnderscoreToCamelCase" value="false"/>
  <setting name="localCacheScope" value="SESSION"/>
  <setting name="jdbcTypeForNull" value="OTHER"/>
  <setting name="lazyLoadTriggerMethods" value="equals,clone,hashCode,toString"/>
</settings>

<typeAliases>
       <typeAlias alias="Student" type="com.qc.entity.Student"/>
</typeAliases>

<select id="allStudent" resultType="Student">
        select * from student;
    </select>

<typeAliases>
       <package name="com.qc"/>
</typeAliases>

@Alias("student")
public class Student{
    ...
}

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

<transactionManager type="MANAGED">
  <property name="closeConnection" value="false"/>
</transactionManager>

<

<mappers>
  <mapper url="file:///var/mappers/AuthorMapper.xml"/>
  <mapper url="file:///var/mappers/BlogMapper.xml"/>
  <mapper url="file:///var/mappers/PostMapper.xml"/>
</mappers>

<mappers>
  <mapper class="org.mybatis.builder.AuthorMapper"/>
  <mapper class="org.mybatis.builder.BlogMapper"/>
  <mapper class="org.mybatis.builder.PostMapper"/>
</mappers>

<mappers>
  <package name="org.mybatis.builder"/>
</mappers>

@Data
public class Stud

m.qc.mapper.StudentMapper">

    <resultMap id="test" type="student">
        <!-- column 数据库中的字段  property 实体类中的属性  -->
        <result column="s_id" property="sId"/>
        <result column="s_name" property="sName"/>
        <result column="s_age" property="sAge"/>
	<!-- 数据库字段和实体类不一样，可以进行映射 -->
        <result column="s_sex" property="sex"/>

    </resultMap>

    <!-- 返回类型为上面定义的resultMap  id -->
    <select id="allStudent" resultMap="test">
        select * from student;
    </select>
</mapper>

<mapper namespace="com.qc.mapper.StudentMapper">

    <resultMap id="test" type="student">
        <!-- 只配置不一样的字段 -->
        <result column="s_sex" property="sex"/>
    </resultMap>

    <!-- 返回类型为上面定义的resultMap  id -->
    <select id="allStudent" resultMap="test">
        select * from student;
    </select>
</mapper>

<settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>

//分页查询
    @Select("select * from student limit #{pageNum},#{pageSize}")
    public List<Student> limitStudent(Map<String, Integer> map);

//增加
    @Insert("insert into student values(default,#{SName},#{sAge},#{sex})")
    public void addStudent(Student student);

//更新、修改
@Update("update student set s_name = #{sName} where s_id = #{sI

public void deleteStudentById(Integer sId);

#创建数据库
create database if not exists mybatis default charset utf8mb4 collate utf8mb4_general_ci;

# 老师表
create table if not exists teacher(
    id int(11) not null auto_increment comment '老师id',
    name varchar(5) default null comment '老师名称',
    primary key (id)
)engine = InnoDB default charset =utf8mb4 comment '老师表';

#学生表
create table if not exists student(
    id int(11) not null auto_increment comment '学生id',
    name varchar(5) default null comment '学生姓名',
    t_id int(11) default null comment '学生老师id',
    primary key (id),
    key fktid (t_id),
    constraint fktid foreign key (t_id) references teacher (id)
)engine = InnoDB default charset = utf8mb4 comment '学生表';

#老师表插入数据
insert into teacher(id,name) value(default,'王老师');

#学生表插入数据
insert into student(id, name, t_id) VALUES
(default,'小明',1),(default,'小红',1),(default,'小张',1),
(default,'小李',1),(default,'小王',1)

@Data
public class Teacher {
    private Integer id;
    private String name;
}

@Data
public class Student {
    private Integer id;
    private String name;
    //学生对应的老师
    private Teacher teacher;
}

public interface StudentMapper {
    //查询所有学生信息和其老师
    public List<Student> allStudentTeacher();
}

<mapper namespace="com.qc.mapper.StudentMapper">

    <resultMap id="stmap" type="student">
        <result property="id" column="id"/>
        <result property="name" column="sname"/>
        <!-- 实体类是本累的teacher属性  对应的是一个java类型 -->
        <association property="teacher" javaType="

Teacher();
            studentList.forEach(System.out::println);
            sqlSession.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

@Data
@Alias("teacher")
public class Teacher {
    private Integer id;
    private String name;
    //一个老师有多个学生
    private List<Student> studentList;
}

@Data
@Alias("student")
public class Student {
    private Integer id;
    private String name;
    private Integer tId;
}

public interface TeacherMapper {

    public Teacher allTeacher();

}

<mapper namespace="com.qc.mapper.TeacherMapper">
    <!-- 一对多:查询老师的信息，包括老师的学生 -->
    <select id="allTeacher" resultMap="tsMap">
        select t.id t_id,t.name t_name,
        s.id s_id,s.name s_name,s.t_id s_tid
        from teacher t right join student s on t.id = s.t_id
    </select>

    <resultMap id="tsMap" type="teacher">
        <result property="id" column="t_id"/>
        <result property="name" column="t_name"/>
        <!-- 实体类是一个学生的集合 所以使用collection代表集合 ofType代表集合泛型 -->
        <collection property="studentList" ofType="Student">
            <result property="id" column="s_id"/>
            <result property="name" column="s_name"/>
            <result property="tId" column="s_tid"/>
        </collection>
    </resultMap>
</mapper>

public class TeacherMapperTest {
    @Test
    public void allTeacher(){
        SqlSession sqlsession = MybatisUtil.getSqlsession();
        TeacherMapper mapper = sqlsession.getMapper(TeacherMapper.class);
        Teacher teacher = mapper.allTeacher();
        System.out.println(teacher);
	sqlsession.close();
    }

l  comment '标题',
    content longtext comment '内容',
    author varchar(30) not null comment '作者',
    create_time datetime not null comment '时间',
    views int(11) not null  comment '浏览量'
)engine = InnoDB default charset=utf8mb4 comment '博客表';

insert into blog(id, title, content, author, create_time, views) values
('582f2f31a560406e9b218b77166d84e8','mybatis','mybatis入门','c',now(),999),
('7795580d03cf4a05abb9058741811a9e','spring','spring入门','c',now(),888),
('88c1b967b8b54d74bee19d964f8975dc','springboot','springboot入门','cz',now(),777),
('26179bfe6a2449c38db6edf616876a3d','mysql','mysql入门','cz',now(),666),
('63fd9e7d658c48daabc43fc75ebd3d54','java','java入门','x',now(),222),
('28cbd309d6ed4fc7bd3bc274377f9f2','golang','golang入门','x',now(),99);

@Data
@Alias("blog")
public class Blog {

    private String id;
    private String title;
    private String content;
    private String author;
    private Date   createTime;
    private Integer views;

}

<mapper namespace="com.qc.mapper.BlogMapper">
  <select id="likeBlog" parameterType="map" resultType="blog">
        <!--  where 1=1 不影响查询，只是为了加上where，方便后面if动态查询 -->
        select * from blog where 1=1
        <!-- 如果传入的map里面没有数据，下面不执行，如果里面有title,或者author，则添加如下sql -->
        <if test="title != null">
            and title  = #{title}
        </if>
        <if test="author != null">
            and author = #{author}
        </if>
  </select>
</mapper>

    @Test
    public void likeBlog(){
        SqlSession sqlsession = MybatisUtil.getSqlsession();
        BlogMapper mapper = sqlsession.getMapper(BlogMapper.class);
        Map<String, String> map = new HashMap<>();
        map.put("author","c");
        List<Blog> blogs = mapper.likeBlog(map);
        blogs.forEach(System.out::println);
        sqlsession.close();
    }

<select id="likeBlog2" parameterType="map" resultType="blog">
        select * from blog where 1=1
       <choose>
            <!-- 如果title有数据就只添加这个条件 -->
           <when test="title != null">
               and title  = #{title}
           </when>
            <!-- 如果只有author 就会添加这个条件 -->
            <when test="author != null">
                and author = #{author}
            </when>
            <!-- 没有数据则默认添加这个条件 -->
            <otherwise>
                and views > 666
            </otherwise>
       </choose>
</select>

<select id="likeBlog2" parameterType="map" resultType="blog">
        <!--  where 1=1 不影响查询，只是为了加上where，方便后面if动态查询 -->
        select * from blog
        <where>
           <choose>
                <!-- 如果title有数据就只添加这个条件 -->
               <when test="title != null">
                   title  = #{title}
               </when>
                <!-- 如果只有author 就会添加这个条件 -->
                <when test="author != null">
                    author = #{author}
                </when>
                <!-- 没有数据则默认添加这个条件 -->
                <otherwise>
                    and views > 666
                </otherwise>
           </choose>
        </where>
</select>

<trim prefix="WHERE" prefixOverrides="AND |OR ">
  ...
</trim>

 <select id="likeBlog" parameterType="map" resultType="blog">
        <!--  where 1=1 不影响查询，只是为了加上where，方便后面if动态查询 -->
        select * from blog
            <trim prefix="where" prefixOverrides="AND |OR ">
                <!-- 如果传入的map里面没有数据，下面不执行，如果里面有title,或者author，则添加如下sql -->
                <if test="title != null">
                    and title  = #{title}
                </if>
                <if test="author != null">
                    and author = #{author}
                </if>
            </trim>
</select>

<update id="updateBlog" parameterType="blog">
        update blog
        <set>
            <!-- 如果传入了那个字段字，判断有就更新该数据  别忘了后面要加逗号-->
            <if test="title !=null">title = #{title},</if>
            <if test="content !=null">content = #{content},</if>
            <if test="author !=null">author = #{author},</if>
            <if test="createTime !=null">create_time = #{createTime},</if>
            <if test="views !=null">views = #{views}</if>
        </set>
        where id = #{id}
    </update>

@Test
    public void updateBlog(){
        SqlSession sqlsession = MybatisUtil.getSqlsession();
        BlogMapper mapper = sqlsession.getMapper(BlogMapper.class);
        //创建blog对象传入要修改的信息
        Blog blog = new Blog();
        blog.setId("88c1b967b8b54d74bee19d964f8975dc");
        blog.setTitle("修改后的springboot");
        blog.setContent("修改后不入门了，直接入土。");
        blog.setCreateTime(new Date());
        mapper.updateBlog(blog);
	sqlsession.commit();
        sqlsession.close();
    }

<!-- suffixOverrides后缀覆盖：因为加了逗号,如果只有第一句匹配了，但是sql语句就多了个逗号，因此需要去除
（就是为了去除多余的逗号） -->
<trim prefix="SET" suffixOverrides=",">
  ...
</trim>

update blog
        <trim prefix="set" suffixOverrides=",">
            <!-- 如果传入了那个字段字，判断有就更新该数据 -->
            <if test="title !=null">title = #{title},</if>
            <if test="content !=null">content = #{content},</if>
            <if test="author !=null">author = #{author},</if>
            <if test="createTime !=null">create_time = #{createTime},</if>
            <if test="views !=null">views = #{views}</if>
        </trim>
        where id = #{id}
</update>

<!-- 将动态sql的if语句全部抽出来 -->
    <sql id="if-title-author">
        <if test="title != null">
            and title  = #{title}
        </if>
        <if test="author != null">
            and author = #{author}
        </if>
    </sql>
  <select id="likeBlog" parameterType="map" resultType="blog">
        <!--  where 1=1 不影响查询，只是为了加上where，方便后面if动态查询 -->
        select * from blog
            <trim prefix="where" prefixOverrides="and">
                <!-- 使用include使用抽出来的动态sql -->
                <include refid="if-title-author"></include>
            </trim>
  </select>

<select id="queryBlog" parameterType="arraylist" resultType="blog">
        <!-- 正常的sql是：
            select * from blog where id in ('582f2f31a560406e9b218b77166d84e8','7795580d03cf4a05abb9058741811a9e');
            但是为了动态的查询，我们将要查询的多个id放入Arraylist中
            在sql中要把集合中的数据取出来，并包装成 in (id,id,id) 的形式
            item:集合中每个数据对应的字段 collection:存入集合的引用
            open:开始用啥拼接  separator:用什么做分割 close:结束用啥拼接
        -->
        select * from blog
        <where>
            id in
            <foreach item="id" collection="list" open="(" separator="," close=")">
                #{id}
            </foreach>
        </where>
</select>

    @Update({
            "<script>",
            "update blog",
            "  <set>",
            "    <if test='title != null'>title=#{title},</if>",
            "    <if test='content != null'>content=#{content},</if>",
            "    <if test='author != null'>author=#{author},</if>",
            "    <if test='createTime != null'>create_time=#{createTime},</if>",
            "    <if test='views != null'>views=#{views},</if>",
            "  </set>",
            "where id=#{id}",
            "</script>"
    })
    public void updateBlog(Blog blog);

    @Test
    public void likeBlog(){
        SqlSession sqlsession = MybatisUtil.getSqlsession();
        BlogMapper mapper = sqlsession.getMapper(BlogMapper.class);
        Map<String, String> map = new HashMap<>();
        map.put("title","spring");
        map.put("author","c");
        List<Blog> blogs = mapper.likeBlog(map);
        blogs.forEach(System.out::println);
        System.out.println("===========================");
        //在查询一次
        List<Blog> blogs2 = mapper.likeBlog(map);
        blogs2.forEach(System.out::println);

        sqlsession.close();
    }

//调用sqlsession.clearCache()方法
sqlsession.clearCache();

<cache/>

<cache
<!-- 创建了一个 FIFO 缓存 -->
  eviction="FIFO"
<!-- 每隔 60 秒刷新 -->
  flushInterval="60000"
<!-- 最多可以存储结果对象或列表的 512 个引用 -->
  size="512"
<!-- 返回的对象被认为是只读的 -->
  readOnly="true"/>

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

<cache/>

    <!-- 启用二级缓存 -->
    <cache
            eviction="FIFO"
            flushInterval="60000"
            size="512"
            readOnly="true"/>

public class Blog implements Serializable