MyBatis01

港控/mmm° 2022-05-29 02:27 188阅读 0赞

# MyBatis01 #

## 1. JDBC编程问题总结 ##

### 1.1 数据准备 ###

/*
    Navicat MySQL Data Transfer
    
    Source Server         : localhost_3306
    Source Server Version : 50521
    Source Host           : localhost:3306
    Source Database       : mybatis
    
    Target Server Type    : MYSQL
    Target Server Version : 50521
    File Encoding         : 65001
    
    Date: 2015-04-09 16:03:53
    */
    
    SET FOREIGN_KEY_CHECKS=0;
    
    -- ----------------------------
    -- Table structure for `orders`
    -- ----------------------------
    DROP TABLE IF EXISTS `orders`;
    CREATE TABLE `orders` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `user_id` int(11) NOT NULL COMMENT '下单用户id',
      `number` varchar(32) NOT NULL COMMENT '订单号',
      `createtime` datetime NOT NULL COMMENT '创建订单时间',
      `note` varchar(100) DEFAULT NULL COMMENT '备注',
      PRIMARY KEY (`id`),
      KEY `FK_orders_1` (`user_id`),
      CONSTRAINT `FK_orders_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
    ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of orders
    -- ----------------------------
    INSERT INTO `orders` VALUES ('3', '1', '1000010', '2015-02-04 13:22:35', null);
    INSERT INTO `orders` VALUES ('4', '1', '1000011', '2015-02-03 13:22:41', null);
    INSERT INTO `orders` VALUES ('5', '10', '1000012', '2015-02-12 16:13:23', null);
    
    -- ----------------------------
    -- Table structure for `user`
    -- ----------------------------
    DROP TABLE IF EXISTS `user`;
    CREATE TABLE `user` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `username` varchar(32) NOT NULL COMMENT '用户名称',
      `birthday` date DEFAULT NULL COMMENT '生日',
      `sex` char(1) DEFAULT NULL COMMENT '性别',
      `address` varchar(256) DEFAULT NULL COMMENT '地址',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of user
    -- ----------------------------
    INSERT INTO `user` VALUES ('1', '王五', null, '2', null);
    INSERT INTO `user` VALUES ('10', '张三', '2014-07-10', '1', '北京市');
    INSERT INTO `user` VALUES ('16', '张小明', null, '1', '河南郑州');
    INSERT INTO `user` VALUES ('22', '陈小明', null, '1', '河南郑州');
    INSERT INTO `user` VALUES ('24', '张三丰', null, '1', '河南郑州');
    INSERT INTO `user` VALUES ('25', '陈小明', null, '1', '河南郑州');
    INSERT INTO `user` VALUES ('26', '王五', null, null, null);

![示例][CfyInfo]

### 1.2 创建工程 ###

说明:

*  简单Java项目即可
 *  可以使用Maven或者Jar的方式添加驱动

代码:

package com.lanou3g;
    
    
    import java.sql.*;
    
    public class Main {
        
        public static void main(String[] args) {
            Connection connection = null;
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;
    
            try {
                // 加载数据库驱动
                Class.forName("com.mysql.cj.jdbc.Driver");
    
                // 通过驱动管理类获取数据库链接
                connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mybatis?serverTimezone=Asia/Shanghai&characterEncoding=utf-8", "root", "123456");
                // 定义sql语句 ?表示占位符
                String sql = "select * from user where username = ?";
                // 获取预处理statement
                preparedStatement = connection.prepareStatement(sql);
                // 设置参数，第一个参数为sql语句中参数的序号（从1开始），第二个参数为设置的参数值
                preparedStatement.setString(1, "王五");
                // 向数据库发出sql执行查询，查询出结果集
                resultSet = preparedStatement.executeQuery();
                // 遍历查询结果集
                while (resultSet.next()) {
                    System.out.println(resultSet.getString("id") + "  " + resultSet.getString("username"));
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                // 释放资源
                if (resultSet != null) {
                    try {
                        resultSet.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                if (preparedStatement != null) {
                    try {
                        preparedStatement.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                if (connection != null) {
                    try {
                        connection.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }
    
        }
    }

运行结果:

![运行结果][CfyInfo 1]

### 1.3 JDBC问题总结 ###

1.  数据库连接创建、释放频繁造成系统资源浪费，从而影响系统性能。如果使用数据库连接池可解决此问题。
2.  Sql语句在代码中硬编码，造成代码不易维护，实际应用中sql变化的可能较大，sql变动需要改变java代码
3.  使用preparedStatement向占有位符号传参数存在硬编码，因为sql语句的where条件不一定，可能多也可能少，修改sql还要修改代码，系统不易维护
4.  对结果集解析存在硬编码（查询列名），sql变化导致解析代码变化，系统不易维护，如果能将数据库记录封装成pojo对象解析比较方便

## 2. MyBatis架构 ##

![MyBatis架构图][MyBatis]

### 2.1 说明 ###

*  mybatis配置  
    
    
     *  SqlMapConfig.xml: 此文件作为mybatis的全局配置文件，配置了mybatis的运行环境等信息
     *  mapper.xml: 即sql映射文件，文件中配置了操作数据库的sql语句。此文件需要在SqlMapConfig.xml中加载,通常一个表对应一个mapper.xml
 *  通过mybatis环境等配置信息构造SqlSessionFactory即会话工厂
 *  由会话工厂创建sqlSession即会话，操作数据库需要通过sqlSession进行
 *  mybatis底层自定义了Executor执行器接口操作数据库，Executor接口有两个实现，一个是基本执行器、一个是缓存执行器
 *  Mapped Statement也是mybatis一个底层封装对象，它包装了mybatis配置信息及sql映射信息等。mapper.xml文件中一个sql对应一个Mapped Statement对象，sql的id即是Mapped statement的id
 *  Mapped Statement对sql执行输入参数进行定义，包括HashMap、基本类型、pojo，Executor通过Mapped Statement在执行sql前将输入的java对象映射至sql中，输入参数映射就是jdbc编程中对preparedStatement设置参数
 *  Mapped Statement对sql执行输出结果进行定义，包括HashMap、基本类型、pojo，Executor通过Mapped Statement在执行sql后将输出结果映射至java对象中，输出结果映射过程相当于jdbc编程中对结果的解析处理过程

## 3. Mybatis入门程序 ##

根据用户ID查询一个用户

### 3.1 mybatis下载 ###

mybatis的代码由github托管,下载地址:[https://github.com/mybatis/mybatis-3/releases][https_github.com_mybatis_mybatis-3_releases]

![下载][CfyInfo 2]

### 3.2 添加依赖 ###

![依赖][CfyInfo 3]

### 3.3 POJO ###

User:

package com.lanou3g.pojo;
    
    import java.io.Serializable;
    import java.util.Date;
    
    public class User implements Serializable {
        
        private static final long serialVersionUID = 1L;
        private Integer id;
        private String username;// 用户姓名
        private String sex;// 性别
        private Date birthday;// 生日
        private String address;// 地址
    
    
        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 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;
        }
        @Override
        public String toString() {
            return "User [id=" + id + ", username=" + username + ", sex=" + sex
                    + ", birthday=" + birthday + ", address=" + address + "]";
        }
    
    }

Orders

package com.lanou3g.pojo;
    
    import java.io.Serializable;
    import java.util.Date;
    
    public class Orders  implements Serializable{
        
    
        private static final long serialVersionUID = 1L;
    
        private Integer id;
    
        private Integer userId;
    
        private String number;
    
        private Date createTime;
    
        private String note;
    
        public Integer getId() {
            return id;
        }
    
        public void setId(Integer id) {
            this.id = id;
        }
    
        public Integer getUserId() {
            return userId;
        }
    
        public void setUserId(Integer userId) {
            this.userId = userId;
        }
    
        public String getNumber() {
            return number;
        }
    
        public void setNumber(String number) {
            this.number = number == null ? null : number.trim();
        }
    
        public Date getCreateTime() {
            return createTime;
        }
    
        public void setCreateTime(Date createTime) {
            this.createTime = createTime;
        }
    
        public String getNote() {
            return note;
        }
    
        public void setNote(String note) {
            this.note = note == null ? null : note.trim();
        }
    }

### 3.4 配置文件 ###

#### mybatis核心配置文件 ####

在src下创建sqlMapConfig.xml

<?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>
    
        <environments default="development">
            <environment id="development">
                
                <transactionManager type="JDBC"/>
                
                <dataSource type="POOLED">
                    <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                    <property name="url" value="jdbc:mysql://localhost:3306/mybatis?serverTimezone=Asia/Shanghai"/>
                    <property name="username" value="root"/>
                    <property name="password" value="123456"/>
                </dataSource>
            </environment>
        </environments>
        
        <mappers>
            <mapper resource="com/lanou3g/sqlmap/User.xml"/>
        </mappers>
    </configuration>

#### log ####

在src下创建log4j.properties

log4j.rootLogger=DEBUG, stdout
    
    log4j.appender.stdout=org.apache.log4j.ConsoleAppender
    log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
    log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

说明:  
\- 不适用log4j 不会影响mybatis的使用

#### mapper映射文件 ####

在sqlmap包下创建User的映射文件 User.xml

<?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="user">
    
        
        <select id="findUserById" parameterType="Integer" resultType="com.lanou3g.pojo.User">
            SELECT * FROM USER WHERE id = #{v}
        </select>
    </mapper>

说明:  
\- 在mybatis中使用 `#{v}` 来作为占位符(`?`)拼接sql语句  
\- id: statement的id或者叫做 sql 的id  
\- parameterType: 声明输入参数的类型  
\- resultType: 声明输出结果的类型,应该填写pojo的全路径

### 3.5 测试运行 ###

使用Junit来进行测试,编写测试程序的步骤:  
1. 加载sqlMapConfig.xml配置文件  
2. 通过SqlSessionFactoryBuilder创建SqlSessionFactory对象  
3. 创建SqlSession对象  
4. 使用SqlSession对象执行在mapper中编写的sql语句获得返回值  
5. 打印结果  
6. 释放资源

package com.lanou3g.junit;
    
    import com.lanou3g.pojo.User;
    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 org.junit.Test;
    import java.io.InputStream;
    
    public class MybatisFirstTest {
    
        @Test
        public void  testMybatis() throws Exception{
            String resource = "sqlMapConfig.xml";
            InputStream in = Resources.getResourceAsStream(resource);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
            SqlSession sqlSession = sqlSessionFactory.openSession();
            User user = sqlSession.selectOne("user.findUserById", 10);
            System.out.println(user);
            in.close();
            sqlSession.close();
        }
    }

![CfyInfo 4][]

### 3.5 说明 ###

sqlMapConfig.xml,User.xml和MybatisFirstTest.java之间的对应关系如图所示:  
![CfyInfo 5][]

*  在sqlMapConfig.xml的mappers标签中将所有的map.xml进行注册,需要制定xml文件的位置
 *  在Java中使用Resource.getResourceAsXXX来加载配置sqlMapConfig.xml,可以有很多的返回值,这个返回值需要和SqlSessionFactoryBuilder.build()方法中的参数对应上
 *  在查找一条数据时使用 `selectOne`方法,第一个参数是mapper中sql语句的id,这个id是mapper的namespace.id

### 3.6 增删改查入门 ###

业务需求:  
\- 根据用户名模糊查询用户列表  
\- 添加用户  
\- 更新用户  
\- 删除用户

#### 1. 模糊查询 ####

映射

<select id="findUserByUserName" parameterType="String" resultType="com.lanou3g.pojo.User">
        SELECT * FROM USER WHERE username LIKE '%${value}%'
    </select>

测试代码:

@Test
    public void testFindUserByName() throws Exception {
        String resource = "sqlMapConfig.xml";
        InputStream in = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder()
                .build(in);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        List<User> users =
                sqlSession.selectList("user.findUserByUserName", "五");
        for (User user : users) {
            System.out.println(user);
        }
        in.close();
        sqlSession.close();
    }

效果:  
![CfyInfo 6][]

##### 说明 #####

###### `#{}`和`${}` ######

`#{}`  
\- `#{}`表示一个占位符,通过`#{}`可以实现 perparedStatement 向占位符中设置值, **自动进行java类型和jdbc类型转换**  
\- `#{}`可以有效**防止sql注入**  
\- `#{}`可以接受简单类型值或pojo属性值  
\- 如果parameterType传输单个简单类型值,`#{}`括号中可以是value或其他名称

`${}`  
\- `${}`表示拼接sql字符串,通过`${}`可以将parameterType传入的内容拼接在sql中且不进行jdbc类型转换  
\- `${}`可以接收简单类型值或pojo属性值  
\- `${}`不能防止sql注入  
\- 如果parameterType传输单个简单类型值，`${}`括号中只能是value

mapper还可以写成:

<select id="findUserByUserName" parameterType="String" resultType="com.lanou3g.pojo.User">
        SELECT * FROM USER WHERE username LIKE "%"#{value}"%"
    </select>

运行结果:  
![CfyInfo 7][]

###### parameterType和resultType ######

*  parameterType：指定输入参数类型，mybatis通过ognl从输入对象中获取参数值拼接在sql中
 *  resultType：指定输出结果类型，mybatis将sql查询结果的一行记录数据映射为resultType指定类型的对象。如果有多条数据，则分别进行映射，并把对象放到容器List中

###### selectOne和selectList ######

*  selectOne: 查询一条记录,如果使用selectOne查询多条记录则抛出异常  
    ![CfyInfo 8][]
 *  selectList: 可以查询一条或多条记录

#### 2. 添加用户 ####

映射文件:

<insert id="saveUser" parameterType="com.lanou3g.pojo.User">
        INSERT INTO `user`
        (username,birthday,sex,address) VALUES
        (#{username},#{birthday},#{sex},#{address})
    </insert>

java代码:

@Test
    public void testSaveUser() throws Exception {
        String resource = "sqlMapConfig.xml";
        InputStream in = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder()
                .build(in);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        User user = new User();
        user.setUsername("张飞");
        user.setSex("1");
        user.setBirthday(new Date());
        user.setAddress("蜀国");
        sqlSession.insert("user.saveUser", user);
        System.out.println(user);
        in.close();
        sqlSession.close();
    }

运行效果  
![CfyInfo 9][]

存在问题:  
当保存完成后User的id并没有跟数据库中的值保持一致  
![CfyInfo 10][]

##### mysql自增主键返回 #####

在mysql中有:

SELECT LAST_INSERT_ID()

可以返回上一次增加的主键值,修改配置文件:

<insert id="saveUser" parameterType="com.lanou3g.pojo.User">
        <selectKey keyColumn="id" keyProperty="id" order="AFTER"
                   resultType="int">
            SELECT LAST_INSERT_ID()
        </selectKey>
        INSERT INTO `user`
        (username,birthday,sex,address) VALUES
        (#{username},#{birthday},#{sex},#{address})
    </insert>

说明:  
\- selectKey: 标签实现主键返回  
\- keyColumn: 主键对应的表中的哪一列  
\- keyProperty: 主键对应的pojo中的哪一个属性  
\- order: 设置在执行insert语句前执行查询id的sql,还是在执行insert语句之后执行查询id的sql  
\- resultType: 设置返回的id的类型

运行结果  
![CfyInfo 11][]

#### 3. 修改用户 ####

映射文件

<update id="updateUserById" parameterType="com.lanou3g.pojo.User">
        UPDATE `user` SET
        username = #{username} WHERE id = #{id}
    </update>

测试代码

@Test
    public void testUpdateUser() throws Exception {
        String resource = "sqlMapConfig.xml";
        InputStream in = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder()
                .build(in);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        User user = new User();
        user.setId(26); // 设置id
        user.setUsername("关羽");
        user.setSex("1");
        user.setBirthday(new Date());
        user.setAddress("蜀国");
        sqlSession.update("user.updateUserById", user);
        System.out.println(user);
        sqlSession.commit();
        in.close();
        sqlSession.close();
    }

运行结果:  
![CfyInfo 12][]

![CfyInfo 12][]

#### 4. 删除用户 ####

配置文件:

<delete id="deleteById" parameterType="Integer">
        DELETE FROM USER WHERE id = #{VALUE}
    </delete>

测试代码:

@Test
    public void testDelById() throws Exception{
        String resource = "sqlMapConfig.xml";
        InputStream in = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder()
                .build(in);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        sqlSession.delete("user.deleteById", 26);
        sqlSession.commit();
        in.close();
        sqlSession.close();
    }

运行效果:  
![CfyInfo 13][]

![CfyInfo 14][]

[CfyInfo]: http://47.93.60.69:88/img/pics/63431886483A413CB8756BD39C7499F5.png?x-oss-process=style/CfyInfo
[CfyInfo 1]: http://47.93.60.69:88/img/pics/E85EA2BAEB9740A7949CBDF1063CE900.png?x-oss-process=style/CfyInfo
[MyBatis]: http://47.93.60.69:88/img/pics/016C3914DC5048C1BCFFCFAA18251531.png?x-oss-process=style/CfyInfo
[https_github.com_mybatis_mybatis-3_releases]: https://github.com/mybatis/mybatis-3/releases
[CfyInfo 2]: http://47.93.60.69:88/img/pics/17DBDF28DDF341FAB8231FF613239E96.png?x-oss-process=style/CfyInfo
[CfyInfo 3]: http://47.93.60.69:88/img/pics/AFA5CB48958846BA9976EBAC7D16B505.png?x-oss-process=style/CfyInfo
[CfyInfo 4]: http://47.93.60.69:88/img/pics/6F72D34AC122412787F78EA769E5938C.png?x-oss-process=style/CfyInfo
[CfyInfo 5]: http://47.93.60.69:88/img/pics/0FDF7B6024CF4903865831E7E2A8C727.png?x-oss-process=style/CfyInfo
[CfyInfo 6]: http://47.93.60.69:88/img/pics/77D59B1B647C40CF9513C5387B832246.png?x-oss-process=style/CfyInfo
[CfyInfo 7]: http://47.93.60.69:88/img/pics/BCAA3DE139324DEC85B64D3BDCF69EFB.png?x-oss-process=style/CfyInfo
[CfyInfo 8]: http://47.93.60.69:88/img/pics/F5CAB78110344FBD8C538F06D0C7AE80.png?x-oss-process=style/CfyInfo
[CfyInfo 9]: http://47.93.60.69:88/img/pics/D1B2AD5B1F8E425FAD7E414F9E5E1188.png?x-oss-process=style/CfyInfo
[CfyInfo 10]: http://47.93.60.69:88/img/pics/BE138B573AA94406B99552630C465D48.png?x-oss-process=style/CfyInfo
[CfyInfo 11]: http://47.93.60.69:88/img/pics/521541F7549A41089EE5B60E6DA7D6F3.png?x-oss-process=style/CfyInfo
[CfyInfo 12]: http://47.93.60.69:88/img/pics/78F18E1904314D84AD540820EB6AF4B1.png?x-oss-process=style/CfyInfo
[CfyInfo 13]: http://47.93.60.69:88/img/pics/D6B32C807ABA4186B022E6853DB1ED89.png?x-oss-process=style/CfyInfo
[CfyInfo 14]: http://47.93.60.69:88/img/pics/E0435BC35AAB41078B62DC553C097ED7.png?x-oss-process=style/CfyInfo