Mybatis中CRUD操作与模糊查询

根据id查询用户

InfpMapper.xml

<?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命名空间 绑定对应的Mapper接口 -->
<mapper namespace="com.bkms.dao.InfoMapper">

    <!-- 查询所有用户 -->
    <select id="getInfoList" resultType="com.bkms.pojo.Info">
        select * from mydb.info
    </select>

    <!-- 根据id查找用户 -->
    <select id="getInfoById" parameterType="int" resultType="com.bkms.pojo.Info">
        select * from mydb.info where id = #{id}
    </select>
</mapper>

InfpMapperTest.java

package com.bkms.dao;

import com.bkms.pojo.Info;
import com.bkms.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class InfoMapperTest {
    @Test
    public void getInfoListTest() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        try {
            InfoMapper sessionMapper = sqlSession.getMapper(InfoMapper.class);
            List<Info> infoList = sessionMapper.getInfoList();
            for (Info info : infoList) {
                System.out.println(info);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            sqlSession.close();
        }
    }

    @Test
    public void getInfoByIdTest() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        InfoMapper mapper = sqlSession.getMapper(InfoMapper.class);
        Info info = mapper.getInfoById(2);
        System.out.println(info);
        sqlSession.close();
    }
}

增加一个用户

	<!-- 增加一个用户 -->
    <insert id="addInfo" parameterType="com.bkms.pojo.Info">
        insert into mydb.info (id, name, email) values (#{id},#{name},#{email});
    </insert>

    @Test
    public void addInfo() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        InfoMapper mapper = sqlSession.getMapper(InfoMapper.class);
        // i 受影响的行数
        int i = mapper.addInfo(new Info(3, "Ray", "Ray246@163.com"));
        if (i > 0) System.out.println("insert success");
        // 提交事务(增删改)
        sqlSession.commit();
        sqlSession.close();
    }

修改一个用户

	<!-- 修改一个用户 -->
    <update id="updateInfo" parameterType="com.bkms.pojo.Info">
        update mydb.info set name=#{name},email=#{email} where id=#{id}
    </update>

	@Test
    public void updateInfo() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        InfoMapper mapper = sqlSession.getMapper(InfoMapper.class);
        int i = mapper.updateInfo(new Info(3, "Bob", "Bob404@163.com"));
        if (i > 0) System.out.println("update success");
        sqlSession.commit();
        sqlSession.close();
    }

删除一个用户

    <!-- 删除一个用户 -->
    <delete id="deleteInfo" parameterType="int">
        delete from mydb.info where id = #{id}
    </delete>

    @Test
    public void deleteInfoTest() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        InfoMapper mapper = sqlSession.getMapper(InfoMapper.class);
        int i = mapper.deleteInfo(4);
        if (i > 0) System.out.println("delete success");
        sqlSession.commit();
        sqlSession.close();
    }

InfoMapper.java

package com.bkms.dao;

import com.bkms.pojo.Info;

import java.util.List;

public interface InfoMapper {
    // 查询全部用户
    List<Info> getInfoList();

    // 根据id查找用户(id是用户的唯一标识)
    Info getInfoById(int id);

    // 增加一个用户
    int addInfo(Info info);

    // 修改一个用户
    int updateInfo(Info info);

    // 删除一个用户
    int deleteInfo(int id);
}

使用Map添加用户

当实体类数据库表字段参数过多，应该考虑Map集合

	<!-- 添加集合中的用户 -->
    <insert id="addInfoByMap" parameterType="map">
        insert into mydb.info (id, name, email) values (#{infoId},#{infoName},#{infoEmail});
    </insert>

	@Test
    public void addInfoByMapTest() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        InfoMapper mapper = sqlSession.getMapper(InfoMapper.class);
        Map<String, Object> map = new HashMap<String,Object>();
        map.put("infoId",4);
        map.put("infoName","Hector");
        map.put("infoEmail","Hector468@163.com");
        int i = mapper.addInfoByMap(map);
        if (i > 0) System.out.println("insert success");
        sqlSession.commit();
        sqlSession.close();
    }

模糊查询

	<!-- 模糊查询 -->
    <select id="getInfoFuzzyQueries" resultType="com.bkms.pojo.Info">
    	<!-- #{%infoName%} 存在sql注入问题，不推荐使用 -->
        select * from mydb.info where name like #{infoName}
    </select>

    @Test
    public void getInfoFuzzyQueries() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        InfoMapper mapper = sqlSession.getMapper(InfoMapper.class);
        List<Info> infos = mapper.getInfoFuzzyQueries("%John%");
        for (Info info : infos) {
            System.out.println(info);
        }
        sqlSession.commit();
        sqlSession.close();
    }