Mybatis from entry to mastery -- parameter transfer

1, Preparations

1.maven dependency

    <dependencies>
        <!--    mysql drive    -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.18</version>
        </dependency>
 
        <!--    junit test    -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
 
        <!--    mybatis    -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.4</version>
        </dependency>
 
 
        <!-- Log dependent -->
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>1.7.10</version>
        </dependency>
        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-classic</artifactId>
            <version>1.1.2</version>
        </dependency>
        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-core</artifactId>
            <version>1.1.2</version>
        </dependency>
        
    </dependencies>

2. entity class

public class Person {
 
    private Integer id;
 
    private String name;
 
    private String sex;
 
    private String telephone;
 
    private String address;
 
    private Integer age;
 
    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 getSex() {
        return sex;
    }
 
    public void setSex(String sex) {
        this.sex = sex;
    }
 
    public String getTelephone() {
        return telephone;
    }
 
    public void setTelephone(String telephone) {
        this.telephone = telephone;
    }
 
    public String getAddress() {
        return address;
    }
 
    public void setAddress(String address) {
        this.address = address;
    }
 
    public Integer getAge() {
        return age;
    }
 
    public void setAge(Integer age) {
        this.age = age;
    }
 
    @Override
    public String toString() {
        return "Person{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", sex='" + sex + '\'' +
                ", telephone='" + telephone + '\'' +
                ", address='" + address + '\'' +
                ", age=" + age +
                '}';
    }
}

3.sql resources

DROP TABLE IF EXISTS `person`;
CREATE TABLE `person` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL COMMENT 'Name',
  `sex` char(1) NOT NULL DEFAULT 'male' COMMENT 'Gender',
  `telephone` varchar(11) DEFAULT NULL COMMENT 'Cell-phone number',
  `address` varchar(50) DEFAULT NULL COMMENT 'address',
  `age` tinyint(4) DEFAULT NULL COMMENT 'Age',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1295453 DEFAULT CHARSET=utf8;
 
 
INSERT INTO `person` VALUES ('1', 'xy', 'male', '123456', 'test', '18');
INSERT INTO `person` VALUES ('2', 'ceshi', 'female', '123456789', 'U.S.A', '20');
INSERT INTO `person` VALUES ('3', 'ceshi', 'female', '123456789', 'U.S.A', '21');

 

4.mybatis configuration file

<?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>

    <settings>
        <!--   Use the specified log implementation class     -->
        <setting name="lazyLoadTriggerMethods"  value=""/>
            <setting name="logImpl" value="SLF4J"/>
    </settings>



    <!--Environment configuration, connected database, which is used here MySQL-->
    <environments default="mysql">
        <environment id="mysql">
            <!--Specify the type of transaction management, which is easy to use here Java Of JDBC Commit and rollback settings for-->
            <transactionManager type="JDBC"></transactionManager>
            <!--dataSource Refers to the connection source configuration, type Property refers to the data source factory implementation class used, which is used here Mybatis Connection pool provided-->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"></property>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis_study?serverTimezone=GMT%2B8"></property>
                <property name="username" value="root"></property>
                <property name="password" value="7830698a"></property>
            </dataSource>
        </environment>
    </environments>

    <!--  mapping mapper Path to file-->
    <mappers>
        <mapper resource="mappers/PersonMapper.xml"></mapper>
    </mappers>

</configuration>

5.mapper 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 namespace="com.my.mapper.PersonMapper">

    <!--  Single simple parameter transfer  -->
    <select id="selectById" parameterType="int" resultType="com.my.entity.Person">
        select * from person where id = #{id}
    </select>

    <!--  Multiple parameter passing  -->
    <select id="selectByNameAndSex" resultType="com.my.entity.Person" >
        select * from person
        where name = #{name} and sex = #{sex}
    </select>

    <!--  Single complex parameter transfer  -->
    <select id="selectByPerson" resultType="com.my.entity.Person" parameterType="com.my.entity.Person">
        select * from person
        <where>
            <if test="id != null">
                and id = #{id}
            </if>
            <if test="name != null and name != ''">
                and name = #{name}
            </if>
            <if test="sex != null and sex != ''">
                and sex = #{sex}
            </if>
            <if test="telephone != null and telephone != ''">
                and telephone = #{telephone}
            </if>
            <if test="address != null and address != ''">
                and address = #{address}
            </if>
            <if test="age != null">
                and age = #{age}
            </if>
        </where>
    </select>

    <!--  Single complex parameter transfer  -->
    <select id="selectByMap" resultType="com.my.entity.Person" parameterType="Map">
        select * from person
        <where>
            <if test="id != null">
                and id = #{id}
            </if>
            <if test="name != null and name != ''">
                and name = #{name}
            </if>
            <if test="sex != null and sex != ''">
                and sex = #{sex}
            </if>
            <if test="telephone != null and telephone != ''">
                and telephone = #{telephone}
            </if>
            <if test="address != null and address != ''">
                and address = #{address}
            </if>
            <if test="age != null">
                and age = #{age}
            </if>
        </where>
    </select>
</mapper>

6.mapper interface

public interface PersonMapper {

    Person selectById(Integer id);

    List<Person> selectByNameAndSex(@Param("name")String name, @Param("sex") String sex);

    List<Person> selectByPerson(Person person);

    List<Person> selectByMap(Map map);
}

7. test class

public class MybatisTest {

    private SqlSessionFactory sqlSessionFactory;

    @Before
    public void init() throws IOException {
        String resource = "mybatis-config.xml";
        //1. Use mybatis to read the configuration file
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
        //2. Create sqlSessionFactory
        sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
        inputStream.close();
    }

    /**
     * Test @ Param parameter
     */
    @Test
    public void testParam() {
        //3. Get sqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //4. Get the mapper interface implementation class
        PersonMapper personMapper = sqlSession.getMapper(PersonMapper.class);
        //5. Call method
        List<Person> peoples = personMapper.selectByNameAndSex("xy", "male");
        printList(peoples);
        //6. Release resources
        sqlSession.close();
    }

    /**
     * Test the Person parameter
     */
    @Test
    public void testPerson() {
        //3. Get sqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //4. Get the mapper interface implementation class
        PersonMapper personMapper = sqlSession.getMapper(PersonMapper.class);
        //5. Call method
        Person person = new Person();
        person.setName("ceshi");
        person.setSex("female");
        person.setAddress("U.S.A");
        List<Person> peoples = personMapper.selectByPerson(person);
        printList(peoples);
        //6. Release resources
        sqlSession.close();
    }

    /**
     * Test Map parameters
     */
    @Test
    public void testMap() {
        //3. Get sqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //4. Get the mapper interface implementation class
        PersonMapper personMapper = sqlSession.getMapper(PersonMapper.class);
        //5. Call method
        Map map = new HashMap();
        map.put("name","ceshi");
        map.put("sex","female");
        map.put("address","U.S.A");
        List<Person> peoples = personMapper.selectByMap(map);
        printList(peoples);
        //6. Release resources
        sqlSession.close();
    }


    public static void printList(List list) {
        for(Object o : list) {
            System.out.println(o);
        }
    }
}

8. Directory structure

 

Two, explain

In Mybatis, parameter transfer mainly includes single simple parameter transfer, single complex parameter transfer and multiple parameter transfer.

1) Single simple parameter transfer: for single simple parameter transfer, such as direct transfer of Integer, String, Date and other simple parameters, the parameter can be obtained through {parameter name} in the mapper mapping file.

2) Single complex parameter transfer: mainly refers to the parameter transfer of some custom objects and Map types. If it is a parameter transfer of custom objects, the parameter value is obtained in the mapper mapping file by {attribute name or key}. If there is a cascade object, the parameter value is obtained by {attribute name or key. Cascade object attribute or key}. Note that the middle is separated by decimal points.

3) Multiple parameter passing: when passing multiple parameters in Mybatis, you need to mark the name of each parameter in the mapper mapping file with @ Param("parameter name"). If there is a cascading object, please refer to the second article. For the parameterType attribute on the mapper mapping file passed by multiple parameters, you can leave it blank.

4) In project use, Map is generally not suitable for parameter transfer and is not easy to maintain. If there are less than 5 parameters, it is recommended to use @ Param for parameter transfer. If there are more than or equal to 5 parameters, it is recommended to encapsulate them as object transfer.

5) For parameter passing of List and array, please refer to the dynamic SQL blog later

 

3, Chen and $description

There are generally two ways to use the passed parameters in Mybatis's mapping file: through {parameter name} and ${parameter name}.

#After Mybatis parsing, it will become a "placeholder" of jdbc precompiled (more efficient), while $after Mybatis parsing, it will become "content", that is, a single quotation mark is added to the obtained value, which can be understood as splicing string SQL, which may lead to SQL injection.

Therefore, we usually use the ා sign placeholder instead of $. Of course, in some cases, we have to use $. For example, when the table name or keywords of SQL need to change dynamically, we can only use $, but we should pay attention to SQL injection when using $.

48 original articles published, praised 81, visited 100000+
Private letter follow

Tags: Mybatis MySQL SQL JDBC

Posted on Sun, 08 Mar 2020 01:38:11 -0500 by Donovan