mybatis详解-(9)参数传递

雨点打透心脏的1/2处 2022-06-06 02:11 208阅读 0赞

前面两篇分别介绍了mybatis的增删改查和主键生成，遇到在查询中使用多个查询条件的情况，使用前面的sql无法解决，运行程序出现错误。这一篇主要介绍一下mybatis查询sql的参数传递问题。

# 1.单个参数 #

创建对象类Employee

package org.mybatis.crud;
    
    public class Employee { 
        private Integer id;
        private String lastName;
        private String email;
        private String gender;
    
        public Integer getId() {
            return id;
        }
        public void setId(Integer id) {
            this.id = id;
        }
        public String getLastName() {
            return lastName;
        }
        public void setLastName(String lastName) {
            this.lastName = lastName;
        }
        public String getEmail() {
            return email;
        }
        public void setEmail(String email) {
            this.email = email;
        }
        public String getGender() {
            return gender;
        }
        public void setGender(String gender) {
            this.gender = gender;
        }
        @Override
        public String toString() {
            return "Employee [id=" + id + ", lastName=" + lastName + ", email=" + email
                    + ", gender=" + gender + "]";
        }
    }

创建方法接口

public interface EmployeeMapper { 
    
        //通过id获取对象
        public Employee getEmployeeById(int id);
        //通过id和lastName获取对象
        public Employee getEmployeeByIdAndLastName(int id,String lastName);
    
    }

创建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="org.mybatis.crud.EmployeeMapper">
    
        
        <select id="getEmployeeById" resultType="org.mybatis.crud.Employee">
            select * from mybatis_employee where id = #{id}
        </select>
    </mapper>

创建主配置文件

<?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>
        
        <properties resource="db.properties"></properties>
        
        <settings>
            <setting name="mapUnderscoreToCamelCase" value="true"/>
        </settings>
    
        <environments default="development">
            <environment id="development">
                <transactionManager type="JDBC" />
                <dataSource type="POOLED">
                    <property name="driver" value="${mysql.driver}" />
                    <property name="url" value="${mysql.url}" />
                    <property name="username" value="${mysql.username}" />
                    <property name="password" value="${mysql.password}" />
                </dataSource>
            </environment>
        </environments>
    
        <mappers>
            <mapper resource="mapper/crudmapper.xml"/>
        </mappers>
    
    </configuration>

创建测试类

package org.mybatis.crud;
    
    import java.io.IOException;
    import java.io.InputStream;
    
    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.Test;
    
    /** * 使用接口式編程 * @author tang * SqlSessionFactory为非线程安全，所以不能够将SqlSessionFactory定义为成员变量 */
    public class MybatisTest { 
    
        public SqlSessionFactory getSqlSessionFactory() throws IOException{
            String resource = "mybatis-config-crud.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            return sqlSessionFactory;
        }
    
        @Test
        public void testMybatisSelect1() {
       //测试查询
                SqlSessionFactory sqlSessionFactory = null;
                SqlSession sqlSession = null;
                try {
                    sqlSessionFactory = getSqlSessionFactory();
                    sqlSession = sqlSessionFactory.openSession();
                    EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
                    Employee employee = mapper.getEmployeeById(1);
                    System.out.println("--------------------------" + employee + "--------------------------");
    
                } catch (IOException e) {
                    e.printStackTrace();
                } finally {
                    sqlSession.close();
                }
        }
    
    }

单个参数，传递过程中不需要做特殊处理  
直接使用\#\{参数名\}：取出参数值

# 2.多个参数 #

如传递多个参数

public Employee getEmployeeByIdAndLastName(int id,String lastName);

如果使用如下配置

<select id="getEmployeeByIdAndLastName" resultType="org.mybatis.crud.Employee">
            select * from mybatis_employee where id = #{id} and last_name = #{lastName}
        </select>

@Test
    public void testMybatisSelect2() {
       //测试查询
            SqlSessionFactory sqlSessionFactory = null;
            SqlSession sqlSession = null;
            try {
                sqlSessionFactory = getSqlSessionFactory();
                sqlSession = sqlSessionFactory.openSession();
                EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
                //传递两个参数
                Employee employee = mapper.getEmployeeByIdAndLastName(1, "zhangsan");
                System.out.println("--------------------------" + employee + "--------------------------");
    
            } catch (IOException e) {
                e.printStackTrace();
            } finally {
                sqlSession.close();
            }
    }

运行结果出现绑定错误，那么这种参数传递方式是有错误的

> org.apache.ibatis.exceptions.PersistenceException:  
> Error querying database. Cause: org.apache.ibatis.binding.BindingException: Parameter ‘id’ not found. Available parameters are ***\[0, 1, param1, param2\]***  
> The error may exist in mapper/crudmapper.xml  
> The error may involve defaultParameterMap  
> The error occurred while setting parameters  
> SQL: select \* from mybatis\_employee where id = ? and last\_name = ?  
> Cause: org.apache.ibatis.binding.BindingException: Parameter ‘id’ not found. Available parameters are \[0, 1, param1, param2\]

错误里面显示使用param1，param2作为参数

<select id="getEmployeeByIdAndLastName" resultType="org.mybatis.crud.Employee">
            select * from mybatis_employee where id = #{param1} and last_name = #{param2}
        </select>

测试结果正确

> ————————–Employee \[id=1, lastName=zhangsan, email=bestjinyi@163.com, gender=1\]————————–

将param1和param2分别替换成下标0 1

<select id="getEmployeeByIdAndLastName" resultType="org.mybatis.crud.Employee">
            select * from mybatis_employee where id = #{0} and last_name = #{1}
        </select>

也可以得到正确的结果。

# 3.命名参数 #

传递多个参数的时候，如果通过下标或者param1，param2，paramn这种形式，容易发生错位导致传递错误。mybatis提供了一个传递参数的注解@Param。将原来的接口方法做一下修改

public Employee getEmployeeByIdAndLastName(@Param("id")int id,@Param("lastName")String lastName);

<select id="getEmployeeByIdAndLastName" resultType="org.mybatis.crud.Employee">
            select * from mybatis_employee where id = #{id} and last_name = #{lastName}
        </select>

继续测试，结果正常。

# 4.传递pojo参数 #

接口中添加方法

public Employee getEmployeeByEmployeeField(Employee employee);

mapper配置文件中添加

<select id="getEmployeeByEmployeeField" resultType="org.mybatis.crud.Employee">
            select * from mybatis_employee where id = #{id} and last_name = #{lastName}
        </select>

测试方法使用

@Test
        public void testMybatisSelect3() {
       //测试查询
                SqlSessionFactory sqlSessionFactory = null;
                SqlSession sqlSession = null;
                try {
                    sqlSessionFactory = getSqlSessionFactory();
                    sqlSession = sqlSessionFactory.openSession();
    
                    Employee emp = new Employee();
                    emp.setId(1);
                    emp.setLastName("zhangsan");
    
                    Employee employee = mapper.getEmployeeByEmployeeField(emp);
                    System.out.println("--------------------------" + employee + "--------------------------");
    
                } catch (IOException e) {
                    e.printStackTrace();
                } finally {
                    sqlSession.close();
                }
        }

# 5.map传递参数 #

新建接口方法

public Employee getEmployeeByMap(Map<String,Object> conditionsMap);

添加配置

<select id="getEmployeeByMap" resultType="org.mybatis.crud.Employee">
            select * from mybatis_employee where id = #{id} and last_name = #{lastName}
        </select>

新增测试方法

@Test
        public void testMybatisSelect4() {
       //测试查询
                SqlSessionFactory sqlSessionFactory = null;
                SqlSession sqlSession = null;
                try {
                    sqlSessionFactory = getSqlSessionFactory();
                    sqlSession = sqlSessionFactory.openSession();
                    EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
    
                    Map<String,Object> conditionsMap = new HashMap<String,Object>();
                    conditionsMap.put("id", 1);
                    conditionsMap.put("lastName", "zhangsan");
    
                    Employee employee = mapper.getEmployeeByMap(conditionsMap);
                    System.out.println("--------------------------" + employee + "--------------------------");
    
                } catch (IOException e) {
                    e.printStackTrace();
                } finally {
                    sqlSession.close();
                }
        }