文章目录
动态SQL中的结果集映射
一对一
一对一按照我自己的理解就是一个类型的对象只对应另外一个类型的对象，例如：书对应的只有一种类型（玄幻，修真等等）
- 实体类
package com.lanou.spring.bean;
import lombok.Getter;
import lombok.Setter;
@Getter
@Setter
public class Book {
private Integer id;
private String bname;
private String btype;
private BookType bookType;//一对一的特征，就是在这个类中引入另一个类的类型 eg:BookType
private String author;
private Integer author_gender;
private Integer price;
private String description;
@Override
public String toString() {
return "Book{" +
"id=" + id +
", bname='" + bname + '\'' +
", btype='" + bookType + '\'' +
", author='" + author + '\'' +
", author_gender=" + author_gender +
", price=" + price +
", description='" + description + '\'' +
"}\n";
}
}
- 实体类2
package com.lanou.spring.bean;
import lombok.Getter;
import lombok.Setter;
@Setter
@Getter
public class BookType {
private Integer id;
private String tname;
@Override
public String toString() {
return "BookType{" +
"id=" + id +
", tname='" + tname + '\'' +
"}\n";
}
}
- 接口中声明方法
package com.lanou.spring.dao;
import com.lanou.spring.bean.Book;
import java.util.List;
public interface BookMapper {
List<Book> queryBooks();
}
- BookMapper.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">
<!--namespace对应的是Dao接口的全名-->
<mapper namespace="com.lanou.spring.dao.BookMapper">
<resultMap id="book" type="com.lanou.spring.bean.Book">
<id column="bid" property="id"/>
<result column="bname" property="bname"/>
<result column="author" property="author"/>
<result column="author_gender" property="author_gender"/>
<result column="price" property="price"/>
<result column="description" property="description"/>
<association property="bookType" javaType="com.lanou.spring.bean.BookType">
<!--<association property="bookType" javaType="com.lanou.spring.bean.BookType">
a开头的是一对一的特色
/*bid bt_id起别名是为了映射resultmap里面的id column="bid" property="id"*
下面的·resultMap="book"对应的是上面的resultMap 的id /-->
<id column="id" property="id"/>
<!--<id column="id" property="id"/>id 也是一对一的特征，一对多应该是result -->
<result column="tname" property="tname"/>
</association>
</resultMap>
<select id="queryBooks" resultMap="book">
select b.*,bt.*, b.id bid, bt.id bt_id from book_table b, book_type bt ;
</select>
</mapper>
- 在映射前还需要在另外一个xml(mybatis_conf.xml)中配置一下(BookMapper.xml)
<mappers>
<mapper class="com.lanou.spring.dao.StudentDao" />
<mapper resource="mapper/TeacherMapper.xml" />
<mapper resource="mapper/StudentMapper.xml" />
<mapper class="com.lanou.spring.dao.CategoryDao" />
<mapper resource="mapper/BookMapper.xml" />
</mappers>
- 测试类入口
@Slf4j
public class AppTest {
BookMapper bookMapper=null;
@Before
public void setUp() {
bookMapper = MyBatisTools.getInstance().openSession().getMapper(BookMapper.class);
}
@Test
public void testQueryCascade() {
List<Book> bookList = bookMapper.queryBooks();
log.info("关联查询图书表：" + bookList);
}
}
一对多
一对多就是一个对象能狗对应另外一个类型的多个对象，例如：一个厂长能对应多个工厂
多对多就像 学生和学科 一个学生可以选修多门学科，一门也可以被多名学生选。
- 实体类1（省）
package com.lanou.spring.bean;
import lombok.Getter;
import lombok.Setter;
import java.util.List;
@Getter
@Setter
public class province {
private Integer id;
private String p_pname;
private List<city> citys;//一个类中引入另一个的类的List，是一对多的特征
@Override
public String toString() {
return "province{" +
"id=" + id +
", p_pname='" + p_pname + '\'' +
", citys=" + citys +
'}';
}
}
- 实体类2（市）
package com.lanou.spring.bean;
import lombok.Getter;
import lombok.Setter;
@Getter
@Setter
public class city {
private Integer id;
private String cname;
private Integer pid;
@Override
public String toString() {
return "city{" +
"c_id=" + id +
", c_cname='" + cname + '\'' +
", c_pid=" + pid +
"}\n";
}
}
- 接口声明方法
package com.lanou.spring.dao;
import com.lanou.spring.bean.city;
import com.lanou.spring.bean.province;
import java.util.List;
public interface ProvinceMapper {
List<province> queryCitys();
}
- ProvinceMapper.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.lanou.spring.dao.ProvinceMapper">
<resultMap id="province" type="com.lanou.spring.bean.province">
<!--<id column="p_id" id column="p_id"/> 中的id 有去重的效果（对应一对多的一）id column="p_id"对应下面sql语句的别名
id column="p_id"对应的Java实体类中的属性名-->
<id column="p_id" property="id"/>
<!--<result column="pname"对应的数据库列名 property="p_pname"Java类中的属性名/>-->
<result column="pname" property="p_pname"/>
<collection property="citys" ofType="com.lanou.spring.bean.city">
<!--<collection property="citys" ofType="com.lanou.spring.bean.city">也是一对多的特征-->
<!-- <result column="c_id" property="id"/> result 对应一对多的多，映射结果集List
-->
<!--<result column="c_id"对应下面的sql语句的别名 property="id"对应的是实体中的属性名/>-->
<result column="c_id" property="id"/>
<!--<result column="cname" 对应是数据库的列名property="cname"对应的是实体类中的属性名，下同/>-->
<result column="cname" property="cname"/>
<result column="pid" property="pid"/>
</collection>
<!--<association property="bookType" javaType="com.lanou.spring.bean.BookType">
<id column="id" property="id"/>
<result column="tname" property="tname"/>
</association>-->
</resultMap>
<!-- 起别名是为了映射上面的 <id column="p_id" property="id"/>和 <result column="c_id" property="id"/>-->
<select id="queryCitys" resultMap="province">
select p.*,c.*, p.id p_id, c.id c_id from province p, city c where p.id=c.pid;
</select>
</mapper>
- 在映射前还需要在另外一个xml(mybatis_conf.xml)中配置一下(ProvinceMapper.xml)
<mappers>
<mapper class="com.lanou.spring.dao.StudentDao" />
<mapper resource="mapper/TeacherMapper.xml" />
<mapper resource="mapper/StudentMapper.xml" />
<mapper class="com.lanou.spring.dao.CategoryDao" />
<mapper resource="mapper/BookMapper.xml" />
<mapper resource="mapper/ProvinceMapper.xml" />
</mappers>
- 测试类入口
@Slf4j
public class AppTest {
ProvinceMapper provinceMapper=null;
@Before
public void setUp() {
provinceMapper = MyBatisTools.getInstance().openSession().getMapper(ProvinceMapper.class);
}
@Test
public void testQueryProcity() {
List<province> bookList = provinceMapper.queryCitys();
log.info("一对多查询省市：" + bookList);
}
}
where标签
当我们拼接动态SQL时，如果一个查询条件都没有，那我们就不需要where子句，而如果有至少一个条件我们就需要写上where 1=1，接下来我们还要写if子句一个个判断，符合就拼接上，现在这些操作在xml中也可以完成
<select id="queryStudentByCondition" resultType="Student">
select * from student
<where>
<if test="sname != null">
sname = #{sname}
</if>
<if test="nickName != null">
and nick_name = #{nickName}
</if>
<if test="id != null">
and id = #{id}
</if>
</where>
where…if…标签（作用和Java中的if一样，只要满足if条件的都可以拼接）
<!-- where、if用法 -->
<select id="queryStudentByCondition" resultType="Student">
select * from student
<where>
<if test="sname != null">
sname = #{sname}
</if>
<if test="nickName != null">
and nick_name = #{nickName}
</if>
<if test="id != null">
and id = #{id}
</if>
</where>
</select>
where…choose…标签（作用和Java类中的switch…case…一样，满足一个后就break了，就不会判断下一个了，达不到拼接的效果）
<select id="queryStudentByChooseWhen" resultType="Student">
select * from student
<where>
<choose>
<when test="sname != null">
and sname = #{sname}
</when>
<when test="nickName != null">
and nick_name = #{nickName}
</when>
<otherwise>
and id = 5
</otherwise>
</choose>
</where>
</select>
set标签（在sql语句里面添加"set"）
<update id="updateById" parameterType="Student">
update student
<set>
<if test="sname != null">
sname = #{sname},
</if>
<if test="nickName != null">
nick_name = #{nickName},
</if>
</set>
where id = #{id}
</update>
trim标签
- prefix: 添加指定前缀
- prefixOverrides: 删除指定前缀
- suffixOverrides: 删除指定后缀
<update id="updateById" parameterType="Student">
update student
<!--
<set>
-->
<trim prefix="set" suffixOverrides=",">
<if test="sname != null">
sname = #{sname},
</if>
<if test="nickName != null">
nick_name = #{nickName},
</if>
</trim>
<!--
</set>
-->
where id = #{id}
</update>
扫一扫
808
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
