SpringBoot整合mybatis

发表于 更新于 分类于 阅读次数： Valine：

配置数据库连接池

1
2
3
4
5
6
7
#驱动包+url+账号+密码
spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/customer
    username: root
    password: 200101

导入驱动

1
2
3
4
5
6
7
8
9
10
11
12
13
<!--阿里巴巴数据库连接池依赖-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.2.9</version>
</dependency>
<!--mysql驱动依赖-->
        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.29</version>
        </dependency>

输出数据源查看，可以看到默认的数据源

1
2
3
4
5
6
7
8
9
10
@SpringBootTest
class RbacProjectApplicationTests {
    @Autowired
    DataSource dataSource;
    @Test
    void contextLoads() {
        System.out.println(dataSource);
    }

}

image-20220523095630811

接下来需要我们指定数据源

1
type: com.alibaba.druid.pool.DruidDataSource

image-20220523101358364

当然此时我们也可以使用druid提供的配置信息，我们重新写一个配置类，进行后台监控

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
package com.zss.rbacproject.config;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;
import java.util.HashMap;

/**
 * @author zss
 */
@Configuration
public class DruidConfig {

    @ConfigurationProperties(prefix = "spring.datasource")
    @Bean
    public DataSource druidDataSource(){
        return new DruidDataSource();
    }
//servlet后台监控
    @Bean
    public ServletRegistrationBean servletRegistrationBean(){
        ServletRegistrationBean<StatViewServlet> bean=new ServletRegistrationBean<>(new StatViewServlet(),"/druid/*");
        //后台需要有人登录，账号密码设置
        HashMap<String ,String> initParameters=new HashMap<>();
        initParameters.put("loginUsername","zss");
        initParameters.put("loginUsername","200101");

        //允许谁可以访问
        initParameters.put("allow","");
        bean.setInitParameters(initParameters);
        return bean;

    }
}

登录账号密码，我们可以查看许多的运行的语句，在这里我们可以监控我们多条语句的执行

image-20220523110002381

当然我们也可以在这里设置过滤器

1
2
3
4
5
6
7
8
9
10
public FilterRegistrationBean webStatFilter(){
    FilterRegistrationBean bean=new FilterRegistrationBean();
    bean.setFilter(new WebStatFilter());
    
    //可以过滤拿一些请求
    Map<String,String > stringStringMap=new HashMap<>();
    stringStringMap.put("exclusions","*.js,*.css,/druid/*");
    bean.setInitParameters(stringStringMap);
    return bean;
}

导入整合包

1
2
3
4
5
6
7
<!-- https://mvnrepository.com/artifact/org.mybatis.spring.boot/mybatis-spring-boot-starter -->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.2.2</version>
</dependency>

建立类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
package com.zss.rbacproject.pojo;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import org.springframework.stereotype.Component;

import java.util.List;
import java.util.Set;

/**
 * @author zss
 */
@Component
public class Employee {
    @TableId(value = "id",type= IdType.AUTO)
    private Long id;
    private String name;
    private String password;
    private String email;
    private Integer age;
    private boolean admin=false;
    private Long deptId;
    @TableField(exist = false)
    private String deptName;
    @TableField(exist = false)
    private List<Role> roles;
    @TableField(exist = false)
    Set<String> expressions;

    public Set<String> getExpressions() {
        return expressions;
    }

    public void setExpressions(Set<String> expressions) {
        this.expressions = expressions;
    }

    public List<Role> getRoles() {
        return roles;
    }

    public void setRoles(List<Role> roles) {
        this.roles = roles;
    }

    public String getDeptName() {
        return deptName;
    }

    public void setDeptName(String deptName) {
        this.deptName = deptName;
    }

    public Employee() {
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public boolean isAdmin() {
        return admin;
    }

    public void setAdmin(boolean admin) {
        this.admin = admin;
    }

    public Long getDeptId() {
        return deptId;
    }

    public void setDeptId(Long deptId) {
        this.deptId = deptId;
    }
}

建立接口类

1
2
3
4
5
6
7
8
9
10
11
@Repository
public interface EmployeeMapper extends BaseMapper<Employee> {
    IPage<Employee> selectPage(EmployeeQueryObject qo);

    int insertBatchRelation(@Param("id") Long id, List<Long> roleIds);

    void deleteRoleRelation(Long id);

    Set<String> selectExpressions(Long id);
}

建立对应的映射文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
<?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.zss.rbacproject.mapper.EmployeeMapper">

    <insert id="insertBatchRelation">
        insert into employee_role(employee_id, role_id) VALUES 
            <foreach collection="roleIds" item="role_id" separator=",">
                (#{id},#{role_id})
            </foreach>
    </insert>
    <delete id="deleteRoleRelation">
        delete from employee_role where employee_id=#{id}
    </delete>


    <select id="selectPage" resultType="com.zss.rbacproject.pojo.Employee">
        select employee.id, employee.name, password, email, age, admin, dept_id, d.name as deptName, sn from employee join department d on employee.dept_id = d.id
        <where>
            <if test="keyword!= null and keyword != '' ">
                and (employee.name like CONCAT('%',#{keyword},'%') or email like CONCAT('%',#{keyword},'%'))
            </if>
            <if test="deptId != -1" >
                and dept_id=#{deptId}
            </if>
        </where>
    </select>
    <select id="selectExpressions" resultType="java.lang.String">
        select pr.expression from permission as pr join role_permission rp on pr.id = rp.permission_id join employee_role er on rp.role_id = er.role_id where employee_id=#{id}
    </select>

</mapper>

告知springbootmapper文件的位置

1
2
3
4
mybatis-plus:
#这里是配置包的别名
  type-aliases-package: com.zss.rbacproject.pojo
  mapper-locations: classpath:/mapper/*.xml