Easy to fix the increase, delete, change and check – MyBatis universal Mapper introduction and use

preface

Most developers using MyBatis will encounter a problem, is to write a large number of SQL in XML files, in addition to special business logic SQL, there are a large number of similar structure of add, delete, change query SQL. Also, when the database table structure changes, all the corresponding SQL and entity classes need to change. This impact of workload and efficiency may be the barrier that separates add-redaction programmers from real programmers. This is where the universal Mapper comes in…

What is a universal Mapper

How to use

Take MySQL as an example, suppose there is a table like this:

CREATE TABLE `test_table` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT '',
  `create_time` datetime DEFAULT NULL,
  `create_user_id` varchar(32) DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `update_user_id` varchar(32) DEFAULT NULL,
  `is_delete` int(8) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Copy the code

The primary key is ID, which is incremented. Here’s an example of how to use a generic Mapper.

Maven rely on

<! Mapper --> <dependency> <groupId>tk.mybatis</groupId> <artifactId> Mapper </artifactId> </dependency>Copy the code

For SpringMVC configuration

<! - general Mapper -- > < bean class = "tk. Mybatis. Spring. Mapper. MapperScannerConfigurer" > < property name = "basePackage" value="cn.com.bluemoon.bd.service.spider.dao"/> <property name="properties"> <value> mappers=tk.mybatis.mapper.common.Mapper </value> </property> </bean>Copy the code

Note here use tk. Mybatis. Spring. Mapper. MapperScannerConfigure replace the original mybatis org. Mybatis. Spring. Mapper. MapperScannerConfigurer.

Configurable parameters:

• UUID: Sets the method for generating UUID, which must be configured in OGNL mode. The returned value is not limited, but must match the field type
• IDENTITYHow to get the primary key back
• ORDER:<seletKey>The order attribute in, with optional values BEFORE and AFTER
• catalog: The catalog of the database. If this value is set, the table name will be prefixed with the catalog setting during query
• schema: Same as catalog. Catalog has a higher priority than Schema
• seqFormatNextval. For Oracle, there are four optional parameters, corresponding to 0,1, and 2, which are SequenceName, ColumnName, PropertyName, and TableName
• notEmptyInsert and update, whether to determine the string type! = “, a few methods will be used
• style: rules for converting entities and tables, default hump to underscore, optional value normal use entity name and field name; Camelhump is the default, with the hump underlined; Uppercase converts to uppercase; Lowercase converted to lowercase
• enableMethodAnnotation: Controls whether JPA annotations on methods are supported. The default is false.

These parameters are not used in most cases, and you can do your own research in special cases.

The way to write an entity class

@Table(name = "test_table") public class TestTableVO implements Serializable { private static final long serialVersionUID = 1L; @Id @GeneratedValue(generator = "JDBC") private Long id; @Transient private String userId; private String name; private Timestamp createTime; private String createUserId; private Timestamp updateTime; private String updateUserId; private Integer isDelete; // omit get, set... }Copy the code

DAOThe writing of

In the traditional Mybatis writing method, DAO interface needs to be associated with Mapper file, that is, SQL needs to be written to implement the methods in DAO interface. In a common Mapper, the DAO only needs to inherit a common interface to have rich methods:

To inherit a generic Mapper, you must specify generics

public interface TestTableDao extends Mapper<TestTableVO> {
}
Copy the code

Once a Mapper is inherited, the inherited Mapper has all the common methods of a Mapper:

List

Select (T record); Note: According to the attribute value of the entity to query, query conditions using equal sign

SelectByPrimaryKey (Object key); Note: To query by primary key field, method parameter must contain complete primary key attribute, query condition use equal sign

List

selectAll(); Select (NULL) to achieve the same effect

Method: T selectOne(T record); Note: according to the attributes of the entity for query, can only have a return value, there are more than one result is thrown exception, query conditions using equal sign

Int selectCount(T record); Note: According to the total number of attributes in the entity query, query conditions use equal sign

Int Insert (T record); Note: When an entity is saved, the null attribute will also be saved. The default database value will not be used

Methods: INT insertSelective(T Record); Note: To save an entity, the null attribute will not be saved, the database default value will be used

Update: int updateByPrimaryKey(T record); Note: Null values will be updated when all fields of the entity are updated according to the primary key

Methods: int updateByPrimaryKeySelective record (T); Note: Update values whose attributes are not NULL based on the primary key

Delete: int Delete (T record); Note: Delete according to entity attributes as a condition, query conditions using equal sign

Int deleteByPrimaryKey(Object key); Note: To delete by primary key field, the method parameter must contain the complete primary key attribute

List

selectByExample(Object Example); Key: This query supports the Example class to specify the query column, the selectProperties method to specify the query column

Method: int selectCountByExample(Object example); Query total number according to Example condition

Int updateByExample(@param (“record”) T record, @param (“example”) Object example); Note: Update all attributes contained in the entity Record according to the Example condition. Null values are updated

Int updateByExampleSelective(@param (“record”) T record, @param (“example”) Object example); Update entity Record containing property values that are not NULL according to the Example condition

Method: int deleteByExample(Object example); Delete data according to the Example condition

Use in code

@Autowired
private TestTableDao testTableDao;
Copy the code

TestTableVO vo = new TestTableVO(); // omit set properties for vo... int row = testTableDao.insertSelective(vo);Copy the code

TestTableVO vo = new TestTableVO(); // omit set properties for vo... int row = testTableDao.updateByPrimaryKeySelective(vo);Copy the code

TestTableVO vo = new TestTableVO();
vo.setId(123L);
TestTableVO result = testTableDao.selectOne(vo);
Copy the code

Example Example = new Example(testTablevo.class); // createCriteria example.criteria = example.createcriteria (); // Add criteria. AndEqualTo ("isDelete", 0); criteria.andLike("name", "%abc123%"); List<TestTableVO> list = testTableDao.selectByExample(example);Copy the code

conclusion

The principle of the universal Mapper is to obtain the information of the entity class through reflection, and construct the corresponding SQL, so we only need to maintain the entity class, for dealing with complex and changeable needs to provide a great convenience. The above mentioned is only a simple usage of the general Mapper. In actual projects, a larger granularity, more general and more convenient method should be encapsulated on the basis of the general Mapper according to the business.

With Spring Boot configuration

<! --mybatis--> <dependency> <groupId>org.mybatis.spring.boot</groupId> < artifactId > mybatis - spring - the boot - starter < / artifactId > < version > 1.3.1 < / version > < / dependency > <! --mapper--> <dependency> <groupId>tk.mybatis</groupId> <artifactId>mapper-spring-boot-starter</artifactId> The < version > 1.1.4 < / version > < / dependency >Copy the code

# # mapper mappers commas when multiple interface mapper.mappers=tk.mybatis.mapper.com mon. Mapper mapper. The not - empty = false mapper. The identity = MYSQLCopy the code

reference

• gitee.com/free/Mapper
• Github.com/abel533/MyB…