mybatis使用接口联合查询

一、先建立两个实体类和配置文件

配置文件

<?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="orc_db.properties"></properties>
  <typeAliases>
    <package name="com.model"/>
  </typeAliases>
    <environments default="development">
     <environment id="development">
       <transactionManager type="JDBC">
       
       </transactionManager>
       <dataSource type="POOLED">
                <property name="driver" value="${driver}"></property>
                <property name="url" value="${url}"></property>
                <property name="username" value="${username}"></property>
                <property name="password" value="${password}"></property>
       </dataSource>
     </environment>      
    </environments>
    <mappers>
      <package name="com.dao"/>
    </mappers>
  </configuration>

 

student 和 studentinfo

package com.model;

import java.util.Date;

public class Student {

    private Integer sno;
    private String sname;
    private String ssex;
    private Date sbirthday;
    private Integer sclass;

    public Student(Integer sno, String sname, String ssex, Date sbirthday, Integer sclass) {
        super();
        this.sno = sno;
        this.sname = sname;
        this.ssex = ssex;
        this.sbirthday = sbirthday;
        this.sclass = sclass;
    }

    public Student() {
        super();
    }

    @Override
    public String toString() {
        return "Student [sno=" + sno + ", sname=" + sname + ", ssex=" + ssex + ", sbirthday=" + sbirthday + ", sclass="
                + sclass + "]";
    }

    public Integer getSno() {
        return sno;
    }

    public void setSno(Integer sno) {
        this.sno = sno;
    }

    public String getSname() {
        return sname;
    }

    public void setSname(String sname) {
        this.sname = sname;
    }

    public String getSsex() {
        return ssex;
    }

    public void setSsex(String ssex) {
        this.ssex = ssex;
    }

    public Date getSbirthday() {
        return sbirthday;
    }

    public void setSbirthday(Date sbirthday) {
        this.sbirthday = sbirthday;
    }

    public Integer getSclass() {
        return sclass;
    }

    public void setSclass(Integer sclass) {
        this.sclass = sclass;
    }

}

package com.model;

import java.util.Date;

public class Student {

    private Integer sno;
    private String sname;
    private String ssex;
    private Date sbirthday;
    private Integer sclass;

    public Student(Integer sno, String sname, String ssex, Date sbirthday, Integer sclass) {
        super();
        this.sno = sno;
        this.sname = sname;
        this.ssex = ssex;
        this.sbirthday = sbirthday;
        this.sclass = sclass;
    }

    public Student() {
        super();
    }

    @Override
    public String toString() {
        return "Student [sno=" + sno + ", sname=" + sname + ", ssex=" + ssex + ", sbirthday=" + sbirthday + ", sclass="
                + sclass + "]";
    }

    public Integer getSno() {
        return sno;
    }

    public void setSno(Integer sno) {
        this.sno = sno;
    }

    public String getSname() {
        return sname;
    }

    public void setSname(String sname) {
        this.sname = sname;
    }

    public String getSsex() {
        return ssex;
    }

    public void setSsex(String ssex) {
        this.ssex = ssex;
    }

    public Date getSbirthday() {
        return sbirthday;
    }

    public void setSbirthday(Date sbirthday) {
        this.sbirthday = sbirthday;
    }

    public Integer getSclass() {
        return sclass;
    }

    public void setSclass(Integer sclass) {
        this.sclass = sclass;
    }

}

二、配置文件和接口

package com.dao;

import java.util.List;

import com.model.StudentInfo;

public interface StudentInfoMapper {
    /**
     * 查询全部学生的信息
     * @return
     */
 public List<StudentInfo> selectAll();
}

package com.dao;

import java.util.List;
import java.util.Map;
import com.model.Student;

public interface StudentMapper {
//    public Integer addStu(Student student);
//    public Integer delStu(Integer sno);
//    public Integer updStu(Student student);
    public List<Student> getStuByMap(Map<String,Object> map);
}

<?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.dao.StudentInfoMapper">
     <resultMap type="studentInfo" id="Info">
     <id property="id" column="id"/>
     <result property="student.sno" column="sno"/>
     <result property="student.sname" column="sname"/>
     <result property="student.ssex" column="ssex"/>
     <result property="student.sbirthday" column="sbirthday"/>
     <result property="student.sclass" column="sclass"/>
     
     <result property="saddress" column="saddress"/>
     </resultMap>
       第二种联合查询方法
     <resultMap type="studentInfo" id="Info1">
      <association property="student" column="sno" select="com.dao.StudentMapper.getStuBySno"></association>
     </resultMap>
    <select id="selectAll" resultMap="Info1">
    select * from studentinfo si left join student s on si.sno = s.sno
    </select>
    </mapper>

<?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.dao.StudentMapper">
    <select id="getStuBySno" resultType="student">
     select * from student s where s.sno=#{sno}
    </select>
  </mapper>

测试

package com.test;

import static org.junit.Assert.*;

import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import com.dao.StudentInfoMapper;
import com.model.StudentInfo;
import com.util.MybatisSqlFactory;

public class Test2 {

    private SqlSession ss;
    private StudentInfoMapper sm;
    @Before
    public void setUp() throws Exception {
        ss = MybatisSqlFactory.getSqlSession();
        sm =ss.getMapper(StudentInfoMapper.class);
    }

    @After
    public void tearDown() throws Exception {
        ss.commit();
        ss.close();
    }

    @Test
    public void test() {
        List<StudentInfo> list = sm.selectAll();
        for(StudentInfo data : list){
            System.out.println(data);
        }
    }

}