Mybatis多表联合查询,嵌套查询,动态SQL Mybatis多表联合查询,嵌套查询,动态SQL
Mybatis多表联合查询
⼀对⼀
⼀对⼀查询:通过⼀⽅关联查询出另外⼀⽅的关系数据
1. 创建数据库和相关数据表
2. 创建需要关联查询的实体类,⾥⾯包含相关的属性
//丈夫查妻⼦
public class Husband {
private Integer id;
private String name;
private Wife wife;
}
public class Wife {
private Integer id;
private String name;
}
3. 编写HusbandMapper接⼝,为了调⽤⽅法
public interface HusbandMapper {
List<Husband>findByName(String name);
}
4. 编写相关的Sql语句
1. 关联查询的时候需要⽤resultMap进⾏⾃定义标签的映射
2. ⾮关联单表查询的时候可以直接使⽤resultType进⾏查询
3. property表⽰属性对应的是实体类的字段名称
4. column表⽰的是⾃⼰定义的属性值,与sql语句定义的字段名称相同
5. association和javaType在⼀对⼀关联查询的时候使⽤
6. 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>
5. 编写HusbandMapperTest测试类
public class CustomerMapperTest {
private SqlSessionFactory sqlSessionFactory;
@Before
public void init()throws IOException {
/
/加载核⼼配置⽂件
InputStream inputStream = ResourceAsStream("mybatis-config");
//创建会话⼯⼚
sqlSessionFactory =new SqlSessionFactoryBuilder().build(inputStream);
}
@Test
public void test(){
//打开会话⼯⼚
SqlSession sqlSession = sqlSessionFactory.openSession();
//获取内容
CustomerMapper mapper = Mapper(CustomerMapper.class);
/
/遍历Customer表数据
List<Customer> ret = mapper.findByName("aa");
//输出
System.out.println(ret);
}
⼀对多
⼀个⽤户可以关联查询出⾃⼰的订单信息
1. 创建数据库和相关数据表
2. 创建需要关联查询的实体类,⾥⾯包含相关的属性
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;
}
3. 编写CustomerMapper接⼝,为了调⽤⽅法
public interface CustomerMapper {
List<Customer>findByName(String name);
}
4. 编写相关的Sql语句
1. collection和ofType⽤于对多查询的场合
2. 左外连接,以左边表为基础和交集取值
<resultMap id="customerAndOrders"type="customer">
<id property="id"column="id"/>
sql left join 多表连接<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 ds,o.c_id from t_customer c
left join t_order o on c.id = o.c_id where c.name = #{name}
</select>
5. 编写CustomerMapperTest测试类
public class CustomerMapperTest {
private SqlSessionFactory sqlSessionFactory;
@Before
public void init()throws IOException {
InputStream inputStream = ResourceAsStream("mybatis-config");      sqlSessionFactory =new SqlSessionFactoryBuilder().build(inputStream);
}
@Test
public void test(){
SqlSession sqlSession = sqlSessionFactory.openSession();
CustomerMapper mapper = Mapper(CustomerMapper.class);
List<Customer> ret = mapper.findByName("aa");
System.out.println(ret);
}
多对多
⼀个⽼师对应多个学⽣,⼀个学⽣也对应多个⽼师
1. 创建数据库和相关数据表
2. 创建需要关联查询的实体类,⾥⾯包含相关的属性
public class Teacher {
private Integer id;
private String name;
private List<Student> students;
}
public class Student {
private Integer id;
private String name;
}
3. 编写TeacherMapper接⼝,为了调⽤⽅法
public interface TeacherMapper {
List<Teacher>findByName(String name);
}
4. 编写相关的Sql语句
1. 多对多查询需要借助⼀个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>
5. 编写TeacherMapperTest测试类
public class TeacherMapperTest {
private SqlSessionFactory sqlSessionFactory;
@Before
public void init()throws IOException {
InputStream inputStream = ResourceAsStream("l");
sqlSessionFactory =new SqlSessionFactoryBuilder().build(inputStream);
}
@Test
public void test(){
SqlSession sqlSession = sqlSessionFactory.openSession();
TeacherMapper mapper = Mapper(TeacherMapper.class);
List<Teacher> ret = mapper.findByName("唐丹");
System.out.println(ret);
}
}
Mybatis的嵌套查询
嵌套查询是将原来多表查询中的联合查询语句拆成单个表的查询,再使⽤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>
动态Sql查询
<insert id="save"parameterType="girl"keyProperty="id"useGeneratedKeys="true">        insert into girl values (null, #{name},#{age},#{address})
</insert>
<sql id="selectAll">
select * from girl
</sql>
<select id="findById"parameterType="int"resultType="girl">
<include refid="selectAll"/> where id = #{id}
</select>
<select id="findAll"resultType="girl">
<include refid="selectAll"/>
</select>

版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198,我们将在24小时内删除。