目录
1 简单聚合函数:
select count(1) from user
2 动态sql查询:
<sql id="ColumnSQL">
id,username,birthday,sex,address
</sql>
<sql id="selectUserByUserNameAndSexSQL">
<if test="username != null and username != ''">
AND username = #{username}
</if>
<if test="sex != null and sex != ''">
AND sex = #{sex}
</if>
</sql>
<select id="findUserById" parameterType="Integer" resultType="User">
select <include refid="ColumnSQL"/> from user where id = #{id}
</select>
select from user where id = #{id}
3 动态sql if标签和where标签
<sql id="WhereSQl">
<if test="username != null and username != ''">
username like "%"#{username}"%"
</if>
<if test="sex != null and sex != ''">
and sex = #{sex}
</if>
</sql>
<select id="findUserByIfAndWhere" parameterType="User" resultType="User">
select * from user
<where>
<include refid="WhereSQl"/>
</where>
</select>
4 foreach
4.1之数组
例子:删除27 和29号员工信息
<delete
<delete id="deleteIds" parameterType="String[]">
delete from tb_build_duty_list where id in
<foreach collection="array" item="id" open="(" close=")" separator=",">
#{id}
</foreach>
</delete>
4.2 参数封装在map
<select id="dynamicForeach3Test" parameterType="java.util.HashMap" resultType="Blog">
select * from t_blog where title like "%"#{title}"%" and id in
<foreach collection="ids" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</select>
dao层:
public List dynamicForeach3Test(Map params);
service层:
List ids = new ArrayList();
ids.add(1);
Map params = new HashMap();
params.put("ids", ids);
5 sqlMapper.xml需要说明的问题
1、一对一:<association property="tempClass" javaType="com.mybatis.bean.Class">
association字面意思关联,这里只专门做一对一关联; property表示是com.mybatis.bean.StudentTemp中的属性名称; javaType表示该属性是什么类型对象
2、类似视图查询: resultType="com.mybatis.bean.studentclass">
resultType 对应简单类型,也就是实体中的属性名称与数据库表字段名称一模一样;
resultMap 对应复杂类型,属性名称与字段名称不一样可以通过resultMap中property,column进行映射,其中一对一与一对多都是用resultMap来映射
3、一对多:<collection property="students" ofType="com.mybatis.bean.Student">
collection字面意思是集合,这里专门做一对多关联 ,property表示集合类型属性名称,ofType表示集合中的对象是什么类型
4、select * from ...与select 字段1,字段2,字段3... from ...
可以显示指出c.class_id,c.class_name,s.s_id,s.s_name列明,如果用"*"代替,则mybatis会自动匹配resultMap或者resultType中提供的列名,
如果某对象存在列映射的属性,但是在select 后面找不到这个列,则在返回结果中,该列多映射的值为null。
至此,对于一对一、一对多、视图查询都能很好的解决,主要还是要重点关注第四点说明。
自关联查询(一对多 直接复制):
<resultMap id="BaseResultMap" type="com.yty.bean.Menu" >
<id column="menuid" property="menuid" jdbcType="VARCHAR" />
<result column="menuname" property="menuname" jdbcType="VARCHAR" />
<result column="icon" property="icon" jdbcType="VARCHAR" />
<result column="url" property="url" jdbcType="VARCHAR" />
<collection property="menus" column="menuid" ofType="com.yty.bean.Menu" select="searchMenusByParentId" />
</resultMap
5.1 mybatis配置一对多(一般可做级联删除)
案例:
用户表和用户详细表
user.xml 中添加一下字段,实体类类中添加 private User user;
<!-- 一对多设置 -->
<!-- detail详细表 -->
<collection property="detaillist" ofType="com.gztpay.card.bean.UserDetail" column="uid">
<id column="id" property="id" jdbcType="INTEGER" />
<result column="money" property="money" jdbcType="DOUBLE" />
<result column="Hit_Card" property="hitCard" jdbcType="VARCHAR" />
<result column="Pay_State" property="payState" jdbcType="VARCHAR" />
<result column="Card_State" property="cardState" jdbcType="VARCHAR" />
<result column="datetime" property="datetime" jdbcType="TIMESTAMP" />
<result column="date" property="date" jdbcType="DATE" />
</collection>
详细表中添加一下字段:实体类中添加private List detaillist;
<!-- 用户表 -->
<association property="user" javaType="com.gztpay.card.bean.User">
<id column="id" property="id" jdbcType="INTEGER" />
<result column="username" property="username" jdbcType="VARCHAR" />
<result column="phone" property="phone" jdbcType="VARCHAR" />
<result column="name" property="name" jdbcType="VARCHAR" />
<result column="userImage" property="userimage" jdbcType="VARCHAR" />
<result column="totalmoney" property="totalmoney" jdbcType="DOUBLE" />
<result column="datetime" property="datetime" jdbcType="TIMESTAMP" />
<result column="date" property="date" jdbcType="DATE" />
</association>
5.2 navicate 中配置一对多
至此,搭建完成