MyBatis的增删改查(CRUD)

发布时间:2023年12月20日

目录

查询

1.单个参数绑定

2.序号参数绑定

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

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

5.Map参数绑定

6.模糊查询

7.sql注入

8.聚合函数查询

删除

修改

添加(主键回填)


数据库

程序结构

查询

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

#{} 和${}的区别:

  • #{}符

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

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

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

  • ${}符

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

    2. ${}可以接收简单类型值或 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();
    }

文章来源:https://blog.csdn.net/qq_57570052/article/details/135095312
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。