MyBatis的增删改查（CRUD）

Bridge Fish

已于 2024-03-07 18:39:21 修改

阅读量417

点赞数 11

分类专栏： MyBatis 文章标签： mybatis java 开发语言

于 2023-12-20 11:13:28 首次发布

本文链接：https://blog.csdn.net/qq_57570052/article/details/135095312

版权

MyBatis 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

数据库

程序结构

查询

1.单个参数绑定

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

xml映射文件
  <!--
        parameterType:指定输入参数的类型
        resultType：指定数据结果封装的数据类型
		#{id}：它代表占位符，相当于原来 jdbc 部分所学的?，都是用于替换实际的数据。
    -->
    <select id="findUserById" parameterType="java.lang.Integer" 
            				  resultType="com.by.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);
    }

2.序号参数绑定

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

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

	<select id="findUserByIdAndName" resultType="com.by.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);
    }

3.注解参数绑定（推荐）

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

 <select id="findUserByIdAndName2" resultType="com.by.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);
    }

4.对象参数绑定（推荐）

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

    <select id="findUserByUserInfo" parameterType="com.by.pojo.User" 
            									resultType="com.by.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);
    }

5.Map参数绑定

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

   <select id="findUserByMap" 
            parameterMap="java.util.Map" resultType="com.by.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);
    }

6.模糊查询

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

  <select id="findUserByName" parameterType="string" resultType="com.by.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);
        }
    }

7.sql注入

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

 <select id="login" parameterType="com.by.pojo.User" resultType="com.by.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();
        userInfo.setUsername("张三丰' #");
        userInfo.setPassword("123");
        User user = userDao.login(userInfo);
        System.out.println(user);
    }

#{} 和${}的区别：

#{}符

#{}表示一个占位符号通过#{}可以实现 preparedStatement 向占位符中设置值，自动进行 java 类型和 jdbc 类型转换

#{}可以接收简单类型值或 pojo 属性值。如果 parameterType 传输单个简单类型值，#{}括号中可以是 value 或其它名称。

#{}可以有效防止 sql 注入。

${}符

${}表示拼接 sql 串通过${}可以将 parameterType 传入的内容拼接在 sql 中且不进行 jdbc 类型转换

${}可以接收简单类型值或 pojo 属性值，如果 parameterType 传输单个简单类型值，${}括号中只能是 value

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);
}

删除

   //删除
    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();
    }

修改

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

    <update id="updateUserById" parameterType="com.by.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();
    }

添加（主键回填）

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

<insert id="insertUser" parameterType="com.by.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.by.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();
    }