目录
一、多表查询
1、一对一
--一个学生对应他的一个班级信息
1)查询结果
2)类实现
● Student类
class Student{
private Integer id;
private String name;
private Integer age;
private String gender;
private Banji banji;//班级信息
/* set、get */
}
● Banji类
class Banji{
private Integer id;
private String name;
/* set、get */
}
3)自己写映射 搜索结果包括(学生(名字...) 班级(id,name))
● resultMap 映射代码
<!--映射(自己来映射,防止类属性和表字段名字不一致)-->
<resultMap id="studentMap" type="Student">
<id column="id" property="id"/>
<result column="name" property="name" />
<result column="age" property="age" />
<result column="gender" property="gender" />
<association property="banji" javaType="Banji">
<id column="banjiId" property="id"/>
<result column="banjiName" property="name" />
</association>
</resultMap>
<association>标签:
用于一对一关系
就是 将另一张表的字段 关联过来 然后一起映射到实体类;
● StudentMapper.xml
<!--一对一:一个学生对应一个班级信息-->
<!--可以直接写一个类封装我们多表的结果,而不用resultMap-->
<select id="selectStuBanji" resultMap="studentMap" >
SELECT s.id,s.`name`,s.age,s.gender,b.id AS banjiId,b.name AS banjiName
FROM student AS s INNER JOIN banji AS b
ON s.banji_id=b.id;
</select>
2、一对多
--一个班级对应他的一群学生
1)查询结果图
2)类实现
● Banji类
class Banji{
private Integer id;
private String name;
private List<Student> studentList;//班级下面有多个学生
/* set、get */
}
3)代码实现
● resultMap 映射 搜索结果
<resultMap id="banjiMap" type="Banji">
<id column="id" property="id"></id>
<result column="name" property="name"/>
<collection property="studentList" ofType="Student">
<id column="studentId" property="id"/>
<result column="studentName" property="name" />
<result column="studentAge" property="age" />
<result column="studentGender" property="gender" />
</collection>
</resultMap>
<collection>标签:实现一对多关联
● BanjiMapper.xml
<!--一对多:一个班级有多个学生-->
<select id="selectBanjiStu" resultMap="banjiMap">
select b.id,b.name,s.id 'studentId',s.name 'studentName',s.age 'studentAge',s.gender 'studentGender'
from banji b inner join student s on b.id=s.banji_id ;
</select>
3、多对多
--一个班级学习多个课程,一个课程被多个班级学习
1)类实现
二、动态SQL(动态拼接)
1、where标签
--解决条件查询的
问题:不知道谁是第一个(第一个不用拼and)
1)原sql语句
select id,name,age,gender from student where name like '%张%' and age=89;
2)where标签--动态Sql
<!--动态sql select-->
<select id="selectByCondition" parameterType="Student" resultType="Student">
select <include refid="studentColumns"/>
from student
<where>
<if test="name!=null and name!=''">
AND name like concat('%',#{name},'%')
</if>
<if test="age!=null">
AND age=#{age}
</if>
<if test="gender!=null and gender!=''">
AND gender=#{gender}
</if>
</where>
</select>
注意:前面加 and
3)测试
@Test
public void test8(){
SqlSession sqlSession=MyBatisUtil.getSqlSession();
Student s=new Student();
s.setName("张");
s.setAge(89);
List<Student> stuList = sqlSession.selectList("student.selectByCondition",s);
for (Student student : stuList) {
System.out.println(student);
}
}
2、set标签
解决更新的
--问题:不知道谁是最后一个(最后一个不用拼 ,)
1)原sql语句
update student set name='张三三新',age=4,gender='女' where id=7;
2)set标签-动态Sql语句
<update id="updateCondition" parameterType="Student">
update student
<set>
<if test="name!=null and name!=''">
name=#{name},
</if>
<if test="age!=null">
age=#{age},
</if>
<if test="gender!=null and gender!=''">
gender=#{gender},
</if>
</set>
where id=#{id}
</update>
3)测试
@Test
public void test9(){
/*手动提交*/
SqlSession sqlSession= MyBatisUtil.getSqlSession();
Student student=new Student();
student.setId(7);
student.setName("张三三新");
student.setAge(4);
student.setGender("女");
System.out.println(student);
int update = sqlSession.update("student.updateCondition", student);
sqlSession.commit();
System.out.println(update);
}
3、foreach标签
--解决in(1,2)的问题
1)原sql语句
delete from student where id in(2,3);
2)foreach标签
● 以数组形式传递
<!--传递数组-->
<delete id="deleteAllByArray">
delete from student
where id in
<foreach collection="array" open="(" item="id" close=")" separator=",">
#{id}
</foreach>
</delete>
● 以集合形式传递
<!--传递集合-->
<delete id="deleteAllByList">
delete from student
where id in
<foreach collection="list" open="(" item="id" close=")" separator=",">
#{id}
</foreach>
</delete>
3)测试
@Test
public void test10(){
int[] array={2,3};
SqlSession sqlSession= MyBatisUtil.getSqlSession();
int delete = sqlSession.delete("student.deleteAllByArray", array);
sqlSession.commit();
System.out.println(delete);
}
4、choose、when、otherwise
==>
if----else if----else if ----else
解决的问题:
name、age、gender搜索时候只能按照其中一个来搜索,但是优先级
name>age>gender
1)代码
<!--if else-->
<select id="selectByCondition2" parameterType="Student" resultType="Student">
select <include refid="studentColumns"/>
from student
where
<choose>
<when test="name!=null and name!=''">
name like concat('%',#{name},'%')
</when>
<when test="age!=null">
age=#{age}
</when>
<when test="gender!=null and gender!=''">
gender=#{gender}
</when>
<otherwise></otherwise>
</choose>
</select>