Mybatis学习笔记--Mybatis实现增删查改

本文章涉及环境版本：

• mysql 5.7
• Mybatis 3.5.x
• Maven 3.6.x
• JDK 1.8

项目代码仓库：
https://github.com/Gang-bb/Study-Record/tree/main/bzhan-mybatis-study
需要clone整个bzhan-mybatis-study项目
(整体是一个maven多module工程)
该文章对应：《bzhan-mybatis-study01 》module项目

1. select

选择，查询语句;

• id : 就是对应的namespace中接口的方法名；
• resultType：Sql语句执行的返回值！
• parameterType ： 参数类型！

1. UserMapper接口中添加

User selectUserById(int id);

2. UserMapper.xml文件中添加查询语句

<select id="selectUserById" resultType="com.gangbb.model.pojo.User" parameterType="_int">
        select * from user where id = #{id}
</select>

3. 测试

@Test
public void testSelectOne(){
	SqlSession sqlSession = null;
    try {
            //1. 获取sqlsession对象
            sqlSession = MybatisUtil.openSession();

            //2.方式一： getMapper
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            User user = userMapper.selectUserById(2);

            //方式二：
            //User user = sqlSession.selectOne("selectUserById", 1);

            System.out.println(user);
        } finally {
            //3. 关闭sqlsession
            sqlSession.close();

	}
}

4. 结果

2. update

1. UserMapper接口中添加

int updateUser(User user);

2. UserMapper.xml文件中添加查询语句

<update id="updateUser">
    update user set name=#{name}, password=#{password} where id = #{id}
</update>

3. 测试

/**
 * 更新一个用户信息
 */
@Test
public void UpdateOne(){
    SqlSession sqlSession = null;
    try {
        //1. 获取sqlsession对象
        sqlSession = MybatisUtil.openSession();


        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        //1.直接定义一个user
        User user = new User(1, "Gangbbxx", "123456");
        //或者从数据库中查出记录后在修改
        //ser user = userMapper.selectUserById(1);
        //user.setName("修改后的名字");

        int i = userMapper.updateUser(user);


        //提交事务
        sqlSession.commit(i != 0);
        System.out.println("成功更新" + i + "行");

    } finally {
        //3. 关闭sqlsession
        sqlSession.close();

    }
}

4. 结果

3. Insert

1. UserMapper接口中添加

int insertUser(User user);

2. UserMapper.xml文件中添加查询语句

<insert id="insertUser">
    insert into user(id, name, password) values (#{id},#{name},#{password});
</insert>

3. 测试

/**
 * 测试插入一条数据
 */
@Test
public void insertOne(){
    SqlSession sqlSession = null;
    try {
        //1. 获取sqlsession对象
        sqlSession = MybatisUtil.openSession();

        //2.方式一： getMapper
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user = new User(null, "Gangbb", "123456");
        //int i = userMapper.insertUser(user);

        //方式二：
        int i =  sqlSession.insert("insertUser", user);

        //提交事务
        sqlSession.commit(i != 0);
        System.out.println("成功插入" + i + "行");

    } finally {
        //3. 关闭sqlsession
        sqlSession.close();

    }
}

4. 结果

4. Delete

1. UserMapper接口中添加

int deleteUser(int id);

2. UserMapper.xml文件中添加查询语句

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

3. 测试

@Test
public void deleteOne(){
    SqlSession sqlSession = null;
    try {
        sqlSession = MybatisUtil.openSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        int i = userMapper.deleteUser(2);

        //提交事务
        sqlSession.commit(i != 0);
        System.out.println("成功删除" + i + "行");

    } finally {
        //3. 关闭sqlsession
        sqlSession.close();

    }
}

4. 结果

PS: 删改需要提交事务！

5. Map传参

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

1. UserMapper接口中添加

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

2. UserMapper.xml文件中添加查询语句

PS：values中的参数名与传入map中的key一一对应

<insert id="addUser" parameterType="map">
    insert  into user(id, name, password) values (#{uid},#{userName},#{password});
</insert>

3. 测试

/**
* 测试用map传递参数插入一条数据
*/
@Test
public void addOne(){
    SqlSession sqlSession = null;
    try {
        //1. 获取sqlsession对象
        sqlSession = MybatisUtil.openSession();

        //2.方式一： getMapper
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user = new User(null, "Gangbb", "123456");
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("uid", 23);
        map.put("userName", "map添加");
        map.put("password", "123456");
        int i = userMapper.addUser(map);


        //提交事务
        sqlSession.commit(i != 0);
        System.out.println("成功插入" + i + "行");

    } finally {
        //3. 关闭sqlsession
        sqlSession.close();

    }
}

4. 结果

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

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

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

多个参数用Map，或者注解！

5. 模糊查询

表中记录：

• 方式一：调用时传入 %

1. UserMapper接口中添加

List<User> selectUserByKey(String key);

2. UserMapper.xml文件中添加查询语句

<!--模糊查询-->
<select id="selectUserByKey" parameterType="string" resultType="com.gangbb.model.pojo.User">
    select * from user where name  like #{key}
</select>

3. 测试类主要关键代码

// 方式A
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> userList = userMapper.selectUserByKey("%G%");

//方式B
List<User> userList = sqlSession.selectList("selectUserByKey", "%G%");

• 方式二：sql中写%

1. UserMapper接口中添加

List<User> selectUserByKey(String key);

2. UserMapper.xml文件中添加查询语句

<!--模糊查询-->
<select id="selectUserByKey" parameterType="string" resultType="com.gangbb.model.pojo.User">
    select * from user where name  like ‘%"#{value}"%’
</select>

3. 测试类主要关键代码

// 方式A
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> userList = userMapper.selectUserByKey("G");

//方式B
List<User> userList = sqlSession.selectList("selectUserByKey", "G");

• 方式三：CONCAT拼接

1. UserMapper接口中添加

List<User> selectUserByKey(String key);

2. UserMapper.xml文件中添加查询语句

<!--模糊查询-->
<select id="selectUserByKey" parameterType="string" resultType="com.gangbb.model.pojo.User">
     select * from user where name  like  CONCAT(CONCAT('%', #{key}), '%');
</select>

3. 测试类主要关键代码

// 方式A
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> userList = userMapper.selectUserByKey("G");

//方式B
List<User> userList = sqlSession.selectList("selectUserByKey", "G");

三种模糊查询的结果都一样：**