Mybatis从入门到精通——参数传递（5）

灰太狼 2023-07-12 11:40 15阅读 0赞

# 一、准备工作 #

## 1.maven依赖 ##

<dependencies>
            
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>8.0.18</version>
            </dependency>
     
            
            <dependency>
                <groupId>junit</groupId>
                <artifactId>junit</artifactId>
                <version>4.12</version>
            </dependency>
     
            
            <dependency>
                <groupId>org.mybatis</groupId>
                <artifactId>mybatis</artifactId>
                <version>3.5.4</version>
            </dependency>
     
     
            
            <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.实体类 ##

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资源 ##

DROP TABLE IF EXISTS `person`;
    CREATE TABLE `person` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `name` varchar(30) NOT NULL COMMENT '名字',
      `sex` char(1) NOT NULL DEFAULT '男' COMMENT '性别',
      `telephone` varchar(11) DEFAULT NULL COMMENT '手机号',
      `address` varchar(50) DEFAULT NULL COMMENT '地址',
      `age` tinyint(4) DEFAULT NULL COMMENT '年龄',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1295453 DEFAULT CHARSET=utf8;
     
     
    INSERT INTO `person` VALUES ('1', 'xy', '男', '123456', '测试', '18');
    INSERT INTO `person` VALUES ('2', 'ceshi', '女', '123456789', '美国', '20');
    INSERT INTO `person` VALUES ('3', 'ceshi', '女', '123456789', '美国', '21');

# #

## 4.mybatis配置文件 ##

<?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>
            
            <setting name="lazyLoadTriggerMethods"  value=""/>
                <setting name="logImpl" value="SLF4J"/>
        </settings>
    
    
    
        
        <environments default="mysql">
            <environment id="mysql">
                
                <transactionManager type="JDBC"></transactionManager>
                
                <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="123456"></property>
                </dataSource>
            </environment>
        </environments>
    
        
        <mappers>
            <mapper resource="mappers/PersonMapper.xml"></mapper>
        </mappers>
    
    </configuration>

## 5.mapper映射文件 ##

<?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">
    
        
        <select id="selectById" parameterType="int" resultType="com.my.entity.Person">
            select * from person where id = #{id}
        </select>
    
        
        <select id="selectByNameAndSex" resultType="com.my.entity.Person" >
            select * from person
            where name = #{name} and sex = #{sex}
        </select>
    
        
        <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>
    
        
        <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接口 ##

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.测试类 ##

public class MybatisTest {
    
        private SqlSessionFactory sqlSessionFactory;
    
        @Before
        public void init() throws IOException {
            String resource = "mybatis-config.xml";
            //1.使用mybatis的工具读取配置文件
            InputStream inputStream = Resources.getResourceAsStream(resource);
            SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
            //2.创建sqlSessionFactory
            sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
            inputStream.close();
        }
    
        /**
         * 测试@Param参数
         */
        @Test
        public void testParam() {
            //3.获取sqlSession
            SqlSession sqlSession = sqlSessionFactory.openSession();
            //4.获取mapper接口实现类
            PersonMapper personMapper = sqlSession.getMapper(PersonMapper.class);
            //5.调用方法
            List<Person> peoples = personMapper.selectByNameAndSex("xy", "男");
            printList(peoples);
            //6.释放资源
            sqlSession.close();
        }
    
        /**
         * 测试Person参数
         */
        @Test
        public void testPerson() {
            //3.获取sqlSession
            SqlSession sqlSession = sqlSessionFactory.openSession();
            //4.获取mapper接口实现类
            PersonMapper personMapper = sqlSession.getMapper(PersonMapper.class);
            //5.调用方法
            Person person = new Person();
            person.setName("ceshi");
            person.setSex("女");
            person.setAddress("美国");
            List<Person> peoples = personMapper.selectByPerson(person);
            printList(peoples);
            //6.释放资源
            sqlSession.close();
        }
    
        /**
         * 测试Map参数
         */
        @Test
        public void testMap() {
            //3.获取sqlSession
            SqlSession sqlSession = sqlSessionFactory.openSession();
            //4.获取mapper接口实现类
            PersonMapper personMapper = sqlSession.getMapper(PersonMapper.class);
            //5.调用方法
            Map map = new HashMap();
            map.put("name","ceshi");
            map.put("sex","女");
            map.put("address","美国");
            List<Person> peoples = personMapper.selectByMap(map);
            printList(peoples);
            //6.释放资源
            sqlSession.close();
        }
    
    
        public static void printList(List list) {
            for(Object o : list) {
                System.out.println(o);
            }
        }
    }

## 8.目录结构 ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3OTE0NTg4_size_16_color_FFFFFF_t_70][]

# 二、说明 #

在Mybatis中参数传递主要分为单个简单参数传递、单个复杂参数传递、多个参数传递。

1）单个简单参数传递：对于单个简单参数传递，如直接传递Integer、String、Date等简单参数直接传递使用即可，在mapper映射文件中通过\#\{形参名\}获取该参数。

2）单个复杂参数传递：主要是指一些自定义对象、Map类型等参数传递，如果是自定义对象的参数传递，则在mapper映射文件中通过\#\{属性名或key\}获取该参数值，如果存在级联对象，则通过\#\{属性名或key.级联对象属性或key\}获取该参数值，注意中间通过小数点隔开。

3）多个参数传递：在Mybatis中多个参数传递时需要通过@Param("参数名")标注每个形参在mapper映射文件中的名字，如果存在级联对象，则参考第二条，对于多个参数传递的mapper映射文件上的parameterType属性可以不用写。

4）在项目使用中，一般不适用Map作为参数传递，不易维护，如果形参小于5个，建议使用@Param进行参数传递，如果形参大于等于5个，建议封装成对象传递。

5）对于List和数组的参数传递，请参考后面的动态SQL博客

# 三、\#和$说明 #

Mybatis的映射文件中使用传递的参数一般有两种方式，即通过\#\{参数名\}和$\{参数名\}。

\#在Mybatis解析后，会变成jdbc预编译的 ? 号占位符（效率更高），而$在经过Mybatis解析后，会变成'内容'，即对获取到的值加上了单引号，可以理解为拼接字符串SQL，这可能会导致SQL注入。

所以一般都是使用\#号占位符，而不是使用$，当然某些情况下是不得不使用$进行拼接，如SQL的表名或者关键词需要动态变化时只能使用$进行拼接，但是使用$时要注意SQL的注入。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3OTE0NTg4_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200308135643853.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3OTE0NTg4,size_16,color_FFFFFF,t_70