mybatis之sql片段和一对多等配置
来源:互联网 发布:蓝牙耳机控制软件 编辑:程序博客网 时间:2024/06/08 07:29
<?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">
<!-- 命名空间 test作区别时 使用原始Dao开发 使用Mapper代理开发 namespace: 对此接口进行绑定 -->
<mapper namespace="com.mybatis.mapper.UserMapper">
<!-- // 查询用户表数据条数 -->
<!-- public Integer countUser(); -->
<select id="countUser" resultType="Integer">
select count(1) from user
</select>
<!-- public User findUserByIdWithQueryVo(QueryVo vo); -->
<select id="findUserByIdWithQueryVo" parameterType="QueryVo"
resultType="User">
select * from user where id = #{user.id}
</select>
<!-- id:findUserById 找到此sql parameterType : 传入参数的类型 parameterMap : 官方 文档
已经废除 #{id} : mybatis #{} 表示占位符 resultMap : 手动映射 半自动的手动映射 不同写上 相同不用写 会自动映射的
resultType : 自动映射 -->
<resultMap type="User" id="user">
<result column="username_" property="username" />
</resultMap>
<select id="findUserById" parameterType="Integer" resultMap="user">
select id,username username_,sex,address,birthday from user where id =
#{vvv}
</select>
<!-- 根据用户名称模糊查询用户列表 List<User> #{} : 占位符 select * from user where username
like '明' '明' == ? 防 ${} : 字符串拼接 select * from user where username like 明
不防 select * from user where username like "%"'明'"%" 此种方式 占位符还能模糊查询 -->
<select id="findUserListByUsername" parameterType="String"
resultType="com.mybatis.pojo.User">
select * from user where username like "%"#{vvv}"%"
</select>
<!-- 添加 order="AFTER" mysql 自增长ID 先保存的数据 再生成的ID order="BEFORE" oracle 自增长
UUID 先生成ID 再保存数据 order="BEFORE" mysql UUID 先生成ID 再保存数据 -->
<!-- <insert id="addUser" parameterType="com.mybatis.pojo.User">
<selectKey keyProperty="id" resultType="Integer" order="AFTER"> select LAST_INSERT_ID()
</selectKey> -->
<insert id="addUser" parameterType="com.mybatis.pojo.User"
useGeneratedKeys="true" keyProperty="id">
insert into user
(username,sex,birthday,address)
values
(#{username},#{sex},#{birthday},#{address})
</insert>
<!-- 更新 -->
<update id="updateUserById" parameterType="com..mybatis.pojo.User">
update user set
username=#{username},
sex=#{sex},
birthday=#{birthday},
address=#{address}
where id = #{id}
</update>
<!-- 删除 -->
<delete id="deleteUserById" parameterType="Integer">
delete from user
where id = #{vvvvv}
</delete>
<!-- sql片断 -->
<sql id="selector">
select * from user
</sql>
<!-- // 需求:根据性别和名字查询用户 -->
<!-- public List<User> findUserListByusernameAndSex(User user); <where>
只能去掉前and 不能去掉后 and -->
<select id="findUserListByusernameAndSex" parameterType="User"
resultType="User">
<include refid="selector" />
<where>
<if test="username != null and username != ''">
and username like "%"#{username}"%"
</if>
<if test="sex != null and sex != ''">
and sex = #{sex}
</if>
</where>
</select>
<!-- // 根据多个id查询用户信息 select * from user where id in (1,10,29) -->
<!-- public List<User> findUserListByList(List<Integer> ids); <foreach collection="list"
跟 入参List<Integer> ids 名称 ids 没有关系 必须List list -->
<select id="findUserListByList" parameterType="Integer"
resultType="User">
<include refid="selector" />
<where>
<foreach collection="list" item="id" separator="," open="id in ("
close=")">
#{id}
</foreach>
</where>
</select>
<!-- // 根据多个id查询用户信息 -->
<!-- public List<User> findUserListByArray(Integer[] ids); -->
<select id="findUserListByArray" parameterType="Integer"
resultType="User">
<include refid="selector" />
<where>
<foreach collection="array" item="id" separator="," open="id in ("
close=")">
#{id}
</foreach>
</where>
</select>
<!-- // 根据多个id查询用户信息 -->
<!-- public List<User> findUserListByQueryVo(QueryVo vo); -->
<select id="findUserListByQueryVo" parameterType="QueryVo"
resultType="User">
<include refid="selector" />
<where>
<foreach collection="ids" item="id" separator="," open="id in ("
close=")">
#{id}
</foreach>
</where>
</select>
<resultMap type="User" id="userAndOrder">
<id column="id" property="id"/>
<result column="username" property="username"/>
<result column="birthday" property="birthday"/>
<result column="sex" property="sex"/>
<result column="address" property="address"/>
<!-- 一对多映射 标签 -->
<collection property="orders" ofType="Orders">
<id column="oid" property="id"/>
<result column="number" property="number"/>
<result column="createtime" property="createtime"/>
</collection>
</resultMap>
<!-- 案例:查询所有用户信息及用户关联的订单信息。 -->
<select id="findUserListAndOrder" resultMap="userAndOrder">
SELECT
u.id,
u.username,
u.birthday,
u.sex,
u.address,
o.id oid,
o.number,
o.createtime
FROM
`user` u
LEFT JOIN `orders` o ON u.id = o.user_id
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!-- 命名空间 test作区别时 使用原始Dao开发 使用Mapper代理开发 namespace: 对此接口进行绑定 -->
<mapper namespace="com.mybatis.mapper.UserMapper">
<!-- // 查询用户表数据条数 -->
<!-- public Integer countUser(); -->
<select id="countUser" resultType="Integer">
select count(1) from user
</select>
<!-- public User findUserByIdWithQueryVo(QueryVo vo); -->
<select id="findUserByIdWithQueryVo" parameterType="QueryVo"
resultType="User">
select * from user where id = #{user.id}
</select>
<!-- id:findUserById 找到此sql parameterType : 传入参数的类型 parameterMap : 官方 文档
已经废除 #{id} : mybatis #{} 表示占位符 resultMap : 手动映射 半自动的手动映射 不同写上 相同不用写 会自动映射的
resultType : 自动映射 -->
<resultMap type="User" id="user">
<result column="username_" property="username" />
</resultMap>
<select id="findUserById" parameterType="Integer" resultMap="user">
select id,username username_,sex,address,birthday from user where id =
#{vvv}
</select>
<!-- 根据用户名称模糊查询用户列表 List<User> #{} : 占位符 select * from user where username
like '明' '明' == ? 防 ${} : 字符串拼接 select * from user where username like 明
不防 select * from user where username like "%"'明'"%" 此种方式 占位符还能模糊查询 -->
<select id="findUserListByUsername" parameterType="String"
resultType="com.mybatis.pojo.User">
select * from user where username like "%"#{vvv}"%"
</select>
<!-- 添加 order="AFTER" mysql 自增长ID 先保存的数据 再生成的ID order="BEFORE" oracle 自增长
UUID 先生成ID 再保存数据 order="BEFORE" mysql UUID 先生成ID 再保存数据 -->
<!-- <insert id="addUser" parameterType="com.mybatis.pojo.User">
<selectKey keyProperty="id" resultType="Integer" order="AFTER"> select LAST_INSERT_ID()
</selectKey> -->
<insert id="addUser" parameterType="com.mybatis.pojo.User"
useGeneratedKeys="true" keyProperty="id">
insert into user
(username,sex,birthday,address)
values
(#{username},#{sex},#{birthday},#{address})
</insert>
<!-- 更新 -->
<update id="updateUserById" parameterType="com..mybatis.pojo.User">
update user set
username=#{username},
sex=#{sex},
birthday=#{birthday},
address=#{address}
where id = #{id}
</update>
<!-- 删除 -->
<delete id="deleteUserById" parameterType="Integer">
delete from user
where id = #{vvvvv}
</delete>
<!-- sql片断 -->
<sql id="selector">
select * from user
</sql>
<!-- // 需求:根据性别和名字查询用户 -->
<!-- public List<User> findUserListByusernameAndSex(User user); <where>
只能去掉前and 不能去掉后 and -->
<select id="findUserListByusernameAndSex" parameterType="User"
resultType="User">
<include refid="selector" />
<where>
<if test="username != null and username != ''">
and username like "%"#{username}"%"
</if>
<if test="sex != null and sex != ''">
and sex = #{sex}
</if>
</where>
</select>
<!-- // 根据多个id查询用户信息 select * from user where id in (1,10,29) -->
<!-- public List<User> findUserListByList(List<Integer> ids); <foreach collection="list"
跟 入参List<Integer> ids 名称 ids 没有关系 必须List list -->
<select id="findUserListByList" parameterType="Integer"
resultType="User">
<include refid="selector" />
<where>
<foreach collection="list" item="id" separator="," open="id in ("
close=")">
#{id}
</foreach>
</where>
</select>
<!-- // 根据多个id查询用户信息 -->
<!-- public List<User> findUserListByArray(Integer[] ids); -->
<select id="findUserListByArray" parameterType="Integer"
resultType="User">
<include refid="selector" />
<where>
<foreach collection="array" item="id" separator="," open="id in ("
close=")">
#{id}
</foreach>
</where>
</select>
<!-- // 根据多个id查询用户信息 -->
<!-- public List<User> findUserListByQueryVo(QueryVo vo); -->
<select id="findUserListByQueryVo" parameterType="QueryVo"
resultType="User">
<include refid="selector" />
<where>
<foreach collection="ids" item="id" separator="," open="id in ("
close=")">
#{id}
</foreach>
</where>
</select>
<resultMap type="User" id="userAndOrder">
<id column="id" property="id"/>
<result column="username" property="username"/>
<result column="birthday" property="birthday"/>
<result column="sex" property="sex"/>
<result column="address" property="address"/>
<!-- 一对多映射 标签 -->
<collection property="orders" ofType="Orders">
<id column="oid" property="id"/>
<result column="number" property="number"/>
<result column="createtime" property="createtime"/>
</collection>
</resultMap>
<!-- 案例:查询所有用户信息及用户关联的订单信息。 -->
<select id="findUserListAndOrder" resultMap="userAndOrder">
SELECT
u.id,
u.username,
u.birthday,
u.sex,
u.address,
o.id oid,
o.number,
o.createtime
FROM
`user` u
LEFT JOIN `orders` o ON u.id = o.user_id
</select>
<resultMap type="Orders" id="orderAndUser">
<id column="id" property="id"/>
<result column="userId" property="userId"/>
<result column="number" property="number"/>
<result column="createtime" property="createtime"/>
<!-- 一 对一映射标签 -->
<association property="user" javaType="User">
<result column="username" property="username"/>
<result column="address" property="address"/>
</association>
</resultMap>
阅读全文
0 0
- mybatis之sql片段和一对多等配置
- Mybatis一对一和一对多配置
- mybatis一对多配置
- mybatis一对多sql语句:
- Mybatis动态sql和sql片段
- hibernate和mybatis一对一和一对多配置
- mybatis一对多查询配置
- MyBatis 一对多映射配置
- mybatis一对多的配置
- mybatis一对多配置问题
- mybatis配置一对多关系
- mybatis 一对多的配置 和 查询数据覆盖问题
- Mybatis一对一和一对多
- mybatis关联配置(一对多配置)
- mybatis 一对多,多对一配置
- mybatis 一对多,多对一配置
- spring+mybatis 一对多配置总结
- Mybatis(ibatis)的一对多关联配置
- validate和bootstrap的使用
- AOP之使用AspectJ案例
- skynet 常用lua函数汇总
- ERDAS大气校正
- RUP,极限编程与敏捷过程的概念
- mybatis之sql片段和一对多等配置
- MMC子系统调用过程浅析(Card层)
- Bitmap 解析大图时 OOM 解决办法
- 2016 ACM-ICPC Asia Regional B – Help the Princess!(bfs)
- python 内存二进制读取图片
- Camera使用-实现简单的自定义相机
- Merged Manifest合并清单的效果并找出冲突错误
- svn代码管理中 需要提交哪些文件
- Self join