一对一查询：通过一方关联查询出另外一方的关系数据
创建数据库和相关数据表创建需要关联查询的实体类,里面包含相关的属性 //丈夫查妻子 public class Husband { private Integer id; private String name; private Wife wife; } public class Wife { private Integer id; private String name; } 编写HusbandMapper接口，为了调用方法 public interface HusbandMapper { List<Husband> findByName(String name); } 编写相关的Sql语句 关联查询的时候需要用resultMap进行自定义标签的映射非关联单表查询的时候可以直接使用resultType进行查询property表示属性对应的是实体类的字段名称column表示的是自己定义的属性值，与sql语句定义的字段名称相同association和javaType在一对一关联查询的时候使用association里映射的是被关联查询的表和属性值 <resultMap id="husbandAndWife" type="husband"> <id property="id" column="id"/> <result property="name" column="name"/> <association property="wife" javaType="wife"> <id property="id" column="wid"/> <result property="name" column="wname"/> </association> </resultMap> <select id="findByName" parameterType="string" resultMap="husbandAndWife"> select h.id,h.name,w.id wid, w.name wname from t_husband h,t_wife w where h.id = w.id and h.name = #{name} </select> 编写HusbandMapperTest测试类 public class CustomerMapperTest { private SqlSessionFactory sqlSessionFactory; @Before public void init() throws IOException { //加载核心配置文件 InputStream inputStream = Resources.getResourceAsStream("mybatis-config"); //创建会话工厂 sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); } @Test public void test(){ //打开会话工厂 SqlSession sqlSession = sqlSessionFactory.openSession(); //获取内容 CustomerMapper mapper = sqlSession.getMapper(CustomerMapper.class); //遍历Customer表数据 List<Customer> ret = mapper.findByName("aa"); //输出 System.out.println(ret); }一个用户可以关联查询出自己的订单信息
创建数据库和相关数据表创建需要关联查询的实体类,里面包含相关的属性 public class Customer { private Integer id; private String name; private List<Order> orders; } public class Order { private Integer id; private String goods; private Integer cid; } 编写CustomerMapper接口，为了调用方法 public interface CustomerMapper { List<Customer> findByName(String name); } 编写相关的Sql语句 collection和ofType用于对多查询的场合左外连接，以左边表为基础和交集取值 <resultMap id="customerAndOrders" type="customer"> <id property="id" column="id"/> <result property="name" column="name"/> <collection property="orders" ofType="order"> <id property="id" column="id"/> <result property="o.goods" column="o.goods"/> <result property="o.c_id" column="o.c_id"/> </collection> </resultMap> <select id="findByName" resultMap="customerAndOrders"> select c.* ,o.id oid,o.goods,o.c_id from t_customer c left join t_order o on c.id = o.c_id where c.name = #{name} </select> 编写CustomerMapperTest测试类 public class CustomerMapperTest { private SqlSessionFactory sqlSessionFactory; @Before public void init() throws IOException { InputStream inputStream = Resources.getResourceAsStream("mybatis-config"); sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); } @Test public void test(){ SqlSession sqlSession = sqlSessionFactory.openSession(); CustomerMapper mapper = sqlSession.getMapper(CustomerMapper.class); List<Customer> ret = mapper.findByName("aa"); System.out.println(ret); }一个老师对应多个学生，一个学生也对应多个老师
创建数据库和相关数据表创建需要关联查询的实体类,里面包含相关的属性 public class Teacher { private Integer id; private String name; private List<Student> students; } public class Student { private Integer id; private String name; } 编写TeacherMapper接口，为了调用方法 public interface TeacherMapper { List<Teacher> findByName(String name); } 编写相关的Sql语句 多对多查询需要借助一个t_s中间表来完成关联 <resultMap id="teacherAndStudent" type="teacher"> <id property="id" column="id"/> <result property="name" column="name"/> <collection property="students" ofType="student"> <id property="id" column="sid"/> <result property="name" column="sname"/> </collection> </resultMap> <select id="findByName" resultMap="teacherAndStudent"> select t.id,t.name,s.id sid,s.name sname from t_teacher t left join t_s ts on t.id = ts.t_id left join t_student s on ts.s_id = s.id where t.name = #{name} </select> 编写TeacherMapperTest测试类 public class TeacherMapperTest { private SqlSessionFactory sqlSessionFactory; @Before public void init() throws IOException { InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml"); sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); } @Test public void test(){ SqlSession sqlSession = sqlSessionFactory.openSession(); TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class); List<Teacher> ret = mapper.findByName("唐丹"); System.out.println(ret); } }嵌套查询是将原来多表查询中的联合查询语句拆成单个表的查询，再使用MyBatis的语法嵌套在一起
嵌套查询使用时，先查询a表的信息，然后依赖a和b表的外键约束，利用in()，再次查询b表对应到a表上的信息。该方式可以改为饿汉式，内存使用较小，但需要多次访问数据库而导致消耗时间多。
查询一个订单，同时查询出该订单所属的用户
<select id="findById" resultType="customer"> select * from t_customer where id = #{id} </select> <resultMap id="orderAndCustomer2" type="order"> <id property="id" column="id"/> <result property="goods" column="goods"/> <result property="cid" column="cid"/> <association property="customer" javaType="customer" column="c_id" select="findById"/> </resultMap> <select id="findByGoods2" resultMap="orderAndCustomer2"> select * from t_order where goods = #{goods} </select>查询一个用户，与此同时查询该用户具有的订单
<resultMap id="baseMap" type="order"> <id property="id" column="id"/> <result property="goods" column="goods"/> <result property="cid" column="c_id"/> </resultMap> <select id="findByCid" resultMap="baseMap"> select * from t_order where c_id = #{cid} </select> <resultMap id="customerAndOrders2" type="customer"> <id property="id" column="id"/> <result property="name" column="name"/> <collection property="orders" ofType="order" column="id" select="findByCid"/> </resultMap> <select id="findByName2" resultMap="customerAndOrders2"> select * from t_customer where name = #{name} </select>****标签相当于 where 1=1
如果没有条件，就不会拼接where关键字
where标签可以忽略我们成立条件前面的and或者or关键字
<select id="findByGirl" resultType="girl"> select * from girl <where> <if test="name != null and name != ''"> and name = #{name} </if> <if test="age != null and age != ''"> and age = #{age} </if> <if test="address != null and address != ''"> and address = #{address} </if> </where> </select> <select id="findByIds" resultType="girl"> select * from girl <where> <foreach collection="list" open="id in (" item="id" separator="," close=")"> #{id} </foreach> </where> </select>