mybatis学习 CRUD记录

三、CRUD

namespace

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

select

• id：就是对应的namespace中的方法名
• resultType：SQL语句执行的返回值
• parameterType：传入的参数

步骤：

1. 编写接口
2. 编写对应的mapper中的SQL语句
3. 测试

/**
 * 获取全部用户
 * @return
 */
List<User> getUserList();

/**
 * 根据id查询一个用户
 * @param id
 * @return
 */
User getUserById(int id);

<!--查询语句   id 对应着方法名  resultType返回的结果-->
<select id="getUserList" resultType="com.zhang.pojo.User">
    select * from user;
</select>
<select id="getUserById" parameterType="int" resultType="com.zhang.pojo.User">
    select * from user where id = #{id}
</select>

@Test
public void test(){
    //获得SQLSession对象
    SqlSession sqlSession = MybatisUtils.getsqlSession();
    //执行SQL
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    List<User> userList = userMapper.getUserList();
    for (User user : userList) {
        System.out.println(user);
    }
    sqlSession.close();
}
@Test
public void  getUserByIdTest(){
    SqlSession sqlSession = MybatisUtils.getsqlSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    User user = mapper.getUserById(1);
    System.out.println(user);
    sqlSession.close();
}

INSERT

/**
 * 插入一个用户
 * @param user
 * @return
 */
int addUser(User user);

<insert id="addUser" parameterType="com.zhang.pojo.User" >

    insert  into user(id,name,pwd) values (#{id},#{name},#{pwd})
</insert>

@Test
public void getaddUser(){

    SqlSession sqlSession = MybatisUtils.getsqlSession();

    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    int i = mapper.addUser(new User(4, "hhh", "qweqw"));
    if (i > 0) {
        // 提交事务
        sqlSession.commit();
        System.out.println(i);
    }

    sqlSession.close();


}

update

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

<update id="updateUser" parameterType="com.zhang.pojo.User" >

    update user
    set name = #{name},
        pwd  = #{pwd}
    where id = #{id};
</update>

@Test
public void getupdateUser(){

    SqlSession sqlSession = MybatisUtils.getsqlSession();

    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    int i = mapper.updateUser(new User(4, "wwww", "qweqw"));
    if (i > 0) {
        // 提交事务
        sqlSession.commit();
        System.out.println(i);
    }

    sqlSession.close();


}

delete

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

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

@Test
public void deleteUser(){

    SqlSession sqlSession = MybatisUtils.getsqlSession();

    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    int i = mapper.deleteUser(4);
    if (i > 0) {
        // 提交事务
        sqlSession.commit();
        System.out.println(i);
    }

    sqlSession.close();


}

**注意：**增删改需要进行事务提交 SQLSession.commit();

Map

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

模糊查询

1. java代码执行的时候，传递通配符%value% 可能出现SQL注入

   List<User> user = mapper.getUserList("%value%");
   

2. 在SQL拼接的时候使用通配符

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