public interface UserDao { //使用原生参数绑定 public User selectUserByIdAndPwd(Integer id , String pwd); }
1 2 3 4 5 6 7 8 9
<select id="selectUserByIdAndPwd" resultType="user"> SELECT * FROM t_users WHERE id = #{arg0} AND password = #{arg1} <!--arg0 arg1 arg2 ...--> </select>
<select id="selectUserByIdAndPwd" resultType="user"> SELECT * FROM t_users WHERE id = #{param1} AND password = #{param2} <!--param1 param2 param3 ...--> </select>
public interface UserDao { //使用MyBatis提供的@Param进行参数绑定 public User selectUserByIdAndPwd(@Param("id") Integer id , @Param("pwd") String pwd); }
1 2 3 4
<select id="selectUserByIdAndPwd" resultType="user"> SELECT * FROM t_users WHERE id = #{id} AND password = #{pwd} <!-- 使用注解值 @Param("pwd") --> </select>
Map参数绑定:
1 2 3 4
public interface UserDao { //添加Map进行参数绑定 public User selectUserByIdAndPwd_map(Map values); }
1 2 3 4
Map values = new HashMap(); //测试类创建Map values.put("myId",1); //自定义key，绑定参数 values.put("myPwd","123456"); User user = userDao.selectUserByIdAndPwd_map(values);
1 2 3 4
<select id="selectUserByIdAndPwd_map" resultType="user"> SELECT * FROM t_users WHERE id = #{myId} AND password = #{myPwd} <!-- 通过key获得value --> </select>
对象参数绑定:
1 2 3 4
public interface UserDao { //使用对象属性进行参数绑定 public User selectUserByUserInfo(User user); }
1 2 3 4
<select id="selectUserByUserInfo" resultType="user"> SELECT * FROM t_users WHERE id = #{id} AND password = #{password} <!-- #{id}取User对象的id属性值、#{password}同理 --> </select>
模糊查询:
1 2 3
public interface UserDao { public List<User> selectUsersByKeyword(@Param("keyword") String keyword); }
1 2 3 4 5 6
<mapper namespace="com.qf.mybatis.part1.different.UserDao"> <select id="selectUsersByKeyword" resultType="user"> SELECT * FROM t_users WHERE name LIKE concat('%',#{keyword},'%') <!-- 拼接'%' --> </select> </mapper>
删除:
标签：< delete id=“” parameterType=“” >
parameterType：指定sql语句中的参数类型
1 2 3 4
<delete id="deleteUser" parameterType="int"> DELETE FROM t_users WHERE id = #{id} <!--只有一个参数时，#{任意书写}--> </delete>
修改:
标签：< update id=“” parameterType=“” >
1 2 3 4
<update id="updateUser" parameterType="user"> UPDATE t_users SET name=#{name}, password=#{password}, sex=#{sex}, birthday=#{birthday} WHERE id = #{id} <!--方法参数为对象时，可直接使用#{属性名}进行获取--> </update>
添加:
标签：< insert id=“” parameterType=“” >
这里的id和mapper相关接口同名，表示其相关方法对应！
1 2 3
<insert id="insertUser" parameterType="user"> INSERT INTO t_users VALUES(#{id},#{name},#{password},#{sex},#{birthday},NULL); </insert>
<mapper namespace="com.qf.mybatis.part2.orm.ManagerDao"> <select id="selectManagerByIdAndPwd" resultType="com.qf.mybatis.part2.orm.Manager"> SELECT mgr_id AS id , mgr_name AS username , mgr_pwd AS password FROM t_managers WHERE mgr_id = #{id} AND mgr_pwd = #{pwd} </select> </mapper>
<if test="sort != null"> sort = #{sort} </if> </trim> WHERE id = #{id} </update>
< foreach >:
1 2 3 4 5 6 7
<delete id="deleteBookByIds"> DELETE FROM t_books WHERE id IN <foreach collection="list" open="(" separator="," close=")" item="id" index="i"> #{id} </foreach> </delete>
<mapper namespace="com.qf.mybatis.part1.different.UserDao"> <select id="selectUsersByKeyword" resultType="user"> SELECT * FROM t_users WHERE name LIKE concat('%',#{keyword},'%') <!-- 拼接'%' --> </select> </mapper>
