SSM实现简单的CRUD之DAO层

来源：互联网发布：linux进入vim 编辑：程序博客网时间：2024/05/22 02:00

前言

在阅读这篇文章之前如果对SSM整合不了解的同学可以先看一下我的这篇文章http://blog.csdn.net/qq_33524158/article/details/78360268

对MyBatis-逆向工程不了解的可以看我这篇文章http://blog.csdn.net/qq_33524158/article/details/78442664

本篇文章CRUD之DAO层的sql 也是用MyBatis-逆向工程生成的，但是我也在生成的基础上进行了更改。

逆向工程生成的源代码我已经发布到github上了https://github.com/hfbin/MyBatis-Benerator 所有生成代码没改动过（最好都看一下生成的源代码后面我只会附上我改动的代码，不在生成的源码与改动模块的代码进行讲解）

创建数据库

注意：如果你已经看了MyBatis-逆向工程的这篇文章了下面的数据库就不要再创建啦！！！

创建数据库

CREATE SCHEMA `ssm_crud` ;

创建部门表

CREATE TABLE `tbl_dept` (  `dept_id` int(11) NOT NULL AUTO_INCREMENT,  `dept_name` varchar(45) DEFAULT NULL,  PRIMARY KEY (`dept_id`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

初始化数据

INSERT INTO `tbl_dept` VALUES (1,'运营部'),(2,'技术部'),(3,'运维部'),(4,'测试部');

创建员工表

CREATE TABLE `tbl_emp` (  `emp_id` int(11) NOT NULL AUTO_INCREMENT,  `emp_name` varchar(225) DEFAULT NULL,  `gender` char(1) DEFAULT NULL,  `email` varchar(45) DEFAULT NULL,  `d_id` int(11) NOT NULL,  PRIMARY KEY (`emp_id`),  KEY `fk_emp_dept_idx` (`d_id`),  CONSTRAINT `fk_emp_dept` FOREIGN KEY (`d_id`) REFERENCES `tbl_dept` (`dept_id`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE=InnoDB AUTO_INCREMENT=2610 DEFAULT CHARSET=utf8;

注意：这里创建了一个外键d_id与tbl_emp的dept_id关联起来
AUTO_INCREMENT=2610 代表自增的初始值

这里初始化数据在后面教用一个批量插入

到这数据库创建完成

MyBatis-逆向工程

在这不做说明具体看前言

在这并不建议使用MyBatis-逆向工程，因为使用得到的sql语句不算多，但是由于上一章介绍了MyBatis-逆向工程，使用到的表也是一样，那就凑合用，下面一节主要介绍代码修改

MyBatis-逆向工程生成的代码更改

mapper xml 中 sql 修改

DepartmentMapper.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="cn.hfbin.crud.dao.DepartmentMapper">  <resultMap id="BaseResultMap" type="cn.hfbin.crud.bean.Department">    <id column="dept_id" jdbcType="INTEGER" property="deptId" />    <result column="dept_name" jdbcType="VARCHAR" property="deptName" />  </resultMap>  <sql id="Example_Where_Clause">    <where>      <foreach collection="oredCriteria" item="criteria" separator="or">        <if test="criteria.valid">          <trim prefix="(" prefixOverrides="and" suffix=")">            <foreach collection="criteria.criteria" item="criterion">              <choose>                <when test="criterion.noValue">                  and ${criterion.condition}                </when>                <when test="criterion.singleValue">                  and ${criterion.condition} #{criterion.value}                </when>                <when test="criterion.betweenValue">                  and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}                </when>                <when test="criterion.listValue">                  and ${criterion.condition}                  <foreach close=")" collection="criterion.value" item="listItem" open="(" separator=",">                    #{listItem}                  </foreach>                </when>              </choose>            </foreach>          </trim>        </if>      </foreach>    </where>  </sql>  <sql id="Base_Column_List">    dept_id, dept_name  </sql>  <select id="selectByExample" parameterType="cn.hfbin.crud.bean.DepartmentExample" resultMap="BaseResultMap">    select    <if test="distinct">      distinct    </if>    <include refid="Base_Column_List" />    from tbl_dept    <if test="_parameter != null">      <include refid="Example_Where_Clause" />    </if>    <if test="orderByClause != null">      order by ${orderByClause}    </if>  </select></mapper>

EmployeeMapper.xml

注意：这里我自己添加了多一个带部门的查询 sql语句也不难使用左外链接

<?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="cn.hfbin.crud.dao.EmployeeMapper">  <resultMap id="BaseResultMap" type="cn.hfbin.crud.bean.Employee">    <id column="emp_id" jdbcType="INTEGER" property="empId" />    <result column="emp_name" jdbcType="VARCHAR" property="empName" />    <result column="gender" jdbcType="CHAR" property="gender" />    <result column="email" jdbcType="VARCHAR" property="email" />    <result column="d_id" jdbcType="INTEGER" property="dId" />  </resultMap>  <resultMap type="cn.hfbin.crud.bean.Employee" id="WithDeptResultMap">    <id column="emp_id" jdbcType="INTEGER" property="empId" />    <result column="emp_name" jdbcType="VARCHAR" property="empName" />    <result column="gender" jdbcType="CHAR" property="gender" />    <result column="email" jdbcType="VARCHAR" property="email" />    <result column="d_id" jdbcType="INTEGER" property="dId" />    <!-- 指定联合查询出的部门字段的封装 -->    <association property="department" javaType="cn.hfbin.crud.bean.Department">        <id column="dept_id" property="deptId"/>        <result column="dept_name" property="deptName"/>    </association>  </resultMap>  <sql id="Example_Where_Clause">    <where>      <foreach collection="oredCriteria" item="criteria" separator="or">        <if test="criteria.valid">          <trim prefix="(" prefixOverrides="and" suffix=")">            <foreach collection="criteria.criteria" item="criterion">              <choose>                <when test="criterion.noValue">                  and ${criterion.condition}                </when>                <when test="criterion.singleValue">                  and ${criterion.condition} #{criterion.value}                </when>                <when test="criterion.betweenValue">                  and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}                </when>                <when test="criterion.listValue">                  and ${criterion.condition}                  <foreach close=")" collection="criterion.value" item="listItem" open="(" separator=",">                    #{listItem}                  </foreach>                </when>              </choose>            </foreach>          </trim>        </if>      </foreach>    </where>  </sql>  <sql id="Update_By_Example_Where_Clause">    <where>      <foreach collection="example.oredCriteria" item="criteria" separator="or">        <if test="criteria.valid">          <trim prefix="(" prefixOverrides="and" suffix=")">            <foreach collection="criteria.criteria" item="criterion">              <choose>                <when test="criterion.noValue">                  and ${criterion.condition}                </when>                <when test="criterion.singleValue">                  and ${criterion.condition} #{criterion.value}                </when>                <when test="criterion.betweenValue">                  and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}                </when>                <when test="criterion.listValue">                  and ${criterion.condition}                  <foreach close=")" collection="criterion.value" item="listItem" open="(" separator=",">                    #{listItem}                  </foreach>                </when>              </choose>            </foreach>          </trim>        </if>      </foreach>    </where>  </sql>  <sql id="Base_Column_List">    emp_id, emp_name, gender, email, d_id  </sql>  <sql id="WithDept_Column_List">    e.emp_id, e.emp_name, e.gender, e.email, e.d_id,d.dept_id,d.dept_name  </sql>   <!-- 查询员工同时带部门信息 -->  <select id="selectByExampleWithDept" resultMap="WithDeptResultMap">       select        <if test="distinct">          distinct        </if>        <include refid="WithDept_Column_List" />        FROM tbl_emp e        left join tbl_dept d on e.`d_id`=d.`dept_id`        <if test="_parameter != null">          <include refid="Example_Where_Clause" />        </if>        order by e.emp_id       <!--  <if test="orderByClause != null">          order by ${orderByClause}        </if> -->  </select>  <!-- 查询员工不带部门信息的 -->  <select id="selectByExample" parameterType="cn.hfbin.crud.bean.EmployeeExample" resultMap="BaseResultMap">    select    <if test="distinct">      distinct    </if>    <include refid="Base_Column_List" />    from tbl_emp    <if test="_parameter != null">      <include refid="Example_Where_Clause" />    </if>    <if test="orderByClause != null">      order by ${orderByClause}    </if>  </select>  <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">    select     <include refid="Base_Column_List" />    from tbl_emp    where emp_id = #{empId,jdbcType=INTEGER}  </select>  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">    delete from tbl_emp    where emp_id = #{empId,jdbcType=INTEGER}  </delete>  <delete id="deleteByExample" parameterType="cn.hfbin.crud.bean.EmployeeExample">    delete from tbl_emp    <if test="_parameter != null">      <include refid="Example_Where_Clause" />    </if>  </delete>  <insert id="insertSelective" parameterType="cn.hfbin.crud.bean.Employee">    insert into tbl_emp    <trim prefix="(" suffix=")" suffixOverrides=",">      <if test="empId != null">        emp_id,      </if>      <if test="empName != null">        emp_name,      </if>      <if test="gender != null">        gender,      </if>      <if test="email != null">        email,      </if>      <if test="dId != null">        d_id,      </if>    </trim>    <trim prefix="values (" suffix=")" suffixOverrides=",">      <if test="empId != null">        #{empId,jdbcType=INTEGER},      </if>      <if test="empName != null">        #{empName,jdbcType=VARCHAR},      </if>      <if test="gender != null">        #{gender,jdbcType=CHAR},      </if>      <if test="email != null">        #{email,jdbcType=VARCHAR},      </if>      <if test="dId != null">        #{dId,jdbcType=INTEGER},      </if>    </trim>  </insert>  <select id="countByExample" parameterType="cn.hfbin.crud.bean.EmployeeExample" resultType="java.lang.Long">    select count(*) from tbl_emp    <if test="_parameter != null">      <include refid="Example_Where_Clause" />    </if>  </select>  <update id="updateByPrimaryKeySelective" parameterType="cn.hfbin.crud.bean.Employee">    update tbl_emp    <set>      <if test="empName != null">        emp_name = #{empName,jdbcType=VARCHAR},      </if>      <if test="gender != null">        gender = #{gender,jdbcType=CHAR},      </if>      <if test="email != null">        email = #{email,jdbcType=VARCHAR},      </if>      <if test="dId != null">        d_id = #{dId,jdbcType=INTEGER},      </if>    </set>    where emp_id = #{empId,jdbcType=INTEGER}  </update></mapper>

dao接口修改

EmployeeMapper.java

package cn.hfbin.crud.dao;import cn.hfbin.crud.bean.Employee;import cn.hfbin.crud.bean.EmployeeExample;import org.apache.ibatis.annotations.Param;import java.util.List;public interface EmployeeMapper {    /**     * 检验用户名是否可用     */    long countByExample(EmployeeExample example);    /**     * 批量删除员工     */    int deleteByExample(EmployeeExample example);    /**     * 员工删除     */    int deleteByPrimaryKey(Integer empId);    /**     * 员工保存     */    int insertSelective(Employee record);    /**     * 按照员工id查询员工     */    Employee selectByPrimaryKey(Integer empId);    /**     * 查询所有员工     */    List<Employee> selectByExampleWithDept(EmployeeExample example);    /**     * 员工更新     */    int updateByPrimaryKeySelective(Employee record);}

DepartmentMapper.java

package cn.hfbin.crud.dao;import cn.hfbin.crud.bean.Department;import cn.hfbin.crud.bean.DepartmentExample;import java.util.List;public interface DepartmentMapper {    //所有部门    List<Department> selectByExample(DepartmentExample example);}

bean 修改

Employee.java

package cn.hfbin.crud.bean;public class Employee {    private Integer empId;    private String empName;    private String gender;    private String email;    private Integer dId;    //查询员工的同时部门信息也是查询好的    private Department department;    @Override    public String toString() {        return "Employee [empId=" + empId + ", empName=" + empName                + ", gender=" + gender + ", email=" + email + ", dId=" + dId                + "]";    }    public Employee() {        super();    }    public Employee(Integer empId, String empName, String gender, String email,            Integer dId) {        super();        this.empId = empId;        this.empName = empName;        this.gender = gender;        this.email = email;        this.dId = dId;    }    public Department getDepartment() {        return department;    }    public void setDepartment(Department department) {        this.department = department;    }    public Integer getEmpId() {        return empId;    }    public void setEmpId(Integer empId) {        this.empId = empId;    }    public String getEmpName() {        return empName;    }    public void setEmpName(String empName) {        this.empName = empName == null ? null : empName.trim();    }    public String getGender() {        return gender;    }    public void setGender(String gender) {        this.gender = gender == null ? null : gender.trim();    }    public String getEmail() {        return email;    }    public void setEmail(String email) {        this.email = email == null ? null : email.trim();    }    public Integer getdId() {        return dId;    }    public void setdId(Integer dId) {        this.dId = dId;    }}

好修改就那么多
其它没有修改的默认不动

测试

在这只测试所有部门跟批量插入，原则上是需要所有的service里面的方法都要进行测试的

package cn.hfbin.crud.test;import cn.hfbin.crud.bean.Employee;import cn.hfbin.crud.dao.DepartmentMapper;import cn.hfbin.crud.dao.EmployeeMapper;import org.apache.ibatis.session.SqlSession;import org.junit.Test;import org.junit.runner.RunWith;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.test.context.ContextConfiguration;import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;import java.util.UUID;/** *使用Spring的单元测试，可以自动注入我们需要的组件 *1、导入SpringTest模块 *2、@ContextConfiguration指定Spring配置文件的位置 *3、直接autowired要使用的组件即可 */@RunWith(SpringJUnit4ClassRunner.class)@ContextConfiguration(locations={"classpath:applicationContext.xml"})public class MapperTest {    @Autowired    DepartmentMapper departmentMapper;    @Autowired    EmployeeMapper employeeMapper;    @Autowired    SqlSession sqlSession;    /**     * 获取所有部门     */    @Test    public void text01(){        System.out.println("所有部门 "+departmentMapper.selectByExample(null));    }    /**     * 批量插入     * 为了插入不同的性别 跟部门我做了四个不同的循环     */    @Test    public void testCRUD(){    EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);        for(int i = 0;i<200;i++){            String uid = UUID.randomUUID().toString().substring(0,5)+i;            mapper.insertSelective(new Employee(null,uid, "f", uid+"@hfbin.com", 1));        }        for(int i = 0;i<200;i++){            String uid = UUID.randomUUID().toString().substring(0,5)+i;            mapper.insertSelective(new Employee(null,uid, "M", uid+"@hfbin.com", 2));        }        for(int i = 0;i<200;i++){            String uid = UUID.randomUUID().toString().substring(0,5)+i;            mapper.insertSelective(new Employee(null,uid, "f", uid+"@hfbin.com", 3));        }        for(int i = 0;i<200;i++){            String uid = UUID.randomUUID().toString().substring(0,5)+i;            mapper.insertSelective(new Employee(null,uid, "M", uid+"@hfbin.com", 4));        }        System.out.println("批量完成");    }}