tkmybatis 分页条件查询 自定义语句

比较简单的tkmybatis使用入门，配置比较少，可以直接用。

数据：

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for test
-- ----------------------------
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of test
-- ----------------------------
INSERT INTO `test` VALUES ('1', 'hts', '123456');
INSERT INTO `test` VALUES ('2', 'xj', '123456');
INSERT INTO `test` VALUES ('3', 'zx', '123456');

配置

server:
  port: 8080

spring:
  datasource:
    username: xxx
    password: xxx
    url: jdbc:mysql://xxx:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false&autoReconnect=true&failOverReadOnly=false
    driver-class-name: com.mysql.jdbc.Driver

mybatis:
  mapper-locations: classpath:mapping/*Mapper.xml
  type-aliases-package: com.example.entity

#showSql
logging:
  level:
    com:
      example:
        mapper : debug

pom

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.example</groupId>
    <artifactId>mybatis</artifactId>
    <version>1.0-SNAPSHOT</version>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.5.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <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.3.2</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>tk.mybatis</groupId>
            <artifactId>mapper-spring-boot-starter</artifactId>
            <version>2.0.2</version>
        </dependency>
        <dependency>
            <groupId>tk.mybatis</groupId>
            <artifactId>mapper</artifactId>
            <version>4.0.4</version>
        </dependency>
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.2.3</version>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

控制层

下面的四个方法分别是根据id查询、简单的条件查询(查询出id大于、按用户名排序)、分页查询和自定义语句查询。

package com.example.controller;

import com.example.entity.User;
import com.example.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.web.bind.annotation.*;
import java.util.List;

@RestController
@RequestMapping("/user")
public class UserController {

    @Autowired
    private UserService userService;

    @RequestMapping("getUser/{id}")
    public String GetUser(@PathVariable int id){
        return userService.Sel(id).toString();
    }

    @RequestMapping("getUserByName/{username}")
    public String GetUserByName(@PathVariable String username){
        return userService.queryByName(username).toString();
    }

    @PostMapping("getUserByPage")
    public List<User> getUserByPage(@RequestParam int page, @RequestParam int size){
        return userService.queryByPage(page,size).getList();
    }

    @PostMapping("getUserByMyself")
    public List<User> getUserByMyself(@RequestParam int range){
        return userService.getUserByMyself(range);
    }
}

服务层

package com.example.service;

import com.example.entity.User;
import com.example.mapper.UserMapper;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import tk.mybatis.mapper.entity.Example;
import java.util.List;

@Service
public class UserService {
    @Autowired
    UserMapper userMapper;

    public User Sel(int id) {
        return (User) userMapper.selectByPrimaryKey(id);
    }

    public List<User> queryByName(String name) {
        Example example = new Example(User.class);
        example.createCriteria().andGreaterThanOrEqualTo("id", 1);
        example.setOrderByClause("username asc");
        return (List<User>) userMapper.selectByExample(example);
    }

    public PageInfo<User> queryByPage(int page, int size) {
        PageHelper.startPage(page,size);
        return new PageInfo<User>(userMapper.selectAll());
    }

    public List<User> getUserByMyself(int range) {
        return userMapper.getUserByMyself(range);
    }
}

如果想实现分页加条件查询，还可以：

        PageHelper.startPage(page,size);
        Example example = new Example(User.class);
        example.createCriteria().andGreaterThanOrEqualTo("id", 1);
        example.setOrderByClause("username asc");
        return new PageInfo<User>(userMapper.selectByExample(example));

mapper层

package com.example.mapper;

import com.example.entity.User;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;
import tk.mybatis.mapper.common.Mapper;
import java.util.List;

@Repository
public interface UserMapper extends Mapper<User> {


    @Select("select * from test where id>#{range}")
    List<User> getUserByMyself(int range);
}

domain(省略get set)

package com.example.entity;

import javax.persistence.Column;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;

@Table(name="test")//设置数据库中表名字
public class User {
    @Id
    @Column(name = "id")
    @GeneratedValue(generator = "JDBC")
    private Integer id;
    @Column(name = "username")
    private String username;
    @Column(name = "password")
    private String password;

启动类

注意mapperscan不要导错

package com.example;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import tk.mybatis.spring.annotation.MapperScan;

@MapperScan(basePackages = {"com.example.mapper"})
@SpringBootApplication
public class DemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(DemoApplication.class, args);
    }
}

可以进一步参考
https://www.cnblogs.com/alimayun/p/12572350.html
https://www.cnblogs.com/guoyafenghome/p/9123442.html