1. 动语态SQL
//查询全部
String statement = “select stuno,stuname from student”;
//根据年龄查询学生
String statement = “select stuno,stuname from student where stuage = #{stuage}”;
//根据姓名和年龄查询学生
String statement = "select stuno,stuname from student where stuage = #{stuage} and stuage = #{stuage} ";
select stuno,stuname from student where stuname = #{stuName}and stuage = #{stuAge}
第一种(不常用):
第二种:
select stuno,stuname,stuage from student <where> and stuname = #{st`uName} and stuage = #{stuAge}`
<where>
会自动处理第一个符合条件标签中的 and,但不会处理之后中的and
2. foreach标签
<foreach>迭代的类型:数组、对象数组、集合、属性(Grade类: List<Integer> ids)
1.类型为属性(Grade类: List<Integer>
ids)时:
查询学号为1、2、3的学生信息
ids = {1,2,3};
(将多个元素放入类的属性中)
select stuno,stuname from student where stuno in(1,2,3)
(stuNos是grade类中的属性,其类型为List)
2. 简单类型数组时:
无论编写代码时,传递的是什么参数名(stuNos),在mapper.xml中 必须用array代替该数组
(将多个元素放入int[ ]中)
<select id="querystudentWithL" parameterType="int[]" resultType="student">
select * from student
<where>
<if test="array!=null and array.length>0">
<foreach collection="array" open="and stunum in (" close=")"
item="stuNum" separator=",">
#{stuNum}
</foreach>
</if>
</where>
</select>
3.集合时:
无论编写代码时,传递的是什么参数名(stuNos),在mapper.xml中 必须用list代替该数组
4.对象数组:
例如:Student[] students = {student0,student1,student2} 每个student中包含一个学号属性
(通过传递对象中的属性学号,来传入参数学号)
<!--注意:parameterType="Object[]"和item="student"-->
<select id="queryStudentWithDA" resultType="student" parameterType="Object[]">
select * from student
<where>
<if test="array!=null and array.length>0">
<foreach collection="array" open="and stunum in (" close=")"
separator="," item="student">
#{student.stuNum}
</foreach>
</if>
</where>
</select>
5.SQL片段:
相同代码时
- java:方法
- 数据库:存储过程、存储函数
- Mybatis :SQL片段
a.提取相似代码
<mapper namespace="com.ph.main.mapperI.StudentMapper">
<sql id="sqlCommon">
<where>
<if test="array!=null and array.length>0">
<foreach collection="array" open="and stunum in (" close=")"
separator="," item="student">
#{student.stuNum}
</foreach>
</if>
</where>
</sql>
.....
b.引用
<select id="queryStudentWithDA" resultType="student" parameterType="Object[]">
select * from student
<!-- 如果SQL片段和引用出不在同一个文件中,
则需要 在refid 引用时 加上namespaces:即namespaces.id ,如:
<include refid="com.ph.main.mapperI.abcMapper.sqlCommon"></include>
-->
<include refid="sqlCommon"></include>
</select>