Mybatis之动态SQL

使用动态Sql根据学生班级和年龄查询学生信息

所需要用到的jar包   mybatis-3.5.1.jar 和 mysql-connector-java-5.1.0-bin.jar

1.配置mybatis-config.xml

创建mybatis-config.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>
        <!-- 资源配置 -->
	<properties resource="database.properties"></properties>

	<!-- 别名配置 -->
	<typeAliases>
		<!--批量设置 默认将包中的所有实体类设置别名，类名小写即别名 -->
		<package name="com.zy.pojo" />
	</typeAliases>

	<environments default="development">
		<environment id="development">
			<transactionManager type="JDBC" />
			<dataSource type="POOLED">
				<property name="driver" value="${driver}" />
				<property name="url" value="${url}" />
				<property name="username" value="${username}" />
				<property name="password" value="${password}" />
			</dataSource>
		</environment>
	</environments>

        <!-- mapper配置 -->
	<mappers>
		<mapper resource="com/zy/mapper/StudentMapper.xml" />
	</mappers>
</configuration>

2.创建类--表映射

创建Student.java类

package com.zy.pojo;

public class Student {
	private Integer id;
	private String stuName;
	private String stuClass;
	private int stuAge;
	private boolean stuSex;
	private StudentAddress stuAddress;
	
	public Student() {
		super();
	}

	public Integer getId() {
		return id;
	}

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

	public String getStuName() {
		return stuName;
	}

	public void setStuName(String stuName) {
		this.stuName = stuName;
	}

	public String getStuClass() {
		return stuClass;
	}

	public void setStuClass(String stuClass) {
		this.stuClass = stuClass;
	}

	public int getStuAge() {
		return stuAge;
	}

	public void setStuAge(int stuAge) {
		this.stuAge = stuAge;
	}

	public boolean isStuSex() {
		return stuSex;
	}

	public void setStuSex(boolean stuSex) {
		this.stuSex = stuSex;
	}

	public StudentAddress getStuAddress() {
		return stuAddress;
	}

	public void setStuAddress(StudentAddress stuAddress) {
		this.stuAddress = stuAddress;
	}

	public Student(Integer id, String stuName, String stuClass, int stuAge, boolean stuSex) {
		super();
		this.id = id;
		this.stuName = stuName;
		this.stuClass = stuClass;
		this.stuAge = stuAge;
		this.stuSex = stuSex;
	}

	public Student(Integer id, String stuName, String stuClass, int stuAge, boolean stuSex, StudentAddress stuAddress) {
		super();
		this.id = id;
		this.stuName = stuName;
		this.stuClass = stuClass;
		this.stuAge = stuAge;
		this.stuSex = stuSex;
		this.stuAddress = stuAddress;
	}

		@Override
	public String toString() {
		return "Student [id=" + id + ", stuName=" + stuName + ", stuClass=" + stuClass + ", stuAge=" + stuAge
				+ ", stuSex=" + stuSex + ", stuAddress=" + stuAddress + "]";
	}

	
	
	
}

创建StudentAddress.java类

package com.zy.pojo;

public class StudentAddress {
	private String homeAddress;
	private String schoolAddress;
	public String getHomeAddress() {
		return homeAddress;
	}
	public void setHomeAddress(String homeAddress) {
		this.homeAddress = homeAddress;
	}
	public String getSchoolAddress() {
		return schoolAddress;
	}
	public void setSchoolAddress(String schoolAddress) {
		this.schoolAddress = schoolAddress;
	}
	
	public StudentAddress(String homeAddress, String schoolAddress) {
		super();
		this.homeAddress = homeAddress;
		this.schoolAddress = schoolAddress;
	}
	public StudentAddress() {
		super();
	}
	
	@Override
	public String toString() {
		return "StudentAddress [homeAddress=" + homeAddress + ", schoolAddress=" + schoolAddress + "]";
	}
	
}

3.创建mapper接口

创建StudentMapper.java接口

package com.zy.mapper;

import java.util.List;

import com.zy.pojo.Student;

public interface StudentMapper {
	List<Student> queryStuByClassOrAge(Student studnet);
	List<Student> queryStuByStuNo(int[] stu);
}

4.创建mapper映射文件

创建StudentMapper.xml映射

<?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="com.zy.mapper.StudentMapper">
        

	<!-- 动态Sql 根据学生班级或年龄查询学生 -->
	<select id="queryStuByClassOrAge" parameterType="student"
		resultMap="studentMap">
		select * from student
		<!-- where标签能自动处理第一个and和or -->
		<where>
			<!-- test:判断条件 -->
			<if test=" stuClass!=null and stuClass!='' "></if>
			or stuClass = #{stuClass}
			<if test=" stuAge!=null and stuAge!=0 "></if>
			or stuAge = #{stuAge}
		</where>
	</select>

        <resultMap type="student" id="studentMap">
		<id property="stuNo" column="stuNo" />
		<result property="stuName" column="stuName" />
		<result property="stuClass" column="stuClass" />
		<result property="stuAge" column="stuAge" />
		<result property="stuSex" column="stuSex" />
		<!-- 一对多 -->
		<collection property="stuAddress" ofType="com.zy.pojo.StudentAddress">
			<result property="homeAddress" column="homeAddress" />
			<result property="schoolAddress" column="schoolAddress" />
		</collection>
	</resultMap>
</mapper>

 

5.测试类

创建测试类StudentTest.java

package com.zy.test;

import java.io.IOException;
import java.util.List;

import org.apache.ibatis.session.SqlSession;

import com.zy.mapper.StudentMapper;
import com.zy.pojo.Student;
import com.zy.util.Util;

public class StudentTest {

	public static void main(String[] args){
        	queryStuByClassOrAge();
	}
	

	
//	根据学生班级和年龄查询学生
	private static void queryStuByClassOrAge() {
		SqlSession sqlSession = Util.getSession();
		Student student1 = new Student();
		student1.setStuClass("3");
		student1.setStuAge(25);

		List<Student> studentlist = sqlSession.getMapper(StudentMapper.class).queryStuByClassOrAge(student1);
		for (Student student : studentlist) {
			System.out.println(student);
		}
		Util.closeSqlSession(sqlSession);
	}
	

}

6.运行结果

7.项目结构

 

分析一下：

• 使用Mybatis的标签来实现动态Sql查询语句的拼接

动态Sql的foreach标签的使用

查询学号为1,2,7学号的学生信息（单参数array数组的类型）

1.mapper映射

<?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="com.zy.mapper.StudentMapper">
	
	<!-- 动态Sql 使用foreach查询学号为1,2,7学号的学生信息
		    item表示集合中每一个元素进行迭代时的别名，
		    index指 定一个名字，用于表示在迭代过程中，每次迭代到的位置，
		    open表示该语句以什么开始，
		    separator表示在每次进行迭代之间以什么符号作为分隔 符，
		    close表示以什么结束。
		    parameterType:可以是简单数组,对象数组,集合等.
	-->
	<select id="queryStuByStuNo" parameterType="int[]" resultMap="studentMap">
		select * from student
		<where>
			and stuNo in
			<!-- item="item"和#{item}名称要相同 -->
			<foreach collection="array" open="(" close=")" item="item" separator=",">
				#{item}
			</foreach>
		</where>
	</select>

        <resultMap type="student" id="studentMap">
		<id property="stuNo" column="stuNo" />
		<result property="stuName" column="stuName" />
		<result property="stuClass" column="stuClass" />
		<result property="stuAge" column="stuAge" />
		<result property="stuSex" column="stuSex" />
		<!-- 一对多 -->
		<collection property="stuAddress" ofType="com.zy.pojo.StudentAddress">
			<result property="homeAddress" column="homeAddress" />
			<result property="schoolAddress" column="schoolAddress" />
		</collection>
	</resultMap>

</mapper>

2.测试类

package com.zy.test;

import java.io.IOException;
import java.util.List;

import org.apache.ibatis.session.SqlSession;

import com.zy.mapper.StudentMapper;
import com.zy.pojo.Student;
import com.zy.util.Util;

public class StudentTest {

	public static void main(String[] args){
        	queryStuByStuNo();
	}
	
//	根据学号查询学生信息
	private static void queryStuByStuNo() {
		SqlSession sqlSession = Util.getSession();
		int[] stuNos = new int[]{1,2,7};
		
		List<Student> studentlist = sqlSession.getMapper(StudentMapper.class).queryStuByStuNo(stuNos);
		for (Student student : studentlist) {
			System.out.println(student);
		}
		Util.closeSqlSession(sqlSession);
	}
	
}

3.运行结果

需要对数据库进行批量操作是可以使用foreach标签