Mapper.xml

Mapper搭建笔记

<select id="queryStudentWithOO2LazyLoad" 	parameterType="int"  	resultMap="student_card_lazyLoad_map" >
	<!-- 先查学生 -->
	select * from student 
</select>
<resultMap type="student" id="student_card_lazyLoad_map">
		<!-- 学生的信息 -->
		<id  property="id" column="id"/>
		<result property="name" column="name" />
		<result property="age" column="age" />
	
		<!-- 学生证  ,通过select 在需要的时候再查学生证 -->
		<association property="card" javaType="StudentCard"  select="org.lsj.mapper.StudentCardMapper.queryCardById"  column="cardid"  >
			<!-- <id property="cardId" column="cardId"/>             
				<result property="cardInfo" column="cardInfo"/>   -->	
		</association>
	</resultMap>
	
	 <!-- 29、一对多  出错-->
	<!-- <select id="queryClassAndStudents" parameterType="int" resultMap="class_studsent_map">
		select student.*,studentclass.* from student,studentclass where student.classid=studentclass.classid and studentclass.classid=#{classid}
	</select>
	<resultMap type="StudentClass" id="class_studsent_map">
		
	</resultMap> --> 
	
	<!-- 28、关联查询也就是多表查询 -->
	<select id="queryStudentByNoWith002" 	parameterType="int"  	resultMap="studentcardmap" >
		<!-- select * from student where id=#{id} -->
		<!-- select student.*,studentcard.* from student,studentcard where student.cardid =student.cardid and student.id=#{id} -->
		select student.*,studentcard.* from student ,studentcard where student.cardid=studentcard.cardid and student.id=#{id}
	</select>
	<resultMap type="student" id="studentcardmap">
		<!-- 学生信息 -->
		<id property="id"  column="id"  />
		<result property="name"  column="name" />
		<result property="age"  column="age" />
		<!-- 一对一映射， -->
		<association property="card" javaType="StudentCard">
			<id property="cardid"  column="cardid"  />
			<result property="cardinfo"  column="cardinfo" />
		</association>
</resultMap>
	
<!-- 27、关联查询也就是多表查询 一对一-->
	<select id="queryStudentByNoWithOO" 	parameterType="int"  	resultType="StudentBusiness" >
		<!-- select * from student where id=#{id} -->
		<!-- select student.*,studentcard.* from student,studentcard where student.cardid =student.cardid and student.id=#{id} -->
	select student.*,studentcard.* from student ,studentcard where student.cardid=studentcard.cardid and student.id=#{id}
	</select>
	
<!--26| sql片段提高代码效率 -->
<sql id="objectArrayStunos">
	<where>
  		 <if test="array!=null and array.length>0">
  		 	<foreach collection="array" open=" and  id in (" close=")" 
  		 		item="student" separator=",">   
  		 		#{student.id}
  		 	</foreach>
  		 </if>
  	</where>  
</sql>

<!-- 25、将多个元素值 放入对象数组中Student[] students = {student0,student1,student2}  每个studentx包含一个学号属性 -->
<select id="queryStudentsWithObjectArray"  parameterType="Object[]" resultType="student">
  	select * from student 
  	 <include refid="objectArrayStunos"></include>
</select>
<!-- 25、将多个元素值 放入对象数组中Student[] students = {student0,student1,student2}  每个studentx包含一个学号属性 -->
<!-- <select id="queryStudentsWithObjectArray"  parameterType="Object[]" resultType="student">
  	select * from student 
  	<where>
  		 <if test="array!=null and array.length>0">
  		 	<foreach collection="array" open=" and  id in (" close=")" 
  		 		item="student" separator=",">   
  		 		#{student.id}
  		 	</foreach>
  		 </if>
  	</where>  
</select> -->

<select id="queryStudentsWithList"  parameterType="list" resultType="student">
  	select * from student 
  	<where>
  		 <if test="list!=null and list.size>0">
  		 	<foreach collection="list" open=" and  id in (" close=")" 
  		 		item="id" separator=",">   
  		 		#{id}
  		 	</foreach>
  		 </if>
  	</where>
</select>

<select id="queryStudentsWithArray"  parameterType="int[]" resultType="student">
  	select * from student 
  	<where>
  		 <if test="array!=null and array.length">
  		 	<foreach collection="array" open=" and  id in (" close=")" 
  		 		item="id" separator=",">   
  		 		#{id}
  		 	</foreach>
  		 </if>
  	</where>
</select>

<!-- 22、动态SQL动态传值集合传值 -->
<!-- 将多个元素值 放入对象的属性中 -->
<select id="queryStudentsWithNosInGrade"  parameterType="grade" resultType="student">
  	select * from student 
  	<where>
  		 <if test="stuNos!=null and stuNos.size>0">
  		 	<foreach collection="stuNos" open=" and  id in (" close=")" 
  		 		item="id" separator=",">   
  		 		#{id}
  		 	</foreach>
  		 </if>
  	</where>
</select>

<!-- 21、动态SQL 根据姓名和年龄动态查询全部学生-->
<select id="queryStuByNOrAWishSQLTag" 	 parameterType="student"	resultType="student" >
	select id,name,age from student 
	<where><!--相当于where 1=1  -->
		<!-- <if test="student有stuname属性 且不为null"> -->
		<if test="name !=null and name!=''"> 
			and name = #{name}
		</if>
		<if test="age !=null and age !=0 "> 
			 and age = #{age}
		</if>
	</where>
</select>
<!-- 20、通过hashmap形式 -->
<select id="queryStudentByIdWithHashMap" 	 parameterType="int"	resultType="student" >
	select ic "id",name "name" from student where ic = #{ic}
</select>
<!--  19、数据库和属性不匹配 这个没有测试-->
<select id="queryStudentById" 	 parameterType="int"	resultMap="queryStudentByIdMap" >
	select ic,name from student where id = #{id}
</select>
<resultMap type="student" id="queryStudentByIdMap">
		<!-- 指定类中的属性 和 表中的字段 对应关系 -->
		<id property="id"  column="ic" /> <!--id属性，ic数据库属性  -->
		<result property="name" column="name" />
</resultMap>
<!-- 18、查询单个学生 用到的是hashmap -->
<select id="queryStudentOutByHashMap"  resultType="HashMap" >
	select id "stuid",name "stuname" from student   where id=1
</select>
<!-- 17别名作为Map的key -->
<select id="queryAllStudentsOutByHashMap"  resultType="HashMap" >
	select id "stuid",name "name" from student  
</select>

<!--  17、根据id查单个学生-->
<select id="queryStuByStuno" 	parameterType="int"  	resultType="student">
	select * from student where id = ${value}
</select>
<!-- 16、返回学生总数int -->
<select id="queryStudentCount" 	 	resultType="int" >
	select count(*) from student 
</select>

<!-- 15、存储过程删除学生 -->
	<!-- 通过存储过程实现删除 -->
<delete id="deleteStuBynoWithProcedure" statementType="CALLABLE" parameterType="HashMap">
	{
		CALL pre_deid(
			#{iid,jdbcType=INTEGER,mode=IN}
		) 
	}	
</delete>

<!-- 14、存储过程调用 -->
<!-- 通过调用[存储过程] 实现查询 ,statementType="CALLABLE"
	存储过程的输入参数，在mybatis用Map来传递（HashMap）-->
<select id="queryCountByGradeWithProcedure" statementType="CALLABLE"  parameterType="HashMap" >
	{CALL pre_name1(#{hadr,jdbcType=VARCHAR,mode=IN},#{scount,jdbcType=INTEGER,mode=OUT}) }	
</select>
<!-- 13、hashmap传值 -->
<select id="queryStudentBystuageOrstuNameWithHashMap" 	parameterType="HashMap"	resultType="student" > 
	select id,name,age  from student
	where age= #{age}  or name like '%${name}%' 
</select>

<!-- 12、嵌套写法 级联属性-->
<select id="queryStudentByaddress1" 	parameterType="student"	resultType="student" > 
	select id,name,age,sex  from student
	where homeAddress = #{address.homeAddress} or schoolAddress ='${address.schoolAddress}'
</select>
<!-- 12、嵌套写法 -->
<select id="queryStudentByaddress" 	parameterType="address"	resultType="student" > 
	select id,name,age,sex  from student
	where homeAddress = #{homeAddress} or schoolAddress ='${schoolAddress}'
</select>

<!--  11、模糊查询 查询全部 -->
<select id="queryStudentBystuageOrstuName" 	parameterType="student"	resultType="student" > 
	select id,name,age,sex  from student
	where age= #{age}  or name like '%${name}%' 
</select>

<!-- 10、查询全部学生排序 -->
<select id="queryStudentOrderByColumn" 	parameterType="string"	resultType="student" >
	select id,name,age sex from student  order by ${value} asc
</select>

<!-- 9、parameterType输入参数值 -->
<select id="queryStudentByStuname" 	parameterType="string"  	resultType="student" >
	select id,name, age  from student where name = '${value}'
</select>

<!--  8、后续通过  namespace.id-->
<!--parameterType:输入参数的类型
resultType：查询返回结果值的类型  ，返回类型  -->

<select id="queryStudentByStuno" 	parameterType="int"  	resultMap="studentMapping" >
	select * from student where id = #{id}
	<!-- select * from student where id = ${value} -->
</select>
<resultMap type="student" id="studentMapping">
		<!-- 分为主键id 和非主键 result-->
		<id property="id"  column="id"  />
		<result property="name"  column="name" />
		<result property="age"  column="age" />
		<result property="sex"  column="sex"  javaType="boolean" jdbcType="INTEGER"/>
</resultMap>

<!-- 7、查询：使用了类型转换器
1如果 类中属性 和表中的字段 类型能够合理识别 （String-varchar2），则可以使用resultType;否则(boolean-number) 使用resultMap
2如果 类中属性名 和表中的字段名能够合理识别 （stuNo -stuno）则可以使用resultType;否则(id-stuno) 使用resultMap
 -->
<select id="queryStudentByStunoWithConverter" 	parameterType="int"  	
resultMap="studentResult" >
	select * from student where id = #{id}
</select>
<resultMap type="student" id="studentResult">
		<!-- 分为主键id 和非主键 result-->
		<id property="id"  column="id"  /> <!-- 写主键 -->
		<result property="name"  column="name" />
		<result property="age"  column="age" />
		<result property="sex"  column="sex"  javaType="boolean" jdbcType="INTEGER"/>
</resultMap>

<!-- 6、带转换器的增加 -->
<insert id="addStudentWithConverter" parameterType="student" >
	insert into student(id,name,age,sex) values(#{id},#{name},#{age} ,#{sex ,javaType=BOOLEAN  ,jdbcType=INTEGER } ) 
</insert>

<!-- 5、学生查询sql语句   加别名-->
<select id="queryStudentId" resultType="student"  parameterType="int">
	select * from Student where  id = #{id} 
</select>	

<!-- 4、添加学生 -->
<insert id="addStudent" parameterType="student" >
	insert into student(id,name,age) values(#{id},#{name},#{age}  ) 
</insert>
<!-- 3、删除学生 -->
<delete id="deleteStudentByStuno"  parameterType="int">
	delete from student where id = #{id} 
</delete>
<!-- 2、更改学生 -->
<update id="updateStudentByStuno" parameterType="student" >
	update student set age=#{age} ,name=#{name}  where id=#{id} 
</update>
<!--  1、查询全部学生-->
<select id="queryAllStudents"  resultType="student" >
	select * from student 
</select>