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>

    @Testpublic 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 userWHERE id = #{arg0} AND username = #{arg1} <!--arg0 arg1 arg2 ...--></select>

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

    @Testpublic 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 userWHERE id = #{id} AND username = #{username}</select>

    @Testpublic 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 userWHERE id = #{id} AND username = #{username}<!--参数为对象时，#{属性名}--></select>

    @Testpublic 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 userWHERE id = #{id} AND username = #{username}</select>

    @Testpublic 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>

    @Testpublic 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>

    @Testpublic 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 userWHERE id = #{id}</delete>

    @Testpublic 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>

    @Testpublic 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>

    @Testpublic 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();}