Parsing multiple line records using Java 8 Streams

Question

There was a suggestion in this StackOverflow question to move this question here. I added some more information and restructured the question a bit.

I'm trying to parse the following file which contains information in the following format:

TABLE_NAME

VARIABLE_LIST_OF_COLUMNS

VARIABLE_NUMBER_OF_ROWS (Seperated by a tab seperator)

An example (using ',' as the seperator for the question; actual seperator is a tab):

STUDENTS

ID

NAME

1,Mike

2,Kimberly

The idea is to build a list of insert sql statements (context for the code snippet). So the output would be (ignore numeric/null values for now):

INSERT INTO STUDENTS (ID, NAME) VALUES ('1','Mike');

INSERT INTO STUDENTS (ID, NAME) VALUES ('2','Kimberly');

What I want to know is whether this kind of multiline parsing is at all possible using java 8 streams API? This is what I have at the moment:

public final class StatementGeneratorMain {

    public static void main(final String[] args) throws Exception{
        List<String> fileNames = Arrays
            .asList("STUDENTS.txt");
        fileNames.stream()
            .forEach(fileName -> {
                String tableName;
                List<String> columnNames;
                List<String[]>  dataRows;
                try (BufferedReader br = getBufferedReader(fileName)) {
                    tableName = br.lines().findFirst().get();
                } catch (Exception e) {
                    throw new RuntimeException(e);
                }

                try (BufferedReader br = getBufferedReader(fileName)) {
                    //skip the first line because its been processed.
                    columnNames = br.lines().skip(1).filter(v -> v.split("\t").length == 1).collect(toList());
                } catch (Exception e) {
                    throw new RuntimeException(e);
                }

                try (BufferedReader br = getBufferedReader(fileName)) {
                    //skip the first line and the columns length to get the data
                    //columns are identified as being splittable on the delimiter
                    dataRows = br.lines().skip(1 + columnNames.size()).map(s -> s.split("\t"))
                        .collect(toList());
                } catch (Exception e) {
                    throw new RuntimeException(e);
                }

                String columns = columnNames.stream().collect(joining(",","(",")"));

                List<String> dataRow = dataRows.stream()
                    .map(arr -> Arrays.stream(arr).map(x -> "'" + x + "'").collect(joining(",", "(", ")")))
                    .map(row -> String.format("INSERT INTO %s %s VALUES %s;", tableName, columns, row))
                    .collect(toList());

                dataRow.forEach(l -> System.out.println(l));
            });
    }

    private static BufferedReader getBufferedReader(String fileName) {
        return new BufferedReader(new InputStreamReader(StatementGeneratorMain.class.getClassLoader().getResourceAsStream(
            fileName)));
    }
}

This piece of code does the job for me, but I don't really like it because I read the same file thrice (once for table name, again to deduce the columns, again to get the rows). I also don't think that it is proper functional style.

What I am looking for is a more elegant way to do this kind of multiline/multirecord parsing using the streams API.

What I thought about:

• Using Files.lines() instead of BufferedReader.
• Using partitioningBy() instead of filter().collect(toList()) when parsing the columns, then true values goes into the column list and false values goes into the dataRows list.

I'm not too particular about stuff like numeric column and null values at this point.

Answer 1

Simply put, you only need a Consumer<String>-implementing class that knows what lines to read as the table name, the columns and rows.

public class StatementGenerator implements Consumer<String> {

    private static String START = " (";
    private static String END = ") ";
    private static String ROW_DELIMITER = "\n";

    private String delimiter = "\t";
    private String parameter = "%";
    private ObjIntConsumer<List<String>> parameterSupplier =
            (list, i) -> { list.add(parameter); };
    private String table = null;
    private List<String> columns = new ArrayList<>();
    private List<String> rows = new ArrayList<>();

    public void setDelimiter(String delimiter) {
        this.delimiter = delimiter;
    }

    public void setParameter(String parameter) {
        this.parameter = parameter;
    }

    public void setParameterSupplier(ObjIntConsumer<List<String>> parameterSupplier) {
        this.parameterSupplier = parameterSupplier;
    }

    @Override
    public void accept(String t) {
        if (table == null) {
            table = t;
            return;
        }
        if (!t.contains(delimiter)) {
            columns.add(t);
            return;
        }
        rows.add(t);
    }

    public String getTableName() {
        return table;
    }

    public List<String> getColumns() {
        return columns;
    }

    public List<String> getRows() {
        return rows;
    }

    public String getParameterizedStatement() {
        StringBuilder result = new StringBuilder("INSERT INTO ");
        result.append(constructSegment(getTableName(), getColumns())).append(
                constructSegment("VALUES", IntStream.rangeClosed(1, getColumns().size())
                        .collect(ArrayList::new, parameterSupplier, List::addAll)));
        return result.append(";").toString();
    }

    public List<String> getRawStatements() {
        String placeholderStatement = getParameterizedStatement()
                .replaceAll(Pattern.quote(parameter), "%s");
        return getRows().stream().map(r -> String.format(placeholderStatement,
                    Pattern.compile(delimiter).splitAsStream(r)
                        .map(v -> "'" + v + "'").toArray()))
                .collect(Collectors.toList());
    }

    public String getFullStatement() {
        return getRawStatements().stream().collect(Collectors.joining(ROW_DELIMITER));
    }

    @Override
    public String toString() {
        return getParameterizedStatement();
    }

    private static String constructSegment(String prefix, List<String> list) {
        return prefix + list.stream().collect(Collectors.joining(", ", START, END));
    }
}

In my sample implementation above, I've made the delimiter configurable, but there's one other configuration I want to highlight - parameter.

SQL injection

The sample implementation provides a getParameterizedStatement() as the starting point, because by right you should let your database driver handle the escaping of quotes (and possibly other magic values). We certainly do not need Little Bobby Tables to hang around here.

The parameter list is driven by an ObjIntConsumer, because if I'm not mistaken, there are certain drivers allowing for an index-based parameter substitution. That will mean you can potentially override parameterSupplier with something like (list, i) -> { list.add("::" + i); }; (or whatever the placeholder format is).

With that said, assuming you can absolutely trust your file-based input (i.e. sanitized inputs), we can then proceed with using getRawStatements(), which performs a simple substitution on the parameter with our String.format()'s "%s" placeholder. This is more aligned with what you are asking for. getFullStatement() simply concatenates all the rows into a single String, if that is what you actually require.

Here's what I used in my main() code:

public class StatementGeneratorMain {

    public static void main(String[] args) throws IOException, URISyntaxException {
        StatementGenerator generator = new StatementGenerator();
        generator.setDelimiter(",");
        try (Stream<String> lines = Files.lines(Paths.get(
                ClassLoader.getSystemResource("sqlRecords.txt").toURI()))) {
            lines.forEach(generator);
        }
        System.out.println(generator.getTableName());
        System.out.println(generator.getColumns());
        System.out.println(generator.getParameterizedStatement());
        generator.getRawStatements().forEach(System.out::println);
    }
}

And the sample output:

STUDENTS
[ID, NAME]
INSERT INTO STUDENTS (ID, NAME) VALUES (%, %) ;
INSERT INTO STUDENTS (ID, NAME) VALUES ('1', 'Mike') ;
INSERT INTO STUDENTS (ID, NAME) VALUES ('2', 'Kimberly') ;

Answer 2

Wow, that's a long lambda. It deserves to be a method of its own.

There is a fundamental limit to how much can be done using functional programming, since the very act of reading from the BufferedReader alters its state. In Haskell, for example, all I/O is a bit of a hassle, using the IO monad to indicate that the function has side-effects of consuming input or producing output.

A further complication here is that the first few lines are to be treated differently. Moreover, the result from the first few lines is to be used in the rest of the response. Making a Stream consisting of all lines of the file is therefore not a great idea.

For those reasons, I recommend abandoning the idea of using streams for processing the header. Here's my suggestion:

import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.io.IOException;
import java.util.ArrayList;
import java.util.stream.Collectors;
import java.util.stream.Stream;

public class StatementGeneratorMain {
    private static BufferedReader getBufferedReader(String fileName) {
        ClassLoader cl = StatementGeneratorMain.class.getClassLoader();
        return new BufferedReader(new InputStreamReader(cl.getResourceAsStream(fileName)));
    }

    private static Stream<String> toSql(BufferedReader br) throws IOException {
        String tableName = br.readLine();
        ArrayList<String> columnNames = new ArrayList<>();
        do {
            br.mark(256);  // max length of column name or first data column
            String line = br.readLine();
            if (line.indexOf("\t") >= 0) {
                // End of header; this is a row of data
                br.reset();
                break;
            }
            columnNames.add(line);
        } while (true);
        String columns = columnNames.stream().collect(Collectors.joining(", "));

        return br.lines()
                 .map(row -> row.replace("'", "''").replace("\t", "', '"))
                 .map(row -> String.format("INSERT INTO %s (%s) VALUES('%s');",
                                           tableName, columns, row));
    }

    public static void main(String[] args) throws IOException {
        try (BufferedReader br = getBufferedReader("STUDENTS.txt")) {
            toSql(br).forEach(System.out::println);
        }
    }
}

Note that this approach is vulnerable to SQL injection. I've done some rudimentary escaping using row.replace("'", "''"), but that might not protect you against all special characters.

Answer 3

This answer is a little offtopic but...

I've had almost the same task not so long ago. The task was to parse CSV file and insert data into specified table. To deal with it I've used org.apache.commons:commons-csv:1.1 library. Its capable of parsing csv files into column name to column value map.

List<Map<String, String>> result = new ArrayList<>();
try (InputStream is = new FileInputStream('');
     Reader reader = new InputStreamReader(is)) {
   CSVParser parser = CSVFormat.EXCEL.withHeader().parse(reader) //Excel is for tab separated files, use DEFAULT format for CSV
   for (CSVRecord record : parser) {
       result.add(record.toMap());
   }
}

That will give you a list of maps from CSV file that you can pass to bulk insert query builder method (or lambda if you want to).

Sample file format:

ID,NAME
1,Mike
2,Kimberly