【MyBatis】MyBtis入门程序

1. 目录结构

2. 数据库表的设计

/*
 Navicat Premium Data Transfer

 Source Server         : Mysql
 Source Server Type    : MySQL
 Source Server Version : 50726
 Source Host           : localhost:3306
 Source Schema         : mybatis

 Target Server Type    : MySQL
 Target Server Version : 50726
 File Encoding         : 65001

 Date: 03/11/2019 12:01:59
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `birthday` date NULL DEFAULT NULL,
  `sex` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 36 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

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

3. 配置文件

3.1 pom.xml

<dependencies>
    <dependency>
      <groupId>junitgroupId>
      <artifactId>junitartifactId>
      <version>4.11version>
    dependency>
    <dependency>
      <groupId>org.mybatisgroupId>
      <artifactId>mybatisartifactId>
      <version>3.4.6version>
    dependency>
    <dependency>
      <groupId>mysqlgroupId>
      <artifactId>mysql-connector-javaartifactId>
      <version>5.1.47version>
    dependency>
    <dependency>
      <groupId>log4jgroupId>
      <artifactId>log4jartifactId>
      <version>1.2.17version>
    dependency>
  dependencies>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

3.2 log4j.properties

# Global logging configuration
# 开发环境下，日志级别要设置成DEBUG或者ERROR
log4j.rootLogger=DEBUG, stdout
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
1
2
3
4
5
6
7

3.3 SqlMapConfig.xml

DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    
    <environments default="development">
        <environment id="development">
            
            <transactionManager type="JDBC" />
            
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver" />
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf-8" />
                <property name="username" value="root" />
                <property name="password" value="" />
            dataSource>
        environment>
    environments>
    
    <mappers>
        <mapper resource="sqlmap/User.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
23
24

4. User.java

import java.util.Date;

public class User {
    //用户po
    //属性名和数据库字段名对应
    private int id;
    private String username;// 用户姓名
    private String sex;// 性别
    private Date birthday;// 生日
    private String address;// 地址

    public int getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    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 getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }
}
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

5. User.xml

DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="test">
    
    
    <select id="findUserById" parameterType="int" resultType="cn.edu.wtu.po.User">
        select * from user where id=#{VALUE }
    select>




    
    <select id="findUserByName" parameterType="java.lang.String" resultType="cn.edu.wtu.po.User">
        select * from user WHERE username LIKE '%${value}%'
    select>



    
    <insert id="insertUser" parameterType="cn.edu.wtu.po.User">
        
        
        <selectKey keyProperty="id" order="AFTER" resultType="java.lang.Integer">
            SELECT LAST_INSERT_ID()
        selectKey>
        insert into user (username,birthday,sex,address) value(#{username},#{birthday},#{sex},#{address})
    insert>


    <delete id="deleteUser" parameterType="java.lang.Integer">
        delete from user where id=#{id}
    delete>

    <update id="updateUser" parameterType="cn.edu.wtu.po.User">
        UPDATE user set username=#{username},birthday=#{birthday},sex=#{sex},address=#{address} where id=#{id}
    update>
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
69
70
71
72

6. MybatisFirst.java

public class MybatisFirst {
    /**
     * 根据id查询用户信息，得到一条记录
     * @throws IOException
     */
    @Test
    public void findUserByIdTest() throws IOException {
        // mybatis配置文件
        String resource = "SqlMapConfig.xml";
        InputStream is = Resources.getResourceAsStream(resource);
        // 创建会话工厂
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        // 通过工厂得到SqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //通过SqlSession操作数据库
        //第一个参数：映射文件中的statement的id，等于namespace+"."+statement的id
        //第二个参数：指定和映射文件中所匹配的所有parameterType的类型
        //sqlSession.selectOne()的结果是映射文件中所匹配的resultType类型的对象
        User user = sqlSession.selectOne("test.findUserById",1);
        System.out.println(user);
        // 释放资源
        sqlSession.close();
    }

    @Test
    public void findUserByName() throws IOException {
        // mybatis配置文件
        String resource = "SqlMapConfig.xml";
        InputStream is = Resources.getResourceAsStream(resource);
        // 创建会话工厂
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        // 通过工厂得到SqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession();
        List<User> list= sqlSession.selectList("test.findUserByName","小明");
        System.out.println(list);
        sqlSession.close();
    }

    /*
        小结：
        selectOne和selectList：

        selectOne表示查询出一条记录进行映射。如果使用selectOne可以实现使用selectList也可以实现（list中只有一个对象）。
        selectList表示查询出一个列表（多条记录）进行映射。如果使用selectList查询多条记录，不能使用selectOne。
        如果使用selectOne报错：
        org.apache.ibatis.exceptions.TooManyResultsException: Expected one result (or null) to be returned by selectOne(), but found: 4

     */

    @Test
    public void insertUserTest() throws IOException {
        //mybatis配置文件
        String resource = "SqlMapConfig.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        //创建会话工厂
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //通过工厂得到SqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession();
        User user = new User();
        user.setUsername("宋江涛");
        user.setBirthday(new Date());
        user.setSex("男");
        user.setAddress("山西");
        //list中的user和映射文件User.xml中的resultType的类型一直
        sqlSession.insert("test.insertUser",user);
        //提交事务
        sqlSession.commit();
        //获取主键
        System.out.println(user.getId());
        sqlSession.close();
    }

    /**
     * 删除用户
     * @throws IOException
     */
    @Test
    public void deleteUserTest() throws IOException {
        //mybatis配置文件
        String resource = "SqlMapConfig.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        //创建会话工厂
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //通过工厂得到SqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession();

        //list中的user和映射文件User.xml中的resultType的类型一直
        sqlSession.delete("test.deleteUser",30);
        //提交事务
        sqlSession.commit();
        sqlSession.close();
    }

    /**
     * 更新用户
     * @throws IOException
     */
    @Test
    public void updateUserTest() throws IOException {
        //mybatis配置文件
        String resource = "SqlMapConfig.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        //创建会话工厂
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //通过工厂得到SqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession();
        User user = new User();
        user.setId(27);
        user.setUsername("宋江涛new2");
        user.setBirthday(new Date());
        user.setSex("男");
        user.setAddress("山西太原new");
        //list中的user和映射文件User.xml中的resultType的类型一直
        sqlSession.update("test.updateUser",user);
        //提交事务
        sqlSession.commit();
        sqlSession.close();
    }
}
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

7. 总结

• parameterType

在映射文件中通过parameterType指定输入参数的类型

• resultType

在映射文件中通过resultType指定输出结果的类型

• #{}和${}

#{}表示一个占位符号;

${}表示一个拼接符号，会引起sql注入，所以不建议使用

• selectOne和selectList

selectOne表示查询一条记录进行映射，使用selectList也可以使用，只不过只有一个对象

selectList表示查询出一个列表(参数记录)进行映射，不能够使用selectOne查，不然会报下面的错:

org.apache.ibatis.exceptions.TooManyResultsException: Expected one result (or null) to be returned by selectOne(), but found: 3
1