//POJO實體publicclassQueryConditionObject{private User user;//user get,set方法}//Dao 接口publicinterfaceUserMapper{List<User>findByCondition(QueryConditionObject qco);}//UserMapper.xml中的select標簽<select id="findByCondition" resultType="com.regotto.domain.User"parameterType="com.itheima.domain.QueryVo"><!--此處使用OGNL表達式, 傳遞select查詢的條件-->select * from user where username like #{user.username};</select>
<select id="findByUser" resultType="user" parameterType="user">select * from user where 1=1<if test="username!=null and username != '' ">and username like #{username}</if><if test="address != null">and address like #{address}</if></select>
where標簽(簡化where 1=1的寫法)
<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>
foreach標簽(等價于for循環, 重復執行Sql)
classQuery{private List<Integer> ids;//ids get set方法...}<!-- 查詢所有用戶在 id 的集合之中 --><select id="findInIds" resultType="user" parameterType="com.regotto.Query"><!-- select * from user where id in (1,2,3,4,5);-->select * from user<where><if test="ids != null and ids.size() > 0"><foreach collection="ids" open="id in ( " close=")" item="uid" separator=",">#{uid}</foreach></if></where></select>SQL 語句：select 字段 from user where id in (?)<foreach>標簽用于遍歷集合，它的屬性：collection:代表要遍歷的集合元素，注意編寫時不要寫#{}open:代表語句的開始部分close:代表結束部分item:代表遍歷集合的每個元素，生成的變量名sperator:代表分隔符
MyBatis多表查詢
一對一(多對一) User <=> Account
1. 編寫sql語句: select * from user, account where user.id = account.id;
2. 定義實體(可以使用int, String等基本屬性定義, 定義的實體內容決定了Mapper.xml中select標簽的編寫方式)class AccountUser{private User user;private String id;private String name;//......//get, set方法}
3. 定義dao接口interface AccountUserMapper{AccountUser findAccountUser();}
4. 定義AccountUserMapper.xml文件resultMap建立查詢結果與AccountUser屬性的對應關系<resultMap type="account" id="accountMap"> <id column="aid" property="id"/><result column="uid" property="uid"/><result column="money" property="money"/><!-- 它是用于指定從表方的引用實體屬性的 --> <association property="user" javaType="user"> <id column="id" property="id"/><result column="username" property="username"/><result column="sex" property="sex"/><result column="birthday" property="birthday"/><result column="address" property="address"/></association></resultMap> <select id="findAll" resultMap="accountMap">select u.*,a.id as aid,a.uid,a.money from account a,user u where a.uid =u.id;</select>
一對多 User <=> 多個Account
1. 編寫sql: select u.*,a.id as aid ,a.uid,a.money from user u left outer join account a on u.id =a.uid
2. 定義實體class User{private String id;private String name;private List<Account> accounts;//get, set方法}
3. 定義dao接口方法: List<Account> findAll();
4. UserMapper.xml
<resultMap type="user" id="userMap"> <id column="id" property="id"></id> <result column="username" property="username"/><result column="address" property="address"/><result column="sex" property="sex"/><result column="birthday" property="birthday"/><!-- collection 是用于建立一對多中集合屬性的對應關系ofType 用于指定集合元素的數據類型--> <collection property="accounts" ofType="account"> <id column="aid" property="id"/><result column="uid" property="uid"/><result column="money" property="money"/></collection>
</resultMap>
<!-- 配置查詢所有操作 -->
<select id="findAll" resultMap="userMap">select u.*,a.id as aid ,a.uid,a.money from user u left outer join account a on u.id =a.uid
</select>
相比于一對一, 若實體屬性是集合等結構, 則使用collection標簽
多對多 多個Role <=> 多個User, 中間表USER_ROLE
1.編寫sql:SELECT r.*,u.id uid, u.username username FROM ROLE rINNER JOIN USER_ROLE ur ON ( r.id = ur.rid) INNER JOIN USER u ON (ur.uid = u.id);
2.編寫實體:class Role {private id;private name;List<User> users;//get set方法}
3.dao接口:List<Role> findAll();
4.編寫Mapper.xml<!--定義 role 表的 ResultMap--><resultMap id="roleMap" type="role"><id property="roleId" column="rid"></id><result property="roleName" column="role_name"></result><result property="roleDesc" column="role_desc"></result><collection property="users" ofType="user"><id column="id" property="id"></id><result column="username" property="username"></result><result column="address" property="address"></result><result column="sex" property="sex"></result><result column="birthday" property="birthday"></result></collection></resultMap><!--查詢所有--><select id="findAll" resultMap="roleMap">select u.*,r.id as rid,r.role_name,r.role_desc from role r
<!-- 根據 id 查詢 -->
<select id="findById" resultType="UsEr" parameterType="int" useCache="true">select * from user where id = #{uid}
</select>useCache: 設定緩存是否有效
