The first project

Creating a Maven project

pom

      
<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>springbbot</groupId>
    <artifactId>mybatisannotation</artifactId>
    <version>1.0 the SNAPSHOT</version>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.2.2. RELEASE</version>
    </parent>

    <dependencies>

        <! --springMVC-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <! --mybatis-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.1</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jdbc</artifactId>
        </dependency>

        <! ---->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.38</version>
        </dependency>


    </dependencies>

</project>
Copy the code

Yml file

mybatis:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
# Development configuration
spring:
  datasource:
    url: jdbc:mysql://localhost:3306/test3? characterEncoding=utf-8
    username: root
    password: 123456
    driver-class-name: com.mysql.jdbc.Driver
Copy the code

Start the class

@SpringBootApplication
// Scan the Mapper interface
@MapperScan(basePackages={"com.mapper"})
public class App {
    public static void main(String[] args) { SpringApplication.run(App.class,args); }}Copy the code

The mapper class

package com.mapper;
import org.apache.ibatis.annotations.Select;
import java.util.List;
import java.util.Map;
public interface UserMapper {
    @Select("select id,userName from user")
    public List<Map<String,Object>> getMaps();
}
Copy the code

controller

package com.controller;
import com.mapper.UserMapper;
import org.springframework.web.bind.annotation.RestController;
import javax.annotation.Resource;
import java.util.List;
import java.util.Map;
@RestController
public class UserController {
    @Resource
    UserMapper userMapper;
    @RequestMapping("/getUser")
    public List<Map<String,Object>> getUsers(){
        List<Map<String, Object>> maps = userMapper.getMaps();
        returnmaps; }}Copy the code

Other cases

Return Map(one parameter)

public interface UserMapper {
    // If you pass in only one argument, #{
    @Select("select id,userName,password from user where id = #{id}")
    public Map<String,Object> getMap(Integer id);
}
Copy the code

@Resource
UserMapper userMapper;
@RequestMapping("/getUser")
public Map<String,Object> getUser(){
    Map<String, Object> map = userMapper.getMap(1);
    return map;
}
Copy the code

Return Map(multiple parameters)

@select (" Select id,userName,password from user where id = #{id} ") and userName = #{userName}") public Map<String,Object> getMap(Integer id,String userName); }Copy the code

@Resource
UserMapper userMapper;

@RequestMapping("/getUser")
public Map<String,Object> getUser(a){
    Map<String, Object> map = userMapper.getMap(1."zhangsan");
    return map;
}
Copy the code

Return List Map

@Resource
UserMapper userMapper;

@RequestMapping("/getUsers")
public List<Map<String,Object>> getUsers(){
    List<Map<String, Object>> maps = userMapper.getMaps();
    return maps;
}
Copy the code

public interface UserMapper {
    @Select("select id,userName from user")
    public List<Map<String,Object>> getMaps();
}
Copy the code

Returns the Entity

public interface UserMapper {
 @Select
 ("select id,userName,password from user where id = #{id} and userName = #{userName}")
 public User getUser(Integer id, String userName);
}
Copy the code

@Resource
UserMapper userMapper;
@RequestMapping("/getUser")
public User getUser(a){
    User user = userMapper.getUser(1."zhangsan");
    return user;
}
Copy the code

Return List Entity

public interface UserMapper {
    @Select("select id,userName from user")
    public List<User> getUsers(a);
}
Copy the code

@Resource
UserMapper userMapper;

@RequestMapping("/getUsers")
public List<User> getUsers(a){
    List<User> users = userMapper.getUsers();
    return users;
}
Copy the code

Resuts annotations

Pass parameters to the statement

The Map

public interface UserMapper {
    @Select("select * from user where id = #{id}")
    public User getUser(Map map);
}
Copy the code

@Resource
UserMapper userMapper;
@RequestMapping("/getUser")
public User aaa(a){
    Map map = new HashMap();
    map.put("id".1);
    userMapper.getUser(map);
    return  null;
}
Copy the code

The Entity

@Select("select * from user where id = #{id}")  //id
public User getUser2(User user);
Copy the code

@RequestMapping("/getUser2")
public User getUser2(a){
    User user = new User();
    user.setId(1);
    User user2 = userMapper.getUser2(user);
    return  user2;
}
Copy the code

To obtain parameters

Get a single parameter

public interface UserMapper {
    // If you pass in only one argument, #{
    @Select("select id,userName,password from user where id = #{id}")
    public Map<String,Object> getMap(Integer id);
}
Copy the code

Get parameters by index

@Select("select * from user where id = #{param1} and userName=#{param2}")  //id
public User getUser3(Integer id,String userName);
Copy the code

Get parameters with annotations (plain parameters)

@Select("select * from user where id = #{a} and userName=#{b}")  //id
public User getUser3(@Param("a") Integer id, @Param("b")String userName);
Copy the code

Get parameters through annotations (object parameters)

@Select("select * from user where id = #{abc.id}")  //id
public User getUser2(@Param("abc") User user);
Copy the code

Get parameters through annotations (Map parameters)

    @Select("select * from user where id = #{a.id}")
    public User getUser(@Param("a") Map map);

Copy the code

@Resource
UserMapper userMapper;
@RequestMapping("/getUser")
public User aaa(a){
    Map map = new HashMap();
    map.put("id".1);
    User user = userMapper.getUser(map);
    return  user;
}
Copy the code

Note the use of Results

    @Select("select id,userName,password from user where id = #{a.id}")
    @Results({ @Result(property = "id2",column = "id"), @Result(property = "userName2",column = "userName"), @Result(property = "password2",column = "passwords") })
    public User2 getUser2(Integer id);
Copy the code

public class User2 {
    private Integer id2;
    private String userName2;
    private  String password2;

    public Integer getId2(a) {
        return id2;
    }
    public void setId2(Integer id2) {
        this.id2 = id2;
    }

    public String getUserName2(a) {
        return userName2;
    }

    public void setUserName2(String userName2) {
        this.userName2 = userName2;
    }

    public String getPassword2(a) {
        return password2;
    }

    public void setPassword2(String password2) {
        this.password2 = password2;
    }

    @Override
    public String toString(a) {
        return "User2{" +
                "id2=" + id2 +
                ", userName2='" + userName2 + '\' ' +
                ", password2='" + password2 + '\' ' +
                '} '; }}Copy the code

More than a pair of

sql

/*
MySQL Data Transfer
Source Host: localhost
Source Database: test3
Target Host: localhost
Target Database: test3
Date: 2020/9/30 13:53:18
*/

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for dept
-- ----------------------------
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
  `deptno` bigint(11) NOT NULL auto_increment,
  `dname` char(255) default NULL.PRIMARY KEY  (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for emp
-- ----------------------------
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
  `empno` bigint(11) NOT NULL,
  `ename` char(255) default NULL,
  `deptno` bigint(11) default NULL.PRIMARY KEY  (`empno`),
  KEY `deptno` (`deptno`),
  CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`deptno`) REFERENCES `dept` (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records 
-- ----------------------------
INSERT INTO `dept` VALUES ('10'.'Personnel Department');
INSERT INTO `dept` VALUES ('20'.'Finance Department');
INSERT INTO `dept` VALUES ('30'.'Technology');
INSERT INTO `dept` VALUES ('40'.Administration Department);
INSERT INTO `emp` VALUES ('7000'.'ename2'.'10');
INSERT INTO `emp` VALUES ('7039'.'ename2'.'10');
INSERT INTO `emp` VALUES ('7051'.'123123'.'20');
INSERT INTO `emp` VALUES ('1599207548262'.'123123'.'20');
INSERT INTO `emp` VALUES ('1599633921514'.'aaa'.'20');

Copy the code

Entity class

public class Emp {
    private Integer empno;
    privateString ename; Dept dept; . }Copy the code

public class Dept {
    private  Integer deptno;
    private String dname;
    List<Emp> emps;
}
Copy the code

mapper

public interface EmpMapper {
    @Select("SELECT * FROM emp WHERE empno = #{empno}")
    @Results({ @Result(property = "dept", column = "deptno", one = @One(select = "com.mapper.DeptMapper.getDept")) })
    Emp getEmp(Integer empno);


    @Select("SELECT * FROM emp WHERE deptno = #{deptno}")
    List<Emp> getEmps(Integer deptno);

    / * * *@Results({
     @Result(property = "dept", column = "deptno",
     one = @One(select = "com.mapper.DeptMapper.getDept"))
     */
}

Copy the code

public interface DeptMapper {
    @Select("SELECT * FROM dept where deptno = #{deptno}")
    Dept getDept(Integer deptno);


    @Select("SELECT * FROM dept where deptno = #{deptno}")
    @Results({ @Result(property = "emps", column = "deptno", many = @Many(select = "com.mapper.EmpMapper.getEmps",fetchType = FetchType.EAGER)) })
    Dept getDept2(Integer deptno);
}
Copy the code

Many to many

Table SQL

/*
MySQL Data Transfer
Source Host: localhost
Source Database: test8
Target Host: localhost
Target Database: test8
Date: 2020/9/30 13:44:55
*/

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `couId` int(11) NOT NULL auto_increment,
  `couName` varchar(255) default NULL.PRIMARY KEY  (`couId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for stucou
-- ----------------------------
DROP TABLE IF EXISTS `stucou`;
CREATE TABLE `stucou` (
  `stuId` int(11) default NULL,
  `couId` int(11) default NULL,
  KEY `stuId` (`stuId`),
  KEY `couId` (`couId`),
  CONSTRAINT `stucou_ibfk_2` FOREIGN KEY (`couId`) REFERENCES `course` (`couId`),
  CONSTRAINT `stucou_ibfk_1` FOREIGN KEY (`stuId`) REFERENCES `student` (`stuId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `stuId` int(11) NOT NULL auto_increment,
  `stuName` varchar(255) default NULL.PRIMARY KEY  (`stuId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records 
-- ----------------------------
INSERT INTO `course` VALUES ('1'.'courseName1');
INSERT INTO `course` VALUES ('2'.'courseName2');
INSERT INTO `course` VALUES ('3'.'courseName3');
INSERT INTO `course` VALUES ('4'.'courseName4');
INSERT INTO `course` VALUES ('5'.'courseName5');
INSERT INTO `course` VALUES ('6'.'courseName6');
INSERT INTO `stucou` VALUES ('1'.'1');
INSERT INTO `stucou` VALUES ('1'.'2');
INSERT INTO `stucou` VALUES ('1'.'3');
INSERT INTO `stucou` VALUES ('2'.'1');
INSERT INTO `stucou` VALUES ('2'.'2');
INSERT INTO `stucou` VALUES ('3'.'1');
INSERT INTO `student` VALUES ('1'.'StudentName1');
INSERT INTO `student` VALUES ('2'.'StudentName2');
INSERT INTO `student` VALUES ('3'.'StudentName3');
INSERT INTO `student` VALUES ('4'.'StudentName4');
INSERT INTO `student` VALUES ('5'.'StudentName5');
INSERT INTO `student` VALUES ('6'.'StudentName6');

Copy the code

Entity class

public class Student {
    private int stuId;
    private String stuName;
    privateList<Course> courses; . }Copy the code

public class Course {
    private int couId;
    private String couName;
    privateList<Student> students; . }Copy the code

Mapper interfaces

public interface CourseMapper {
    @Select("select * from Course where couId in(select couId from stuCou where stuId=#{stuId})")
    public List<Course> selectCourseBy(int stuId);

    @Select("select * from Course")
    @Results({ @Result(id = true,property = "couId",column = "couId"), @Result(property = "students",column = "couId",many = @Many (select = "com.mapper.StudentMapper.selectStudentBy")) })
    public List<Course> allCourse(a);

}
Copy the code

public interface StudentMapper {

    @Select("select * from Student")
    @Results({ @Result(id = true,property = "stuId",column = "stuId"), @Result(property = "courses",column = "stuId",many = @Many (select = "com.mapper.CourseMapper.selectCourseBy")) })
    public List<Student> allStudent(a);

    @Select
     ("select * from student where stuId in(select stuId from stuCou where couId=#{couId})")
    public List<Student> selectStudentBy(int couId);

}
Copy the code

test

@Resource
CourseMapper courseMapper;
@Resource
StudentMapper studentMapper;
@RequestMapping("/testCourse")
public void testCourse(a) {
    for (Course course : courseMapper.allCourse()) {
        System.out.println(course.getCouId() + "\t" + course.getCouName());
        for (Student student : course.getStudents()) {
            System.out.println(student.getStuId() + "\t"+ student.getStuName()); } System.out.println(); }}@RequestMapping("/testStudent")
public void testStudent(a) {

    for (Student student : studentMapper.allStudent()) {
        System.out.println(student.getStuId() + "\t" + student.getStuName());
        for (Course course : student.getCourses()) {
            System.out.println(course.getCouId() + "\t"+ course.getCouName()); } System.out.println(); }}Copy the code

The use of SelectProvider

package mapper;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.SelectProvider;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.List;
import java.util.Map;
import java.util.Set;

public interface AbstractMapper<T> {

    @SelectProvider(type = Sql.class, method = "getSql")
    public List<T> getEntitys(@Param("param1") T entity);



    @SelectProvider(type = Sql.class, method = "getSql")
    public T getEntity(@Param("param1") T entity);

    @SelectProvider(type = Sql.class, method = "getSql")
    public List<T> getEntitysByPageNow(@Param("param1")
                                            T entity,
                                    @Param("param2")
                                            Integer pageNow,
                                    @Param("param3")
                                            Integer pageSize);


    public class Sql {
        public String getSql(Map param) {
            // reflection gets class information
            Object entity = param.get("param1"); Class<? > entityClass = entity.getClass(); String simpleName = entityClass.getSimpleName(); String TABLENAME = simpleName.toUpperCase(); Method[] declaredMethods = entityClass.getDeclaredMethods(); String columns ="";
            String condition = "";
            String conditionValue = "";


            // Get attribute and method information
            for (Method declaredMethod : declaredMethods) {
                String methosName = declaredMethod.getName();
                System.out.println(methosName.indexOf("get"));
                if (methosName.indexOf("get") != -1) {
                    // Get the get method
                    String column = methosName.replace("get"."").toUpperCase();
                    System.out.println(column);
                    columns += column + ",";
                    Object returnValue = invodGetMethod(entity, declaredMethod);
                    if(returnValue ! =null) {
                        condition += column + "=#{param1." + getPropName(methosName) + "} and ";
                    }
                }
            }

            columns = columns.substring(0, columns.length() - 1);

            // Concatenate statements
            final String SELECT = " SELECT ";
            final String FROM = " FROM ";
            final String WHERE = " WHERE ";
            String sql = SELECT + columns + FROM + TABLENAME;


            // Check whether there is a condition
            if(! condition.equals("")){

                condition = condition.substring(0, condition.lastIndexOf("and "));

                sql +=  WHERE + condition;
            }
            // Check whether paging is available
            if (param.getOrDefault("param2".null) != null) {
                / / the current page
                int pagenow = Integer.parseInt(param.get("param2").toString());
                / / how many pages
                int pagesize = Integer.parseInt(param.get("param3").toString());

                Integer start = (pagenow-1)*pagesize;
                sql += " limit "+start+","+pagesize;

            }
            System.out.println(sql);


            return sql;
        }

        public String getPropName(String getName) {
            String get = getName.replace("get"."");
            String propName = get.replace(get.charAt(0), Character.toLowerCase(get.charAt(0)));
            return propName;
        }

        Object invodGetMethod(Object obj, Method method) {
            Object invoke = null;
            try {
                invoke = method.invoke(obj, null);
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            } catch (InvocationTargetException e) {
                e.printStackTrace();
            }
            returninvoke; }}}Copy the code

The cache

Mybatis cache

Level 1 cache

Session level cache

The second level cache

@CacheNamespace
public interface UserMapper {
    @Select("select id from user")
    public List<Map> getMaps(a);
}
Copy the code

For additional articles please refer to juejin.cn/user/175884…