这次主要是应客户要求,需要有多表查询结果返回,做的一个测试例子
建表语句:
CREATE TABLE student (
id int ,
name varchar(10) NOT NULL,
gender char(1) NOT NULL,
major varchar(20) NOT NULL,
grade char(4) NOT NULL,
supervisor_id int NOT NULL
) ;
CREATE TABLE teacher (
id int ,
name varchar(10) NOT NULL,
gender char(1) NOT NULL,
research_area varchar(20) NOT NULL
);
INSERT INTO student VALUES (0, '李林', 'a', '计算机科学与技术', '2011', '1');
INSERT INTO teacher VALUES (0,'张伟', 'a', '软件工程');
工程结构部署如下图:
Student.java
package mybatis;
public class Student {
private int id;
private String name; // 姓名
private String gender; // 性别
private String major; // 专业
private String grade; // 年级
private Teacher supervisor; //指导教师
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public String getMajor() {
return major;
}
public void setMajor(String major) {
this.major = major;
}
public String getGrade() {
return grade;
}
public void setGrade(String grade) {
this.grade = grade;
}
public Teacher getSupervisor() {
return supervisor;
}
public void setSupervisor(Teacher supervisor) {
this.supervisor = supervisor;
}
}
Teacher.java
package mybatis;
import java.util.List;
public class Teacher {
private int id;
private String name; // 教师姓名
private String gender; // 教师性别
private String researchArea; // 研究领域
private List<Student> supStudents; // 指导学生
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public String getResearchArea() {
return researchArea;
}
public void setResearchArea(String researchArea) {
this.researchArea = researchArea;
}
public List<Student> getSupStudents() {
return supStudents;
}
public void setSupStudents(List<Student> supStudents) {
this.supStudents = supStudents;
}
}
测试启动类:
package mybatis;
import java.io.IOException;
import java.io.Reader;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.omg.CORBA.portable.InputStream;
public class Test1 {
public static void main(String[] args) {
try {
//mybatis的配置文件
String resource = "conf.xml";
Reader reader = Resources.getResourceAsReader(resource);
//构建sqlSession的工厂
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
//创建能执行映射文件中sql的sqlSession
SqlSession session = sessionFactory.openSession();
String statement = "mybatis.StudentMapper.getById";//映射sql的标识字符串
Student s = session.selectOne(statement, 0);
System.out.println(s.getName()+s.getMajor()+" 1"+s.getSupervisor().getResearchArea());
} catch (Exception e) {
e.printStackTrace();
// TODO: handle exception
}
}
}
配置文件:
<?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="mybatis.StudentMapper">
<!-- 定义java Bean的属性与数据库的列之间的映射 -->
<resultMap type="mybatis.Student" id="studentResultMap">
<id column="id" property="id" />
<result column="st.name" property="name" />
<result column="st.gender" property="gender" />
<result column="st.major" property="major" />
<result column="st.grade" property="grade"/>
<association property="supervisor" javaType="mybatis.Teacher">
<id property="id" column="t_id"/>
<result property="name" column="t_name"/>
<result property="gender" column="t_gender"/>
<result property="researchArea" column="t_research_area"/>
</association>
</resultMap>
<!-- SQL语句中以"#{}"的形式引用参数 我是写死的 -->
<select id="getById" parameterType="int" resultMap="studentResultMap">
SELECT st.id,st.name,st.gender,st.major,st.grade,t.id t_id,t.name t_name, t.gender t_gender,t.research_area t_research_area
FROM student st inner join teacher t on st.id=t_id WHERE st.id=0
</select>
</mapper>
mybatis的链接配置文件conf.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>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC" />
<!-- 配置数据库连接信息 -->
<dataSource type="POOLED">
<property name="driver" value="com.cloudwave.jdbc.CloudDriver" />
<property name="url" value="jdbc:cloudwave:@localhost:1978" />
<property name="username" value="itest" />
<property name="password" value="itest" />
</dataSource>
<!-- <dataSource type="POOLED">
<property name="driver" value="oracle.jdbc.driver.OracleDriver" />
<property name="url" value="jdbc:oracle:thin:@192.168.2.250:1521:orcl" />
<property name="username" value="plsql" />
<property name="password" value="plsql" />
</dataSource>-->
</environment>
</environments>
<mappers>
<!-- <mapper resource="mybatis/userMapper.xml"/>-->
<mapper resource="mybatis/StudentMapper.xml"/>
</mappers>
</configuration>
因为不是直接返回一个map,需要把结果集处理下,所以这个调试花了点时间,也求助了同事,现在做点总结:
- 的type和 的javaType需要把对应的路径写完整,要不然mybatis加载的时候会找不着该类
- 的column有别名,写别名,没别名,就写表名+列名