Web学习day04

  • 开发
  • 21

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

<if>

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

<where>

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

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

<set>

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

<foreach >

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

<sql>

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

<include>

引入sql片段

四、配置文件

4.1settings标签

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

4.2mappers标签

加载Mapper接口位置

4.3environments标签

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

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

五、案例

5.1数据表

5.2实现类

代码如下（示例）：

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

import java.time.LocalDate;
import java.time.LocalDateTime;

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Emp {
    private Integer id;
    private String username;
    private String password;
    private String name;
    private Short gender;
    private String image;
    private Short job;

    //注意:这四个属性跟数据表中的字段不一致
    private LocalDate ed;

    private Integer deptId;
    private LocalDateTime createTime;
    private LocalDateTime updateTime;
}

5.3mapper实现

代码如下：

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;

import java.time.LocalDate;
import java.util.Date;
import java.util.List;

public interface EmpMapper {

    @Select("select * from emp")
    public List<Emp> findAll();

    @Select("select * from emp")
    public List<Emp> findAll1();

    @Select("select id,username,password,name,gender,image,job,entrydate ed,dept_id,create_time,update_time from emp")
    public List<Emp> findAll2();

    @Select("select * from emp")
    @Results({
            @Result(column = "entrydate",property = "ed")
    })
    public List<Emp> findAll3();

    @Select("select * from emp where name =#{name} and gender = #{gender} and entrydate between #{begin} and #{end} ")
    @Results({
            @Result(column = "entrydate",property = "ed")
    })
    public List<Emp> findByCondition(@Param("name") String name,@Param("gender") Integer gender,@Param("begin") LocalDate begin,@Param("end") LocalDate end);

    @Select("select * from emp where name like concat('%',#{name},'%') and gender = #{gender} and entrydate between #{begin} and #{end} ")
    @Results({
            @Result(column = "entrydate",property = "ed")
    })
    public List<Emp> findByCondition2(@Param("name") String name,@Param("gender") Integer gender,@Param("begin") LocalDate begin,@Param("end") LocalDate end);

    public Emp findById(Integer id);

    List<Emp> findByCondition3(@Param("name") String name,@Param("gender") Short gender,@Param("begin") LocalDate begin,@Param("end") LocalDate end);
    
    void update(Emp emp);

    void deleteByIds(@Param("ids") List<Integer> ids);
}

5.4工具类实现

代码如下：

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.InputStream;

public class MybatisUtil {

    private static SqlSessionFactory sqlSessionFactory = null;

    //保证SqlSessionFactory仅仅创建一次
    static {
        try {
            //读取配置文件
            InputStream stream = Resources.getResourceAsStream("SqlMapConfig.xml");
            //创建SqlSessionFactory
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(stream);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //获取sqlSession
    public static SqlSession getSqlSession() {
        return sqlSessionFactory.openSession();
    }

    //提交事务 关闭sqlSession
    public static void close(SqlSession sqlSession) {
        if (sqlSession != null) {
            //提交事务
            sqlSession.commit();
            //释放资源
            sqlSession.close();
        }
    }
}

5.5XML动态SQL实现

代码如下：

<?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.itheima.mapper.EmpMapper">

    <sql id="mySql">
        select * from emp
    </sql>

    <resultMap id="MyMap" type="com.itheima.domain.Emp">
        <result column="entrydate" property="ed"/>
    </resultMap>
    <select id="findById" resultMap="MyMap">
        <include refid="mySql"/>
        where id = #{id}
    </select>


    <select id="findByCondition3" resultType="com.itheima.domain.Emp">
        <include refid="mySql"/>
        <where>
            <if test="name != null and name !=''">
                name like concat('%',#{name},'%')
            </if>
            <if test="gender != null">
                and gender = #{gender}
            </if>
            <if test="begin != null and end != null">
                and entrydate between #{begin} and #{end}
            </if>
        </where>
    </select>

    <update id="update">
        update emp
        <set>
            <if test="username != null and username != ''">
                username = #{username},
            </if>
            <if test="password != null and password != ''">
                password = #{password},
            </if>
            <if test="name != null and name != ''">
                name = #{name},
            </if>
            <if test="gender != null">
                gender = #{gender},
            </if>
            <if test="image != null and image != ''">
                image = #{image},
            </if>
            <if test="job != null">
                job = #{job},
            </if>
            <if test="ed != null">
                entrydate = #{ed},
            </if>
            <if test="deptId != null">
                dept_id = #{deptId},
            </if>
            <if test="createTime != null">
                create_time = #{createTime},
            </if>
            <if test="updateTime != null">
                update_time = #{updateTime},
            </if>
        </set>
        where id = #{id}
    </update>

    <delete id="deleteByIds">
        delete from emp where id in
        <foreach collection="ids" item="id" separator="," open="(" close=")">
            #{id}
        </foreach>
    </delete>


</mapper>

5.6XML配置实现

代码如下：

<?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"/>
        <!--在控制台输出发送的sql日志-->
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>


    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>

            <!--目前只关注这部分内容,它的作用就是声明要连接的数据信息-->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <!--声明含有sql的接口所在包-->
        <package name="com.itheima.mapper"/>
    </mappers>
</configuration>

5.7测试实现

代码如下：

package com.itheima.test;

import com.itheima.domain.Emp;
import com.itheima.mapper.EmpMapper;
import com.itheima.util.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.Arrays;
import java.util.List;

public class EmpMapperTest {

    // 测试查询所有
    @Test
    public void testFindAll(){

        SqlSession sqlSession = MybatisUtil.getSqlSession();
        EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
        List<Emp> empList = empMapper.findAll();
        for (Emp emp : empList) {
            System.out.println(emp);
        }
        MybatisUtil.close(sqlSession);
    }
    // 测试结果集映射开启驼峰命名规则
    @Test
    public void testFindAll1(){

        SqlSession sqlSession = MybatisUtil.getSqlSession();
        EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
        List<Emp> empList = empMapper.findAll1();
        for (Emp emp : empList) {
            System.out.println(emp);
        }
        MybatisUtil.close(sqlSession);
    }
    // 测试结果集映射起别名
    @Test
    public void testFindAll2(){

        SqlSession sqlSession = MybatisUtil.getSqlSession();
        EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
        List<Emp> empList = empMapper.findAll2();
        for (Emp emp : empList) {
            System.out.println(emp);
        }
        MybatisUtil.close(sqlSession);
    }
    // 测试结果集映射手动结果映射@Results @Result
    @Test
    public void testFindAll3(){

        SqlSession sqlSession = MybatisUtil.getSqlSession();
        EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
        List<Emp> empList = empMapper.findAll3();
        for (Emp emp : empList) {
            System.out.println(emp);
        }
        MybatisUtil.close(sqlSession);
    }
    // 测试条件查询
    @Test
    public void testFindCondition(){

        SqlSession sqlSession = MybatisUtil.getSqlSession();
        EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
        List<Emp> empList = empMapper.findByCondition("张三丰",1, LocalDate.of(2000,1,1),LocalDate.of(2020,1,1));
        for (Emp emp : empList) {
            System.out.println(emp);
        }
        MybatisUtil.close(sqlSession);
    }
    // 测试模糊查询
    @Test
    public void testFindCondition2(){

        SqlSession sqlSession = MybatisUtil.getSqlSession();
        EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
        List<Emp> empList = empMapper.findByCondition2("张",1, LocalDate.of(2000,1,1),LocalDate.of(2020,1,1));
        for (Emp emp : empList) {
            System.out.println(emp);
        }
        MybatisUtil.close(sqlSession);
    }
    // 测试根据id查询
    @Test
    public void testFindById(){

        SqlSession sqlSession = MybatisUtil.getSqlSession();
        EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
        Emp emp1 = empMapper.findById(4);
        System.out.println(emp1);
        MybatisUtil.close(sqlSession);
    }

    //条件查询
    @Test
    public void testFindByCondition() {
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);

        //List<Emp> empList = empMapper.findByCondition3("张", (short) 1, LocalDate.of(2002, 01, 01), LocalDate.of(2023, 12, 31));
        //List<Emp> empList = empMapper.findByCondition3("张", (short) 1, null, null);
        List<Emp> empList = empMapper.findByCondition3("", (short) 1, null, null);
        empList.forEach(e -> System.out.println(e));//lambda方式打印

        MybatisUtil.close(sqlSession);
    }

    //更新
    @Test
    public void testUpdate() {
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);

        Emp emp = new Emp();
        emp.setId(2);
        emp.setUsername("haha2");
        emp.setName("sdnajn");
        emp.setGender( (short) 1);
        emp.setImage("haha.jpg");
        emp.setJob((short) 2);
        emp.setDeptId(1);
        emp.setCreateTime(LocalDateTime.of(2023, 1, 1, 1, 1,1));
        emp.setUpdateTime(LocalDateTime.now());

        empMapper.update(emp);

        MybatisUtil.close(sqlSession);
    }

    //批量删除
    @Test
    public void deleteByIds() {
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);

        empMapper.deleteByIds(Arrays.asList(13, 14, 15));

        MybatisUtil.close(sqlSession);
    }

}

5.8pom.xml配置

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.itheima</groupId>
    <artifactId>day04-01-mybatis</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>17</maven.compiler.source>
        <maven.compiler.target>17</maven.compiler.target>
    </properties>

    <dependencies>
        <!--mysql-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.28</version>
        </dependency>
        <!--mybatis-->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.9</version>
        </dependency>
        <!--junit-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13.2</version>
            <scope>test</scope>
        </dependency>
        <!--lombok-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.26</version>
        </dependency>
    </dependencies>

</project>

总结

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

阅读全部

相关推荐

  3. Web学习day05

    2024-07-14 13:32:05       24 阅读

  9. MySQL学习day04（一）

    2024-07-14 13:32:05       53 阅读

最近更新

  3. docker php8.1+nginx base 镜像 dockerfile 配置

    2024-07-14 13:32:05       66 阅读

  4. Could not load dynamic library ‘cudart64_100.dll‘

    2024-07-14 13:32:05       70 阅读

  9. 在Django里面运行非项目文件

    2024-07-14 13:32:05       57 阅读

  19. Python语言-面向对象

    2024-07-14 13:32:05       68 阅读

  20. 如何分清楚常见的 Git 分支管理策略Git Flow、GitHub Flow 和 GitLab Flow

    2024-07-14 13:32:05       62 阅读

热门阅读

  1. python-生成器generator

    2024-07-14 13:32:05       21 阅读

  2. ACE之ACE_Handle_Set

    2024-07-14 13:32:05       20 阅读

  3. 微服务有哪些组件？

    2024-07-14 13:32:05       18 阅读

  8. LVM调整VG和PV

    2024-07-14 13:32:05       23 阅读

  10. docker部署sentinel

    2024-07-14 13:32:05       20 阅读

  13. 在Spring Boot中集成分布式日志系统

    2024-07-14 13:32:05       31 阅读

  15. PHP FTP: A Comprehensive Guide

    2024-07-14 13:32:05       24 阅读

  16. 林毅夫谈中国未来经济：创业者如何把握机遇，实现可持续发展？

    2024-07-14 13:32:05       28 阅读

  20. 深入解析std::string的设计哲学【C++、STL库】

    2024-07-14 13:32:05       20 阅读

  21. 常用几种远程控制协议总结(telnet,rlogin,ssh,rfb,rdp)

    2024-07-14 13:32:05       18 阅读

  22. Rockchip RK3588 - 从零开始制作recovery系统

    2024-07-14 13:32:05       20 阅读

  25. 护网HW面试—apache&iis&nginx中间件解析漏洞篇

    2024-07-14 13:32:05       28 阅读

  26. 【Flask从入门到精通：第十课：数据基本查询、关联查询】

    2024-07-14 13:32:05       21 阅读

  27. PHP使用，按时间水平分表，跨月多表条件查询数据分页显示

    2024-07-14 13:32:05       21 阅读

  28. 响应状态码

    2024-07-14 13:32:05       23 阅读

  29. python生成器与迭代器

    2024-07-14 13:32:05       27 阅读