Mybatis的注解开发

1 前言

• 注解开发虽然不如XML开发那样，可以完全做到代码和配置文件分离，但是其开发灵活快速的特性，使得在企业开发中的地位日益显著，而Mybatis作为时下最流行的持久层框架，掌握Mybatis注解开发势在必行。

2 环境准备

• sql脚本：

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '用户名',
  `birthday` datetime(0) NULL DEFAULT NULL COMMENT '生日',
  `gender` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '性别',
  `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '地址',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

DROP TABLE IF EXISTS `account`;
CREATE TABLE `account`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NULL DEFAULT NULL COMMENT '外键',
  `money` double NULL DEFAULT NULL COMMENT '存款',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `fk_user_id`(`user_id`) USING BTREE,
  CONSTRAINT `fk_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

• 导入相关jar包的Maven坐标：

<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.4.1</version>
</dependency>
<dependency>
    <groupId>log4j</groupId>
    <artifactId>log4j</artifactId>
    <version>1.2.17</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.21</version>
</dependency>
<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.13</version>
    <scope>test</scope>
</dependency>

• 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">
<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://192.168.134.100:3306/test?useUnicode=true&amp;characterEncoding=UTF-8&amp;autoReconnect=true&amp;useSSL=false&amp;serverTimezone=GMT%2B8&amp;allowPublicKeyRetrieval=true"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <package name="com.sunxiaping.mapper"/>
    </mappers>
</configuration>

• log4j.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">
 
<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">
 
 <appender name="STDOUT" class="org.apache.log4j.ConsoleAppender">
   <param name="Encoding" value="UTF-8" />
   <layout class="org.apache.log4j.PatternLayout">
    <param name="ConversionPattern" value="%-5p %d{MM-dd HH:mm:ss,SSS} %m  (%F:%L) \n" />
   </layout>
 </appender>
 <logger name="java.sql">
   <level value="debug" />
 </logger>
 <logger name="org.apache.ibatis">
   <level value="info" />
 </logger>
 <root>
   <level value="debug" />
   <appender-ref ref="STDOUT" />
 </root>
</log4j:configuration>

3 Mybatis注解实现常用的CRUD

• User.java

package com.sunxiaping.domain;

import java.io.Serializable;
import java.util.Date;

public class User implements Serializable {

    private Integer id;

    private String username;

    private Date birthday;

    private String gender;

    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 Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", birthday=" + birthday +
                ", gender='" + gender + '\'' +
                ", address='" + address + '\'' +
                '}';
    }
}

• UserMapper.java

package com.sunxiaping.mapper;

import com.sunxiaping.domain.User;
import org.apache.ibatis.annotations.*;

import java.util.List;

public interface UserMapper {

    /**
     * 查询所有用户信息
     *
     * @return
     */
    @Select(" SELECT * FROM `user`")
    @Results({
            @Result(id = true, property = "id", column = "id"),
            @Result(property = "username", column = "username"),
            @Result(property = "birthday", column = "birthday"),
            @Result(property = "gender", column = "gender"),
            @Result(property = "address", column = "address"),
    })
    List<User> findAll();

    /**
     * 添加用户信息
     *
     * @param user
     */
    @Insert(" INSERT INTO `user` (username,birthday,gender,address) VALUES (#{username},#{birthday},#{gender},#{address})")
    void saveUser(User user);

    /**
     * 修改用户信息
     *
     * @param user
     */
    @Update(" UPDATE `user` set username = #{username},gender= #{gender} ,address = #{address} WHERE id = #{id} ")
    void updateUser(User user);

    /**
     * 删除用户信息
     *
     * @param id
     */
    @Delete(" DELETE FROM `user` WHERE id = #{id} ")
    void deleteById(Integer id);

}

• 测试：

package com.sunxiaping;

import com.sunxiaping.domain.User;
import com.sunxiaping.mapper.UserMapper;
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.After;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.Date;
import java.util.List;

public class UserTest {
    SqlSessionFactory sqlSessionFactory = null;
    SqlSession sqlSession = null;

    @Before
    public void before() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

    }

    @After
    public void after() {
        if (sqlSession != null) {
            sqlSession.close();
        }
    }


    @Test
    public void testFindAll() throws IOException {
        sqlSession = sqlSessionFactory.openSession(true);

        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        List<User> userList = userMapper.findAll();

        System.out.println("userList = " + userList);

    }

    @Test
    public void testSaveUser() {
        sqlSession = sqlSessionFactory.openSession(true);

        User user = new User();
        user.setUsername("zhangsan");
        user.setBirthday(new Date());
        user.setGender("男");
        user.setAddress("江苏苏州");

        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        userMapper.saveUser(user);
    }


    @Test
    public void testUpdateUser() {
        sqlSession = sqlSessionFactory.openSession(true);

        User user = new User();
        user.setId(1);
        user.setUsername("张三");
        user.setGender("女");
        user.setAddress("江苏苏州");
        user.setBirthday(new Date());

        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        userMapper.updateUser(user);

    }

    @Test
    public void testDeleteById() {
        sqlSession = sqlSessionFactory.openSession(true);

        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        userMapper.deleteById(1);
    }

}

4 Mybatis注解实现一对一关联查询

• 示例：
• User.java

package com.sunxiaping.domain;

import java.io.Serializable;
import java.util.Date;

public class User implements Serializable {

    private Integer id;

    private String username;

    private Date birthday;

    private String gender;

    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 Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", birthday=" + birthday +
                ", gender='" + gender + '\'' +
                ", address='" + address + '\'' +
                '}';
    }
}

• UserMapper.java

package com.sunxiaping.mapper;

import com.sunxiaping.domain.User;
import org.apache.ibatis.annotations.Select;

public interface UserMapper {

    /**
     * 根据id查询用户信息
     *
     * @param id
     * @return
     */
    @Select("SELECT * FROM `user` WHERE id = #{id}")
    User findById(Integer id);

}

• Account.java

package com.sunxiaping.domain;

import java.io.Serializable;

public class Account implements Serializable {

    private Integer id;

    private Double money;

    /**
     * 一个账户属于一个用户
     */
    private User user;

    public Integer getId() {
        return id;
    }

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

    public Double getMoney() {
        return money;
    }


    public void setMoney(Double money) {
        this.money = money;
    }

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }

    @Override
    public String toString() {
        return "Account{" +
                "id=" + id +
                ", money=" + money +
                ", user=" + user +
                '}';
    }
}

• AccountMapper.java

package com.sunxiaping.mapper;

import com.sunxiaping.domain.Account;
import com.sunxiaping.domain.User;
import org.apache.ibatis.annotations.One;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.mapping.FetchType;

import java.util.List;

public interface AccountMapper {


    /**
     * 查询所有账户以及所属用户信息
     *
     * @return
     */
    @Select(" SELECT * FROM `account`")
    @Results(value = {
            @Result(id = true, property = "id", column = "id"),
            @Result(property = "money", column = "money"),
            @Result(property = "user", column = "user_id", javaType = User.class, one = @One(select = "com.sunxiaping.mapper.UserMapper.findById",fetchType = FetchType.LAZY))
    })
    List<Account> findAccountWithUser();

}

• 测试：

package com.sunxiaping;

import com.sunxiaping.domain.Account;
import com.sunxiaping.mapper.AccountMapper;
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.After;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class AccountTest {
    SqlSessionFactory sqlSessionFactory = null;
    SqlSession sqlSession = null;

    @Before
    public void before() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

    }

    @After
    public void after() {
        if (sqlSession != null) {
            sqlSession.close();
        }
    }

    @Test
    public void test(){
         sqlSession = sqlSessionFactory.openSession(true);

        AccountMapper accountMapper = sqlSession.getMapper(AccountMapper.class);

        List<Account> accountList = accountMapper.findAccountWithUser();

        System.out.println("accountList = " + accountList);

    }
}

5 Mybatis注解实现一对多关联查询

• 示例：
• User.java

package com.sunxiaping.domain;

import java.io.Serializable;
import java.util.Date;
import java.util.List;

public class User implements Serializable {

    private Integer id;

    private String username;

    private Date birthday;

    private String gender;

    private String address;

    private List<Account> accountList;


    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 Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public List<Account> getAccountList() {
        return accountList;
    }

    public void setAccountList(List<Account> accountList) {
        this.accountList = accountList;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", birthday=" + birthday +
                ", gender='" + gender + '\'' +
                ", address='" + address + '\'' +
                ", accountList=" + accountList +
                '}';
    }
}

• Account.java

package com.sunxiaping.domain;

import java.io.Serializable;

public class Account implements Serializable {

    private Integer id;

    private Double money;

    public Integer getId() {
        return id;
    }

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

    public Double getMoney() {
        return money;
    }


    public void setMoney(Double money) {
        this.money = money;
    }

    @Override
    public String toString() {
        return "Account{" +
                "id=" + id +
                ", money=" + money +
                '}';
    }
}

• AccountMapper.java

package com.sunxiaping.mapper;

import com.sunxiaping.domain.Account;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;

public interface AccountMapper {

    @Select("SELECT * FROM `account`")
    @Results({
            @Result(id = true, property = "id", column = "id"),
            @Result(property = "money", column = "money")}
    )
    Account findById(Integer id);
}

• UserMapper.java

package com.sunxiaping.mapper;

import com.sunxiaping.domain.User;
import org.apache.ibatis.annotations.Many;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface UserMapper {

    @Select(" SELECT * FROM `user`")
    @Results({
            @Result(id = true, property = "id", column = "id"),
            @Result(property = "username", column = "username"),
            @Result(property = "birthday", column = "birthday"),
            @Result(property = "gender", column = "gender"),
            @Result(property = "address", column = "address"),
            @Result(property = "accountList", column = "id", many = @Many(select = "com.sunxiaping.mapper.AccountMapper.findById")),
    })
    List<User> findAll();
    
}

• 测试：

package com.sunxiaping;

import com.sunxiaping.domain.User;
import com.sunxiaping.mapper.UserMapper;
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.After;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class UserTest {
    SqlSessionFactory sqlSessionFactory = null;
    SqlSession sqlSession = null;

    @Before
    public void before() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

    }

    @After
    public void after() {
        if (sqlSession != null) {
            sqlSession.close();
        }
    }

    @Test
    public void test() {
        sqlSession = sqlSessionFactory.openSession(true);

        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<User> userList = userMapper.findAll();

        System.out.println("userList = " + userList);
    }


}