• JavaWeb开发-09-MyBatis

     官网：icon-default.png?t=N7T8https://mybatis.org/mybatis-3/zh/index.html

    一.Mybatis入门

    1.快速入门

     


    2.JDBC介绍


    3.数据库连接池

     官方地址：icon-default.png?t=N7T8https://github.com/alibaba/druid/tree/master/druid-spring-boot-starter


    4.lombok

    二.Mybatis基础增删改查

    1.准备


    2.删除

     


    3.新增

     


    4.更新


    5.查询

    1. # 配置数据库的连接信息 - 四要素
    2.  
    3. #驱动类名称
    4. spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
    5. #数据库连接的url
    6. spring.datasource.url=jdbc:mysql://localhost:3306/mybatis
    7. #连接数据库的用户名
    8. spring.datasource.username=root
    9. #连接数据库的密码
    10. spring.datasource.password=1234
    11.  
    12. # 配置mybatis的日志信息,指定到输出控制台
    13. mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
    14.  
    15. #开启mybatis的驼峰命名自动映射开关
    16. mybatis.configuration.map-underscore-to-camel-case=true
    1. package com.wjh.pojo;
    2.  
    3. import lombok.AllArgsConstructor;
    4. import lombok.Data;
    5. import lombok.NoArgsConstructor;
    6.  
    7. import java.time.LocalDate;
    8. import java.time.LocalDateTime;
    9.  
    10. @Data
    11. @NoArgsConstructor
    12. @AllArgsConstructor
    13. public class Emp {
    14.  
    15.     private Integer id;
    16.     private String username;
    17.     private String password;
    18.     private String name;
    19.     private Short gender;
    20.     private String image;
    21.     private Short job;
    22.     private LocalDate entrydate;
    23.     private Integer deptId;
    24.     private LocalDateTime createTime;
    25.     private LocalDateTime updateTime;
    26.  
    27. }
    1. package com.wjh.mapper;
    2.  
    3. import com.wjh.pojo.Emp;
    4. import org.apache.ibatis.annotations.*;
    5.  
    6. import java.time.LocalDate;
    7. import java.util.List;
    8.  
    9. @Mapper
    10. public interface EmpMapper {
    11.  
    12.     //查询所有员工信息
    13.     @Select("select * from emp")
    14.     public List select();
    15.  
    16.     //根据ID删除员工信息操作
    17.     @Delete("delete from emp where id = #{id}")
    18.     //public void delete(Integer id);
    19.     public int delete(Integer id);
    20.  
    21.     //新增员工
    22.     @Options(useGeneratedKeys = true, keyProperty = "id")
    23.     @Insert("insert into emp(username, name, gender, image, job, entrydate, dept_id, create_time, update_time)" +
    24.             "values (#{username}, #{name}, #{gender}, #{image}, #{job}, #{entrydate}, #{deptId}, #{createTime}, #{updateTime}) ")
    25.     public void insert(Emp emp);
    26.  
    27.     //更新员工信息(修改)
    28.     @Update("update emp set username = #{username}, name = #{name}, gender = #{gender}, image = #{image}, job = #{job}, entrydate = #{entrydate}, dept_id = #{deptId}, update_time = #{updateTime} where id = #{id};")
    29.     public void update(Emp emp);
    30.  
    31.     //根据id查询用户信息
    32.     @Select("select * from emp where id = #{id}")
    33.     public Emp selectId(Integer id);
    34.  
    35. //    //方案一给字段起别名,让别名与实体名一致
    36. //    @Select("select id, username, password, name, gender, image, job, entrydate, " +
    37. //            "dept_id deptId, create_time createTime, update_time updateTime from emp where id = #{id}")
    38. //    public Emp selectId2(Integer id);
    39. //
    40. //    //方案二: 通过#@Result注解手动映射
    41. //    @Results({
    42. //            @Result(column = "dept_id",property =  "deptId"),
    43. //            @Result(column = "create_time",property =  "createTome"),
    44. //            @Result(column = "update_time",property =  "updateTime")
    45. //    })
    46. //    @Select("select * from emp where id = #{id}")
    47. //    public Emp selectId3(Integer id);
    48.  
    49.     //方案三: 开启mybatis的驼峰命名自动映射开关 -- a_column --------> aColumn
    50.  
    51.     //条件查询员工信息
    52.     @Select("select * from emp where name like concat('%', #{name},'%') and gender = #{gender} " +
    53.             "and entrydate between #{begin} and #{end} order by update_time desc")
    54.     public List list(String name, Short gender, LocalDate begin, LocalDate end );
    55.  
    56. //    select concat('hello', 'mysql', 'word');
    57. //     select * from emp where name like concat('%', '张', '%') and gender = '1' and
    58. //     entrydate between '2010-01-01' and '2020-01-01' order by update_time desc;
    59.  
    60. }

    1. package com.wjh;
    2.  
    3. import com.wjh.mapper.EmpMapper;
    4. import com.wjh.pojo.Emp;
    5. import org.apache.ibatis.annotations.Mapper;
    6. import org.junit.jupiter.api.Test;
    7. import org.springframework.beans.factory.annotation.Autowired;
    8. import org.springframework.boot.test.context.SpringBootTest;
    9.  
    10. import java.time.LocalDate;
    11. import java.time.LocalDateTime;
    12. import java.util.ArrayList;
    13. import java.util.List;
    14.  
    15. @SpringBootTest
    16. class SpringbootMybatisCrudApplicationTests {
    17.  
    18.     @Autowired
    19.     private EmpMapper EmpMapper;
    20.  
    21.     //查询所有员工测试类
    22.     @Test
    23.     public void testListEmp(){
    24.         List empList = EmpMapper.select();
    25.         empList.stream().forEach(selectEmp -> {
    26.             System.out.println(selectEmp);
    27.         });
    28.     }
    29.  
    30.     //根据id删除员工信息测试类
    31.     @Test
    32.     public void testDelete(){
    33.         EmpMapper.delete(4);
    34.         //int delete = EmpMapper.delete(4);
    35.         //System.out.println("删除了" + delete + "条数据");
    36.         testListEmp();
    37.     }
    38.  
    39.     //新增员工测试类
    40.  
    41.     @Test
    42.     public void testInsert(){
    43.         Emp empInsert = new Emp();
    44.         empInsert.setUsername("ikun6");
    45.         empInsert.setName("坤坤6");
    46.         empInsert.setGender((short)2);
    47.         empInsert.setImage("#");
    48.         empInsert.setJob((short)1);
    49.         empInsert.setEntrydate(LocalDate.of(2000,01,01));
    50.         empInsert.setDeptId(1);
    51.         empInsert.setCreateTime(LocalDateTime.now());
    52.         empInsert.setUpdateTime(LocalDateTime.now());
    53.         //执行员工信息操作
    54.         EmpMapper.insert(empInsert);
    55.         System.out.println("主键:" + empInsert.getId());
    56.     }
    57.  
    58.     //修改员工信息测试类
    59.     @Test
    60.     public void testUpdate(){
    61.         Emp empUpdate = new Emp();
    62.         empUpdate.setId(10);
    63.         empUpdate.setUsername("zhaomingming");
    64.         empUpdate.setName("赵敏");
    65.         empUpdate.setGender((short)1);
    66.         empUpdate.setImage("#");
    67.         empUpdate.setJob((short)1);
    68.         empUpdate.setEntrydate(LocalDate.of(2000,01,01));
    69.         empUpdate.setDeptId(1);
    70.         empUpdate.setUpdateTime(LocalDateTime.now());
    71.  
    72.         //执行员工信息操作
    73.         EmpMapper.update(empUpdate);
    74.     }
    75.  
    76.     //根据id查询用户信息测试类
    77.     @Test
    78.     public void testSelectId(){
    79.         Emp emp = EmpMapper.selectId(10);
    80.         System.out.println(emp);
    81.     }
    82.  
    83.     //根据条件查询员工
    84.     @Test
    85.     public void testSelect(){
    86.         EmpMapper.list("张", (short) 1, LocalDate.of(2010, 01, 01), LocalDate.of(2020, 01 , 01));
    87.     }
    88.  
    89. }

     三.Mybatis动态SQL

    1.XML映射文件

     

     

     

    1.  //条件查询员工信息
    2.     public List list(String name, Short gender, LocalDate begin, LocalDate end);
    1. "1.0" encoding="UTF-8" ?>
    2. mapper
    3.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
    4.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    5.  
    6. <mapper namespace="com.wjh.mapper.EmpMapper">
    8.     <select id="list" resultType="com.wjh.pojo.Emp">
    9.         select *
    10.         from emp
    11.         where name like concat('%', #{name}, '%')
    12.           and gender = #{gender}
    13.           and entrydate between #{begin} and #{end}
    14.         order by update_time desc
    15.     select>
    16.  
    17. mapper>
    1. //根据条件查询员工
    2.     @Test
    3.     public void testSelect(){
    4.         List empList = EmpMapper.list("张", (short) 1,
    5.                 LocalDate.of(2010, 01, 01),
    6.                 LocalDate.of(2020, 01 , 01));
    7.         System.out.println(empList);
    8.     }

     

     

     

    官方说明icon-default.png?t=N7T8https://mybatis.net.cn/getting-started.html

     

    2.动态SQL

    (1).

     

     

    1. <select id="list" resultType="com.wjh.pojo.Emp">
    12.  
    13.  
    15.         
    16.         select *
    17.         from emp
    18.         <where>
    19.         <if test="name != null">
    20.             name like concat('%', #{name}, '%')
    21.         if>
    22.         <if test="gender != null">
    23.             and gender = #{gender}
    24.         if>
    25.         <if test="begin != null and end != null">
    26.             and entrydate between #{begin} and #{end}
    27.         if>
    28.         where>
    29.             order by update_time desc
    30.     select>

     

    1. //动态更新员工信息(修改)
    2.     //@Update("update emp set username = #{username}, name = #{name}, gender = #{gender}, image = #{image}, job = #{job}, " +
    3.     //        "entrydate = #{entrydate}, dept_id = #{deptId}, update_time = #{updateTime} where id = #{id};")
    4.     public void update2(Emp emp);

     

    2.     <update id="update2">
    3.         update emp
    4.         <set>
    5.             <if test="username != null">
    6.                 username = #{username},
    7.             if>
    8.             <if test="name != null">
    9.                 name = #{name},
    10.             if>
    11.             <if test="gender != null">
    12.                 gender = #{gender},
    13.             if>
    14.             <if test="image != null">
    15.                 image = #{image},
    16.             if>
    17.             <if test="job != null">
    18.                 job = #{job},
    19.             if>
    20.             <if test="entrydate != null">
    21.                 entrydate = #{entrydate},
    22.             if>
    23.             <if test="deptId != null">
    24.                 dept_id = #{deptId},
    25.  
    26.             if>
    27.             <if test="updateTime != null">
    28.                 update_time = #{updateTime}
    29.             if>
    30.         set>
    31.           where id = #{id}
    32.     update>

     

    1.  //动态更新员工信息-- 跟新id = 18 的员工 username 为 Tom11,name = 汤姆111, gander更新为2
    2.  
    3.     @Test
    4.     public void testUpdate2(){
    5.         //构造员工对象
    6.         Emp emp = new Emp();
    7.         emp.setId(19);
    8.         emp.setUsername("tanmgu11");
    9.         emp.setName("汤姆111");
    10.         emp.setGender((short) 2);
    11.         emp.setUpdateTime(LocalDateTime.now());
    12.         //执行员工操作
    13.         EmpMapper.update2(emp);
    14.  
    15.     }


    (2).

     

    1. //批量删除员工
    2.     public void deleteByIds(List ids);
    3.     <delete id="deleteByIds">
    4.         delete from emp where id in
    5.         <foreach collection="ids" item="id" separator="," open="(" close=")">
    6.             #{id}
    7.         foreach>
    8.     delete>
    1. //批量删除员工
    2.     @Test
    3.     public void testdeleteByIds(){
    4.         List list = Arrays.asList(16,17,18);
    5.         EmpMapper.deleteByIds(list);
    6.     }

    (3).

     

     

    1. "1.0" encoding="UTF-8" ?>
    2. mapper
    3.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
    4.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    5.  
    6. <mapper namespace="com.wjh.mapper.EmpMapper">
    7.     
    8.     <sql id="commonSelect">
    9.         select id, username, password, name, gender, image, job, entrydate, dept_id, create_time, update_time
    10.         from emp
    11.     sql>
    12.  
    13.     <select id="list" resultType="com.wjh.pojo.Emp">
    24.  
    25.  
    27.         -- if
    28.         <include refid="commonSelect" />
    29.         <where>
    30.         <if test="name != null">
    31.             name like concat('%', #{name}, '%')
    32.         if>
    33.         <if test="gender != null">
    34.             and gender = #{gender}
    35.         if>
    36.         <if test="begin != null and end != null">
    37.             and entrydate between #{begin} and #{end},
    38.         if>
    39.         where>
    40.             order by update_time desc
    41.     select>
    42.  
    43.  
    45.     <update id="update2">
    46.         update emp
    47.         <set>
    48.             <if test="username != null">
    49.                 username = #{username},
    50.             if>
    51.             <if test="name != null">
    52.                 name = #{name},
    53.             if>
    54.             <if test="gender != null">
    55.                 gender = #{gender},
    56.             if>
    57.             <if test="image != null">
    58.                 image = #{image},
    59.             if>
    60.             <if test="job != null">
    61.                 job = #{job},
    62.             if>
    63.             <if test="entrydate != null">
    64.                 entrydate = #{entrydate},
    65.             if>
    66.             <if test="deptId != null">
    67.                 dept_id = #{deptId},
    68.  
    69.             if>
    70.             <if test="updateTime != null">
    71.                 update_time = #{updateTime}
    72.             if>
    73.         set>
    74.           where id = #{id}
    75.     update>
    76.  
    79.     <delete id="deleteByIds">
    80.         delete from emp where id in
    81.         <foreach collection="ids" item="id" separator="," open="(" close=")">
    82.             #{id}
    83.         foreach>
    84.     delete>
    85.  
    86.  
    87.  
    88. mapper>

  • 相关阅读:
    FFmpeg直播能力更新计划与新版本发布
    如何彻底卸载mysql
    如何在Windows电脑上同时运行多个程序？
    关于POI包处理excel方法详解 （一）
    时间序列分解：将时间序列分解成基本的构建块
    不知道10年老电脑如何重装系统？其实很简单
    2022ICPC 网络赛第二场 B Non-decreasing Array（区间dp）
    .Net Core 实现WebSocket Server 的另外三种方式
    idea怎么快速查看所有断点
    电脑桌面文件删除了怎么恢复？分享3个技巧，你见过第3个吗？
  • 原文地址：https://blog.csdn.net/wangjh11234/article/details/133191484