General CRUD operation of MyBatisPlus series

Posted by slibob on Mon, 13 Sep 2021 05:09:37 +0200

Part I: Environment integration of MyBatisPlus series

1, Getting started HelloWorld

1. Universal CRUD

(1) Ask questions:

  • Suppose we already have a tbl_employee table, and the corresponding entity class employee already exists, which implements TBL_ What do we need to do to CRUD the employee table?

(2) Implementation method:

Based on Mybatis

  • You need to write the EmployeeMapper interface and manually write the CRUD method
  • Provide the EmployeeMapper.xml mapping file, and manually write the SQL statement corresponding to each method
MP based
  • Just create the EmployeeMapper interface and inherit the BaseMapper interface. This is using MP
  • You don't even need to create an SQL mapping file.

2. Coding implementation

2.1. General method insertion

(1) Create an EmployeeMapper interface

package org.apache.mybatisplus.mapper;

import com.baomidou.mybatisplus.mapper.BaseMapper;
import org.apache.mybatisplus.bean.Employee;

/**
 * Mapper Interface
 * 	
 * Based on Mybatis: write CRUD related methods in Mapper interface, and provide SQL mapping files corresponding to Mapper interface and SQL statements corresponding to methods 
 * 
 * Based on MP: let the xxmapper interface inherit the BaseMapper interface
 * 		   BaseMapper<T> : The generic type specifies the entity class type operated by the current Mapper interface 
 * 
 */
public interface EmployeeMapper extends BaseMapper<Employee> {
   
}

Must be curious about the use of inheriting BaseMapper? When we click in to check, we find that it implements the following interface methods:

/**
     * <p>
     * Insert a record
     * </p>
     *
     * @param entity Entity object
     * @return int
     */
    Integer insert(T entity);

    /**
     * <p>
     * Insert a record
     * </p>
     *
     * @param entity Entity object
     * @return int
     */
    Integer insertAllColumn(T entity);

    /**
     * <p>
     * Delete by ID
     * </p>
     *
     * @param id Primary key ID
     * @return int
     */
    Integer deleteById(Serializable id);

    /**
     * <p>
     * Delete the record according to the columnMap condition
     * </p>
     *
     * @param columnMap Table field map object
     * @return int
     */
    Integer deleteByMap(@Param("cm") Map<String, Object> columnMap);

    /**
     * <p>
     * Delete the record according to the entity condition
     * </p>
     *
     * @param wrapper Entity object encapsulates operation class (null able)
     * @return int
     */
    Integer delete(@Param("ew") Wrapper<T> wrapper);

    /**
     * <p>
     * Delete (batch delete according to ID)
     * </p>
     *
     * @param idList List of primary key ID S
     * @return int
     */
    Integer deleteBatchIds(@Param("coll") Collection<? extends Serializable> idList);

    /**
     * <p>
     * Modify according to ID
     * </p>
     *
     * @param entity Entity object
     * @return int
     */
    Integer updateById(@Param("et") T entity);

    /**
     * <p>
     * Modify according to ID
     * </p>
     *
     * @param entity Entity object
     * @return int
     */
    Integer updateAllColumnById(@Param("et") T entity);

    /**
     * <p>
     * Update the record according to the whereEntity condition
     * </p>
     *
     * @param entity  Entity object
     * @param wrapper Entity object encapsulates operation class (null able)
     * @return
     */
    Integer update(@Param("et") T entity, @Param("ew") Wrapper<T> wrapper);

    /**
     * <p>
     * Update the record according to the whereEntity condition
     * </p>
     *
     * @param setStr  set character string
     * @param wrapper Entity object encapsulates operation class (null able)
     * @return
     */
    Integer updateForSet(@Param("setStr") String setStr, @Param("ew") Wrapper<T> wrapper);

    /**
     * <p>
     * Query by ID
     * </p>
     *
     * @param id Primary key ID
     * @return T
     */
    T selectById(Serializable id);

    /**
     * <p>
     * Query (batch query by ID)
     * </p>
     *
     * @param idList List of primary key ID S
     * @return List<T>
     */
    List<T> selectBatchIds(@Param("coll") Collection<? extends Serializable> idList);

    /**
     * <p>
     * Query (based on columnMap criteria)
     * </p>
     *
     * @param columnMap Table field map object
     * @return List<T>
     */
    List<T> selectByMap(@Param("cm") Map<String, Object> columnMap);

    /**
     * <p>
     * Query a record according to the entity condition
     * </p>
     *
     * @param entity Entity object
     * @return T
     */
    T selectOne(@Param("ew") T entity);

    /**
     * <p>
     * Query the total number of records according to Wrapper conditions
     * </p>
     *
     * @param wrapper Entity object
     * @return int
     */
    Integer selectCount(@Param("ew") Wrapper<T> wrapper);

    /**
     * <p>
     * Query all records according to the entity condition
     * </p>
     *
     * @param wrapper Entity object encapsulates operation class (null able)
     * @return List<T>
     */
    List<T> selectList(@Param("ew") Wrapper<T> wrapper);

    /**
     * <p>
     * Query all records according to Wrapper conditions
     * </p>
     *
     * @param wrapper Entity object encapsulates operation class (null able)
     * @return List<T>
     */
    List<Map<String, Object>> selectMaps(@Param("ew") Wrapper<T> wrapper);

    /**
     * <p>
     * Query all records according to Wrapper conditions
     * Note: only the value of the first field is returned
     * </p>
     *
     * @param wrapper Entity object encapsulates operation class (null able)
     * @return List<Object>
     */
    List<Object> selectObjs(@Param("ew") Wrapper<T> wrapper);

    /**
     * <p>
     * Query all records (and turn the page) according to the entity condition
     * </p>
     *
     * @param rowBounds Paging query criteria (can be rowboundaries. Default)
     * @param wrapper   Entity object encapsulates operation class (null able)
     * @return List<T>
     */
    List<T> selectPage(RowBounds rowBounds, @Param("ew") Wrapper<T> wrapper);

    /**
     * <p>
     * Query all records (and turn pages) according to Wrapper conditions
     * </p>
     *
     * @param rowBounds Paging query criteria (can be rowboundaries. Default)
     * @param wrapper   Entity object encapsulation operation class
     * @return List<Map<String, Object>>
     */
    List<Map<String, Object>> selectMapsPage(RowBounds rowBounds, @Param("ew") Wrapper<T> wrapper);

(2) Problem with test insert operation

package org.apache.test;


import org.apache.mybatisplus.bean.Employee;
import org.apache.mybatisplus.mapper.EmployeeMapper;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import javax.sql.DataSource;
import java.sql.Connection;


public class TestMP {


	private ApplicationContext ioc =
				new ClassPathXmlApplicationContext("applicationContext.xml");

	private EmployeeMapper employeeMapper =
			ioc.getBean("employeeMapper",EmployeeMapper.class);

	/**
	 * Universal insert operation
	 */
	@Test
	public void testCommonInsert() {
		//Initialize Employee object
		Employee employee  = new Employee();
		employee.setLastName("MP");
		employee.setEmail("mp@apache.mybatis-plus.com");
		employee.setGender(1);
		employee.setAge(22);
		// When the insert method is inserted, it will make non empty judgment according to each attribute of the entity class. Only the fields corresponding to non empty attributes will appear in the SQL statement
		Integer result =employeeMapper.insert(employee);
		System.out.println("result: " + result );

	}

	@Test
	public void testEnvironment() throws Exception {
		DataSource ds = ioc.getBean("dataSource", DataSource.class);
		Connection conn = ds.getConnection();
		System.out.println(conn);

	}
	
}

  Startup program error

org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.reflection.ReflectionException: Could not set 
property 'id' of 'class org.apache.mybatisplus.bean.Employee' 
with value '1437004223093944322' Cause: java.lang.IllegalArgumentException: 
argument type mismatch

 

Analysis reason: MybatisPlus will use the class name of the entity class by default to find the corresponding table in the data

Solution: you need to add a @ TableId annotation to the primary key id of the entity class and specify the primary key policy

  Run again and find another exception

Solution: add @ TableName(value="tbl_employee") on the entity class

  Run again and find that the data has been inserted

  Above description: MyBatisPlus insert ok

2.2. Global policy configuration

(1) First, in addition to configuring the following parameters:

<!--TODO  definition MybatisPlus Global policy configuration for-->
	<bean id ="globalConfiguration" class="com.baomidou.mybatisplus.entity.GlobalConfiguration">
		<!-- In 2.3 After the version, dbColumnUnderline The default is true -->
		<property name="dbColumnUnderline" value="true"></property>

		<!-- Global primary key policy -->
		<property name="idType" value="0"></property>

		<!-- Global table prefix policy configuration -->
		<property name="tablePrefix" value="tbl_"></property>
	</bean>

(2) Secondly, it needs to be injected into the injection global MP policy configuration, otherwise it will not take effect

As shown in the figure:

  (3) When the parameter takes effect, we do not need to add @ TableName and @ TableId annotations to the entity class

(4) Console printout information:

 

2.3 Annotation "@ TableField"  

In the actual development process, we know that after version 2.3, the default value of dbColumnUnderline is true. Review

In the past, it is meaningless to discuss it again. For example, before version 2.3, this feature is not supported, and its default value is false, some bug s will be encountered in development problems.

The above shows that the hump naming of database and entity class is inconsistent

To solve this problem: @ TableField can solve it

Insert data to get primary key value  

 

Sometimes, the database does not have this field, and we are artificially bug set   There will be some errors

To solve this problem, we can use the annotation "@ TableField" in the entity class to exclude our own judgment to solve this problem

2.4 insertAllColumn method

package org.apache.test;


import org.apache.mybatisplus.bean.Employee;
import org.apache.mybatisplus.mapper.EmployeeMapper;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import javax.sql.DataSource;
import java.sql.Connection;


public class TestMP {


	private ApplicationContext ioc =
				new ClassPathXmlApplicationContext("applicationContext.xml");

	private EmployeeMapper employeeMapper =
			ioc.getBean("employeeMapper",EmployeeMapper.class);

	/**
	 * Universal insert operation
	 */
	@Test
	public void testCommonInsert() {
		//Initialize Employee object
		Employee employee  = new Employee();
		employee.setLastName("MP");
		employee.setEmail("mp@apache.mybatis-plus.com");
		
		// When the insert method is inserted, it will make non empty judgment according to each attribute of the entity class. Only the fields corresponding to non empty attributes will appear in the SQL statement
		employee.setSalary(2000.0);
		Integer result = employeeMapper.insertAllColumn(employee);

		System.out.println("result: " + result );
		

		//Gets the primary key value of the current data in the database
		Integer key = employee.getId();
		System.out.println("key:" + key );

	}

	@Test
	public void testEnvironment() throws Exception {
		DataSource ds = ioc.getBean("dataSource", DataSource.class);
		Connection conn = ds.getConnection();
		System.out.println(conn);

	}
	
}

Printout information:

Note: the difference between insert and insertAllColumn

When the insert method is inserted, it will make non empty judgment according to each attribute of the entity class. Only the fields corresponding to non empty attributes will appear in the SQL statement

When insertAllColumn method is inserted, the field corresponding to the attribute will appear in the SQL statement regardless of whether the attribute is non empty or not

 

3. Update operation

package org.apache.test;


import org.apache.mybatisplus.bean.Employee;
import org.apache.mybatisplus.mapper.EmployeeMapper;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import javax.sql.DataSource;
import java.sql.Connection;


public class TestMP {


	private ApplicationContext ioc =
				new ClassPathXmlApplicationContext("applicationContext.xml");

	private EmployeeMapper employeeMapper =
			ioc.getBean("employeeMapper",EmployeeMapper.class);


	/**
	 * General update operation
	 */
	@Test
	public void testCommonUpdate() {
		//Initialize modified object
		Employee employee = new Employee();
		employee.setId(7);
		employee.setLastName("Mr Ozawa");
		employee.setEmail("xz@sina.com");
		employee.setGender(0);
	

		Integer result = employeeMapper.updateAllColumnById(employee);

		System.out.println("result: " + result );
	}


	@Test
	public void testEnvironment() throws Exception {
		DataSource ds = ioc.getBean("dataSource", DataSource.class);
		Connection conn = ds.getConnection();
		System.out.println(conn);

	}
	
}

Printout information:

4. Query operation

4.1. Operate through the selectById method

Code implementation:

package org.apache.test;


import org.apache.mybatisplus.bean.Employee;
import org.apache.mybatisplus.mapper.EmployeeMapper;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import javax.sql.DataSource;
import java.sql.Connection;


public class TestMP {
	private ApplicationContext ioc =
				new ClassPathXmlApplicationContext("applicationContext.xml");
	private EmployeeMapper employeeMapper =
			ioc.getBean("employeeMapper",EmployeeMapper.class);

	/**
	 * General query operation
	 */
	@Test
	public void  testCommonSelect() {
		//1. Query by id
		Employee employee = employeeMapper.selectById(7);
		System.out.println(employee);

	}

	@Test
	public void testEnvironment() throws Exception {
		DataSource ds = ioc.getBean("dataSource", DataSource.class);
		Connection conn = ds.getConnection();
		System.out.println(conn);

	}
}

Printout:

 

4.2. Operate through selectOne method

Code implementation:

package org.apache.test;


import org.apache.mybatisplus.bean.Employee;
import org.apache.mybatisplus.mapper.EmployeeMapper;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import javax.sql.DataSource;
import java.sql.Connection;


public class TestMP {


	private ApplicationContext ioc =
				new ClassPathXmlApplicationContext("applicationContext.xml");

	private EmployeeMapper employeeMapper =
			ioc.getBean("employeeMapper",EmployeeMapper.class);

	/**
	 * General query operation
	 */
	@Test
	public void  testCommonSelect() {
		
		//2. Query through multiple columns ID + LastName
		Employee  employee = new Employee();
     	employee.setId(7);
		employee.setLastName("Mr Ozawa");

		System.out.println("result: " +result );
	}

	@Test
	public void testEnvironment() throws Exception {
		DataSource ds = ioc.getBean("dataSource", DataSource.class);
		Connection conn = ds.getConnection();
		System.out.println(conn);

	}
	
}

Printout:

  For example, if you query multiple

  There will be multiple conditions

4.3. Batch query is realized by selectBatchIds method  

Code implementation:

package org.apache.test;


import org.apache.mybatisplus.bean.Employee;
import org.apache.mybatisplus.mapper.EmployeeMapper;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import javax.sql.DataSource;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;


public class TestMP {


	private ApplicationContext ioc =
				new ClassPathXmlApplicationContext("applicationContext.xml");

	private EmployeeMapper employeeMapper =
			ioc.getBean("employeeMapper",EmployeeMapper.class);

	/**
	 * General query operation
	 */
	@Test
	public void  testCommonSelect() {
		//3. Query through multiple IDS < foreach >
		List<Integer> idList = new ArrayList<>();
		idList.add(4);
		idList.add(5);
		idList.add(6);
		idList.add(7);
		List<Employee> emps = employeeMapper.selectBatchIds(idList);
		System.out.println(emps);

	}
		
	@Test
	public void testEnvironment() throws Exception {
		DataSource ds = ioc.getBean("dataSource", DataSource.class);
		Connection conn = ds.getConnection();
		System.out.println(conn);

	}
	
}

Printout:

 

4.4. Condition query of selectByMap method

Code implementation:

package org.apache.test;


import org.apache.mybatisplus.bean.Employee;
import org.apache.mybatisplus.mapper.EmployeeMapper;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import javax.sql.DataSource;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


public class TestMP {


	private ApplicationContext ioc =
				new ClassPathXmlApplicationContext("applicationContext.xml");

	private EmployeeMapper employeeMapper =
			ioc.getBean("employeeMapper",EmployeeMapper.class);
	
	/**
	 * General query operation
	 */
	@Test
	public void  testCommonSelect() {
		//4. Query by Map encapsulation condition
		Map<String,Object> columnMap = new HashMap<>();
		columnMap.put("last_name", "Tom");
		columnMap.put("gender", 1);

		List<Employee> emps = employeeMapper.selectByMap(columnMap);
		System.out.println(emps);

	}

	@Test
	public void testEnvironment() throws Exception {
		DataSource ds = ioc.getBean("dataSource", DataSource.class);
		Connection conn = ds.getConnection();
		System.out.println(conn);

	}
}

Printout:

sql statements printed on the analysis console:

SELECT id,last_name AS lastName,email,gender,age
 FROM tbl_employee WHERE gender = ? AND last_name = ? 

 

4.5. Realize paging query through selectPage method

Query the BaseMapper class and the implementation interface of the source code [find out]

  Coding implementation:

package org.apache.test;


import com.baomidou.mybatisplus.plugins.Page;
import org.apache.mybatisplus.bean.Employee;
import org.apache.mybatisplus.mapper.EmployeeMapper;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import javax.sql.DataSource;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


public class TestMP {


	private ApplicationContext ioc =
				new ClassPathXmlApplicationContext("applicationContext.xml");

	private EmployeeMapper employeeMapper =
			ioc.getBean("employeeMapper",EmployeeMapper.class);


	/**
	 * General query operation
	 */
	@Test
	public void  testCommonSelect() {
		
		//5. Query by page, divided into 3 pages, and query 2 pieces of data per page
		List<Employee> emps = employeeMapper.selectPage(new Page<>(3, 2), null);
		System.out.println(emps);
	}

	@Test
	public void testEnvironment() throws Exception {
		DataSource ds = ioc.getBean("dataSource", DataSource.class);
		Connection conn = ds.getConnection();
		System.out.println(conn);

	}
}

Printout

  sql statements printed on the analysis console:

SELECT id,last_name AS lastName,
email,gender,age FROM tbl_employee

5. Delete operation

5.1. Use the deleteById method

Coding implementation:

package org.apache.test;


import com.baomidou.mybatisplus.plugins.Page;
import org.apache.mybatisplus.bean.Employee;
import org.apache.mybatisplus.mapper.EmployeeMapper;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import javax.sql.DataSource;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


public class TestMP {


	private ApplicationContext ioc =
				new ClassPathXmlApplicationContext("applicationContext.xml");

	private EmployeeMapper employeeMapper =
			ioc.getBean("employeeMapper",EmployeeMapper.class);


	/**
	 * General delete operation
	 */
	@Test
	public void testCommonDelete() {
		//1. Delete by id
		Integer result = employeeMapper.deleteById(11);
		System.out.println("result: " + result );


	@Test
	public void testEnvironment() throws Exception {
		DataSource ds = ioc.getBean("dataSource", DataSource.class);
		Connection conn = ds.getConnection();
		System.out.println(conn);

	}
	
}

  Printout:

  sql statements printed on the analysis console:

DELETE FROM tbl_employee WHERE id=? 

  5.2. Delete conditions through the deleteByMap method

Coding implementation:

package org.apache.test;


import com.baomidou.mybatisplus.plugins.Page;
import org.apache.mybatisplus.bean.Employee;
import org.apache.mybatisplus.mapper.EmployeeMapper;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import javax.sql.DataSource;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


public class TestMP {


	private ApplicationContext ioc =
				new ClassPathXmlApplicationContext("applicationContext.xml");

	private EmployeeMapper employeeMapper =
			ioc.getBean("employeeMapper",EmployeeMapper.class);


	/**
	 * General delete operation
	 */
	@Test
	public void testCommonDelete() {
		//2. Delete according to conditions
		Map<String,Object> columnMap = new HashMap<>();
		columnMap.put("last_name", "MP");
		columnMap.put("email", "mp@apache.mybatis-plus.com");
		Integer result = employeeMapper.deleteByMap(columnMap);
		System.out.println("result: " + result );

	}

	@Test
	public void testEnvironment() throws Exception {
		DataSource ds = ioc.getBean("dataSource", DataSource.class);
		Connection conn = ds.getConnection();
		System.out.println(conn);

	}
}

Printout: [delete 4 pieces of data]

  sql statements printed on the analysis console:

DELETE FROM tbl_employee WHERE last_name = ? AND email = ? 

  5.3. Batch deletion is realized through the deleteBatchIds method

Coding implementation:

package org.apache.test;


import com.baomidou.mybatisplus.plugins.Page;
import org.apache.mybatisplus.bean.Employee;
import org.apache.mybatisplus.mapper.EmployeeMapper;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import javax.sql.DataSource;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


public class TestMP {


	private ApplicationContext ioc =
				new ClassPathXmlApplicationContext("applicationContext.xml");

	private EmployeeMapper employeeMapper =
			ioc.getBean("employeeMapper",EmployeeMapper.class);

	/**
	 * General delete operation
	 */
	@Test
	public void testCommonDelete() {


		//3. Batch deletion
		List<Integer> idList = new ArrayList<>();
		idList.add(3);
		idList.add(4);
		idList.add(5);
		Integer result = employeeMapper.deleteBatchIds(idList);
		System.out.println("result: " + result );
	}


	@Test
	public void testEnvironment() throws Exception {
		DataSource ds = ioc.getBean("dataSource", DataSource.class);
		Connection conn = ds.getConnection();
		System.out.println(conn);

	}
	
}

Console printout:

 

 

 

Topics: Vue.js html linear algebra