예제 #1
0
 private String toValue(String[] oneObjects, String[] file_columns, InsertColumn ic)
     throws SQLException {
   String value = "";
   if (ic.getLctype().equals("File")) {
     value = file_columns[Integer.parseInt(ic.getExpstr())];
   } else if (ic.getLctype().equals("Camp")) {
     value = oneObjects[Integer.parseInt(ic.getExpstr())];
   }
   return value;
 }
예제 #2
0
  public int run() {
    int rt = -1;

    String step = leadsfile.split("_")[2];
    if (!"01".equals(step)) {
      logger.info(
          "Leads setp :" + step + " skip leadsInsert",
          leadsfile,
          "",
          "2-1",
          "",
          "名单营销波次为:" + step + ",跳过LeadsInsert.");
      return 0;
    }

    LineIterator it = null;
    JDBCExecute jdbcExecute =
        new JDBCExecute(
            conf.getProperty("leads.insert.db.driverClass"),
            conf.getProperty("leads.insert.db.databaseURL"),
            new KeyMapResource().get(conf.getProperty("leads.insert.db.databaseUser")),
            new KeyMapResource().get(conf.getProperty("leads.insert.db.databasePassword")));

    String getCampSQL = conf.getProperty("leads.insert.get.camp.sql");

    //        JDBCExecute jdbcExecute1 = new JDBCExecute(
    //                conf.getProperty("leads.insert.dwdb.driverClass"),
    //                conf.getProperty("leads.insert.dwdb.databaseURL"),
    //                new KeyMapResource().get(conf.getProperty("leads.insert.dwdb.databaseUser")),
    //                new
    // KeyMapResource().get(conf.getProperty("leads.insert.dwdb.databasePassword"))
    //        );

    //        logger.info("LeadsInsert.getCampSQL:"+getCampSQL,
    //                leadsfile,
    //                "",
    //                "2-1",
    //                "",
    //                "LeadsInsert.getCampSQL:"+getCampSQL);

    List<String[]> sqls = new ArrayList<String[]>();
    List<String> file_sqls = new ArrayList<String>();

    try {

      File readfile = new File(conf.getProperty("leads.insert.readpath") + leadsfile);
      File outfile = new File(conf.getProperty("leads.insert.savepath") + insertfile);
      String tableName = conf.getProperty("leads.insert.db.table.name");

      logger.info(
          "Leads Insert Table Name:" + tableName,
          leadsfile,
          "",
          "2-1",
          "",
          "名单要插入的表为:" + tableName);

      // INSERT INTO ABC(C1,C1) VALUES ('1','2');
      StringBuffer sqlColumnBuffer = new StringBuffer("INSERT INTO");
      sqlColumnBuffer.append(" ");
      sqlColumnBuffer.append(tableName);
      sqlColumnBuffer.append("(");
      StringBuffer sqlValuesBuffer = new StringBuffer("VALUES (");
      int i = 1;
      String tempStr = conf.getProperty("leads.insert.column." + i);
      String[] tempArr;
      List<InsertColumn> insertColumns = new ArrayList<InsertColumn>();
      InsertColumn ic;
      while (tempStr != null) {
        ic = new InsertColumn();

        tempArr = tempStr.split("\\|", -1);
        //  System.out.println(tempStr);
        if (i != 1) {
          sqlColumnBuffer.append(",");
        }

        ic.setName(tempArr[0]);
        ic.setLctype(tempArr[1]);
        ic.setExpstr(tempArr[2]);

        insertColumns.add(ic);
        //  System.out.println(tempArr[0]);
        sqlColumnBuffer.append(tempArr[0]);

        i++;
        tempStr = conf.getProperty("leads.insert.column." + i);

        // System.out.println(tempArr);
      }

      sqlColumnBuffer.append(") ");

      it = FileUtils.lineIterator(readfile, conf.getProperty("leads.insert.file.encoding"));
      String[] sql;
      String line;
      String[] file_columns;
      String[] oneObjects = null;
      String value;
      String selectSQL = conf.getProperty("leads.insert.check.sql");
      String newSelectSQL;
      boolean isOneQuery = false;
      while (it.hasNext()) {
        line = it.nextLine();
        file_columns = line.split("\\|", -1);
        if (!isOneQuery) { // 文件读取循环开始时,执行一次。
          String tempsql = SqlStrUtils.toSQL(getCampSQL, file_columns);
          logger.info(
              "LeadsInsert.getCampSQL:" + tempsql.replaceAll("\'", ""),
              leadsfile,
              "",
              "2-1",
              "",
              "LeadsInsert.getCampSQL:" + tempsql.replaceAll("\'", ""));
          oneObjects = jdbcExecute.getOneObject(tempsql);
          isOneQuery = true;
        }
        for (int j = 0; j < insertColumns.size(); j++) { // int index : indexStrs){
          // indexStrs[j];
          if (j != 0) {
            sqlValuesBuffer.append(",");
          }
          value = toValue(oneObjects, file_columns, insertColumns.get(j));
          if (value != null
              && !value.equals("")) { // toValue(camp_resultSet,file_columns,insertColumns.get(j))
            sqlValuesBuffer.append("'" + value + "'");
          } else {
            sqlValuesBuffer.append("NULL");
          }
        }
        sqlValuesBuffer.append(")");

        newSelectSQL = toExpstrValue(selectSQL, file_columns);

        sql = new String[2];

        sql[0] = sqlColumnBuffer.toString() + sqlValuesBuffer.toString();

        sql[1] = newSelectSQL;
        file_sqls.add(sql[0]);
        sqls.add(sql);
        sqlValuesBuffer.delete(8, sqlValuesBuffer.length());
      }
      // FileUtils.writeLines(outfile,sqls,conf.getProperty("leads.insert.file.encoding"));
      FileUtils.writeLines(outfile, conf.getProperty("leads.insert.file.encoding"), file_sqls);
      jdbcExecute.execute(sqls);
      logger.info(
          "Leads Insert Table[" + tableName + "] success. row count:" + sqls.size(),
          leadsfile,
          "",
          "2-1",
          "",
          "名单要插入的表[" + tableName + "]完成!插入笔数:" + sqls.size());
      logger.info(
          "Leads Insert run success!gen .SQL file:" + insertfile + " ",
          leadsfile,
          "",
          "2-1",
          "LINST00001",
          "LeadsInsert运行成功!生成 " + insertfile + " 完成!");
      rt = 0;
      // List<String[]> leadsList = new ArrayList<String[]>();
    } catch (Exception e) {
      if (e instanceof FileNotFoundException) {
        rt = 805;
        logger.error(
            e.getMessage().replaceAll("\'", ""),
            leadsfile,
            "",
            "2-1",
            "805",
            "没有找到指定文件!" + e.getMessage().replaceAll("\'", ""));
      } else if (e instanceof SQLIntegrityConstraintViolationException) {
        rt = 815;
        logger.error(
            e.getMessage().replaceAll("\'", ""),
            leadsfile,
            "",
            "2-1",
            "815",
            "插入数据库发生异常!" + e.getMessage().replaceAll("\'", ""));
      } else {
        rt = 809;
        e.printStackTrace();
        logger.error(
            "leadsInsert error:" + e.getMessage().replaceAll("\'", ""),
            leadsfile,
            "",
            "2-1",
            "809",
            "程序发生异常!" + e.getMessage().replaceAll("\'", ""));
      }
    } finally {
      LineIterator.closeQuietly(it);
      jdbcExecute.closeConnection();
    }

    return rt;
  }