mybatis中的关联查询
定义两个类
@Setter
@Getter
public class Book {
private Integer id;
private String bname;
private BookType bookType;
private String author;
private String authorGender;
private Float price;
private String description;
@Override
public String toString() {
return "Book{" +
"id=" + id +
", bname='" + bname + '\'' +
", bookType={" + bookType +
"}, author='" + author + '\'' +
", authorGender='" + authorGender + '\'' +
", price=" + price +
", description='" + description + '\'' +
"}\n";
}
}
@Getter
@Setter
public class BookType {
private Integer id;
private String tname;
@Override
public String toString() {
return "BookType{" +
"id=" + id +
", tname='" + tname + '\'' +
'}';
}
}
定义一个接口
package com.lanou3g.mybatis.dao;
import com.lanou3g.mybatis.bean.Book;
import java.util.List;
public interface BookMapper {
List<Book> queryBooks();
}
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.lanou3g.mybatis.dao.BookMapper">
<resultMap id="book" type="com.lanou3g.mybatis.bean.Book">
<id column="bid" property="id" />
<result column="bname" property="bname" />
<result column="author" property="author" />
<result column="author_gender" property="authorGender" />
<result column="price" property="price" />
<result column="description" property="description" />
<association property="bookType" javaType="com.lanou3g.mybatis.bean.BookType">
<id column="bt_id" property="id" />
<result column="tname" property="tname" />
</association>
</resultMap>
<select id="queryBooks" resultMap="book">
select b.*,bt.*, b.id bid, bt.id bt_id from book b, booktype bt where b.btype = bt.id;
</select>
</mapper>
入口
@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);
}
}
一对多关联查询
定义两个类
@Getter
@Setter
public class Student {
private Integer id;
private String sname;
private String nickName;
public Student() {}
public Student(String sname, String nickName) {
this.sname = sname;
this.nickName = nickName;
}
@Override
public String toString() {
return "{" +
"id='" + id + '\'' +
"sname='" + sname + '\'' +
", nickName='" + nickName + '\'' +
"}\n";
}
}
@Getter
@Setter
public class Teacher {
private Integer id;
private String tname;
private Integer age;
private List<Student> students;
public Teacher (){}
public Teacher(String tname) {
this.tname = tname;
}
@Override
public String toString() {
return "{" +
"id:" + id +
", tname:'" + tname + '\'' +
", age:'" + age + '\'' +
", 所教学生:[" + students + '\'' +
"]}\n";
}
}
定义一个接口
public interface TeacherDao {
List<Teacher> queryTeacherCascade();
}
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.lanou3g.mybatis.dao.TeacherDao">
<resultMap id="teacher" type="Teacher">
<id column="t_id" property="id" />
<result column="tname" property="tname" />
<result column="age" property="age" />
<collection property="students" ofType="Student">
<id column="s_id" property="id" />
<result property="sname" column="sname" />
<result property="nickName" column="nick_name" />
</collection>
</resultMap>
<select id="queryTeacherCascade" resultMap="teacher">
SELECT t.*,s.*,t.id t_id,s.id s_id from teacher t, student s WHERE t.id = s.tid;
</select>
</mapper>
入口
@Slf4j
public class AppTest {
TeacherDao teacherDao = null;
@Before
public void setUp() {
teacherDao = MyBatisTools.getInstance().openSession().getMapper(TeacherDao.class);
}
/**
* 一对多关联查询
*/
@Test
public void testQueryTeacherCascade() {
List<Teacher> teacherList = teacherDao.queryTeacherCascade();
log.info("关联查询教师表:" + teacherList);
}
}
动态SQL
where , if ,when, choose的用法
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.lanou3g.mybatis.dao.StudentDao">
<resultMap id="stu" type="Student">
<result property="sname" column="sname" />
<result property="nickName" column="nick_name" />
</resultMap>
<select id="queryResultMap" resultMap="stu">
select * from student;
</select>
<!-- 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>
<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>
</mapper>
public interface StudentDao {
@Select("select * from student;")
List<Student> queryStudentByChooseWhen(Student condition);
List<Student> queryStudentByCondition(Student condition);
}
入口
@Slf4j
public class AppTest {
StudentDao studentDao = null;
@Before
public void setUp() {
studentDao = MyBatisTools.getInstance().openSession().getMapper(StudentDao.class);
}
/**
* 动态SQL
*/
@Test
public void testQueryDynamicSQL() {
Student condition = new Student();
List<Student> studentList = studentDao.queryStudentByCondition(condition);
log.info("where..if...学生列表:" + studentList);
studentList = studentDao.queryStudentByChooseWhen(condition);
log.info("choose..when...学生列表:" + studentList);
}
}
trim用法
<?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.lanou3g.mybatis.dao.StudentDao">
<resultMap id="stu" type="Student">
<result property="sname" column="sname" />
<result property="nickName" column="nick_name" />
</resultMap>
<select id="queryResultMap" resultMap="stu">
select * from student;
</select>
<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>
</mapper>
public interface StudentDao {
@Select("select * from student;")
int updateById(Student condition);
}
入口
@Slf4j
public class AppTest {
StudentDao studentDao = null;
@Before
public void setUp() {
studentDao = MyBatisTools.getInstance().openSession().getMapper(StudentDao.class);
}
@Test
public void testUpdateDynamicSQL() {
Student student = new Student();
student.setId(5);
student.setSname("楚云飞");
student.setNickName("楚帮忙");
int rows = studentDao.updateById(student);
log.info("更新行数: " + rows);
}
}
## **Mybatis Spring整合**
第一步 添加依赖
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>2.0.1</version>
</dependency>
第二步 在Spring中管理SqlSessionFactory
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<!-- 注入数据源 -->
<property name="dataSource" ref="dataSource"/>
<!-- 设置mybatis核心配置文件路径(可选) -->
<property name="configLocation" value="classpath:/mybatis-config.xml" />
<!-- 配置mybatis xml映射文件位置(如果Mapper是用注解配置的,这里就不用设置此属性了) -->
<property name="mapperLocations" value="classpath:/mappers/*" />
</bean>
如果是注解的方式配置的Mapper,我们需要在Spring配置文件中添加mybatis的schema以支持mybatis注解扫描
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:mybatis="http://mybatis.org/schema/mybatis-spring"
xsi:schemaLocation="
http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://mybatis.org/schema/mybatis-spring http://mybatis.org/schema/mybatis-spring.xsd">
<!-- 配置基于注解的Mapper所在包的路径 -->
<mybatis:scan base-package="org.mybatis.spring.sample.mapper" />
</beans>
或者这样配置
@Configuration
@MapperScan("org.mybatis.spring.sample.mapper")
public class AppConfig {
// ...
}
第三步 用Spring管理事务
和单独使用Spring时一样, 配置Spring的声明式事务就可以了,mybatis会自动参与到spring的事务中