mybatis基本查询

<?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="cn.xdf.wlyy.jxdtest.dao.RoleDao">

<select id="findById" parameterType="int"
resultType="cn.xdf.wlyy.jxdtest.po.Role">
SELECT * from role where id=#{value}
</select>

<select id="findByName" parameterType="String"
resultType="cn.xdf.wlyy.jxdtest.po.Role">
<!-- 模糊查询的两种方法 -->
<!-- SELECT * from role where name like '%${value}%'; -->
SELECT * from role where name like concat(concat('%',#{value}),'%');

</select>

<delete id="deleteById" parameterType="int">
DELETE FROM role WHERE
id=#{value}
</delete>

<insert id="insertRole" parameterType="cn.xdf.wlyy.jxdtest.po.Role">
INSERT into
role(name,u_id) VALUES(#{name},#{u_id})
</insert>

<update id="updateRole" parameterType="cn.xdf.wlyy.jxdtest.po.Role">
UPDATE role set
name=#{name},u_id=#{u_id} where id =#{id}
</update>

<!--查询所有 resultMap返回结果 -->
<resultMap type="cn.xdf.wlyy.jxdtest.po.Role" id="listRoleMap">
<id property="id" column="id" />
<id property="name" column="name" />
<id property="u_id" column="u_id" />
</resultMap>
<select id="listRole" resultMap="listRoleMap">
select r.id,r.name,r.u_id from
role as r;
</select>
<!-- 查询所有 resultType返回结果 -->
<select id="listRoletype" resultType="cn.xdf.wlyy.jxdtest.po.Role">
select r.id,r.name,r.u_id
from role as r;
</select>

<!-- 多表联查 -->
<!-- 关联所需要的列 笛卡尔积 -->
<resultMap type="cn.xdf.wlyy.jxdtest.po.User" id="getUserMap">
<id property="id" column="u_id" />
<result property="name" column="u_name" />
</resultMap>
<resultMap type="cn.xdf.wlyy.jxdtest.po.Role" id="userRoleMap">
<id property="id" column="r_id" />
<result property="name" column="r_name" />
<association property="user" javaType="cn.xdf.wlyy.jxdtest.po.User"
resultMap="getUserMap" />
</resultMap>
<select id="getUserRole" resultMap="userRoleMap">
SELECT r.id r_id,u.id u_id,
r.name r_name,u.name u_name FROM role as r,user as u where r.u_id=u.id
</select>

<!-- 根据参数联查 -->
<resultMap type="cn.xdf.wlyy.jxdtest.po.Role" id="userRoleMapById">
<id property="id" column="r_id" />
<result property="name" column="r_name" />
<!-- 一对一内嵌如查询 colum指向要映射的一方 -->
<!-- <association property="user" column="u_id" select="getUser"></association> -->
<!-- 映射实体一一对应 列名需通过别名映射 负责为空 -->
<association property="user" javaType="cn.xdf.wlyy.jxdtest.po.User" >
<id property="id" column="u_id" />
<result property="name" column="u_name" />
</association>
<!-- 一对多实现的两种方式 -->
<!-- ofType指定集合中的对象类型 -->
<!-- 不嵌套映射 -->
<!-- <collection property="users" ofType="cn.xdf.wlyy.jxdtest.po.User">
映射中的别名必须写别名 <id property="id" column="u_id"/> <result property="name" column="u_name"/>
</collection> -->

 

<!--一对多嵌套方式column指向一的一方(唯一键) -->
<collection property="users" ofType="cn.xdf.wlyy.jxdtest.po.User"
column="r_id" select="getUsers"></collection>
<!--一对多实现的两种方式 -->

</resultMap>
<select id="getUserRoleByUid" parameterType="int" resultMap="userRoleMapById">
<!-- 多对一查询 -->
<!-- select r.id r_id ,r.name r_name ,u.id u_id,u.name u_name from role
as r ,user as u where r.u_id=u.id and r.u_id=#{value} -->
<!-- 一对多查询 -->
select r.id r_id ,r.name r_name ,u.id u_id,u.name u_name from role as
r ,user as u where r.id=u.r_id and r.id=#{value}
</select>

<!-- 在做单表查询时候 字段为数据表中原有字段 -多对一 -->
<select id="getUser" parameterType="int" resultType="cn.xdf.wlyy.jxdtest.po.User">
select id
,name from user where id=#{value}
</select>
<!-- 一对多查询 -->
<select id="getUsers" parameterType="int"
resultType="cn.xdf.wlyy.jxdtest.po.User">
select id ,name from user where r_id=#{value}
</select>

</mapper>