Mybatis interface development exercise

– 1. Requirement: query all data in the car table

– 2, development steps

Prepare tables and data

DROP TABLE IF EXISTS `car`;
CREATE TABLE `car` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(10) default NULL,
  `color` varchar(10) default NULL,
  `price` double default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `car` VALUES ('1', 'BMW', 'red', '9.9');
INSERT INTO `car` VALUES ('2', 'Audi', 'black', '0.3');

Create Car class

package cn.tedu.pojo;
//Encapsulates the data orm returned from the car table
public class Car {
    private Integer id;
    private String name;
    private String color;
    private Double price;

    @Override
    public String toString() {
        return "Car{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", color='" + color + '\'' +
                ", price=" + price +
                '}';
    }

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public String getColor() {
        return color;
    }

    public void setColor(String color) {
        this.color = color;
    }

    public Double getPrice() {
        return price;
    }

    public void setPrice(Double price) {
        this.price = price;
    }
}

Create CarMapper interface

package cn.tedu.dao;
import cn.tedu.pojo.Car;
import java.util.List;
//Various methods for manipulating the car table
public interface CarMapper {
    List<Car> selectList();//Check all data
}

Create carmapper XML Mapping File

<?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 Is the root label of the mapping file,
    namespace Properties are used as mapper Unique identification of the file
    namespace The value of the property is the full path of the interface
-->
<mapper namespace="cn.tedu.dao.CarMapper">
    <!--select Used to mark that this is a query SQL sentence
        id Attribute is SQL Unique identification of
        id The value of the property is the corresponding method name in the interface
        resultType The value of the attribute is the full path of which class to encapsulate the query results
    -->
    <select id="selectList" resultType="cn.tedu.pojo.Car">
        select * from car
    </select>
</mapper>

Modify mybatis config XML, add mapping file

<!-- It is used to import mapping files, and the underlying maintenance MapperRegistry，Used to store all kinds of Mapper file-->
    <mappers>
        <!--Added a new mapping file-->
        <mapper resource="CarMapper.xml"></mapper>
        <mapper resource="UserMapper.xml"></mapper>
        <mapper resource="DeptMapper.xml"></mapper>
    </mappers>

Create test class

package cn.tedu.mybatis;

import cn.tedu.dao.CarMapper;
import cn.tedu.pojo.Car;
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 org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class TestCar {
    private SqlSessionFactory factory;

    @BeforeEach //Only JUnit 5 version supports annotations, and JUnit 4 version uses @ Before
    public void init() throws IOException {
        //1. Read the core configuration file
        InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
        //2. Create a session factory
        factory = new SqlSessionFactoryBuilder().build(in);
    }
    @Test
    public void get(){
        //Open session
        SqlSession session = factory.openSession();
        //Gets the specified interface
        CarMapper mapper = session.getMapper(CarMapper.class);
        //Call methods in the interface
        List<Car> list = mapper.selectList();
        //Traversal printing
        for (Car c : list) {
            System.out.println(c);
        }
    }

}

2, Parsing SQL parameters

– 1, demand: query the vehicle data with id=1

Modify POM XML and add the jar package of mybatis

 <!--add to mybatis Dependence of-->
    <dependencies>
        <!--mybatis Dependent package-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.4</version>
        </dependency>
        <!--jdbc Dependent package-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.48</version>
        </dependency>
    </dependencies>

Create the 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">

<!--This file is mybatis The core configuration files of the framework are configured with data sources, transactions and mapping files-->
<configuration>

    <environments default="test">
        <!--Configure data source, transaction-->
        <environment id="test">
            <!--Configured transactions, using jdbc Provided transactions-->
            <transactionManager type="JDBC"></transactionManager>
            <!--The data source is configured, and four parameters for connecting to the database are specified-->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatisdb?characterEncoding=utf8&amp;serverTimezone=Asia/Shanghai" />
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>

</configuration>

Create Car class

package cn.tedu.pojo;

public class Car {
    private Integer id;
    private String name;
    private String color;
    private Double price;

    @Override
    public String toString() {
        return "Car{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", color='" + color + '\'' +
                ", price=" + price +
                '}';
    }

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public String getColor() {
        return color;
    }

    public void setColor(String color) {
        this.color = color;
    }

    public Double getPrice() {
        return price;
    }

    public void setPrice(Double price) {
        this.price = price;
    }
}

Create CarMapper interface

package cn.tedu.dao;
import cn.tedu.pojo.Car;
public interface CarMapper {

    Car getById(Integer id);/**Query car data with id=1*/

}

Create carmapper xml

<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!--namespace The value of is the full path of the interface-->
<mapper namespace="cn.tedu.dao.CarMapper">
    <!--id Write the name of the method in the interface-->
    <select id="getById" resultType="cn.tedu.pojo.Car">
        select * from car
        where id = ${id}   /*Get the parameters passed in by the user, fixed syntax: ${whose value do you want to get}*/
    </select>
</mapper>

Modify the core configuration file and import the mapper file

  <!--introduce mapper file-->
    <mappers>
        <mapper resource="CarMapper.xml"></mapper>
    </mappers>

test

package cn.tedu.test;

import cn.tedu.dao.CarMapper;
import cn.tedu.pojo.Car;
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 org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;

import java.io.IOException;
import java.io.InputStream;
import java.net.CacheRequest;

public class Test1 {
    private SqlSessionFactory factory;

    //@BeforeEach is executed before @ Test is executed. The following two steps are repeated and extracted to improve the reusability of the code
    @BeforeEach
    public void init() throws IOException {
        //1. Read the core configuration file
        InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
        //2. Create a session factory
        factory = new SqlSessionFactoryBuilder().build(in);
    }
    @Test
    public void get(){
        SqlSession session = factory.openSession();
        CarMapper mapper = session.getMapper(CarMapper.class);
        Car c = mapper.getById(1);
        System.out.println(c);
    }
}

summary

3, Practice of parsing SQL parameters

– 1. Demand: check vehicle data by name
Modify mapping file

 <!--Check by name-->
    <select id="getByName" resultType="cn.tedu.pojo.Car">
        select * from car where
        name = #{name}
        /*$Both # and # can obtain parameters, but $will not splice strings, which is unsafe and inefficient, # will automatically splice strings, and it is efficient and safe to avoid SQL attacks*/
    </select>

Modify interface file

Car getByName(String name);/**Query the car data with name=bmw*/

Modify test file

@Test
    public void get(){
        SqlSession session = factory.openSession();
        CarMapper mapper = session.getMapper(CarMapper.class);
        Car c = mapper.getById(2);//Check by id
        System.out.println(c);

        Car c2 = mapper.getByName("bmw");//Check by name
        System.out.println(c2);
    }

summary

4, Dynamic SQL

– 1, function
Make SQL more flexible and dynamic
1
– 2, common labels
SQL tag: used to extract SQL fragments
include tag: refers to the specified SQL fragment
if tag: used for judgment
where tag: used to indicate filter conditions
Set tag: used to set a new value in the update statement
foreach tag: used to complete the loop structure

General label test

'
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!--namespace The value of is the full path of the interface-->
<mapper namespace="cn.tedu.dao.CarMapper">
    <!--1.  dynamic SQL Tags are used to extract commonalities SQL-->
    <sql id="columns">
        id,name,color,price
    </sql>

    <!--id Write the name of the method in the interface-->
    <select id="getById" resultType="cn.tedu.pojo.Car">
        select
        /*2. The specified SQL fragment is referenced*/
         <include refid="columns"></include>
        from car
        where id = ${id}
        /*Get the parameters passed in by the user, fixed syntax: ${whose value do you want to get}*/
    </select>

    <!--Check by name-->
    <select id="getByName" resultType="cn.tedu.pojo.Car">
        select 
        <include refid="columns"></include>
        from car
        <where> /*4, where label*/
            /*3, if The tag is used for judgment, and the SQL is executed only when the conditions are met. The test attribute is used to write the judgment conditions*/
            <if test="name != null">
                name = #{name}
            </if>
        </where>
        /*$And # can obtain parameters, but $will not splice strings, which is unsafe and inefficient, # will automatically splice strings, and it is efficient and safe to avoid SQL attacks*/
    </select>
</mapper>

4. Foreach label test

1. Requirement: query the data of car with id 1 or 2
2. Modify the mapping file and add a new SQL

<!--query id 1 or 2 car Data-->
    <select id="getByIds" resultType="cn.tedu.pojo.Car">
        select * from car where id
        in
        /*Get the data in the array. The value of collection is a fixed value array
        open Start with what close ends with what separator separates with what item represents each data in the array
        */
        <foreach collection="array" open="(" close=")" separator="," item="i">
            #{i}
        </foreach>
    </select>

Modify the interface file and add a new method

 List<Car> getByIds(Integer[] ids);/*Query the data of car with id 1 or 2*/

Modify the test file and call the new method

package cn.tedu.test;

import cn.tedu.dao.CarMapper;
import cn.tedu.pojo.Car;
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 org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;

import java.io.IOException;
import java.io.InputStream;
import java.net.CacheRequest;
import java.util.List;

public class Test1 {
    private SqlSessionFactory factory;

    //@BeforeEach is executed before @ Test is executed. The following two steps are repeated and extracted to improve the reusability of the code
    @BeforeEach
    public void init() throws IOException {
        //1. Read the core configuration file
        InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
        //2. Create a session factory
        factory = new SqlSessionFactoryBuilder().build(in);
    }
    @Test
    public void get(){
        SqlSession session = factory.openSession();
        CarMapper mapper = session.getMapper(CarMapper.class);
        Car c = mapper.getById(2);//Check by id
        System.out.println(c);

        Car c2 = mapper.getByName("bwm");//Check by name
        System.out.println(c2);


        Integer[] ids = {1, 2};
//        mapper.getByIds(new Integer[]{1, 2});
        //Multiple parameters are passed into SQL, and the parameters are parsed through the foreach tag in the SQL statement
        List<Car> list = mapper.getByIds(ids);
        for (Car cc : list) {
            System.out.println(cc);
        }

    }
}

5, resultmap

– 1, function:

**Generally, if the attribute name in the class is consistent with the field name in the table, the ORM can be automatically generated
**
If the name of the field in the table is different from the name of the attribute in the class, the resultType cannot complete the ORM, and the resultMap needs to be used

2. Usage:

When using multi table query, use resultmap
Change the attribute of the SQL tag from resultType to resultMap. In addition, deal with the special cases where the name of the field in the table is different from that of the attribute in the class

Prepare tables and data

create table user_info(
	id int primary key auto_increment,
	user_name varchar(100),
	user_addr varchar(200),
	user_age int);
insert into user_info values(null,'Mei Mei Han','Shanghai',20);
insert into user_info values(null,'Wang Haitao','Beijing',30);
insert into user_info values(null,'Zhang Shenzheng','Henan',10);

create table user_extra(
	id int primary key auto_increment,
	user_id int,
	work varchar(100),
	salary double);
insert into user_extra values(null,'1','programmer',100000);
insert into user_extra values(null, '2','teacher',1000);
insert into user_extra values(null, '3','CTO',100000);

Test:

Requirement: query user_ All data of extra

Create UserExtra class

package cn.tedu.pojo;

public class UserExtra {
    private Integer id;
    private Integer userId; //?
    private String work;
    private Double salary;

    @Override
    public String toString() {
        return "UserExtra{" +
                "id=" + id +
                ", userId=" + userId +
                ", work='" + work + '\'' +
                ", salary=" + salary +
                '}';
    }

    public Integer getId() {
        return id;
    }

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

    public Integer getUserId() {
        return userId;
    }

    public void setUserId(Integer userId) {
        this.userId = userId;
    }

    public String getWork() {
        return work;
    }

    public void setWork(String work) {
        this.work = work;
    }

    public Double getSalary() {
        return salary;
    }

    public void setSalary(Double salary) {
        this.salary = salary;
    }

Create UserExtraMapper interface

package cn.tedu.dao;
import cn.tedu.pojo.UserExtra;
import java.util.List;

public interface UserExtraMapper {
    List<UserExtra> selectList();//Check all data
}

Create userextramapper XML Mapping File

<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!--namespace The value of is the full path of the interface-->
<mapper namespace="cn.tedu.dao.UserExtraMapper">
    
    <!--id The value of is the method name in the interface  resultType The value of is the full path of which class to encapsulate the result to-->
    <select id="selectList" resultType="cn.tedu.pojo.UserExtra">
        select * from user_extra
    </select>
    
</mapper>

Modify the core configuration file and introduce a new mapper file

<!--introduce mapper file-->
    <mappers>
        <mapper resource="CarMapper.xml"></mapper>
        <!--Introduce new mapper file-->
        <mapper resource="UserExtraMapper.xml"></mapper>
    </mappers>

test

 @Test
    public void get2(){
        //1. Open a session
        SqlSession session = factory.openSession();
        //2. Obtain the interface
        UserExtraMapper mapper = session.getMapper(UserExtraMapper.class);
        //3. Call method
        List<UserExtra> list = mapper.selectList();

        for (UserExtra ue : list) {
            System.out.println(ue);
        }
    }

Problem: if the field name and attribute name are inconsistent, there is no ORM

UserExtra{id=1, userId=null, work='programmer', salary=100000.0}
UserExtra{id=2, userId=null, work='teacher', salary=1000.0}
UserExtra{id=3, userId=null, work='CTO', salary=100000.0}

Solution: use resultMap instead of resultType

<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!--namespace The value of is the full path of the interface-->
<mapper namespace="cn.tedu.dao.UserExtraMapper">

    <!--Used when the field name and property name are inconsistent resultMap
        id The attribute is, each resultMap Unique identification of
        type The attribute is to put the result ORM Full path to which class
    -->
    <resultMap id="UserERM" type="cn.tedu.pojo.UserExtra">
        <!--Describe special fields,column Field name in description table  property Describes the property name in the class  -->
        <result column="user_id" property="userId"></result>
    </resultMap>

    <!--id The value of is the method name in the interface  resultType The value of is the full path of which class to encapsulate the result to-->
    <!--<select id="selectList" resultType="cn.tedu.pojo.UserExtra"> No special fields ORM-->
    <select id="selectList" resultMap="UserERM">
        select * from user_extra
    </select>

</mapper>

test

UserExtra{id=1, userId=1, work='programmer', salary=100000.0}
UserExtra{id=2, userId=2, work='teacher', salary=1000.0}
UserExtra{id=3, userId=3, work='CTO', salary=100000.0}