CRUD

1. 注意点

• 增删改需要提交事务，即sqlSession.commit();
• namespace中的值要和接口的带包名称一致。

2. Select

/**
     * 获取单个用户信息
     * @return
     */
User getUser(int id);

<select id="getUser" resultType="org.gs.pojo.User" parameterType="int">
  select *
  from user
  where id = #{id}
</select>

@Test
public void getUser() {
  int id = 1;
  SqlSession sqlSession = null;
  try {
    sqlSession = MyBatisUtils.getSqlsession();

    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    User user = mapper.getUser(id);
    System.out.println(user);

  } finally {
    sqlSession.close();
  }
}

2.1. 模糊查询的两种方式

1. 在Java代码执行的时候拼接通配符%

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

2. 在mapper的sql中拼接使用通配符%

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

3. Insert

/**
     * 添加单个用户
     * @param user
     * @return
     */
int addUser(User user);

<insert id="addUser" parameterType="org.gs.pojo.User">
  insert into user (id, name, pwd)
  values (#{id}, #{name}, #{pwd})
</insert>

@Test
public void addUser() {
  User user = new User(4, "赵六", "123321");
  SqlSession sqlSession = null;
  try {
    sqlSession = MyBatisUtils.getSqlsession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    int i = mapper.addUser(user);
    if (i > 0) {
      sqlSession.commit();
      System.out.println("插入成功");
    } else {
      sqlSession.rollback();
      System.out.println("插入失败");
    }
  } finally {
    sqlSession.close();
  }
}

4. Update

/**
     * 修改单个用户
     * @param user
     * @return
     */
int updateUser(User user);

<update id="updateUser" parameterType="org.gs.pojo.User">
  update user
  set name = #{name},
  pwd  = #{pwd}
  where id = #{id}
</update>

@Test
public void updateUser() {
  User user = new User(4, "陈七", "23333");
  SqlSession sqlSession = null;
  try {
    sqlSession = MyBatisUtils.getSqlsession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    int i = mapper.updateUser(user);
    if (i > 0) {
      sqlSession.commit();
      System.out.println("修改成功");
    } else {
      sqlSession.rollback();
      System.out.println("修改失败");
    }
  } finally {
    sqlSession.close();
  }
}

5. Delete

/**
     * 删除单个用户
     * @param id
     * @return
     */
int deleteUser(int id);

<delete id="deleteUser" parameterType="int">
  delete
  from user
  where id = #{id}
</delete>

@Test
public void deleteUser(){
  int id = 4;
  SqlSession sqlSession = null;
  try {
    sqlSession = MyBatisUtils.getSqlsession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    int i = mapper.deleteUser(id);
    if (i > 0) {
      sqlSession.commit();
      System.out.println("删除成功");
    } else {
      sqlSession.rollback();
      System.out.println("删除失败");
    }
  } finally {
    sqlSession.close();
  }
}

6. 利用Map作为参数传递

假设，我们的实体类，或者数据库中的表，字段或者参数过多，如果规范允许，可以考虑使用Map。

int addUser2(Map<String,Object> map);

<!--对象中的属性，可以直接取出来    传递map的key-->
<insert id="addUser2" parameterType="map">
  insert into mybatis.user (id, pwd)
  values (#{userid}, #{password});
</insert>

@Test
public void addUser2() {
  Map<String, Object> map = new HashMap<String, Object>();
  map.put("userid", 5);
  map.put("password", "2222333");
  SqlSession sqlSession = null;
  try {
    sqlSession = MyBatisUtils.getSqlSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);

    int i = mapper.addUser2(map);
    if (i > 0) {
      sqlSession.commit();
      System.out.println("插入成功");
    } else {
      sqlSession.rollback();
      System.out.println("插入失败");
    }
  } finally {
    sqlSession.close();
  }
}

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

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

只有一个基本类型参数的情况下，可以直接在sql中取到，可以不设置parameterType。