(二)mybatis学习之实现对mySQL数据库的增删查改

mybatis配置数据源请参阅(一)mybatis学习之mybatis-config.xml
创建实体类Empolyee.java

package com.loren.entity;

import org.apache.ibatis.type.Alias;


//@Alias()为实体类指定新的别名
@Alias("emp")
public class Employee {
    private Integer id;
    private String lastName;
    private String email;
    private String gender;



    public Employee() {
        super();
    }
    public Employee(Integer id, String lastName, String email, String gender) {
        super();
        this.id = id;
        this.lastName = lastName;
        this.email = email;
        this.gender = gender;
    }
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getLastName() {
        return lastName;
    }
    public void setLastName(String lastName) {
        this.lastName = lastName;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    public String getGender() {
        return gender;
    }
    public void setGender(String gender) {
        this.gender = gender;
    }
    @Override
    public String toString() {
        return "Employee [id=" + id + ", lastName=" + lastName + ", email=" + email + ", gender=" + gender + "]";
    }

}

创建EmployeeMapper.java接口

package com.loren.dao;

import com.loren.entity.Employee;

public interface EmployeeMapper {
    //查询方法
    public Employee getEmpById(Integer id);

    //添加方法
    public void addEmp(Employee employee);

    //修改方法
    public void updateEmp(Employee employee);

    //删除方法
    public void deleteEmp(Integer id);
}

创建EmployeeMapper.xml文件进行映射

<?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">

<!-- namespace:名称空间(指定为接口的全类名) 
    id:唯一标识
    resultType:返回值类型
    #{id}:从传递过来的参数中取出id值
-->
<mapper namespace="com.loren.dao.EmployeeMapper">
    <!-- databaseId：用来标识sql语句是在那个环境下使用的 -->
    <select id="getEmpById"  resultType="emp" databaseId="mysql">
    SELECT * FROM tb_employee where id = #{id}
    </select>

    <!-- 添加员工 -->
        <!-- 
            parameterType:参数类型，可以省略 
            mysql支持自增主键，mybatis用statement.getGenreatedKeys();
            获取自增主键的值
                useGeneratedKeys="true";使用自增主键获取主键值策略
                keyProperty：指定对应的主键属性，也就是获取到主键值后将其封装给javaBean的哪个属性
        -->
    <insert id="addEmp" parameterType="com.loren.entity.Employee" useGeneratedKeys="true"
    keyProperty="id">
        insert into tb_employee(last_name,email,gender) values(#{lastName},#{email},#{gender})
    </insert>

    <!-- Oracle不支持自增，使用序列来模拟自增的获取 -->
    <!-- <insert id="addEmp" databaseId="oracle">

            keyProperty:查出的主键值封装给javaBean的哪个属性 
            order="BEFORE":当前sql在插入sql之前运行
                   AFTER:当前sql在插入sql之后运行
            resultType="Integer":查出数据的返回值类型
            运行顺序：先运行查询id的sql，查出id封装给bean属性，再运行插入sql

        <selectKey keyProperty="id" order="BEFORE" resultType="Integer">
            编写查询主键的sql语句
            select EMPLOYEE_SEQ.nextval from dual
        </selectKey> 
        插入时的主键是从序列中拿到的
        insert into tb_employee(id,last_name,email,gender) values(#{id},#{lastName},#{email},#{gender})
    </insert> -->
    <!-- 更新员工 -->
    <update id="updateEmp">
        update tb_employee
            set last_name=#{lastName},email=#{email},gender=#{gender}
            where id=#{id}
    </update>

    <!-- 删除员工 -->
    <delete id="deleteEmp">
        delete from tb_employee where id=#{id}
    </delete>
</mapper>