[toc]

introduce

MyBatis is a Java semi-automated ORM framework, and MyBatis-Plus is its secondary packaging, simplifying the use of MyBatais, simply said, can be as little as possible handwritten add, delete, change and check SQL statements.

Official document address: mp.baomidou.com/guide

The document is in Chinese, students who like to read the document by themselves can directly follow the official document to operate and learn. This article is mainly to record my own learning process, and finally will provide their own code and test database environment deployment. If you don’t want to do these steps yourself, you can use this article to set up an environment for learning.

Deploying a database

Docker-composemess. yml: docker-composemess. yml: docker-composemess. yml: docker-composemess. yml: docker-composemess. yml

version: '3.1'Services: mysql: restart: always image: mysql:5.7.22 container_name: mysql ports: -3306:3306 environment: TZ: Asia/Shanghai MYSQL_ROOT_PASSWORD: 123456command:
      --character-set-server=utf8mb4
      --collation-server=utf8mb4_general_ci
      --explicit_defaults_for_timestamp=true
      --lower_case_table_names=1
      --max_allowed_packet=128M
      --sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO"
    volumes:
      - ./data:/var/lib/mysql
Copy the code

Not being able to use Docker containers can be learned from this article

Spring Boot integration with MyBatis-Plus

The project is based on Spring Boot. After creating the project, add various dependencies. The complete POM.xml file 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 http://maven.apache.org/xsd/maven-4.0.0.xsd"> < modelVersion > 4.0.0 < / modelVersion > < the parent > < groupId > org. Springframework. Boot < / groupId > The < artifactId > spring - the boot - starter - parent < / artifactId > < version > 2.1.6. RELEASE < / version > < relativePath / > <! -- lookup parent from repository --> </parent> <groupId>com.domain</groupId> <artifactId>hello-mybatisplus</artifactId> < version > 0.0.1 - the SNAPSHOT < / version > < name > hello - mybatisplus < / name > < description > Demo projectforSpring Boot</description> <properties> <java.version>1.8</java.version> </properties> <dependencies> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <! --mysql start--> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> < version > 1.1.10 < / version > < / dependency > < the dependency > < groupId > mysql < / groupId > <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <! --mysql end--> <! --lombok start--> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </dependency> <! --lombok end --> <! --mybatisplus start--> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> < version > 3.1.2 < / version > < / dependency > < the dependency > < groupId > com. Baomidou < / groupId > < artifactId > mybatis - plus - generator < / artifactId > < version > 3.1.2 < / version > < / dependency > < the dependency > < the groupId > org. Apache. Velocity < / groupId > < artifactId > velocity - engine - core < / artifactId > < version > 2.1 < / version > </dependency> <! --mybatisplus end--> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> </build> </project>Copy the code

Added database connection, and MyBatis-Plus dependency.

Connecting to a Database

Add the database connection configuration to application.yml:

Spring: a datasource: druid: url: JDBC: mysql: / / 192.168.65.130:3306 / myshop? useUnicode=true&characterEncoding=utf-8&useSSL=false
      username: root
      password: 123456
      initial-size: 1
      min-idle: 1
      max-active: 20
      test-on-borrow: true
      # MySQL 8.x: com.mysql.cj.jdbc.Driver
      driver-class-name: com.mysql.jdbc.Driver
      test-while-idle: true
      
mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
Copy the code

The main configuration of the database address, user name, password, and open mybatis-plus SQL log, so that when the test log will print out the SQL statement of the current database operation, easy to learn and troubleshoot.

Code generator

MyBatis-Plus provides the function of code generator, which can automatically help us generate mapper. XML, Mapper. Java POJO and other files. Run the code to generate these files:

package com.domain.hello.mybatisplus; import com.baomidou.mybatisplus.core.exceptions.MybatisPlusException; import com.baomidou.mybatisplus.core.toolkit.StringPool; import com.baomidou.mybatisplus.core.toolkit.StringUtils; 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.TableInfo; import com.baomidou.mybatisplus.generator.config.rules.NamingStrategy; import java.util.ArrayList; import java.util.List; import java.util.Scanner; Public class CodeGenerator {/** * <p> */ public static String Scanner (String tip) {scanner scanner = new Scanner(System.in); StringBuilderhelp = new StringBuilder();
        help.append("Please enter" + tip + "：");
        System.out.println(help.toString());
        if (scanner.hasNext()) {
            String ipt = scanner.next();
            if (StringUtils.isNotEmpty(ipt)) {
                return ipt;
            }
        }
        throw new MybatisPlusException("Please enter the correct one" + tip + "！"); } public static void main(String[] args) {// AutoGenerator MPG = new AutoGenerator(); // GlobalConfig gc = new GlobalConfig(); String projectPath = System.getProperty("user.dir");
        gc.setOutputDir(projectPath + "/src/main/java");
        gc.setAuthor("domain");
        gc.setOpen(false);
//        gc.setSwagger2(true); // Entity attribute Swagger2 annotation mpg.setGlobalConfig(gc); // DataSourceConfig DSC = new DataSourceConfig(); dsc.setUrl("JDBC: mysql: / / 192.168.65.130:3306 / myshop? useUnicode=true&useSSL=false&characterEncoding=utf8");
        // dsc.setSchemaName("public");
        dsc.setDriverName("com.mysql.jdbc.Driver");
        dsc.setUsername("root");
        dsc.setPassword("123456"); mpg.setDataSource(dsc); PackageConfig PC = new PackageConfig(); // pc.setModuleName(scanner("Module name"));
        pc.setParent("com.domain.hello.mybatisplus"); mpg.setPackageInfo(pc); InjectionConfig CFG = newInjectionConfig() {
            @Override
            public void initMap() {
                // to donothing } }; If the template engine is freemarker // String templatePath ="/templates/mapper.xml.ftl"; // If the template engine is Velocity String templatePath ="/templates/mapper.xml.vm"; List<FileOutConfig> focList = new ArrayList<>(); Add (new FileOutConfig(templatePath) {@override public String outputFile(TableInfo TableInfo) { // Customize the output file name. If your Entity has a prefix or suffix, note that the XML name will change accordingly!!return projectPath + "/src/main/resources/mapper/" /*+ pc.getModuleName()*/
                        + "/" + tableInfo.getEntityName() + "Mapper"+ StringPool.DOT_XML; }}); /* cfg.setFileCreate(newIFileCreate() { @Override public boolean isCreate(ConfigBuilder configBuilder, FileType fileType, String filePath) {// Check whether the custom folder needs to create checkDir("Directory created by calling the default method");
                return false; }}); */ cfg.setFileOutConfigList(focList); mpg.setCfg(cfg); // TemplateConfig TemplateConfig = new TemplateConfig(); // Configure a custom output template. // Specify a custom template path without. FTL /. Vm, which is automatically identified according to the template engine used."templates/entity2.java"); // templateConfig.setService(); // templateConfig.setController(); templateConfig.setXml(null); mpg.setTemplate(templateConfig); // StrategyConfig strategy = new StrategyConfig(); strategy.setNaming(NamingStrategy.underline_to_camel); strategy.setColumnNaming(NamingStrategy.underline_to_camel); // strategy.setSuperEntityClass("com.domain.hello.mybatisplus.common.BaseEntity");
        strategy.setEntityLombokModel(true);
//        strategy.setRestControllerStyle(true); Public parent / / / / strategy. SetSuperControllerClass ("com.domain.hello.mybatisplus.common.BaseController"); / / write in public field in the parent class strategy. SetSuperEntityColumns ("id");
//        strategy.setInclude(scanner("Table name, separated by multiple Commas").split(","));
        strategy.setInclude(new String[] { "tb_user"."tb_item_cat"."tb_content"});
        strategy.setControllerMappingHyphenStyle(true);
        strategy.setTablePrefix(pc.getModuleName() + "_");
        mpg.setStrategy(strategy);


//        strategy.setRestControllerStyle(false); // strategy.setSuperServiceClass(null); // mpg.setTemplateEngine(new FreemarkerTemplateEngine()); // mpg.setTemplateEngine(new VelocityTemplateEngine()); mpg.execute(); }}Copy the code

CRUD operations

Unit test to perform CRUD operations provided by MyBatis-Plus

increase

/** */ @test public voidtestInsert() {// Construct a test datafor (int i = 0; i < 3; i++) {

            TbUser tbUser = new TbUser();
            tbUser.setUsername("domain" + RandomUtils.nextDouble());
            tbUser.setPassword("123456" + RandomUtils.nextDouble());
            tbUser.setPhone("130" + RandomUtils.nextInt());
            tbUser.setEmail("[email protected]"+ RandomUtils.nextDouble()); tbUser.setCreated(new Date()); tbUser.setUpdated(new Date()); // Insert data tbusermapper. insert(tbUser); }}Copy the code

delete

 @Test
    public void testDelete() {

        UpdateWrapper updateWrapper = new UpdateWrapper<TbUser>();

        HashMap<String, Object> params = new HashMap<>();
        params.put("id", 7);
        params.put("username"."zhangsan");

        updateWrapper.allEq(params);

        tbUserMapper.delete(updateWrapper);

//        tbUserMapper.deleteById("46");
//        ArrayList<Integer> ids = new ArrayList<Integer>();
//        ids.add(47);
//        tbUserMapper.deleteBatchIds(ids);

    }
Copy the code

change

 @Test
    public void testUpdate() {

        UpdateWrapper<TbUser> updateWrapper = new UpdateWrapper<>();
        updateWrapper.eq("username"."domain111");

        TbUser tbUser = new TbUser();
        tbUser.setUsername("domain");

        tbUserMapper.update(tbUser, updateWrapper);

    }
Copy the code

check

    @Test
    public void testSelect() {
        UpdateWrapper<TbUser> wrapper = new UpdateWrapper<>();
        wrapper.eq("username"."domain"); List<TbUser> tbUsers = tbusermapper.selectList (wrapper);for (TbUser tbUser : tbUsers) {
            System.out.println("\n"+ tbUser.getUsername()); }}Copy the code

paging

MyBatis-Plus also supports paging and requires a simple configuration to use

The Config. Java configuration

package com.domain.hello.mybatisplus.config;

import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@EnableTransactionManagement
@Configuration
@MapperScan("com.domain.hello.mybatisplus.mapper.*.mapper*"Public class MybatisPlusConfig {/** ** PaginationInterceptor public PaginationInterceptorpaginationInterceptor() {
        returnnew PaginationInterceptor(); }}Copy the code

Add mapper methods

mapper.java

@Repository public interface TbUserMapper extends BaseMapper<TbUser> { List<TbUser> getAll(); /** * <p> : * </p> * * @param pagination object * </p> * * @param pagination object * @param state Indicates the status * @returnPage object */ IPage<TbUser> selectPageVo(Page Page, @param ("city") String city);

}
Copy the code

mapper.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.domain.hello.mybatisplus.mapper.TbUserMapper">

    <select id="selectPageVo" resultType="com.domain.hello.mybatisplus.entity.TbUser">
    SELECT id,username FROM tb_user WHERE city=#{city}
    </select>

</mapper>

Copy the code

The test page

    @Test
    public void testPageSetOptimizeCountSql () {page.setoptimizecountsql () {page.setopTimizecountsql (false); // When total is less than 0 or setsetSearchCount(falseThe pagination plugin does not do count queries. // Page number subscript Integer pageIndex = 1; // Number of pages per page Integer pageSize = 2; IPage<TbUser> iPage = tbUserMapper.selectPageVo(new Page(pageIndex * pageSize, pageSize),"gz");


        List<TbUser> tbUsers = iPage.getRecords();

        for (TbUser tbUser : tbUsers) {
            System.out.println("\n"+ tbUser.getUsername()); }}Copy the code

Conditional constructor

MyBatis-Plus provides conditional constructors to build conditions for SQL statements.

 @Test
    public void testSelect() {

        UpdateWrapper<TbUser> wrapper = new UpdateWrapper<>();
//        wrapper.eq("password"."195d91be1e3ba6f1c857d46f24c5a454"); // is equal to // wrapper.ne("username"."domain"); // Does not equal // wrapper.gt("age", 18); // greater than // wrapper.ge("age", 22); // wrapper.lt("age", 22); //小于
//        wrapper.le("age", 22); // Wrapper.between ("age"."22"."25"); // Between value1 and value2 // wrapper. NotBetween ("age"."22"."25"); // Not between value1 and value2, null will not be detected // wrapper.like("username"."domain"); %value%
//        wrapper.likeLeft("username"."1");  %value
//        wrapper.likeRight("username"."zhangsan"); value%
//        wrapper.isNull("age"); //age == null data // wrapper.in("age"."31"."33"); Age is 31, 33 // wrapper.notin ("age"."22"."23"); // wrapper.insql ()"id"."select id from tb_user where id > 3"); // id is the value of the id setwhere id in(4 and 6); SELECT username,password,phone,email,created,updated FROM tb_user WHERE id IN (select id from tb_userwhere id > 3)
//        wrapper.notInSql("id"."select id from tb_user where id < 22"); // Record with id not less than 22 // wrapper.notinsql ("id"."select id from tb_user where id < 22"); // wrapper.groupby (); // wrapper.groupby (); // Wrapper.groupby ("age"); Wrapper.orderbyasc () {// Age is the first record of a group."id"."age"); Wrapper.orderby (); // Sort by id, age;true.true."id"."age"); // Wrapper. orderByDesc("id"."age"); // All elements in the // //having clause must also appear in the select list. Some database exceptions are oracle. // having clauses andwhereClauses can be used to set constraints so that the query results meet certain constraints. Having clauses limit groups, not rows.whereYou cannot use aggregate functions in a clause, whereas you can in a having clause. // if other SQL statements are added, the order of execution is as follows: // s-f-w-g-h-O // // select - >where-- > group by -- > having -- >order by // // // // SELECT username, // city, // SUM(price) // FROM // tb_user // GROUP BY // city // HAVING // SUM(price) > 2000 // wrapper.groupBy("city").having("sum(price)>2000");

//        wrapper.having("sum(age)>22");


//        wrapper.eq("username"."domain").or().eq("age"."22"); Username = domain OR age = 22 //OR nested // wrapper.eq("username"."domain")
//                .or(i -> i.eq("age"."22").eq("phone"."13333333334")); // username = domain or (age = 22 and phone = 13333333334) //and nested // wrapper.eq("age"."22")
//                .and(i -> i.eq("phone"."13333333333").or().eq("phone"."13333333334")); // Age ==22 and (phone =13333333333 or phone = 13333333334) // Nested username=domain or age<22 // wrapper  -> i.eq("username"."domain").or().lt("age"."22"));


        wrapper.set("username"."domain").eq("username"."niuniu"); List<TbUser> tbUsers = tbUserMapper.selectList(wrapper); QueryWrapper<TbUser> QueryWrapper = new QueryWrapper<>(); // QueryWrapper<TbUser> QueryWrapper = new QueryWrapper<>(); // queryWrapper.select("username"."password"."phone");
//        queryWrapper.eq("username"."domain"); // List<TbUser> tbUsers = tbUserMapper.selectList(queryWrapper); // List<TbUser> tbUsers = tbusermapper.getall ();for (TbUser tbUser : tbUsers) {
            System.out.println("\n"+ tbUser.getUsername()); }}Copy the code

Custom SQL

If the CRUD method provided by MyBatis-Plus does not meet the requirements, it is time to write your own SQL to meet the requirements.

For example, query the records of the user’s city where the total amount is greater than 2000.

Mapper.java defines methods

@Repository
public interface TbUserMapper extends BaseMapper<TbUser> {

     List<TbUser> getAll();
}
Copy the code

Mapper XML definition SQL

<? 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.domain.hello.mybatisplus.mapper.TbUserMapper">

    <select id="getAll" resultType="com.domain.hello.mybatisplus.entity.TbUser">
        SELECT
            username,
            city,
            SUM(price) as amount
        FROM
            tb_user
        GROUP BY
            city
        HAVING
            SUM(price) > 2000

    </select>
</mapper>
Copy the code

Test custom SQL

@Test
    public void testSelectList<TbUser> tbUsers = tbusermapper.getall ();for (TbUser tbUser : tbUsers) {
            System.out.println("\n"+ tbUser.getUsername()); }}Copy the code

The complete code

Full code address :github.com/domain9065/…

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

MyBatis-Plus User guide

introduce

Deploying a database

Spring Boot integration with MyBatis-Plus

Connecting to a Database

Code generator

CRUD operations

increase

delete

change

check

paging

The Config. Java configuration

Add mapper methods

The test page

Conditional constructor

Custom SQL

Mapper.java defines methods

Mapper XML definition SQL

Test custom SQL

The complete code

MyBatis-Plus User guide

introduce

Deploying a database

Spring Boot integration with MyBatis-Plus

Connecting to a Database

Code generator

CRUD operations

increase

delete

change

check

paging

The Config. Java configuration

Add mapper methods

The test page

Conditional constructor

Custom SQL

Mapper.java defines methods

Mapper XML definition SQL

Test custom SQL

The complete code

Related Posts

Introduction to Redis

ORACLE lock mechanism – Blue – Blog park

14 Good Habits for Writing Java Code