Spring Boot学习笔记----mybatis注解（一）

淡淡的烟草味﹌ 2022-06-04 06:54 245阅读 0赞

之前使用jpa，感觉不错。不用涉及sql便可完成对数据库的增删改查。但项目组要求使用mybatis进行开发，也就用了。两者的区别在于，后者需要进行SQL语句的注解绑定。

网上搜到的mybatis资料，繁琐复杂，多是使用各种配置文件。这对不怎么用XML的开发者来说，简直是头大。因此，还是喜欢注解的方式来解决问题。尽管mybatis官网并不推荐这种方式。官网对于注解的方式，也只是星星点点，躲躲闪闪。

废话不再多说，奔主题。

mybatis如何进行数据库操作呢。需要以下5个步骤。（本文以MAVEN工程为例，开发环境为IDEA，数据库为MySql）  
（1）添加Dependence  
（2）创建数据库表实例entity。  
（3）创建Dao接口，将SQL与方法进行绑定  
（4）设置遍历Dao的地址（包名）  
（5）使用Dao

我们通过一个实例来看一下整个过程。基于angular的阴影，我们以hero为例。

## （1）添加Dependence ##

<dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>1.3.1</version>
    </dependency>

## （2）创建数据库及其表 ##

这是mybatis的硬伤。当然，网上也有博文讲解如何自动生成对应表，类似转基因了，暂且放下，之后再看。

在Application.properties中配置数据库链接，内容如下

spring.datasource.url=jdbc:mysql://127.0.0.1:3306/db_hero
    spring.datasource.username=root
    spring.datasource.password=hero
    spring.datasource.driver-class-name=com.mysql.jdbc.Driver

创建语句如下

CREATE SCHEMA `db_hero` ;
    CREATE TABLE `db_hero`.`hero` ( `id` INT NOT NULL AUTO_INCREMENT, `name` VARCHAR(45) NOT NULL, `age` INT NOT NULL, PRIMARY KEY (`id`));

## （3）创建实体类 ##

之前的[博文][Link 1]中，有关于IDEA如何自动生成实体类的相关描述，这里不再啰嗦。

## （4）创建Dao接口 ##

从代码角度来看，Dao只是一个虚拟接口，但其作用至关重要。它是绑定数据库表，实体类，以及实务操作的枢纽。也是使用注解的关键所在。

先来看最简单的普通查询。使用最简单的增删改查注解。

@Select()
    @Insert()
    @Update()
    @Delete()
    @Result()

我们来看个简单的例子

package com.breakloop.mybatis.mapper;
    
    import com.breakloop.mybatis.entity.Hero;
    import org.apache.ibatis.annotations.*;
    
    import java.util.List;
    @Mapper
    public interface HeroMapper {
        @Insert("Insert into hero(name,age) values(#{name},#{age})")
        @Options(useGeneratedKeys=true, keyColumn="id", keyProperty="id")
        void addHero(Hero hero);
    
        @Update("update hero set name=#{name},age=#{age} where id=#{id}")
        void updateHero(Hero hero);
    
        @Select("select * from hero where name=#{name}")
        List<Hero> selectHeroByName(String name);
    
        @Select("select * from hero where age=#{age}")
        List<Hero> selectHeroByAge(int age);
    
        @Select("select * from hero where id=#{id}")
        List<Hero> selectHeroById(Long id);
    
        @Select("select * from hero")
        List<Hero> selectAll();
    
        @Delete("delete from hero where name=#{name}")
        void deleteHeroByName(String name);
    
        @Delete("delete from hero where age=#{age}")
        void deleteHeroByAge(int age);
    
        @Delete("delete from hero where id=#{id}")
        void deleteHeroById(Long id);
    
        //@Delete("delete from hero where name=#{name} and age=#{age}")
        //void deleteHero(Hero hero);
    
        @Delete("delete from hero where name=#{aa} and age=#{bb}")
        void deleteHero(@Param("aa") String name,@Param("bb") int age);
    
        @Delete("delete from hero")
        void deleteAll();
    }

需要注意的是

（a）需要使用\#\{实体类成员属性\}跟列名进行绑定

（b）在添加数据时，对于自增列需要进行注解

@Options(useGeneratedKeys=true, keyColumn="id", keyProperty="id")

（c）如果列名跟实体类成员属性名不同，对于Select，需要对结果进行绑定，使用@Result。

例如，Hero实体类中，sName对应name列，nAge代表age列。  
那么，以selectHeroByName为例

@Select("select * from hero where name=#{
       sName}")
        @Results( {
                        @Result(id = true, property = "id", column = "id"), @Result(property = "nAge",column = "age"), @Result(property = "sName",column = "name") } ) List<Hero> selectHeroByName(String name);

（d）如果绑定的sql语句相同，将会报错。

这也是代码中屏蔽void deleteHero(Hero hero)方法的原因。该方法与void deleteHero(String sName,int nAge)方法所绑定的sql语句一致。

（e）对于@Param要多说几句。

如果传参不是实体类，或者参数数量大于1，则需要@Param。  
如果不用@Param，即使传参名称与实体类属性名（或列名）一致，也会报错（执行方法时）。  
***错误案例如下***

@Delete("delete from hero where name=#{sName} and age=#{nAge}")
        void deleteHero(String sName,int nAge);

或

@Delete("delete from hero where name=#{name} and age=#{age}")
        void deleteHero(String name,int age);

注：参考了博文  
[https://www.cnblogs.com/whisper527/p/6568028.html][https_www.cnblogs.com_whisper527_p_6568028.html]，但发现$的方式不被识别。不知道是什么原因。有知道的童鞋，还望指点。

## （5）标记Dao所在Package ##

Dao既然如此重要，那么Application该如何找到它呢？有两种方式  
（1）在每个Mapper Interface上添加@Mapper  
（2）在Application类上添加@MapperScan(“com.breakloop.mybatis.mapper”)，其中com.breakloop.mybatis.mapper为mapper所在包。  
若存在多个，则可以使用数组的方式@MapperScan(\{“com.breakloop.mybatis.mapper”,”com.breakloop.mybatis.entity”\})  
建议使用第二种方式，不用逐个添加@Mapper

## （6）使用Dao ##

在这里，我们只是简单的在controller中使用Dao，在Logger里打印结果。

package com.breakloop.mybatis.controller;
    
    import com.breakloop.mybatis.entity.Hero;
    import com.breakloop.mybatis.mapper.HeroMapper;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RestController;
    
    import java.util.List;
    
    @RestController
    public class HeroController {
    
        Logger logger= LoggerFactory.getLogger(HeroController.class);
    
        @Autowired
        HeroMapper mapper;
    
        @RequestMapping("/ShowLogger")
        public void ShowInLogger(){
            InsertCase();
            SelectCase();
            UpdateCase();
            DeleteCase();
        }
    
        public void InsertCase(){
            String name;
            for (int i = 0; i < 9; i++) { 
                Hero hero=new Hero();
                hero.setAge(i);
                name="hero"+i;
                hero.setName(name);
                mapper.addHero(hero);
                logger.info("Insert Hero, Hero Name = "+name+", Age = "+i);
            }
        }
    
        public void SelectCase(){
            List<Hero> heroes=mapper.selectAll();
            int age=0;
            String name="hero1";
            Long id=2l;
            for (Hero item :
                    heroes) {
                logger.info("Select All, Hero Name "+item.getName()+", Age = "+item.getAge());
            }
    
            heroes=mapper.selectHeroByAge(age);
            if(heroes.size()!=0){
                for (Hero item :
                        heroes) {
                    logger.info("Select Hero by Age = "+age+", Hero Name "+item.getName()+", Age = "+item.getAge());
                }
            }
    
            heroes=mapper.selectHeroByName(name);
            if(heroes.size()!=0){
                for (Hero item :
                        heroes) {
                    logger.info("Select Hero by Name = "+name+", Hero Name "+item.getName()+", Age = "+item.getAge());
                }
            }
    
        }
    
        public void UpdateCase(){
            List<Hero> heroes;
            Long id=0L;
            int age=10;
            String name="hero"+age;
            Hero hero=new Hero();
    
            hero.setAge(age);
            hero.setName(name);
            hero.setId(id);
            mapper.updateHero(hero);
            logger.info("Update Hero by Id = "+id+", Hero Name "+hero.getName()+", Age = "+hero.getAge());
    
            heroes=mapper.selectHeroById(id);
            if(heroes.size()!=0){
                for (Hero item :
                        heroes) {
                    logger.info("Select Hero by Id = "+id+", Hero Name "+item.getName()+", Age = "+item.getAge());
                }
            }
        }
    
        public void DeleteCase(){
            Long id=1l;
            String name="hero2";
            int age=3;
            List<Hero> heroes;
            Hero hero;
    
            mapper.deleteHeroById(id);
            logger.info("Delete Hero by Id = "+id);
    
            mapper.deleteHeroByName(name);
            logger.info("Delete Hero by Name = "+name);
    
            mapper.deleteHeroByAge(age);
            logger.info("Delete Hero by Age = "+age);
    
            //hero=new Hero();
            //hero.setName("hero4");
            //hero.setAge(4);
            //mapper.deleteHero(hero);
            //logger.info("Delete Hero by Age = 4 and Name = hero4");
    
            mapper.deleteHero("hero5",5);
            logger.info("Delete Hero by Age = 5 and Name = hero5");
    
            heroes=mapper.selectAll();
            if(heroes.size()!=0){
                for (Hero item :
                        heroes) {
                    logger.info("Select All, Hero Name "+item.getName()+", Age = "+item.getAge());
                }
            }
    
            mapper.deleteAll();
            heroes=mapper.selectAll();
            if(heroes.size()!=0){
                for (Hero item :
                        heroes) {
                    logger.info("Select All, Hero Name "+item.getName()+", Age = "+item.getAge());
                }
            }else {
                logger.info("No Hero");
            }
        }
    }

执行结果如下：

2017-12-08 14:08:01.312  INFO 8684 --- [nio-8080-exec-6] c.b.mybatis.controller.HeroController    : Insert Hero, Hero Name = hero0, Age = 0
    2017-12-08 14:08:01.318  INFO 8684 --- [nio-8080-exec-6] c.b.mybatis.controller.HeroController    : Insert Hero, Hero Name = hero1, Age = 1
    2017-12-08 14:08:01.327  INFO 8684 --- [nio-8080-exec-6] c.b.mybatis.controller.HeroController    : Insert Hero, Hero Name = hero2, Age = 2
    2017-12-08 14:08:01.333  INFO 8684 --- [nio-8080-exec-6] c.b.mybatis.controller.HeroController    : Insert Hero, Hero Name = hero3, Age = 3
    2017-12-08 14:08:01.340  INFO 8684 --- [nio-8080-exec-6] c.b.mybatis.controller.HeroController    : Insert Hero, Hero Name = hero4, Age = 4
    2017-12-08 14:08:01.345  INFO 8684 --- [nio-8080-exec-6] c.b.mybatis.controller.HeroController    : Insert Hero, Hero Name = hero5, Age = 5
    2017-12-08 14:08:01.352  INFO 8684 --- [nio-8080-exec-6] c.b.mybatis.controller.HeroController    : Insert Hero, Hero Name = hero6, Age = 6
    2017-12-08 14:08:01.357  INFO 8684 --- [nio-8080-exec-6] c.b.mybatis.controller.HeroController    : Insert Hero, Hero Name = hero7, Age = 7
    2017-12-08 14:08:01.365  INFO 8684 --- [nio-8080-exec-6] c.b.mybatis.controller.HeroController    : Insert Hero, Hero Name = hero8, Age = 8
    2017-12-08 14:08:01.375  INFO 8684 --- [nio-8080-exec-6] c.b.mybatis.controller.HeroController    : Select All, Hero Name hero0, Age = 0
    2017-12-08 14:08:01.375  INFO 8684 --- [nio-8080-exec-6] c.b.mybatis.controller.HeroController    : Select All, Hero Name hero1, Age = 1
    2017-12-08 14:08:01.375  INFO 8684 --- [nio-8080-exec-6] c.b.mybatis.controller.HeroController    : Select All, Hero Name hero2, Age = 2
    2017-12-08 14:08:01.375  INFO 8684 --- [nio-8080-exec-6] c.b.mybatis.controller.HeroController    : Select All, Hero Name hero3, Age = 3
    2017-12-08 14:08:01.375  INFO 8684 --- [nio-8080-exec-6] c.b.mybatis.controller.HeroController    : Select All, Hero Name hero4, Age = 4
    2017-12-08 14:08:01.375  INFO 8684 --- [nio-8080-exec-6] c.b.mybatis.controller.HeroController    : Select All, Hero Name hero5, Age = 5
    2017-12-08 14:08:01.375  INFO 8684 --- [nio-8080-exec-6] c.b.mybatis.controller.HeroController    : Select All, Hero Name hero6, Age = 6
    2017-12-08 14:08:01.375  INFO 8684 --- [nio-8080-exec-6] c.b.mybatis.controller.HeroController    : Select All, Hero Name hero7, Age = 7
    2017-12-08 14:08:01.375  INFO 8684 --- [nio-8080-exec-6] c.b.mybatis.controller.HeroController    : Select All, Hero Name hero8, Age = 8
    2017-12-08 14:08:01.378  INFO 8684 --- [nio-8080-exec-6] c.b.mybatis.controller.HeroController    : Select Hero by Age = 0, Hero Name hero0, Age = 0
    2017-12-08 14:08:01.381  INFO 8684 --- [nio-8080-exec-6] c.b.mybatis.controller.HeroController    : Select Hero by Name = hero1, Hero Name hero1, Age = 1
    2017-12-08 14:08:01.384  INFO 8684 --- [nio-8080-exec-6] c.b.mybatis.controller.HeroController    : Update Hero by Id = 0, Hero Name hero10, Age = 10
    2017-12-08 14:08:01.409  INFO 8684 --- [nio-8080-exec-6] c.b.mybatis.controller.HeroController    : Delete Hero by Id = 1
    2017-12-08 14:08:01.416  INFO 8684 --- [nio-8080-exec-6] c.b.mybatis.controller.HeroController    : Delete Hero by Name = hero2
    2017-12-08 14:08:01.422  INFO 8684 --- [nio-8080-exec-6] c.b.mybatis.controller.HeroController    : Delete Hero by Age = 3
    2017-12-08 14:08:01.429  INFO 8684 --- [nio-8080-exec-6] c.b.mybatis.controller.HeroController    : Delete Hero by Age = 5 and Name = hero5
    2017-12-08 14:08:01.432  INFO 8684 --- [nio-8080-exec-6] c.b.mybatis.controller.HeroController    : Select All, Hero Name hero1, Age = 1
    2017-12-08 14:08:01.432  INFO 8684 --- [nio-8080-exec-6] c.b.mybatis.controller.HeroController    : Select All, Hero Name hero4, Age = 4
    2017-12-08 14:08:01.432  INFO 8684 --- [nio-8080-exec-6] c.b.mybatis.controller.HeroController    : Select All, Hero Name hero6, Age = 6
    2017-12-08 14:08:01.432  INFO 8684 --- [nio-8080-exec-6] c.b.mybatis.controller.HeroController    : Select All, Hero Name hero7, Age = 7
    2017-12-08 14:08:01.432  INFO 8684 --- [nio-8080-exec-6] c.b.mybatis.controller.HeroController    : Select All, Hero Name hero8, Age = 8
    2017-12-08 14:08:01.440  INFO 8684 --- [nio-8080-exec-6] c.b.mybatis.controller.HeroController    : No Hero

## 参考文献 ##

官方文档  
[http://www.mybatis.org/mybatis-3/zh/java-api.html\#sqlSessions][http_www.mybatis.org_mybatis-3_zh_java-api.html_sqlSessions]  
MyBatis使用注解实现增删改查  
[http://blog.csdn.net/x\_iya/article/details/72983906][http_blog.csdn.net_x_iya_article_details_72983906]  
java 中MyBatis注解映射的实例详解  
[http://www.jb51.net/article/122942.htm][http_www.jb51.net_article_122942.htm]  
使用注解配置Mapper  
[http://blog.csdn.net/kingice1014/article/details/70263148][http_blog.csdn.net_kingice1014_article_details_70263148]

[Link 1]: http://blog.csdn.net/daihuimaozideren/article/details/78744480
[https_www.cnblogs.com_whisper527_p_6568028.html]: https://www.cnblogs.com/whisper527/p/6568028.html
[http_www.mybatis.org_mybatis-3_zh_java-api.html_sqlSessions]: http://www.mybatis.org/mybatis-3/zh/java-api.html#sqlSessions
[http_blog.csdn.net_x_iya_article_details_72983906]: http://blog.csdn.net/x_iya/article/details/72983906
[http_www.jb51.net_article_122942.htm]: http://www.jb51.net/article/122942.htm
[http_blog.csdn.net_kingice1014_article_details_70263148]: http://blog.csdn.net/kingice1014/article/details/70263148