实习修炼之第六天

主要进度

今天具体学习了利用mabatis对数据库的操作，具体分为两部分：xml文件配置（数据库操作）、dao层调用相关内容进行数据持久化，可参照如下代码进行学习。
xml配置文件部分代码如下

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//dal.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="ShardingAccountDao">

    <sql id="tableName">##account##</sql>

    <resultMap id="accountMap" type="com.mine.account.dal.domainobject.AccountDo">
        <result property="id" column="id"/>
        <result property="userId" column="user_id"/>
        <result property="phone" column="phone"/>
        <result property="wechatUnionId" column="wechat_union_id"/>
        <result property="nickname" column="nickname"/>
        <result property="head" column="head"/>
        <result property="status" column="status"/>
        <result property="createTime" column="create_time"/>
        <result property="updateTime" column="update_time"/>
    </resultMap>

    <sql id="columns">
        id,user_id,phone,wechat_union_id,nickname,head,status,create_time,update_time
    </sql>

    <sql id="allColumns">
        <include refid="columns"/>
    </sql>


    <select id="selectByUserId" resultMap="accountMap">
        select
        <include refid="allColumns"/>
        from
        <include refid="tableName"/>
        where user_id=#{userId};
    </select>

    <insert id="insert" parameterType="com.mine.account.dal.domainobject.AccountDo">
        insert into
        <include refid="tableName"/>
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <include refid="allColumns"/>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            #{id},#{userId},#{phone},#{wechatUnionId},#{nickname},#{head},#{status},now(),now()
        </trim>
    </insert>

    <update id="update" parameterType="com.mine.account.dal.domainobject.AccountDo">
        update
        <include refid="tableName"/>
        <set>
            <if test="phone != null">phone=#{phone},</if>
            <if test="nickname != null">nickname=#{nickname},</if>
            <if test="head != null">head=#{head},</if>
            <if test="status != null">status=#{status},</if>
            <if test="wechatUnionId !=null">wechat_union_id=#{wechatUnionId},</if>
            update_time=now()
        </set>
        where user_id=#{userId}
    </update>

    <select id="selectByLoginTime" parameterType="java.util.Map" resultMap="accountMap">
        select
        *
        from
        <include refid="tableName"/> account
        where account.id >#{id}  AND status=0
        order by id limit #{limit}
    </select>

    <select id="batchGetUserByUserIds" parameterType="java.util.Map" resultMap="accountMap">
        select
        <include refid="allColumns"/>
        from
        <include refid="tableName"/>
        where user_id in (${userIds})
    </select>

    <select id="getUserByUnionId" resultMap="accountMap">
        SELECT <include refid="allColumns"/>
          FROM <include refid="tableName"/>
         WHERE wechat_union_id = #{wechatUnionId}
      ORDER BY id ASC
        LIMIT 1
    </select>

    <insert id="migration" parameterType="com.mine.account.dal.domainobject.AccountDo">
        insert into
        <include refid="tableName"/>
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <include refid="allColumns"/>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            #{id},#{userId},#{phone},#{wechatUnionId},#{nickname},#{head},#{status},#{createTime},#{updateTime}
        </trim>
    </insert>

    <select id="selectByUserIdAndId" resultMap="accountMap">
        select
        <include refid="allColumns"/>
        from
        <include refid="tableName"/>
        where user_id=#{userId} and id = #{id};
    </select>

    <select id="updateByUserIdAndId" parameterType="com.mine.account.dal.domainobject.AccountDo">
        update
        <include refid="tableName"/>
        <set>
            phone = #{phone},
            wechat_union_id = #{wechatUnionId},
            nickname = #{nickname},
            head = #{head},
            status = #{status},
            update_time = #{updateTime}
        </set>
        where user_id = #{userId} and id = #{id}
    </select>

    <update id="rollBackAccountPhone" parameterType="map">
        update  <include refid="tableName"/>
        <set>
            <choose>
                <when test="rollBackToPhone !=null">
                    phone=#{rollBackToPhone}
                </when>
                <otherwise>
                    phone = null
                </otherwise>
            </choose>
        </set>
        where user_id = #{userId}
            and phone = #{checkPhone}
    </update>

    <update id="rollBackAccountWxUnionId" parameterType="map">
        update  <include refid="tableName"/>
        <set>
            <choose>
                <when test="rollBackToWxUnionId !=null">
                    `wechat_union_id`=#{rollBackToWxUnionId}
                </when>
                <otherwise>
                    `wechat_union_id` = null
                </otherwise>
            </choose>
        </set>
        where user_id = #{userId}
            and `wechat_union_id` = #{checkWxUnionId}
    </update>

</mapper>

dao层利用xml配置持久化数据代码如下

package com.mine.account.dal.sharding;

import com.mine.account.dal.domainobject.AccountDo;
import com.mine.account.dal.domainobject.ShardingWechatAccountDo;
import com.mine.account.strategy.AccountShardingStrategy;
import com.mine.account.strategy.ShardingRule;
import com.mine.account.utils.ThreadPoolUtils;
import com.mine.mybatis.core.MdsSqlSessionTemplate;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.util.*;
import java.util.concurrent.Callable;
import java.util.concurrent.TimeUnit;

/**
 * Created by tangch on 2018/11/12.
 */
@Component("shardingAccountDao")
public class ShardingAccountDao {

    private static final Logger log = LoggerFactory.getLogger("running");

    @Resource
    private MdsSqlSessionTemplate mdsSqlSessionTemplate;

    @Resource
    private WechatAccountUnionIdDao wechatAccountUnionIdDao;

    @Resource
    private AccountShardingStrategy shardingStrategy;


    /**
     * 根据用户id查找用户信息
     *
     * @param userId
     * @return
     */
    public AccountDo selectByUserId(Long userId) {
        Map<String, Object> param = new HashMap<>();
        param.put("userId", userId);
        return mdsSqlSessionTemplate.selectOne("ShardingAccountDao.selectByUserId", ShardingRule.getNumbericUniqueKeyRule(userId), param);
    }

    /**
     * 添加账户记录
     *
     * @param accountDo
     * @return
     */
    public int insert(AccountDo accountDo) {
        return mdsSqlSessionTemplate.insert("ShardingAccountDao.insert", ShardingRule.getNumbericUniqueKeyRule(accountDo.getUserId()), accountDo);
    }

    /**
     * 更新账户记录
     *
     * @param accountDo
     * @return
     */
    public int update(AccountDo accountDo) {
        return mdsSqlSessionTemplate.insert("ShardingAccountDao.update", ShardingRule.getNumbericUniqueKeyRule(accountDo.getUserId()), accountDo);
    }

    /**
     * 根据用户最后登录日期查询
     *
     * @param param
     * @return
     */
    public List<AccountDo> selectByLoginTime(Map<String, Object> param) {
        Integer tableIndex = (Integer) param.get("tableIndex");
        if (tableIndex > shardingStrategy.getAccountTableNum() - 1) {
            return new ArrayList<>();
        }
        return mdsSqlSessionTemplate.selectList("ShardingAccountDao.selectByLoginTime",
                ShardingRule.getTableNumKeyRule(tableIndex), param);
    }


    /**
     * 批量查询用户信息
     *
     * @param userIds
     * @return
     */
    public List<AccountDo> batchGetUserByUserIds(List<Long> userIds) {
        //修改方案：使用userId取模，轮询取各个分片的数
        Map<String, List<Long>> targetTable2UserIds = mapUserIdToTargetTable(userIds);
        List<Callable<List<AccountDo>>> userIdsCallList = new ArrayList<>();
        for (List<Long> ids : targetTable2UserIds.values()) {
            userIdsCallList.add(() -> getUsersByIds(ids.get(0), listToString(ids)));
        }
        try {
            List<List<AccountDo>> resultList = ThreadPoolUtils.submit(userIdsCallList, 300, TimeUnit.MILLISECONDS);
            List<AccountDo> accountDosList = new ArrayList<>();
            for (List<AccountDo> accountlist : resultList) {
                accountDosList.addAll(accountlist);
            }
            return accountDosList;
        } catch (Exception e) {
            log.error("批量查询超时" + userIds, e);
            return new ArrayList<>();
        }
    }

    private String listToString(List<Long> userIds) {
        StringJoiner joiner = new StringJoiner(",");
        for (Long id : userIds) {
            joiner.add(id.toString());
        }
        return joiner.toString();
    }

    public List<AccountDo> getUsersByIds(Object shardingKey, String userIds) {
        Map<String, Object> param = new HashMap<>();
        param.put("userIds", userIds);
        return mdsSqlSessionTemplate.selectList("ShardingAccountDao.batchGetUserByUserIds", ShardingRule.getNumbericUniqueKeyRule(shardingKey), param);
    }

    /**
     * 根据unionId查询用户
     *
     * @param wechatUnionId
     * @return
     */
    public AccountDo getUserByUnionId(String wechatUnionId) {
        ShardingWechatAccountDo shardingWechatAccountDo = wechatAccountUnionIdDao.getWechatAccountUnionId(wechatUnionId);
        if (shardingWechatAccountDo != null) {
            Long userId = shardingWechatAccountDo.getUserId();
            return selectByUserId(userId);
        }
        return null;
    }

    private Map<String, List<Long>> mapUserIdToTargetTable(List<Long> userIds) {
        Map<String, List<Long>> targetTable2UserIds = new HashMap<>();
        for (Long userId : userIds) {
            String targetTable = shardingStrategy.calculateTargetShardingTable(userId);
            if (targetTable2UserIds.get(targetTable) == null) {
                List<Long> ids = new ArrayList<>();
                ids.add(userId);
                targetTable2UserIds.put(targetTable, ids);
            } else {
                targetTable2UserIds.get(targetTable).add(userId);
            }
        }
        return targetTable2UserIds;
    }

    public int datamigration(AccountDo accountDo) {
        return mdsSqlSessionTemplate.insert("ShardingAccountDao.migration"
                , ShardingRule.getNumbericUniqueKeyRule(accountDo.getUserId()), accountDo);
    }

    public AccountDo selectByUserIdAndId(Long userId, Long id) {
        Map<String, Object> param = new HashMap<>();
        param.put("userId", userId);
        param.put("id", id);
        return mdsSqlSessionTemplate.selectOne("ShardingAccountDao.selectByUserIdAndId",
                ShardingRule.getNumbericUniqueKeyRule(userId), param);
    }


    public int updateByUserIdAndId(AccountDo accountDo) {
        return mdsSqlSessionTemplate.update("ShardingAccountDao.updateByUserIdAndId",
                ShardingRule.getNumbericUniqueKeyRule(accountDo.getUserId()), accountDo);
    }

    public int rollBackAccountPhone(Long userId, String rollBackToPhone, String checkPhone) {
        Map<String, Object> params = new HashMap<>();
        params.put("userId", userId);
        params.put("rollBackToPhone", rollBackToPhone);
        params.put("checkPhone", checkPhone);
        return mdsSqlSessionTemplate.update("ShardingAccountDao.rollBackAccountPhone"
                , ShardingRule.getNumbericUniqueKeyRule(userId), params);
    }

    public int rollBackAccountWxUnionId(Long userId, String rollBackToWxUnionId, String checkWxUnionId) {
        Map<String, Object> params = new HashMap<>();
        params.put("userId", userId);
        params.put("rollBackToWxUnionId", rollBackToWxUnionId);
        params.put("checkWxUnionId", checkWxUnionId);
        return mdsSqlSessionTemplate.update("ShardingAccountDao.rollBackAccountWxUnionId"
                , ShardingRule.getNumbericUniqueKeyRule(userId), params);
    }

}