一、SQL语句中接收参数的方式有两种:
1、 #{}预编译 (可防止sql注入)
2、${}非预编译(直接拼接sql,不能防止sql注入)
#{}和${}的区别是什么?
#{} 占位符,相当于?,sql预编译,可以防止sql注入
${} 原样替换,相当于sql拼接
某些情况下,#{}和${}可以互相替换,但是有些情况下必须使用${},比如order by的字段是sql参数。
二、dao层的四种传参方式 ↓
基本数据类型
dao层:
List<Bean> selectIdBySortTime(@Param(value="id")Long id);
xml:
<sql id="Base_Column_List" >
id, car_dept_name, car_maker_name, icon,car_maker_py,hot_type
</sql>
<select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Long" >
select
<include refid="Base_Column_List" />
from common_car_make
where id = #{id,jdbcType=BIGINT} (jdbcType可省略)
</select>
多参数传递
dao层:
User login(@Param(value="name")String name,@Param(value="password")String password );
xml:
<select id="login" resultType="com.pojo.User">
select * from us where name=#{name} and password=#{password}
</select>
封装成map传递
service层:
Map<String, Object> paramMap=new hashMap();
paramMap.put(“id”, value);
paramMap.put(“carDeptName”,value);
paramMap.put(“carMakerName”,value);
paramMap.put(“hotType”,value);
dao层:
List<Bean> queryCarMakerList(@Param(value="cm")Map paramMap);
xml:
<select id="queryCarMakerList" resultMap="BaseResultMap" parameterType="java.util.Map">
select
<include refid="Base_Column_List" />
from common_car_make cm
where 1=1
<if test="id != null">
and id = #{cm.id,jdbcType=DECIMAL}
</if>
<if test="carDeptName != null">
and car_dept_name = #{cm.carDeptName,jdbcType=VARCHAR}
</if>
<if test="carMakerName != null">
and car_maker_name = #{cm.carMakerName,jdbcType=VARCHAR}
</if>
<if test="hotType != null" >
and hot_type = #{cm.hotType,jdbcType=BIGINT}
</if>
ORDER BY id
</select>
pojo对象传递
dao层:
public UserSms getSmsByPhoneAndSmsCode(UserSms u);
xml:
<?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.jyt.shop.dao.UserSmsMapper">
<!-- 发送给用户手机号的resultMap将项目的实体类名与数据库表user_sms映射一一对应 -->
<resultMap id="UserSmsResultMap" type="com.jyt.shop.entity.UserSms">
<result property="usId" column="usId" />
<result property="userId" column="userId" />
<result property="smsContent" column="smsContent" />
<result property="smsType" column="smsType" />
<result property="smsPhone" column="smsPhone" />
<result property="smsStatus" column="smsStatus" />
<result property="usCreatedate" column="usCreatedate" />
</resultMap>
<select id="getSmsByPhoneAndSmsCode" parameterType="com.jyt.shop.entity.UserSms" resultMap="UserSmsResultMap">
SELECT usId,userId,smsContent,smsType,smsPhone,smsStatus,usCreatedate
FROM USER_SMS
<where>
smsStatus= 1
<if test="smsPhone !=null and smsPhone !=''">
and smsPhone = #{smsPhone}
</if>
<if test="smsContent !=null and smsContent !=''">
and smsContent= #{smsContent}
</if>
</where>
</select>
</mapper>