java练习生

一、添加依赖

添加mybatis依赖

        <!-- mybatis begin-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.1.2</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
            <version>3.0.0</version>
        </dependency>
        <!-- mybatis end-->

添加数据库驱动依赖

mysql:

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

mssql:

        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>mssql-jdbc</artifactId>
            <version>7.2.1.jre11</version>
        </dependency>

二、添加配置

spring:
  datasource:
    dynamic:
      primary: sys
      datasource:
        sys:
          driver-class-name: ${mysql.driver.class.name}
          url: ${mysql.url}
          username: ${mysql.username}
          password: ${mysql.password}
          type: com.zaxxer.hikari.HikariDataSource
          hikari:
            auto-commit: true
            connection-test-query: SELECT 1
            connection-timeout: 3000
            idle-timeout: 30000
            max-lifetime: 30000
            maximum-pool-size: 10
            minimum-idle: 5
            pool-name: MyHikariCP
        loan:
          driver-class-name: ${mssql-driver-class-name}
          url: ${db-loan-url}
          username: ${db-loan-username}
          password: ${db-loan-password}
          type: com.zaxxer.hikari.HikariDataSource
          hikari:
            auto-commit: true
            connection-test-query: SELECT 1
            connection-timeout: 3000
            idle-timeout: 30000
            max-lifetime: 30000
            maximum-pool-size: 10
            minimum-idle: 5
            pool-name: MyHikariCP

primary:设置默认的数据源。

datasource:可以配置多个数据源，在实体映射中通过@DS("loan")注解指定。

三、创建实体对象和实体映射

实体对象

import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import lombok.Data;

import java.time.LocalDateTime;

/**
 * 基础对象
 */
@Data
public class BasePO {
    /**
     * 主键
     */
    @TableId(value = "id")
    private Long id;

    /**
     * 创建时间
     */
    @TableField("created_time")
    private LocalDateTime createdTime;

    /**
     * 修改时间
     */
    @TableField("updated_time")
    private LocalDateTime updatedTime;

    /**
     * 是否删除
     */
    @TableField(value = "deleted")
    private Integer deleted;
}

import com.baomidou.mybatisplus.annotation.FieldStrategy;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
import lombok.experimental.Accessors;

import java.time.LocalDateTime;

/**
 * 流程同步表
 */
@Data
@Accessors(chain = true)
@TableName(value = "t_process_sync")
public class ProcessSyncPO extends BasePO {
    /**
     * 产品种类
     */
    @TableField("loan_kind")
    private String loanKind;
    /**
     * 放款方key
     */
    @TableField("loan_side")
    private String loanSide;
    /**
     * 流程Id
     */
    @TableField("process_id")
    private Long processId;
    /**
     * 外部编号
     */
    @TableField("external_id")
    private String externalId;
    /**
     * 步骤
     */
    @TableField("step")
    private Integer step;
    /**
     * 是否挂起：1 挂起，0 正常
     */
    @TableField("is_hangup")
    private Integer isHangup;
    /**
     * 是否初始化完成
     */
    @TableField("is_init")
    private Integer isInit;
    /**
     * 上步同步失败的时间（成功后置为空）
     */
    @TableField(value = "last_failure_time", updateStrategy = FieldStrategy.IGNORED)
    private LocalDateTime lastFailureTime;
    /**
     * 上步同步成功的时间
     */
    @TableField(value = "last_success_time", updateStrategy = FieldStrategy.IGNORED)
    private LocalDateTime lastSuccessTime;
    /**
     * 备注
     */
    @TableField("remark")
    private String remark;
    /**
     * 操作次数
     */
    @TableField("handle_count")
    private Integer handleCount;

    public static ProcessSyncPO initProcessSync(){
        return new ProcessSyncPO().setIsHangup(0).setIsInit(0).setHandleCount(0).setStep(0);
    }
}

实体映射

import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;

/**
 * sys数据库基础映射
 *
 * @author huangzhongqing
 * @date 2020-12-04
 */
@DS("sys")
public interface SysBaseMapper<T> extends BaseMapper<T> {
}

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface ProcessSyncMapper extends SysBaseMapper<ProcessSyncPO> {
    /**
     * 查询当前流程待初始化的订单列表
     * @param processId
     * @param step
     * @param handleCount
     * @return
     */
    @Select({"select * from t_process_sync where process_id=${processId} and step=${step} and is_hangup=0 and is_init=0 and handle_count<=${handleCount} and deleted=0"})
    List<ProcessSyncPO> getProcessInitList(@Param("processId") Long processId, @Param("step") Integer step, @Param("handleCount") Integer handleCount);

    /**
     * 查询当前流程待同步的订单列表
     * @param processId
     * @param step
     * @param handleCount
     * @param queryCount
     * @return
     */
    List<ProcessSyncPO> getProcessSyncList(@Param("processId") Long processId, @Param("step") Integer step, @Param("handleCount") Integer handleCount, @Param("queryCount") Integer queryCount, @Param("limitTime") String limitTime);

    /**
     * 批量新增 t_process_sync记录
     *
     * @param list
     */
    void insertProcessSyncList(@Param("list") List<ProcessSyncPO> list);

    /**
     * 批量更新 t_process_sync状态
     *
     * @param model
     */
    void updateProcessBatch(UpdateProcessRequest model);
}

目前已知3种数据操作方法：

1.通过继承BaseMapper，使用BaseMapper中自带的数据操作方法。

 2.直接将sql数据写在方法注解上。

 3.添加resources/mapper/xxxMapper.xml文件，对应xxxMapper类中自定义的方法

 

application.yml中添加映射文件的路径： 

mybatis-plus:
  mapper-locations: classpath:mapper/*.xml

 mapper.xml中的namespace对应映射类的完整类名，id对应映射类中的方法名。

 mapper.xml中的参数对象名称要写完整，否则可以在application.yml中添加type-aliases-package配置：

mybatis-plus:
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.vcredit.fts.process.model.po

四、使用

五、常用脚本

mysql:

1.批量插入

    <insert id="insertProcessSyncList">
        INSERT INTO t_process_sync
            (id,loan_kind,loan_side,process_id,external_id,step,is_hangup,is_init,last_success_time,last_failure_time,remark,handle_count)
        VALUES
        <foreach collection="list" item="element" index="index" separator=",">
            (
            #{element.id},
            #{element.loanKind},
            #{element.loanSide},
            #{element.processId},
            #{element.externalId},
            #{element.step},
            #{element.isHangup},
            #{element.isInit},
            #{element.lastSuccessTime},
            #{element.lastFailureTime},
            #{element.remark},
            #{element.handleCount}
            )
        </foreach>
    </insert>

2.部分字段更新

    <update id="updateProcessBatch"  parameterType="com.vcredit.fts.process.model.dto.UpdateProcessRequest">
        UPDATE t_process_sync
        <trim prefix="set" suffixOverrides=",">
            <if test="step!=null">step=#{step},</if>
            <if test="isHangup!=null">is_hangup=#{isHangup},</if>
            <if test="isInit!=null">is_init=#{isInit},</if>
            <if test="remark!=null">remark=#{remark},</if>
            <if test="handleCount!=null">handle_count=#{handleCount},</if>
            <if test="deleted!=null">deleted=#{deleted},</if>
            <if test="lastSuccessTime!=null">last_success_time=#{lastSuccessTime},</if>
            <if test="lastFailureTime!=null">last_failure_time=#{lastFailureTime},</if>
        </trim>
        WHERE process_id=#{processId} AND external_id IN
              <foreach collection="externalIds" separator="," item="item" open="(" close=")">#{item}</foreach>
    </update>

3.条件查询

    <select id="getProcessSyncList" resultType="com.vcredit.fts.process.model.po.ProcessSyncPO">
        SELECT * FROM t_process_sync
        WHERE process_id=#{processId}
        AND step=#{step}
        AND is_init IN(-1,1)
        <if test="limitTime!=null and limitTime!=''">
            AND (last_success_time IS NULL OR last_success_time &lt; #{limitTime})
        </if>
        AND handle_count &lt;= #{handleCount}
        AND is_hangup=0
        AND (last_failure_time IS NULL OR last_failure_time &lt; (CURRENT_TIMESTAMP - INTERVAL 10 MINUTE))
        AND deleted=0 LIMIT #{queryCount}
    </select>

执行多头语句，网上说法是需要在数据库连接上加上【allowMultiQueries=true】 ，多条语句使用分号分隔，我还没试验过。

如：jdbc.jdbcUrl=jdbc:mysql://127.0.0.1:3306/database?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true

<delete id="deleteRecipeVideoInfo" parameterType="int"> 
delete from tb_parent where id = #{id}; 
delete from tb_child where pid = #{id} 
</delete>

sqlserver:

1.条件查询

    <select id="getUserCenterSyncInfo" resultType="com.vcredit.vts.entity.bo.UserCenterSyncData">
        SELECT TOP ${num} a.Bid,a.LoanKind,b.IdentityNo,b.Mobile,c.RegisterId,c.VcreditCustId
        FROM Loan.apply.ApplyInfo a WITH(NOLOCK)
        JOIN Loan.apply.ApplyInfoCustomer b WITH(NOLOCK) ON b.Bid = a.Bid
        LEFT JOIN Loan.business.BusinessRelatedInfo c WITH(NOLOCK) ON c.Bid = a.Bid
        WHERE a.LoanKind = #{loanKind} AND a.Bid > ${lastBid}
             AND ( ISNULL(c.RegisterId, '') = '' OR ISNULL(c.VcreditCustId, '') = '' )
        ORDER BY a.Bid;
    </select>

    <select id="getUserCenterSyncStatistics" resultType="com.vcredit.vts.entity.bo.UserCenterSyncStatistics">
        SELECT a.LoanKind,COUNT(1) AllCount,
        COUNT(CASE WHEN ISNULL(c.RegisterId, '') = '' THEN 1 END) NotSyncCount,
        COUNT(CASE WHEN c.RegisterId='-999' THEN 1 END) SyncFailureCount
        FROM Loan.apply.ApplyInfo a WITH(NOLOCK)
        LEFT JOIN Loan.business.BusinessRelatedInfo c WITH(NOLOCK) ON c.Bid = a.Bid
        WHERE a.LoanKind IN <foreach collection="loanKinds" separator="," item="item" open="(" close=")">#{item}</foreach>
        GROUP BY a.LoanKind;
    </select>

2.执行复杂语句

    <!--保存并更新数据-->
    <insert id="saveUserCenterInfo" parameterType="com.vcredit.vts.entity.bo.UserCenterSyncData">
        USE [Test];
        SET XACT_ABORT ON;
        BEGIN TRANSACTION;

        CREATE TABLE #sync_tmp([bid]  INT,[vcredit_cust_id] [varchar] (50),[register_id] [varchar] (50));
        INSERT #sync_tmp ( bid, vcredit_cust_id, register_id )
        VALUES
        <foreach collection="syncDataList" separator="," item="item" open="" close=";">( #{item.Bid}, #{item.VcreditCustId}, #{item.RegisterId} )</foreach>

        -- 补BusinessRelatedInfo表缺失的记录
        UPDATE b SET b.RegisterId = a.register_id,b.VcreditCustId = a.vcredit_cust_id
        FROM #sync_tmp a WITH(NOLOCK)
        JOIN business.BusinessRelatedInfo b WITH(ROWLOCK) ON a.bid = b.Bid
        WHERE ISNULL(b.RegisterId, '') = '' OR ISNULL(b.VcreditCustId, '') = '';

        -- 补BusinessRelatedInfo表没有的记录
        INSERT INTO business.BusinessRelatedInfo(Bid,RegisterId,VcreditCustId,CreateTime,UpdateTime)
        SELECT a.bid,a.register_id,a.vcredit_cust_id,GETDATE(),GETDATE() FROM #sync_tmp a WITH(NOLOCK)
        LEFT JOIN business.BusinessRelatedInfo b WITH(NOLOCK) ON a.bid = b.Bid
        WHERE B.Id IS NULL AND a.register_id=(SELECT MAX(register_id) FROM #sync_tmp WHERE bid=a.bid);

        DROP TABLE #sync_tmp;
        COMMIT TRANSACTION;
    </insert>

六、常见问题

1.语句执行异常，参数p0附近有语法错误：

#{xxx}会自动给参数两边加上引号，所以如果是数字或常量需要改为${xxx}; 不过${xxx}存在SQL注入的风险，正常情况下还是建议使用#{xxx}。