springboot集成mybatis实现crud，并简单介绍mybatis的xml配置

mybatis无法像jpa一样自动建表，表需提前建好

添加依赖

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>
<!--mybatis-->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
</dependency>

application.yml配置

spring:
  datasource:
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/xxxx?characterEncoding=utf8&useSSL=true
    username: xxxx
    password: xxxxxxx

mapper配置类

public interface Mapper {

    //通过map插入
    @Insert("insert into product_category(category_name,category_type) values (#{category_name,jdbcType=VARCHAR},#{category_type,jdbcType=INTEGER})")
    int insertByMap(Map<String, Object> map);//写进去返回1，没有写进去返回0


    //通过对象插入
    @Insert("insert into product_category(category_name,category_type) values (#{categoryName,jdbcType=VARCHAR},#{categoryType,jdbcType=INTEGER})")
    int insertByObject(ProductCategory productCategory);


    //写几个@Result就查出几个结果，column = 必须与数据库列名保持一致
    @Select("select * from product_category where category_type=#{categoryType}")
    @Results({@Result(column = "category_type", property = "categoryType"), @Result(column = "category_name", property = "categoryName"), @Result(column = "category_id", property = "categoryId")

    })
    List<ProductCategory> findByCategoryType(Integer categoryType);


    //通过某一个字段更新
    //更新所有category_type相同的
    @Update("update product_category set category_name=#{categoryName} where category_type=#{categoryType} ")
    int updateByCategoryType(@Param("categoryName") String categoryName,@Param("categoryType") Integer categoryType);

    //通过对象更新
    //若有多个相同categoryType会全改
    @Update("update product_category set category_name=#{categoryName} where category_type=#{categoryType} ")
    int updateObject(ProductCategory productCategory);


    //通过字段删除
    @Delete("delete from product_category where category_type=#{categoryType}")
    int deleteByCategoryType(Integer categoryType);


    //通过配置ProductCatagoryMapper.xml实现crud
    //记得在application.yml配置，让程序找到ProductCatagoryMapper.xml
    //不推荐此方法，用上面的方法
    ProductCategory selectByCategoryType(Integer categoryType);
}

domain类：

@Data
public class ProductCategory {
    private Integer categoryId;
    private String categoryName;
    private Integer categoryType;
    private Date createTime;
    private Date updateTime;

    public ProductCategory() {

    }

    public ProductCategory(String categoryName, Integer categoryType) {
        this.categoryName = categoryName;
        this.categoryType = categoryType;
    }
}

记得在application程序上添加@MapperScan(basePackages = "com.example.demo.mybatis.mapper")注解，basePackages后是Mapper的路径

 

使用xml的方式使用mybatis：

ProductCatagoryMapper.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">
<mapper namespace="com.example.demo.mybatis.mapper.ProductCatagoryMapper">

    <!--要返回的结果  type是ProductCategory的路径-->
<resultMap id="BaseResultMap" type="com.example.demo.mybatis.domain.ProductCategory">
    <id column="category_id" property="categoryId" jdbcType="INTEGER" />
    <id column="category_name" property="categoryName" jdbcType="VARCHAR" />
    <id column="category_type" property="categoryType" jdbcType="INTEGER" />
</resultMap>

    <!--写一个查询的方法  parameterType是传参类型-->
    <select id="selectByCategoryType"  resultMap="BaseResultMap" parameterType="java.lang.Integer">
        select category_id,category_name,category_type
        from product_category
        where category_type=#{category_type,jdbcType=INTEGER}
    </select>
</mapper>

 

为了程序能找到xml，还要在application.yml添加如下配置：

mybatis:
  mapper-locations: classpath:mapper/*.xml

sql建表：

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `product_category`;
CREATE TABLE `product_category` (
  `category_id` int(11) NOT NULL AUTO_INCREMENT,
  `category_name` varchar(255) DEFAULT NULL,
  `category_type` int(11) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;