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>