转载 联合查询 in ibatis

ibatis的联合查询

(2011-06-29 11:17:29)

转载

和一般查询区别不同的有三个地方

 

1、首先是实体里面，以前存的是string 现在肯定要修改为 对象

package bean;

import java.sql.Timestamp;
import java.util.Date;

import baseparam.DeptBean;//存对象

public class StudentBean
{
 private String id;
 
 private String name;
 
 private String email;
 
 private Date birthday;
 
 private DeptBean dept;
 
 public DeptBean getDept()
 {
  return dept;
 }

 public void setDept(DeptBean dept)
 {
  this.dept = dept;
 }

 public String getId()
 {
  return id;
 }

 public void setId(String id)
 {
  this.id = id;
 }

 public Date getBirthday()
 {
  return birthday;
 }

 public void setBirthday(Date birthday)
 {
  this.birthday = birthday;
 }

 public String getName()
 {
  return name;
 }

 public void setName(String name)
 {
  this.name = name;
 }

 public String getEmail()
 {
  return email;
 }

 public void setEmail(String email)
 {
  this.email = email;
 }

 public StudentBean(String name)
 {
  super();
  this.name = name;
 }

 public StudentBean()
 {
  super();
  }
 
}

 

2、在配置文件中指定部门实体路径（/ibatis/src/SqlMapConfig.xml）

 

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//ibatis.apache.org//DTD Config 3.0//EN"
"http://ibatis.apache.org/dtd/ibatis-3-config.dtd">
<configuration>
 <typeAliases>
  <typeAlias type="bean.StudentBean" alias="studentBean" />
  
  <!-- 指定部门实体路径-->
  <typeAlias type="baseparam.DeptBean" alias="deptBean"/>
 </typeAliases>
 
 <environments default="development">
  <environment id="development">
   <transactionManager type="JDBC" />
   <dataSource type="UNPOOLED">
    <property name="driver" value="oracle.jdbc.driver.OracleDriver" />
    <property name="url"
     value="jdbc:oracle:thin:@192.168.17.201:1521:DEPPONIT" />
    <property name="username" value="deppon009" />
    <property name="password" value="dpit20090511" />
   </dataSource>
  </environment>
 </environments>
 <mappers>
  <mapper resource="Student.xml" />
 </mappers>
</configuration>

 

3、最后一个就是修改mapper文件了（/ibatis/src/Student.xml）

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN"
"http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd">
<mapper namespace="student">

 <sql id="studentColumn">
  fid,
  fname,
  fbirthday
 </sql>
 <select id="getAll" resultType="studentBean" resultMap="studentResultMap">
  select stu.fid fid,
   stu.fname fname,
   dept.fid deptid,
   dept.fname deptname,
   stu.fbirthday
  from t_student stu
   inner join t_afei_dept dept on dept.fid = stu.fdept
 </select>
 
 <select id="getByID" resultType="studentBean" resultMap="studentResultMap">
  select stu.fid fid,
   stu.fname fname,
   dept.fid deptid,
   dept.fname deptname,
   stu.fbirthday
  from t_student stu
   inner join t_afei_dept dept on dept.fid = stu.fdept
  where stu.fid = #{id}
 </select>
 
 <insert id="add" parameterType="studentBean">
  insert into t_student(fid, fname, fbirthday) values
   ( newbosid('SFSAFSDF'),
    #{name, jdbcType=VARCHAR},
    #{birthday}
   )
 </insert>
 
 <delete id="deleteById" parameterType="int">
  delete from t_student where fid = #{id}
 </delete>
 
 <update id="updateStudent" parameterType="studentBean">
  update t_student t set t.fname = #{name}, t.fbirthday = #{birthday}
  where t.fid = #{id}
 </update>
 
 <!-- 数据库字段和Bean对象字段映射 -->
 <resultMap type="studentBean" id="studentResultMap">
  <id property="id" column="fid"/>
  <result property="name" column="fname"/>
  <result property="birthday" column="fbirthday"/>
  
  <!-- 联合查询 property中的名字应该和deptBean这个名字一致
   column中的名字应该和查询时的别名一样 -->
  <association property="dept" column="fdept" javaType="deptBean">
   <id property="id" column="deptid"/>
   <result property="name" column="deptname"/>
   <result property="num" column="deptnum"/>
  </association>
 </resultMap>
 </mapper>

其他地方和一般的配置一样

 

最后测试类

 
 public void getAll()
 {
  StudentDao dao = new StudentDaoImpl();
  
  List<StudentBean> students = dao.getAllStudents();
  
  for(StudentBean c : students)
  {
   DeptBean dept = c.getDept();
   System.out.println("student'id is\t" + c.getId());
   System.out.println("student'name is\t" + c.getName());
   if(dept != null)
   {
    
    System.out.println("dept'name is\t" + dept.getName());
   }
   System.out.println("------------------------------");
  }
 }