SpringBoot整合Mybatis注解版

港控/mmm° 2022-05-26 08:19 328阅读 0赞

javax.persistence jar的作用 实体类采用注解版

<dependency>  
    <groupId>javax.persistence</groupId>  
    <artifactId>persistence-api</artifactId>  
    <version>1.0</version>

</dependency>

1 简约版：

项目结构：

![70][]

实体类：Employee

public class Employee {
    
          private Integer id;
          private String name;
          private Double salary;
          private Integer age;
    
        public Employee(Integer id, String name, Double salary, Integer age) {
            this.id = id;
            this.name = name;
            this.salary = salary;
            this.age = age;
        }
    
        public Employee() {
        }
    
        public Integer getId() {
            return id;
        }
    
        public void setId(Integer id) {
            this.id = id;
        }
    
    
        public String getName() {
            return name;
        }
    
        public void setName(String name) {
            this.name = name;
        }
    
    
        public Double getSalary() {
            return salary;
        }
    
        public void setSalary(Double salary) {
            this.salary = salary;
        }
    
    
        public Integer getAge() {
            return age;
        }
    
        public void setAge(Integer age) {
            this.age = age;
        }
    
        @Override
        public String toString() {
            return "Employee{" +
                    "id=" + id +
                    ", name='" + name + '\'' +
                    ", salary=" + salary +
                    ", age=" + age +
                    '}';
        }
    
        public Employee(String name, Double salary, Integer age) {
            this.name = name;
            this.salary = salary;
            this.age = age;
        }
    }

mapper接口：

/*mapper接口*/
    
    //1 @Mapper将UserDao声明为一个Mapper接口
    //2 @Results是结果映射列表，@Result中property是User类的属性名，colomn是数据库表的字段名
    //3 @Select, @Insert 分别代表了执行的真实SQL
    
    @Component
    @Mapper
    public interface EmployeeMapper {
           @Select("select * from employee where age=#{age}")
           List<Employee> get(int age);
           @Insert("insert into employee(name,salary,age) value(#{name},#{salary},#{age})")
           void insert(Employee employee);
    
    
    }

EmployeeController:

@Controller
    public class EmployeeController {
    
        @Autowired //自动连接到UserService Bean
        private EmployeeMapper employeeMapper;
    
        @RequestMapping(value = "/show")
        @ResponseBody
        public String show() {
            return "ok";
        }
    
        @RequestMapping(value = "/get")
        @ResponseBody
            public Object get(int age) {
    
            return employeeMapper.get(age);
        }
    
        @RequestMapping(value=/insert")
        public String insert() {
            Employee employee=new Employee("tom",12000.0,25);
             employeeMapper.insert(employee);
            return "ok";
        }
    }

SpringBoot启动类：注意这里要扫描mapper接口的所在包

@SpringBootApplication
    @MapperScan(basePackages = "yiche.com.mapper")
    public class MyApplication {
        public static void main(String[] args){
            SpringApplication.run(MyApplication.class,args);
        }
    }

application.properties

![70 1][]

pom 文件：

<dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    
    <dependency>
      <groupId>org.mybatis.spring.boot</groupId>
      <artifactId>mybatis-spring-boot-starter</artifactId>
      <version>1.1.1</version>
    </dependency>
    
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.21</version>
    </dependency>
    
    
    <dependency>
      <groupId>javax.persistence</groupId>
      <artifactId>persistence-api</artifactId>
      <version>1.0</version>
    </dependency>

下面SpringBoot整合Mybatis注解升级版：

三张表之间的关联查询     user  -->多个orders

order --> 多个orderItem

建表sql如下

CREATE TABLE USER(  
  uid INT NOT NULL AUTO\_INCREMENT,  
  NAME VARCHAR(50),  
  PRIMARY KEY(uid)  
);  
  
  
  
  
CREATE TABLE orders(  
  oid INT NOT NULL AUTO\_INCREMENT,  
  NAME VARCHAR(50),  
  uid INT,  
  PRIMARY KEY(oid),  
  FOREIGN KEY(uid) REFERENCES USER(uid)  
);  
  
  
CREATE TABLE orderItem(  
  id INT NOT NULL AUTO\_INCREMENT,  
  NAME VARCHAR(50),  
  oid INT,  
  PRIMARY KEY(id),  
  FOREIGN KEY(oid) REFERENCES orders(oid)  
)

实体类：

public class User \{  
     private Integer uid;  
     private String name;  
     private List<Orders> orders;

public class Orders \{  
     private  Integer oid ;  
     private  String name;  
     private  User user;  
     private  List<OrderItem> orderItems;

public class OrderItem \{  
     private Integer id;  
     private String  name;  
     private Orders orders;

userMapper中的接口重新定义方法

@Select("select * from user where uid=#{id}")
    User getUserById(int id);

和如下的这种写法一致  
  
@Select("select \* from user where uid=\#\{id\}")  
       @Results(\{  
               @Result(property = "uid", column = "uid"),  
               @Result(property = "name", column = "name")  
       \})

User getUserById(int id);

Controller：

@RequestMapping(value="/getUser")
    @ResponseBody
    public User getUser() {
        int id=1;
        User user= employeeMapper.getUserById(id);
        return user;
    }

结果： 查到的orders属性为为null  因为还没和ordes属性建立关联

![70 2][]

下面建立联系

上面得到uid  还需要在order中写方法 根据外键uid得到orders的方法

在OrderMapper中定义方法

public interface OrderMapper {
    
            //根据外键获得orders的方法
            @Select("select * from orders where uid=#{uid}")
            List<Orders> getOrdersByUid(int uid);
    }

然后修改原来userMapper接口中的方法因为要填充属性原来的方法sql不满足条件

注意：properties=“orders”是 User实体类中的属性

column="uid" 是orders表中的外键

@Select("select * from user where uid=#{id}")
    @Results({
            @Result(property = "uid", column = "uid"),
            @Result(property = "name", column = "name"),
            @Result(
                    property = "orders",
                    column = "uid",
                    many = @Many(select="yiche.com.mapper.OrderMapper.getOrdersByUid")
            )
    })
    User getUserById(int id);

结果：  此时orders已经有值  但是orders中的属性 orderItems还没有值  user也没有值  用同理的方法可以实现值

![70 3][]

现在把orderItem完善值 ：

在OrderItemMapper添加工具外键获取OrderItem的方法

public interface OrderItemMapper {
    
           @Select("select * from orderitem where oid=#{oid}")
           List<OrderItem> getOrderItemByOid();
    
    }

在修改OrderMapper接口原来的方法 让order的属性和orderItem建立联系

public interface OrderMapper {
    
            //根据外键获得orders的方法
            @Select("select * from orders where uid=#{uid}")
            @Results(
                    {
                            @Result(property = "oid", column = "oid"),
                            @Result(property = "name", column = "name"),
                            @Result(
                                    property = "orderItems",
                                    column = "oid",
                                    many = @Many(select = "yiche.com.mapper.OrderItemMapper.getOrderItemByOid")
                            )
                    }
            )
            List<Orders> getOrdersByUid(int uid);
    }

访问：controller        getUser方法

结果：打印 orderitem已经有值

{"uid":1,"name":"tom","orders":[{"oid":1,"name":"a-order","user":null,"orderItems":[{"id":1,"name":"apple","orders":null},{"id":2,"name":"paper","orders":null}]},{"oid":2,"name":"b-order","user":null,"orderItems":[{"id":3,"name":"dirnk","orders":null},{"id":4,"name":"pel","orders":null}]}]}

同理：@用@one的方法同样可以把orders里面的user   orderItem里面的order完善值

### @One @Many的总结 ###

注意

同点：  
\- 无论是一对一还是一对多，都是通过附属查询来实现的，我们需要定义这个附属查询方法。  
\- 在主查询方法中通过@One、@Many指定附属查询方法的全路径。  
\- 都通过column来传递参数给附属方法。

不同点：  
\- 一对一，那么附属方法返回的是一个单独的对象

\- 一对多，那么附属方法返回的是一个对象集合

接下来看的内容：动态语言注解：

（1）动态语言注解  
（2）@Provider使用思路  
（3）@SelectProvider小试牛刀  
（4）@SelectProvider初露锋芒  
（5）@SelectProvider过关斩将  
（6）@InsertProvider小弟不敢当  
（7）@UpdateProvider你加我来改

（8）@DeleteProvider不高兴就删

使用的方式就是编写一个类 SqlProvide 提供sql语句  而mapper接口使用对应的注解

public class SqlProvide \{  
  
  
         public String select1(Employee employee)\{  
  
  
               StringBuilder sql=new StringBuilder();  
               sql.append("select \* from employee where 1=1");  
               if (employee.getName() != null)\{  
                   sql.append(" and name=\#\{name\}");  
               \}  
  
  
               if (employee.getAge() != null)\{  
                   sql.append(" and age=\#\{age\}");  
               \}  
  
  
  
  
               return  sql.toString();  
         \}

EmployeeMapper使用对应的注解：

@SelectProvider(type = SqlProvide.class,method = "select1")

Employee  getEmployeeBy(Employee employee);

Controller方法：

@RequestMapping(value="/testsql")  
    @ResponseBody  
    public Employee testSql() \{  
  
  
        Employee employee=new Employee();  
        employee.setName("jack");  
        employee.setAge(22);  
  
  
        employee=employeeMapper.getEmployeeBy(employee);  
        return employee;

结果：

{"id":2,"name":"jack","salary":11000.0,"age":22}

上面的代码直接纯SQL编写了，可读性还是相对差了点，MyBatis提供了SQL类（org.apache.ibatis.jdbc.SQL），可以让代码看起来更有意义。

修改SqlProvide方法

public String select1(Employee employee)\{  
             return new SQL()\{ \{  
                 SELECT("\*");  
                 FROM("employee");  
                 if (employee.getName() != null)\{  
  
  
                     WHERE("name=\#\{name\}");  
  
  
                    \}  
  
  
                 if (employee.getAge() != null)\{  
                     WHERE("age=\#\{age\}");  
  
  
                    \}  
               \}\}.toString();

结果一样

{"id":2,"name":"jack","salary":11000.0,"age":22}

其余的参考：

http://412887952-qq-com.iteye.com/blog/2391924

[70]: /images/20220526/c584e57ae19743e098602f5172e558a1.png
[70 1]: /images/20220526/0702bfbbbac34167b305562b2f06a513.png
[70 2]: /images/20220526/1b295353be2d48599eb55e26700d3f91.png
[70 3]: /images/20220526/055a9833249a44afad69392cf8a1c382.png