MyBatis CRUD实例

1.项目的相关配置文件

(1)mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
 PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
 "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>

    <!-- 
        properties可以引入外部的properties文件
            resource:类路径下的配置文件
            uri:磁盘下的路径
     -->
    <properties resource="jdbc.properties"></properties>

    <!-- 
        settings中有很多重要的设置:
            mapUnderscoreToCamelCase:将数据库中的xx_yy 转化为java程序中的xxYy
     -->
    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

    <!-- 
        typeAliases:别名处理器
            typeAlias:为某一个java的bean取一个别名,默认别名是mployee
            alias:为别名自定义一个别名
     -->
    <typeAliases>
        <!-- <typeAlias type="com.shen.bean.Employee" alias="employee"/> -->
        <!-- 批量取别名 -->
        <package name="com.shen.bean"/>
    </typeAliases>

    <!-- 
        environments:系统环境配置. mybatis可以配置多种环境,default属性指定使用哪一种环境,方便切换
            environment:某一种环境,id是唯一标识
                transactionManager:事务管理器,可以使用JDBC|MANAGED方式;也可以是自定义的,type是自定义类的全路径
                dataSource:数据源
                    type:数据源类型,UNPOOLED(不使用连接池)|POOLED(使用连接池)|JNDI()
                            自定义数据源,可以实现DataSourceFactory接口即可
     -->

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC" />
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}" />
                <property name="url" value="${jdbc.url}" />
                <property name="username" value="${jdbc.username}" />
                <property name="password" value="${jdbc.password}" />
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="EmployeeMapper.xml" />
    </mappers>
</configuration>

(2)jdbc.properties

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mybatis?useSSL=true
jdbc.username=root
jdbc.password=123456

2.CRUD的写法

(1)实体类 Employee.java

package com.shen.bean;
public class Employee {
    private Integer id;
    private String lastName;
    private String gender;
    private String email;

    public Employee(Integer id, String lastName, String gender, String email) {
        super();
        this.id = id;
        this.lastName = lastName;
        this.gender = gender;
        this.email = email;
    }
    public Employee() {
        super();
        // TODO Auto-generated constructor stub
    }
    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 getGender() {
        return gender;
    }
    public void setGender(String gender) {
        this.gender = gender;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    @Override
    public String toString() {
        return "Employee [id=" + id + ", lastName=" + lastName + ", gender="
                + gender + ", email=" + email + "]";
    }
}

(2)数据库表

CREATE TABLE tbl_employee(
id INT(11) PRIMARY KEY AUTO_INCREMENT,
last_name VARCHAR(255),
gender CHAR(1),
email VARCHAR(255)
)

(3) 接口文件 EmployeeMapper.java

package com.shen.dao;

import org.apache.ibatis.annotations.Param;

import com.shen.bean.Employee;

public interface EmployeeMapper {

    //select selectEmpByIdAndLastName
    public Employee selectEmpByIdAndLastName(@Param("id")Integer id,@Param("lastName")String lastName);

    //select
    public Employee getEmployeeById(Integer id);

    //insert
    public Integer insertEmployee(Employee employee);

    //delete
    public Boolean deleteEmployee(Integer id);

    //update 
    public Boolean updateEmployee(Employee employee);
}

(4)映射配置文件 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">
<mapper namespace="com.shen.dao.EmployeeMapper">
<!-- 
    namespace:命名空间
    id:sql语句的唯一标识
    resultType:返回值类型
    #{id}:执行sql传入的参数
 -->

 <!-- 
    传入多个参数的时候,mybatis的处理方式 ,
    我们传入的参数不再是用参数名获取了,而是被mybatis封装成一个map,key值是param1...paramN,value是我们传入的值
    命名参数的写法: 在EmployeeMapper.java文件中,在接口的方法声明中的参数中用@Param("name")命名参数,那么使用的时候,就能够用#{id} #{lastName}
 -->
 <!-- <select id="selectEmpByIdAndLastName" resultType="employee">
    select * from tbl_employee where id=#{param1} and last_name=#{param2}
 </select> -->
 <select id="selectEmpByIdAndLastName" resultType="employee">
    select * from tbl_employee where id=#{id} and last_name=#{lastName}
 </select>

  <!-- EmployeeMapper select -->
  <select id="getEmployeeById" resultType="employee">
    select * from tbl_employee where id=#{id}
  </select>

  <!-- 
    EmployeeMapper insert 
    获取增加的数据的自增的id: useGeneratedKeys="true" keyProperty="id"
  -->
  <insert id="insertEmployee" useGeneratedKeys="true" keyProperty="id">
    insert into tbl_employee(last_name,email,gender) 
    values(#{lastName},#{email},#{gender})
  </insert>

  <!-- EmployeeMapper update -->
  <update id="updateEmployee">
    update tbl_employee set 
    last_name=#{lastName},email=#{email},gender=#{gender}
    where id=#{id}
  </update>

  <!-- EmployeeMapper delete -->
  <delete id="deleteEmployee">
    delete from tbl_employee where 
    id=#{id}
  </delete>
</mapper>

(5)测试类 CRUD.java

package com.shen.handler;

import java.io.IOException;
import java.io.InputStream;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import com.shen.bean.Employee;
import com.shen.dao.EmployeeMapper;

public class CRUD {
    /**
     * 测试曾删改查 
     * @throws IOException 
     */

    public static void main(String[] args) throws IOException {
        System.out.println(selectEmpByIdAndLastName(2,"yu"));
//      System.out.println(selectEmp(2));

        /*Employee employee = new Employee(null,"tom","0","456@qq.com");
        System.out.println(employee);
        insertEmp(employee);*/

        /*Employee emp = selectEmp(3);
        System.out.println(emp);
        updateEmp(emp);*/

        //deleteEmp(3);
    }

    private static SqlSession getMySession() throws IOException{
        String resource ="mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        return sqlSessionFactory.openSession();
    }

    //查询
    public static Employee selectEmp(Integer id) throws IOException {
        SqlSession session = getMySession();
        Employee employee ;
        try{
            EmployeeMapper mapper = session.getMapper(EmployeeMapper.class);
            employee = mapper.getEmployeeById(id);
        }finally{
            session.close();
        }
        return employee;
    }

    //增加
    public static void insertEmp(Employee employee) throws IOException {
        SqlSession session = getMySession();
        try {
            EmployeeMapper mapper = session.getMapper(EmployeeMapper.class);
            Integer ressult = mapper.insertEmployee(employee);
            System.out.println(employee);
            session.commit();
        } finally {
            session.close();
        }
    }

    //修改
    public static void updateEmp(Employee employee) throws IOException {
        SqlSession session = getMySession();
        try{
            EmployeeMapper mapper = session.getMapper(EmployeeMapper.class);
            employee.setLastName("chao");
            Boolean result = mapper.updateEmployee(employee);
            System.out.println(result);
            session.commit();
        }finally{
            session.close();
        }
    }

    //删除
    public static void deleteEmp(Integer id) throws IOException {
        SqlSession session = getMySession();
        try {
            EmployeeMapper mapper = session.getMapper(EmployeeMapper.class);
            Boolean result = mapper.deleteEmployee(id);
            System.out.println(result);
            session.commit();
        } finally {
            session.close();
        }
    }


    /**
     * 当传入多个参数的时候,mybatis的处理方式
     * */
    public static Employee selectEmpByIdAndLastName(Integer id,String lastName) throws IOException {
        SqlSession session = getMySession();
        Employee employee;
        try {
            EmployeeMapper mapper = session.getMapper(EmployeeMapper.class);
            employee = mapper.getEmployeeById(id);
        } finally {
            session.close();
        }
        return employee;
    }
}