application.properties

数据库是lyr
端口是4711(一定要看端口是啥，否则死活连接不上。我花了半天的时间才搞定)

server.port=8081
## sqlserver
spring.datasource.url=jdbc:sqlserver://127.0.0.1:4711;databaseName=lyr
spring.datasource.username=admin
spring.datasource.password=123456
spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver

实体类

根据实体类可以知道实体类的三个属性都是varchar。

package com.lyr.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class People {
    private String id;
    private String name;
    private String email;
}

数据库表的设计

dao

package com.lyr.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.lyr.entity.People;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Component;
import java.util.List;

@Component
@Mapper
public interface PeopleDao extends BaseMapper<People> {

    List<People> selectByEmail();

    People findPersonById(String id);

}

service和impl

service

package com.lyr.service;

import com.baomidou.mybatisplus.extension.service.IService;
import com.lyr.entity.People;

import java.util.List;
import java.util.Optional;

public interface PeopleService extends IService<People> {

    List<People> getPersonByEmail();

    Optional<People> findPersonById(String id);

}

impl

package com.lyr.service.impl;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.google.common.collect.Lists;
import com.lyr.dao.PeopleDao;
import com.lyr.entity.People;
import com.lyr.service.PeopleService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import java.util.List;
import java.util.Optional;

@Service
public class PeopleServiceImpl extends ServiceImpl<PeopleDao, People> implements PeopleService {

    @Autowired
    private PeopleDao peopleDao;

    @Override
    public List<People> getPersonByEmail() {
        List<People> peopleList = peopleDao.selectByEmail();
        if(CollectionUtils.isEmpty(peopleList)){
            return Lists.newArrayList();
        }
        return peopleList;
    }

    @Override
    public Optional<People> findPersonById(String id) {
        People people = peopleDao.findPersonById(id);
        return Optional.ofNullable(people);
    }
}

运行类

package com.lyr;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

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

与打包成war包相关的一个类

package com.lyr;

import org.springframework.boot.builder.SpringApplicationBuilder;
import org.springframework.boot.web.servlet.support.SpringBootServletInitializer;

public class ServletInitializer extends SpringBootServletInitializer {

    @Override
    protected SpringApplicationBuilder configure(SpringApplicationBuilder application) {
        return application.sources(RunApplication.class);
    }

}

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.lyr.dao.PeopleDao">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.lyr.entity.People">
        <id column="id" property="id" />
        <result column="name" property="name" />
        <result column="email" property="email" />
    </resultMap>

    <select id="selectByEmail" resultType="com.lyr.entity.People">
        select
        id,name,email
        from people where email like ‘%com%‘;
    </select>

    <select id="findPersonById" resultType="com.lyr.entity.People">
        select id,name,email
        from people where id = #{id};
    </select>

</mapper>

pom.xml文件

<?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>com.lyr</groupId>
    <artifactId>springboot-test</artifactId>
    <version>1.0-SNAPSHOT</version>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.2.6.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <dependencies>

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

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

        <!--lombok-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>

        <!--Mybatis-Plus依赖-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.3.2</version>
        </dependency>
        <!--Mybatis-Plus代码生成器-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-generator</artifactId>
            <version>3.3.2</version>
        </dependency>

        <!--for SqlServer-->
        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>sqljdbc4</artifactId>
            <version>4.0</version>
        </dependency>
        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>mssql-jdbc</artifactId>
            <version>8.1.0.jre8-preview</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

    </dependencies>

    <build>
        <resources>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.yml</include>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>false</filtering>
            </resource>
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/*.yml</include>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>false</filtering>
            </resource>
        </resources>
    </build>

</project>

使用Controller进行测试

package com.lyr.controller;

import com.lyr.entity.People;
import com.lyr.entity.Student;
import com.lyr.service.PeopleService;
import com.lyr.service.StudentService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.MediaType;
import org.springframework.http.converter.HttpMessageConverter;
import org.springframework.http.converter.json.MappingJackson2HttpMessageConverter;
import org.springframework.stereotype.Controller;
import org.springframework.stereotype.Repository;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.client.RestTemplate;

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

@Controller
public class TestController {
    @Autowired
    private PeopleService peopleService;

    @GetMapping("/getp")
    @ResponseBody
    public Optional<People> getp(){
        Optional<People> people = peopleService.findPersonById("1");
        return people;
    }

}

项目结构图

规范说明：需要在resources新建一个mapper文件夹，里面放xml文件。这样子SpringBoot能够自动读取到xml文件。

005-SpringBoot整合MyBatis-plus，SQLServer