com.luoshu.open.id.JdbcIdDao Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of luoshu-id Show documentation
There is a newer version: 0.32
package com.luoshu.open.id;


import com.luoshu.open.id.exception.IdException;
import com.luoshu.open.id.ui.model.ConditionDTO;
import lombok.extern.slf4j.Slf4j;
import org.luoshu.util.StringUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 操作数据库
 */
@Slf4j
public class JdbcIdDao {

    private DataSource dataSource;
    private JdbcTemplate jdbcTemplate;

    public JdbcIdDao(DataSource dataSource){
        this.dataSource = dataSource;
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }



    public IdPO findByCategory(String category){
        if(category == null || "".equals(category.trim())){
            throw new NullPointerException("category is empty");
        }
        String sql = "select num , version from t_id where category = ?";

        Connection connection = null;
        String version = null;
        long num = 0;

        try {
            connection = dataSource.getConnection();
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setString(1 , category);
            ResultSet resultSet = statement.executeQuery();

            if(!resultSet.next()){
                // 查不到这条数据
                return null;
            }
            version = resultSet.getString("version");
            num = resultSet.getLong("num");
        } catch (Throwable e) {
            throw new IdException(e.getMessage() , e);
        } finally {
            SqlUtil.close(connection);
        }

        IdPO idPO = new IdPO();
        idPO.setCategory(category);
        idPO.setVersion(version);
        idPO.setNum(num);
        return idPO;
    }

    public void insertCategory(IdPO idPO) {
        String sql = "INSERT into t_id(category , num , version , remark , create_time , update_time)\n" +
                "VALUES(? , ? , ? , ? , ? , ?)";

        Connection connection = null;
        try {
            connection = dataSource.getConnection();
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setString(1 , idPO.getCategory());
            statement.setLong(2 , idPO.getNum());
            statement.setString(3 , idPO.getVersion());
            statement.setString(4 , "");
            statement.setDate(5 , new Date(System.currentTimeMillis()));
            statement.setDate(6 , new Date(System.currentTimeMillis()));

            statement.executeUpdate();
        } catch (Throwable e) {
            throw new IdException(e.getMessage() , e);
        } finally {
            SqlUtil.close(connection);
        }
    }

    /**
     * 当本地可分配的序列id用完时，向数据库申请一批新的范围，这里要用乐观锁保证数据一致性
     * @param category 分类
     * @param markerIndex 标记的位置，会直接写入到数据库中
     * @param oldVersion 用于版本号的比对 ， 用于乐观锁
     * @param newVersion 如果更新成功，则会把新的版本号写进去
     * @return
     */
    public boolean updateSquenceNum(String category, long markerIndex, String oldVersion , String newVersion) {
        if(category == null || "".equals(category.trim())){
            throw new IdException("category is empty");
        }
        if(oldVersion == null || "".equals(oldVersion.trim())){
            throw new IdException("oldVersion is empty");
        }
        if(newVersion == null || "".equals(newVersion.trim())){
            throw new IdException("newVersion is empty");
        }

        String sql = "UPDATE t_id SET num = ? , version = ? , update_time = ? where category = ? and version= ?";

        Connection connection = null;

        try {
            connection = dataSource.getConnection();
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setLong(1 , markerIndex);
            statement.setString(2 , newVersion);
            statement.setDate(3 , new Date(System.currentTimeMillis()));
            statement.setString(4 , category);
            statement.setString(5 , oldVersion);
            int effect = statement.executeUpdate();
            // ResultSet 有两种情况，一种是没有这个 category ， 二是 version 乐观锁不通过
            return effect > 0;
        } catch (SQLException e) {
            throw new IdException(e.getMessage() , e);
        } finally {
            SqlUtil.close(connection);
        }
    }

    public List listByCondition(ConditionDTO conditionDTO) {
        List