1.查询操作:
1.1单个参数查询:
<select id="selectPerson1ByIdWithMap" parameterType="java.lang.Integer" resultType="java.util.Map">
select * from person p where p.person_id = #{id}
</select>
1.2查询结果不是实体类:使用resultType
<select id="selectPersonCount" resultType="java.lang.Integer">
select count(*) from person
</select>
说明:
resultMap与resultType区别
resultMap:适合使用返回值是自定义实体类的情况
resultType:适合使用返回值的数据类型是非自定义的,即jdk的提供的类型
1.3多个参数查询:参数使用map
<select id="selectPerson1ByParams" parameterType="map" resultMap="BaseResultMap">
select * from person p where p.name = #{name} and p.gender = #{gender}
</select>
1.4查询list实体:
<!-- 当查询集合的时候resultMap指的是集合中的泛型 -->
<select id="selectPerson1All" resultMap="BaseResultMap">
select * from person
</select>
说明:resultMap用VO类
1.5模糊查询:
<select id="selectPerson1ByLike" parameterType="java.util.Map" resultMap="BaseResultMap">
select * from person p where p.name like '%${name}%'
</select>
说明:
1.模糊查询:参数需要用map
2.符号为:${}
2.插入:可以返回主键
通过:selectKey标签查询主键:
<insert id="insert" parameterType="person">
<selectKey keyProperty="personId" order="AFTER" resultType="java.lang.Integer">
select LAST_INSERT_ID()
</selectKey>
insert into person1 (person_id, name, gender, person_addr, birthday)
values(#{personId}, #{name}, #{gender}, #{personAddr}, #{birthday})
</insert>
selectKey标签:
keyProperty:实体类中的id属性名,当返回主键的时候就是返回给keyProperty
order:生成主键和执行insert语句的顺序,在mysql是AFTER,在oracle中使用BEFORE
resultType:主键返回的数据类型,和实体类中的主键的类型一致
select LAST_INSERT_ID():mysql中生成主键的sql,oracle中select xxxx.nextval from dual
3.修改操作:
<update id="update" parameterType="person">
update person p set p.name = #{name}
where p.person_id = #{personId}
</update>
<delete id="delete" parameterType="java.lang.Integer">
delete from person where person_id = #{personId}
</delete>
说明:mysql中删除不能使用别名
即:不能用下面的形式
<delete id="delete" parameterType="java.lang.Integer">
delete from person p where p.person_id = #{personId}
</delete>