SSMybatis

最新推荐文章于 2024-08-21 23:39:15 发布

原创最新推荐文章于 2024-08-21 23:39:15 发布 · 505 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#mybatis

一、引入依赖配置

(一)、依赖

使用 mysql-connector-java 连接 mysql

<dependency>  
    <groupId>com.alibaba</groupId>  
    <artifactId>druid-spring-boot-starter</artifactId>  
    <version>1.2.20</version>  
</dependency>  
  
<dependency>  
    <groupId>org.mybatis.spring.boot</groupId>  
    <artifactId>mybatis-spring-boot-starter</artifactId>  
    <version>2.2.2</version>  
</dependency>  
  
<dependency>  
    <groupId>mysql</groupId>  
    <artifactId>mysql-connector-java</artifactId>  
    <version>8.0.22</version>  
</dependency>

(二)、配置

server:  
  port: 8089  
  
spring:  
  datasource:  
    type: com.alibaba.druid.pool.DruidDataSource  
    driver-class-name: com.mysql.cj.jdbc.Driver # 空 8.0 cj    url: jdbc:mysql://127.0.0.1/youlai_boot?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&autoReconnect=true&allowMultiQueries=true  
    username: root  
    password: root  
  
mybatis:  
  mapper-locations: classpath:mapper/*.xml  
  type-aliases-package: com.like.system.entity  
  # 驼峰命名可以不用设置 ResultMap  configuration:  
    map-underscore-to-camel-case: true

配置日志logback-spring.xml

<?xml version="1.0" encoding="UTF-8"?>  
<!-- 日志级别从低到高分为TRACE < DEBUG < INFO < WARN < ERROR < FATAL，如果设置为WARN，则低于WARN的信息都不会输出 -->  
<configuration>  
  
    <!-- SpringBoot默认logback的配置 -->  
    <include resource="org/springframework/boot/logging/logback/defaults.xml"/>  
  
    <springProperty scope="context" name="APP_NAME" source="spring.application.name"/>  
    <property name="LOG_HOME" value="/logs/${APP_NAME}"/>  
  
    <!--1. 输出到控制台-->  
    <appender name="CONSOLE" class="ch.qos.logback.core.ConsoleAppender">  
        <!-- <withJansi>true</withJansi>-->  
        <!--此日志appender是为开发使用，只配置最低级别，控制台输出的日志级别是大于或等于此级别的日志信息-->  
        <filter class="ch.qos.logback.classic.filter.ThresholdFilter">  
            <level>DEBUG</level>  
        </filter>        <encoder>            <Pattern>${CONSOLE_LOG_PATTERN}</Pattern>  
            <charset>UTF-8</charset>  
        </encoder>    </appender>  
    <!-- 2. 输出到文件  -->  
    <appender name="FILE" class="ch.qos.logback.core.rolling.RollingFileAppender">  
        <!-- 当前记录的日志文档完整路径 -->  
        <file>${LOG_HOME}/log.log</file>  
        <!--日志文档输出格式-->  
        <encoder>  
            <pattern>%d{yyyy-MM-dd HH:mm:ss.SSS} -%5level ---[%15.15thread] %-40.40logger{39} : %msg%n%n</pattern>  
            <charset>UTF-8</charset> <!-- 此处设置字符集 -->  
        </encoder>  
        <!-- 日志记录器的滚动策略，按日期，按大小记录 -->  
        <rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">  
            <fileNamePattern>${LOG_HOME}/%d{yyyy-MM-dd}.%i.log</fileNamePattern>  
            <timeBasedFileNamingAndTriggeringPolicy class="ch.qos.logback.core.rolling.SizeAndTimeBasedFNATP">  
                <maxFileSize>10MB</maxFileSize>  
            </timeBasedFileNamingAndTriggeringPolicy>            <!--日志文档保留天数-->  
            <maxHistory>15</maxHistory>  
        </rollingPolicy>        <!-- 临界值过滤器，输出大于INFO级别日志 -->  
        <filter class="ch.qos.logback.classic.filter.ThresholdFilter">  
            <level>INFO</level>  
        </filter>    </appender>  
    <!-- 开发环境输出至控制台 -->  
    <springProfile name="dev">  
        <root level="INFO">  
            <appender-ref ref="CONSOLE"/>  
            <appender-ref ref="FILE"/>  
        </root>    </springProfile>  
    <!-- 生产环境输出至文件 -->  
    <springProfile name="prod">  
        <root level="INFO">  
            <appender-ref ref="CONSOLE"/>  
            <appender-ref ref="FILE"/>  
        </root>    </springProfile></configuration>

(三)、mapper.xml

namespace

<?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.like.system.mapper.UserMapper">  
  
</mapper>

二、实现示例

实体类: SysUser

@Data  
public class SysUser {  
    private Integer id;  
    private String username;  
    private String nickname;  
    private Integer gender;  
    private String password;  
    private Integer deptId;  
    private String avatar;  
    private String mobile;  
    private Integer status;  
    private String email;  
    private Integer deleted;  
    private Date createTime;  
    private Date updateTime;  
}

映射器接口: UserMapper

@Mapper
public interface UserMapper {
    List<SysUser> selectUserList(SysUser sysUser);
    List<SysUser> selectUserChoose(SysUser sysUser);
    SysUser selectUserDetail(Integer id);
    int insertUser(SysUser sysUser);
    int updateUser(SysUser sysUser);
    int deleteUser(Integer id);
    int deleteUserBatch(@Param("ids") List<Integer> ids);
}

MyBatis 映射配置文件

<?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.like.system.mapper.UserMapper">  
  
    <!--  
        数据库的 和 实体类的属性名称不一致时，不能自动封装数据  
        需要resultMap: 配置映射关系  
        或者开启驼峰命名    -->  
    <resultMap id="SysUserMap" type="com.like.system.entity.SysUser">  
	    <id column="id" property="id" />  
	    <result column="username" property="username" />  
	    <result column="nickname" property="nickname" />  
	    <result column="email" property="email" />  
	    <result column="mobile" property="mobile" />  
	    <result column="gender" property="gender" />  
	    <result column="avatar" property="avatar" />  
	    <result column="password" property="password" />  
	    <result column="status" property="status" />  
	    <result column="dept_id" property="deptId" />  
	    <result column="create_time" property="createTime" />  
	    <result column="update_time" property="updateTime" />  
	    <result column="deleted" property="deleted" />  
	</resultMap>
	
	<!--通用sql-->  
	<sql id="sysUserSql">  
	    id, username, nickname, email, mobile, gender, avatar, password, status, deleted, create_time, update_time  
	</sql>  
</mapper>

1、查询模块

select 标签属性

id: 标识映射语句的唯一标识。
resultMap: 引用<resultMap>标签定义的映射关系，用于将查询结果映射到实体对象

1-1、多条件查询

<!-- 多条件查询 -->  
<select id="selectUserList" resultMap="SysUserMap">  
    SELECT  
        <include refid="sysUserSql"/>  
    FROM  
        sys_user    
     <where>  
        <!-- 精准查询 -->  
        <if test="status != null">  
            status = #{status}  
        </if>  
        <!-- 模糊查询 -->  
        <if test="username != null and username != ''">  
            AND username LIKE CONCAT('%', #{username}, '%')  
        </if>  
        <if test="nickname != null and nickname != ''">  
            AND nickname LIKE CONCAT('%', #{nickname}, '%')  
        </if>  
        <if test="email != null and email != ''">  
            AND email LIKE CONCAT('%', #{email}, '%')  
        </if>  
        AND deleted = 0  
    </where>  
</select>

1-2、选择条件查询

<!-- 选择条件查询 -->  
<select id="selectUserChoose" resultMap="SysUserMap">  
    SELECT  
        <include refid="sysUserSql"/>  
    FROM  
        sys_user    
    <where>  
        <!-- 精准查询 -->  
        <if test="status != null">  
            status = #{status}  
        </if>  
        <!-- 相当于switch case -->  
        <choose>  
            <when test="username != null and username != ''">  
                AND username LIKE CONCAT('%', #{username}, '%')  
            </when>  
            <when test="nickname != null and nickname != ''">  
                AND nickname LIKE CONCAT('%', #{nickname}, '%')  
            </when>  
            <when test="email != null and email != ''">  
                AND email LIKE CONCAT('%', #{email}, '%')  
            </when>  
            <otherwise>  
                AND deleted = 0  
            </otherwise>  
        </choose>  
    </where>  
</select>

1-3、查询详细信息

<!-- 查询详细信息 -->  
<select id="selectUserDetail" resultMap="SysUserMap">  
    SELECT  
        <include refid="sysUserSql"/>  
    FROM  
        sys_user    
    WHERE        
	    id = #{id}
</select>

2、新增模块

insert 标签属性

id: 标识映射语句的唯一标识。
useGeneratedKeys: 指定是否使用生成的键值（例如自增主键），默认为false。
keyProperty: 指定自动插入成功后，将返回的主键值赋值给实体对象的哪个属性。

trim 标签属性

prefix: 指定在动态SQL语句的开始部分添加的文本。
suffix: 指定在动态SQL语句的结束部分添加的文本。
suffixOverrides: 指定是否覆盖最后一个元素的后缀，通常用于逗号。

示例

    <!-- 新增 -->
    <insert id="insertUser">
        INSERT INTO sys_user
            <trim prefix="(" suffix=")" suffixOverrides=",">
                <if test="username != null">username,</if>
                <if test="nickname != null">nickname,</if>
                <if test="email != null">email,</if>
                <if test="mobile != null">mobile,</if>
                <if test="gender != null">gender,</if>
                <if test="avatar != null">avatar,</if>
                <if test="status != null">status,</if>
                <!--
	                <if test="photoUrl != null">photo_url,</if>
                -->
            </trim>
        VALUES
            <trim prefix="(" suffix=")" suffixOverrides=",">
                <if test="username != null">#{username},</if>
                <if test="nickname != null">#{nickname},</if>
                <if test="email != null">#{email},</if>
                <if test="mobile != null">#{mobile},</if>
                <if test="gender != null">#{gender},</if>
                <if test="avatar != null">#{avatar},</if>
                <if test="status != null">#{status},</if>
	            <!--
	                <if test="photoUrl != null">#{photoUrl},</if>
                -->
            </trim>
    </insert>

3、修改模块

    <!-- 修改 -->
    <update id="updateUser">
        UPDATE sys_user
            <set>
                <if test="username != null">username = #{username},</if>
                <if test="password != null">password = #{password},</if>
                <!--
	                <if test="userId != null and userId != ''">user_id = #{userId},</if>
                -->
            </set>
        WHERE id = #{id}
    </update>

4、删除模块

4-1、软删除

    <!-- 根据id删除 软删除 -->
    <delete id="deleteUser">
        UPDATE sys_user
        SET deleted = 1
        WHERE id = #{id}
    </delete>

    <!-- 批量删除 -->
    <delete id="deleteUserBatch">
        UPDATE sys_user
        SET deleted = 1
        WHERE id IN
        <foreach collection="ids" item="id" separator="," open="(" close=")">
            #{id}
        </foreach>
    </delete>

5-1、硬删除

        <!-- 根据id删除-->
        <delete id="deleteUser">
            DELETE FROM sys_user
            WHERE id = #{id}
        </delete>

		<!-- 批量删除 -->
        <delete id="deleteUserBatch">
            DELETE FROM sys_user
            WHERE id IN
            <foreach collection="ids" item="id" separator="," open="(" close=")">
                #{id}
            </foreach>
        </delete>