mybatis orm映射框架的两种写法

1、在接口中通过注解的方式进行sql编写

1-1、pom.xml文件引入依赖的jar文件gav

        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.6</version>
        </dependency>

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.0.1</version>
        </dependency>

        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis-spring</artifactId>
            <version>1.3.0</version>
        </dependency>

1-2、接口文件编写

 import com.jmyd.scene.openapi.template.model.Pictures;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.data.repository.query.Param;
import java.util.List;

@Mapper
public interface PicturesMapper {
    @Select("SELECT * FROM  TABLE_NAME")
    List<Pictures> selectSceneListByUserId(@Param("curentPage")Integer curentPage, @Param("pageSize")Integer pageSize, @Param("userId")String userName);
}

2、在xml配置文件中进行sql编写

2-1、pom.xml文件引入依赖的jar文件gav

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.1.2</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-annotation</artifactId>
            <version>3.1.2</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-extension</artifactId>
            <version>3.1.2</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus</artifactId>
            <version>3.1.2</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-generator</artifactId>
            <version>3.1.2</version>
        </dependency>

2-2、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="com.jmyd.xxxxxx.mapper.xxxxxxTableMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.jmyd.xxxxxx.entity.TableEntity">
        <id column="ID" property="id" />
        <result column="CREATOR" property="creator" />
        <result column="IS_DELETE" property="isDelete" />
        <result column="CREATE_DATE" property="createDate" />
        <result column="UPDATE_DATE" property="updateDate" />
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        ID, CREATOR, IS_DELETE, CREATE_DATE, UPDATE_DATE
    </sql>
    <select id="selectTableById" parameterType="com.jmyd.xxxxxx.dto.TableDTO" resultType="com.jmyd.xxxxxx.vo.TableVO">
        SELECT
        <include refid="Base_Column_List"/>
        FROM  xxxxxx_Table WHERE ID=#{id}
    </select>

    <select id="selectTableList" parameterType="com.jmyd.xxxxxx.dto.TableDTO" resultType="com.jmyd.xxxxxx.vo.TableVO">
        SELECT ys.ID,   ys.CREATOR, ys.IS_DELETE, ys.CREATE_DATE, ys.UPDATE_DATE,ys.IMAGE_ID,ys.DESIGN_ID,ysl.id as lookatId
        FROM  xxxxxx_Table ys,xxxxxx_Table_lookat ysl WHERE ys.ID=ysl.hall_id AND  ys.IS_DELETE=1
        <if test="TableName != null">
            AND ys.Table_NAME LIKE concat(concat('%',#{TableName}),'%')
        </if> 
        ORDER BY ys.UPDATE_DATE DESC
        LIMIT #{offset},#{pageSize}
    </select>



    <select id="selectTableListByImageIds" parameterType="java.util.ArrayList" resultType="com.jmyd.xxxxxx.vo.TableVO">
        SELECT
        <include refid="Base_Column_List"/>
        FROM  xxxxxx_Table WHERE IMAGE_ID IN
        <foreach collection="list" item="imageid" separator="," open="(" close=")">
            #{imageid}
        </foreach>
    </select>

    <select id="selectTableByDesignIds" parameterType="java.util.ArrayList" resultType="com.jmyd.xxxxxx.vo.TableVO">
        SELECT
        ys.ID, ys.Table_NAME, ys.REMARK, ys.ICON_URL, ys.CREATOR, ys.IS_DELETE, ys.CREATE_DATE, ys.UPDATE_DATE,
        ys.IMAGE_ID,ys.DESIGN_ID,ysl.id AS lookatId
        FROM  xxxxxx_Table ys,xxxxxx_Table_lookat ysl WHERE ys.ID=ysl.hall_id AND  ys.DESIGN_ID IN
        <foreach collection="list" item="designId" separator="," open="(" close=")">
            #{designId}
        </foreach>
    </select>


    <select id="selectTableCount" parameterType="com.jmyd.xxxxxx.dto.TableDTO" resultType="java.lang.Integer">
        SELECT COUNT(*) FROM  xxxxxx_Table WHERE  IS_DELETE=1
        <if test="TableName != null">
            AND Table_NAME LIKE concat(concat('%',#{TableName}),'%')
        </if> 
    </select>

    <insert id="saveTable" parameterType="com.jmyd.xxxxxx.dto.TableDTO">
        INSERT INTO xxxxxx_Table( Table_NAME,  REMARK, CREATOR,ICON_URL,IMAGE_ID,DESIGN_ID)VALUES
         (#{TableName},#{remark},#{creator},#{iconUrl},#{imageId},#{designId})
    </insert>

    <insert id="saveBatchTable" parameterType="java.util.List" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO xxxxxx_Table( Table_NAME,   CREATOR,ICON_URL,IMAGE_ID,DESIGN_ID) VALUES
        <foreach collection="list" item="Table" separator="," >
            (#{Table.TableName},#{Table.creator},#{Table.iconUrl},#{Table.imageId},#{Table.designId})
        </foreach>
    </insert>

    <update id="updateTable" parameterType="com.jmyd.xxxxxx.dto.TableDTO">
        UPDATE xxxxxx_Table
        <set>
            <if test="iconUrl != null">
                ICON_URL=#{iconUrl},
            </if>
            <if test="remark!= null">
                REMARK=#{remark}
            </if>
        </set>
        WHERE ID=#{id}
    </update>
    <update id="deleteTableById" parameterType="com.jmyd.xxxxxx.dto.TableDTO">
        UPDATE xxxxxx_Table  SET IS_DELETE=0        WHERE ID=#{id}
    </update>

    <delete id="deleteBathTableById" parameterType="java.util.List">
        DELETE FROM xxxxxx_Table  WHERE IMAGE_ID IN
        <foreach collection="list" item="imageid" separator="," open="(" close=")">
            #{imageid}
        </foreach>
    </delete>
</mapper>