11.2 注解的使用示例1 select insert update和delete操作

发表于 2019-06-13 更新于 2020-06-05 分类于 4 Spring+Mybatis企业应用实战(第2版) ，第11章 MyBatis的注解配置， 11.2 基于注解的动态SQL Waline：本文字数： 13k 阅读时长 ≈ 12 分钟

11.2 注解的使用

@select、@insert、@update和@delete可以完成常见的CRUD(增删改査)SQL语句映射。

示例:测试 select,insert,update和delete操作

项目搭建

展开/折叠

G:\workspace_web2\MyADMLTest
├─src\
│ ├─db.properties
│ ├─domain\
│ │ └─User.java
│ ├─fractory\
│ │ └─SqlSessionFactoryTools.java
│ ├─log4j.xml
│ ├─mapper\
│ │ └─UserMapper.java
│ ├─mybatis-config.xml
│ ├─tb_user.sql
│ └─test\
│   ├─DeleteTest.java
│   ├─InsertUserTest.java
│   ├─SelectAllUser.java
│   ├─SelectUserByIdTest.java
│   └─UpdateUserTest.java
└─WebContent\
  ├─META-INF\
  │ └─MANIFEST.MF
  └─WEB-INF\
    └─lib\
      ├─ant-1.9.6.jar
      ├─ant-launcher-1.9.6.jar
      ├─asm-5.2.jar
      ├─cglib-3.2.5.jar
      ├─commons-logging-1.2.jar
      ├─javassist-3.22.0-CR2.jar
      ├─log4j-1.2.17.jar
      ├─log4j-api-2.3.jar
      ├─log4j-core-2.3.jar
      ├─mybatis-3.4.5.jar
      ├─mysql-connector-java-5.1.44-bin.jar
      ├─ognl-3.1.15.jar
      ├─slf4j-api-1.7.25.jar
      └─slf4j-log4j12-1.7.25.jar

数据库表

/MyADMLTest/src/tb_user.sql

# 创建数据库表
DROP TABLE IF EXISTS `tb_user`;
CREATE TABLE `tb_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(18) DEFAULT NULL,
  `sex` char(2) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
# 插入数据
INSERT INTO `tb_user` VALUES ('1', '小明', '男', '21');
INSERT INTO `tb_user` VALUES ('2', '小王', '男', '22');
INSERT INTO `tb_user` VALUES ('3', '小丽', '女', '18');
INSERT INTO `tb_user` VALUES ('4', '小芳', '女', '18');
INSERT INTO `tb_user` VALUES ('5', '小王', '男', '22');

持久化对象

/MyADMLTest/src/domain/User.java

package domain;
public class User {
    private Integer id;
    private String name;
    private String sex;
    private Integer age;
    // 此处省略无参构造器User()
    // 此处省略getter和setter方法,请自己补上
    // 此处省略toString()方法.
}

mapper接口

/MyADMLTest/src/mapper/UserMapper.java

package mapper;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import domain.User;
public interface UserMapper {
    @Select("select * from tb_user where id=#{id}")
    @Results({
        @Result(column = "id", property = "id", id = true),
        @Result(column = "name", property = "name"),
        @Result(column = "sex", property = "sex"),
        @Result(column = "age", property = "age") 
    })
    User selectUserById(Integer id);
    //如果数据表的列名和持久化对象的属性名完全一致,则可以省略@Results注解,Mybatis可以自动映射.
    @Select("select * from tb_user where id=#{id}")
    User selectUserById2(Integer id);
}

mybatis-cofig.xml

/MyADMLTest/src/mybatis-config.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">
<!-- 该配置文件包含对 MyBatis 系统的核心设置 -->
<configuration>
    <!-- 引入数据库信息配置文件 -->
    <properties resource="db.properties"/>
    <!-- 设置日志实现 -->
    <settings>
        <setting
            name="logImpl"
            value="log4j"/>
    </settings>
    <environments default="mysql">
        <environment id="mysql">
            <transactionManager type="JDBC"/>
            <dataSource type="pooled">
                <property
                    name="driver"
                    value="${driver}"/>
                <property
                    name="url"
                    value="${url}"/>
                <property
                    name="username"
                    value="${username}"/>
                <property
                    name="password"
                    value="${password}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <!-- mapper如果引入的是接口，则使用class属性,class属性的值设为接口的完全限定类名 -->
        <!-- mapper如果引入的是XML文件，则使用resource属性,resource属性的值设为xml相对于src的路径 -->
        <mapper class="mapper.UserMapper"/>
    </mappers>
</configuration>

数据库信息配置文件db.properties

/MyADMLTest/src/db.properties

# 保存为db.properties文件,然后在mybatis-config.xml中通过下面标签引入:
# <properties resource="db.properties"/>
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis
username=root
password=root

log4j.xml

/MyADMLTest/src/log4j.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE log4j:configuration 
    PUBLIC "-//LOG4J//DTD LOG4J//EN" 
    "https://logging.apache.org/log4j/1.2/apidocs/org/apache/log4j/xml/doc-files/log4j.dtd" >
<!-- 请在mybatis-config.xml中配置如下设置 -->
<!-- <settings> -->
<!-- <setting -->
<!-- name="logImpl" -->
<!-- value="log4j"/> -->
<!-- </settings> -->
<log4j:configuration>
    <appender
        name="STDOUT"
        class="org.apache.log4j.ConsoleAppender">
        <layout class="org.apache.log4j.PatternLayout">
            <param
                name="ConversionPattern"
                value="%5p [%t] %m%n"/>
        </layout>
    </appender>
    <logger name="domain.User">
        <level value="DEBUG"/>
    </logger>
    <root>
        <level value="ERROR"/>
        <appender-ref ref="STDOUT"/>
    </root>
</log4j:configuration>

工具类

package fractory;

import java.io.IOException;
import java.io.InputStream;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class SqlSessionFactoryTools {
    private static SqlSessionFactory sqlSessionFactory = null;
    static
    {
        try
        {
            InputStream mybatisConfigXML = Resources.getResourceAsStream("mybatis-config.xml");
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(mybatisConfigXML);
        } catch (IOException e)
        {
            e.printStackTrace();
        }
    }
    public static SqlSession getSqlSession()
    {
        return sqlSessionFactory.openSession();
    }
}

测试select功能

SelectUserByIdTest.java

/MyADMLTest/src/test/SelectUserByIdTest.java

package test;
import org.apache.ibatis.session.SqlSession;
import domain.User;
import fractory.SqlSessionFactoryTools;
import mapper.UserMapper;
public class SelectUserByIdTest {
    public static void main(String[] args)
    {
        SqlSession sqlSession = null;
        sqlSession = SqlSessionFactoryTools.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user = userMapper.selectUserById(1);
        System.out.println("-----------------------------------------");
        System.out.println(user);
        sqlSession.close();
    }
}

运行结果:

DEBUG [main] ==>  Preparing: select * from tb_user where id=? 
DEBUG [main] ==> Parameters: 1(Integer)
DEBUG [main] <==      Total: 1
-----------------------------------------
User [id=1, name=小明, sex=男, age=21]

调用selectUserById方法,会执行@Select注解中的SQL语句。

数据表列名和持久化对象属性名相同的情况

@Result注解用于列和属性之间的结果映射,如果列和属性名称相同,则可以省略@Result注解,MyBatis会自动进行映射。
我的持久化类的属性名和数据表的列名完全一致,则上面的查询方法可以写成如下形式:

1 2	@Select("select * from tb_user where id=#{id}") User selectUserById2(Integer id);

测试:

public static void main(String[] args) {
    SqlSession sqlSession = null;
    sqlSession = SqlSessionFactoryTools.getSqlSession();
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    // User user = userMapper.selectUserById(1);
    User user = userMapper.selectUserById2(1);
    System.out.println("-----------------------------------------");
    System.out.println(user);
    sqlSession.close();
}

运行结果:

DEBUG [main] ==>  Preparing: select * from tb_user where id=? 
DEBUG [main] ==> Parameters: 1(Integer)
DEBUG [main] <==      Total: 1
-----------------------------------------
User [id=1, name=小明, sex=男, age=21]

可以看到运行结果与上面的完全一致.

测试insert

mapper接口方法

在mapper接口中添加如下方法:

@Insert("insert into tb_user(name,sex,age) values(#{name},#{sex},#{age})")
// useGeneratedKeys = true,表示使用自动增长的主键
// keyProperty = "id",表示插入数据库表时生成的主键设置到User对象的id属性.
@Options(useGeneratedKeys = true, keyProperty = "id")
void insertUser(User user);

InsertUserTest.java

/MyADMLTest/src/test/InsertUserTest.java

package test;
import org.apache.ibatis.session.SqlSession;
import domain.User;
import fractory.SqlSessionFactoryTools;
import mapper.UserMapper;
public class InsertUserTest {
    public static void main(String[] args)
    {
        SqlSession sqlSession = null;
        // 1.加载mybatis-cofig.xml获取SqlSession实例
        sqlSession = SqlSessionFactoryTools.getSqlSession();
        // 2.获取mapper接口的代理对象.
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user = new User("小张", "男", 22);
        System.out.println("插入数据库之前:" + user);
        System.out.println("--------------------------------------------------");
        userMapper.insertUser(user);
        System.out.println("--------------------------------------------------");
        System.out.println("插入数据库之后:" + user);
        // 提交事务
        sqlSession.commit();
        sqlSession.close();
    }
}

运行结果:

插入数据库之前:User [id=null, name=小张, sex=男, age=22]
--------------------------------------------------
DEBUG [main] ==>  Preparing: insert into tb_user(name,sex,age) values(?,?,?) 
DEBUG [main] ==> Parameters: 小张(String), 男(String), 22(Integer)
DEBUG [main] <==    Updates: 1
--------------------------------------------------
插入数据库之后:User [id=6, name=小张, sex=男, age=22]

调用insertUser方法,会执行@insert注解中的SQL语句。需要注意的是,insertUser方法还使用了@Options注解,@Options注解的属性

useGeneratedKeys=true表示使用数据库自动增长的主键,该操作需要底层数据库的支持。
keyProperty="id"表示把插入数据时数据库生成的主键设置到user对象的id变量之中。

测试update

在UserMapper接口中添加如下方法:

1 2	@Update("update tb_user set name=#{name},sex=#{sex},age=#{age} where id=#{id}") void updateUser(User user);

UpdateUserTest.java

/MyADMLTest/src/test/UpdateUserTest.java

package test;

import org.apache.ibatis.session.SqlSession;
import domain.User;
import fractory.SqlSessionFactoryTools;
import mapper.UserMapper;

public class UpdateUserTest {
    public static void main(String[] args) {
        SqlSession sqlSession = null;
        try {
            // 1.加载mybatis-cofig.xml配置文件,并获取会话
            sqlSession = SqlSessionFactoryTools.getSqlSession();
            // 2.创建mapper接口的代理对象.
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            // 查询要更新的用户
            User user = userMapper.selectUserById(6);
            // 如果该用户存在
            if (user != null) {
                System.out.println("更新之前的用户信息:" + user);
                User user1 = new User(user.getId(), "老张头", "男", 88);
                // 更新用户信息
                userMapper.updateUser(user1);
                // 从数据库中查询更新后的信息
                user = userMapper.selectUserById(6);
                System.out.println("更新之后的用户信息:" + user);
                sqlSession.commit();
            }
        } catch (Exception e) {
            e.printStackTrace();
            sqlSession.rollback();
        } finally {
            if (sqlSession != null) {
                sqlSession.close();
            }
        }
    }
}

运行结果:

DEBUG [main] ==>  Preparing: select * from tb_user where id=? 
DEBUG [main] ==> Parameters: 6(Integer)
DEBUG [main] <==      Total: 1
更新之前的用户信息:User [id=6, name=小张, sex=男, age=22]
DEBUG [main] ==>  Preparing: update tb_user set name=?,sex=?,age=? where id=? 
DEBUG [main] ==> Parameters: 老张头(String), 男(String), 88(Integer), 6(Integer)
DEBUG [main] <==    Updates: 1
DEBUG [main] ==>  Preparing: select * from tb_user where id=? 
DEBUG [main] ==> Parameters: 6(Integer)
DEBUG [main] <==      Total: 1
更新之前的用户信息:User [id=6, name=老张头, sex=男, age=88]

测试delete

mapper接口方法

1 2	@Delete("delete from tb_user where id=#{id}") void deleteUserById(@Param("id") Integer id);

调用deleteUser方法,会执行@Delete注解中的SQL语句。deleteUser方法参数前面的@Param("id")注解表示给该注解后面的变量取一个参数名称,对应@Delete注解中的#{id}。如果没有使用Param注解,则参数将会以它们的顺序位置来和SQL语句中的表达式进行映射.

DeleteTest.java

/MyADMLTest/src/test/DeleteTest.java

package test;

import org.apache.ibatis.session.SqlSession;
import domain.User;
import fractory.SqlSessionFactoryTools;
import mapper.UserMapper;

public class DeleteTest {
    public static void main(String[] args) {
        SqlSession sqlSession = null;
        try {
            sqlSession = SqlSessionFactoryTools.getSqlSession();
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            // 在数据库中查找要删除的用户
            User user = userMapper.selectUserById(5);
            // 如果找到用户
            if (user != null) {
                System.out.println("即将删除记录:" + user);
                // 删除用户
                userMapper.deleteUserById(user.getId());
            }
            sqlSession.commit();
        } catch (Exception e) {
            sqlSession.rollback();
            e.printStackTrace();
        } finally {
            sqlSession.close();
        }
    }
}

运行效果:

DEBUG [main] ==>  Preparing: select * from tb_user where id=? 
DEBUG [main] ==> Parameters: 5(Integer)
DEBUG [main] <==      Total: 1
即将删除记录:User [id=5, name=小王, sex=男, age=22]
DEBUG [main] ==>  Preparing: delete from tb_user where id=? 
DEBUG [main] ==> Parameters: 5(Integer)
DEBUG [main] <==    Updates: 1

全部查询

mapper接口方法

1 2	@Select("select * from tb_user") List<User> seleteAllUser();

SelectAllUser.java

/MyADMLTest/src/test/SelectAllUser.java

package test;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import domain.User;
import fractory.SqlSessionFactoryTools;
import mapper.UserMapper;
public class SelectAllUser {
    public static void main(String[] args)
    {
        SqlSession sqlSession = null;
        sqlSession = SqlSessionFactoryTools.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<User> users = userMapper.seleteAllUser();
        users.forEach(user -> System.out.println("   " + user));
        sqlSession.close();
    }
}

运行效果:

DEBUG [main] ==>  Preparing: select * from tb_user 
DEBUG [main] ==> Parameters: 
DEBUG [main] <==      Total: 5
   User [id=1, name=小明, sex=男, age=21]
   User [id=2, name=小王, sex=男, age=22]
   User [id=3, name=小丽, sex=女, age=18]
   User [id=4, name=小芳, sex=女, age=18]
   User [id=6, name=老张头, sex=男, age=88]