Mybatis 单表 常用增删改查

1.编写sql表，插入原始数据

 -- 删除表

 DROP TABLE testA;

 -- 创建表

 CREATE TABLE testA(

     id INT AUTO_INCREMENT PRIMARY KEY,

     username VARCHAR(50),

     date1 DATETIME,

     str_date VARCHAR(50),

     date_str DATE

 )

 -- 插入数据

 INSERT INTO testA(username,date1,str_date,date_str) VALUES('夕阳游子',NOW(),DATE_FORMAT(SYSDATE(),'%Y-%m-%d %h:%i:%s'),STR_TO_DATE('2018-12-26','%Y-%m-%d %h:%i:%s'));

2.编写实体类 TestA.java

package com.jtfr.pojo;

import java.util.Date;

public class TestA {

    private int id;

    private String username;

    private Date date;

    private String strDate;

    private Date dateStr;

    public int getId() {

        return id;

    }

    public void setId(int id) {

        this.id = id;

    }

    public String getUsername() {

        return username;

    }

    public void setUsername(String username) {

        this.username = username;

    }

    public Date getDate() {

        return date;

    }

    public void setDate(Date date) {

        this.date = date;

    }

    public String getStrDate() {

        return strDate;

    }

    public void setStrDate(String strDate) {

        this.strDate = strDate;

    }

    public Date getDateStr() {

        return dateStr;

    }

    public void setDateStr(Date dateStr) {

        this.dateStr = dateStr;

    }

    @Override

    public String toString() {

        return "TestA [id=" + id + ", username=" + username + ", date=" + date

                + ", strDate=" + strDate + ", dateStr=" + dateStr + "]";

    }

}

3.编写Dao层 TestADao.java

 package com.jtfr.mapper;

 import java.util.List;

 import com.jtfr.pojo.TestA;

 public interface TestADao {

     void insertTestA(TestA testA);

     void updateTestA(TestA testA);

     void deleteTestA(TestA testA);

     List<TestA> findAll();

 }

4.编写Dao层对应的 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必需是接口的全路径名 -->

<mapper namespace="com.jtfr.mapper.TestADao">

    <resultMap id="TestA" type="com.jtfr.pojo.TestA"></resultMap>

    <sql id="query_sql">

        SELECT

            t.`id`,

            t.`username`,

            t.`date1` date,

            DATE_FORMAT(t.`str_date`,'%Y-%m-%d %h:%i:%s') strDate,

            STR_TO_DATE(t.`date_str`, '%Y-%m-%d') dateStr

        FROM testA t;

    </sql>

    <!-- 批量添加数据 -->

    <!-- <insert id="insertUserList" parameterType="list">

        INSERT INTO usertable VALUES

        <foreach collection="list" item="user" index="0" separator=","  >

            ( #{id}, #{username}, #{passwd})

        </foreach>

    </insert> -->

    <insert id="insertTestA">

        INSERT INTO testA

            (

            username

            <if test="date != null">

                ,date1

            </if>

            <if test="strDate != null">

                ,str_date

            </if>

            <if test="dateStr != null">

                ,date_str

            </if>

            )

        VALUES

            (

            #{username, jdbcType=VARCHAR}

            <if test="date != null">

                ,#{date, jdbcType=TIMESTAMP}

            </if>

            <if test="strDate != null">

                ,DATE_FORMAT(#{dateStr}, '%Y-%m-%d %h:%i:%s')

            </if>

            <if test="dateStr != null">

                ,STR_TO_DATE(#{strDate}, '%Y-%m-%d %h:%i:%s')

            </if>

            )

    </insert>

    <update id="updateTestA">

        UPDATE testA t SET

            t.username=#{username}

            <if test="date != null">

                ,t.date1=#{date}

            </if>

            <if test="strDate != null">

                ,t.str_date=DATE_FORMAT(#{dateStr},'%Y-%m-%d %h:%i:%s')

            </if>

            <if test="dateStr != null">

                ,t.date_str=STR_TO_DATE(#{strDate},'%Y-%m-%d %h:%i:%s')

            </if>

        WHERE t.id=#{id};

    </update>

    <delete id="deleteTestA">

        <!-- 注意不能写成 testA t -->

        DELETE FROM testA WHERE username=#{username};

    </delete>

    <select id="findAll" resultMap="TestA">

        <include refid="query_sql"></include>

    </select>

</mapper>

5.项目源码，Maven结构

下载地址：https://files.cnblogs.com/files/jtfr/MybatisMapperDemo.zip