Mybatis

3、CRUD

3.1、namespace

• namespace中的包名要和Dao/mapper 接口的包名一致！

3.2、步骤

• 编写接口

• 编写对应的mapper中的sql语句

  • <?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE mapper
            PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
            "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
        <!--namespace=绑定一个对应的Dao/Mapper接口-->
        <mapper namespace="com.jcooling.mapper.UserMapper">
        <!--select查询语句-->
        <select id="getUserList" resultType="com.jcooling.pojo.User">
            select * from ibatis.user;
        </select>
        <select id="getUserById" resultType="com.jcooling.pojo.User" parameterType="int">
            select * from ibatis.user where id= #{id};
        </select>
        <!--对象中的属性，可以直接取出来-->
        <insert id="addUser" parameterType="com.jcooling.pojo.User">
            insert into user(id, name, pwd) values (#{id},#{name},#{pwd})
        </insert>
        <update id="updateUser" parameterType="com.jcooling.pojo.User" >
            update user set name=#{name},pwd=#{pwd} where id=#{id}
        </update>
        <update id="deleteUser" parameterType="int">
            delete from user where id=#{id}
        </update>
    </mapper>
    

• 测试

  •  @Test
        public void testSelectAll(){
                //获取SqlSession对象
               SqlSession sqlSession = MybatisUtils.getSqlSession();
                //执行SQL语句
                UserMapper mapper=sqlSession.getMapper(UserMapper.class);
                List<User> userList = mapper.getUserList();
                for (User user : userList) {
                    System.out.println(user);
                //关闭SqlSession
                sqlSession.close();
        }
        @Test
        public void testSelectById(){
            SqlSession sqlSession = MybatisUtils.getSqlSession();
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);
            User userById = mapper.getUserById(2);
            System.out.println(userById);
            sqlSession.close();
        }
        //增删改需要提交事务
        @Test
        public void testAddUser(){
            SqlSession sqlSession = MybatisUtils.getSqlSession();
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);
            int i = mapper.addUser(new User(4, "4", "4"));
            if (i!=0) {
                System.out.println("插入成功");
            }else {
                System.out.println("插入失败");
            }
            //提交事务
            sqlSession.commit();
            sqlSession.close();
        }
        @Test
        public void testUpdateUser(){
            SqlSession sqlSession = MybatisUtils.getSqlSession();
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);
            int i = mapper.updateUser(new User(4, "5", "5"));
            if (i!=0) {
                System.out.println("更新成功");
            }else {
                System.out.println("更新失败");
            }
            //提交事务
            sqlSession.commit();
            sqlSession.close();
        }
      
        @Test
        public void testDeleteUser(){
            SqlSession sqlSession = MybatisUtils.getSqlSession();
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);
            int i = mapper.deleteUser(4);
            if (i!=0) {
                System.out.println("删除成功");
            }else {
                System.out.println("删除失败");
            }
            //提交事务
            sqlSession.commit();
            sqlSession.close();
        }
    

3.3、Select

• 选择，查询语句：

  • id：就是对应的namespace中的方法名；

  • resultType:Sql语句执行的返回值！

  • parameterType：参数类型！

  •     //查询全部用户
        List<User> getUserList();
        //根据id查询用户
        User getUserById(int id);
    

3.4、Insert

•     //insert一个用户
      int addUser(User user);
  

3.5、Update

•     //update一个用户
      int updateUser(User user);
     
  

3.6、Delete

•     //delete一个用户
      int deleteUser(int id);
  

3.7、注意点

• 增删改需要提交事务

3.8、分析错误

• 标签不要匹配错！
• resource 绑定mapper，需要使用路径！
• 程序配置文件必须符合规范！
• NulPointerException，没有注册到资源！
• 输出的xml文件中存在中文乱码问题！I
• maven资源没有导出问题！

3.9、万能Map

•     //万能的Map
      User getUserById1(Map<String,Object> map);
      //万能的Map
      int insertUser(Map<String,Object> map);
  

• <insert id="insertUser" parameterType="map">
          insert into user(id, name, pwd) values (#{userId},#{userName},#{userPwd})
      </insert>
   <select id="getUserById1" resultType="com.jcooling.pojo.User" parameterType="map">
          select * from user where id= #{userId} and name=#{userName};
   </select>
  

•  @Test
      public void testInsertUser() {
          SqlSession sqlSession = MybatisUtils.getSqlSession();
          UserMapper mapper = sqlSession.getMapper(UserMapper.class);
          Map<String, Object> map = new HashMap<String, Object>();
          map.put("userId",4);
          map.put("userName","zhangsan");
          map.put("userPwd","4");
          int i = mapper.insertUser(map);
          if (i!=0) {
              System.out.println("插入成功");
          }else {
              System.out.println("插入失败");
          }
          sqlSession.commit();
          sqlSession.close();
      }
      @Test
      public void testSelectById1(){
          SqlSession sqlSession = MybatisUtils.getSqlSession();
          UserMapper mapper = sqlSession.getMapper(UserMapper.class);
          Map<String, Object> map = new HashMap<String, Object>();
          map.put("userId",4);
          map.put("userName","zhangsan");
          User userById1 = mapper.getUserById1(map);
          System.out.println(userById1);
          sqlSession.close();
      }
  }
  

• Map传递参数，直接在sql中取出key即可！【parameterType="map"】

• 对象传递参数，直接在sql中取对象的属性即可！【parameterType="Object"】

• 只有一个基本类型参数的情况下，可以直接在sql中取到！

• 多个参数用Map，或者注解!