mybatis与翰云库结合示例

这次主要是应客户要求，需要有多表查询结果返回，做的一个测试例子

建表语句：

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，需要把结果集处理下,所以这个调试花了点时间，也求助了同事，现在做点总结：

1. 的type和 的javaType需要把对应的路径写完整，要不然mybatis加载的时候会找不着该类
2. 的column有别名，写别名，没别名，就写表名+列名