Mybatis之SQL映射

最新推荐文章于 2022-12-30 15:00:57 发布
最新推荐文章于 2022-12-30 15:00:57 发布
阅读量756 收藏
点赞数
分类专栏： Mybatis Java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/javaACMer/article/details/9416987
版权


TeacherMapper.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.baidu.sem.teacher.persistence.TeacherMapper">
	<sql id="teacher_fields_All">
		id,user_id,level,main_field,main_course,seniority,information,create_time,end_time
	</sql>
	<sql id="teacher_fields_noId">
		user_id,level,main_field,main_course,seniority,information,create_time,end_time
	</sql>


	<resultMap type="Teacher" id="teacher_ResultMap_ALL">
		<id property="id" column="T_ID" />
		<result property="userId" column="T_USERID" />
		<result property="name" column="U_NAME" />
		<result property="realName" column="U_REALNAME" />
		<result property="email" column="U_EMAIL" />
		<result property="idKind" column="U_IDKIND" />
		<result property="idCardNumber" column="U_IDCARDNUMBER" />
		<result property="cerName" column="CER_NAME" />
		<result property="cerNumber" column="CER_NUMBER" />
		<result property="cerCreate" column="CER_CREATE" />
		<result property="cerEnd" column="CER_END" />
		<result property="telMobile" column="U_TELMOBILE" />
		<result property="gender" column="U_GENDER" />
		<result property="province" column="U_PROVINCE" />
		<result property="city" column="U_CITY" />
		<result property="add" column="U_ADD" />
		<result property="status" column="TA_STATUS" />
		<result property="level" column="T_LEVEL" />
		<result property="mainField" column="T_MAINFIELD" />
		<result property="mainCourse" column="T_MAINCOURSE" />
		<result property="seniority" column="T_SENIORITY" />
		<result property="information" column="T_INFORMATION" />
		<result property="createTime" column="T_CREATE" />
		<result property="endTime" column="T_END" />
	</resultMap>

	<select id="listByCondition" parameterType="map"
		resultMap="teacher_ResultMap_ALL">
		select
		t.id AS T_ID,
		t.user_id AS T_USERID,
		u.name AS U_NAME,
		u.real_name
		AS U_REALNAME,
		u.email AS U_EMAIL,
		u.id_kind AS U_IDKIND,
		u.id_card_number AS U_IDCARDNUMBER,
		u.tel_mobile AS U_TELMOBILE,
		u.gender AS U_GENDER,
		t.level AS T_LEVEL,
		t.main_field AS T_MAINFIELD,
		t.main_course AS T_MAINCOURSE,
		t.seniority
		AS T_SENIORITY,
		t.information
		AS T_INFORMATION,
		t.create_time AS
		T_CREATE,
		t.end_time AS T_END
		FROM
		teacher t
		LEFT OUTER JOIN USER u
		ON t.user_id=u.id
	<where>
			<if test="levels!=null">
				t.level in
				<foreach collection="levels" item="item" index="index" open="("
					separator="," close=")">
					#{item}
				</foreach>
			</if>
			<if test="genders!=null">
				and u.gender in
				<foreach collection="genders" item="item" index="index"
					open="(" separator="," close=")">
				#{item}
				</foreach>
			</if>
			<if test="later_create_time!=null"> and t.create_time >#{later_create_time}</if>
			<if test="earlier_create_time!=null">and t.create_time <#{earlier_create_time}</if>
			<if test="keyWord!=null&keyWord!=''"> and (u.name like #{keyWord}or u.real_name like
				#{keyWord})</if>
		</where>
		<if test="orderBy!=null">
			order by ${orderBy}
			<if test="order!=null">${order}</if>
		</if>
		<if test="size!=null">
			limit
			<choose>
				<when test="start !=null">#{start}</when>
				<otherwise>0</otherwise>
			</choose>
			,#{size}
		</if>
	</select>

	<select id="totalCount" parameterType="map" resultType="long">
		select count(*)
		FROM
		teacher t
		LEFT OUTER JOIN USER u
		ON t.user_id=u.id
	<where>
			<if test="levels!=null">
				t.level in
				<foreach collection="levels" item="item" index="index" open="("
					separator="," close=")">
					#{item}
				</foreach>
			</if>
			<if test="genders!=null">
				and u.gender in
				<foreach collection="genders" item="item" index="index"
					open="(" separator="," close=")">
				#{item}
				</foreach>
			</if>
			<if test="later_create_time!=null"> and t.create_time >#{later_create_time}</if>
			<if test="earlier_create_time!=null">and t.create_time <#{earlier_create_time}</if>
			<if test="keyWord!=null&keyWord!=''"> and (u.name like #{keyWord}or u.real_name like
				#{keyWord})</if>
		</where>
	</select>

	<select id="findByApplyId" parameterType="java.lang.Long"
		resultMap="teacher_ResultMap_ALL">
		select
		t.id AS T_ID,
		t.user_id AS T_USERID,
		u.name AS U_NAME,
		u.real_name
		AS U_REALNAME,
		u.email AS U_EMAIL,
		u.id_kind AS U_IDKIND,
		u.id_card_number AS U_IDCARDNUMBER,
		u.tel_mobile AS U_TELMOBILE,
		u.gender AS U_GENDER,
		ta.status AS TA_STATUS,
		t.level AS T_LEVEL,
		t.main_field AS T_MAINFIELD,
		t.main_course AS T_MAINCOURSE,
		t.seniority
		AS T_SENIORITY,
		t.information AS T_INFORMATION,
		t.create_time AS
		T_CREATE,
		t.end_time AS T_END
		FROM
		teacher_apply ta
		LEFT OUTER JOIN teacher t
		ON ta.teacher_id = t.id
		LEFT OUTER JOIN USER u
		ON t.user_id=u.id
		where ta.id=#{applyId}
</select>

	<select id="findByUserId" parameterType="java.lang.Long"
		resultMap="teacher_ResultMap_ALL">
		select
		t.id AS T_ID,
		t.user_id AS T_USERID,
		u.name AS U_NAME,
		u.real_name
		AS U_REALNAME,
		u.email AS U_EMAIL,
		u.id_kind AS U_IDKIND,
		u.id_card_number AS U_IDCARDNUMBER,
		u.tel_mobile AS U_TELMOBILE,
		u.gender AS U_GENDER,
		t.level AS T_LEVEL,
		t.main_field AS T_MAINFIELD,
		t.main_course AS T_MAINCOURSE,
		t.seniority
		AS T_SENIORITY,
		t.information AS T_INFORMATION,
		t.create_time AS
		T_CREATE,
		t.end_time AS T_END
		FROM
		teacher t
		LEFT OUTER JOIN USER u
		ON t.user_id=u.id
		where u.id=#{userId} ;
	</select>


	<select id="findByTeacherId" parameterType="java.lang.Long"
		resultMap="teacher_ResultMap_ALL">
		select
		t.id AS T_ID,
		t.user_id AS T_USERID,
		u.name AS U_NAME,
		u.real_name
		AS U_REALNAME,
		u.email AS U_EMAIL,
		u.id_kind AS U_IDKIND,
		u.id_card_number AS U_IDCARDNUMBER,
		u.tel_mobile AS U_TELMOBILE,
		u.gender AS U_GENDER,
		t.level AS T_LEVEL,
		t.main_field AS T_MAINFIELD,
		t.main_course AS T_MAINCOURSE,
		t.seniority
		AS T_SENIORITY,
		t.information AS T_INFORMATION,
		t.create_time AS
		T_CREATE,
		t.end_time AS T_END
		FROM
		teacher t
		LEFT OUTER JOIN USER u
		ON t.user_id=u.id
		where t.id=#{teacherId} limit 0,1;
	</select>

	<insert id="insert" parameterType="Teacher" useGeneratedKeys="true"
		keyProperty="id">
		INSERT INTO teacher (
		<include refid="teacher_fields_noId" />
		)
		VALUES
		(#{userId},#{level},#{mainField},#{mainCourse},#{seniority},#{information},#{createTime},#{endTime})
	</insert>
	<update id="update" parameterType="Teacher">
		UPDATE teacher SET
		level =
		#{level},
		main_field = #{mainField},
		main_course = #{mainCourse},
		seniority = #{seniority},
		information = #{information},
		create_time =
		#{createTime},
		end_time = #{endTime}
		where user_id=#{userId}
	</update>
	<update id="updateUser" parameterType="Teacher">
		UPDATE user SET
		real_name =
		#{realName},
		tel_mobile = #{telMobile}
		where id=#{userId}
	</update>

	<delete id="delete" parameterType="Teacher">
		delete from
		teacher
		where id=#{id}
 </delete>
</mapper>




TeacherApplyMapper.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.baidu.sem.teacher.persistence.TeacherApplyMapper">

	<sql id="teacherApply_fields_All">
		id,teacher_id,level,main_field,main_course,seniority,information,file_path,create_time,update_time,status,refuse_reason
	</sql>
	<sql id="teacherApply_fields_insert">
		teacher_id,main_field,main_course,seniority,information,file_path,create_time,status
	</sql>

	<resultMap type="TeacherApply" id="teacherApply_ResultMap">
		<id property="id" column="id" />
		<result property="teacherId" column="teacher_id" />
		<result property="name" column="U_NAME" />
		<result property="realName" column="U_REAL_NAME" />
		<result property="level" column="level" />
		<result property="mainField" column="main_field" />
		<result property="mainCourse" column="main_course" />
		<result property="seniority" column="seniority" />
		<result property="information" column="information" />
		<result property="filePath" column="file_path" />
		<result property="createTime" column="create_time" />
		<result property="updateTime" column="update_time" />
		<result property="status" column="status" />
		<result property="refuseReason" column="refuse_reason" />
	</resultMap>

	<resultMap type="TeacherApply" id="teacherApply_ResultMap11111111">
		<id property="id" column="TA_ID" />
		<result property="teacherId" column="TA_TEACHERID" />
		<result property="name" column="U_NAME" />
		<result property="realName" column="U_REAL_NAME" />
		<result property="level" column="TA_LEVEL" />
		<result property="mainField" column="TA_MAINFIELD" />
		<result property="mainCourse" column="TA_MAINCOURSE" />
		<result property="seniority" column="TA_SENIORITY" />
		<result property="information" column="TA_INFORMATION" />
		<result property="filePath" column="TA_FILEPATH" />
		<result property="createTime" column="TA_CREATE" />
		<result property="updateTime" column="TA_UPDATE" />
		<result property="status" column="TA_STATUS" />
		<result property="refuseReason" column="TA_REFULEREASON" />
	</resultMap>

	<select id="listByCondition" parameterType="map"
		resultMap="teacherApply_ResultMap11111111">
		select
		ta.id AS TA_ID,
		ta.teacher_id AS TA_TEACHERID,
		u.name AS U_NAME,
		u.real_name AS U_REAL_NAME,
		ta.level AS TA_LEVEL,
		ta.main_field AS TA_MAINFIELD,
		ta.main_course AS TA_MAINCOURSE,
		ta.seniority AS TA_SENIORITY,
		ta.information AS TA_INFORMATION,
		ta.file_path AS TA_FILEPATH,
		ta.create_time AS TA_CREATE,
		ta.update_time AS TA_UPDATE,
		ta.refuse_reason AS TA_REFULEREASON,
		ta.status AS STATUS
		from
		teacher_apply ta
		left outer join teacher te
		on ta.teacher_id=te.id
		left outer join user u
		on te.user_id=u.id
		<where>
			<if test="status!=null"> ta.status=#{status}</if>
			<if test="keyWord!=null&keyWord!=''">and (u.name like #{keyWord} or u.real_name like
				#{keyWord})</if>
		</where>
		<if test="orderBy!=null">
			order by ${orderBy}
			<if test="order!=null">${order}</if>
		</if>
		<if test="size!=null">
			limit
			<choose>
				<when test="start !=null">#{start}</when>
				<otherwise>0</otherwise>
			</choose>
			,#{size}
		</if>
	</select>


	<select id="totalCount" parameterType="map" resultType="long">
		select count(*)
		from teacher_apply ta left outer join teacher te
		on
		ta.teacher_id=te.id left outer join user u
		on te.user_id=u.id
		<where>
			<if test="status!=null"> ta.status=#{status}</if>
			<if test="keyWord!=null&keyWord!=''">and (u.name like #{keyWord} or u.real_name like
				#{keyWord})</if>
		</where>
	</select>


	<select id="listByTeacherId" parameterType="java.lang.Long"
		resultMap="teacherApply_ResultMap">
		select
		<include refid="teacherApply_fields_All" />
		from teacher_apply
		where teacher_id=#{teacherId}
		order by create_time desc ;
	</select>

	<select id="findByApplyId" parameterType="java.lang.Long"
		resultMap="teacherApply_ResultMap">
		select
		<include refid="teacherApply_fields_All" />
		from teacher_apply
		where id=#{id} limit 0,1;
	</select>




	<insert id="insert" parameterType="TeacherApply"
		useGeneratedKeys="true" keyProperty="id">
		INSERT INTO teacher_apply (
		<include refid="teacherApply_fields_insert" />
		)
		VALUES
		(#{teacherId},#{mainField},#{mainCourse},#{seniority},#{information},#{filePath},#{createTime},#{status})
	</insert>



	<update id="applySucceed" parameterType="TeacherApply">
		UPDATE teacher_apply app
		SET
		app.update_time= #{updateTime},
		app.status = #{status}
		WHERE
		app.id =#{id}
	</update>


	<update id="applyFailed" parameterType="TeacherApply">
		UPDATE teacher_apply app
		SET
		app.update_time= #{updateTime},
		app.status = #{status},
		app.refuse_reason =#{refuseReason}
		WHERE
		app.id =#{id}
	</update>

	<delete id="deleteByTeaId" parameterType="java.lang.Long">
		delete from
		teacher_apply
		where teacher_id=#{teacherId}
 </delete>



</mapper>



javaACMer旭日东升
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mybatis 映射mysql
cf8833的博客
09-22 294
mybatis 映射mysql step1: F:\project\goodsql52\goodQuery\pom.xml <?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://m
mybatis&sql映射
weixin_43645809的博客
04-12 252
全局配置 属性(properties) 既可以在典型的 Java 属性文件中配置这些属性，也可以在 properties 元素的子元素中设置。 resources–>创建config.properties driverClassName=com.mysql.cj.jdbc.Driver url=jdbc:mysql://127.0.0.1:3306/web_db?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shangh
<select id="selectList" resultMap="BaseResultMap"> select <include refid="Base_Colum...
weixin_35755823的博客
12-30 237
这似乎是一段 SQL 语句。这段语句表示从 sys_terminal_info 表中选择所有列，并在 WHERE 子句中使用 id = 进行过滤。如果 id 不为空，则执行这个过滤。
ThinkPHP3.1新特性：字段合法性检…
zhexiao
03-28 1102
3.1增加了表单提交的字段合法性检测，更好的保护数据的安全性。这一特性是3.1安全特性中的一个重要部分。 表单字段合法性检测需要使用create方法创建数据对象的时候才能生效，有两种方式：   一、属性定义 可以给模型配置insertFields 和 updateFields属性用于新增和编辑表单设置，使用create方法创建数据对象的时候，不在定义范围内的属性将直接丢弃，避免表单提交非法
mybatis <if test=""></if>标签０为null
zhou920786312的博客
07-13 3762
转载https://blog.csdn.net/every__day/article/details/78964180采过一个坑，写篇日志来记录下&lt;if test="state != null and state != ''"&gt;state　=　#{state}&lt;/if&gt;1当状态值设置为０时，操作完了，数据库没反应，没有设置为０ 把状态用1和2表示，不使用０，一切正常，问题消...
Mybatis的sql映射
04-12
mybatis最核心的部分，快速上手！
MyBatis：SQL映射框架的深度探索.pdf
最新发布
05-28
SQL映射文件是MyBatis的核心配置文件，它定义了SQL语句与Java方法之间的映射关系。通过XML文件或注解的方式，开发者可以指定如何将Java对象的属性映射到数据库表的列，以及如何执行数据库操作。
MyBatis SQL映射文件
09-13
MyBatis是一个强大的Java持久层框架，其核心特性之一就是SQL映射文件。这个文件用于定义数据库操作的具体SQL语句，使得开发人员可以专注于SQL的编写，而无需过多关心数据访问层的细节。映射文件的结构清晰，配置简单...
mybatis动态sqlSQL 映射 XML 文件是所有 sql 语句
04-28
mybatis动态sql:SQL 映射 XML 文件是所有 sql 语句放置的地方。需要定义一个 workspace,一般定义为对应的接口类的路径。写好 SQL 语句映射文件后需要在 MyBAtis 配置文件 mappers 标签中引用。
详解Java的MyBatis框架中SQL语句映射部分的编写
09-02
这篇教程将深入讲解SQL映射XML文件的编写，主要关注`resultMap`以及增删查改（CRUD）的实现。 首先，我们来看`resultMap`。`resultMap`是MyBatis中最复杂且强大的元素，它可以极大地简化从结果集中加载对象的过程，...
工具类之一：CSVUtils
javaACMer的专栏
08-12 7323
package com.test.core.util; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import java.io.InputStream; import java.io.InputStre
通用32位MD5算法总结：MD5Util
javaACMer的专栏
07-21 5095
通用32位MD5算法总结：MD5Util
记录日志的工具类LogWriter
javaACMer的专栏
09-10 4053
一个工具类 用来记录日志的： class LogWriter { // 可以写作配置：true写文件; false输出控制台 private static boolean fileLog = true; private static String logFileName = "/home/yzx/yzx/elong/sysout.log"; public static
修改Eclipse指定的javaw.exe
javaACMer的专栏
07-18 3646
修改Eclipse指定的javaw.exe： -startup plugins/org.eclipse.equinox.launcher_1.1.1.R36x_v20101122_1400.jar --launcher.library plugins/org.eclipse.equinox.launcher.win32.win32.x86_1.1.2.R36x_v20101222 -
Java数据库＿缓存思路
javaACMer的专栏
06-05 1966
http://wenku.baidu.com/view/c8d328d7c1c708a1284a44b0.html 舍得网支撑1亿pv/天构架，开源了 说是支持1亿pv/天，也许有点夸张，也是为了吸引您能点进来，如果您能认真看完相信也不会让您失望，当然，肯定有很多“高手”会对此会嗤之以鼻，没关系，有很多眼高手低的人总喜欢评论别人却从不会看清自己。   如果大家真想支持我、支持中国人开源
工具类之二：RegexpUtils
javaACMer的专栏
08-12 1393
package com.test.core.util; import org.apache.log4j.Logger; import org.apache.oro.text.regex.MalformedPatternException; import org.apache.oro.text.regex.Pattern; import org.apache.oro.text.regex.Patt
Java并发 : ThreadLocal的几种误区
javaACMer的专栏
06-05 1364
http://www.blogjava.net/jspark/archive/2006/08/01/61165.html ThreadLocal的几种误区  最近由于需要用到ThreadLocal，在网上搜索了一些相关资料，发现对ThreadLocal经常会有下面几种误解  一、ThreadLocal是java线程的一个实现       ThreadLocal的确是和java线程有关，
算法:排序----Java快速排序
javaACMer的专栏
06-06 1318
http://blog.csdn.net/wangkuifeng0118/article/details/7286332 说来感到惭愧，昨天看别人的博客上面一一讲了一些算法，其实这些算法在大学都学过，不过几乎全部忘记了。虽然现在做java上层开发基本上用不到算法，但是还是感觉算法是一种思想，是一种灵魂，所以又不仅翻开了严蔚敏老师的数据结构，一个一个把以前忘记的算法实现一遍。
JAVA日期加减运算
javaACMer的专栏
07-26 1279
http://blog.csdn.net/liwenfeng1022/article/details/6534176 1.用java.util.Calender来实现    Calendar calendar=Calendar.getInstance();       calendar.setTime(new Date());     System.out.prin
Mybatis 的 SQL 映射 xml代码
05-28
以下是一个简单的 Mybatis SQL 映射 XML 文件的代码示例： ```xml <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> SELECT * FROM user WHERE...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值