Mybatis高级查询

MyBatis 专栏收录该内容
3 篇文章 0 订阅
一、一对一查询
1. 工程目录结构

在这里插入图片描述

2. 工程pom.xml文件
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.itcast</groupId>
    <artifactId>mybatis_one2one</artifactId>
    <version>1.0-SNAPSHOT</version>

    <packaging>jar</packaging>

    <dependencies>

        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.5</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.26</version>
        </dependency>

        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.12</version>
        </dependency>

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.10</version>
        </dependency>

    </dependencies>

</project>
3. 数据库设计
CREATE DATABASE mybatis_mapper;

USE mybatis_mapper;

DROP TABLE teacher;

CREATE TABLE teacher(
	teacherNo INT(8) NOT NULL AUTO_INCREMENT COMMENT '教师编号',
	teacherName VARCHAR(32) NOT NULL COMMENT '教师姓名',
	PRIMARY KEY (teacherNo)
)ENGINE = INNODB DEFAULT CHARSET = UTF8;

INSERT INTO teacher(teacherNo,teacherName) VALUES(10,'张三教师');
INSERT INTO teacher(teacherNo,teacherName) VALUES(11,'李四教师');

--------------------------------------------------------------------------

DROP TABLE student;

CREATE TABLE student(
	studentNo INT(8) NOT NULL AUTO_INCREMENT COMMENT '学生编号',
	studentName VARCHAR(32) NOT NULL COMMENT '学生姓名',
	teacherNo INT(8) NOT NULL COMMENT '教师编号',
	PRIMARY KEY (studentNo),
	FOREIGN KEY (teacherNo) REFERENCES teacher(teacherNo)
)ENGINE = INNODB DEFAULT CHARSET = UTF8;

INSERT INTO student(studentNo,studentName,teacherNo) VALUES(100,'王五学生',10);
INSERT INTO student(studentNo,studentName,teacherNo) VALUES(101,'赵六学生',11);
4. 实体类:Student
public class Student implements Serializable {
    private Integer studentNo;
    private String studentName;
    private Teacher teacher;
}
5. 实体类:Teacher
public class Teacher implements Serializable {
    private Integer teacherNo;
    private String teacherName;
}
6. DAO接口:StudentDao
public interface StudentDao {
    /**
     * 根据条件查询
     * @param student
     * @return
     */
    List<Student> findByCondition(Student student);
}
7. 接口对应的映射文件:StudentDao.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="dao.StudentDao">
    <resultMap id="studentMap" type="domain.Student">
        <id property="studentNo" column="studentNo"></id>
        <result property="studentName" column="studentName"></result>
        <association property="teacher" column="teacher" javaType="domain.Teacher">
            <id property="teacherNo" column="teacherNo"></id>
            <result property="teacherName" column="teacherName"></result>
        </association>
    </resultMap>

    <select id="findByCondition" parameterType="domain.Student" resultMap="studentMap">
        SELECT *
        FROM student s, teacher t
        WHERE s.teacherNo = t.teacherNo
            <if test="studentNo != null">
                AND studentNo = #{studentNo}
            </if>
            <if test="studentName != null">
                AND studentName = #{studentName}
            </if>
    </select>
</mapper>
8. 数据源配置文件(db.properties)
jdbc.driver = com.mysql.jdbc.Driver
jdbc.url = jdbc:mysql://localhost:3306/mybatis_mapper
jdbc.username = root
jdbc.password = root
9. 全局配置文件(mybatis-config.xml)
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>

    <properties resource="db.properties"></properties>

    <environments default="development">
        <environment id="development">
            <transactionManager type="jdbc"></transactionManager>
            <dataSource type="pooled">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="dao/StudentDao.xml"></mapper>
    </mappers>

</configuration>
10. Junit测试类
public class TestMybatis {
    private InputStream inputStream;
    private SqlSessionFactory sqlSessionFactory;
    private SqlSession sqlSession;

    @Before
    public void init() {
        try {
            inputStream = Resources.getResourceAsStream("mybatis-config.xml");
        } catch (IOException e) {
            e.printStackTrace();
        }
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        sqlSession = sqlSessionFactory.openSession();
    }

    @After
    public void destroy() {
        sqlSession.commit();
        if (sqlSession != null) {
            sqlSession.close();
        }
        if (inputStream != null) {
            try {
                inputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    @Test
    public void test_findCondition() {
        Student student = new Student();
        student.setStudentNo(101);
        StudentDao studentDao = sqlSession.getMapper(StudentDao.class);
        List<Student> students = studentDao.findByCondition(student);
        for (Student stu : students) {
            System.out.println(stu);
        }
    }
}
  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

相关推荐
©️2020 CSDN 皮肤主题: 技术黑板 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值