天天看點
傳回

mybatis的使用及源碼分析(三) mybatis使用注解執行SQL

本项目搭建源码：https://github.com/zhuquanwen/mybatis-learn/releases/tag/with-annotation

搭建过程：

在上一篇文章基础上搭建，有些过程不详细描述，之前的源码在上一篇已附上，参见上一篇：https://blog.csdn.net/u011943534/article/details/104717560

项目结构：

mybatis的使用及源碼分析(三) mybatis使用注解執行SQL

1、之前引入了Log4j但未做配置，这次通过InitLog4J类配置一下

package com.learn.zqw.log;

import org.apache.log4j.PropertyConfigurator;

/**
 * //TODO
 *
 * @author zhuquanwen
 * @vesion 1.0
 * @date 2020/3/8 10:35
 * @since jdk1.8
 */
public class InitLog4J {
    public static void init(){
        PropertyConfigurator.configure("log4j.properties");
    }
    static {
        init();
    }
}

2、引入测试的两个sql脚本，因为这次顺带实现了关联查询功能，故引入两个

/*
Navicat MySQL Data Transfer

Source Server         : localhost
Source Server Version : 50722
Source Host           : localhost:3306
Source Database       : mybatis_learn

Target Server Type    : MYSQL
Target Server Version : 50722
File Encoding         : 65001

Date: 2020-03-08 14:23:37
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for library
-- ----------------------------
DROP TABLE IF EXISTS `library`;
CREATE TABLE `library` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `address` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of library
-- ----------------------------
INSERT INTO `library` VALUES ('1', '国家图书馆', '北京市');

           
/*
Navicat MySQL Data Transfer

Source Server         : localhost
Source Server Version : 50722
Source Host           : localhost:3306
Source Database       : mybatis_learn

Target Server Type    : MYSQL
Target Server Version : 50722
File Encoding         : 65001

Date: 2020-03-08 14:23:47
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for book
-- ----------------------------
DROP TABLE IF EXISTS `book`;
CREATE TABLE `book` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `num` int(11) DEFAULT NULL,
  `library_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `book_ibfk_1` (`library_id`),
  CONSTRAINT `book_ibfk_1` FOREIGN KEY (`library_id`) REFERENCES `library` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of book
-- ----------------------------
INSERT INTO `book` VALUES ('1', 'C语言程序设计V2', '789', '1');
INSERT INTO `book` VALUES ('2', '泰戈尔诗集', '234', null);
INSERT INTO `book` VALUES ('3', '朝花夕拾', '435', null);
INSERT INTO `book` VALUES ('5', '离散数学', '400', '1');
INSERT INTO `book` VALUES ('6', '大数据时代', '300', '1');

3、定义两个对应的实体Book、Library

package com.learn.zqw.sqlannotation.domain;

import lombok.Data;

/**
 * //TODO
 *
 * @author zhuquanwen
 * @vesion 1.0
 * @date 2020/3/8 13:18
 * @since jdk1.8
 */
@Data
public class Library {

    private Integer id;

    private String name;

    private String address;
}

           
package com.learn.zqw.sqlannotation.domain;

import lombok.Data;

/**
 * Book实体类
 *
 * @author zhuquanwen
 * @vesion 1.0
 * @date 2020/3/8 12:05
 * @since jdk1.8
 */
@Data
public class Book {

    private Integer id;

    private String name;

    private Integer num;

    private Library library;
}

如上，再Book中定义library属性，book与library之间是多对一的关系

4、定义Book和Library的Mapper，共四个文件

<?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.learn.zqw.sqlannotation.mapper.LibraryMapper">
    
</mapper>
           
package com.learn.zqw.sqlannotation.mapper;

import com.learn.zqw.sqlannotation.domain.Book;
import com.learn.zqw.sqlannotation.domain.Library;
import org.apache.ibatis.annotations.Select;

public interface LibraryMapper {

    @Select("select * from library where id = #{id}")
    Library findById(int id);

    int insert(Book book);

}

           
<?xml version="1.0" encoding="UTF8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.learn.zqw.sqlannotation.mapper.BookMapper">
    <!--插入一条记录-->
    <insert id="insert" parameterType="com.learn.zqw.sqlannotation.domain.Book">
        insert into book (name, num, library_id) values (#{name}, #{num}, #{library.id})
    </insert>

    <!--插入记录并返回ID-->
    <insert id="insertReturnId" useGeneratedKeys="true" keyProperty="id" keyColumn="id" parameterType="com.learn.zqw.sqlannotation.domain.Book">
        insert into book(name, num, library_id)
        values(#{name}, #{num}, #{library.id})
    </insert>
</mapper>
           
package com.learn.zqw.sqlannotation.mapper;

import com.learn.zqw.sqlannotation.domain.Book;
import com.learn.zqw.sqlannotation.domain.Library;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.mapping.FetchType;

import java.util.List;

public interface BookMapper {

    int insert(Book book);

    int insertReturnId(Book book);

    @Select("select * from book")
    List<Book> findAll();

    @Select("select * from book where id = #{id}")
    @Results({
            @Result(id=true,column="id",property="id"),
            @Result(column = "name",property = "name"),
            @Result(column = "num",property = "num"),
            @Result(column = "library_id",property = "library",javaType = Library.class,
                    one = @One(select = "com.learn.zqw.sqlannotation.mapper.LibraryMapper.findById",fetchType = FetchType.LAZY)),
    })
    Book findById(int id);
    Book findById(Book book);


    @Select(value = {" <script>" +
            " SELECT * FROM book " +
            " <where> 1=1 " +
            " <if test=\"name != null\"> AND name like CONCAT('%', #{name}, '%')</if> " +
            " <if test=\"num != null\" >  AND num>#{num}</if> " +
            " </where>" +
            " </script>"})
    @Results({
            @Result(id=true,column="id",property="id"),
            @Result(column = "name",property = "name"),
            @Result(column = "num",property = "num"),
            @Result(column = "library_id",property = "library",javaType = Library.class,
                    one = @One(select = "com.learn.zqw.sqlannotation.mapper.LibraryMapper.findById",fetchType = FetchType.LAZY)),
    })
    List<Book> findByCondition(Book book);

    @Insert("insert into book(name, num, library_id) values (#{name}, #{num}, #{library.id})")
    int insert2(Book book);

    @Update("update book set name = #{name} where id = #{id}")
    int update(Book book);

    @Delete("delete from book where id = #{id}")
    int delete(int id);
    int delete(Book id);


}

如上，查询时使用

@Select

,修改使用

@Update

，删除使用

@Delete

，新增使用

@Insert

，SQL写法与在XML中基本一致，可以使用

@Results

等注解自定义返回结果。

注意以下几点:

  • 注解和XML可以混用，这里的

    insert、insertReturnId

     等函数的SQL是在XML中定义的；
  • 关联查询时可以在返回的自定义

    @Result

     中使用

    [email protected]

     的方式，定义一个关联的查询；
  • 嵌套取值时可以使用如上的

    #{library.id}

     的方式；
  • 模糊查询使用语法如下

    name like CONCAT('%', #{name}, '%')

     这样的方式；
  • 动态取值时

    #和$

     是有区别的，这里都是用的

    #

     ，

    $

     直接做字符串拼接，有SQL注入的风险，一般用于动态表名和列名的读取，如order by 后，而

    #

     使用JDBC的preparestatement预编译，会将参数转换为字符串，能够防止SQL注入；
  • 这里和generator混用也是没问题的。

5、注入新的Mapper

在

SqlMapConfig.xml

中添加两个新的xml或使用package扫描

<mappers>

        <mapper resource="com/learn/zqw/IUserMapper.xml" />
        <mapper resource="com/learn/zqw/generator/mapper/StudentMapper.xml" />

        <!--可以直接把每个xml作配置，也可以使用包扫描，使用包扫描，如果使用纯注解方式，也可以把对应的.xml文件删除-->
        <!--<mapper resource="com/learn/zqw/sqlannotation/mapper/BookMapper.xml" />-->
        <!-- <mapper resource="com/learn/zqw/sqlannotation/mapper/LibraryMapper.xml" />-->
        <package name="com.learn.zqw.sqlannotation.mapper"/>
    </mappers>

6、编写单元测试

package com.learn.zqw.sqlannotation;

import com.learn.zqw.sqlannotation.domain.Book;
import com.learn.zqw.sqlannotation.domain.Library;
import com.learn.zqw.sqlannotation.mapper.BookMapper;
import com.learn.zqw.sqlannotation.mapper.LibraryMapper;
import lombok.Cleanup;
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.Assert;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.junit.runners.JUnit4;

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

/**
 * 测试注解方式SQL执行
 *
 * @author zhuquanwen
 * @vesion 1.0
 * @date 2020/3/8 12:11
 * @since jdk1.8
 */
@RunWith(JUnit4.class)
public class BookTests {

    /**
     * 测试查询所有
     * */
    @Test
    public void test() throws IOException {
        @Cleanup InputStream in = Resources.getResourceAsStream ("SqlMapConfig.xml");
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        SqlSessionFactory factory = builder.build(in);
        SqlSession session = factory.openSession();
        BookMapper mapper = session.getMapper(BookMapper.class);
        List<Book> books = mapper.findAll();
        Assert.assertNotNull(books);
        if (books != null) {
            books.forEach(System.out::println);
        }
    }

    /**
     * 测试按照ID查询，并关联一个主表记录，且自定义result
     * */
    @Test
    public void test2() throws IOException {
        @Cleanup InputStream in = Resources.getResourceAsStream ("SqlMapConfig.xml");
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        SqlSessionFactory factory = builder.build(in);
        SqlSession session = factory.openSession();
        BookMapper mapper = session.getMapper(BookMapper.class);
        Book book = mapper.findById(1);
        Assert.assertNotNull(book);
        System.out.println(book);
        Book book1 = new Book();
        book1.setId(2);
        Book book2 = mapper.findById(book1);
        Assert.assertNotNull(book2);
        System.out.println(book2);
    }

    /**
     * 测试使用XML中的插入方法,并且为嵌套插入
     *
     * */
    @Test
    public void test3() throws IOException {
        @Cleanup InputStream in = Resources.getResourceAsStream ("SqlMapConfig.xml");
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        SqlSessionFactory factory = builder.build(in);
        SqlSession session = factory.openSession();
        BookMapper mapper = session.getMapper(BookMapper.class);
        Book book = new Book();
        book.setName("离散数学");
        book.setNum(400);
        LibraryMapper libraryMapper = session.getMapper(LibraryMapper.class);
        Library library = libraryMapper.findById(1);
        book.setLibrary(library);
        int inserted = mapper.insert(book);
        Assert.assertEquals(1, inserted);
        System.out.println(inserted);
        session.commit();
    }

    /**
     * 测试注解条件查询
     * */
    @Test
    public void test4() throws IOException {
        @Cleanup InputStream in = Resources.getResourceAsStream ("SqlMapConfig.xml");
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        SqlSessionFactory factory = builder.build(in);
        SqlSession session = factory.openSession();
        BookMapper mapper = session.getMapper(BookMapper.class);
        Book book = new Book();
        book.setName("C");
        book.setNum(10);
        List<Book> books = mapper.findByCondition(book);
        Assert.assertNotNull(books);
        books.forEach(System.out::println);
    }

    /**
     * 测试使用注解中的插入方法,并且为嵌套插入
     *
     * */
    @Test
    public void test5() throws IOException {
        @Cleanup InputStream in = Resources.getResourceAsStream ("SqlMapConfig.xml");
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        SqlSessionFactory factory = builder.build(in);
        SqlSession session = factory.openSession();
        BookMapper mapper = session.getMapper(BookMapper.class);
        Book book = new Book();
        book.setName("大数据时代");
        book.setNum(300);
        LibraryMapper libraryMapper = session.getMapper(LibraryMapper.class);
        Library library = libraryMapper.findById(1);
        book.setLibrary(library);
        int inserted = mapper.insert2(book);
        Assert.assertEquals(1, inserted);
        System.out.println(inserted);
        session.commit();
    }

    /**
     * 测试使用注解中的修改入方法
     *
     * */
    @Test
    public void test6() throws IOException {
        @Cleanup InputStream in = Resources.getResourceAsStream ("SqlMapConfig.xml");
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        SqlSessionFactory factory = builder.build(in);
        SqlSession session = factory.openSession();
        BookMapper mapper = session.getMapper(BookMapper.class);
        Book book = mapper.findById(1);
        book.setName("C语言程序设计V2");
        int updated = mapper.update(book);
        Assert.assertEquals(1, updated);
        System.out.println(updated);
        session.commit();
    }

    /**
     * 测试使用注解中删除方法
     *
     * */
    @Test
    public void test7() throws IOException {
        @Cleanup InputStream in = Resources.getResourceAsStream ("SqlMapConfig.xml");
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        SqlSessionFactory factory = builder.build(in);
        SqlSession session = factory.openSession();
        BookMapper mapper = session.getMapper(BookMapper.class);
        Book book = new Book();
        book.setName(UUID.randomUUID().toString());
        book.setNum(300);
        LibraryMapper libraryMapper = session.getMapper(LibraryMapper.class);
        Library library = libraryMapper.findById(1);
        book.setLibrary(library);
        int inserted = mapper.insertReturnId(book);
        session.commit();
        Assert.assertEquals(1, inserted);

        int delete = mapper.delete(book.getId());
        Assert.assertEquals(1, delete);
        session.commit();
    }
}
mybatis mybatis注解 注解SQL
上一篇: Mybatis動态sql語句注解的簡單執行個體
下一篇: mybatis 簡單使用注解編寫sql

繼續閱讀