src/main/java/com/conveyal/gtfs/loader/JdbcTableWriter.java
private PreparedStatement getUpdateReferencesStatement(
        SqlMethod sqlMethod,
        String refTableName,
        Field keyField,
        String keyValue,
        String newKeyValue
    ) throws SQLException {
        String sql;
        PreparedStatement statement;
        boolean isArrayField = keyField.getSqlType().equals(JDBCType.ARRAY);
        switch (sqlMethod) {
            case DELETE:
                if (isArrayField) {
                     sql = String.format(
                        "delete from %s where %s @> ARRAY[?]::text[]",
                        refTableName,
                        keyField.name
                    );
                } else {
                     sql = String.format("delete from %s where %s = ?", refTableName, keyField.name);
                }
                statement = connection.prepareStatement(sql);
                statement.setString(1, keyValue);
                return statement;
            case UPDATE:
                if (isArrayField) {
                    // If the field to be updated is an array field (of which there are only text[] types in the db),
                    // replace the old value with the new value using array contains clause.
                    // NOTE: We have to cast the string values to the text type because the StringListField uses arrays
                    // of text.
                    sql = String.format(
                        "update %s set %s = array_replace(%s, ?::text, ?::text) where %s @> ?::text[]",
                        refTableName,
                        keyField.name,
                        keyField.name,
                        keyField.name
                    );
                    statement = connection.prepareStatement(sql);
                    statement.setString(1, keyValue);
                    statement.setString(2, newKeyValue);
                    String[] values = new String[]{keyValue};
                    statement.setArray(3, connection.createArrayOf("text", values));
                } else {
                    sql = String.format(
                        "update %s set %s = ? where %s = ?",
                        refTableName,
                        keyField.name,
                        keyField.name
                    );
                    statement = connection.prepareStatement(sql);
                    statement.setString(1, newKeyValue);
                    statement.setString(2, keyValue);
                }
                return statement;
            default:
                throw new SQLException("SQL Method must be DELETE or UPDATE.");

        }
    }
private PreparedStatement getUpdateReferencesStatement(
        SqlMethod sqlMethod,
        String refTableName,
        Field keyField,
        String keyValue,
        String newKeyValue
    ) throws SQLException {
        String sql;
        PreparedStatement statement;
        boolean isArrayField = keyField.getSqlType().equals(JDBCType.ARRAY);
        switch (sqlMethod) {
            case DELETE:
                if (isArrayField) {
                     sql = String.format(
                        "delete from %s where %s @> ARRAY[?]::text[]",
                        refTableName,
                        keyField.name
                    );
                } else {
                     sql = String.format("delete from %s where %s = ?", refTableName, keyField.name);
                }
                statement = connection.prepareStatement(sql);
                statement.setString(1, keyValue);
                return statement;
            case UPDATE:
                if (isArrayField) {
                    // If the field to be updated is an array field (of which there are only text[] types in the db),
                    // replace the old value with the new value using array contains clause.
                    // NOTE: We have to cast the string values to the text type because the StringListField uses arrays
                    // of text.
                    sql = String.format(
                        "update %s set %s = array_replace(%s, ?::text, ?::text) where %s @> ?::text[]",
                        refTableName,
                        keyField.name,
                        keyField.name,
                        keyField.name
                    );
                    statement = connection.prepareStatement(sql);
                    statement.setString(1, keyValue);
                    statement.setString(2, newKeyValue);
                    String[] values = new String[]{keyValue};
                    statement.setArray(3, connection.createArrayOf("text", values));
                } else {
                    sql = String.format(
                        "update %s set %s = ? where %s = ?",
                        refTableName,
                        keyField.name,
                        keyField.name
                    );
                    statement = connection.prepareStatement(sql);
                    statement.setString(1, newKeyValue);
                    statement.setString(2, keyValue);
                }
                return statement;
            default:
                throw new SQLException("SQL Method must be DELETE or UPDATE.");

        }
    }
private PreparedStatement getUpdateReferencesStatement ( SqlMethod sqlMethod , String refTableName , Field keyField , String keyValue , String newKeyValue ) throws SQLException { String sql ; PreparedStatement statement ; boolean isArrayField = keyField . getSqlType ( ) . equals ( JDBCType . ARRAY ) ; switch ( sqlMethod ) { case DELETE : if ( isArrayField ) { sql = String . format ( "delete from %s where %s @> ARRAY[?]::text[]" , refTableName , keyField . name ) ; } else { sql = String . format ( "delete from %s where %s = ?" , refTableName , keyField . name ) ; } statement = connection . prepareStatement ( sql ) ; statement . setString ( 1 , keyValue ) ; return statement ; case UPDATE : if ( isArrayField ) { // If the field to be updated is an array field (of which there are only text[] types in the db), // replace the old value with the new value using array contains clause. // NOTE: We have to cast the string values to the text type because the StringListField uses arrays // of text. sql = String . format ( "update %s set %s = array_replace(%s, ?::text, ?::text) where %s @> ?::text[]" , refTableName , keyField . name , keyField . name , keyField . name ) ; statement = connection . prepareStatement ( sql ) ; statement . setString ( 1 , keyValue ) ; statement . setString ( 2 , newKeyValue ) ; String [ ] values = new String [ ] { keyValue } ; statement . setArray ( 3 , connection . createArrayOf ( "text" , values ) ) ; } else { sql = String . format ( "update %s set %s = ? where %s = ?" , refTableName , keyField . name , keyField . name ) ; statement = connection . prepareStatement ( sql ) ; statement . setString ( 1 , newKeyValue ) ; statement . setString ( 2 , keyValue ) ; } return statement ; default : throw new SQLException ( "SQL Method must be DELETE or UPDATE." ) ; } }
Constructs prepared statement to update or delete (depending on the method specified) records with foreign
references to the provided key value.
Constructs prepared statement to update or delete ( depending on the method specified ) records with foreign references to the provided key value .