对数据库的增删改查：
映射文件...Mapper.xml：
<mapper namespace="TeacherMapper">
<!-- 查询 -->
<select id="selectTeacher" parameterType="Integer" resultMap="teacherList">
select t.tno,t.tname,t.tsex,t.tbirthday,t.prof,t.depart from teacher t where t.tno=#{tno}
</select>
<!-- 增添 -->
<insert id="insertTeacher" parameterType="tea" useGeneratedKeys="true" keyProperty="tno" keyColumn="TNO">
insert into teacher values(#{tno}, #{tname}, #{tsex}, #{tbirthday}, #{prof}, #{depart})
</insert>
<!-- 修改 -->
<update id="updateTeacher" parameterType="Map">
update teacher t set t.tsex=#{teatsex},t.prof=#{teaprof}
where t.tno=#{teatno}
</update>
<!-- 删除 -->
<delete id="deleteTeacher" parameterType="Integer">
delete teacher t Where t.tno=#{tno};
</delete>
</mapper>
java文件...Test.java:
public void test() {
//查询
// Teacher s = ss.selectOne("TeacherMapper.selectTeacher", 831);
// System.out.println(s);
//添加
// Teacher t = new Teacher(843, "刘毅", "男", new Date(), "副教授", "计算机系");
// int a = ss.insert("TeacherMapper.insertTeacher", t);
// System.out.println(a);
// System.out.println(t.getTno());
//修改
Map<String, Object> map = new HashMap<String, Object>();
map.put("teatno", 804);
map.put("teaprof", "教授");
map.put("teadepart", "计算机系");
int c = ss.update("TeacherMapper.updateTeacher", map);
System.out.println(c);
//删除
// Teacher n = ss.selectOne("TeacherMapper.selectTeacher", 831);
// int i = ss.delete("TeacherMapper.deleteTeacher", n);
}
}
一对一，一对多的查询：
一对一查询：（映射文件）
<!-- 对象及联 -->
<!-- <resultMap type="Employee" id="empList">
<id property="ids" column="IDS" />
<result property="ename" column="ENAME" />
<result property="esex" column="ESEX" />
<result property="birthday" column="BIRTHDAY" />
<result property="address" column="ADDRESS" />
<result property="dept.id" column="ID" />
<result property="dept.dname" column="DNAME" />
<result property="dept.createtime" column="CREATETIME" />
</resultMap> -->
<!-- 关联 -->
<!-- <resultMap type="Employee" id="empList2">
<id property="ids" column="IDS" />
<result property="ename" column="ENAME" />
<result property="esex" column="ESEX" />
<result property="birthday" column="BIRTHDAY" />
<result property="address" column="ADDRESS" />
<association property="dept" resultMap="deptList"></association>
</resultMap>
<resultMap type="dept" id="deptList">
<result property="id" column="ID" />
<result property="dname" column="DNAME" />
<result property="createtime" column="CREATETIME" />
</resultMap> -->
<!-- 关联查询 -->
<resultMap type="Employee" id="empList3">
<association property="dept" column="DEPTID"
select="com.hanqi.maya.mapper.DeptMapper.selectDeptById" />
</resultMap>
<select id="selectAllEmployee" resultMap="empList3">
select * from employee
</select>
一对多：（映射文件）
<resultMap type="Dept" id="deptList">
<id property="id" column="ID"></id> <!-- 不加它取出的ID主键为空 -->
<collection property="employees" column="ID"
select="com.hanqi.maya.mapper.EmployeeMapper.selectEmpsByDeptid"></collection>
</resultMap>
<select id="selectDeptById" parameterType="Integer" resultMap="deptList">
select * from dept t where t.id=#{id}
</select>
动态SQL语句：
映射文件...Mapper.xml:
<mapper namespace="com.hanqi.maya.mapper.EmployeeMapper">
<!-- 使用Map参数查询 -->
<select id="selectEmpByMapParam" parameterType="Map" resultType="Employee">
select * from employee e
<if test="sex!=null">
where e.esex=#{sex}
</if>
</select>
<!-- 使用多个参数查询 -->
<select id="selectEmpByMultipleParam" resultType="Employee">
select * from employee e where e.ename like #{param1} and e.esex like #{param2}
</select>
<!-- 以实体类作为参数 -->
<select id="selectEmpByModelParam" parameterType="Employee" resultType="Employee">
select * from employee e where 1=1
<if test="ename!=null">
and e.ename like #{ename}
</if>
<if test="esex!=null">
and e.esex like #{esex}
</if>
</select>
<!-- <choose><when> 分支标签, 只判断其中一个值 -->
<select id="selectEmpByChoose" parameterType="Map" resultType="Employee">
select * from employee e where 1=1
<choose>
<when test="ename!=null">
and e.ename like #{ename}
</when>
<when test="esex!=null">
and e.esex like #{esex}
</when>
<otherwise>
and e.address = "山东淄博"
</otherwise>
</choose>
</select>
<!-- where标签 -->
<select id="selectEmpByWhere" parameterType="Map" resultType="Employee">
select * from employee e where 1=1
<if test="ename!=null">
and e.ename like #{ename}
</if>
<if test="address!=null">
and e.address like #{address}
</if>
</select>
<!-- Trim标签 -->
<select id="selectEmpByTrim" resultType="Employee">
select * from Employee e
<trim prefix="where" prefixOverrides="and|or">
<if test="name!=null">
and e.ename like #{name}
</if>
<if test="address!=null">
and e.address like #{address}
</if>
</trim>
</select>
<!-- Foreach标签 方式一-->
<select id="selectEmpByForeach1" resultType="Employee">
select * from employee e where e.ids in
<foreach collection="idsList" item="mids" open="(" close=")" separator=",">
#{mids}
</foreach>
</select>
<!-- Foreach标签 方式二-->
<select id="selectEmpByForeach2" resultType="Employee">
select * from employee e where e.ids in
<foreach collection="list" item="mdis" open="(" close=")" separator=",">
#{mdis}
</foreach>
</select>
<!-- Foreach标签 方式三-->
<select id="selectEmpByForeach3" resultType="Employee">
select * from employee e where e.ids in
<foreach collection="array" item="mids" open="(" close=")" separator="," index="index">
#{mids}
</foreach>
</select>
<!-- set标签 -->
<update id="updateEmpBySet" parameterType="Employee">
update employee e
<set>
<if test="ename!=null">
e.ename=#{ename},
</if>
<if test="esex!=null">
e.esex=#{esex},
</if>
<if test="birthday!=null">
e.birthday=#{birthday},
</if>
<if test="address!=null">
e.address=#{address},
</if>
</set>
where e.ids=#{ids}
</update>
<!-- Sql标签 -->
<sql id="pageSqlPre">
select * from (
</sql>
<sql id="pageSqlSuf">
where rownum <![CDATA[<=]]> 9) r where r.rnum>6
</sql>
<select id="selectEmployeeBySql" resultType="Employee">
<include refid="pageSqlPre"></include>
select e.*, rownum rnum from employee e
<include refid="pageSqlSuf"></include>
</select>
<!-- selectKey标签 -->
<insert id="insertEmployee" parameterType="Employee">
<selectKey keyProperty="ids" order="BEFORE" resultType="int">
select test.nextval from dual
</selectKey>
insert into Employee
values(#{ids},#{ename},#{esex},#{birthday},#{address},null)
</insert>
接口...Mapper.xml:
public interface EmployeeMapper {
//使用Map参数查询
List<Employee> selectEmpByMapParam(Map<String,Object> map);
//使用多个参数查询
List<Employee> selectEmpByMultipleParam(String name,String sex);
//以实体类为参数
List<Employee> selectEmpByModelParam(Employee emp);
// <choose><when> 分支标签, 只判断其中一个值
List<Employee> selectEmpByChoose(Map<String,Object> map);
//where标签
List<Employee> selectEmpByWhere(Map<String,Object> map);
//Trim标签
List<Employee> selectEmpByTrim(Map<String,Object> map);
//Foreach标签 方式一
List<Employee> selectEmpByForeach1(Map<String,Object> map);
//Foreach标签 方式二
List<Employee> selectEmpByForeach2(List<Integer> idsList);
//Foreach标签 方式三
List<Employee> selectEmpByForeach3(Integer[] idsList);
//set标签
int updateEmpBySet(Employee emp);
//Sql标签
List<Employee> selectEmployeeBySql();
//selectKey标签
int insertEmployee(Employee emp);
}
测试类：
@Test
public void test() {
//使用Map参数查询
// Map<String,Object> mapParam = new HashMap<String,Object>();
// mapParam.put("sex", "男");
// List<Employee> elist = em.selectEmpByMapParam(mapParam);
//使用多个参数查询
// List<Employee> elist = em.selectEmpByMultipleParam("%小%", "男");
//以实体类为参数
// Employee emp = new Employee("%小%","男");
// List<Employee> elist = em.selectEmpByModelParam(emp);
// <choose><when> 分支标签, 只判断其中一个值
// Map<String,Object> mapParam = new HashMap<String,Object>();
// mapParam.put("ename", "%小%");
// mapParam.put("esex", "男");
// List<Employee> elist = em.selectEmpByChoose(mapParam);
//where标签
// Map<String,Object> mapParam = new HashMap<String,Object>();
// mapParam.put("ename", "%小%");
// mapParam.put("address", "%淄博");
// List<Employee> elist = em.selectEmpByWhere(mapParam);
//Trim标签
// Map<String,Object> mapParam = new HashMap<String,Object>();
// mapParam.put("ename", "%小%");
// mapParam.put("address", "%淄博");
// List<Employee> elist = em.selectEmpByTrim(mapParam);
//Foreach标签 方法一
// Map<String,Object> mapParam = new HashMap<String,Object>();
// List<Integer> idsList = new ArrayList<Integer>();
// idsList.add(3);
// idsList.add(6);
// idsList.add(7);
// mapParam.put("idsList", idsList);
// List<Employee> elist = em.selectEmpByTrim(mapParam);
//Foreach标签 方法二
// Map<String,Object> mapParam = new HashMap<String,Object>();
// List<Integer> idsList = new ArrayList<Integer>();
// idsList.add(3);
// idsList.add(6);
// idsList.add(7);
// List<Employee> elist = em.selectEmpByForeach2(idsList);
//Foreach标签 方法三
// Map<String,Object> mapParam = new HashMap<String,Object>();
// Integer[] idsList = {3,6,7};
// List<Employee> elist = em.selectEmpByForeach3(idsList);
//set标签
// Employee emp = new Employee(4, "李丽", "女", new Date(), "山东淄博7", null);
// int a = em.updateEmpBySet(emp);
// System.out.println(a);
//Sql标签
// List<Employee> elist = em.selectEmployeeBySql();
//selectkey标签
Employee emp = new Employee(4, "李丽1111", "女", new Date(), "山东淄博7", null);
int a = em.updateEmpBySet(emp);
System.out.println(a);
// for(Employee e : elist) {
// System.out.println(e);
// }