工单系统的设计与实现（4）

项目的层次结构清楚之后，首先进行mapper层的编写。关于Mybatis的更多知识，可参见之前的博客。

因为要进行ORM映射，首先准备好pojo，也可以用mybatis的逆向工程生成。

OrderInfo的Pojo如下：

package com.example.demo.bean;


import java.util.Date;

public class OrderInfo {
    /**
     *
     * This field was generated by MyBatis Generator.
     * This field corresponds to the database column order.id
     *
     * @mbg.generated
     */
    private Integer id;

    /**
     *
     * This field was generated by MyBatis Generator.
     * This field corresponds to the database column order.number
     *
     * @mbg.generated
     */
    private String number;

    /**
     *
     * This field was generated by MyBatis Generator.
     * This field corresponds to the database column order.title
     *
     * @mbg.generated
     */
    private String title;

    /**
     *
     * This field was generated by MyBatis Generator.
     * This field corresponds to the database column order.status
     *
     * @mbg.generated
     */
    private Integer status;

    /**
     *
     * This field was generated by MyBatis Generator.
     * This field corresponds to the database column order.priority
     *
     * @mbg.generated
     */
    private Integer priority;

    /**
     *
     * This field was generated by MyBatis Generator.
     * This field corresponds to the database column order.category
     *
     * @mbg.generated
     */
    private Integer category;

    /**
     *
     * This field was generated by MyBatis Generator.
     * This field corresponds to the database column order.userID
     *
     * @mbg.generated
     */
    private Integer userid;

    /**
     *
     * This field was generated by MyBatis Generator.
     * This field corresponds to the database column order.phone
     *
     * @mbg.generated
     */
    private String phone;

    /**
     *
     * This field was generated by MyBatis Generator.
     * This field corresponds to the database column order.email
     *
     * @mbg.generated
     */
    private String email;

    /**
     *
     * This field was generated by MyBatis Generator.
     * This field corresponds to the database column order.attachment
     *
     * @mbg.generated
     */
    private String attachment;

    /**
     *
     * This field was generated by MyBatis Generator.
     * This field corresponds to the database column order.engineerID
     *
     * @mbg.generated
     */
    private Integer engineerid;

    /**
     *
     * This field was generated by MyBatis Generator.
     * This field corresponds to the database column order.createTime
     *
     * @mbg.generated
     */
    private Date createtime;

    /**
     *
     * This field was generated by MyBatis Generator.
     * This field corresponds to the database column order.finishTime
     *
     * @mbg.generated
     */
    private Date finishtime;

    /**
     *
     * This field was generated by MyBatis Generator.
     * This field corresponds to the database column order.content
     *
     * @mbg.generated
     */
    private String content;

    /**
     * This method was generated by MyBatis Generator.
     * This method returns the value of the database column order.id
     *
     * @return the value of order.id
     *
     * @mbg.generated
     */
    public Integer getId() {
        return id;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method sets the value of the database column order.id
     *
     * @param id the value for order.id
     *
     * @mbg.generated
     */
    public void setId(Integer id) {
        this.id = id;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method returns the value of the database column order.number
     *
     * @return the value of order.number
     *
     * @mbg.generated
     */
    public String getNumber() {
        return number;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method sets the value of the database column order.number
     *
     * @param number the value for order.number
     *
     * @mbg.generated
     */
    public void setNumber(String number) {
        this.number = number == null ? null : number.trim();
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method returns the value of the database column order.title
     *
     * @return the value of order.title
     *
     * @mbg.generated
     */
    public String getTitle() {
        return title;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method sets the value of the database column order.title
     *
     * @param title the value for order.title
     *
     * @mbg.generated
     */
    public void setTitle(String title) {
        this.title = title == null ? null : title.trim();
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method returns the value of the database column order.status
     *
     * @return the value of order.status
     *
     * @mbg.generated
     */
    public Integer getStatus() {
        return status;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method sets the value of the database column order.status
     *
     * @param status the value for order.status
     *
     * @mbg.generated
     */
    public void setStatus(Integer status) {
        this.status = status;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method returns the value of the database column order.priority
     *
     * @return the value of order.priority
     *
     * @mbg.generated
     */
    public Integer getPriority() {
        return priority;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method sets the value of the database column order.priority
     *
     * @param priority the value for order.priority
     *
     * @mbg.generated
     */
    public void setPriority(Integer priority) {
        this.priority = priority;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method returns the value of the database column order.category
     *
     * @return the value of order.category
     *
     * @mbg.generated
     */
    public Integer getCategory() {
        return category;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method sets the value of the database column order.category
     *
     * @param category the value for order.category
     *
     * @mbg.generated
     */
    public void setCategory(Integer category) {
        this.category = category;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method returns the value of the database column order.userID
     *
     * @return the value of order.userID
     *
     * @mbg.generated
     */
    public Integer getUserid() {
        return userid;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method sets the value of the database column order.userID
     *
     * @param userid the value for order.userID
     *
     * @mbg.generated
     */
    public void setUserid(Integer userid) {
        this.userid = userid;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method returns the value of the database column order.phone
     *
     * @return the value of order.phone
     *
     * @mbg.generated
     */
    public String getPhone() {
        return phone;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method sets the value of the database column order.phone
     *
     * @param phone the value for order.phone
     *
     * @mbg.generated
     */
    public void setPhone(String phone) {
        this.phone = phone == null ? null : phone.trim();
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method returns the value of the database column order.email
     *
     * @return the value of order.email
     *
     * @mbg.generated
     */
    public String getEmail() {
        return email;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method sets the value of the database column order.email
     *
     * @param email the value for order.email
     *
     * @mbg.generated
     */
    public void setEmail(String email) {
        this.email = email == null ? null : email.trim();
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method returns the value of the database column order.attachment
     *
     * @return the value of order.attachment
     *
     * @mbg.generated
     */
    public String getAttachment() {
        return attachment;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method sets the value of the database column order.attachment
     *
     * @param attachment the value for order.attachment
     *
     * @mbg.generated
     */
    public void setAttachment(String attachment) {
        this.attachment = attachment == null ? null : attachment.trim();
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method returns the value of the database column order.engineerID
     *
     * @return the value of order.engineerID
     *
     * @mbg.generated
     */
    public Integer getEngineerid() {
        return engineerid;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method sets the value of the database column order.engineerID
     *
     * @param engineerid the value for order.engineerID
     *
     * @mbg.generated
     */
    public void setEngineerid(Integer engineerid) {
        this.engineerid = engineerid;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method returns the value of the database column order.createTime
     *
     * @return the value of order.createTime
     *
     * @mbg.generated
     */
    public Date getCreatetime() {
        return createtime;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method sets the value of the database column order.createTime
     *
     * @param createtime the value for order.createTime
     *
     * @mbg.generated
     */
    public void setCreatetime(Date createtime) {
        this.createtime = createtime;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method returns the value of the database column order.finishTime
     *
     * @return the value of order.finishTime
     *
     * @mbg.generated
     */
    public Date getFinishtime() {
        return finishtime;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method sets the value of the database column order.finishTime
     *
     * @param finishtime the value for order.finishTime
     *
     * @mbg.generated
     */
    public void setFinishtime(Date finishtime) {
        this.finishtime = finishtime;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method returns the value of the database column order.content
     *
     * @return the value of order.content
     *
     * @mbg.generated
     */
    public String getContent() {
        return content;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method sets the value of the database column order.content
     *
     * @param content the value for order.content
     *
     * @mbg.generated
     */
    public void setContent(String content) {
        this.content = content == null ? null : content.trim();
    }
}

然后编写OrderMapper.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.edu.ustc.sse.workorder.mapper.OrderMapper">
    <resultMap id="BaseResultMap" type="cn.edu.ustc.sse.workorder.bean.OrderInfo">
        <!--
          WARNING - @mbg.generated
          This element is automatically generated by MyBatis Generator, do not modify.
        -->
        <id column="id" jdbcType="INTEGER" property="id"/>
        <result column="number" jdbcType="VARCHAR" property="number"/>
        <result column="title" jdbcType="VARCHAR" property="title"/>
        <result column="status" jdbcType="INTEGER" property="status"/>
        <result column="priority" jdbcType="INTEGER" property="priority"/>
        <result column="category" jdbcType="INTEGER" property="category"/>
        <result column="userID" jdbcType="INTEGER" property="userid"/>
        <result column="phone" jdbcType="VARCHAR" property="phone"/>
        <result column="email" jdbcType="VARCHAR" property="email"/>
        <result column="attachment" jdbcType="VARCHAR" property="attachment"/>
        <result column="engineerID" jdbcType="INTEGER" property="engineerid"/>
        <result column="createTime" jdbcType="TIMESTAMP" property="createtime"/>
        <result column="finishTime" jdbcType="TIMESTAMP" property="finishtime"/>
    </resultMap>
    <resultMap extends="BaseResultMap" id="ResultMapWithBLOBs" type="cn.edu.ustc.sse.workorder.bean.OrderInfo">
        <!--
          WARNING - @mbg.generated
          This element is automatically generated by MyBatis Generator, do not modify.
        -->
        <result column="content" jdbcType="LONGVARCHAR" property="content"/>
    </resultMap>

    <sql id="Base_Column_List">
        <!--
          WARNING - @mbg.generated
          This element is automatically generated by MyBatis Generator, do not modify.
        -->
        id, number, title, status, priority, category, userID, phone, email, attachment,
        engineerID, createTime, finishTime
    </sql>

    <sql id="Blob_Column_List">
        <!--
          WARNING - @mbg.generated
          This element is automatically generated by MyBatis Generator, do not modify.
        -->
        content
    </sql>

    <!--根据id查找工单全部信息-->
    <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="ResultMapWithBLOBs">
        select
        <include refid="Base_Column_List"/>
        ,
        <include refid="Blob_Column_List"/>
        from OrderInfo
        where id = #{id,jdbcType=INTEGER}
    </select>

    <select id="selectAllOrders" resultType="cn.edu.ustc.sse.workorder.bean.OrderInfo">
        select *
        from OrderInfo
    </select>

    <!--使用工单编号，创建时间，关键字查询工单-->
    <select id="selectByKeywords" parameterType="cn.edu.ustc.sse.workorder.bean.OrderInfoCustom"
            resultType="cn.edu.ustc.sse.workorder.bean.OrderInfo">
        select * from OrderInfo
        <where>
            <if test="userID!=null and userID!='' ">
                and userID = #{userID}
            </if>
            <if test="number!=null and number!='' ">
                and number = #{number}
            </if>
            <if test="startTime!=null  and endTime!=null  ">
                and createTime between #{startTime} and #{endTime}
            </if>
            <if test="keyword!=null and keyword!='' ">
                and (title like concat('%' , #{keyword} ,'%') or content like concat('%' , #{keyword} ,'%'))
            </if>
        </where>
    </select>

    <select id="selectByUserID" parameterType="java.lang.Integer" resultType="cn.edu.ustc.sse.workorder.bean.OrderInfo">
        select *
        from OrderInfo
        where userID = #{userID}
    </select>
    <!--根据id删除工单信息-->
    <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
        <!--
          WARNING - @mbg.generated
          This element is automatically generated by MyBatis Generator, do not modify.
        -->
        delete from OrderInfo
        where id = #{id,jdbcType=INTEGER}
    </delete>

    <!--插入一条工单信息-->
    <insert id="insert" parameterType="cn.edu.ustc.sse.workorder.bean.OrderInfo">
        <!--
          WARNING - @mbg.generated
          This element is automatically generated by MyBatis Generator, do not modify.
        -->
        insert into OrderInfo (number, title,
        status, priority, category,
        userID, phone, email,
        attachment, engineerID, createTime,
        finishTime, content)
        values (#{number,jdbcType=VARCHAR}, #{title,jdbcType=VARCHAR},
        #{status,jdbcType=INTEGER}, #{priority,jdbcType=INTEGER}, #{category,jdbcType=INTEGER},
        #{userid,jdbcType=INTEGER}, #{phone,jdbcType=VARCHAR}, #{email,jdbcType=VARCHAR},
        #{attachment,jdbcType=VARCHAR}, #{engineerid,jdbcType=INTEGER}, #{createtime,jdbcType=TIMESTAMP},
        #{finishtime,jdbcType=TIMESTAMP}, #{content,jdbcType=LONGVARCHAR})
    </insert>

    <!--根据id更新一条工单的信息-->
    <update id="update" parameterType="cn.edu.ustc.sse.workorder.bean.OrderInfo">
        <!--
          WARNING - @mbg.generated
          This element is automatically generated by MyBatis Generator, do not modify.
        -->
        update OrderInfo
        <set>
            <if test="number != null">
                number = #{number,jdbcType=VARCHAR},
            </if>
            <if test="title != null">
                title = #{title,jdbcType=VARCHAR},
            </if>
            <if test="status != null">
                status = #{status,jdbcType=INTEGER},
            </if>
            <if test="priority != null">
                priority = #{priority,jdbcType=INTEGER},
            </if>
            <if test="category != null">
                category = #{ategory,jdbcType=INTEGER},
            </if>
            <if test="userid != null">
                userID = #{userid,jdbcType=INTEGER},
            </if>
            <if test="phone != null">
                phone = #{phone,jdbcType=VARCHAR},
            </if>
            <if test="email != null">
                email = #{email,jdbcType=VARCHAR},
            </if>
            <if test="attachment != null">
                attachment = #{attachment,jdbcType=VARCHAR},
            </if>
            <if test="engineerid != null">
                engineerID = #{engineerid,jdbcType=INTEGER},
            </if>
            <if test="createtime != null">
                createTime = #{createtime,jdbcType=TIMESTAMP},
            </if>
            <if test="finishtime != null">
                finishTime = #{finishtime,jdbcType=TIMESTAMP},
            </if>
            <if test="content != null">
                content = #{content,jdbcType=LONGVARCHAR},
            </if>
        </set>
        where id = #{id,jdbcType=INTEGER}
    </update>

    <!--更新工单的状态  频繁操作  所以单独一个接口-->
    <update id="updateStatus" parameterType="cn.edu.ustc.sse.workorder.bean.OrderInfo">
        update OrderInfo set status=#{status,jdbcType=INTEGER} where id= #{id}
    </update>


</mapper>

然后编写OrderMapper.java接口文件，名字要与xml对应文件的名字相同，这样开启包扫描的时候能够检测到，并能够自动产生实现类。  接口中方法的名字就是xml文件中SQL语句的id名称。

package com.example.demo.mapper;

import com.example.demo.bean.OrderInfo;

import java.util.List;

public interface OrderMapper {
    public OrderInfo selectByPrimaryKey(int id);

    public List<OrderInfo> selectAllOrders();

    public int deleteByPrimaryKey(int id);

    public int insert(OrderInfo order);

    public int update(OrderInfo order);

   // public List<OrderInfo> selectByKeywords(OrderInfoCustom orderInfoCustom);

    public List<OrderInfo> selectByUserID(int id);

    public int updateStatus(OrderInfo orderInfo);
}

 进行完这两步后，还需要编写mybatis的主配置文件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>

    <plugins>
        <plugin interceptor="com.github.pagehelper.PageInterceptor">
            <!-- 设置数据库类型 Oracle,Mysql,MariaDB,SQLite,Hsqldb,PostgreSQL六种数据库-->
            <property name="helperDialect" value="mysql"/>
        </plugin>
    </plugins>

    <mappers>

    </mappers>



</configuration>

然后在application.properties文件中配置数据库的连接：

spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/workorder?serverTimezone=UTC&characterEncoding=utf-8
spring.datasource.username=root
spring.datasource.password=Test1234_
mybatis.config-location=classpath:/mybatis/mybatis_config.xml

 

最后在SpringBoot项目的主入口文件上开启mapperscan自动扫描。当然也可以在其他的配置类上加上mapperscan注解。

package com.example.demo;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@MapperScan("com.example.demo.mapper")
@SpringBootApplication
public class DemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(DemoApplication.class, args);
    }

}

 

至此，mapper层的编写工作已经完成。至于其他的接口实现方式类似，就不一一演示。