一 动态sql
添加接口和配置文件
public interface UserMapper {
List<User> queryUserByCondition(User user);
}
<?xml version="1.0" encoding="UTF-8" ?>
<!--MyBatis的DTD约束-->
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xinzhi.dao.UserMapper">
<select id="queryUserByCondition" resultType="com.xinzhi.model.User" parameterType="com.xinzhi.model.User">
</select>
</mapper>
1 if (格式)
<if test="条件">
sql 语句
</if>
当条件成立的时候,会执行sql语句
if (条件){
sql 语句
}
choose
<choose>
<when test="条件1">
sql语句1
</when>
<when test="条件2">
sql语句2
</when>
<otherwise>
sql语句3
</otherwise>
</choose>
和我们java的if...else if ...else格式一样
当条件1成立,那么就不会执行后面的代码
where
<select id="queryUserByCondition" resultType="com.xinzhi.model.User" parameterType="com.xinzhi.model.User">
select id,username,password,age,phone from user
<where>
<if test="age!=null and age!=''">
and age=#{age}
</if>
<if test="phone!=null and phone!=''">
and phone=#{phone}
</if>
</where>
</select>
set
<update id="updateUser" parameterType="com.xinzhi.model.User">
update user
<set>
<if test="username!=null and username!=''">username=#{username},</if>
<if test="password!=null and password!=''">password=#{password},</if>
</set>
where id=#{id}
</update>
foreach
循环遍历标签。适用于多个参数或者的关系。
<foreach collection=“”open=“”close=“”item=“”separator=“”>
获取参数
</foreach>
trim
格式 <trim prefix=前缀'' prefixoverrides=''
suffix=后缀'' suffixoverrides=''>
Sql片段
<sql id="别名">
查询的所有字段
</sql>
使用的时候 <include refid="别名"/>
二 分页
<!-- pageHelper依赖-->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>4.1.6</version>
</dependency>
<dependency>
<groupId>com.github.jsqlparser</groupId>
<artifactId>jsqlparser</artifactId>
<version>0.9.6</version>
</dependency>
2 mybatis配置文件中指定方言
<plugins>
<!-- 注意:分页助手的插件 配置在通用mapper之前 -->
<plugin interceptor="com.github.pagehelper.PageHelper">
<!-- 指定方言 -->
<property name="dialect" value="mysql"/>
</plugin>
</plugins>
mybatis多表查询
<resultMap id="cards" type="com.xinzhi.model.Card">
<id column="id" property="id"></id>
<result column="num" property="num"></result>
<association property="user" javaType="com.xinzhi.model.User">
<id column="uid" property="id"></id>
<result column="username" property="username"></result>
<result column="password" property="password"></result>
<result column="age" property="age"></result>
<result column="phone" property="phone"></result>
</association>
</resultMap>
<select id="findAllCard" resultMap="cards">
select c.id id,num,uid,username,password,age,phone from card c,user u where c.uid=u.id
</select>