springboot的mybatis的简单配置

我的配置信息:

server:
  port: 8080
spring:
  datasource:
    username: root
    password: 88888888
    url: jdbc:mysql://localhost:3306/test_db?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
    driver-class-name: com.mysql.cj.jdbc.Driver



mybatis:
  mapper-locations: classpath:mapping/*Mapping.xml
  type-aliases-package: com.example.demo

 

1.简单的增删改查,这里用到的表是st5

 

 (1)mapping的接口数据

@Repository
public interface UserMapper {

    Stu Sel(int id);
    List<Stu> selectAllStu();
    void saveStu(Stu stu);
    void delStu(int id);
    void upadteStu(Stu stu);
}

 mapping的配置

<?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.demo.service.UserMapper">

    <select id="Sel" resultType="com.example.demo.domain.Stu">
        select * from st5 where id = #{id}
    </select>
    <select id="selectAllStu" resultType="com.example.demo.domain.Stu">
        select * from st5
    </select>
    <insert id="saveStu" parameterType="com.example.demo.domain.Stu"  >
        insert into st5 values (#{id},#{name},#{age})

    </insert>
    <delete id="delStu">
        delete from st5 where id = #{id}
    </delete>
    <update id="upadteStu" parameterType="com.example.demo.domain.Stu">
       UPDATE st5 SET name = #{name},age = #{age} WHERE id = #{id}
    </update>
</mapper>

 

插入的时候返回id
<insert id="add" parameterType="com.example.demo.domai.Stu"> 
<!--通过mybatis框架提供的selectKey标签获得自增产生的ID值--> 
<selectKey resultType="java.lang.Integer" order="AFTER" keyProperty="id"> select LAST_INSERT_ID() </selectKey>

insert into st5 values (null,#{name},#{age})

</insert>

 

如果是多对多关系的话，就可以这查询

<collection property="emps"
                    ofType="com.example.demo.domain.Employee"
                    select="EmployeeMapping.findbyid"
                    column="id">
        </collection>

 <select id="findbyid" resultType="com.example.demo.domain.Employee">
    select * from employee where dep_id = #{id}
</select>

 

 

 

 

2.多表查询的配置一对一

<?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.demo.service.EmployeeMapper">


    <select id="selectAllEmployee" resultType="com.example.demo.domain.Employee">
        select * from employee
    </select>

    <!--
    <resultMap id="selectEmpAndDep" type="com.example.demo.domain.Employee">
        <id property="id" column="eid" />
        <result property="name" column="name" />
        <result property="age" column="age" />
        <result property="department.id" column="did" />
        <result property="department.dep_name" column="dep_name" />
        <result property="department.dep_location" column="dep_location" />
    </resultMap>-->
    <resultMap id="selectEmpAndDep" type="com.example.demo.domain.Employee">
        <id property="id" column="eid" />
        <result property="name" column="name" />
        <result property="age" column="age" />
        <association property="department" javaType="com.example.demo.domain.Department" >
            <result property="id" column="did" />
            <result property="dep_name" column="dep_name" />
            <result property="dep_location" column="dep_location" />
        </association>

    </resultMap>
    <select id="selectEmpAndDep" resultMap="selectEmpAndDep">
        SELECT *,employee.id eid,department.id did FROM employee ,department WHERE employee.dep_id = department.id
    </select>

</mapper>

对标查询一对多

<if test="value != null and value != '' and value.length > 0">

 

<?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.demo.service.UserMapper">

    <select id="Sel" resultType="com.example.demo.domain.Stu">
        select * from st5 where id = #{id}
    </select>
    <select id="selectAllStu" resultType="com.example.demo.domain.Stu">
        select * from st5
    </select>
    <insert id="saveStu" parameterType="com.example.demo.domain.Stu"  >
        insert into st5 values (#{id},#{name},#{age})

    </insert>
    <delete id="delStu">
        delete from st5 where id = #{id}
    </delete>
    <update id="upadteStu" parameterType="com.example.demo.domain.Stu">
       UPDATE st5 SET name = #{name},age = #{age} WHERE id = #{id}
    </update>
</mapper>

 <!--根据ID动态修改检查组相关字段-->
    <update id="updateStu" parameterType="com.example.demo.domain.Stu">
        update st5
        <set>
            <if test="name != null">
                name = #{name},
            </if>
            <if test="age != null">
                sex = #{sex},
            </if>
            
        </set>
        where id = #{id}
    </update>

 

<resultMap id="selectDeApndEmp" type="com.example.demo.domain.Department">
        <id property="id" column="did" />
        <result property="dep_name" column="dep_name" />
        <result property="dep_location" column="dep_location" />

        <collection property="emps" ofType="com.example.demo.domain.Employee" >
            <result property="id" column="eid" />
            <result property="name" column="name" />
            <result property="age" column="age" />
        </collection>

    </resultMap>
    <select id="selectDepAndEmp" resultMap="selectDeApndEmp">
        SELECT *,department.id did ,employee.id eid FROM department,employee WHERE department.id = employee.dep_id;
    </select>

 

 

 

3.条件语句查询

    <select id="findByCondition" parameterType="com.example.demo.domain.Stu" resultType="com.example.demo.domain.Stu">
        select * from st5
        <where>
            <if test="id!=0">
                and id=#{id}
            </if>
            <if test="name!=null">
                and name=#{name}
            </if>
            <if test="age!=0">
                and age=#{age}
            </if>
        </where>
    </select>

 

if也可以这么写

 <if test="age!=0 or age!=null">

 

 <if test="age!=0 and age!=null">

 

 

foreach

<sql id="selectUser" select * from User</sql>
<select id="findByIds" parameterType="list" resultType="user">
    select * from st5
    <where>
        <foreach collection="array" open="id in(" close=")" item="id" separator=",">
            #{id}
        </foreach>
    </where>
</select>

 

int[] ids = new int[]{2,5};

List<User> userList = userMapper.findByIds(ids);

 

分页,导入坐标

<dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.3.0</version>

        </dependency>

 .yml文件配置

pagehelper:

      helperDialect: mysql 

      reasonable: true  

      supportMethodsArguments: true   

      pageSizeZero: true    

      params: count=countSql

 使用:

 PageHelper.startPage(2,2);
        List<Stu> list =  userService.getAllStu();;

        PageInfo<Stu> pageInfo = new PageInfo<Stu>(list);
        System.out.println("总条数："+pageInfo.getTotal());
        System.out.println("总页数："+pageInfo.getPages());
        System.out.println("当前页："+pageInfo.getPageNum());
        System.out.println("每页显示长度："+pageInfo.getPageSize());
        System.out.println("是否第一页："+pageInfo.isIsFirstPage());
        System.out.println("是否最后一页："+pageInfo.isIsLastPage());


        return list;