data-repo/src/main/java/com/data/datarepo/services/DataCenterService.java

package com.data.datarepo.services;

import com.data.datarepo.domain.DataRepoTable;
import com.data.datarepo.utils.DataSource;
import com.data.datarepo.utils.DataSourceOra;
import com.data.datarepo.utils.EquipmentNo;
import com.mysql.cj.jdbc.MysqlDataSource;
import oracle.jdbc.pool.OracleDataSource;

import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Date;

public class DataCenterService extends DataSourceOra {

    EquipmentNo equipmentNo = new EquipmentNo();
    DataDeleteService dataDeleteService = new DataDeleteService();

    public void insertData(List nameList, String repo_name, List valueList) throws SQLException {
        Connection connection = null;
//        String selectSQL = "insert into "+repo_name+"("+name+")values ("+value+")";
        StringBuilder sb = new StringBuilder();
        sb.append("INSERT ALL ");
        for (int i = 0; i < valueList.size(); i++) {
            sb.append(" INTO " + repo_name + "_OLD (" + nameList.get(i) + ") values (" + valueList.get(i) + ")");
        }
        sb.append("select * from dual");
        String selectSQL = sb.toString();
//        System.out.println(selectSQL);
        PreparedStatement prepStmt = null;
        try {
            connection = this.getConn();
            prepStmt = connection.prepareStatement(selectSQL);
            prepStmt.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (connection != null) {
                connection.commit();
                connection.close();
            }
            if (prepStmt != null) {
                prepStmt.close();
            }

        }
    }

    public void updateData(String num, String repo_name) throws SQLException {
        Connection connection = null;
        String selectSQL = "update data_sync_table set equipmentNo='" + num + "'where repo_name='" + repo_name + "'";
        PreparedStatement prepStmt = null;
        try {
            MysqlDataSource basicDS = DataSource.getInstance().getMySqlDS();
            connection = basicDS.getConnection();
            prepStmt = connection.prepareStatement(selectSQL);
            prepStmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();

        } finally {
            if (prepStmt != null) {
                prepStmt.close();
            }
            if (connection != null) {
                connection.close();
            }
        }
    }

    public void insertUpnum(DataRepoTable dataRepoTable) throws SQLException {
        Connection connection = null;
        String selectSQL = "insert into data_sync_upnum(table_name,repo_name,table_comment,equipmentNo,create_time_str)values (?,?,?,?,NOW())";
        PreparedStatement prepStmt = null;
        try {
            MysqlDataSource basicDS = DataSource.getInstance().getMySqlDS();
            connection = basicDS.getConnection();
            prepStmt = connection.prepareStatement(selectSQL);
            prepStmt.setString(1, dataRepoTable.getTable_name());
            prepStmt.setString(2, dataRepoTable.getRepo_name());
            prepStmt.setString(3, dataRepoTable.getTable_comment());
            prepStmt.setString(4, dataRepoTable.getUp_num());
            prepStmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();

        } finally {
            if (prepStmt != null) {
                prepStmt.close();
            }
            if (connection != null) {
                connection.close();
            }
        }
    }

    public String selectState(String resname, String resvalue, String repo_name, String up_num) throws SQLException {

        Connection connection = null;
        String selectSQL = "select * from " + repo_name + " where " + resname + "='" + resvalue + "' and UP_NUM='" + up_num + "'";
        PreparedStatement prepStmt = null;
        String state = "";
        try {
//            MysqlDataSource basicDS = DataSource.getInstance().getMySqlDS();
            connection = this.getConn();
            prepStmt = connection.prepareStatement(selectSQL);
            ResultSet rs = prepStmt.executeQuery();
            if (rs.next()) {
                state = "U";
            } else {
                state = "I";
            }

        } catch (Exception e) {
            e.printStackTrace();

        } finally {
            if (prepStmt != null) {
                prepStmt.close();
            }
            if (connection != null) {
                connection.close();
            }
        }
        return state;
    }

    public DataRepoTable ora2ora(String repo_name, String up_num) throws SQLException {

        DataRepoTable dataRepoTable = new DataRepoTable();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
//        String num = equipmentNo.getNewEquipmentNo(repo_name, up_num);
        dataRepoTable.setUp_num(up_num);
        try {
            conn = this.getConn();
            ps = conn.prepareStatement("select * from " + repo_name + "_OLD where UP_NUM='" + up_num + "'");
            rs = ps.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            int numberOfColumns = rsmd.getColumnCount();
            StringBuilder sb = new StringBuilder();
            sb.append("insert into " + repo_name + "_OLD (");
            for (int i = 1; i <= numberOfColumns - 1; i++) {
                sb.append(rsmd.getColumnName(i) + ",");
            }
            sb.append("UP_NUM");
            sb.append(")values(");
            for (int i = 1; i <= numberOfColumns - 1; i++) {
                sb.append("?,");
            }
            sb.append("?");
            sb.append(")");
            System.out.println(sb.toString());
            PreparedStatement pstmt = conn.prepareStatement(sb.toString());
            while (rs.next()) {
                for (int i = 1; i <= numberOfColumns - 1; i++) {
                    String value = rs.getString(i);
                    String name2 = rs.getMetaData().getColumnName(i);
                    if(name2.equals("AXBE9001")||name2.equals("UPDATETIME")||name2.equals("AHDX6145")
                            ||name2.equals("AHDX6146")||name2.equals("AHDX6158")||name2.equals("AHDX6177")
                            ||name2.equals("AHDX6165")||name2.equals("AHDX6174")||name2.equals("AHDX6159")
                            ||name2.equals("AHAE2493")||name2.equals("AHDX6020")||name2.equals("AXCP0039")
                            ||name2.equals("AHDP5070")||name2.equals("AHDP5071")||name2.equals("AHDP5112")
                            ||name2.equals("AHDP5082")||name2.equals("AHDP5102")||name2.equals("AHDP5039")
                            ||name2.equals("AHDP5040")||name2.equals("AHAE2325")||name2.equals("AHAE2328")
                            ||name2.equals("AHAE2349")||name2.equals("AXBE0017")||name2.equals("AHAE0041")
                            ||name2.equals("AHAE5024")){
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                        Date date = sdf.parse(value);
                        java.sql.Date startDate=new java.sql.Date(date.getTime());
                        pstmt.setDate(i, startDate);
                        continue;
                    }
                    pstmt.setString(i, value);
                }
                pstmt.setObject(numberOfColumns, up_num);
                pstmt.addBatch();
            }
            pstmt.executeBatch();
            updateData(up_num, repo_name);
//            dataDeleteService.DataDelete(repo_name);//清除oracle中数据
        } catch (Exception e) {
            System.out.println(e.getMessage());
        } finally {
            if (conn != null) {
                conn.close();
            }
            if (ps != null) {
                ps.close();
            }
        }
        return dataRepoTable;
    }

}