02 - mybatis - crud

3 crud

注意：所有增删改都必须提交事务

提取共有代码

SqlSession session = null;

@Before
public void befor() throws IOException {
    // 加载配置文件
    InputStream stream = Resources.getResourceAsStream("mybatis.xml");
    //创建会话工厂
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(stream);
    // 打开session
    session = sqlSessionFactory.openSession();
}

@After
public void afte(){
    if(session!=null){
        session.close();
    }
}

增加

xml配置

<!--添加-->
<insert id="add" parameterType="cn.laixueit.pojo.User">
   <selectKey keyColumn="id" keyProperty="id" resultType="int">
       select last_insert_id()
   </selectKey>
   insert into user(username)
   values(#{username})
</insert>

@Test
public void add(){
   UserMapper userMapper = session.getMapper(UserMapper.class);

   StringBuffer stringBuffer = new StringBuffer();
   for (int i = 0; i < 10; i++) {
       char c = (char) (Math.random() * 26 + 97);
       stringBuffer.append(c);
   }


   User user = new User();
   user.setUsername(stringBuffer.toString());

   int i = userMapper.add(user);

   // 必须要执行事务提交
   session.commit();
   System.out.println(user);

}

增加返回自增主键

 <selectKey keyProperty="id" keyColumn="id" resultType="int">
     select last_insert_id()
</selectKey>

删除

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

@Test
public void delete() throws IOException {

   // 加载配置文件
   InputStream stream = Resources.getResourceAsStream("mybatis.xml");
   //创建会话工厂
   SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(stream);

   SqlSession session = sqlSessionFactory.openSession(true);

   UserMapper userMapper = session.getMapper(UserMapper.class);

   int i = userMapper.delete(75);

   System.out.println(i);
}

修改

<update id="update" parameterType="cn.laixueit.pojo.User">
       update user set username= #{username} where id =#{id}
   </update>

@Test
public void update() throws IOException {

   // 加载配置文件
   InputStream stream = Resources.getResourceAsStream("mybatis.xml");
   //创建会话工厂
   SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(stream);

   SqlSession session = sqlSessionFactory.openSession(true);

   UserMapper userMapper = session.getMapper(UserMapper.class);

   int i = userMapper.update(new User(75, "aaa"));

   System.out.println(i);
}

查询所有/查询单条

<!--查所有-->
   <select id="findAll" resultType="cn.laixueit.pojo.User">
       select * from user
   </select>

   <!--查单个根据id-->
   <select id="findById" resultType="cn.laixueit.pojo.User" parameterType="int">
       select * from user where id = #{id}
   </select>

@Test
public void findAll() throws IOException {

   // 得到mapper的实例
   UserMapper userMapper = session.getMapper(UserMapper.class);

   List<User> users = userMapper.findAll();

   System.out.println(users);

}

@Test
public void findById(){
   UserMapper userMapper = session.getMapper(UserMapper.class);

   User user = userMapper.findById(44);

   System.out.println(user);

}

4 查询

1 多条件查询

<select id="findByNameAndSex" resultType="cn.laixueit.pojo.User" parameterType="cn.laixueit.pojo.User">
        select * from user where username=#{username} and sex = #{sex}
    </select>

@Test
    public void findByNameAndSex() throws IOException {

        // 加载配置文件
        InputStream stream = Resources.getResourceAsStream("mybatis.xml");
        //创建会话工厂
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(stream);

        SqlSession session = sqlSessionFactory.openSession(true);

        UserMapper userMapper = session.getMapper(UserMapper.class);

        List<User> byNameAndSex = userMapper.findByNameAndSex(new User("老王", "男女"));

        System.out.println(byNameAndSex);

    }

2 分页查询

两种方法

<select id="selectPage" resultType="cn.laixueit.pojo.User" parameterType="cn.laixueit.common.Page">
    select * from user limit #{startPage},#{pageSize}
</select>


<select id="selectPage2" resultType="cn.laixueit.pojo.User" parameterType="map">
    select * from user limit #{startPage1},#{pageSize2}
</select>

@Test
    public void selectPage() throws IOException {

        // 加载配置文件
        InputStream stream = Resources.getResourceAsStream("mybatis.xml");
        //创建会话工厂
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(stream);

        SqlSession session = sqlSessionFactory.openSession(true);

        UserMapper userMapper = session.getMapper(UserMapper.class);


        Page page = new Page();
        page.setStartPage((2-1)*5);
        page.setPageSize(5);

        List<User> pageList = userMapper.selectPage(page);

        for (User user : pageList) {
            System.out.println(user);
        }

    }

    @Test
    public void selectPage2() throws IOException {

        // 加载配置文件
        InputStream stream = Resources.getResourceAsStream("mybatis.xml");
        //创建会话工厂
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(stream);

        SqlSession session = sqlSessionFactory.openSession(true);

        UserMapper userMapper = session.getMapper(UserMapper.class);


        HashMap<String, Integer> map = new HashMap<String, Integer>();
        map.put("startPage1",(2-1)*5);
        map.put("pageSize2",5);

        List<User> pageList = userMapper.selectPage2(map);

        for (User user : pageList) {
            System.out.println(user);
        }

    }

3 分页条件查询

public List<User> selectPageCondition(Map map);

<select id="selectPageCondition" resultType="cn.laixueit.pojo.User" parameterType="map">
    select * from user where username  like '%${user.username}%' limit #{startPage},#{pageSize}
</select>

@Test
public void selectPageCondition() throws IOException {

    // 加载配置文件
    InputStream stream = Resources.getResourceAsStream("mybatis.xml");
    //创建会话工厂
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(stream);

    SqlSession session = sqlSessionFactory.openSession(true);

    UserMapper userMapper = session.getMapper(UserMapper.class);


    User u = new User();u.setUsername("华硕");


    HashMap<String, Object> map = new HashMap<String, Object>();
    map.put("startPage",(1-1)*5);
    map.put("pageSize",5);
    map.put("user",u);

    List<User> pageList = userMapper.selectPageCondition(map);

    for (User user : pageList) {
        System.out.println(user);
    }

}        

4 模糊查询

<select id="findLike" resultType="cn.laixueit.pojo.Student" parameterType="string">
   select * from student where name like #{name}
</select>

<select id="findLike2" resultType="cn.laixueit.pojo.Student" parameterType="string">
   select * from student where name like "%"#{name}"%"
</select>

<select id="findLike3" resultType="cn.laixueit.pojo.Student" parameterType="string">
   select * from student where name like '%${name}%'
</select>

@Test
public void findLike() throws IOException {

   StudentMapper mapper = session.getMapper(StudentMapper.class);

   List<Student> studentList = mapper.findLike("%"+"零零"+"%");

   studentList.forEach(System.out::println);

}

@Test
public void findLike2() throws IOException {

   StudentMapper mapper = session.getMapper(StudentMapper.class);

   List<Student> studentList = mapper.findLike2("零零");

   studentList.forEach(System.out::println);

}


@Test
public void findLike3() throws IOException {

   StudentMapper mapper = session.getMapper(StudentMapper.class);

   List<Student> studentList = mapper.findLike3("零零");

   studentList.forEach(System.out::println);

}