MyBatis动态SQL

MyBatis动态SQL

<select id="findUserByCondition" resultType="user" parameterType="user">
    select * from user
    <where>
        <if test="id != 0 and id != null">
            and id=#{id}
        </if>
        <if test="username != null">
            and username = #{username}
        </if>
        <if test="password != null">
            and password=#{password}
        </if>
    </where>
</select>

<select id="findUserByIds" resultType="user" parameterType="list">
    select * from user
    <where>
        <foreach collection="list" open="id in (" close=")" item="uid" separator=",">
            #{uid}
        </foreach>
    </where>
</select>

MyBatis多表查询

1.配置文件

<resultMap id="userMap" type="user">
    <id property="id" column="id"/>
    <result property="username" column="username"/>
    <result property="password" column="password"/>
    <association property="department" javaType="department">
        <id property="id" column="did"/>
        <result property="dName" column="d_name"/>
    </association>
</resultMap>
<select id="selectAll" resultMap="userMap">
    SELECT * FROM dept d , USER u WHERE u.id=d.did
</select>


<resultMap id="deptMap" type="department">
    <id column="did" property="id"/>
    <result column="d_name" property="dName"/>
    <collection property="userList" ofType="user">
        <id column="id" property="id"/>
        <result column="username" property="username"/>
        <result column="password" property="password"/>
    </collection>
</resultMap>
<select id="findAllDept" resultMap="deptMap">
    SELECT * FROM dept d , USER u WHERE u.id=d.did
</select>

2.多表注解

@Select("SELECT * FROM dept d , USER u WHERE u.id=d.did")
@Results({
    @Result(id = true,column ="id",property = "id"),
    @Result(column = "username",property = "username"),
    @Result(column = "password",property = "password"),
    @Result(column = "did",property = "department.id"),
    @Result(column = "d_name",property = "department.dName")
})
List<User> selectAll();


@Select("SELECT * FROM dept d , USER u WHERE u.id=d.did")
@Results({
    @Result(id = true,column = "did",property = "id"),
    @Result(column = "d_name",property = "dName"),
    @Result(
        column = "id",
        property = "userList",
        javaType = List.class,
        many = @Many(select = "cn.liuweiwei.mapper.UserMapper.selectUserById")
    )
})
List<Department> selectAllDept();

3.MyBatis基本注解（基本的CRUD）

@Delete("delete from user where id = #{id}")
void deleteUserById(Integer id);

@Insert("insert into user values(null,#{username},#{password})")
void insertUser(User user);

@Update("update user set username=#{username},password=#{password} where id=#{id} ")
void updateUser(User user);

@Select("select * from user where id = #{id}")
User  selectUserById(Integer id);