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"

<!-- 模糊查询的两种方法 -->

<!-- SELECT * from role where name like '%${value}%'; -->

SELECT * from role where name like concat(concat('%',#{value}),'%');

<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;

<!-- 查询所有 resultType返回结果 -->

<select id="listRoletype" resultType="cn.xdf.wlyy.jxdtest.po.Role">

select r.id,r.name,r.u_id

from role as r;

<!-- 多表联查 -->

<!-- 关联所需要的列 笛卡尔积 -->

<resultMap type="cn.xdf.wlyy.jxdtest.po.User" id="getUserMap">

<id property="id" column="u_id" />

<result property="name" column="u_name" />

<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" />

<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

<!-- 根据参数联查 -->

<resultMap type="cn.xdf.wlyy.jxdtest.po.Role" id="userRoleMapById">

<!-- 一对一内嵌如查询 colum指向要映射的一方 -->

<!-- <association property="user" column="u_id" select="getUser"></association> -->

<!-- 映射实体一一对应 列名需通过别名映射 负责为空 -->

<association property="user" javaType="cn.xdf.wlyy.jxdtest.po.User" >

</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>

<!--一对多实现的两种方式 -->

<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 id="getUser" parameterType="int" resultType="cn.xdf.wlyy.jxdtest.po.User">

select id

,name from user where id=#{value}

<select id="getUsers" parameterType="int"

resultType="cn.xdf.wlyy.jxdtest.po.User">

select id ,name from user where r_id=#{value}

</mapper>