• Web学习day04

    mybatis

    目录

    mybatis

    文章目录

    一、查询

    1.1结果映射

    1.2多条件查询

    1.3模糊查询

    二、XML

    书写规范

    三、动态SQL

    四、配置文件

    4.1settings标签

    4.2mappers标签

    4.3environments标签

    五、案例

    5.1数据表

    5.2实现类

    5.3mapper实现

    5.4工具类实现

    5.5XML动态SQL实现

    5.6XML配置实现

    5.7测试实现

    5.8pom.xml配置

    总结

    一、查询

    1.1结果映射

    开启驼峰映射：如果字段名与属性名符合驼峰命名规则，mybatis会自动通过驼峰命名规则映射。

    字段起别名：在SQL语句中，对不一样的列名起别名，别名和实体类属性名一样。

    @Results @Result：通过 @Results及@Result 进行手动结果映射。

    1.2多条件查询

    @Param 标注在方法参数的前面,用于声明参数在#{}中的名字

    1.3模糊查询

    ${}性能低，不安全，存在SQL注入问题：

    #{}推荐：

    二、XML

    书写规范

    XML文件的名称与Mapper接口名称一致，并且放置在相同包下（同包同名）。

    XML文件的namespace属性为Mapper接口全限定名一致。

    XML文件中sql语句的id与Mapper 接口中的方法名一致。

    XML文件中sql语句的返回类型与Mapper 接口中的方法返回类型一致。

    三、动态SQL

    用于判断条件是否成立，如果条件为true，则拼接SQL

    where 元素只会在子元素有内容的情况下才插入where子句

    而且会自动去除子句的开头的AND 或OR

    动态地在行首插入SET关键字，并会删掉额外的逗号（用在update语句中）

    用来批量处理的 比如批量删除拼接 in后面的值

    定义一个sql片段 就是提取公共的sql

    引入sql片段

    四、配置文件

    4.1settings标签

    控制一些全局配置项的开闭

    4.2mappers标签

    加载Mapper接口位置

    4.3environments标签

    Druid(德鲁伊):  阿里巴巴提供的数据库连接池技术，国内使用率很高，提供了完善的监控机制

    HikariCP:  日本人开发的连接池技术，号称性能之王，速度最快，SpringBoot2.0默认使用此连接池

    五、案例

    5.1数据表

    5.2实现类

    代码如下（示例）：

    1. import lombok.AllArgsConstructor;
    2. import lombok.Data;
    3. import lombok.NoArgsConstructor;
    4.  
    5. import java.time.LocalDate;
    6. import java.time.LocalDateTime;
    7.  
    8. @Data
    9. @NoArgsConstructor
    10. @AllArgsConstructor
    11. public class Emp {
    12.     private Integer id;
    13.     private String username;
    14.     private String password;
    15.     private String name;
    16.     private Short gender;
    17.     private String image;
    18.     private Short job;
    19.  
    20.     //注意:这四个属性跟数据表中的字段不一致
    21.     private LocalDate ed;
    22.  
    23.     private Integer deptId;
    24.     private LocalDateTime createTime;
    25.     private LocalDateTime updateTime;
    26. }

    5.3mapper实现

    代码如下：

    1. import org.apache.ibatis.annotations.Param;
    2. import org.apache.ibatis.annotations.Result;
    3. import org.apache.ibatis.annotations.Results;
    4. import org.apache.ibatis.annotations.Select;
    5.  
    6. import java.time.LocalDate;
    7. import java.util.Date;
    8. import java.util.List;
    9.  
    10. public interface EmpMapper {
    11.  
    12.     @Select("select * from emp")
    13.     public List findAll();
    14.  
    15.     @Select("select * from emp")
    16.     public List findAll1();
    17.  
    18.     @Select("select id,username,password,name,gender,image,job,entrydate ed,dept_id,create_time,update_time from emp")
    19.     public List findAll2();
    20.  
    21.     @Select("select * from emp")
    22.     @Results({
    23.             @Result(column = "entrydate",property = "ed")
    24.     })
    25.     public List findAll3();
    26.  
    27.     @Select("select * from emp where name =#{name} and gender = #{gender} and entrydate between #{begin} and #{end} ")
    28.     @Results({
    29.             @Result(column = "entrydate",property = "ed")
    30.     })
    31.     public List findByCondition(@Param("name") String name,@Param("gender") Integer gender,@Param("begin") LocalDate begin,@Param("end") LocalDate end);
    32.  
    33.     @Select("select * from emp where name like concat('%',#{name},'%') and gender = #{gender} and entrydate between #{begin} and #{end} ")
    34.     @Results({
    35.             @Result(column = "entrydate",property = "ed")
    36.     })
    37.     public List findByCondition2(@Param("name") String name,@Param("gender") Integer gender,@Param("begin") LocalDate begin,@Param("end") LocalDate end);
    38.  
    39.     public Emp findById(Integer id);
    40.  
    41.     List findByCondition3(@Param("name") String name,@Param("gender") Short gender,@Param("begin") LocalDate begin,@Param("end") LocalDate end);
    42.     
    43.     void update(Emp emp);
    44.  
    45.     void deleteByIds(@Param("ids") List ids);
    46. }

    5.4工具类实现

    代码如下：

    1. import org.apache.ibatis.io.Resources;
    2. import org.apache.ibatis.session.SqlSession;
    3. import org.apache.ibatis.session.SqlSessionFactory;
    4. import org.apache.ibatis.session.SqlSessionFactoryBuilder;
    5.  
    6. import java.io.InputStream;
    7.  
    8. public class MybatisUtil {
    9.  
    10.     private static SqlSessionFactory sqlSessionFactory = null;
    11.  
    12.     //保证SqlSessionFactory仅仅创建一次
    13.     static {
    14.         try {
    15.             //读取配置文件
    16.             InputStream stream = Resources.getResourceAsStream("SqlMapConfig.xml");
    17.             //创建SqlSessionFactory
    18.             sqlSessionFactory = new SqlSessionFactoryBuilder().build(stream);
    19.         } catch (Exception e) {
    20.             e.printStackTrace();
    21.         }
    22.     }
    23.  
    24.     //获取sqlSession
    25.     public static SqlSession getSqlSession() {
    26.         return sqlSessionFactory.openSession();
    27.     }
    28.  
    29.     //提交事务 关闭sqlSession
    30.     public static void close(SqlSession sqlSession) {
    31.         if (sqlSession != null) {
    32.             //提交事务
    33.             sqlSession.commit();
    34.             //释放资源
    35.             sqlSession.close();
    36.         }
    37.     }
    38. }

    5.5XML动态SQL实现

    代码如下：

    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. <mapper namespace="com.itheima.mapper.EmpMapper">
    6.  
    7.     <sql id="mySql">
    8.         select * from emp
    9.     sql>
    10.  
    11.     <resultMap id="MyMap" type="com.itheima.domain.Emp">
    12.         <result column="entrydate" property="ed"/>
    13.     resultMap>
    14.     <select id="findById" resultMap="MyMap">
    15.         <include refid="mySql"/>
    16.         where id = #{id}
    17.     select>
    18.  
    19.  
    20.     <select id="findByCondition3" resultType="com.itheima.domain.Emp">
    21.         <include refid="mySql"/>
    22.         <where>
    23.             <if test="name != null and name !=''">
    24.                 name like concat('%',#{name},'%')
    25.             if>
    26.             <if test="gender != null">
    27.                 and gender = #{gender}
    28.             if>
    29.             <if test="begin != null and end != null">
    30.                 and entrydate between #{begin} and #{end}
    31.             if>
    32.         where>
    33.     select>
    34.  
    35.     <update id="update">
    36.         update emp
    37.         <set>
    38.             <if test="username != null and username != ''">
    39.                 username = #{username},
    40.             if>
    41.             <if test="password != null and password != ''">
    42.                 password = #{password},
    43.             if>
    44.             <if test="name != null and name != ''">
    45.                 name = #{name},
    46.             if>
    47.             <if test="gender != null">
    48.                 gender = #{gender},
    49.             if>
    50.             <if test="image != null and image != ''">
    51.                 image = #{image},
    52.             if>
    53.             <if test="job != null">
    54.                 job = #{job},
    55.             if>
    56.             <if test="ed != null">
    57.                 entrydate = #{ed},
    58.             if>
    59.             <if test="deptId != null">
    60.                 dept_id = #{deptId},
    61.             if>
    62.             <if test="createTime != null">
    63.                 create_time = #{createTime},
    64.             if>
    65.             <if test="updateTime != null">
    66.                 update_time = #{updateTime},
    67.             if>
    68.         set>
    69.         where id = #{id}
    70.     update>
    71.  
    72.     <delete id="deleteByIds">
    73.         delete from emp where id in
    74.         <foreach collection="ids" item="id" separator="," open="(" close=")">
    75.             #{id}
    76.         foreach>
    77.     delete>
    78.  
    79.  
    80. mapper>

    5.6XML配置实现

    代码如下：

    1. "1.0" encoding="UTF-8" ?>
    2. configuration
    3.         PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
    4.         "http://mybatis.org/dtd/mybatis-3-config.dtd">
    5. <configuration>
    6.  
    7.     <settings>
    8.         <setting name="mapUnderscoreToCamelCase" value="true"/>
    9.         
    10.         <setting name="logImpl" value="STDOUT_LOGGING"/>
    11.     settings>
    12.  
    13.  
    14.     <environments default="development">
    15.         <environment id="development">
    16.             <transactionManager type="JDBC"/>
    17.  
    18.             
    19.             <dataSource type="POOLED">
    20.                 <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
    21.                 <property name="url" value="jdbc:mysql://localhost:3306/mybatis"/>
    22.                 <property name="username" value="root"/>
    23.                 <property name="password" value="root"/>
    24.             dataSource>
    25.         environment>
    26.     environments>
    27.  
    28.     <mappers>
    29.         
    30.         <package name="com.itheima.mapper"/>
    31.     mappers>
    32. configuration>

    5.7测试实现

    代码如下：

    1. package com.itheima.test;
    2.  
    3. import com.itheima.domain.Emp;
    4. import com.itheima.mapper.EmpMapper;
    5. import com.itheima.util.MybatisUtil;
    6. import org.apache.ibatis.session.SqlSession;
    7. import org.junit.Test;
    8.  
    9. import java.time.LocalDate;
    10. import java.time.LocalDateTime;
    11. import java.util.Arrays;
    12. import java.util.List;
    13.  
    14. public class EmpMapperTest {
    15.  
    16.     // 测试查询所有
    17.     @Test
    18.     public void testFindAll(){
    19.  
    20.         SqlSession sqlSession = MybatisUtil.getSqlSession();
    21.         EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
    22.         List empList = empMapper.findAll();
    23.         for (Emp emp : empList) {
    24.             System.out.println(emp);
    25.         }
    26.         MybatisUtil.close(sqlSession);
    27.     }
    28.     // 测试结果集映射开启驼峰命名规则
    29.     @Test
    30.     public void testFindAll1(){
    31.  
    32.         SqlSession sqlSession = MybatisUtil.getSqlSession();
    33.         EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
    34.         List empList = empMapper.findAll1();
    35.         for (Emp emp : empList) {
    36.             System.out.println(emp);
    37.         }
    38.         MybatisUtil.close(sqlSession);
    39.     }
    40.     // 测试结果集映射起别名
    41.     @Test
    42.     public void testFindAll2(){
    43.  
    44.         SqlSession sqlSession = MybatisUtil.getSqlSession();
    45.         EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
    46.         List empList = empMapper.findAll2();
    47.         for (Emp emp : empList) {
    48.             System.out.println(emp);
    49.         }
    50.         MybatisUtil.close(sqlSession);
    51.     }
    52.     // 测试结果集映射手动结果映射@Results @Result
    53.     @Test
    54.     public void testFindAll3(){
    55.  
    56.         SqlSession sqlSession = MybatisUtil.getSqlSession();
    57.         EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
    58.         List empList = empMapper.findAll3();
    59.         for (Emp emp : empList) {
    60.             System.out.println(emp);
    61.         }
    62.         MybatisUtil.close(sqlSession);
    63.     }
    64.     // 测试条件查询
    65.     @Test
    66.     public void testFindCondition(){
    67.  
    68.         SqlSession sqlSession = MybatisUtil.getSqlSession();
    69.         EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
    70.         List empList = empMapper.findByCondition("张三丰",1, LocalDate.of(2000,1,1),LocalDate.of(2020,1,1));
    71.         for (Emp emp : empList) {
    72.             System.out.println(emp);
    73.         }
    74.         MybatisUtil.close(sqlSession);
    75.     }
    76.     // 测试模糊查询
    77.     @Test
    78.     public void testFindCondition2(){
    79.  
    80.         SqlSession sqlSession = MybatisUtil.getSqlSession();
    81.         EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
    82.         List empList = empMapper.findByCondition2("张",1, LocalDate.of(2000,1,1),LocalDate.of(2020,1,1));
    83.         for (Emp emp : empList) {
    84.             System.out.println(emp);
    85.         }
    86.         MybatisUtil.close(sqlSession);
    87.     }
    88.     // 测试根据id查询
    89.     @Test
    90.     public void testFindById(){
    91.  
    92.         SqlSession sqlSession = MybatisUtil.getSqlSession();
    93.         EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
    94.         Emp emp1 = empMapper.findById(4);
    95.         System.out.println(emp1);
    96.         MybatisUtil.close(sqlSession);
    97.     }
    98.  
    99.     //条件查询
    100.     @Test
    101.     public void testFindByCondition() {
    102.         SqlSession sqlSession = MybatisUtil.getSqlSession();
    103.         EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
    104.  
    105.         //List empList = empMapper.findByCondition3("张", (short) 1, LocalDate.of(2002, 01, 01), LocalDate.of(2023, 12, 31));
    106.         //List empList = empMapper.findByCondition3("张", (short) 1, null, null);
    107.         List empList = empMapper.findByCondition3("", (short) 1, null, null);
    108.         empList.forEach(e -> System.out.println(e));//lambda方式打印
    109.  
    110.         MybatisUtil.close(sqlSession);
    111.     }
    112.  
    113.     //更新
    114.     @Test
    115.     public void testUpdate() {
    116.         SqlSession sqlSession = MybatisUtil.getSqlSession();
    117.         EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
    118.  
    119.         Emp emp = new Emp();
    120.         emp.setId(2);
    121.         emp.setUsername("haha2");
    122.         emp.setName("sdnajn");
    123.         emp.setGender( (short) 1);
    124.         emp.setImage("haha.jpg");
    125.         emp.setJob((short) 2);
    126.         emp.setDeptId(1);
    127.         emp.setCreateTime(LocalDateTime.of(2023, 1, 1, 1, 1,1));
    128.         emp.setUpdateTime(LocalDateTime.now());
    129.  
    130.         empMapper.update(emp);
    131.  
    132.         MybatisUtil.close(sqlSession);
    133.     }
    134.  
    135.     //批量删除
    136.     @Test
    137.     public void deleteByIds() {
    138.         SqlSession sqlSession = MybatisUtil.getSqlSession();
    139.         EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
    140.  
    141.         empMapper.deleteByIds(Arrays.asList(13, 14, 15));
    142.  
    143.         MybatisUtil.close(sqlSession);
    144.     }
    145.  
    146. }

    5.8pom.xml配置

    1. "1.0" encoding="UTF-8"?>
    2. <project xmlns="http://maven.apache.org/POM/4.0.0"
    3.          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    4.          xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    5.     <modelVersion>4.0.0modelVersion>
    6.  
    7.     <groupId>com.itheimagroupId>
    8.     <artifactId>day04-01-mybatisartifactId>
    9.     <version>1.0-SNAPSHOTversion>
    10.  
    11.     <properties>
    12.         <maven.compiler.source>17maven.compiler.source>
    13.         <maven.compiler.target>17maven.compiler.target>
    14.     properties>
    15.  
    16.     <dependencies>
    17.         
    18.         <dependency>
    19.             <groupId>mysqlgroupId>
    20.             <artifactId>mysql-connector-javaartifactId>
    21.             <version>8.0.28version>
    22.         dependency>
    23.         
    24.         <dependency>
    25.             <groupId>org.mybatisgroupId>
    26.             <artifactId>mybatisartifactId>
    27.             <version>3.5.9version>
    28.         dependency>
    29.         
    30.         <dependency>
    31.             <groupId>junitgroupId>
    32.             <artifactId>junitartifactId>
    33.             <version>4.13.2version>
    34.             <scope>testscope>
    35.         dependency>
    36.         
    37.         <dependency>
    38.             <groupId>org.projectlombokgroupId>
    39.             <artifactId>lombokartifactId>
    40.             <version>1.18.26version>
    41.         dependency>
    42.     dependencies>
    43.  
    44. project>

    总结

    以上就是今天学习的内容。

  • 相关阅读:
    MAXScript - tyFlow for 3dsMax
    spring bean管理2
    Jenkins安装
    Grafana 系列-GaC-2-Grafana Terraform Provider 基础
    首发Yolov8优化：Adam该换了！斯坦福最新Sophia优化器，比Adam快2倍 | 2023.5月斯坦福最新成果
    最新SQL注入漏洞修复建议
    高性能数据访问中间件 OBProxy（四）：一文讲透连接管理
    SOME/IP TTL 在各种Entry 中各是什么意思？有什么限制？
    架构师范文（AI写作）两篇
    Linux下Cmake安装或版本更新
  • 原文地址：https://blog.csdn.net/filthy555/article/details/140406602