MyBatis - 映射配置文件:SQL编写(案例)
MyBatis - 映射配置文件:SQL编写
一般性CRUD
1 查询所有:selectAll
<mapper namespace="StudentMapper">
<!--
select:查询功能的标签
id属性:唯一标识
resultType属性:指定结果映射对象类型,可以不写
parameterType属性:指定参数映射对象类型,可以不写
-->
<select id="selectAll" resultType="student">
SELECT * FROM student
</select>
</mapper>
对应java语句:
List<Student> list = sqlSession.selectList("StudentMapper.selectAll");
sqlSession.close();
2 依据id查询:selectById
<mapper namespace="StudentMapper">
<select id="selectById" resultType="student" parameterType="int">
SELECT * FROM student WHERE id = #{id}
</select>
</mapper>
对应java语句:
Student stu = sqlSession.selectOne("StudentMapper.selectById", 3);
sqlSession.close();
3 模糊查询:姓名含有“张” - 三种方式
<mapper namespace="StudentMapper">
<select id="findByName" resultType="com.itheima.domain.Student" parameterType="String">
<!-- 1 -->
select * from student where name like '%${value}%'
<!-- 2 -->
select * from student where name like concat{'%', #{name}, '%'}
<!-- 3 -->
select * from student where name like #{name}
</select>
</mapper>
对应java语句:
<!-- 1 -->
List<Student> list = sqlSession.selectList("StudentMapper.findByName", "张");
<!-- 2 -->
List<Student> list = sqlSession.selectList("StudentMapper.findByName", "张");
<!-- 3 -->
List<Student> list = sqlSession.selectList("StudentMapper.findByName", "%张%");
sqlSession.close();
4 查询student表记录数
<mapper namespace="StudentMapper">
<select id="findCount" resultType="int">
select count(*) from student
</select>
</mapper>
对应java语句:
int count = sqlSession.selectOne("StudentMapper.findCount");
sqlSession.close();
5 插入数据
<mapper namespace="StudentMapper">
<insert id="insert" parameterType="student">
INSERT INTO student VALUES (#{id},#{name},#{age})
</insert>
</mapper>
对应java语句:
int result = sqlSession.insert("StudentMapper.insert", stu);
sqlSession.commit();
sqlSession.close();
6 插入数据,并返回添加数据的主键id
<mapper namespace="StudentMapper">
<!--
keyColumn: 获取哪个字段的值
keyProperty: 将查询结果封装到对象的哪个属性
useGeneratedKeys: 是否返回最后添加数据的主键id值
-->
<insert id="saveUserReturnId2" keyColumn="id" keyProperty="id" useGeneratedKeys="true">
insert into student values(#{id},#{name},#{age})
</insert>
</mapper>
对应java语句:
sqlSession.insert("StudentMapper.saveUserReturnId2", student);
sqlSession.commit();
sqlSession.close();
7 修改数据:依据id设置student 的 name、age
<mapper namespace="StudentMapper">
<update id="update" parameterType="student">
UPDATE student SET name = #{name},age = #{age} WHERE id = #{id}
</update>
</mapper>
对应java语句:
int result = sqlSession.update("StudentMapper.update",stu);
sqlSession.commit();
sqlSession.close();
8 依据id删除数据
<mapper namespace="StudentMapper">
<delete id="delete" parameterType="int">
DELETE FROM student WHERE id = #{id}
</delete>
</mapper>
对应java语句:
int result = sqlSession.delete("StudentMapper.delete",5);
sqlSession.commit();
sqlSession.close();
动态SQL
9 多条件查询(可缺少部分条件)
<mapper namespace="StudentMapper">
<!--第一个AND会被自动去除-->
<select id="selectCondition" resultType="student" parameterType="student">
<include refid="select"/>
<where>
<if test="id != null">
AND id = #{id}
</if>
<if test="name != null">
AND name = #{name}
</if>
<if test="age != null">
AND age = #{age}
</if>
</where>
</select>
</mapper>
对应java代码:
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
Student stu = new Student();
stu.setId(2);
stu.setName("李四");
List<Student> list = mapper.selectCondition(stu);
for (Student student : list) {
System.out.println(student);
}
sqlSession.close();
inputStream.close();
10 实现 SELECT * FROM student WHERE id IN (1,2,5) - 参数类型list
<mapper namespace="StudentMapper">
<!--
collection:参数容器类型, (list-集合, array-数组)。
open:开始的 SQL 语句。
close:结束的 SQL 语句。
item:参数变量名。
separator:分隔符。
-->
<!-- 范围查询: 查询条件是List -->
<select id="selectByIds" resultType="student" parameterType="list">
<include refid="select"/>
<where>
<foreach collection="list" open="id IN (" close=")" item="id" separator=",">
#{id}
</foreach>
</where>
</select>
</mapper>
对应java代码:
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
List<Integer> ids = new ArrayList<>();
ids.add(1);
ids.add(2);
ids.add(5);
List<Student> list = mapper.selectByIds(ids);
for (Student student : list) {
System.out.println(student);
}
sqlSession.close();
inputStream.close();
11 实现 SELECT * FROM student WHERE id IN (1,2,5) - 参数类型array
<mapper namespace="StudentMapper">
<!--注意: 参数类型为数组, parameterType类型必须设置为list-->
<select id="findByIdsArray" resultType="Student" parameterType="list">
SELECT * FROM student
<where>
<foreach collection="array" open="id in (" item="id" separator="," close=")">
#{id}
</foreach>
</where>
</select>
</mapper>
对应java代码:
Integer[] ids = {1,2,3};
List<Student> students = dao.findByIdsArray(ids);
for (Student s : students) {
System.out.println(s);
}
sqlSession.close();
inputStream.close();
12 动态更新(修改)数据(可缺少部分参数)
<mapper namespace="StudentMapper">
<update id="updateStu">
update student set
<set>
<!-- 判断条件, 控制set后面的参数 逗号会自动控制 建议都写-->
<if test="name !=null and name!=''">
name = #{name},
</if>
<if test="age !=null and age!=0">
age = #{age},
</if>
</set>
<where>
<if test="id!=null and id!=0">
id=#{id}
</if>
</where>
</update>
</mapper>
对应java代码:
Student stu = new Student();
stu.setId(1);
stu.setName("武2郎");
stu.setAge(18);
dao.updateStu(stu);
sqlSession.commit();
sqlSession.close();
inputStream.close();
抽取sql片段简化编写
<mapper namespace="StudentMapper">
<select id="selectById" resultType="student" parameterType="int">
SELECT * FROM student WHERE id = #{id}
</select>
</mapper>
等价于:
<mapper namespace="StudentMapper">
<sql id="selectStudent">select * from student</sql>
<select id="findById" parameterType="int" resultType="student">
<include refid="selectStudent"></include> where id=#{id}
</select>
</mapper>
数据表字段 与 JavaBean类属性 名称不一致
<mapper namespace="StudentMapper">
<!--
查询的【数据库中表列名】和实体类的【JavaBean类属性】名称不一致情况
id标签:主键
result标签:非主键
property: 配置为实体类的属性名
column: 配置为SQL列名
若对象属性和列名一致时, 可以不配置. 但是为了代码的阅读性,建议都配置上.
-->
<resultMap id="userMap" type="com.itheima.domain.User">
<id property="userId" column="id"></id>
<result property="userName" column="username"></result>
<result property="userAddress" column="address"></result>
<result property="userSex" column="sex"></result>
<result property="userBirthday" column="birthday"></result>
</resultMap>
对应:
<select id="findAll" resultMap="userMap">
select * from user
</select>
</mapper>