iBATIS - 使用总结

a. sqlMap的配置:

<sqlMapConfig>

<sqlMap resource="WEB-INF/configs/data.xml" />
<!--<sqlMap resource="WEB-INF/configs/addEmployee.xml" />-->
 
</sqlMapConfig>

b: 如下文件叫data.xml

<sqlMap>

<typeAlias alias="employee" type="com.bean.Employee" />
<typeAlias alias="departMent" type="com.bean.Department" />

<resultMap id="employeeMap" class="employee">
<result property="empNO" column="emp_no" />
<result property="firName" column="first_name" />
<result property="lastName" column="last_name" />
<result property="phoneNum" column="phone_no" />
<result property="hireDate" column="hire_date" />
<result property="sex" column="sex" />
<result property="bithDate" column="birth_date" />
<result property="deptNum" column="dept_no" />
</resultMap>
<resultMap id="deptMap" class="departMent">
<result property="deptNum" column="dept_no" />
<result property="deptName" column="dept_name" />
<result property="manaName" column="manager_name" />
<result property="location" column="location" />
</resultMap>

<!-- 
<select id="employee" parameterClass="String"
resultMap="employeeMap">
<![CDATA[     
SELECT EMP.emp_no,EMP.first_name,EMP.last_name,EMP.phone_no,EMP.hire_date,EMP.sex,EMP.birth_date,EMP.dept_no FROM
t_employee EMP join t_department DEPT on EMP.dept_no = DEPT.dept_no where EMP.first_name=#value#;
]]>
</select>
-->
------------------------------------------------------
<select id="employee" parameterClass="String"
resultClass="employee">
<![CDATA[     
SELECT EMP.first_name as firName,EMP.last_name as lastName,EMP.emp_no as empNO,EMP.dept_no as deptNum FROM
t_employee EMP join t_department DEPT on EMP.dept_no = DEPT.dept_no where EMP.first_name=#value#;
        ]]>
------------------------------------------------------
</select>
<!-- 根据ID选出所有的员工 -->
<select id="findById" parameterClass="String"
resultClass="employee">
<![CDATA[     
SELECT EMP.first_name as firName,EMP.last_name as lastName,EMP.emp_no as empNO,EMP.dept_no as deptNum,EMP.hire_date as hireDate,EMP.sex as sex,EMP.birth_date as bithDate,EMP.phone_no as phoneNum FROM
t_employee EMP where EMP.emp_no=#value#;
        ]]>
------------------------------------------------------
</select>
<!-- 选择出所有员工的信息 -->
<!-- 利用resultMap 返回 -->
 
<select id="getAll"
resultMap="employeeMap">
<![CDATA[     
SELECT EMP.first_name,EMP.last_name,EMP.emp_no,EMP.phone_no,EMP.hire_date,EMP.sex,EMP.birth_date,EMP.dept_no FROM
t_employee EMP;
        ]]>
------------------------------------------------------
</select>

<!-- 利用hashmap来作为返回的结果 -->

<select id="getAllUser"
resultClass="java.util.HashMap">
<![CDATA[     
SELECT EMP.first_name as firName,EMP.last_name as lastName,EMP.emp_no as empNO,EMP.phone_no as phoneNum,EMP.hire_date as hireDate,EMP.sex as sex,EMP.birth_date as bithDate,EMP.dept_no as deptNum FROM
t_employee EMP;
        ]]>
------------------------------------------------------
</select>

<!--  删除员工根据 传进来的员工的主键 -->
<delete id="deleteEmployee" parameterClass="java.lang.String">
<![CDATA[
delete from t_employee where emp_no=#value#;
]]>
</delete>
------------------------------------------------------
<!-- 更新Employee -->
<update id="updateEmployee" parameterClass="employee">
        <![CDATA[
            UPDATE t_employee SET first_name=#firName#, last_name=#lastName#,emp_no=#empNO#
            ,phone_no=#phoneNum#,hire_date=#hireDate#,sex=#sex#,birth_date=#bithDate#,dept_no=#deptNum# WHERE emp_no=#empNO#
        ]]>
</update>
------------------------------------------------------
<select id="getAllUser" parameterClass="java.lang.String"
resultClass="java.util.HashMap">
<![CDATA[     
SELECT EMP.first_name as firName,EMP.last_name as lastName,EMP.emp_no as empNO,EMP.phone_no as phoneNum,EMP.hire_date as hireDate,EMP.sex as sex,EMP.birth_date as bithDate,EMP.dept_no as deptNum FROM
t_employee EMP where EMP.emp_no=#value#;
        ]]>
</select>
------------------------------------------------------
<select id="getAllUser" parameterClass="java.lang.String"
resultClass="java.lang.String">
<![CDATA[     
SELECT EMP.emp_no as empNO  FROM
t_employee EMP where EMP.emp_name=#value#;
        ]]>

</select>
------------------------------------------------------
<select id="preAuthId" parameterClass="java.util.HashMap"
resultClass="java.lang.Long">
<![CDATA[
select distinct count(EMP.emp_no) from t_employee EMP
where EMP.emp_no = 20001
    and a.update_dt >= date_format(#startDate#, '%Y-%m-%d')
    and a.update_dt <= date_format(#endDate#, '%Y-%m-%d');
]]>
</select>

</sqlMap>