Mybatis用xml配置sql传参方式

绝地灬酷狼 2024-04-17 14:34 55阅读 0赞

#### 1、传入一个参数 ####

方法：

int deleteByPrimaryKey(Integer id);

配置

<delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
        delete from t_people
        where id = #{id,jdbcType=INTEGER}
      </delete>

这里面可以直接用\#\{id\}在sql语句中写。

#### 2、2、\#\{0\}、\#\{1\}……索引方式，传入多个参数 ####

public User selectUserByNameAndAge(String name,int age);

<select id="selectUserByNameAndAge" resultType="user">
        select * from user where name = #{0} and age = #{1}
    </select>

这里面用数组索引的形式指代。

#### 3、\#\{参数名\}，传入多个参数，并且参数用@param注解 ####

public User selectUserByNameAndAge(@param("name")String name,@param("age")int age);

<select id="selectUserByNameAndAge" resultType="user">
        select * from user where name = #{name} and age = #{age}
    </select>

可以根据param名称查询

#### 4、传入多个基本类型参数，参数用map封装，通过\#\{mapKey\}取值 ####

public User selectUserByNameAndAge(Map map);

<select id="selectUserByNameAndAge" parameterType="Map" resultType="user">
        select * from user where name = #{name} and age = #{age}
    </select>

这里的\#\{mapKey\}指的就是\#\{name\}、\#\{age\}

#### 5、使用map封装实体类,通过通过\#\{mapKey.attributeName\}取值 ####

public User selectUserByNameAndAge(Map map);

<select id="selectUserByNameAndAge" parameterType="Map" resultType="user">
        select * from user where name = #{userParam.name} and age = #{userParam.age}
    </select>

Service

User userParam = new User("lucy",18);
    
    Map<String,Object> map=new HasMap<String,Object>();  
    map.put("user",userParam);  
    User user = userDao.selectUserByNameAndAge(map);

#### 6、直接传入实体参数，通过\#\{属性名\}取值 ####

public User selectUserByNameAndAge(User userParam);

<select id="selectUserByNameAndAge" parameterType="User" resultType="user">
        select * from user where name = #{ userParam.name} and age = #{ userParam.age}
    </select>

#### 7、使用$\{\} ####

\#\{\}与$\{\}的区别:  
\{\}拿到值之后，拼装sql，会自动对值添加单引号”  
$\{\}则把拿到的值直接拼装进sql，如果需要加单引号”，必须手动添加，一般用于动态传入表名或字段名使用，同时需要添加属性statementType=”STATEMENT”，使用非预编译模式。

public List<User> selectUserByOrgId(String orgId);

<select id="selectUserByOrgId" parameterType="java.lang.String" resultType="user" statementType="STATEMENT">
        select * from user where org_id = ${ orgId}
    </select>

Service

String orgId = "100";
    orgId = "'" + orgId + "'";
    List<User> users = userDao.selectUserByOrgId(orgId);