Springboot整合Mybatis-阿里云开发者社区

MyBatis简单介绍

MyBatis是一个Java持久化框架,它通过XML描述符或注解把对象与存储过程或SQL语句关联起来。大部分都是比较灵活的，我们可以进行多表写比较复杂的查询sql,比较灵活。hibernate相对于来说比较固定，实体和表进行一一对应，比较固定。对于业务比较复杂的，选择MyBatis框架比较合适。

建表脚本

我们新建一个订单表来测试。

CREATE TABLE test.`order` (

order_id INT auto_increment NOT NULL primary key COMMENT '订单ID',

order_no INT NULL COMMENT '订单号',

product_id int NULL COMMENT '产品id',

user_id INT NULL COMMENT '用户ID',

order_num int comment '订单产品数量',

order_amt double comment '订单金额',

order_status varchar(10) NULL COMMENT '订单状态',

pay_status varchar(10) NULL COMMENT '支付状态',

create_time DATETIME NULL COMMENT '创建时间' default current_timestamp,

update_time DATETIME NULL COMMENT '更新时间' default current_timestamp,

create_user varchar(10) NULL COMMENT '创建人',

update_user varchar(10) NULL COMMENT '更新人'

)

ENGINE=InnoDB

DEFAULT CHARSET=utf8mb4

COLLATE=utf8mb4_0900_ai_ci

COMMENT='订单表';

需要的话，可以自己插入部分数据。

INSERT INTO test.`order`

(order_id, order_no, product_id, user_id, order_num, order_amt, order_status, pay_status, create_time, update_time, create_user, update_user)

VALUES(1, 1, 1, 1, 2, 0.00, '已发货', '支付完成', '2022-08-21 08:32:40', '2022-08-21 10:48:44', 'user1', 'user2');

INSERT INTO test.`order`

(order_id, order_no, product_id, user_id, order_num, order_amt, order_status, pay_status, create_time, update_time, create_user, update_user)

VALUES(3, 2, 2, 1, 2, 20.00, '取消下单', '未支付', '2022-08-21 11:20:29', '2022-08-21 11:20:29', 'annotation', 'annotation');

INSERT INTO test.`order`

(order_id, order_no, product_id, user_id, order_num, order_amt, order_status, pay_status, create_time, update_time, create_user, update_user)

VALUES(4, 4, 1, 1, 2, 20.00, '下单', '支付', '2022-08-21 11:25:09', '2022-08-21 11:25:09', 'annotation', 'annotation');

引入依赖

主要引入mybatis，mysql驱动以及druid数据源。

<groupId>org.mybatis.spring.boot</groupId>

<artifactId>mybatis-spring-boot-starter</artifactId>

</dependency>

<groupId>mysql</groupId>

<artifactId>mysql-connector-java</artifactId>

</dependency>

<groupId>com.alibaba</groupId>

<artifactId>druid</artifactId>

</dependency>

druid数据源

druid阿里的，可以方便我们进行监控。

配置类：

package com.elite.springboot.config;

import com.alibaba.druid.pool.DruidDataSource;

import com.alibaba.druid.support.http.StatViewServlet;

import com.alibaba.druid.support.http.WebStatFilter;

import org.springframework.boot.context.properties.ConfigurationProperties;

import org.springframework.boot.web.servlet.FilterRegistrationBean;

import org.springframework.boot.web.servlet.ServletRegistrationBean;

import org.springframework.context.annotation.Bean;

import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;

import java.util.Arrays;

import java.util.HashMap;

import java.util.Map;

//导入druid数据源

@Configuration

public class DruidConfig {

@ConfigurationProperties(prefix = "spring.datasource")

@Bean

public DataSource druid(){

return new DruidDataSource();

}

//配置Druid的监控

//1、配置一个管理后台的Servlet

@Bean

public ServletRegistrationBean statViewServlet(){

ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(),

"/druid/*");

Map<String,String> initParams = new HashMap<>();

initParams.put("loginUsername","admin");

initParams.put("loginPassword","123456");

initParams.put("allow","");//默认就是允许所有访问

//initParams.put("deny","192.168.15.21");

bean.setInitParameters(initParams);

return bean;

}

//2、配置一个web监控的filter

@Bean

public FilterRegistrationBean webStatFilter(){

FilterRegistrationBean bean = new FilterRegistrationBean();

bean.setFilter(new WebStatFilter());

Map<String,String> initParams = new HashMap<>();

initParams.put("exclusions","*.js,*.css,/druid/*");

bean.setInitParameters(initParams);

bean.setUrlPatterns(Arrays.asList("/*"));

return bean;

}

yml配置

server:

port: 8013

spring:

application:

name: SpringbootMybatis

#配置数据库

datasource:

type: com.alibaba.druid.pool.DruidDataSource

url: jdbc:mysql://数据库ip:3306/test

driver-class-name: com.mysql.jdbc.Driver

username: root

password: 123456

druid:

# 初始化大小，最小，最大

initialSize: 5

minIdle: 5

maxActive: 20

# 配置获取连接等待超时的时间(毫秒)

maxWait: 60000

# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒

timeBetweenEvictionRunsMillis: 60000

# 配置有一个连接在连接池中的最小生存时间，单位是毫秒

minEvictableIdleTimeMillis: 300000

validationQuery: SELECT 1 FROM DUAL

testWhileIdle: true

testOnBorrow: false

testOnReturn: false

# 打开PSCache，指定每个连接上PSCache的大小

poolPreparedStatements: true

maxPoolPreparedStatementPerConnectionSize: 20

# 配置监控统计拦截的filters，去掉后监控界面sql将无法统计，'wall'用于防火墙

filters: stat, wall, log4j

# 通过connectProperties属性来打开mergeSql功能，慢SQL记录

connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

#mybatis配置

mybatis:

#config‐location: classpath:mybatis‐config.xml #指定全局配置文件的位置

mapper‐locations: classpath:mapper/*.xml #指定sql映射文件的位置

实体类Order

package com.elite.springboot.entity;

import lombok.Data;

import lombok.Getter;

import lombok.Setter;

import java.math.BigDecimal;

import java.sql.Date;

/**

* 订单表

@Data

@Getter

@Setter

public class Order {

//订单ID

private Integer order_id;

//订单号

private Integer order_no;

//商品编号

private Integer product_id;

//用户

private Integer user_id;

//'订单产品数量'

private Integer order_num ;

//'订单金额'

private BigDecimal order_amt ;

//订单状态 0待确认，1已确认，2已收货，3已取消，4已完成，5已作废

private String order_status;

//支付状态 0待支付，1已支付，2部分支付，3已退款，4拒绝退款

private String pay_status;

//创建时间

private Date create_time;

//更新时间

private Date update_time;

//创建用户

private String create_user;

//更新用户

private String update_user;

}

MyBatis 注解和xml方式

XML配置方式

OrderMapper.xml

<?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">

SELECT order_id,

order_no,

product_id,

user_id,

order_num,

order_amt,

order_status,

pay_status,

create_time,

update_time,

create_user,

update_user

from `order` where order_id=#{order_id};

</select>

SELECT order_id,

order_no,

product_id,

user_id,

order_num,

order_amt,

order_status,

pay_status,

create_time,

update_time,

create_user,

update_user

from `order`

</select>

update `order`

<set>

<if test="order_no != null and order_no != ''">order_no = #{order_no},</if>

<if test="product_id != null and product_id != ''">product_id = #{product_id},</if>

<if test="order_num != null and order_num != ''">order_num = #{order_num},</if>

<if test="order_amt != null and order_amt != ''">order_amt = #{order_amt},</if>

<if test="order_status != null">order_status = #{order_status},</if>

<if test="pay_status != null">pay_status = #{pay_status},</if>

<if test="create_user != null and create_user != ''">create_user = #{create_user},</if>

<if test="update_user != null and update_user != ''">update_user = #{update_user},</if>

update_time =CURRENT_TIMESTAMP

</set>

where order_id = #{order_id}

</update>

DELETE FROM `order`

WHERE order_id= #{order_id};

</delete>

INSERT INTO `order`(`order_no`,

`product_id`,

`user_id`,

`order_num`,

`order_amt`,

`order_status`,

`pay_status`,

`create_user`,

`update_user`)

VALUES( #{order_no},

#{product_id},

#{user_id},

#{order_num},

#{order_amt},

#{order_status},

#{pay_status},

#{create_user},

#{update_user})

</insert>

</mapper>

OrderMapper.java接口类

package com.elite.springboot.mapper;

import com.elite.springboot.entity.Order;

import org.apache.ibatis.annotations.Mapper;

import java.util.List;

/**

* 科室映射类

//@Mapper

public interface OrderMapper {

//保存数据

void saveOrder(Order order);

//更新订单

void updateOrder(Order order);

//删除订单

void delOrder(Integer order_id);

//获取订单

Order getOneOrder(Integer order_id);

//获取所有的订单

List<Order> getAllOrder();

}

xml测试

package com.elite.springboot;

import com.elite.springboot.entity.Order;

import com.elite.springboot.mapper.OrderMapper;

import lombok.extern.slf4j.Slf4j;

import org.junit.Test;

import org.junit.runner.RunWith;

import org.springframework.boot.test.context.SpringBootTest;

import org.springframework.test.context.junit4.SpringRunner;

import javax.annotation.Resource;

import java.math.BigDecimal;

import java.util.List;

/**

* Unit test for simple App.

@Slf4j

@RunWith(SpringRunner.class)

@SpringBootTest

public class AppTest {

@Resource

OrderMapper orderMapper;

//保存数据

@Test

public void saveOrder(){

Order order = new Order();

order.setOrder_no(1);

order.setProduct_id(1);

order.setUser_id(1);

order.setOrder_num(2);

order.setOrder_amt(BigDecimal.valueOf(0));

order.setOrder_status("下单");

order.setPay_status("未支付");

order.setCreate_user("user1");

order.setUpdate_user("user2");

orderMapper.saveOrder(order);

}

//更新订单

@Test

public void updateOrder(){

Order order = new Order();

order.setOrder_id(1);

order.setOrder_status("已发货");

order.setPay_status("支付完成");

orderMapper.updateOrder(order);

}

//删除订单

@Test

public void delOrder(){

orderMapper.delOrder(2);

}

//获取订单

@Test

public void getOneOrder(){

Order oneOrder = orderMapper.getOneOrder(1);

System.out.println(oneOrder);

//Order(order_id=1, order_no=1, product_id=1, user_id=1, order_num=2,

// order_amt=0.00, order_status=已发货, pay_status=支付完成, create_time=2022-08-21, update_time=2022-08-21, create_user=user1, update_user=user2)

}

//获取所有的订单

@Test

public void getAllOrder(){

List<Order> orderList = orderMapper.getAllOrder();

for(Order order:orderList){

System.out.println(order);

}

//Order(order_id=1, order_no=1, product_id=1, user_id=1, order_num=2, order_amt=0.00, order_status=已发货, pay_status=支付完成, create_time=2022-08-21, update_time=2022-08-21, create_user=user1, update_user=user2)

//Order(order_id=2, order_no=2, product_id=2, user_id=1, order_num=2, order_amt=10.00, order_status=已发货, pay_status=支付, create_time=2022-08-21, update_time=2022-08-21, create_user=user1, update_user=user2)

}

注意事项

1.接口类的mapper名字和xml的名字一定要一致。

2.注意xml的命名空间，以及mapper类的方法名字和xml的方法id需要一样。

3.mapper类需要加上注解@Mapper或者在启动类加上@MapperScan(value = “com.elite.springboot.mapper”)

4.pom.xml中可能需要加入以下配置，编译的时候xml才会被打包。

<build>

<directory>src/main/resources</directory>

<include>**/*.properties</include>

</includes>

<filtering>false</filtering>

</resource>

</resources>

</build>

5.mysql版本的问题，xml中表明需要加上 ``,否则报语法错误。**

注解实现

接口类

package com.elite.springboot.mapper;

import com.elite.springboot.entity.Order;

import org.apache.ibatis.annotations.Delete;

import org.apache.ibatis.annotations.Insert;

import org.apache.ibatis.annotations.Select;

import org.apache.ibatis.annotations.Update;

import java.util.List;

/**

* 使用注解进行

public interface OrderAnnotationMapper {

//保存数据

@Insert("INSERT INTO `order`(`order_no`,`product_id`,`user_id`,`order_num`,`order_amt`,`order_status`,`pay_status`,`create_user`,`update_user`)\n" +

" VALUES( #{order_no},#{product_id},#{user_id},#{order_num},#{order_amt},#{order_status},#{pay_status},#{create_user},#{update_user})")

void insertOrder(Order order);

//更新订单

@Update("update `order` set order_status = #{order_status} where order_id= #{order_id}")

void updateOrderStatusById(Order order);

//删除订单

@Delete("DELETE FROM `order` WHERE order_id= #{order_id}")

void delOrderById(Integer order_id);

//获取订单

@Select("select * from `order` where order_id= #{order_id}")

Order getOneOrderById(Integer order_id);

//获取所有的订单

@Select("select * from `order`")

List<Order> getAllOrder();

}

测试

package com.elite.springboot;

import com.elite.springboot.entity.Order;

import com.elite.springboot.mapper.OrderAnnotationMapper;

import lombok.extern.slf4j.Slf4j;

import org.junit.Test;

import org.junit.runner.RunWith;

import org.springframework.boot.test.context.SpringBootTest;

import org.springframework.test.context.junit4.SpringRunner;

import javax.annotation.Resource;

import java.math.BigDecimal;

import java.util.List;

/**

* 注解sql测试

@Slf4j

@RunWith(SpringRunner.class)

@SpringBootTest

public class AppAnnotationTest {

@Resource

OrderAnnotationMapper orderAnnotationMapper;

/**

* 保存订单

@Test

public void InserOrder(){

Order order = new Order();

// order.setOrder_no(2);

// order.setProduct_id(2);

// order.setUser_id(1);

// order.setOrder_num(2);

// order.setOrder_amt(BigDecimal.valueOf(20));

// order.setOrder_status("下单");

// order.setPay_status("未支付");

// order.setCreate_user("annotation");

// order.setUpdate_user("annotation");

order.setOrder_no(4);

order.setProduct_id(1);

order.setUser_id(1);

order.setOrder_num(2);

order.setOrder_amt(BigDecimal.valueOf(20));

order.setOrder_status("下单");

order.setPay_status("支付");

order.setCreate_user("annotation");

order.setUpdate_user("annotation");

orderAnnotationMapper.insertOrder(order);

}

/**

* 更新订单状态

@Test

public void updateOrderStatusById(){

Order order = new Order();

order.setOrder_id(3);

order.setOrder_status("取消下单");

orderAnnotationMapper.updateOrderStatusById(order);

}

/**

* 查询订单

@Test

public void getOneOrderById(){

Order order = orderAnnotationMapper.getOneOrderById(3);

System.out.println(order);

//Order(order_id=3, order_no=2, product_id=2, user_id=1, order_num=2, order_amt=20.00, order_status=取消下单, pay_status=未支付, create_time=2022-08-21, update_time=2022-08-21, create_user=annotation, update_user=annotation)

}

/**

* 查询所有订单

@Test

public void getAllOrder(){

List<Order> allOrder = orderAnnotationMapper.getAllOrder();

for (Order order :allOrder){

System.out.println(order);

}

//Order(order_id=4, order_no=4, product_id=1, user_id=1, order_num=2, order_amt=20.00, order_status=下单, pay_status=支付, create_time=2022-08-21, update_time=2022-08-21, create_user=annotation, update_user=annotation)

//Order(order_id=5, order_no=4, product_id=1, user_id=1, order_num=2, order_amt=20.00, order_status=下单, pay_status=支付, create_time=2022-08-21, update_time=2022-08-21, create_user=annotation, update_user=annotation)

}

/**

*删除订单

@Test

public void delOrderById(){

orderAnnotationMapper.delOrderById(5);

}