Mybatis is one of the methods to add, delete, change and query MySQL database

Time:2021-11-28

Environmental Science:

  • jdk1.8
  • mysql5.7
  • maven3.6.0
  • IDEA

What is the mybatis framework?

  • Mybatis is an excellent persistence layer framework,
  • It supports custom SQL, stored procedures, and advanced mapping.
  • Mybatis eliminates almost all JDBC code and the work of setting parameters and obtaining result sets.
  • Mybatis can configure and map primitive types, interfaces and Java POJOs (plain old Java objects) to records in the database through simple XML or annotations.
  • Mybatis was originally a part of ApacheOpen source projectIbatis, 2010projectMigrated from Apache Software Foundation to [Google Code](https://baike.baidu.com/item/googleCode / 2346604) and renamed mybatis. Moved to in November 2013Github

Let’s start building

  1. The SQL file is as follows:
/*
SQLyog Ultimate v13.1.1 (64 bit)
MySQL - 5.7.31-log : Database - mybatis
*********************************************************************
*/

CREATE DATABASE /*!32312 IF NOT EXISTS*/`mybatis` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `mybatis`;

/*Table structure for table `user` */

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (
  `id` int(20) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  `pwd` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `user` */

insert  into `user`(`id`,`name`,`pwd`) values 
(1,'qijian','123'),
(2,'Tom','123'),
(3,'qijian','111');
  1. Use Maven to build the project. The pom.xml file code is as follows. Note that this project is a sub project of mybatis study.
<?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">
    <parent>
        <artifactId>mybatis-study</artifactId>
        <groupId>com.qijijan</groupId>
        <version>1.0-SNAPSHOT</version>
    </parent>
    <modelVersion>4.0.0</modelVersion>

    <artifactId>mybatis-01</artifactId>

    <dependencies>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.2</version>
        </dependency>
    </dependencies>

    <build>
      <!-- Maven static resource filtering problem -- >
        <resources>
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>true</filtering>
            </resource>

            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.poroperties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>true</filtering>
            </resource>
        </resources>
    </build>

</project>
  1. The XML configuration file contains the core settings for the mybatis system, including the data source to obtain the database connection instance and the transaction manager to determine the transaction scope and control mode.
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=false&amp;useUnicode=true&amp;characterEncoding=UTF-8"/>
                <property name="username" value="root"/>
                <property name="password" value="****"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="com/qijian/dao/UserMapper.xml"/>
    </mappers>

</configuration>

Note: the declaration of the XML header, which is used to verify the correctness of the XML document. The environment element body contains the configuration of transaction management and connection pool. The mappers element contains a set of mappers whose XML mapping files contain SQL code and mapping definition information.

  1. Each mybatis based application takes an instance of sqlsessionfactory as the core. An instance of sqlsessionfactory can be obtained through sqlsessionfactorybuilder. Sqlsessionfactory builder can build a sqlsessionfactory instance from an XML configuration file or a pre configured configuration instance. The XML configuration file is used here.

Mybatisutils tool class

package com.qijian.utils;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;

public class MybatisUtils {
   private static SqlSessionFactory sqlSessionFactory;
   private static String resource = "mybatis-Config.xml";
   private static InputStream inputStream;

   static {

       //The first step to using mybatis: get the sqlsessionfactory object
       try {
           inputStream = Resources.getResourceAsStream(resource);
           sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
       } catch (IOException e) {
           e.printStackTrace();
       }
   }
        //Now that we have sqlsessionfactory, as the name suggests, we can get an instance of sqlsession from it.
        //Sqlsession provides all the methods required to execute SQL commands in the database.
     public static SqlSession getSqlSession() throws IOException {
         return sqlSessionFactory.openSession();
        }

}
  1. The entity class user is as follows
package com.qijian.pojo;

public class User {
    private int id;
    private String name;
    private String pwd;

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", pwd='" + pwd + '\'' +
                '}';
    }

    public User(int id, String name, String pwd) {
        this.id = id;
        this.name = name;
        this.pwd = pwd;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

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

    public String getPwd() {
        return pwd;
    }

    public void setPwd(String pwd) {
        this.pwd = pwd;
    }
}
  1. Usermapper class
package com.qijian.dao;

import com.qijian.pojo.User;

import java.util.List;
import java.util.Map;

public interface UserMapper {
    //Query all users
    //@Select("SELECT * FROM mybatis.user WHERE id = #{id}")
    List<User> getUserList();

    //Query user by ID
    User getUserById(int id);

    //Add user
    int addUser(User user);

    //Add map collection used by users
    int addUserByMap(Map<String,Object> map);

    //Modify user
    int updateUser(User user);

    //Delete user
    void deleteUser(int id);
}
  1. Based on the XML Mapping statement, define the following mapping statement in the namespace “com.qijian.dao.usermapper” (for example, “getuserlist”), so that you can call the mapping statement with the fully qualified name “com.qijian.dao.usermapper.getuserlist”
<?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.qijian.dao.UserMapper">
    <select id="getUserList" resultType="com.qijian.pojo.User">
    select * from mybatis.user
    </select>

    <select id="getUserById" parameterType="int" resultType="com.qijian.pojo.User">
        select *from mybatis.user where id = #{id};
    </select>

    <insert id="addUser" parameterType="com.qijian.pojo.User">
        insert into mybatis.user(id,name,pwd) values(#{id},#{name},#{pwd});
    </insert>

    <insert id="addUserByMap" parameterType="Map">
        insert into mybatis.user(id,name,pwd) values(#{id},#{name},#{pwd});
    </insert>

    <update id="updateUser" parameterType="com.qijian.pojo.User">
        update mybatis.user
        set name = #{name},pwd = #{pwd}
        where id=#{id};
    </update>

    <delete id="deleteUser" parameterType="int">
        delete from mybatis.user  where id=#{id};
    </delete>

</mapper>

be careful:
A little supplement to namespace
In previous versions of mybatis, namespaces did not play a big role and were optional. But now, as namespaces become more important, you have to specify namespaces.
Namespace has two functions. One is to use a longer fully qualified name to isolate different statements. At the same time, it also implements the interface binding you see above. Even if you don’t think you can use interface binding for the time being, you should follow the rules here in case you change your mind one day. In the long run, as long as you put the namespace in the appropriate java package namespace, your code will become cleaner and easier for you to use mybatis.
Name resolution: in order to reduce the amount of input, mybatis uses the following name resolution rules for all configuration elements with names (including statements, result mapping, cache, etc.).
Fully qualified names (such as “com. Mypackage. Mymapper. Selectallthings”) will be used directly for finding and using.
Short names (such as “selectallthings”) can also be used as a separate reference if they are globally unique. If it is not unique and there are two or more identical names (such as “com. Foo. Selectallthings” and “com. Bar. Selectallthings”), the error of “short name is not unique” will occur when using it. In this case, the fully qualified name must be used.
8. Testing

package com.qijian.dao;

import com.qijian.pojo.User;
import com.qijian.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.io.IOException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class UserMapperTest {
    @Test
    public void testSelect() throws IOException {
        //Step 1: get sqlsession object
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        //Execute
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
       List<User> userList =  userMapper.getUserList();
       for(User user : userList){
           System.out.println(user);
       }
       //Close resource
        sqlSession.close();
    }

    @Test
    public void testSelectById() throws IOException {
        //Get sqlsession object
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        //Execute
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        User user = userMapper.getUserById(1);
        System.out.println(user);

        sqlSession.close();
    }

//Note that adding, deleting, and modifying differences require submitting transactions
    @Test
    public void testAddUser() throws IOException {
        SqlSession sqlSession = MybatisUtils.getSqlSession();

        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        int res = userMapper.addUser(new User(7,"herry","123"));
        if (res>0){
            System. Out. Println ("insert succeeded!");
        }

        //Commit transaction
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void testUpdateUser() throws IOException {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        sqlSession.getMapper(UserMapper.class).updateUser(new User(3,"qijian","111"));

        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void testDeleteUser() throws IOException {
        SqlSession sqlSession = MybatisUtils.getSqlSession();

        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

       userMapper.deleteUser(7);

        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void testAddByMap() throws IOException {
        SqlSession sqlSession = MybatisUtils.getSqlSession();

        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        //Empty map can easily insert data
        //Assuming that there are too many entity classes or tables, fields or parameters in the database, we should use map

        Map<String,Object> map = new HashMap<String, Object>();

        map.put("id",8);
        map.put("name","wangwu");
        map.put("pwd","1234");
        userMapper.addUserByMap(map);

        sqlSession.commit();
        sqlSession.close();

    }

//Pass the parameters in the map and directly get the key in the SQL
//Object passes parameters, and the properties of the object are directly obtained in SQL
//When there is only one basic type parameter, it can be obtained directly in SQL
//Multi parameter map

}

The problems encountered in the construction of the project are as follows:

Recommended Today

On the mutation mechanism of Clickhouse (with source code analysis)

Recently studied a bit of CH code.I found an interesting word, mutation.The word Google has the meaning of mutation, but more relevant articles translate this as “revision”. The previous article analyzed background_ pool_ Size parameter.This parameter is related to the background asynchronous worker pool merge.The asynchronous merge and mutation work in Clickhouse kernel is completed […]