我們之前拿最簡(jiǎn)單的查詢(xún)做了例子演示框架搭建董瞻，現(xiàn)在暴心，讓我們看一下復(fù)雜的查詢(xún)以及增刪改如何去做，一般情況下谷遂，我們都是一個(gè)SQL映射文件對(duì)應(yīng)一張表和一個(gè)實(shí)體類(lèi)，有關(guān)該實(shí)體類(lèi)的增刪改查都在同一個(gè)映射文件中

一单料、回顧一下最簡(jiǎn)單的查詢(xún)

select元素相關(guān)屬性
id,必須存在的屬性埋凯，值從概念上講是唯一標(biāo)示点楼，不能隨便寫(xiě)扫尖，應(yīng)對(duì)應(yīng)接口中方法的名
parameterType，若該方法有參掠廓，則必須有該屬性换怖，值和接口中參數(shù)的類(lèi)型相對(duì)應(yīng)
resultType，結(jié)果集的類(lèi)型蟀瞧，經(jīng)過(guò)查詢(xún)沉颂，數(shù)據(jù)庫(kù)中的信息自動(dòng)封裝，指定類(lèi)型悦污，就告訴框架你把信息封裝成一個(gè)什么類(lèi)型的對(duì)象

二铸屉、多個(gè)參數(shù)

需求：根據(jù)用戶(hù)名和編號(hào)進(jìn)行查詢(xún)
接口中增加方法

User selectUser(String username,int id);

發(fā)現(xiàn)parameterType無(wú)法指定類(lèi)型，
①加注解

User selectUser(@Param("username")String username,@Param("id")int id);

<select id="selectUser" resultType="User">
        select * from t_user where username = #{username} and id = #{id}
    </select>

②參數(shù)整合在一起切端，使用bean對(duì)象,

List<User> selectUser(User user);

<select id="selectUser" parameterType="User" resultType="User">
        select * from t_user where username = #{username} and id = #{id}
    </select>

注意彻坛，查詢(xún)結(jié)果有多條，使用list集合作為返回值類(lèi)型踏枣，resultType指定集合中元素的類(lèi)型

三昌屉、刪除

需求：根據(jù)id進(jìn)行刪除

void deleteUser(int id);

<delete id="deleteUser" parameterType="int">
        delete from t_user where id = #{value}
    </delete>

去測(cè)試類(lèi)中執(zhí)行發(fā)現(xiàn)并沒(méi)有生效，沒(méi)有自動(dòng)提交茵瀑，因此需要在try中調(diào)用session的commit方法间驮，在catch中調(diào)用session的rollback方法

四、修改

void updateUser(User user);

<update id="updateUser" parameterType="User">
        update t_user set username = #{username},password = #{password}
        where id = #{id}
    </update>

五马昨、插入

<!-- keyProperty屬性主鍵列對(duì)應(yīng)的屬性的名稱(chēng)
    order屬性BEFORE表示子查詢(xún)?cè)谥鞑樵?xún)前
     -->
    <insert id="insertUser">
        insert into t_user values(#{id},#{username},sysdate,#{password})
        <selectKey keyProperty="id" resultType="int" order="BEFORE">
            select max(id)+1 from t_user
        </selectKey>
    </insert>

void insertUser(User user);

User user = new User();
            user.setUsername("k");
            user.setPassword("k");
            userMapper.insertUser(user);
            session.commit();

六竞帽、特殊結(jié)果、參數(shù)

1鸿捧、特殊參數(shù)類(lèi)型使用
根據(jù)用戶(hù)名和密碼查詢(xún)屹篓，比如此時(shí)用戶(hù)名和密碼不同時(shí)寫(xiě)在同一個(gè)實(shí)體類(lèi)中，此時(shí)要使用一個(gè)參數(shù)就要用到Map

User selectUserByNAP(Map<String,String> map);

<select id="selectUserByNAP" parameterType="map" resultType="User">
        select * from t_user where username = #{m_username} and password = #{m_password}
    </select>

Map<String,String> map = new HashMap<String,String>();
            map.put("m_username", "l");
            map.put("m_password", "l");
            User user = userMapper.selectUserByNAP(map);
            System.out.println(user.getPassword());

sql語(yǔ)句中參數(shù)等于Map的鍵
2笛谦、特殊結(jié)果集使用
如果實(shí)體類(lèi)屬性與數(shù)據(jù)庫(kù)列名不一樣抱虐，就使用resultMap作為結(jié)果集類(lèi)型

User selectUserByNAP1(Map<String,String> map);

<select id="selectUserByNAP1" parameterType="map" resultMap="userres">
        select * from t_user where username = #{m_username} and password = #{m_password}
    </select>
    <resultMap type="User" id="userres">
        <result column="password" property="password"></result>
    </resultMap>

Map<String,String> map = new HashMap<String,String>();
            map.put("m_username", "l");
            map.put("m_password", "l");
            User user = userMapper.selectUserByNAP1(map);
            System.out.println(user.getPassword());

type:查詢(xún)結(jié)果最終得到的類(lèi)型
3、建employee和department兩張表饥脑，其中employee表有eid恳邀、eno懦冰、ename、deptid等字段谣沸，department表有did刷钢、dno、dname等字段

-- Create table
create table T_DEPARTMENT
(
  did   NUMBER(4) not null,
  dno   VARCHAR2(20),
  dname VARCHAR2(20)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table T_DEPARTMENT
  add constraint PK_DID_DEPARTMENT primary key (DID)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

-- Create table
create table T_EMPLOYEE
(
  eid    NUMBER(4) not null,
  eno    VARCHAR2(20),
  ename  VARCHAR2(20),
  deptid NUMBER(4)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table T_EMPLOYEE
  add constraint PK_EID_EMPLOYEE primary key (EID)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
alter table T_EMPLOYEE
  add constraint FK_DEPTID_EMPLOYEE foreign key (DEPTID)
  references T_DEPARTMENT (DID);

public class Employee {
    private int eid;
    private String eno;
    private String ename;
    private Department dept;
}

public class Department {
    private int did;
    private String dno;
    private String dname;
    private List<Employee> employees;
}

七乳附、多表查詢(xún)

1内地、一對(duì)一關(guān)聯(lián)
如果要做查詢(xún)員工信息同時(shí)將部門(mén)信息同時(shí)查出來(lái)
（1）嵌套子查詢(xún)

public Employee getEmployeeById(int eid);

<select id="getEmployeeById" parameterType="int" resultMap="empres">
        select * from t_employee where eid = #{eid}
    </select>
    <!-- 嵌套子查詢(xún)
    association元素
    property屬性嵌套查詢(xún)的結(jié)果賦值給Employee中屬性的名字 
    column屬性外鍵字段的名稱(chēng)
    javaType屬性嵌套子查詢(xún)返回結(jié)果集的類(lèi)型
    select屬性嵌套的子查詢(xún)對(duì)相應(yīng)查詢(xún)
    -->
    <resultMap type="Employee" id="empres">
        <association property="dept" column="deptid" javaType="Department" select="getDeptById"></association>
    </resultMap>
    <select id="getDeptById" parameterType="int" resultType="Department">
        select * from t_department where did = #{deptid}
    </select>

association元素
property屬性嵌套查詢(xún)的結(jié)果賦值給Employee中屬性的名字 
column屬性外鍵字段的名稱(chēng)
javaType屬性嵌套子查詢(xún)返回結(jié)果集的類(lèi)型
select屬性嵌套的子查詢(xún)對(duì)相應(yīng)查詢(xún)

以上查詢(xún)的做法實(shí)際上非常不好，僅做了一個(gè)多表查詢(xún)就需要多次操作數(shù)據(jù)庫(kù)
（2）嵌套結(jié)果

public Employee getEmployeeByNo(String eno);

<select id="getEmployeeByNo" parameterType="string" resultMap="empRes">
        select * 
        from t_employee e,t_department d
        where e.deptid = d.did
        and e.eno = #{eno}
    </select>
    <resultMap type="employee" id="empRes">
        <result column="eid" property="eid"/>
        <result column="eno" property="eno"/>
         <result column="ename" property="ename"/>
        <association property="dept" column="deptid" javaType="Department" resultMap="deptRes"></association>
    </resultMap>
    <resultMap type="department" id="deptRes">
        <result property="did" column="did"></result>
        <result property="dno" column="dno"></result>
        <result property="dname" column="dname"></result>
    </resultMap>

EmployeeDao employeeDao = session.getMapper(EmployeeDao.class);
            Employee employee = employeeDao.getEmployeeByNo("1");
            System.out.println(employee.getDept().getDid());

2赋除、一對(duì)多關(guān)聯(lián)
查詢(xún)部門(mén)信息阱缓，并將該部門(mén)所有員工查詢(xún)出來(lái)

Department getDepartmentById(int did);

<select id="getDepartmentById" parameterType="int" resultMap="deptRes">
        select *
        from t_employee e,t_department d
        where e.deptid = d.did
        and d.did = #{did}
    </select>
    <resultMap type="department" id="deptRes">
    <result property="did" column="did"></result>
    <result property="dno" column="dno"></result>
    <result property="dname" column="dname"></result>
    <!-- 一對(duì)多的關(guān)聯(lián)查詢(xún)
    collection元素
    property屬性針對(duì)多個(gè)員工信息封裝出的集合容器類(lèi)型的對(duì)象，賦值給department類(lèi)中的對(duì)應(yīng)的屬性
    ofType屬性集合中每個(gè)元素的類(lèi)型 -->
        <collection property="employees" ofType="employee">
            <result property="eid" column="eid"></result>
            <result property="eno" column="eno"></result>
            <result property="ename" column="ename"></result>
        </collection>
    </resultMap>

DepartmentDao departmentDao = session.getMapper(DepartmentDao.class);
            Department department = departmentDao.getDepartmentById(1);
            List<Employee> list = department.getEmployees();
            System.out.println(list.get(0).getEid());

3举农、向員工表中插入一條數(shù)據(jù)荆针，同時(shí)指定其部門(mén)信息

<insert id="insert" parameterType="employee">
        insert into t_employee values (#{eid},#{eno},#{ename},#{dept.did})
        <selectKey keyProperty="eid"  resultType="int" order="BEFORE">
            select max(eid)+1 from t_employee
        </selectKey>
    </insert>

void insert(Employee e);

Employee employee = new Employee();
            employee.setEno("2");
            employee.setEname("Lily");
            Department department = new Department();
            department.setDid(1);
            employee.setDept(department);
            EmployeeDao employeeDao = session.getMapper(EmployeeDao.class);
            employeeDao.insert(employee);
            session.commit();

4、分頁(yè)查詢(xún)
利用pageHelper插件
首先引入jar包
jsqlparser-0.9.jar
pagehelper-3.6.4.jar
在核心配置文件中加入

<!-- plugins元素加在typeAliases下 -->
    <plugins>
        <plugin interceptor="com.github.pagehelper.PageHelper">
            <!-- property元素指明和哪個(gè)數(shù)據(jù)庫(kù)匹配使用 -->
            <property name="dialect" value="oracle"></property>
        </plugin>
    </plugins>

在查詢(xún)映射文件中加入

<select id="getAll" resultType="user">
        select * from t_user
    </select>

List<User> getAll();

//獲取第一頁(yè)數(shù)據(jù)颁糟，每頁(yè)顯示1條
            Page page = PageHelper.startPage(1, 1);
            userMapper.getAll();
            System.out.println("共"+page.getTotal()+"條記錄");
            List<User> list = page.getResult();
            for(User u:list){
                System.out.println(u.getPassword());
            }

5航背、組合查詢(xún)

<!-- 條件查詢(xún)where元素，補(bǔ)齊where關(guān)鍵字 -->
    <select id="getByCondition" parameterType="user" resultType="user">
        select * from t_user
        <!-- where 1=1 -->
        <where>
            <if test="id != null and id != 0">
                and id = #{id}
            </if>
            <if test="username != null and username != ''">
                and username = #{username}
            </if>
        </where>
    </select>

6棱貌、批量刪除

<!-- 參數(shù)是數(shù)組玖媚，無(wú)需加參數(shù)
        foreach元素遍歷集合容器對(duì)象
        collection屬性指定的是方法傳入的類(lèi)型
        item屬性每次遍歷到的元素
        open屬性遍歷得到的結(jié)果的開(kāi)始字符
        separator屬性元素之間的分隔符
     -->
    <delete id="deleteUsers">
        delete from t_user where id in
        <foreach collection="array" item="item" open="(" close=")" separator=",">
            #{item}
        </foreach>
    </delete>

void deleteUsers(int[] ids);

int[] i = new int[2];
            i[0] = 3;
            i[1] = 4;
            userMapper.deleteUsers(i);
            session.commit();

7、緩存

User user = userMapper.getUser(1);
            user.setPassword("k");
            //修改查詢(xún)到的user信息
            System.out.println(user.getPassword());
            user.setPassword("j");
            //再次查詢(xún)理論上應(yīng)該得到數(shù)據(jù)庫(kù)中的內(nèi)容婚脱，但是卻是上述修改后的內(nèi)容今魔，這是因?yàn)镸ybatis緩存
            //緩存是因?yàn)樘岣咝阅?            user = userMapper.getUser(1);
            System.out.println(user.getPassword());

此時(shí)需要在sql映射文件中加入flushCache="true"

<select id="getUser" parameterType="java.lang.Integer" resultType="User" flushCache="true">
        select * from t_user where id = #{value}
    </select>

當(dāng)查詢(xún)數(shù)據(jù)庫(kù)返回結(jié)果時(shí)，mybatis會(huì)生成key起惕、value形式的對(duì)象存在于緩存涡贱，在接下來(lái)在內(nèi)存中針對(duì)查詢(xún)結(jié)果做變更，相當(dāng)于改變了緩存中的數(shù)據(jù)惹想，再次查詢(xún)不訪問(wèn)數(shù)據(jù)庫(kù)问词，而是直接采用緩存中的數(shù)據(jù)

八、模糊查詢(xún)

接口中寫(xiě)入

List<User> getUserByName(String name);

注意｀至弧＜づ病！mybatis接口中方法不要重載
mapper文件中
寫(xiě)法1

<select id="getUserByName" parameterType="String" resultType="User">
        select * from t_user where username like '%${value}%'
    </select>

寫(xiě)法2

<select id="getUserByName" parameterType="String" resultType="User">
        select * from t_user where username like concat('%',#{value},'%')
    </select>

寫(xiě)法3

<select id="getUserByName" parameterType="String" resultType="User">
        select * from t_user where username like "%"#{value}"%"
    </select>