springboot integration mybatis-plus connection sqlserver

Here's a story about automating the generation of sqlserver code classes through the mybatis generator class, which is integrated through springboot.Summary of connections to sqlserver.

1. Create a springboot project

The development tool chooses idea or creates a springboot web project by quickly creating a springboot project.

Next, just check out the necessary packages, and the rest are ready to be introduced manually.

2. Edit pom.xml

maven's project is that good, edit Pom.xml directly to manage packages
Because you are connecting to sqlserver, the driver for importing sqlserver is new because it connects to a version of sqlserver2012 or later.

 <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>sqljdbc4</artifactId>
            <version>4.2</version>
            <scope>runtime</scope>
        </dependency>

To connect to the database using mybatis-plus, import mybatis-plus

 <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.1.2</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-generator</artifactId>
            <version>3.1.2</version>
        </dependency>

Choose database connection pool too, use druid

  <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>

Other things like hot deployment and log processing need to be integrated.The complete pom.xml is as follows:

<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.7.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.xyzh.mybatisPlus</groupId>
    <artifactId>demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>demo</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

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

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>mssql-jdbc</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>sqljdbc4</artifactId>
            <version>4.2</version>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.1.2</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-generator</artifactId>
            <version>3.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.freemarker</groupId>
            <artifactId>freemarker</artifactId>
        </dependency>
        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-core</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>springloaded</artifactId>
            <version>1.2.8.RELEASE</version>
        </dependency>
    </dependencies>

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

</project>

3. Profile settings

mybatis-plus:
  mapper-locations: classpath:/mapper/*.xml
  global-config:
    db-config:
      id-type: auto
      table-underline: true
      logic-not-delete-value: 0
      logic-delete-value: 1
spring:
  datasource:
    driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
    type: com.alibaba.druid.pool.DruidDataSource
    url: jdbc:sqlserver://172.20.100.232:1433;databaseName=XXX
    username: sa
    password: ###
  application:
    name: microservice-provider
server:
  port: 8002

This configuration file is roughly the driver class settings, the mybatis-plus key parameter settings, and nothing else.

4. Configuring generator classes

stay src Find the bottom package in the directory and create a new one generator Package, create SqlserverGenerator Class, code and comments are as follows:
package com.xyzh.mybatisplus.demo.generator;

import com.baomidou.mybatisplus.core.toolkit.StringPool;
import com.baomidou.mybatisplus.generator.AutoGenerator;
import com.baomidou.mybatisplus.generator.InjectionConfig;
import com.baomidou.mybatisplus.generator.config.*;
import com.baomidou.mybatisplus.generator.config.po.TableFill;
import com.baomidou.mybatisplus.generator.config.po.TableInfo;
import com.baomidou.mybatisplus.generator.config.rules.NamingStrategy;
import com.baomidou.mybatisplus.generator.engine.FreemarkerTemplateEngine;

import java.util.ArrayList;
import java.util.List;

public class SqlserverGenerator {
    /**
     * RUN THIS
     */
    //Project path where the generated file is located
    private static String baseProjectPath = "D:\\java\\demo";

    //Base Package Name
    private static String basePackage="com.xyzh.mybatisplus.demo";
    //Set Author
    private static String authorName="adrian";
    //Here is the name of the table to be generated (commented out here if all are generated)
    //private static String[] tables= {"t_role","t_resource","t_role_resource","t_user_role"};
    //table prefix can be set
    private static String prefix="t_";

    //Four Elements of Database Configuration
    private static String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
    private static String url = "jdbc:sqlserver://172.20.100.232:1433;databaseName=XXX";
    private static String username = "sa";
    private static String password = "###";


    public static void main(String[] args) {
        // Code generator
        AutoGenerator mpg = new AutoGenerator();

        // Global Configuration
        GlobalConfig gc = new GlobalConfig();
        String projectPath = System.getProperty("user.dir");
        gc.setOutputDir(baseProjectPath + "/src/main/java");
        // TODO Set User Name
        gc.setAuthor("adrian");
        gc.setOpen(true);
        // service naming
        gc.setServiceName("%sService");
        // service impl naming
        gc.setServiceImplName("%sServiceImpl");
        // Custom file naming, note that%s will automatically populate table entity attributes!
        gc.setMapperName("%sMapper");
        gc.setXmlName("%sMapper");
        gc.setFileOverride(true);
        gc.setActiveRecord(true);
        // XML Secondary Cache
        gc.setEnableCache(false);
        // XML ResultMap
        gc.setBaseResultMap(true);
        // XML columList
        gc.setBaseColumnList(false);
        mpg.setGlobalConfig(gc);

        // TODO Data Source Configuration
        DataSourceConfig dsc = new DataSourceConfig();
        dsc.setUrl(url);
        dsc.setDriverName(driverName);
        dsc.setUsername(username);
        dsc.setPassword(password);
        mpg.setDataSource(dsc);

        // TODO package configuration
        PackageConfig pc = new PackageConfig();
        //pc.setModuleName(scanner("module name"));
        pc.setParent("com.xyzh.meetingsys");
        pc.setEntity("entity");
        pc.setService("service");
        pc.setServiceImpl("service.impl");
        mpg.setPackageInfo(pc);

        // Customize the fields that need to be populated
        List<TableFill> tableFillList = new ArrayList<>();
        
        // Custom Configuration
        InjectionConfig cfg = new InjectionConfig() {
            @Override
            public void initMap() {
                // to do nothing
            }
        };
        List<FileOutConfig> focList = new ArrayList<>();
        focList.add(new FileOutConfig("/templates/mapper.xml.ftl") {
            @Override
            public String outputFile(TableInfo tableInfo) {
                // Custom Input File Name
                return projectPath + "/src/main/resources/mapper/"
                        + "/" + tableInfo.getEntityName() + "Mapper" + StringPool.DOT_XML;
            }
        });
        cfg.setFileOutConfigList(focList);
        mpg.setCfg(cfg);
        mpg.setTemplate(new TemplateConfig().setXml(null));

        // Policy Configuration
        StrategyConfig strategy = new StrategyConfig();
        strategy.setNaming(NamingStrategy.underline_to_camel);
        strategy.setColumnNaming(NamingStrategy.underline_to_camel);
        strategy.setEntityLombokModel(true);
        // Set the logical delete key (this is the logical delete operation)
        strategy.setLogicDeleteFieldName("deleted");
        // TODO specifies the database table name of the generated bean s (commented out here if all are generated)
        //strategy.setInclude("userinfos");
        //strategy.setSuperEntityColumns("id");
        // Hump hyphen
        strategy.setControllerMappingHyphenStyle(true);
        mpg.setStrategy(strategy);
        // Selecting the freemarker engine requires the following addition, note that the pom dependency must have!
        mpg.setTemplateEngine(new FreemarkerTemplateEngine());
        mpg.execute();
    }
}

There is only one place to mention that the template engine chosen here is a freemarker, so the pom needs to import the FreeMarker dependencies.
Another is that durid does not support sqlserver very well, so some data types, such as datetime, require extra attention

5. Run generator

Run generator directly
The resulting structure is as follows:

6. Implement query function

mybatis-plus is better than mybatis in that it can automatically generate a lot of operation methods, but these methods are not enough, so you need to define your own sql operation methods

Customize how sql operates can be written in mapper.xml or unloaded in mapper classes.
This article is written in the mapper class
Find the mapper class for the method we want to add, as shown in the figure:

Add two new methods directly:
mapper:

 @Select("SELECT * FROM internalMeetingUserInfo where isDeleted=0 and  internalStatus= #{internalStatus}")
     List<InternalMeetingUserInfo> selectUsersByInternalStatus(@Param("internalStatus") int internalStatus);

    @Select("SELECT * FROM internalMeetingUserInfo &{ew.customSqlSegment}")
    List<InternalMeetingUserInfo> selectUsersByWrapper(@Param(Constants.WRAPPER) Wrapper<InternalMeetingUserInfo> userWrapper);

After adding in mapper, you need to add corresponding methods in service, service-impl, controller

service

List<InternalMeetingUserInfo> selectUserByInternalStatus(Integer interStatus);

service-impl

  @Autowired
    InternalMeetingUserInfoMapper internalMeetingUserInfoMapper;

    @Override
    public List<InternalMeetingUserInfo> selectUserByInternalStatus(Integer interStatus) {
        return internalMeetingUserInfoMapper.selectUsersByInternalStatus(interStatus);
    }

At this point, you'll notice an autowire error telling us that the internalMeetingUserInfoMapper object cannot be automatically assembled here
This is because the mapper class is not found by the program and the program mapper needs to be informed of its location using the scan tag class.
So you need to annotate @MapperScan("com.xyzh.mybatisplus.demo.mapper") on the application startup class DemoApplication

You also need to annotate the components above the mapper class with the @Component annotation

controller

@Autowired
    InternalMeetingUserInfoService internalMeetingUserInfoService;

    @RequestMapping(value="/userinfo/{userStatus}",method = RequestMethod.GET)
    @ResponseBody
    public List<InternalMeetingUserInfo> selectUserByInterStatus(@PathVariable("userStatus") Integer userStatus){

        List<InternalMeetingUserInfo> users=internalMeetingUserInfoService.selectUserByInternalStatus(userStatus);
        return users;
    }

Then run the program, running the application class.

At this point, in fact, the configuration work has been completed.
But record a few errors.
Because I have a datetime type field in my database, and when generated through autogenerator, the corresponding entity field is
The localdatetime type, so it's shown here that there will be errors in writing and updating.
Error attempting to get column'passportDate'from result set. Cause: java.sql.SQLFeatureNotSupportedException; null; nested exception is java.sql.SQLFeatureNotSupportedException

Here I manually change the localdatetime type to Date type and write a custom transformation class to display

First in the entity package, find the corresponding entity,
Change data type LocalDateTime to Date

Add a conversion method to the controller:

 @InitBinder
    public  void initBinder(WebDataBinder binder, WebRequest request){
        //Conversion Date
        DateFormat dateFormat=new SimpleDateFormat("yyyy-MM-dd HH:mm");
        binder.registerCustomEditor(Date.class,new CustomDateEditor(dateFormat,true));
    }

This solves the problem.
However, if you write such a method, you need to add it to each controller.
I can write a global transformation method.

Create a new package config

Add a new class, DateFormatConfig, with the following code:

package com.xyzh.mybatisplus.demo.config;

import com.fasterxml.jackson.core.JsonGenerator;
import com.fasterxml.jackson.core.JsonParser;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.DeserializationContext;
import com.fasterxml.jackson.databind.JsonDeserializer;
import com.fasterxml.jackson.databind.JsonSerializer;
import com.fasterxml.jackson.databind.SerializerProvider;
import org.springframework.boot.jackson.JsonComponent;

import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

@JsonComponent
public class DateFormatConfig {
    private static SimpleDateFormat dateFormat=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    public static class DateJsonSerializer extends JsonSerializer<Date>{
        @Override
        public void serialize(Date date, JsonGenerator jsonGenerator, SerializerProvider serializerProvider) throws IOException {
            jsonGenerator.writeString(dateFormat.format(date));
        }
    }

    public static class DateJsonDeserializer extends JsonDeserializer<Date>{


        @Override
        public Date deserialize(JsonParser jsonParser, DeserializationContext deserializationContext) throws IOException, JsonProcessingException {
            try{
                return dateFormat.parse(jsonParser.getText());
            }catch (ParseException e){
                throw new RuntimeException(e);
            }
        }
    }
}

Note the comment @JsonComponent below, which is also a component class.
The serialization and deserialization methods are then overridden.

Tags: Java Mybatis xml Spring

Posted on Mon, 02 Sep 2019 20:17:20 -0700 by mailtome