myBatis 基礎測試 表關聯關係配置 集合 測試

myBatis 基礎測試 表關聯關係配置 集合 測試

 測試myelipse專案原始碼 sql 下載 http://download.csdn.net/detail/liangrui1988/5993881

在上一篇做了簡單 增刪改查 的測試，基本程式碼+api 下載 可以看上一遍博文 myBatis 基礎測試 增 刪 改 查 用過hibrenate 之後，感覺很好理解

動行效果:

sql :

CREATE TABLE `student` (
  `grade_id` int(11) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `password` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_sutids` (`grade_id`),
  CONSTRAINT `fk_sutids` FOREIGN KEY (`grade_id`) REFERENCES `grade` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

INSERT INTO `student` VALUES (1,1,'admin',30,'123'),(1,2,'hello',100,'world'),(1,3,'林沖',45,'aaa'),(1,4,'宋江',55,'123456'),(2,5,'吳用',46,'123456'),(2,6,'武松',30,'3333');

sql:

-- MySQL dump 10.13  Distrib 5.5.20, for Win32 (x86)
--
-- Host: localhost    Database: ruiabc
-- ------------------------------------------------------
-- Server version	5.5.20

CREATE TABLE `grade` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `grade_name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;


INSERT INTO `grade` VALUES (1,'157'),(2,'158');

-- Dump completed on 2013-08-22 16:10:48

在裡演示表關聯的查詢方試 注要程式碼

班級 xml：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.2//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="accp.dao">
<!-- 班集物件 型別    resultMap-->
<resultMap type="Grade" id="gradeRsultMap">
<id property="id" column="id"/>
<result property="grade_name" column="grade_name"/>
<!-- 學生集合 -->
<collection property="stu_list" column="id" javaType="ArrayList" 
            select="selectStudent" ofType="Student">
</collection>
</resultMap>


<!-- 查詢班級 並且和班級裡的學生  條件-->
<select id="selectGradeAndStudentById" resultMap="gradeRsultMap" parameterType="int">
select * from grade where id=#{id}
</select>

<!-- 查詢班級下的學生  給集合引用 -->
<select id="selectStudent" resultType="Student" parameterType="int">
select * from student where grade_id=#{id}
</select>

<!-- 方式二  -->
<select id="sqlSelectGradeAndStdeentById" resultMap="gradeMapbySql" parameterType="int">
select * from  student s inner join grade g on s.grade_id= g.id where g.id=#{id}
</select>
<!-- select g.id,g.grade_name from  student s inner join grade g on s.grade_id= g.id where g.id=#{id} -->
<resultMap type="Grade" id="gradeMapbySql">
<id property="id" column="id"/>
<result property="grade_name" column="grade_name"/>
<!-- 學生集合 型別的 引用 column="id" -->
<collection property="stu_list" ofType="Student"  resultMap="StudentRsultMap" />
</resultMap>

<!-- 學生集合 型別的 -->
<resultMap type="Student" id="StudentRsultMap">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="age" column="age"/>
<result property="password" column="password"/>

</resultMap>

<!-- select grade by id select="selectGradeById" -->
<select id="selectGradeById" parameterType="int" resultType="Grade">
select * from grade where id=#{id}
</select>
<!-- select All grade -->
<select id="selectAllGradeAndStudents" resultType="Grade">
select * from grade 
</select>

</mapper>

sqlSession工具程式碼在上一篇裡有，或都下載原始碼

測試程式碼:

package accp.test;

import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.ibatis.session.SqlSession;

import accp.bean.Grade;
import accp.bean.Student;
import accp.dao.Dao;
import accp.dao.imp.DaoImp;
import accp.util.GetSession;

public class TestRsultMap {
	public static void main(String[] args) {
		
		SqlSession sqlSession=GetSession.getInstans().getSqlSession();
		/*查詢學生資訊 包括所在班級*/
		/*Student stu=sqlSession.selectOne("accp.dao.selectStuAndGrade",2);
		System.out.println("學生id: "+stu.getId()+"  "+stu.getName()+"  "+stu.getPassword()+
					 "  "+stu.getAge()+" 所在班級名稱： "+stu.getGrade_id().getGrade_name()+
					 " 班級id: "+stu.getGrade_id().getId());*/
		
		//查詢班級資訊 包括班級裡的所有學生
		Grade grade=sqlSession.selectOne("accp.dao.selectGradeAndStudentById",1);
		System.out.println("班級ID："+grade.getId()+" name: "+grade.getGrade_name());
		System.out.println(" 班級裡的學生: "+grade.getStu_list().size()+" 個 -------------");
		 System.out.println("id \t 姓名  \t 密碼  \t 年齡");
		for(Student tempStu:grade.getStu_list()){
			System.out.println(tempStu.getId()+" \t "+tempStu.getName()+" \t "+tempStu.getPassword()+
					 " \t "+tempStu.getAge());
		}
		
		
		//查詢班級資訊 包括班級裡的所有學生 通過sql關聯查詢方式
		/*直接取對應取不出來？？？？沒有第一種方式好用
		 * Map<String,Grade> map=sqlSession.selectMap("accp.dao.sqlSelectGradeAndStdeentById","id");
		System.out.println(map.size());
		for(Entry entry:map.entrySet()){
		Grade gr=(Grade)entry.getValue();
		System.out.println("MapKey "+entry.getKey() +
				" mapValue Gr Name:"+ gr.getGrade_name()+
				"gr Stu 共幾個:"+gr.getStu_list().size());
		}*/
		
		//list-------- test
	/*	List<Grade> list=sqlSession.selectList("accp.dao.sqlSelectGradeAndStdeentById",1);
		for(Grade grade2:list){
		System.out.println("班級ID："+grade2.getId()+" name: "+grade2.getGrade_name());
		System.out.println(" 班級裡的學生: "+grade2.getStu_list().size()+" 個 -------------");
		System.out.println("id \t 姓名  \t 密碼  \t 年齡");
		for(Student tempStu:grade2.getStu_list()){
			System.out.println(tempStu.getId()+" \t "+tempStu.getName()+" \t "+tempStu.getPassword()+
					 " \t "+tempStu.getAge());
		}
		}*/
		
		
		
	}
}