Mybatis implements simple CRUD (addition, deletion, modification and query)

Database used:

CREATE DATABASE `mybatis`;
USE `mybatis`;
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;
insert  into `user`(`id`,`name`,`pwd`) values (1,'Reliable poplar','123456'),(2,'Zhang San','abcdef'),(3,'Li Si','987654');

Before using, import the Mybatis dependency package in Maven

<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.2</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.47</version>
</dependency>

The official MyBatis core configuration file mybatis-config.xml

<?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=utf8"/>
                <property name="username" value="root"/>
                <property name="password" value="000429"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="com/kuang/dao/userMapper.xml"/>
    </mappers>
</configuration>

be careful

It is likely that an error will be reported here. Maven needs to solve the problem of static resource filtering. Maven is used to the principle of greater than configuration

Add the following code to pom.xml file:

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

Note: refresh maven after configuration!

Simple data type User.java:

package com.kuang.pojo;
public class User {
	private int id;  //id
	private String name;   //full name
	private String pwd;   //password
	//Structure, with and without parameters
	//set/get
	//toString()

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

	public User(){}
	public int getId() {
		return id;
	}

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

	public User(int id, String name, String pwd) {

		this.id = id;
		this.name = name;
		this.pwd = pwd;
	}

	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;
	}
}

Data interface layer UserMapper.java:

public interface UserMapper {
	//Query all users
	List<User> getUserList();
	//Query user by ID
	User getUserByID(int id);
	//insert a user
	int addUser(User user);
	//update a user
	int updateUser(User user);
	//delete a user
	int deleteUser(User user);
}

User defined userMapper.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.kuang.dao.UserMapper">
    
    <select id="getUserList" resultType="com.kuang.pojo.User">
    select * from user
    </select>
    
    <select id="getUserByID" resultType="com.kuang.pojo.User" parameterType="int">
    select * from user WHERE id = #{id}
    </select>
    
    <!--The properties in the object can be extracted directly -->
    <insert id="addUser" parameterType="com.kuang.pojo.User">
        insert into mybatis.user (id,name,pwd) values (#{id},#{name},#{pwd});
    </insert>

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

Here, we can find that the data interface corresponds to xml. The xml file instances the interface and the methods in it. The namespace is the name of the interface, the select tag in it corresponds to a method in the interface, the id is the method name, and the resultType corresponds to the return value type of the method.

The package name of the xml file namespace should be consistent with the Dao/Mapper interface class name!

• id (getUserList): is the method name in the corresponding namespace (List getUserList();)
• resultType: is the return value after the SQL statement is executed! (Class, basic data type)
• parameterType: parameter type!
• delete from mybatis.user where id = #{id} analyze this sql statement. The assignment method is different from JDBC. The parameter in this format #{} is this method parameter. The member attribute ID in the User object can be directly put here!

The configuration tool class (MybatisUtils.java) obtains the connection object:

package com.kuang.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 {
	//Obtain an instance of SqlSession from SqlSessionFactory. SqlSession completely contains the execution method of SQL commands
	private static SqlSessionFactory sqlSessionFactory;
	static {
		try {
			String resource = "mybatis-config.xml";
			InputStream inputStream = Resources.getResourceAsStream(resource);
            //Read configuration file to get connection
			sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	//Returns the obtained SqlSession connection
	public static SqlSession getSession(){
		return sqlSessionFactory.openSession();
	}
}

Analyze this tool class:

First, the SqlSessionFactory class is a factory class that can generate PreparedStatement objects obtained from traditional JDBC methods. That is, to complete the same task as JDBC utils, the tool class first obtains the connection with the database. The first step is to read the mybatis-config.xml file configured by the user to obtain a database connection object, and then use this object to obtain an object of SqlSession class similar to PreparedStatement object.

The specific calling method (UserDaoTest.java) is given below:

import com.kuang.dao.UserMapper;
import com.kuang.pojo.User;
import com.kuang.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
public class UserDaoTest {
	@Test
	public void test(){
		//The sqlSession object is equivalent to a JDBC PreparedStatement object, which can execute sql statements
		//What we are doing now is: get the database connection, get the sqlSession object, and use this object to implement the data operation interface we defined
		//UserMapper, this interface executes specific methods with SQL statements by parsing the userMapper.xml file defined by us.
		SqlSession sqlSession = MybatisUtils.getSession();
		UserMapper mapper = sqlSession.getMapper(UserMapper.class);
//		List<User> userList = mapper.getUserList();
//		for (User user : userList)
//		{
//			System.out.println(user);
//		}
		//Get by ID
		/*User user=mapper.getUserByID(1);
		System.out.println(user);
		sqlSession.close();*/
		//Update a piece of data with id 1
//		updateUser();
		//Delete a user
		deleteUser();
	}
	public void addUser(){
		SqlSession sqlSession = MybatisUtils.getSession();
		UserMapper mapper = sqlSession.getMapper(UserMapper.class);
		//Insert a piece of data
		User user = new User(4, "name", "password");
		int res = mapper.addUser(user);
		if (res>0){
			System.out.println("Insert succeeded!");
		}
		//Commit transaction
		sqlSession.commit();
		sqlSession.close();
	}
	public void updateUser(){
		//Get the object connecting the coon and executing the sql statement, which is similar to the PreparedStatement of JDBC
		SqlSession sqlSession = MybatisUtils.getSession();
		//Implement UserMapper interface through reflection mechanism
		UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
		User user = new User(1, "Modified name", "resetpass01");
		userMapper.updateUser(user);
		sqlSession.commit();
		sqlSession.close();
	}

	public void deleteUser(){
		//Get connection
		SqlSession sqlSession = MybatisUtils.getSession();
		//Implementation interface
		UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
		//After implementing the interface, the mapper calls its own methods to execute sql statements
		userMapper.deleteUser(new User(1,"sss","123"));
		sqlSession.commit();
		sqlSession.close();
	}
}

Analyze these two lines of code:

	SqlSession sqlSession = MybatisUtils.getSession();
	UserMapper mapper = sqlSession.getMapper(UserMapper.class);

• The first line of code obtains an sqlSession object through the getSession method of the MybatisUtils class. This object is an object that can execute sql statements (similar to the PreparedStatement object of JDBC) that has been connected to the database
• This is not enough. It just stays at the tool class level. The next thing to do is to let this tool class implement the user-defined data layer interface, so that the corresponding sql statements can be executed according to the user-defined data operation methods, and then the corresponding result set can be obtained.