SSM实现简单的CRUD之DAO层

6 篇文章 0 订阅
5 篇文章 0 订阅
前言

在阅读这篇文章之前如果对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("批量完成");

    }

}

控制台打印结果:
text01()运行结果如下图:
这里写图片描述

testCRUD()运行结果如下图:
这个批量插入数据需要些时间 因为插入的数据量较大

这里写图片描述

到数据库查看插入的数据 如图:

这里写图片描述

好了剩下的方法希望各位老哥能够一个一个的测试

DAO层就编写到这 下一章将介绍service层

SSM整合 http://blog.csdn.net/qq_33524158/article/details/78360268

MyBatis-逆向工程讲解 http://blog.csdn.net/qq_33524158/article/details/78442664

SSM实现简单的CRUD之DAO层 http://blog.csdn.net/qq_33524158/article/details/78442771

SSM实现简单的CRUD之Service层 http://blog.csdn.net/qq_33524158/article/details/78449897

SSM实现简单的CRUD之Web层http://blog.csdn.net/qq_33524158/article/details/78462932

  • 0
    点赞
  • 5
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值