Eclipse's Blog
0%

newcoder项目前置知识之Mybatis

发表于 更新于 分类于

官方文档mybatis-spring

核心组件

SqlSessionFactory

负责创建SqlSession对象

SqlSession

用于与数据库进行交互。它提供了各种方法来执行SQL语句、获取Mapper接口的实例以及管理事务

XML配置文件

mybatis底层配置

application.properties配置

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
# DataSourceProperties，统一管理连接，反复使用，管理连接池
# 数据库驱动
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
# 数据库地址
spring.datasource.url=jdbc:mysql://localhost:3306/community?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=HongKong
# 数据库用户名
spring.datasource.username=root
spring.datasource.password=123456
# 连接池
spring.datasource.type=com.zaxxer.hikari.HikariDataSource
# 连接池最大连接数
spring.datasource.hikari.maximum-pool-size=15
# 连接池最小空闲连接
spring.datasource.hikari.minimum-idle=5
# 连接池等多久把空闲的关掉
spring.datasource.hikari.idle-timeout=30000

# MybatisProperties
# 映射文件存放位置
mybatis.mapper-locations=classpath:mapper/*.xml
# 实体类所在包名，封装某个表的数据
mybatis.type-aliases-package=com.newcoder.community.entity
# 启动id自增长
mybatis.configuration.use-generated-keys=true
# 表的字段不区分大小写，属性名是驼峰命令，这个属性让表的字段和属性相匹配
mybatis.configuration.map-underscore-to-camel-case=true

Mapper接口

定义数据访问操作的接口，通常对应于数据库中的一个表或一个实体

1
2
3
4
5
6
7
8
9
10
11
12
@Mapper
public interface UserMapper {

    User selectId(int id);
    User selectByName(String username);
    User selectByEmail(String email);
    int insertUser(User user);
    int updateStatus(int id, int status);
    int updateHeader(int id, String headUrl);
    int updatePassword(int id, String password);

}

Mapper映射器

用于将Mapper接口中的方法与对应的SQL语句进行映射，可以用XML也可以用注解写。

对UserMapper中每一个方法都要写一个标签对应，很容易写错

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
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.newcoder.community.dao.UserMapper">
    <sql id="selectField">
        id, username, password, salt, email, type, status, activation_code, header_url, create_time
    </sql>
    <sql id="insertFile">
        username, password, salt, email, type, status, activation_code, header_url, create_time
    </sql>
    <select id="selectById" resultType="User">
        select <include refid="selectField"></include>
        from user
        where id = #{id}
    </select>
    <select id="selectByName" resultType="User">
        select <include refid="selectField"></include>
        from user
        where username = #{username}
    </select>
    <select id="selectByEmail" resultType="User">
        select <include refid="selectField"></include>
        from user
        where email = #{email}
    </select>

    <insert id="insertUser" parameterType="User"  keyProperty="id">
        insert into user (<include refid="insertFile"></include>)
        value (#{username}, #{password}, #{salt}, #{email}, #{type}, #{status}, #{activationCode}, #{headerUrl},
        #{createTime})
    </insert>

    <update id="updateStatus">
        update user set status = #{status} where id = #{id}
    </update>

    <update id="updateHeader">
        update user set header_url = #{headerUrl} where id = #{id}
    </update>

    <update id="updatePassword">
        update user set password = #{password} where id = #{id}
    </update>

    <delete id="deleteByName">
        delete
        from user
        where username = #{username}
    </delete>
</mapper>

Mybatis的注解

@Param注解

用于解决方法参数与SQL语句中的参数对应关系不明确的情况，给参数名起别名

XML文件中使用动态SQL，要拼接SQL语句时，参数中一定要用@Param注解

问题

1. 连不上数据库

报错:

1
2
3
4
5
6
7
org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)
### The error may exist in file [D:\learning\projects\newcoder\community\target\classes\mapper\user-mapper.xml]
### The error may involve com.newcoder.community.dao.UserMapper.selectById
### The error occurred while executing a query
### Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)

可能的原因：

  1. 用户名，密码写错
  2. 查询数据库的语句写错
  3. 数据库的连接地址不对

我的问题是，查询语句中的属性名写错了。

在mapper文件中表名爆红，不会报错。

为了便于调试可以把日志级别在配置里改为debug

1
2
# logger
logging.level.com.newcoder.community=debug