MyBatis的增删改查（CRUD）

喜欢ヅ旅行 2024-02-05 16:53 16阅读 0赞

**目录**

查询

1.单个参数绑定

2.序号参数绑定

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

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

5.Map参数绑定

6.模糊查询

7.sql注入

8.聚合函数查询

删除

修改

添加（主键回填）

--------------------

数据库

### ![1c444bc6887d4c21932066b254aca30d.png][] ###

程序结构

![972df43dc9ef4b2d999ed58fe6761bcf.png][]

### 查询 ###

#### 1.单个参数绑定 ####

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

xml映射文件
      
        <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} 
        </select>

<select id="findUserByIdAndName" resultType="com.by.pojo.User" >
            SELECT * FROM user
            WHERE id = #{param1} AND username = #{param2} 
        </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 '%${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">
            
            <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>

或者

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

[1c444bc6887d4c21932066b254aca30d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/2778f567b92a4d778f55fc74a3c2d631.png
[972df43dc9ef4b2d999ed58fe6761bcf.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/7e7f025eb00741d182ef274e9f528df8.png