2022.5.13 mybatis增删改查

3、增删改查

步骤：

编写接口
编写对应的mapper中的sql语句
测试

一些参数

namespace中的包名要和Dao/mapper接口的包名一致!
id：就是对应的namespace中的方法名
resultType：Sql语句执行的返回值
parameterType：parameterType :参数类型!

编写接口

 1  package com.xing.dao;
 2  
 3  import com.xing.pojo.User;
 4  
 5  import java.util.List;
 6  
 7  public interface UserMapper {
 8      //查询全部用户
 9      List<User> getUserList();
10  
11      //根据id查询用户
12      User getUserById(int id);
13  
14      //insert一个用户
15      int addUser(User user);
16  
17      //修改用户
18      int updateUser(User user);
19  
20      //删除用户
21      int deleteUser(int id);
22  }

mapper中的sql语句

 1  <?xml version="1.0" encoding="UTF-8" ?>
 2  <!DOCTYPE mapper
 3          PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
 4          "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 5  
 6  <mapper namespace="com.xing.dao.UserMapper">
 7  
 8      <select id="getUserList" resultType="com.xing.pojo.User">
 9          select * from mybatis.user;
10      </select>
11  
12      <select id="getUserById" parameterType="int" resultType="com.xing.pojo.User">
13          select *
14          from mybatis.user
15          where id = #{id};    # 接收方法中的参数id 参数名必须一致
16      </select>
17  
18      <insert id="addUser" parameterType="com.xing.pojo.User">
19          insert into mybatis.user (id, name, pwd)
20          values (#{id}, #{name}, #{pwd}); #对应实体类字段
21      </insert>
22      
23      <update id="updateUser" parameterType="com.xing.pojo.User">
24          update mybatis.user
25          set name = #{name},pwd=#{pwd}
26          where id = #{id};
27      </update>
28  
29      <delete id="deleteUser" parameterType="int">
30          delete
31          from mybatis.user
32          where id = #{id};
33      </delete>
34  </mapper>

测试

 1  package com.xing.dao;
 2  
 3  import com.xing.pojo.User;
 4  import com.xing.utils.MybatisUtils;
 5  import org.apache.ibatis.session.SqlSession;
 6  import org.junit.Test;
 7  
 8  import java.util.List;
 9  
10  public class UserDaoTest {
11      @Test
12      public void test() {
13          SqlSession sqlSession = MybatisUtils.getSqlSession();
14          //方式一：执行  得到接口对象调用其中方法  接口的class对象
15          UserMapper userDao = sqlSession.getMapper(UserMapper.class);
16          List<User> userList = userDao.getUserList();
17  
18          for (User user : userList) {
19              System.out.println(user);
20          }
21  
22          //关闭SqlSession
23          sqlSession.close();
24      }
25  
26      @Test
27      public void getUserById() {
28          SqlSession sqlSession = MybatisUtils.getSqlSession();
29  
30          UserMapper mapper = sqlSession.getMapper(UserMapper.class);
31          User user = mapper.getUserById(1);//查询一号用户
32          System.out.println(user);
33  
34  
35          sqlSession.close();
36      }
37  
38      //增删改需要提交事务
39      @Test
40      public void addUser() {
41          SqlSession sqlSession = MybatisUtils.getSqlSession();
42  
43          UserMapper mapper = sqlSession.getMapper(UserMapper.class);
44          mapper.addUser(new User(4, "小黄", "123456"));
45  
46          //提交事务 才能插入数据库
47          sqlSession.commit();
48          sqlSession.close();
49      }
50  
51      @Test
52      public void updateUser() {
53          SqlSession sqlSession = MybatisUtils.getSqlSession();
54  
55          UserMapper mapper = sqlSession.getMapper(UserMapper.class);
56          mapper.updateUser(new User(4, "大黄", "123456"));
57  
58          sqlSession.commit();
59          sqlSession.close();
60      }
61  
62      @Test
63      public void deleteUser() {
64          SqlSession sqlSession = MybatisUtils.getSqlSession();
65  
66          UserMapper mapper = sqlSession.getMapper(UserMapper.class);
67          mapper.deleteUser(4);
68  
69          sqlSession.commit();
70          sqlSession.close();
71      }
72  
73  
74  }
75

getUserById()

addUser()

updateUser()

deleteUser()

万能Map

假设，我们的实体类，或者数据库中的表，字段或者参数过多，我们应当考虑使用Map!

 1  package com.xing.dao;
 2  
 3  import com.xing.pojo.User;
 4  
 5  import java.util.List;
 6  import java.util.Map;
 7  
 8  public interface UserMapper {
 9      //insert一个用户
10      int addUser(User user);
11  
12      //万能的map
13      int addUser2(Map<String, Object> map);
14  
15  }

 1  <?xml version="1.0" encoding="UTF-8" ?>
 2  <!DOCTYPE mapper
 3          PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
 4          "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 5  
 6  <mapper namespace="com.xing.dao.UserMapper">
 7      
 8      <insert id="addUser" parameterType="com.xing.pojo.User">
 9          insert into mybatis.user (id, name, pwd)
10          values (#{id}, #{name}, #{pwd});
11      </insert>
12  
13  <!--    传递map的key  可以随意定制参数名-->
14      <insert id="addUser2" parameterType="map">
15          insert into mybatis.user (id, name, pwd)
16          values (#{userid}, #{userName}, #{passWord});
17      </insert>
18      
19  
20  </mapper>

 1  package com.xing.dao;
 2  
 3  import com.xing.pojo.User;
 4  import com.xing.utils.MybatisUtils;
 5  import org.apache.ibatis.session.SqlSession;
 6  import org.junit.Test;
 7  
 8  import java.util.HashMap;
 9  import java.util.List;
10  import java.util.Map;
11  
12  public class UserDaoTest {
13  
14      //增删改需要提交事务
15      @Test
16      public void addUser() {
17          SqlSession sqlSession = MybatisUtils.getSqlSession();
18  
19          UserMapper mapper = sqlSession.getMapper(UserMapper.class);
20          mapper.addUser(new User(4, "小黄", "123456"));
21  
22          //提交事务 才能插入数据库
23          sqlSession.commit();
24          sqlSession.close();
25      }
26  
27      @Test
28      public void addUser2() {
29          SqlSession sqlSession = MybatisUtils.getSqlSession();
30  
31          UserMapper mapper = sqlSession.getMapper(UserMapper.class);
32  
33          Map<String, Object> map = new HashMap<>();
34          map.put("userid", 5);
35          map.put("userName", "大黄");
36          map.put("passWord", "123456");
37  
38          mapper.addUser2(map);
39  
40          //提交事务 才能插入数据库
41          sqlSession.commit();
42          sqlSession.close();
43      }
44  
45  
46  }

Map传递参数，直接在sql中取出key即可!【parameterType="map"】
对象传递参数，直接在sql中取对象的属性即可!【parameterType="Object"】
只有一个基本类型参数的情况下，可以直接在sql中取到! 可以不写parameterType
多个参数用Map,或者注解!

思考题模糊查询怎么写?

1.Java代码执行的时候，传递通配符%%

List<user> userList = mapper.getuserLike("%李%");

2.在sql拼接中使用通配符! 会有sql注入问题

select * from mybatis.user where name like "%"#{value]"%"

 1  package com.xing.dao;
 2  
 3  import com.xing.pojo.User;
 4  
 5  import java.util.List;
 6  import java.util.Map;
 7  
 8  public interface UserMapper {
 9      List<User> getUserLike(String value);
10  }

 1  <?xml version="1.0" encoding="UTF-8" ?>
 2  <!DOCTYPE mapper
 3          PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
 4          "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 5  
 6  <mapper namespace="com.xing.dao.UserMapper">
 7      <select id="getUserLike" resultType="com.xing.pojo.User">
 8          select *
 9          from mybatis.user
10          where name LIKE #{value};
11      </select>
12  </mapper>

 1  package com.xing.dao;
 2  
 3  import com.xing.pojo.User;
 4  import com.xing.utils.MybatisUtils;
 5  import org.apache.ibatis.session.SqlSession;
 6  import org.junit.Test;
 7  
 8  import java.util.HashMap;
 9  import java.util.List;
10  import java.util.Map;
11  
12  public class UserDaoTest {
13      @Test
14      public void getUserLike() {
15          SqlSession sqlSession = MybatisUtils.getSqlSession();
16          //方式一：执行  得到接口对象调用其中方法  接口的class对象
17          UserMapper mapper = sqlSession.getMapper(UserMapper.class);
18          List<User> userLike = mapper.getUserLike("%小%");
19  
20          for (User user : userLike) {
21              System.out.println(user);
22          }
23  
24          //关闭SqlSession
25          sqlSession.close();
26      }
27  }
28