peteryuanpan / notebook

喜欢的,值得留念的,就记下来,总会有用的。
73 stars 43 forks source link

MyBatis学习总结 #171

Closed peteryuanpan closed 3 years ago

peteryuanpan commented 3 years ago

参考

总结

peteryuanpan commented 3 years ago

参考 最简单的SpringBoot整合MyBatis教程

SpringBoot整合 Mybatis

image

pom.xml,其中 lombok 是需要的

    <dependencies>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.12</version>
            <scope>provided</scope>
        </dependency>

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.0.0</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.20</version>
        </dependency>

    </dependencies>

Application

package com.peter;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class Application {

    public static void main(String[] args) {
        SpringApplication.run(Application.class);
    }
}

application.yml,其中 mybatis.mapper-locations 用于指定扫描 mapper.xml

server:
  port: 8082

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
    username: root
    password: 123456
    type: com.alibaba.druid.pool.DruidDataSource

mybatis:
  mapper-locations:
    - classpath:mapper/*.xml

logging:
  level:
    com:
      peter:
        mapper: debug

EmployeeMapper

<?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.peter.mapper.EmployeeMapper">
    <select id="getEmpById" resultType="com.peter.model.Employee">
    select * from tbl_employee where id = #{id}
  </select>
</mapper>

DaoController

package com.peter.controller;

import com.peter.mapper.EmployeeMapper;
import com.peter.model.Employee;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.List;

@RestController
@RequestMapping("/dao")
public class DaoController {

    private static final Logger logger = LoggerFactory.getLogger(DaoController.class);

    @Autowired
    private EmployeeMapper employeeMapper;

    @GetMapping("/test1")
    public void test1(HttpServletRequest request, HttpServletResponse response) {
        List<Employee> employee = employeeMapper.getEmpById(1);
        logger.info(employee);
    }
}

MybatisConfig

package com.peter.config;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Configuration;

@Configuration
@MapperScan({"com.peter.mapper"})
public class MybatisConfig {
}

EmployeeMapper

package com.peter.mapper;

import com.peter.model.Employee;

import java.util.List;

public interface EmployeeMapper {

    List<Employee> getEmpById(Integer id);
}

Employee

package com.peter.model;

public class Employee {

    private Integer id;
    private String lastName;
    private String email;
    private String gender;

    @Override
    public String toString() {
        return "Emplyee{" +
            "id=" + id +
            ", lastName='" + lastName + '\'' +
            ", email='" + email + '\'' +
            ", gender='" + gender + '\'' +
            '}';
    }
}

启动SpringBoot,使用 Postman 向 http://localhost:8082/dao/test1 发送 GET请求,得到如下输出结果

2020-12-07 17:19:12.879 DEBUG 360 --- [nio-8082-exec-1] c.p.mapper.EmployeeMapper.getEmpById     : ==>  Preparing: select * from tbl_employee where id = ? 
2020-12-07 17:19:12.899 DEBUG 360 --- [nio-8082-exec-1] c.p.mapper.EmployeeMapper.getEmpById     : ==> Parameters: 1(Integer)
2020-12-07 17:19:12.919 DEBUG 360 --- [nio-8082-exec-1] c.p.mapper.EmployeeMapper.getEmpById     : <==      Total: 1
2020-12-07 18:21:22.979  INFO 17084 --- [nio-8082-exec-1] com.peter.controller.DaoController       : [Emplyee{id=1, lastName='null', email='peter@qq.com', gender='0'}]
peteryuanpan commented 3 years ago

这段yml中druid的作用是什么?

参考:Druid简介(Spring Boot + Mybatis + Druid数据源【官方start】) druid是Druid是一个非常优秀的数据库连接池。在功能、性能、扩展性方面,都超过其他数据库连接池,包括DBCP、C3P0、BoneCP、Proxool、JBoss DataSource。Druid已经在阿里巴巴部署了超过600个应用,经过一年多生产环境大规模部署的严苛考验

druid官方文档

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/mall?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
    username: root
    password: 123456
    druid:
      initial-size: 5 #连接池初始化大小
      min-idle: 10 #最小空闲连接数
      max-active: 20 #最大连接数
      web-stat-filter:
        exclusions: "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*" #不统计这些请求数据
      stat-view-servlet: #访问监控网页的登录用户名和密码
        login-username: druid
        login-password: druid
peteryuanpan commented 3 years ago

这段xml中,resultMap以及if语句的含义是什么?

<?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.macro.mall.mapper.UmsAdminMapper">
  <resultMap id="BaseResultMap" type="com.macro.mall.model.UmsAdmin">
    <id column="id" jdbcType="BIGINT" property="id" />
    <result column="username" jdbcType="VARCHAR" property="username" />
    <result column="password" jdbcType="VARCHAR" property="password" />
    <result column="icon" jdbcType="VARCHAR" property="icon" />
    <result column="email" jdbcType="VARCHAR" property="email" />
    <result column="nick_name" jdbcType="VARCHAR" property="nickName" />
    <result column="note" jdbcType="VARCHAR" property="note" />
    <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
    <result column="login_time" jdbcType="TIMESTAMP" property="loginTime" />
    <result column="status" jdbcType="INTEGER" property="status" />
  </resultMap>

  <select id="selectByExample" parameterType="com.macro.mall.model.UmsAdminExample" resultMap="BaseResultMap">
    select
    <if test="distinct">
      distinct
    </if>
    <include refid="Base_Column_List" />
    from ums_admin
    <if test="_parameter != null">
      <include refid="Example_Where_Clause" />
    </if>
    <if test="orderByClause != null">
      order by ${orderByClause}
    </if>
  </select>

</mapper>
peteryuanpan commented 3 years ago

参考

mybatis $ 与 # 的区别

能使用#{}的地方应尽量使用#{}

${}的使用场景,比如动态参数。有两张表,分别是emp_2017 和 emp_2018 .如果需要在查询语句中 动态指定表名,就只能使用${}

<select>
      select *  from emp_ ${year}
<select>

再比如MyBatis 排序时使用order by 动态参数时,此时也只能使用${}

<select>
       select  *  from dept order by ${name}
</select>