MyBatis——MyBatis的CRUD（增删改查）

1.MyBatis的CRUD

创建工程：

1.1.查询

1.1.1.单个参数绑定

    //单个参数传递
    public User findUserById(Integer id);

    <!--
        parameterType:指定输入参数的类型
        resultType：指定数据结果封装的数据类型
		#{id}：它代表占位符，相当于原来 jdbc 部分所学的?，都是用于替换实际的数据。
    -->
    <select id="findUserById" parameterType="java.lang.Integer" 
            				  resultType="com.wt.pojo.User" >
        select * from user where id=#{id}<!--只有一个参数时，#{任意书写}-->
    </select>

    @Test
    public void testFindUserById(){
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        User user = userDao.findUserById(41);
        System.out.println(user);
    }

1.1.2.序号参数绑定

    //传递多个参数
    public User findUserByIdAndName(Integer id, String username);

     <select id="findUserByIdAndName" resultType="com.wt.pojo.User" >
        SELECT * FROM user
        WHERE id = #{arg0} AND username = #{arg1} <!--arg0 arg1 arg2 ...-->
    </select>

	<select id="findUserByIdAndName" resultType="com.wt.pojo.User" >
        SELECT * FROM user
        WHERE id = #{param1} AND username = #{param2} <!--param1 param2 param3 ...-->
    </select>

    @Test
    public void testFindUserByIdAndName(){
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        User user = userDao.findUserByIdAndName(41,"张三丰");
        System.out.println(user);
    }

1.1.3.注解参数绑定(推荐)

    //传递多个参数
    public User findUserByIdAndName2(@Param("id") Integer id,
                                     @Param("username")String username);

    <select id="findUserByIdAndName2" resultType="com.wt.pojo.User" >
        SELECT * FROM user
        WHERE id = #{id} AND username = #{username}
    </select>

    @Test
    public void testFindUserByIdAndName2(){
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        User user = userDao.findUserByIdAndName2(41,"张三丰");
        System.out.println(user);
    }

1.1.4.对象参数绑定(推荐)

    //使用对象属性进行参数绑定
    public User findUserByUserInfo(User user);

    <select id="findUserByUserInfo" parameterType="com.wt.pojo.User" 
            									resultType="com.wt.pojo.User">
        SELECT * FROM user
        WHERE id = #{id} AND username = #{username}<!--参数为对象时，#{属性名}-->
    </select>

    @Test
    public void testFindUserByName(){
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        User userInfo = new User();
        userInfo.setId(41);
        userInfo.setUsername("张三丰");
        User user = userDao.findUserByUserInfo(userInfo);
        System.out.println(user);
    }

1.1.5.Map参数绑定

	//使用Map进行参数绑定
	public User findUserByMap(Map<String, Object> map);

    <select id="findUserByMap" 
            parameterMap="java.util.Map" resultType="com.wt.pojo.User">
        SELECT * FROM user
        WHERE id = #{id} AND username = #{username}
    </select>

    @Test
    public void testFindUserByMap(){
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("id",41);
        map.put("username","张三丰");
        User user = userDao.findUserByMap(map);
        System.out.println(user);
    }

1.1.6.模糊查询(推荐使用${})

    //模糊查询
    public  List<User> findUserByName(String username);

    <select id="findUserByName" parameterType="string" resultType="com.wt.pojo.User">
        <!-- select * from user where username like concat('%',#{username},'%') -->
        select * from user where username like '%${value}%'<!--${}括号中只能是value-->
    </select>

    @Test
    public void testFindUserByName(){
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        List<User> userList = userDao.findUserByName("张");
        for (User user : userList) {
            System.out.println(user);
        }
    }

1.1.7.sql注入

    //sql注入
    public User login(User user);

    <select id="login" parameterType="com.wt.pojo.User" resultType="com.wt.pojo.User">
        select * from user where username = '${username}' and password = '${password}'
    </select>

    @Test
    public void testLogin(){
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        User userInfo = new User();
        //' #会使sql语句后面的内容被注释掉，此时无论密码输入什么都会成功
        userInfo.setUsername("张三丰' #");
        userInfo.setPassword("123");
        User user = userDao.login(userInfo);
        System.out.println(user);
    }

#{} 和${}的区别：

• #{}符
  1. #{}表示一个占位符号 通过#{}可以实现 preparedStatement 向占位符中设置值，自动进行 java 类型和 jdbc 类型转换
  2. #{}可以接收简单类型值或 pojo 属性值。 如果 parameterType 传输单个简单类 型值，#{}括号中可以是 value 或其它名称。
  3. #{}可以有效防止 sql 注入。
• ${}符
  1. $表示拼接sql串通过${}可以将 parameterType 传入的内容拼接在 sql 中且不进行 jdbc 类型转换
  2. $可以接收简单类型值或pojo属性值，如果parameterType传输单个简单类型值，${}括号中只能是 value

1.1.8.聚合函数查询

//聚合函数查询
public Integer getTotal();

<!--聚合函数查询-->
<select id="getTotal" resultType="int">
    SELECT COUNT(id) FROM user
</select>

@Test
public void testGetTotal(){
    Integer total = userDao.getTotal();
    System.out.println(total);
}

1.2.删除

    //删除
    public void deleteUserById(Integer id);

    <delete id="deleteUserById" parameterType="Integer">
        DELETE FROM user
        WHERE id = #{id}
    </delete>

    @Test
    public void testDeleteUserById(){
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        userDao.deleteUserById(41);
        sqlSession.commit();
    }

1.3.修改

    //修改
    public void updateUserById(User user);

    <update id="updateUserById" parameterType="com.wt.pojo.User">
        update user set username=#{username},password=#{password},
        birthday=#{birthday},sex=#{sex},address=#{address} where id=#{id}
    </update>

    @Test
    public void testUpdateUserById(){
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        User user = new User();
        user.setUsername("马德华");
        user.setPassword("111");
        user.setBirthday(new Date());
        user.setSex("男");
        user.setAddress("高老庄");
        user.setId(42);
        userDao.updateUserById(user);
        sqlSession.commit();
    }

1.4.添加

    //添加
    public void insertUser(User user);

<insert id="insertUser" parameterType="com.wt.pojo.User">
        <!--
            主键回填：新增之后，获取新增记录的id值
            keyProperty="id"：主键对应实体类的属性
            order="AFTER"：先执行插入语句，之后再执行查询语句
            resultType="java.lang.Integer"：主键的数据类型
        -->
        <selectKey keyProperty="id" order="AFTER" resultType="java.lang.Integer">
            /*查询出刚刚插入的记录自增长id*/
            select last_insert_id();
        </selectKey>
        insert into user(username,password,birthday,sex,address)
        values(#{username},#{password},#{birthday},#{sex},#{address});
</insert>

或者

	<!--
        useGeneratedKeys=“true”：获取数据库生成的主键
        keyProperty=“id”：主键对应实体类的属性
    -->
    <insert id="insertUser" useGeneratedKeys="true" 
            keyProperty="id" parameterType="com.wt.pojo.User">
        <!--
            主键回填：新增之后，获取新增记录的id值
            keyProperty="id"：主键对应实体类的属性
            order="AFTER"：先执行插入语句，之后再执行查询语句
            resultType="java.lang.Integer"：主键的数据类型

            <selectKey keyProperty="id" order="AFTER" resultType="java.lang.Integer">
                /*查询出刚刚插入的记录自增长id*/
                select last_insert_id();
            </selectKey>
         -->
        insert into user(username,password,birthday,sex,address)
        values(#{username},#{password},#{birthday},#{sex},#{address})
    </insert>

    @Test
    public void testInsertUser(){
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        User user = new User();
        user.setUsername("刘德华");
        user.setPassword("111");
        user.setBirthday(new Date());
        user.setSex("男");
        user.setAddress("香港");
        userDao.insertUser(user);
        System.out.println("新增记录的id值："+user.getId());
        sqlSession.commit();
    }