3.3.4.2 包: com.atguigu.dataanalasis.conversion

1. 类: DimensionConversion

维度转换的类, 负责根据维度信息, 或者相关的id值, 然后插入到主表中.

package com.atguigu.dataanalasis.conversion;

import com.atguigu.dataanalasis.bean.ContactDimension;
import com.atguigu.dataanalasis.bean.DateDimension;
import com.atguigu.dataanalasis.bean.IDimension;
import com.atguigu.dataanalasis.util.JDBCUtil;
import com.atguigu.dataanalasis.util.LRUCache;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Logger;

/**
 * 这个类的主要功能是根据
 * 1. 电话号码 得到在 联系人表中的这个电话号码的id
 * 2. 年月日 得到这个日期的 id
 * <p>
 * 得到这些id之后用来存储在主表中
 */
public class DimensionConversion {
    LRUCache lruCache = new LRUCache(5000);

    public int getDimensionId(IDimension dimension) throws SQLException {
        //         先在缓存中查询
        String lruKey = getLruKey(dimension);
        if (lruCache.containsKey(lruKey)) {  // 如果缓存中存在就直接返回了
            return lruCache.get(lruKey);
        }

        // 获取查询和插入数据的sql语句
        String[] sqls = getSqls(dimension);
        // 获取id
        Connection conn = JDBCUtil.getInstance();
        int id = execSql(conn, sqls, dimension);
        if (id == 0) throw new RuntimeException("未找到匹配的维度id");
        // 存入缓存
        lruCache.put(lruKey, id);
        // 返回id
        return id;
    }

    /**
     * 执行sql语句: 查询, 插入, 查询
     *
     * @param conn
     * @param sqls
     * @param dimension
     * @return
     */
    private synchronized int execSql(Connection conn, String[] sqls, IDimension dimension) throws SQLException {
        // 执行第一次查询
        PreparedStatement preparedStatement = conn.prepareStatement(sqls[0]);
        setArguments(preparedStatement, dimension);
        ResultSet resultSet = preparedStatement.executeQuery();
        if (resultSet.next()) {
            return resultSet.getInt(1);
        }
        JDBCUtil.close(resultSet, preparedStatement, null);
        // 执行插入
        preparedStatement = conn.prepareStatement(sqls[1]);
        setArguments(preparedStatement, dimension);
        preparedStatement.executeUpdate();
        JDBCUtil.close(null, preparedStatement, null);
        // 执行第二次查询
        preparedStatement = conn.prepareStatement(sqls[0]);
        setArguments(preparedStatement, dimension);
        resultSet = preparedStatement.executeQuery();
        if (resultSet.next()) {
            return resultSet.getInt(1);
        }
        JDBCUtil.close(resultSet, preparedStatement, null);
        return 0;
    }

    /**
     * 给预执行语句设置参数
     *
     * @param preparedStatement
     * @param dimension
     */
    private void setArguments(PreparedStatement preparedStatement, IDimension dimension) throws SQLException {
        if (dimension instanceof ContactDimension) {
            ContactDimension cd = (ContactDimension) dimension;
            preparedStatement.setString(1, cd.getTelephone());
            preparedStatement.setString(2, cd.getName());

        } else if (dimension instanceof DateDimension) {
            DateDimension dd = (DateDimension) dimension;
            preparedStatement.setInt(1, Integer.parseInt(dd.getYear()));
            preparedStatement.setInt(2, Integer.parseInt(dd.getMonth()));
            preparedStatement.setInt(3, Integer.parseInt(dd.getDay()));
        }
    }


    /**
     * 根据传入的维度返回两条 sql语句: 查询语句和插入语句
     *
     * @param dimension
     * @return
     */
    private String[] getSqls(IDimension dimension) {
        String[] sqls = new String[2];
        if (dimension instanceof ContactDimension) {
            sqls[0] = "SELECT `id` FROM `tb_contacts` WHERE `telephone`=? AND `name`=?";
            sqls[1] = "INSERT INTO `tb_contacts` VALUES(NULL, ?, ?)";
        } else if (dimension instanceof DateDimension) {
            sqls[0] = "SELECT `id` FROM `tb_date` WHERE `year`=? AND `month`=? AND `day`=?";
            sqls[1] = "INSERT INTO `tb_date` VALUES(NULL, ?, ?, ?)";
        }
        return sqls;
    }

    /**
     * 根据维度信息, 获取相应的缓存的key
     *
     * @param dimension
     * @return
     */
    private int i = 0;

    private String getLruKey(IDimension dimension) {
        StringBuffer sb = new StringBuffer();
        if (dimension instanceof ContactDimension) {
            ContactDimension cd = (ContactDimension) dimension;
            Logger global = Logger.getGlobal();
            global.info(++i + "");
            global.info(cd.getTelephone());
            sb.append(cd.getTelephone());
        } else {
            DateDimension dd = (DateDimension) dimension;
            sb.append(dd.getYear()).append(dd.getMonth()).append(dd.getDay());
        }
        return sb.toString();
    }


}