Mybatis第五天：高级查询

1.Mybatis高级查询

1.1 resultMap

• IUserDao接口

package com.hh.mapper;
import com.hh.domain.User;

public interface IUserDao {
    public User selectUser(Integer id);
}

• 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.hh.mapper.IUserDao">
    <!--id:手动封装的唯一标识名
        type：需要封装的实体类型
        column:表中的列名
        property:实体的属性名-->

    <!--  <id></id>表中主键的封装
          <result></result>表中普通字段的封装-->
    <!-- 如果列名和属性名一一对应，那么可以不用封装-->
    <resultMap id="userResultMap" type="user">
        <id column="id" property="id1"></id>
        <result column="username" property="username1"></result>
        <result column="age" property="age1"></result>
    </resultMap>
    <select id="selectUser" parameterType="int" resultMap="userResultMap">
        select * from users where id=#{id1}
    </select>
</mapper>

• MybatisTest.class

import com.hh.domain.User;
import com.hh.mapper.IUserDao;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class MybatisTest {
    private InputStream input;
    private SqlSessionFactory factory;
    private SqlSession sqlSession;

    @Before
    public void loadConfig() throws IOException {
        input = Resources.getResourceAsStream("SqlMapConfig.xml");
        factory = new SqlSessionFactoryBuilder().build(input);
        sqlSession = factory.openSession();
    }

    @Test
    public void selectUser(){
        IUserDao userDao = sqlSession.getMapper(IUserDao.class);
        User user = (User)userDao.selectUser(1);
        System.out.println(user);
    }

    // 关闭相关资源
    @After
    public void close(){
        try {
            if (input != null) {
                input.close();
            }
            if (sqlSession != null) {
                sqlSession.close();
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }
}

1.2 多条件查询（三种）

1.使用#{arg0}-#{argn}或者#{param1}-#{paramn}来代表参数

• IUserDao接口中

public User selectIdAndAge(Integer id, Integer age);

-UserMapper.xml中

 <select id="selectIdAndAge" resultMap="userResultMap">
        <!--select * from users where id=#{param1} and age=#{param2}-->
        select * from users where id=#{arg0} and age=#{arg1}
  </select>

• MybatisTest中

@Test
public void selectUserByIdAndAge(){
    IUserDao userDao = sqlSession.getMapper(IUserDao.class);
    User user = userDao.selectIdAndAge(1, 18);
    System.out.println(user);
}

2.使用@Param（“id”）注解获取参数

• IUserDao接口中

    public User selectIdAndUsername(@Param("id") Integer id, @Param("username") String username);

-UserMapper.xml中

<select id="selectIdAndUsername" resultMap="userResultMap">
     select * from users where id=#{id} and username=#{username}
</select>

• MybatisTest中

  @Test
  public void selectUserByIdAndUsername(){
       IUserDao userDao = sqlSession.getMapper(IUserDao.class);
       User user = userDao.selectIdAndUsername(1, "susan");
       System.out.println(user);
   }
}

3.使用pojo传递参数

• IUserDao接口中

    public User selectAgeAndUsername(User user);

-UserMapper.xml中

    <!--方式3-->
    <!--输入一个user，通过getAge1获取参数，数据库表的age和age1的属性名可以不一样-->
    <!--输出的时候和输入不一样，必须通过映射-->
    <select id="selectAgeAndUsername" parameterType="user" resultMap="userResultMap">
        select * from users where age=#{age1} and username=#{username1}
    </select>

• MybatisTest中

  @Test
    public void selectUserByAgeAndUsername(){
        IUserDao userDao = sqlSession.getMapper(IUserDao.class);
        User user = new User();
        user.setAge1(18);
        user.setUsername1("susan");

        User getUser = userDao.selectAgeAndUsername(user);
        System.out.println(getUser);
    }

1.3 模糊查询

1.方式1 使用#{}

• IUserDao接口

// 模糊查询方式1
    public List<User> selectUsersByUsername(String username);

• UserMapper.xml

<!--模糊查询方式1-->
<select id="selectUsersByUsername" parameterType="string" resultMap="userResultMap">
     select * from users where username like #{name}
 </select>

• MybatisTest

    // 模糊查询方式1
    @Test
    public void selectByUsername(){
        IUserDao userDao = sqlSession.getMapper(IUserDao.class);
        List<User> users = userDao.selectUsersByUsername("%han%"); //默认会添加'%han%'
        for(User user : users){
            System.out.println(user);
        }
    }

2.方式2 '${value}'

• IUserDao接口

// 模糊查询方式2
    public List<User> selectUsersByUsername2(String username);

• UserMapper.xml

<!--模糊查询方式2-->
    <select id="selectUsersByUsername2" parameterType="string" resultMap="userResultMap">
    	<!--如果是基本数据类型或者string，那么${},里面只能是value，如果是实体类型，那么就写实体类型名-->
        select * from users where username like '${value}'
    </select>

• MybatisTest

  // 模糊查询方式2
    @Test
    public void selectByUsername2(){
        IUserDao userDao = sqlSession.getMapper(IUserDao.class);
        List<User> users = userDao.selectUsersByUsername2("%han%");
        for(User user : users){
            System.out.println(user);
        }
    }

第1种方式，会手动为我们添加上’ '单引号
第2种方式，是直接拼接sql语句的