MyBatis增删查改

MyBatis.xml文件以及工具类、实体类可以见上篇博客
MyBatis入门简介及XML文件配置

数据源
两个表，分别为staff和role

查询

接口

package mapper;

import domain.Staff;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;

public interface StaffMapper {
    public List<Staff> findAllStaff();
    public Staff findStaffByUid(String uid);
    public List<Staff> findStaffByUname(String uname);
    public List<Staff> findStaffByMap(Map<String,Object> map);
    public List<Staff> findStaffByUpsw(@Param("upsw") String upsw);
}

接口映射文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="mapper.StaffMapper">
    <!--配置结果类型-->
    <resultMap id="stafflist" type="Staff">
        <!--property实体类属性；column表中字段-->
        <result property="uid" column="uid"></result>
        <result property="pid" column="pid"></result>
        <result property="sid" column="sid"></result>
        <result property="uname" column="uname"></result>
        <result property="upsw" column="upsw"></result>
        <result property="urealname" column="urealname"></result>
        <result property="sname" column="sname"></result>
    </resultMap>

    <select id="findAllStaff" resultMap="stafflist">
        SELECT s.*,r.sname FROM staff s,role r WHERE s.sid=r.sid
   </select>

    <select id="findStaffByUid" resultType="Staff" parameterType="String">
        SELECT * FROM staff WHERE uid=#{uid}
    </select>

    <select id="findStaffByUname" resultType="Staff" parameterType="String">
        SELECT * FROM staff WHERE urealname LIKE concat(concat('%',#{urealname}),'%')
    </select>

    <select id="findStaffByMap" resultType="Staff" parameterType="Map">
        SELECT * FROM staff WHERE urealname LIKE concat(concat('%',#{urealname}),'%') AND upsw=#{upsw}
    </select>

    <select id="findStaffByUpsw" resultType="Staff" parameterType="String">
        SELECT * FROM staff WHERE upsw=#{upsw}
    </select>
</mapper>

测试

package test;

import domain.Staff;
import mapper.StaffMapper;
import org.apache.ibatis.session.SqlSession;
import util.MybatisUtil;

import java.util.HashMap;
import java.util.List;

public class Test {
    public static void main(String[] args) {
        //test01();
        //test02();
        //test03();
        //test04();
        test05();
    }
    
    private static void test05() {
        SqlSession session = MybatisUtil.getSession();
        List<Staff> staffByUpsw = session.getMapper(StaffMapper.class).findStaffByUpsw("123");
        for (Staff staff : staffByUpsw) {
            System.out.println(staff.getUrealname());
        }
    }

    private static void test04() {
        SqlSession session = MybatisUtil.getSession();
        HashMap<String, Object> map = new HashMap<String, Object>();
        map.put("urealname","博");
        map.put("upsw","666");
        List<Staff> staffByMap = session.getMapper(StaffMapper.class).findStaffByMap(map);
        for (Staff staff : staffByMap) {
            System.out.println(staff.getUrealname());
        }
    }

    private static void test03() {
        SqlSession session = MybatisUtil.getSession();
        List<Staff> staffByUname = session.getMapper(StaffMapper.class).findStaffByUname("博");
        for (Staff staff : staffByUname) {
            System.out.println(staff.getUrealname());
        }

    }

    private static void test02() {
        SqlSession session = MybatisUtil.getSession();
        Staff staffByUid = session.getMapper(StaffMapper.class).findStaffByUid("100");
        System.out.println(staffByUid.getUrealname());
    }

    private static void test01() {
        SqlSession session = MybatisUtil.getSession();
        List<Staff> allStaff = session.getMapper(StaffMapper.class).findAllStaff();
        for (Staff staff : allStaff) {
            System.out.println(staff.getUrealname()+"\t"+staff.getSname());
        }
    }
}

增、删、改

接口

package mapper;

import domain.Staff;

public interface StaffMapper {
    public int update(Staff uid);
    public int delete(int uid);
    public int insert(Staff id);
}

接口映射文件

	<update id="update" parameterType="Staff">
        UPDATE staff SET pid=#{pid},sid=#{sid},uname=#{uname},upsw=#{upsw},urealname=#{urealname} WHERE uid=#{uid}
    </update>

    <delete id="delete" parameterType="String">
        DELETE FROM staff WHERE uid=#{uid}
    </delete>

    <insert id="insert" parameterType="Staff">
        INSERT INTO staff VALUES(#{uid},#{pid},#{sid},#{uname},#{upsw},#{urealname})
    </insert>

测试

	private static void test08() {
        SqlSession session = MybatisUtil.getSession();
        int i = session.getMapper(StaffMapper.class).insert(new Staff(104, 3, 3, "zining", "123", "紫宁"));
        if (i>0){
            System.out.println("哈哈哈");
        }
        session.commit();
    }

    private static void test07() {
        SqlSession session = MybatisUtil.getSession();
        Staff staffByUid = session.getMapper(StaffMapper.class).findStaffByUid("104");
        if (staffByUid!=null){
            int i = session.getMapper(StaffMapper.class).delete(staffByUid.getUid());
            if (i>0){
                System.out.println("哈哈哈");
            }
        }
        session.commit();
    }

    private static void test06() {
        SqlSession session = MybatisUtil.getSession();
        Staff staffByUid = session.getMapper(StaffMapper.class).findStaffByUid("100");
        staffByUid.setUname("az");
        staffByUid.setUpsw("123");
        int i = session.getMapper(StaffMapper.class).update(staffByUid);
        if (i>0){
            System.out.println("OK");
        }
        session.commit();//提交到数据库
    }