JavaEE（2）Mybatis的CRUD、模糊查询、Map查询

1. Mybatis的CRUD

1. 【强调！！！注意！！！】：增删改一定记得要提交事务！！！
2. 代码示例
（1）mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <environments default="mysql">
        <environment id="mysql">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="com/nelws/dao/UserMapper.xml"/>
    </mappers>
</configuration>

（2）User

public class User {
    private int id;
    private String name;
    private String pwd;

    public User() {
    }

    public User(int id, String name, String pwd) {
        this.id = id;
        this.name = name;
        this.pwd = pwd;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPwd() {
        return pwd;
    }

    public void setPwd(String pwd) {
        this.pwd = pwd;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", pwd='" + pwd + '\'' +
                '}';
    }
}

（3）MyBatisUtils

public class MyBaitsUtils {

    private static SqlSessionFactory sqlSessionFactory;

    static {
        try {
            InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static SqlSession getSqlSession(){
        return sqlSessionFactory.openSession();
    }
}

（4）UserMapper

public interface UserMapper {
    /**
     * 查询全部
     * @return
     */
    List<User> selectUser();

    /**
     * 根据id查询用户
     * @return
     */
    User selectUserById(int id);

    /**
     * 增加用户
     * @return
     */
    int addUser(User user);

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

    /**
     * 通过id删除用户
     */
    int delUserById(int id);
}

（5）UserMapper.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">
<mapper namespace="com.nelws.dao.UserMapper">
    <!--配置查询所有-->
    <select id="selectUser" resultType="com.nelws.domain.User">
        select * from user
    </select>

    <!--根据id查询用户-->
    <select id="selectUserById" resultType="com.nelws.domain.User">
        select * from user where id = #{id}
    </select>

    <!--增加用户-->
    <insert id="addUser" parameterType="com.nelws.domain.User">
        insert into user(id,name,pwd) values (#{id},#{name},#{pwd})
    </insert>

    <!--修改用户-->
    <update id="updateUser" parameterType="com.nelws.domain.User">
        update user set name = #{name}, pwd = #{pwd} where id = #{id}
    </update>

    <!--通过id删除用户-->
    <delete id="delUserById" parameterType="int">
        delete from user where id = #{id}
    </delete>
</mapper>

（6）UserMapperTest

public class UserMapperTest {

    /**
     * 查询全部
     */
    @Test
    public void test(){
        SqlSession sqlSession = MyBaitsUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> users = mapper.selectUser();
        for (User user : users) {
            System.out.println(user);
        }

        sqlSession.close();
    }

    /**
     * 根据id查询用户
     */
    @Test
    public void test2(){
        SqlSession sqlSession = MyBaitsUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        User user = mapper.selectUserById(1);
        System.out.println(user);

        sqlSession.close();
    }

    /**
     * 增加用户
     */
    @Test
    public void test3(){
        SqlSession sqlSession = MyBaitsUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        int i = mapper.addUser(new User(5, "赵六", "12432"));
        System.out.println(i);

        sqlSession.commit();
        sqlSession.commit();
    }

    /**
     * 修改用户
     */
    @Test
    public void test4(){
        SqlSession sqlSession = MyBaitsUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        mapper.updateUser(new User(2,"UZI","123432"));

        sqlSession.commit();
        sqlSession.close();
    }

    /**
     * 通过id删除用户
     */
    @Test
    public void test5(){
        SqlSession sqlSession = MyBaitsUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        mapper.delUserById(5);

        sqlSession.commit();
        sqlSession.close();
    }
}

2. 模糊查询

1. 代码示例
（1）UserMapper

 /**
  * 模糊查询
  * @return
  */
 List<User> selectLike(String value);

（2）UserMapper.xml

<!--模糊查询-->
<select id="selectLike" resultType="com.nelws.domain.User">
   select * from user where name like #{value}
</select>

（3）UserMapperTest

/**
 * 模糊查询
 */
@Test
public void test6(){
   SqlSession sqlSession = MyBaitsUtils.getSqlSession();
   UserMapper mapper = sqlSession.getMapper(UserMapper.class);
   List<User> users = mapper.selectLike("%王%");
   for (User user : users) {
     System.out.println(user);
   }

   sqlSession.close();
}

3. 万能的Map

1. 需求：根据name和pwd查询用户
2. 代码示例
（1）UserMapper

/**
     * 使用map查询用户
     * @param map
     * @return
     */
    User selectUserByMap(Map<String,Object> map);

（2）UserMapper.xml

<!--使用map查询用户-->
    <select id="selectUserByMap" parameterType="map" resultType="com.nelws.domain.User">
        select * from user where name = #{name} and pwd = #{pwd}
    </select>

（3）UserMapperTest

/**
     * 使用map查询用户
     */
    @Test
    public void test7(){
        SqlSession sqlSession = MyBaitsUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("name","张三");
        map.put("pwd","123456");

        User user = mapper.selectUserByMap(map);
        System.out.println(user);

        sqlSession.close();
    }