Learning back-end fast food for all (3) - accessing database with MyBatis

In the last section, we learned the JdbcTemplate to access the database, which is much more convenient than using the original JDBC. At least you don't have to deal with a lot of exceptions.
But we can still use some frameworks to make the work less. The main choice is MyBatis

About MyBatis

MyBatis was developed by iBatis. iBatis was first developed by Clinton Begin in 2001 and later donated to the Apache foundation in 2004.
In 2010, the iBatis team decided to separate from the Apache project and rename it myBatis

MyBatis is a persistence layer framework that supports SQL, stored procedures and advanced mapping.

Accessing database with MyBatis

Unfortunately, myBatis is not officially supported by Spring Boot, but the myBatis team has provided its own integration with Spring Boot.

Reference MyBatis

First, we introduce the dependency that works with Spring Boot.

        <!-- MyBatis -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>

Mapper

MyBatis uses Mapper to map SQL statements to function calls.
Let's write two SQL statements for the query. Replace the parameter with {variable name}:

package cn.alios.system.service.prefix;

import cn.alios.system.service.prefix.pojo.Issue;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

@Mapper
public interface IssueMapper {
    @Select("select id, filename, linenum, issuestring from issue where id = #{id};")
    Issue findIssue(Long id);

    @Select("select id, filename, linenum, issuestring from issue where filename like concat('%', #{filename}, '%');")
    Issue findIssueByFileName(String filename);
}

Calling Mapper to implement query

Let's use @ Autowired to automatically assemble the issueMapper:

    @Autowired
    IssueMapper issueMapper = null;

Then call it:

    @RequestMapping("/findbyid")
    @ResponseBody
    public String testFindById() {
        Issue issue = issueMapper.findIssue((long) 1);
        if (issue != null) {
            return issue.getFilename() + "," + issue.getIssuestring()+ ":" + issue.getLinenum();
        } else {
            return "Test MyBatis failed!";
        }
    }

Let's look at the complete Controller code:

package cn.alios.system.service.prefix.controller;

import cn.alios.system.service.prefix.IssueMapper;
import cn.alios.system.service.prefix.pojo.Issue;
import cn.alios.system.service.prefix.service.JdbcTemplateIssueServiceImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

@Controller
@RequestMapping("/test")
public class TestController {
    @Autowired
    JdbcTemplateIssueServiceImpl jdbcTemplateIssueService = null;

    @Autowired
    IssueMapper issueMapper = null;

    @RequestMapping("/")
    @ResponseBody
    public String test() {
        Issue issue = jdbcTemplateIssueService.getIssue((long) 1);
        if (issue != null) {
            return issue.getFilename() + "," + issue.getIssuestring();
        } else {
            return "Test Controller!";
        }
    }

    @RequestMapping("/findbyid")
    @ResponseBody
    public String testFindById() {
        Issue issue = issueMapper.findIssue((long) 1);
        if (issue != null) {
            return issue.getFilename() + "," + issue.getIssuestring()+ ":" + issue.getLinenum();
        } else {
            return "Test MyBatis failed!";
        }
    }

    @RequestMapping("/findbyfilename")
    @ResponseBody
    public String testFindByFileName() {
        Issue issue = issueMapper.findIssueByFileName("test");
        if (issue != null) {
            return issue.getFilename() + "," + issue.getIssuestring()+ ":" + issue.getLinenum();
        } else {
            return "Test MyBatis failed!";
        }
    }
}

Test it, mvn package, and run it.
Test it in browser: http://127.0.0.1:8080/test/findbyfilename.
The output is as follows: test.java,test:1