QA From Shallow to Deep Persistent Layer Framework (11) – MyBatis Universal Mapper (middle)

“This is my 36th day of participating in the First Challenge 2022. For details: First Challenge 2022”

Two, universal Mapper to achieve basic increase, delete, change and check -BaseMapper

2.5 Insert related methods

// When an entity is saved, the null attribute is also saved. The database default value is not used
int insert(T record);
// To save an entity, the null attribute is not saved, the database default value is used
int insertSelective(T record);
Copy the code

2.5.1 insert method

Add the save method to PorscheService

void save(Porsche porsche);
Copy the code

Save method is realized in PorscheServiceImpl

@Override
public void save(Porsche porsche) {
    porscheMapper.insert(porsche);
}
Copy the code

Test the save method in PorscheServiceTest

@Test
public void save(a){
    // Construct the inserted entity class
    Porsche porsche = new Porsche();
    porsche.setPorName("Panamera 4S 2022");
    porsche.setPorPrice(880000d);
    porsche.setPorStock(10);

    porscheService.save(porsche);
}
Copy the code

Execute the save test methodThe database updated a row and the insert succeeded. Add the test code, print the primary key increment, and run the test again

The output primary key is null. The program does not obtain the primary key generated after the successful insertion. To obtain the generated primary key Value, add @GeneratedValue(Strategy = GenerationType.identity) to the attributes corresponding to the Porsche entity class and the data primary key to perform the test again

Two SQL statements were executed, one was an insert statement, and the other was to query the Value of the latest primary key. The console successfully output the Value of the auto-increment primary key generated after the insert.

The @GeneratedValue annotation allows the generic Mapper to write back the value of the auto-increment primary key generated by the database to the entity class after an INSERT operation

2.5.2 insertSelective method

Add savePorscheSelective method to PorscheService

void savePorscheSelective(Porsche porsche);
Copy the code

Add savePorscheSelective method in PorscheServiceImpl

@Override
public void savePorscheSelective(Porsche porsche) {
    porscheMapper.insertSelective(porsche);
}
Copy the code

Add savePorscheSelective test method

@Test
public void savePorscheSelective(a){
    Porsche porsche = new Porsche();
    porsche.setPorName("Porsche 911 2022");

    porscheService.savePorscheSelective(porsche);
}
Copy the code

Non-primary key fields that are null will not be added to the SQL statement

This is suitable for cases where there are many attributes in the entity class, but less data is inserted into the NULL part of the attribute when the insert is performed

2.6 Update Related methods

// Update all fields of the entity according to the primary key, null values are updated
int updateByPrimaryKey(T record);
// Update values whose attributes are not NULL based on the primary key
int updateByPrimaryKeySelective(T record);
Copy the code

2.6.1 updateByPrimaryKeySelective

Add updatePorscheSelective method to PorscheService

void updatePorscheSelective(Porsche porsche);
Copy the code

Add the implementation of updatePorscheSelective method in PorscheServiceImpl

@Override
public void updatePorscheSelective(Porsche porsche) {
    porscheMapper.updateByPrimaryKeySelective(porsche);
}
Copy the code

Added the updatePorscheSelective test method

@Test
public void updatePorscheSelective(a){
    Porsche porsche = new Porsche();
    porsche.setPorId(1);
    porsche.setPorPrice(900000d);

    porscheService.updatePorscheSelective(porsche);
}
Copy the code

Perform the test

Entity classes that update for partial fields use this method to update for fields with values, while fields without values remain unchanged

2.7 Delete Related methods

// Delete according to entity attribute as condition, query condition using equal sign
int delete(T record);
// Delete by primary key field. The method parameter must contain the full primary key attribute
int deleteByPrimaryKey(Object key);
Copy the code

2.7.1 delete

Add removePorsche method to PorscheService

void removePorsche(Porsche porsche);
Copy the code

Added an implementation of the removePorsche method in PorscheServiceImpl

@Override
public void removePorsche(Porsche porsche) {
    porscheMapper.delete(porsche);
}
Copy the code

Added removePorsche test method

@Test
public void removePorsche(a){
    // Construct the entity class to be deleted
    Porsche porsche = new Porsche();
    porsche.setPorName("Panamera 4S 2022");
    porsche.setPorPrice(880000d);
    porsche.setPorStock(10);

    porscheService.removePorsche(porsche);
}
Copy the code

Perform the test

If the object passed in is NULL, comment out setter methods for the object and run the test again

Data is deleted completely, use this method must be careful, to make conditional judgment

2.7.2 deleteByPrimaryKey

Add removePorscheById method to PorscheService

void removePorscheById(Integer id);
Copy the code

Add removePorscheById method implementation in PorscheServiceImpl

@Override
public void removePorscheById(Integer id) {
    porscheMapper.deleteByPrimaryKey(id);
}
Copy the code

Added the removePorscheById test method

@Test
public void removePorscheById(a){
    // Delete the primary key of the record
    Integer id = 1;
    porscheService.removePorscheById(id);
}
Copy the code

Perform the test

DeleteByPrimaryKey the most common method of deleting

Mapper -ExampleMapper -ExampleMapper

3.1 QBC query

QBC is Query By Criteria, QBC Query is the Query conditions through Java objects for modular encapsulation.

Criterion Criterion is an internal class. Criterion is the plural form of Criterion. The Criterion internal class code is the same as that of CatExample, the entity class that MBG generates complex query criteria, and also provides the createCriteria method to create composite query criteria

MBG generates the XXXExample class that encapsulates the query criteria versus the Example class of the generic MapperBoth encapsulate a large number of query criteria. MBG generates XXXExample directly for the Entity class, such as CatExample and TeacherExample generated. When a generic Mapper uses conditional queries, it needs to pass in an entity class to be queried when creating an Example

Example porscheExample = new Example(Porsche.class);
Copy the code

3.1.1 Querying Information Using QBC

Use QBC to create the following composite query

WHERE (por_id > 1 and por_stock > 20) OR ( por_price < 1000000 and por_stock > 20)
Copy the code

Increase in PorscheService getPorschesByPriceAndStock method

List<Porsche> getPorschesByPriceAndStock(Example porscheExample);
Copy the code

The implementation of the increase in PorscheServiceImpl getPorschesByPriceAndStock method

@Override
public List<Porsche> getPorschesByPriceAndStock(Example porscheExample) {

    return porscheMapper.selectByExample(porscheExample);
}
Copy the code

Increase in PorscheServiceTest getPorscheByPriceAndStock test method, the process of the construction of the query conditions to view comments.

@Test
public void getPorscheByPriceAndStock(a){
    Example porscheExample = new Example(Porsche.class);
    // WHERE (por_id > 1 and por_stock > 20) OR ( por_price < 1000000 and por_stock > 20)
    // Create two query criteria. The two brackets following the WHERE keyword correspond to two query criteria
    Example.Criteria criteria1 = porscheExample.createCriteria();
    Example.Criteria criteria2 = porscheExample.createCriteria();
    // Set two query criteria
    criteria1.andGreaterThan("porId".1)
            .andGreaterThan("porStock".20);

    criteria2.andLessThan("porPrice".1000000d)
            .andGreaterThan("porStock".20);

    // join query criteria1 --criteria1 and query criteria2 --criteria2 via OR
    // porschExample is equivalent to the select column name from WHERE criteria 1
    // Just call the OR method to concatenate criteria2 to form the completed SQL statement
    // Select field name from indicates WHERE (criteria1) OR (criteria2)
    porscheExample.or(criteria2);

    List<Porsche> porscheList = porscheService.getPorschesByPriceAndStock(porscheExample);
    for (Porsche porsche : porscheList) {
        System.out.println("The query output is:"+ porsche); }}Copy the code

Perform the test

3.1.2 Other QBC Settings

QBC query can be set at the same time, such as sorting, to heavy, set the fields of the query, add the code in the getPorscheByPriceAndStock test method,

porscheExample.setDistinct(true);
porscheExample.orderBy("porPrice").asc().orderBy("porName").desc();
porscheExample.selectProperties("porName"."porPrice");
Copy the code

Run the test againView the SQL statement deleted by the console, and the compound condition built takes effect when the SQL is executed.

4, universal Mapper implementation of pagination -RowBoundsMapper

4.1 Paging related methods

// Paging queries based on the Example condition and RowBounds
List<T> selectByExampleAndRowBounds(Object example, RowBounds rowBounds);

// Paging queries based on entity attributes and RowBounds
List<T> selectByRowBounds(T record, RowBounds rowBounds);
Copy the code

4.1.1 selectByRowBounds

Added paging query method getPorschesByRowBounds to the PorscheService interface

List<Porsche> getPorschesByRowBounds(Porsche record, RowBounds rowBounds);
Copy the code

Implement paging query method getPorschesByRowBounds in PorscheServiceImpl

@Override
public List<Porsche> getPorschesByRowBounds(Porsche record, RowBounds rowBounds) {

    return porscheMapper.selectByRowBounds(record,rowBounds);
}
Copy the code

Add the test code for this method

@Test
public void getPorschesByRowBounds(a){
    int pageNo = 2;
    int pageSize = 3;

    int index = (pageNo - 1) * pageSize;

    RowBounds rowBounds = new RowBounds(index, pageSize);

    List<Porsche> porscheList = porscheService.getPorschesByRowBounds(null, rowBounds);

    for (Porsche porsche : porscheList) {
        System.out.println("The query output is:"+ porsche); }}Copy the code

Perform the test

Mapper is not recommended to use this pagination query. It is recommended to use PageHelper plug-in to achieve real pagination

4.2 @ Transient

In general, attributes in an entity class correspond to fields in a database table, but some attributes may not have corresponding fields in the database. In this case, you need to use the @TRANSIENT annotation to indicate that this is not a field in the table.

Reverse engineering of universal Mapper

5.1 Reverse engineering of universal Mapper

The code generated by the reverse engineering of universal Mapper is slightly different from the code generated by the native MyBatis. First of all, it contains the Entity Entity class, XxxMapper interface and xxxmapper.xml. However, the Entity class generated by the generic Mapper will contain annotations such as @Table, @Column, @ID, and @GeneratedValue. The generated XxxMapper interface inherits the Mapper<Xxx> class; There are no SQL statements in the generated xxxmapper.xml, only a resultMap tag.

5.2 Reverse engineering using universal Mapper

Create a project common-mapper-MBG, and the dependencies, Spring configuration and MyBatis configuration used by the project are the same as those used by the general-Mapper project. The difference is that the new General-Mapper-MBG project does not contain the Entity class mapper interface, Service class and test class, etc.

In common-mapper-MBG project, we add the code generator dependency and Maven plug-in to the pop. XML file. We use maven plug-in to execute the code generator to generate code.

<dependency>
    <groupId>tk.mybatis</groupId>
    <artifactId>mapper-generator</artifactId>
    <version>1.0.0</version>
</dependency>
Copy the code

Added Generatorconfig.xml to the resources directory

<! DOCTYPEgeneratorConfiguration
        PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
        "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">

<generatorConfiguration>
    <properties resource="db.properties"/>

    <context id="Mysql" targetRuntime="MyBatis3Simple" defaultModelType="flat">
        <property name="beginningDelimiter" value="`"/>
        <property name="endingDelimiter" value="`"/>

        <! -- MBG plugin for Mapper
        <plugin type="tk.mybatis.mapper.generator.MapperPlugin">
            <property name="mappers" value="tk.mybatis.mapper.common.Mapper"/>
            <property name="caseSensitive" value="true"/>
        </plugin>

        <jdbcConnection driverClass="${jdbc_driver}"
                        connectionURL="${jdbc_url}"
                        userId="${jdbc_username}"
                        password="${jdbc_password}">
        </jdbcConnection>

        <javaModelGenerator targetPackage="com.citi.entity"
                            targetProject="./src/main/java"/>

        <sqlMapGenerator targetPackage="mappers"
                         targetProject="./src/main/resources"/>

        <javaClientGenerator targetPackage="com.citi.mapper"
                             targetProject="./src/main/java"
                             type="XMLMAPPER"/>
        <! --tableName="%" -->
        <table tableName="t_teacher" domainObjectName="Teacher">
            <generatedKey column="id" sqlStatement="Mysql"/>
        </table>

    </context>
</generatorConfiguration>
Copy the code

Add plug-ins to pom.xml

<build>
    <plugins>
        <plugin>
            <groupId>org.apache.maven.plugins</groupId>
            <artifactId>maven-compiler-plugin</artifactId>
            <version>3.8.1</version>
            <configuration>
                <source>1.8</source>
                <target>1.8</target>
                <encoding>UTF-8</encoding>
            </configuration>
        </plugin>
        <plugin>
            <groupId>org.mybatis.generator</groupId>
            <artifactId>mybatis-generator-maven-plugin</artifactId>
            <version>1.3.7</version>
            <configuration>
                <configurationFile>
                    ${basedir}/src/main/resources/generatorConfig.xml
                </configurationFile>
                <overwrite>true</overwrite>
                <verbose>true</verbose>
            </configuration>
            <dependencies>
                <dependency>
                    <groupId>mysql</groupId>
                    <artifactId>mysql-connector-java</artifactId>
                    <version>8.0.16</version>
                </dependency>
                <dependency>
                    <groupId>tk.mybatis</groupId>
                    <artifactId>mapper</artifactId>
                    <version>4.0.0</version>
                </dependency>
            </dependencies>
        </plugin>
    </plugins>
</build>
Copy the code

MBG to run the universal Mapper

The code generator ran successfully

5.3 Testing code generated by the Universal Mapper

Create a new TeacherMapper test class generated by the code generator

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = "classpath:application.xml")
public class TeacherMapperTest {

    @Resource
    private TeacherMapper teacherMapper;

    @Test
    public void selectAll(a){
        List<Teacher> teachers = teacherMapper.selectAll();
        for (Teacher teacher : teachers) {
            System.out.println("The query output is :"+ teacher.getTeacherName()); }}}Copy the code

Execute the testConsole output query content successfully, universal Mapper reverse engineering generated code verification success.