where标簽

會自動處理第一個标簽中的 and，但不會處理之後中的and

personMapper.xml

<select id="selectPersonByIdorName" parameterType="person"  resultType="person"> 
	 	select * from person 
	 	<where> <!-- where 标簽會自動去除第一個and -->
	 		<if test="id != null  and id != 0"> and id = #{id}</if>
	 		<if test="name != null  and name != '' "> and name like concat('%','${name}','%')</if>
	 	</where>
	 </select>

測試類

public static void selectPersonByIdorName() throws IOException {
		InputStream in = Resources.getResourceAsStream("config.xml");
		SqlSessionFactory sqlFactory =  new SqlSessionFactoryBuilder().build(in);
		SqlSession session = sqlFactory.openSession();
		PersonMapper personMapper = session.getMapper(PersonMapper.class);
		Person per = new Person();
		per.setId(1);
		per.setName("j");
		List<Person> allPerson = personMapper.selectPersonByIdorName(per);
		for(Person person: allPerson)
			System.out.println(person);
	}

foreach标簽

疊代的類型：數組、對象數組、集合（List）

普通數組

<select id="selectPersonWithIdsArray" parameterType = "int[]" resultType="person">
	 	select * from person 
	 	<where>
	 		<if test="array != null  and array.length > 0">
	 			<foreach collection="array" open=" id in (" close=")" item="ids" separator=",">
	 				#{ids}
	 		</foreach>
	 		</if>
	 	</where>
	 </select>

測試類

public static void selectPersonWithIdsArray() throws IOException {
		InputStream in = Resources.getResourceAsStream("config.xml");
		SqlSessionFactory sqlFactory =  new SqlSessionFactoryBuilder().build(in);
		SqlSession session = sqlFactory.openSession();
		PersonMapper personMapper = session.getMapper(PersonMapper.class);
		//查詢id為1、2、52的人資訊
		int[] ids= {1,2,52};
		List<Person> allPerson = personMapper.selectPersonWithIdsArray(ids);
		for(Person person: allPerson)
			System.out.println(person);
	}

無論編寫代碼時，傳遞的是什麼參數名(ids)，在mapper.xml中都必須用array代替該數組 ## 對象數組

<select id="selectPersonWithObjectArray" parameterType = "Object[]" resultType="person">
	 	select * from person 
	 	<where>
	 		<if test="array != null  and array.length > 0">
	 			<foreach collection="array" open=" id in (" close=")" item="person" separator=",">
	 				#{person.id}
	 		</foreach>
	 		</if>
	 	</where>
	 </select>

測試類

public static void selectPersonWithObjectArray() throws IOException {
		InputStream in = Resources.getResourceAsStream("config.xml");
		SqlSessionFactory sqlFactory =  new SqlSessionFactoryBuilder().build(in);
		SqlSession session = sqlFactory.openSession();
		PersonMapper personMapper = session.getMapper(PersonMapper.class);
		Person p1 = new Person();
		Person p2 = new Person();
		Person p3 = new Person();
		p1.setId(2);
		p2.setId(4);
		p3.setId(52);
		Person[] persons= {p1,p2,p3};
		List<Person> allPerson = personMapper.selectPersonWithObjectArray(persons);
		for(Person person: allPerson)
			System.out.println(person);
	}

foreach 标簽的collection屬性值為array， parameterType屬性值為Object[]

無論編寫代碼時，傳遞的是什麼參數名(ids)，在mapper.xml中都必須用array代替該數組 ## 集合集合：無論編寫代碼時，傳遞的是什麼參數名(stuNos)，在mapper.xml中必須用list代替該數組

一對一

一對一關聯查詢可以使用業務類，該業務類必須包含查詢結果的所有屬性。另一種解決方案是使用resultMap，

使用業務類

<!-- 多表内連接配接查詢 -->
	 		select p.* ,c.* from  person p 
	 		inner join idcard c 
	 		on p.card_id = c.card_id 
	 		where p.id = #{id}

需要建立一個業務類，該類具有Person 和IDcard的屬性。

resultMap

IDcard 類

package com.johnny.entity;

public class IDcard {
	private int cardId;
	private String cardCode;
	public int getCardId() {
		return cardId;
	}
	public void setCardId(int cardId) {
		this.cardId = cardId;
	}
	public String getCardCode() {
		return cardCode;
	}
	public void setCardCode(String cardCode) {
		this.cardCode = cardCode;
	}
	

}

person.java，在從表對應類一端增加屬性private IDcard idCard;

package com.johnny.entity;

public class Person {
	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 int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
	@Override
	public String toString() {
		return this.id+"- "+ this.name+"- " +this.age+" - 性别："+this.sex+" 卡："+this.idCard.getCardId()+" "+this.idCard.getCardCode();
	}
	
	public Person() {}
	public Person(String name, int age) {
		this.name = name;
		this.age = age;
	}
	public Person(int id,String name, int age) {
		this.id = id;
		this.name = name;
		this.age = age;
	}

	public boolean isSex() {
		return sex;
	}
	public void setSex(boolean sex) {
		this.sex = sex;
	}

	public IDcard getIdCard() {
		return idCard;
	}
	public void setIdCard(IDcard idCard) {
		this.idCard = idCard;
	}
	
	private int id;
	private String name;
	private int age;
	private boolean sex;
	
}

<!-- 一對一映射 -->
	 
	 <select id="queryPersonByIdWithIDcard" parameterType="int" resultMap="personWithIDcard">
	 		<!-- 多表内連接配接查詢 -->
	 		select p.* ,c.* from  person p 
	 		inner join idcard c 
	 		on p.card_id = c.card_id 
	 		where p.id = #{id}
	 </select>
	 
	 <resultMap id="personWithIDcard" type="person">
	 	<id property="id" column="id"/>
	 	<result property="name" column="name"/>
	 	<result property="age" column="age"/>
	 	<result property="sex" column="perSex"/>
	 	<association property="idCard">
	 		<id property="cardId" column="card_id" />
	 		<result property="cardCode"  column="card_code" />
	 	</association>
	 </resultMap>

public static void queryPersonByIdWithIDcard() throws IOException {
		InputStream in = Resources.getResourceAsStream("config.xml");
		SqlSessionFactory sqlFactory =  new SqlSessionFactoryBuilder().build(in);
		SqlSession session = sqlFactory.openSession();
		PersonMapper personMapper = session.getMapper(PersonMapper.class);
		Person person = personMapper.queryPersonByIdWithIDcard(2);
		System.out.println(person);
	}

一對多

民族與個人為一對多關系：一個民族下有多個人。而一個人隻屬于一個民族。

personMapper.xml

<!-- 一對多映射 根據nation_id查詢該民族下的所有人-->
	<select id="queryNationByIdWithPerson" parameterType="int" resultMap="personWithNation">
		select p.*, n.* from person p 
		inner join nation n
		on p.nation_id = n.nation_id
		where p.nation_id = #{nation_id}
	</select>
	<resultMap id="personWithNation" type="nation">
		<id property="nationId"  column="nation_id" />
		<result property="nationName" column="nation_name" />
		<!-- 屬性類型則使用jdbcType   屬性的元素類型則使用ofType -->
		<collection property="persons" ofType="person">
			<id property="id" column="id"/>
			<result property="name" column="name"/>
			<result property="age" column="age"/>
			<result property="sex" column="perSex"/>
		</collection>
	</resultMap>

測試類

//一對多關聯查詢
	public static void queryNationByIdWithPerson() throws IOException {
		InputStream in = Resources.getResourceAsStream("config.xml");
		SqlSessionFactory sqlFactory =  new SqlSessionFactoryBuilder().build(in);
		SqlSession session = sqlFactory.openSession();
		PersonMapper personMapper = session.getMapper(PersonMapper.class);
		Nation nation = personMapper.queryNationByIdWithPerson(1);
		System.out.println(nation);
		List<Person> persons = nation.getPersons();
		System.out.println(persons);
	}

nation.java

package com.johnny.entity;

import java.util.List;

public class Nation {
	public List<Person> getPersons() {
		return persons;
	}
	public void setPersons(List<Person> persons) {
		this.persons = persons;
	}
	private int nationId;
	private String nationName;
	//在多端增加屬性
	private List<Person>  persons;
	public int getNationId() {
		return nationId;
	}
	public void setNationId(int nationId) {
		this.nationId = nationId;
	}
	public String getNationName() {
		return nationName;
	}
	public void setNationName(String nationName) {
		this.nationName = nationName;
	}
	@Override
	public String toString() {
		return this.nationId+" - "+this.nationName;
	}
}

person.java

package com.johnny.entity;

public class Person {
	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 int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
	@Override
	public String toString() {
		return this.id+"- "+ this.name+"- " +this.age+" - 性别："+this.sex;//+" 卡："+this.idCard.getCardId()+" "+this.idCard.getCardCode();
	}
	
	public Person() {}
	public Person(String name, int age) {
		this.name = name;
		this.age = age;
	}
	public Person(int id,String name, int age) {
		this.id = id;
		this.name = name;
		this.age = age;
	}

	public boolean isSex() {
		return sex;
	}
	public void setSex(boolean sex) {
		this.sex = sex;
	}

	public IDcard getIdCard() {
		return idCard;
	}
	public void setIdCard(IDcard idCard) {
		this.idCard = idCard;
	}
	
	private int id;
	private String name;
	private int age;
	private boolean sex;
	private IDcard idCard;
}

Mybatis 學習筆記（四） —— 動态SQL、關聯查詢where标簽foreach标簽一對一一對多

where标簽

foreach标簽

普通數組

一對一

使用業務類

resultMap

一對多

繼續閱讀

SSM 整合 Spring+Spring MVC+MyBatisSSM 整合

MyEclipse安裝MyBatipse 從dao快速定位mapper的sql語句

Mybatis問答集

【附源碼】計算機畢業設計SSM網絡求職招聘系統

Mybatis源碼閱讀（一）：Mybatis初始化1.1 解析properties、settings前言入口引申結語

mybatis源碼解析（二）初始化SqlSessionFactory對象

Mybatis源碼閱讀（四）：核心接口4.2——Executor（上）

MyBatis源碼解析(一)——MyBatis初始化過程解MyBatis源碼解析(一)——MyBatis初始化過程解1. 準備工作2. MyBatis初始化過程

從你的WEB世界路過之--0.初識0) 前言1) WEB基本概念2) 響應資料3. 結語

Java 實作Gbase資料庫增删改查功能

mybatis——使用代理對象進行資料庫操作

mybatis中#{}和${}和差別，以及防止sql注入。

myeclipse 搭建mybatis架構時報錯（1）Communications link failure 通訊連結故障

MyBatis學習筆記Mybatis學習筆記（maven、Idea版）

用mybatis的generator插件在項目中自動生成dao及entity

從mybatis到mybatis plus(mp)