Mybatis执行CRUD操作

Mybatis执行CRUD操作

通用步骤

• 编写mapper接口
• 编写mapper配置文件
  • namespace:对应mapper接口的完整包名
  • id：与mapper接口中的方法名对应
  • parameterType：传入SQL语句的参数类型
  • resultType：SQL语句执行的返回类型(完整包名或别名)
• 执行操作
  • 获取SqlSession对象
  • 获取mapper对象
  • 执行相应方法
  • 增删改需要提交事务
  • 关闭Sqlsession对象

1. select

mapper接口

User selectById(int id);

mapper配置文件

< select id="selectUserById" parameterType="int" resultType="ltd.lccyj.domain.User">
    select * from user where id = #{id}
< /select>

测试方法

@Test
public void selectById() {
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    User user = mapper.selectUserById(1);
    System.out.println(user);
    sqlSession.close();
}

模糊查询

传参时传入通配符，防止SQL注入

mapper接口

List< User> selectUserLike(String value);

mapper配置文件

< select id="selectUserLike" parameterType="string" resultType="ltd.lccyj.domain.User">
   select * from user where name like #{value}
< /select>

测试方法

@Test
public void selectUserLike() {
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    List< User> users = mapper.selectUserLike("%刘%");
    System.out.println(users);
    sqlSession.close();
}

ResultMap结果集映射

当实体类属性名与数据库字段名不一致时，mybatis无法自动完成类型转换

此时可以用ResultMap指定映射关系

< resultMap id="userResultMap" type="User">
  < id property="id" column="user_id" />
  < result property="username" column="user_name"/>
  < result property="password" column="hashed_password"/>
< /resultMap>

< select id="selectUsers" resultMap="userResultMap">
  select user_id, user_name, hashed_password
  from some_table
  where id = #{id}
< /select>

2. insert

mapper接口

int addUser(User user);

mapper配置文件

< insert id="addUser" parameterType="ltd.lccyj.domain.User">
   insert into user(id, name, password) values (#{id},#{name},#{password})
< /insert>

测试方法

@Test
public void addUser() {
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    User user=new User(3,"阿巴","123");
    int result = mapper.addUser(user);
    System.out.println(result);
    sqlSession.commit();
    sqlSession.close();
}

3. update

mapper接口

int updateUser(User user);

mapper配置文件

< insert id="addUser" parameterType="ltd.lccyj.domain.User">
   insert into user(id, name, password) values (#{id},#{name},#{password})
< /insert>

测试方法

@Test
public void updateUser() {
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    User user=new User(3,"阿巴","abaaba");
    int result = mapper.updateUser(user);
    System.out.println(result);
    sqlSession.commit();
    sqlSession.close();
}

4. delete

mapper接口

int deleteUserById(int id);

mapper配置文件

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

测试方法

@Test
public void deleteUserById() {
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    int result = mapper.deleteUserById(3);
    System.out.println(result);
    sqlSession.commit();
    sqlSession.close();
}