preface

MyBatis is an excellent persistence layer framework, which supports customized SQL, stored procedures and advanced mapping. MyBatis avoids almost all JDBC code and manually setting parameters and getting result sets. MyBatis can use simple XML or annotations to configure and map native information, and map interfaces and Java POJOs (plain ordinary Java objects) into records in the database.

1, Improvement of JDBC to Mybatis

1. Steps of JDBC programming

1) Load database driver
2) Create and get database links
3) Create a jdbc statement object
4) Set sql statement
5) Setting parameters in sql statements (using preparedStatement)
6) Execute sql through statement and get results
7) Parse the sql execution results
8) Release resources (resultSet, preparedstatement, connection)

2. Improvement of JDBC by mybatis

1) Use database connection pool to solve resource waste
The frequent creation and release of database links in JDBC mode cause a waste of system resources, which affects the system performance. Using database connection pool, you can repeatedly use the established connections to access the database. Reduce the opening and closing time of the connection.
2) Encapsulating SQL statements using XML configuration files
Mybatis writes SQL statements in the configuration file, configures various statements (statement, preparedStatemnt, CallableStatement) to be executed through xml or annotation, and maps java objects and SQL in the statement to generate the final executed SQL statements. Finally, the mybatis framework executes SQL, maps the results into java objects and returns them. In this way, when you need to change SQL, you only need to change the configuration file. (without affecting the interface)
3) Define the sql execution output result through the Mapped Statement
The Mapped Statement defines the output results of sql execution, including HashMap, basic type and pojo. The Executor maps the output results to java objects after executing sql through the Mapped Statement. The output result mapping process is equivalent to the result parsing process in jdbc programming.
Mybatis architecture:

2, Mybatis under IDEA

1. New spring mybatis project

1) File–>New–>Project

2) Edit project information

Select version 8 for the java version

3) Select Dependencies
First select Web and check Spring Web

Then click SQL and check Spring Data JDBC and Mybatis Framework

Click next after selection

4) Set the project name and save location, and click Finish to complete the project creation

5) Create four packages: bean, controller, mapper and service.

**6) * * create classes and interfaces in corresponding packages as follows:

2. Configuration code

Account:

package com.example.springboot.bean;

public class Account {
    String no;
    String name;
    String password;
    String root;

    public void setNo(String no) {
        this.no = no;
    }

    public void setName(String name) {
        this.name = name;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public void setRoot(String root) {
        this.root = root;
    }

    public String getRoot() {
        return root;
    }

    public String getPassword() {
        return password;
    }

    public String getName() {
        return name;
    }

    public String getNo() {
        return no;
    }

    @Override
    public String toString() {
        return "Account{" +
                "no='" + no + '\'' +
                ", name='" + name + '\'' +
                ", password='" + password + '\'' +
                ", root='" + root + '\'' +
                '}';
    }
}

DataBaseLink:

package com.example.springboot.bean;

import java.sql.Connection;
import java.sql.DriverManager;

public class DataBaseLink {
    static public Connection getConn() {
        String serverName="com.mysql.jdbc.Driver";
        String dbURL="jdbc:mysql://localhost:3306/studentadmin";
        String userName="user name";
        String userPwd="Database connection password";
        try {
            Class.forName(serverName);
            Connection tmp = DriverManager.getConnection(dbURL, userName, userPwd);
            System.out.println("Successfully connected to database");
            return tmp;
        }
        catch(Exception e) {
            System.out.println("Failed to connect to the database");
            System.out.println(e);
        }
        return null;
    }
}

AccountController:

package com.example.springboot.controller;

import com.example.springboot.bean.Account;
import com.example.springboot.bean.DataBaseLink;
import com.example.springboot.service.AccountService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
@RestController
public class AccountController {
    @Autowired
    private AccountService accountService;

    @ResponseBody
    @RequestMapping(value = "/get",method = RequestMethod.GET)
    public List<Account> get(){
        Connection conn= DataBaseLink.getConn();
        if(conn!=null){
            String sql="select name from account";
            try {
                PreparedStatement pst = conn.prepareStatement(sql);
                ResultSet results = pst.executeQuery();
                while (results.next()){
                    System.out.println(results.getString("name"));
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return accountService.getAllAccounts();
    }

}

AccountMapper:

package com.example.springboot.mapper;

import com.example.springboot.bean.Account;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper
public interface AccountMapper {
    List<Account> getAllAccounts();

}

AccountService:

package com.example.springboot.service;

import com.example.springboot.bean.Account;
import com.example.springboot.mapper.AccountMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class AccountService {
    @Autowired
    AccountMapper accountMapper;
    public List<Account> getAllAccounts(){
        return accountMapper.getAllAccounts();
    }
}

In addition, you need to create a new file mapper under the resources file to store the xml file of sql statements

AccountMapper.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.example.springboot.mapper.AccountMapper">
    <select id="getAllAccounts" resultType="com.example.springboot.bean.Account">
      select * from user

    </select>

</mapper>

Then the configuration file

input

spring.datasource.url=jdbc:mysql://127.0.0.1:3306/studentAdmin?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT&useSSL=false
spring.datasource.username=user name
spring.datasource.password=Database connection password
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
mybatis.typeAliasesPackage=com.example.springboot.bean
mybatis.mapperLocations=classpath:mapper/*.xml
server.port=8080

3. Test

1) Target database

2) Connection test
backstage:

About ssl service errors
Establishing SSL connection without server's identity verification is not recommended.
Add & SSL = false to the address connecting to the database
Solutions to ssl connection problems

Browser input http://localhost:8080/get

Access normal

summary

From JDBC to MyBatis, the SQL statements in the code are encapsulated in external XML files, which simplifies the troublesome steps of modifying java source code and makes more efficient use of database connection resources. MyBatis can use simple XML or annotations to configure and map native information.

reference resources

https://blog.csdn.net/qq_47281915/article/details/120782706
https://blog.csdn.net/u010176014/article/details/52028854