Spring Boot + MyBatis + MySQL 整合(2)

上一篇 Spring Boot + MyBatis + MySQL 整合(1) http://mp.blog.csdn.net/postedit/79425758，已经介绍了如何使用注解来配置mybatis，这篇文章主要是介绍如何使用XML来对mybatis进行配置，整合步骤前4步和上一篇有点相似

整合步骤

1、在pom.xml文件里面引入 mybatis 和 mysql 的依赖

<!-- 添加 MyBatis -->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>1.3.0</version>
</dependency>
<!-- 添加 MySQL -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.41</version>
</dependency>

2、在application.yml文件里面对数据库进行相应的配置

spring:
  datasource:
      driver-class-name: com.mysql.jdbc.Driver
      username: root
      password: xxx
      url: jdbc:mysql://127.0.0.1/sell?characterEncoding=utf-8&useSSL=false

server:
  context-path: /demo
  port: 8088

4、为了mysql的测试，先在mysql中创建 product_category 表

create table `product_category` (
    `category_id` int not null auto_increment,
    `category_name` varchar(64) not null comment '类目名字',
    `category_type` int not null comment '类目编号',
   primary key (`category_id`)
);

4、在src里面新建一个mapper 文件夹，里面放置mapper.java等相关接口，新建CategoryMapper.java，里面写增删查改相关接口

public interface CategoryMapper {

    ProductCategory findByCategoryType(Integer categoryType);

    List<ProductCategory> findByCategoryName(String categoryName);

    //以map的方式插入
    int insertByMap(Map<String, Object> map);

    //以对象的方式插入
    int insertByObject(ProductCategory productCategory);

    //以对象的方式update
    int updateByObject(ProductCategory category);

    //删除
    int deleteByCategoryType(Integer categoryType);
}

5、在resource下新建一个mapper 文件夹，里面放置mapper.xml文件，新建CategoryMapper.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.mapper.CategoryMapper">
    <resultMap id="categoryResult" type="com.example.demo.entity.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>

    <select id="findByCategoryType" resultMap="categoryResult" parameterType="java.lang.Integer">
        select category_id,category_name,category_type
        from product_category
        where category_type = #{categoryType, jdbcType=INTEGER}
    </select>

    <select id="findByCategoryName" resultMap="categoryResult" parameterType="java.lang.String">
        select category_id,category_name,category_type
        from product_category
        where category_name = #{categoryName, jdbcType=INTEGER}
    </select>

    <insert id="insertByMap" parameterType="java.util.Map">
        insert into product_category(category_name,category_type) values (#{categoryName, jdbcType = VARCHAR},
        #{categoryType, jdbcType = INTEGER})
    </insert>

    <insert id="insertByObject" parameterType="com.example.demo.entity.ProductCategory">
        insert into product_category(category_name,category_type) values (#{categoryName, jdbcType = VARCHAR},
        #{categoryType, jdbcType = INTEGER})
    </insert>

    <update id="updateByObject" parameterType="com.example.demo.entity.ProductCategory">
        update product_category set category_name = (#{categoryName, jdbcType = VARCHAR}) where category_type = #{categoryType, jdbcType = INTEGER}
    </update>
    
    <delete id="deleteByCategoryType" parameterType="java.lang.Integer">
        delete from product_category where category_type = #{categoryType, jdbcType=INTEGER}
    </delete>
</mapper>

6、然后编写测试类CategoryMapperTest.java，看看是否测试通过

@RunWith(SpringRunner.class)
@SpringBootTest
public class CategoryMapperTest {

    @Autowired
    private CategoryMapper categoryMapper;
    @Test
    public void findByCategoryType() throws Exception {
        ProductCategory category=categoryMapper.findByCategoryType(17);
        Assert.assertNotNull(category);
    }
    @Test
    public void findByCategoryName() throws Exception {
        List<ProductCategory> categoryList=categoryMapper.findByCategoryName("我的最爱2");
        Assert.assertNotEquals(0,categoryList);
    }
    @Test
    public void insertByMap() throws Exception {
        Map<String, Object> map = new HashedMap();
        map.put("categoryName", "我的最爱2");
        map.put("categoryType", 24);
        int insertResult= categoryMapper.insertByMap(map);
        Assert.assertEquals(1,insertResult);
    }

    @Test
    public void insertByObject() throws Exception {
        ProductCategory category=new ProductCategory();
        category.setCategoryName("我的最爱2");
        category.setCategoryType(25);
        int insertResult= categoryMapper.insertByObject(category);
        Assert.assertEquals(1,insertResult);
    }

    @Test
    public void updateByObject() throws Exception {
        ProductCategory category=new ProductCategory();
        category.setCategoryName("我的最爱2");
        category.setCategoryType(23);
        int updateResult=categoryMapper.updateByObject(category);
        Assert.assertEquals(1,updateResult);
    }

    @Test
    public void deleteByCategoryType() throws Exception {
        int delResult= categoryMapper.deleteByCategoryType(22);
        Assert.assertEquals(1,delResult);
    }
}

7、其实到了这一步，mapper已经可以直接拿来用了，看到网上不少文章都是到了这一步就直接写controller了，但是，我们一般不这么写，虽然麻烦点，为了开发的规范，我们还是按照dao\service\controller这样的结构来继续完成吧，新建dao文件夹，用来存放dao，新建ProductCategoryDao.java(为了简洁点，这里只做select操作好了)

public class ProductCategoryDao {
//    @Autowired
//    private ProductCategoryMapper productCategoryMapper;
//
//    public List<ProductCategory> findByCategoryType(Integer type) {
//        return productCategoryMapper.findByCategoryType(type);
//    }
    @Autowired
    private CategoryMapper categoryMapper;

    public ProductCategory findByCategoryType(Integer type) {
        return categoryMapper.findByCategoryType(type);
    }
}

8、然后在service目录下，新建ProductCategoryService  和 ProductCategoryServiceImpl

public interface ProductCategoryService {
    ProductCategory findByCategoryType(Integer type);
}

@Service
public class ProductCategoryServiceImpl implements ProductCategoryService {
    @Autowired
    private CategoryMapper productCategoryMapper;

    @Override
    public ProductCategory findByCategoryType(Integer type) {
        return productCategoryMapper.findByCategoryType(type);
    }
}

9、controller和之前一样

@RestController
public class ProductCategoryController {
    @Autowired
    private ProductCategoryService categoryService;

    @GetMapping("db/findByCategoryType/{type}")
    public ResultVO getCategory(@PathVariable("type") Integer type) {
        return ResultVOUtil.success(categoryService.findByCategoryType(type));
    }
}

10、在浏览器上请求这个API：  http://localhost:8087/demo/db/findByCategoryType/17  

得到结果

{"code": 0,"msg": "success","data": {"categoryName": "我讨厌的","categoryType": 17,"categoryId": 8}}