<select id="findById" parameterType="java.lang.Integer" resultType="com.mybatis.domain.User" useCache="true">
select * from user
where id = #{id}
</select>
02 collection 实现懒加载
List<User> findAllLazyInit();
<select id="findAllLazyInit" resultMap="user_account_lazy_init_map">
select * from user
</select>
<select id="findById" resultType="user" parameterType="int" useCache="true">
select * from user where id = #{uid}
</select>
【注意】针对每次查询都需要最新的数据 sql,要设置成 useCache=false,禁用二级缓存
@Update("update user set username=#{username},sex=#{sex},birthday=#{birthday},address=#{address} where id=#{id}")
void update(User user);
@Delete("delete from user where id=#{id} ")
void deleteById(Integer id);
@Select("select * from user where id=#{id} ")
User findById(Integer id);
@Select("select * from user where username like concat('%',#{username},'%') ")
// @Select("select * from user where username like '%${value}%' ")
List<User> findByName(String username);
@Select("select * from user where id=#{id} ")
User findById(Integer id);
05 dao查询使用多个参数
@Select("select * from user where username like concat('%',#{username},'%') and sex = #{sex} ")
List<User> findByUsernameAndSex(@Param("username") String username,@Param("sex") String sex);
03 动态SQL
01 if
<select id="findByCondition" resultMap="userMap">
select * from user
where 1=1
<if test="username != null"></if>
and username like #{username}
</select>
02 choose(when otherwise)
03 trim(where set)
<select id="findByUser" resultType="user" parameterType="user">
select * from user
<where>
<if test="username!=null and username != '' ">
and username like #{username}
</if>
<if test="address != null">
and address like #{address}
</if>
</where>
</select>
04 foreach
<select id="findUserByIds" resultMap="userMap" parameterType="QueryVO">
select * from user
<where>
<if test="ids!=null and ids.size()>0">
<foreach collection="ids" open="id in (" separator="," close=")" item="tmpId" >
#{tmpId}
</foreach>
</if>
</where>
</select>
04 简化编写 sql 语句
<sql id="defaultSql">
select * from user
</sql>
<select id="findAllVersion2" resultMap="accountResultMap">
select
a.id as aid ,a.uid,a.money,
u.id,u.username,u.sex,u.birthday,u.address
from account a
inner join user u
on a.uid = u.id
</select>
<select id="findAllVersion2" resultMap="userMap">
select
a.id as aid ,a.uid,a.money,
u.id,u.username,u.sex,u.birthday,u.address
from user u
left join account a
on a.uid = u.id
</select>
<select id="findAll" resultMap="roleResultMap">
select
u.*,r.id as rid,r.role_name,r.role_desc
from role r
left join user_role ur on r.id = ur.rid
left join user u on u.id = ur.uid
</select>
【mybatis-02】
01 基于代理dao 实现 CRUD
01 select
User findById(Integer id);
<select id="findById" parameterType="java.lang.Integer" resultType="com.mybatis.domain.User">
select * from user
where id = #{id}
</select>
<update id="update" parameterType="com.mybatis.domain.User">
UPDATE USER
set username = #{username},
birthday = #{birthday},
sex = #{sex},
address = #{userAddress}
where id = #{id}
</update>
<select id="findByName" parameterType="java.lang.String" resultType="com.mybatis.domain.User">
select * from user
where username like #{username}【】
</select>
<select id="findByName" parameterType="java.lang.String" resultType="com.mybatis.domain.User">
select * from user
where username like '%${value}%'【写法固定】
</select>
List<User> userList = userDao.findByName("王");【】
04 聚合函数查询
Integer findTotal();
<select id="findTotal" resultType="java.lang.Integer">
select count(*) from user
</select>