遇一山，过一山，处处有风景；只要勇敢向前，一路尽是繁花盛开。 | （点击查看→）【测试干货】python/java自动化、持续集成、性能、测开、简历、笔试面试等

MyBatis简易教程（01）：mybatis基础

 

MyBatis简易教程汇总，详见：https://www.cnblogs.com/uncleyong/p/17984096

简介

MyBatis是一个支持普通SQL查询、存储过程和高级映射的优秀持久层框架（ORM框架），可以使用简单的XML或注解配置，将接口和Java的POJO映射成数据库中的记录。

ORM概念：Object Relational Mapping，“对象关系映射”，解决了对象和关系型数据库之间的数据交互问题

?
1
2
3
4
数据库表--->实体类
数据库表中字段--->实体类的属性
数据库表中字段的类型--->实体类中属性的类型
数据库表中记录--->对象

 

搭建MyBatis环境 

idea下载插件、配置maven

下载插件，下载完成后重启idea

配置maven

 

pom依赖

主要是mybatis和mysql驱动，另外，log4j是打印日志的，junit是做单元测试的

?
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
<?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.qzcsbj.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>1.0-SNAPSHOT</version>
 
 
    <dependencies>
        <!-- mybatis -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.6</version>
        </dependency>
 
        <!-- mysql驱动 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
 
        <!-- https://mvnrepository.com/artifact/log4j/log4j -->
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
 
        <!--单元测试-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
    </dependencies>
 
 
 
    <build>
        <resources>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.xml</include>
                </includes>
            </resource>
        </resources>
    </build>
</project>

 

resources下添加日志配置文件：log4j.properties

?
1
2
3
4
5
6
7
8
9
10
11
12
13
### 根logger 设置###
log4j.rootLogger=DEBUG, Console
 
### 输出信息到控制台 ###
log4j.appender.Console=org.apache.log4j.ConsoleAppender
log4j.appender.Console.layout=org.apache.log4j.PatternLayout
log4j.appender.Console.layout.ConversionPattern=%d [%t] %-5p [%c] - %m%n
 
log4j.logger.java.sql.ResultSet=INFO
log4j.logger.org.apache=INFO
log4j.logger.java.sql.Connection=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG

　　

resources下添加数据库连接配置文件：jdbc.properties

?
1
2
3
4
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://192.168.117.180:3306/gifts?useUnicode=true&characterEncoding=utf-8&useSSL=true
jdbc.username=root
jdbc.password=qzcsbj

 

resources下创建主配置文件（mybatis全局配置文件）：mybatis-config.xml

?
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
<?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>
 
    <!--引入外部的数据库配置文件-->
    <properties resource="jdbc.properties"></properties>
 
    <!--环境-->
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>
 
 
    <!--引入映射文件-->
    <mappers>
        <mapper resource="com\qzcsbj\mapper\UserMapper.xml"/>
    </mappers>
 
</configuration>

 

准备测试数据

建库

 

建表、造数据

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL COMMENT '用户名',
  `password` varchar(255) NOT NULL COMMENT '密码',
  `realname` varchar(255) NOT NULL COMMENT '真实名',
  `sex` tinyint(1) DEFAULT NULL COMMENT '性别',
  `birthday` date DEFAULT NULL COMMENT '生日',
  `phone` varchar(255) NOT NULL COMMENT '电话',
  `utype` tinyint(1) DEFAULT NULL COMMENT '用户类型',
  `addtime` datetime DEFAULT NULL COMMENT '添加时间',
  `adduser` varchar(255) DEFAULT NULL COMMENT '添加者',
  PRIMARY KEY (`id`),
  UNIQUE KEY `phone` (`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
insert into `user` values(1,'qzcsbj1','62f7f5673e94eca261b8fce7ae7863a4','qzcsbj1',1,'2018-09-06','13800000001',0,now(),'qzcsbj');
insert into `user` values(2,'qzcsbj2','62f7f5673e94eca261b8fce7ae7863a4','qzcsbj2',1,'2018-09-07','13800000002',0,now(),'qzcsbj');
insert into `user` values(3,'qzcsbj3','62f7f5673e94eca261b8fce7ae7863a4','qzcsbj3',1,'2018-09-08','13800000003',0,now(),'qzcsbj');
insert into `user` values(4,'qzcsbj4','62f7f5673e94eca261b8fce7ae7863a4','qzcsbj4',1,'2018-09-09','13800000004',0,now(),'qzcsbj');
insert into `user` values(5,'qzcsbj5','62f7f5673e94eca261b8fce7ae7863a4','qzcsbj5',1,'2018-09-10','13800000005',0,now(),'qzcsbj');

 

 idea连接数据库

 

 

 

 

示例：添加用户

User.java

?
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
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
package com.qzcsbj.bean;
 
import java.util.Date;
 
/**
 * @公众号 : 全栈测试笔记
 * @博客 : www.cnblogs.com/uncleyong
 * @微信 : ren168632201
 * @描述 : <>
 */
public class User {
    private Integer id;
    private String username;
    private String password;
    private String realname;
    private String sex;
    private Date birthday;
    private String phone;
    private String utype;
    private Date addtime;
    private String adduser;
 
 
    public User() {
    }
 
    public User(String username, String password, String realname, String sex, Date birthday, String phone, String utype, String adduser) {
        this.username = username;
        this.password = password;
        this.realname = realname;
        this.sex = sex;
        this.birthday = birthday;
        this.phone = phone;
        this.utype = utype;
        this.adduser = adduser;
    }
 
    public Integer getId() {
        return id;
    }
 
    public void setId(Integer id) {
        this.id = id;
    }
 
    public String getUsername() {
        return username;
    }
 
    public void setUsername(String username) {
        this.username = username;
    }
 
    public String getPassword() {
        return password;
    }
 
    public void setPassword(String password) {
        this.password = password;
    }
 
    public String getRealname() {
        return realname;
    }
 
    public void setRealname(String realname) {
        this.realname = realname;
    }
 
    public String getSex() {
        return sex;
    }
 
    public void setSex(String sex) {
        this.sex = sex;
    }
 
    public Date getBirthday() {
        return birthday;
    }
 
    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }
 
    public String getPhone() {
        return phone;
    }
 
    public void setPhone(String phone) {
        this.phone = phone;
    }
 
    public String getUtype() {
        return utype;
    }
 
    public void setUtype(String utype) {
        this.utype = utype;
    }
 
    public Date getAddtime() {
        return addtime;
    }
 
    public void setAddtime(Date addtime) {
        this.addtime = addtime;
    }
 
    public String getAdduser() {
        return adduser;
    }
 
    public void setAdduser(String adduser) {
        this.adduser = adduser;
    }
 
    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", realname='" + realname + '\'' +
                ", sex='" + sex + '\'' +
                ", birthday=" + birthday +
                ", phone='" + phone + '\'' +
                ", utype='" + utype + '\'' +
                ", addtime=" + addtime +
                ", adduser='" + adduser + '\'' +
                '}';
    }
}

 

封装工具类：MyBatisUtils.java

?
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
package com.qzcsbj.utils;
 
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.IOException;
import java.io.InputStream;
 
/**
 * @公众号 : 全栈测试笔记
 * @博客 : www.cnblogs.com/uncleyong
 * @微信 : ren168632201
 * @描述 : <>
 */
public class MyBatisUtils {
    public static SqlSession getSession(){
        SqlSession session=null;
        InputStream inputStream=null;
        try {
            inputStream = Resources.getResourceAsStream("mybatis-config.xml");
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            session = sqlSessionFactory.openSession(true);  // mybatis增、删、改要手动提交事务，true表示自动提交事务
            return session;
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            if (inputStream != null){
                try {
                    inputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return null;
    }
 
    public static void closeSession(SqlSession session){
        if(session != null){
            session.close();
        }
    }
}

　　

接口层mapper文件

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
package com.qzcsbj.mapper;
 
import com.qzcsbj.bean.User;
 
import java.util.List;
 
/**
 * @公众号 : 全栈测试笔记
 * @博客 : www.cnblogs.com/uncleyong
 * @微信 : ren168632201
 * @描述 : <>
 */
public interface UserMapper {
    public abstract int addUser(User user);
}

　　

映射文件

?
1
2
3
4
5
6
7
8
9
10
11
12
<?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.qzcsbj.mapper.UserMapper">
 
    <!--parameterType是参数类型，可以省略；新增、更新、删除，不需要写返回值，默认是int-->
    <insert id="addUser" parameterType="com.qzcsbj.bean.User">
        insert into user values(null,#{username},#{password},#{realname},#{sex},#{birthday},#{phone},#{utype},now(),#{adduser})
    </insert>
 
</mapper>

 

测试类

?
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
package com.qzcsbj;
 
import com.qzcsbj.bean.User;
import com.qzcsbj.mapper.UserMapper;
import com.qzcsbj.utils.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
 
import java.util.Date;
import java.util.List;
 
/**
 * @公众号 : 全栈测试笔记
 * @博客 : www.cnblogs.com/uncleyong
 * @微信 : ren168632201
 * @描述 : <>
 */
public class testMybatis {
 
    SqlSession session = null;
    UserMapper userMapper = null;
 
    @Before
    public void init(){
        System.out.println("初始化。。。");
        session = MyBatisUtils.getSession();
        userMapper = session.getMapper(UserMapper.class);
    }
 
    @After
    public void destory(){
        System.out.println("关闭session");
        MyBatisUtils.closeSession(session);
    }
 
    @Test
    public void testAddUser(){
        User user = new User("qzcsbj9","123456","qzcsbj9","1",new Date(),"13800000009","1","qzcsbj");
        int n = userMapper.addUser(user);
        System.out.println(n>0?"新增成功":"新增失败");
    }
}

 

结果

 

 

 

优化配置文件：mybatis-config.xml

typeAliases

自定义别名：

?
1
2
3
4
5
6
7
<typeAliases>
    <!--方法一：别名可以随意定义，比如：XXX，但是最好和类名一样，见名知意-->
    <!--<typeAlias type="com.qzcsbj.bean.User" alias="User"/>-->
 
    <!--方法二（推荐）：直接给所有的实体类取别名。默认的实体类的别名就是类名，不区分小大写-->
    <package name="com.qzcsbj.bean"/>
</typeAliases>

 

此时，映射文件中就可以直接写类名

?
1
2
3
4
5
6
7
8
9
10
11
<?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.qzcsbj.mapper.UserMapper">
 
    <insert id="addUser" parameterType="User">
        insert into user values(null,#{username},#{password},#{realname},#{sex},#{birthday},#{phone},#{utype},now(),#{adduser})
    </insert>
 
</mapper>

 

mappers

mapper映射文件的引入有3种方式：

?
1
2
3
4
5
6
7
8
9
10
<mappers>
    <!--方法一-->
    <!--<mapper resource="com\qzcsbj\mapper\UserMapper.xml"/>-->
 
    <!--方法二-->
    <!--<mapper class="com.qzcsbj.mapper.UserMapper"/>-->
 
    <!--方法三（推荐）：写接口的包名，包下所有mapper接口会自动映射-->
    <package name="com.qzcsbj.mapper"/>
</mappers>

 

示例：根据id查询、更新、删除

mapper接口

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
package com.qzcsbj.mapper;
 
import com.qzcsbj.bean.User;
 
import java.util.List;
 
/**
 * @公众号 : 全栈测试笔记
 * @博客 : www.cnblogs.com/uncleyong
 * @微信 : ren168632201
 * @描述 : <>
 */
public interface UserMapper {
    public abstract int addUser(User user);
    public User getUserById(int id);
    public int updateUserById(User user);
    public int deleteUserById(int id);
}

　　

映射文件

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
<?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.qzcsbj.mapper.UserMapper">
 
    <insert id="addUser" parameterType="User">
        insert into user values(null,#{username},#{password},#{realname},#{sex},#{birthday},#{phone},#{utype},now(),#{adduser})
    </insert>
 
    <select id="getUserById" resultType="User" parameterType="int">
        select * from user where id=#{id}
    </select>
 
    <update id="updateUserById" parameterType="User">
        update user set username=#{username},password=#{password},realname=#{realname},sex=#{sex},birthday=#{birthday},phone=#{phone},utype=#{utype} where id=#{id}
    </update>
 
    <delete id="deleteUserById">
        delete from user where id=#{id}
    </delete>
 
</mapper>

 

测试类

?
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
package com.qzcsbj;
 
import com.qzcsbj.bean.User;
import com.qzcsbj.mapper.UserMapper;
import com.qzcsbj.utils.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
 
import java.util.Date;
import java.util.List;
 
/**
 * @公众号 : 全栈测试笔记
 * @博客 : www.cnblogs.com/uncleyong
 * @微信 : ren168632201
 * @描述 : <>
 */
public class testMybatis {
 
    SqlSession session = null;
    UserMapper userMapper = null;
 
    @Before
    public void init(){
        System.out.println("初始化。。。");
        session = MyBatisUtils.getSession();
        userMapper = session.getMapper(UserMapper.class);
    }
 
    @After
    public void destory(){
        System.out.println("关闭session");
        MyBatisUtils.closeSession(session);
    }
 
    @Test
    public void testAddUser(){
        User user = new User("qzcsbj9","123456","qzcsbj9","1",new Date(),"13800000009","1","qzcsbj");
        int n = userMapper.addUser(user);
        System.out.println(n>0?"新增成功":"新增失败");
    }
 
    @Test
    public void testGetUserById(){
        User user = userMapper.getUserById(1);
        System.out.println(user);
    }
 
 
    @Test
    public void testUpdateUserById(){
        // 先查询出来，再更新
        User user = userMapper.getUserById(1);
        user.setUsername("qzcsbj1-1");
        user.setRealname("qzcsbj1-1");
        user.setAdduser("hello");
        int n = userMapper.updateUserById(user);
        System.out.println(n>0?"更新成功":"更新失败");
    }
 
    @Test
    public void testDeleteUserById(){
        int n = userMapper.deleteUserById(11);
        System.out.println(n>0?"删除成功":"删除失败");
    }
}

 

结果

根据id查询

 

根据id更新

 

 

根据id删除

 

模糊查询

mapper接口

?
1
public abstract List<User> findUserByUsername(String username);

 

映射文件

?
1
2
3
4
<!--模糊查询-->
<select id="findUserByUsername" resultType="User">
    select * from user where username like #{username}
</select>

 

测试类

?
1
2
3
4
5
6
7
8
9
// 模糊查询
@Test
public void testLike(){
    String keyword = "qzcs";  // 用户输入的字符串
    List<User> users = userMapper.findUserByUsername("%" + keyword + "%");
    for (User user : users) {
        System.out.println(user);
    }
}

 

 

获取新增成功后的主键

表中主键需要是自增的

 

mapper接口

?
1
public abstract int addUser(User user);

 

映射文件

?
1
2
3
4
5
6
7
<!--
    useGeneratedKeys="true"，开启自动增长的映射
    keyProperty="id"，表中自增字段id所对应对象中的属性名
-->
<insert id="addUser" parameterType="User" useGeneratedKeys="true" keyProperty="id">
    insert into user values(null,#{username},#{password},#{realname},#{sex},#{birthday},#{phone},#{utype},now(),#{adduser})
</insert>

　　

测试类

?
1
2
3
4
5
6
@Test
public void testAddUser(){
    User user = new User("qzcsbj9","123456","qzcsbj9","1",new Date(),"13800000009","1","qzcsbj");
    int n = userMapper.addUser(user);
    System.out.println(n>0?"新增成功，id = "+user.getId():"新增失败");
}

 

结果

 

 

parameterType的入参：简单类型、pojo类型、HashMap类型

简单类型

参考：根据id查询用户 

pojo类型

参考：新增用户

传入的参数是Map类型（HashMap）

mapper接口

?
1
2
// 按时间区间查询
public List<User> findUserByBirthday(Map<String,Object> map);

　　

映射文件

?
1
2
3
<select id="findUserByBirthday" resultType="User">
    select * from user where birthday between #{xxx} and #{yyy}
</select>

　　

测试类

注意：map的key要和sql中的占位符保持名字一致

?
1
2
3
4
5
6
7
8
9
10
@Test
public void testFindUserByBirthday(){
    Map<String, Object> map = new HashMap<String, Object>();
    map.put("xxx","2018-09-07");
    map.put("yyy","2018-09-09");
    List<User> users = userMapper.findUserByBirthday(map);
    for (User user : users) {
        System.out.println(user);
    }
}

　　

结果

 

数据库表字段名和实体类属性名不一样

数据库中表字段名username改为uname

 

mapper接口

?
1
public abstract List<User> findAll();

　　

映射文件

?
1
2
3
<select id="findAll" resultType="User">
   select * from user
</select>

　　

测试类

?
1
2
3
4
5
6
7
@Test
public void testFindAll(){
    List<User> all = userMapper.findAll();
    for (User user : all) {
        System.out.println(user);
    }
}

　　

结果

username是null

 

解决方法一：

在sql中给uname取别名为username

?
1
2
3
<select id="findAll" resultType="User">
    select id,uname as "username",password,realname,sex,birthday,phone,utype,addtime,adduser from user
</select>

 

解决方法二：

通过resultMap来映射字段名和实体类属性名的一一对应关系

?
1
2
3
4
5
6
7
8
9
10
<select id="findAll" resultMap="UserMap">
   select * from user
</select>
 
<resultMap id="UserMap" type="User">
    <!--主键字段用id标签-->
    <!--<id column="id" property="id"/>-->
    <!--普通字段用result标签-->
    <result column="uname" property="username"/>
</resultMap>

　

结果　

 

【bak1】

【bak2】

 

原文会持续更新，原文地址：https://www.cnblogs.com/uncleyong/p/17004699.html

 

posted @   全栈测试笔记  阅读(442)  评论(0)  编辑  收藏  举报
相关博文：
·  MyBatis简易教程（02）：mybatis动态sql
·  MyBatis简易教程（06）：MyBatisPlus的用法
·  MyBatis
·  MyBatis
·  MyBatis笔记
阅读排行：
· TypeScript + Deepseek 打造卜卦网站：技术与玄学的结合
· 阿里巴巴 QwQ-32B真的超越了 DeepSeek R-1吗？
· 【译】Visual Studio 中新的强大生产力特性
· 【设计模式】告别冗长if-else语句：使用策略模式优化代码结构
· 10年+ .NET Coder 心语 ── 封装的思维：从隐藏、稳定开始理解其本质意义
浏览器标题切换
浏览器标题切换end
点击右上角即可分享
微信分享提示
SQL AI 助手