学习博客：【MyBatis】增删查改

增删查改

1. namespace

其中的包名要和Mapper接口的包名一致

2. select、insert、update、delete

• id：对应namespace中的方法名
• resultType：SQL语句执行的返回值
• parameterType：参数类型

步骤：

1. 编写接口

   import com.yl.pojo.User;
   import java.util.List;
   
   public interface UserMapper {
   
       //根据ID查询用户
       User getUserById(int id);
   
       //添加用户
       int addUser(User user);
   
       //修改用户
       int updateUser(User user);
   
       //删除用户
       int delUser(int id);
       
   }
   

2. 编写对应的mapper中的sql语句

   <?xml version="1.0" encoding="GBK" ?>
   <!DOCTYPE mapper
           PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
           "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
   <!--namespace 绑定一个对应的Dao\Mapper接口-->
   <mapper namespace="com.yl.dao.UserMapper">
       <!--select 查询语句-->
       <!--id=方法  resultMap  resultType-->
       <select id="getUserList" resultType="com.yl.pojo.User">
           select * from mybatis.user
       </select>
   
       <!--通过ID查询用户-->
       <select id="getUserById" parameterType="int" resultType="com.yl.pojo.User">
           select * from mybatis.user where id = #{id}
       </select>
   
       <!--添加用户 不需要resultType-->
       <insert id="addUser" parameterType="com.yl.pojo.User">
           insert into mybatis.user (id, name, pwd) values (#{id}, #{name}, #{pwd})
       </insert>
   
       <!--修改用户-->
       <!--参数必须和parameterType的类的属性一一对应，否则取不到-->
       <update id="updateUser" parameterType="com.yl.pojo.User">
           update mybatis.user set `name`=#{name},pwd=#{pwd} where id = #{id}
       </update>
   
       <!--删除用户-->
       <delete id="delUser" parameterType="int">
           delete from mybatis.user where id = #{id}
       </delete>
       
   </mapper>
   

3. 测试

   //根据ID查询用户
   @Test
   public void getUserById(){
   
       SqlSession sqlSession = MyBatisUntils.getSqlSession();
   
       UserMapper mapper = sqlSession.getMapper(UserMapper.class);
   
       User user = mapper.getUserById(2);
       System.out.println(user);
   
       sqlSession.close();
   
   }
   
   //添加用户
   //增删改需要提交事务
   @Test
   public void addUser(){
       SqlSession sqlSession = MyBatisUntils.getSqlSession();
   
       UserMapper mapper = sqlSession.getMapper(UserMapper.class);
   
       mapper.addUser(new User(5, "李安", "qwerty"));
   
       //提交事务
       sqlSession.commit();
       sqlSession.close();
   }
   
   //修改用户
   @Test
   public void updateUser(){
       SqlSession sqlSession = MyBatisUntils.getSqlSession();
   
       UserMapper mapper = sqlSession.getMapper(UserMapper.class);
   
       mapper.updateUser(new User(5, "丽丽", "zxcvbn"));
   
       //提交事务
       sqlSession.commit();
       sqlSession.close();
   }
   
   
   //删除用户
   @Test
   public void delUser(){
       SqlSession sqlSession = MyBatisUntils.getSqlSession();
   
       UserMapper mapper = sqlSession.getMapper(UserMapper.class);
   
       mapper.delUser(5);
   
       //提交事务
       sqlSession.commit();
       sqlSession.close();
   
   }
   

注意：增删改必须提交事务

3. error

• 标签匹配错误

• resource 绑定 mapper 需要使用路径
  

• 配置文件规范

  <?xml version="1.0" encoding="GBK" ?>
  <!DOCTYPE configuration
          PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
          "http://mybatis.org/dtd/mybatis-3-config.dtd">
  
  <!--核心配置文件-->
  <configuration>
      <environments default="development">
          <environment id="development">
              <transactionManager type="JDBC"/>
              <dataSource type="POOLED">
                  <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                  <property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=true&amp;useUnicode=true&amp;characterEncoding=UTF-8&amp;serverTimezone=GMT%2B8"/>
                  <property name="username" value="root"/>
                  <property name="password" value="yL@98"/>
              </dataSource>
          </environment>
      </environments>
  
      <!--每一个Mapper.xml都需要在Mybatis核心配置文件中注册-->
      <mappers>
          <mapper resource="com/yl/dao/UserMapper.xml"/>
      </mappers>
  
  </configuration>
  

• 空指针异常，未注册资源

• xml乱码

• maven资源无法导出

  <!--在build中配置resources，来防止我们资源导出失败的问题-->
  <build>
      <resources>
          <resource>
              <directory>src/main/resources</directory>
              <includes>
                  <include>**/*.properties</include>
                  <include>**/*.xml</include>
              </includes>
              <filtering>true</filtering>
          </resource>
          <resource>
              <directory>src/main/java</directory>
              <includes>
                  <include>**/*.properties</include>
                  <include>**/*.xml</include>
              </includes>
              <filtering>false</filtering>
          </resource>
      </resources>
  </build>
  

4. 万能map

实体类属性过多，使用 map

//map
int addUserMap(Map<String,Object> map);

<insert id="addUserMap" parameterType="map">
    insert into mybatis.user (id, pwd) values (#{userID},#{userPwd})
</insert>

@Test
public void addUserMap(){

    SqlSession sqlSession = MyBatisUntils.getSqlSession();

    UserMapper mapper = sqlSession.getMapper(UserMapper.class);

    Map<String,Object> map = new HashMap<String, Object>();

    map.put("userID", 6);
    map.put("userPwd", "aaaaaa");

    mapper.addUserMap(map);

    sqlSession.commit();
    sqlSession.close();

}

---

键值对实现可定制化，可通过 map 取 key

• map 传递参数，直接在SQL中取出 key 即可
• 对象传递参数，直接在SQL中取出对象的属性即可
• 只有一个基本类型参数时 ，可直接在SQL中取到
• 多个参数用 map 或者注解

User getUserByIdMap(Map<String,Object> map);

<select id="getUserByIdMap" parameterType="map" resultType="com.yl.pojo.User">
    select * from mybatis.user where id = #{adminID} and name = #{adminName}
</select>

@Test
public void getUserByIdMap(){
    SqlSession sqlSession = MyBatisUntils.getSqlSession();

    UserMapper mapper = sqlSession.getMapper(UserMapper.class);

    Map<String,Object> map = new HashMap<String, Object>();

    map.put("adminID",2);
    map.put("adminName", "王记");

    User user = mapper.getUserByIdMap(map);
    System.out.println(user);

    sqlSession.close();

}

5. 模糊查询

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

   List<User> userList = mapper.getUserLike("%李%");
   

   select * from mybatis.user where name like #{value}
   

2. 在SQL拼接中使用通配符，可防止SQL注入

   List<User> userList = mapper.getUserLike("李");
   

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