（BUG）Kettle8.1.0.0-365转换步骤输出SQL文件insert脚本日期类型数据，格式不正确且没有双引号...

为什么80%的码农都做不了架构师？>>>   

使用Kettle的SQL输出步骤输出SQL脚本时，出现SQL脚本中的日期类型的数据格式不正确，且没有使用单引号括起来。修改DataBase的GetSQLOutput方法解决了此问题，在此方法中增加了对日期数据的处理，具体见下面代码有中文注释的哪一行。

package org.pentaho.di.core.database;



/**
 * Database handles the process of connecting to, reading from, writing to and updating databases. The database specific
 * parameters are defined in DatabaseInfo.
 *
 * @author Matt
 * @since 05-04-2003
 */
public class Database implements VariableSpace, LoggingObjectInterface {


  /**
   * Return SQL statement (INSERT INTO TableName ...
   *
   * @param schemaName tableName The schema
   * @param tableName
   * @param fields
   * @param dateFormat date format of field
   * @throws KettleDatabaseException
   */

  public String getSQLOutput( String schemaName, String tableName, RowMetaInterface fields, Object[] r,
                              String dateFormat ) throws KettleDatabaseException {
    StringBuilder ins = new StringBuilder( 128 );

    try {
      String schemaTable = databaseMeta.getQuotedSchemaTableCombination( schemaName, tableName );
      ins.append( "INSERT INTO " ).append( schemaTable ).append( '(' );

      // now add the names in the row:
      for ( int i = 0; i < fields.size(); i++ ) {
        if ( i > 0 ) {
          ins.append( ", " );
        }
        String name = fields.getValueMeta( i ).getName();
        ins.append( databaseMeta.quoteField( name ) );

      }
      ins.append( ") VALUES (" );

      java.text.SimpleDateFormat[] fieldDateFormatters = new java.text.SimpleDateFormat[ fields.size() ];

      // new add values ...
      for ( int i = 0; i < fields.size(); i++ ) {
        ValueMetaInterface valueMeta = fields.getValueMeta( i );
        Object valueData = r[ i ];

        if ( i > 0 ) {
          ins.append( "," );
        }

        // Check for null values...
        //
        if ( valueMeta.isNull( valueData ) ) {
          ins.append( "null" );
        } else {
          // Normal cases...
          //
          switch ( valueMeta.getType() ) {
            case ValueMetaInterface.TYPE_BOOLEAN:
            case ValueMetaInterface.TYPE_STRING:
              String string = valueMeta.getString( valueData );
              // Have the database dialect do the quoting.
              // This also adds the single quotes around the string (thanks to
              // PostgreSQL)
              //
              string = databaseMeta.quoteSQLString( string );
              ins.append( string );
              break;
            case ValueMetaInterface.TYPE_DATE:
            case ValueMetaInterface.TYPE_TIMESTAMP: //增加此行，否则输出的SQL脚本中，不带单引号
              Date date = fields.getDate( r, i );

              if ( Utils.isEmpty( dateFormat ) ) {
                if ( databaseMeta.getDatabaseInterface() instanceof OracleDatabaseMeta ) {
                  if ( fieldDateFormatters[ i ] == null ) {
                    fieldDateFormatters[ i ] = new java.text.SimpleDateFormat( "yyyy/MM/dd HH:mm:ss" );
                  }
                  ins.append( "TO_DATE('" ).append( fieldDateFormatters[ i ].format( date ) ).append(
                    "', 'YYYY/MM/DD HH24:MI:SS')" );
                } else {
                  ins.append( "'" + fields.getString( r, i ) + "'" );
                }
              } else {
                try {
                  java.text.SimpleDateFormat formatter = new java.text.SimpleDateFormat( dateFormat );
                  ins.append( "'" + formatter.format( fields.getDate( r, i ) ) + "'" );
                } catch ( Exception e ) {
                  throw new KettleDatabaseException( "Error : ", e );
                }
              }
              break;
            default:
              ins.append( fields.getString( r, i ) );
              break;
          }
        }

      }
      ins.append( ')' );
    } catch ( Exception e ) {
      throw new KettleDatabaseException( e );
    }
    return ins.toString();
  }
}

 

 

 

转载于:https://my.oschina.net/gq2010/blog/3017371