Filter out items by Date using Java 8 streams-CodePudding

I have a transaction table with a list of transactions by users with dates.

Transaction has an amount field and a date field.

I want to search the DB. And then I want to filter only transactions done today (currentDate) and sum the total of the amount.

I need help to write the stream that will group the transaction list by today's date and sum them.

The date is stored like this in my DB:

2022-05-16 20:54:48.110

My attempt:

BigDecimal totalDailyTransaction = findTransaction.stream()
    .map(Transaction::getTransactionAmount)
    .reduce(BigDecimal.ZERO, BigDecimal::add);

CodePudding user response：

This doesn't make sense. What you're proposing, based on the statement 'using java 8 streams' and 'get all transactions by a user then group' (emphasis mine) - is to query the database for a boatload of records, then toss away most of them.

Whyever would you do that?

The right move is to tell the database you want the sum (did you mean 'count'?) of column X for all rows which have date Y.

Note that databases store dates as dates. What you pasted is what the database is showing you, it doesn't store dates as strings. However, it does show that your transaction is a timestamp (as in, a specific moment in time, not just 'some day').

public int getTransactionTotalOnDay(Connection con, LocalDate day) throws SQLException {
  try (var ps = connection.prepareStatement(
    "SELECT SUM(amount) FROM transaction WHERE timestamp BETWEEN ? AND ?")) {

    ps.setTimestamp(1, Timestamp.valueOf(day.atStartOfDay()));
    ps.setTimestamp(2, Timestamp.valueOf(day.plusDays(1).atStartOfDay().minusSeconds(1)));

    try (var rs = ps.executeQuery()) {
       if (!rs.next()) return 0;
       return rs.getInt(1);
    }
  }
}

Java streams are irrelevant and would be ridiculously inefficient (as that would involve having the DB stream the entire table to your java process, only for java to then ignore most of that - databases have indices and can do these queries orders of magnitude faster by just looking up precisely the information you need).

Concepts used in this snippet:

Raw JDBC database access - you may want to use a nicer abstraction such as JDBI or JOOQ.
Prepared Statements and ResultSets must be safely closed, so we use the try-with-resources construct to ensure it happens.
The only proper time API in java is the java.time package (that's where LocalDate is from. Unfortunately, JDBC predates it, so you need to convert things to JDBC-ness with Timestamp.valueOf. Hopefully your db and java server's ideas about 'current time zone' are identical or you'll need to write additional code.
Just about any queries you send to a database that involve user input must use prepared statement, because otherwise you'll be vulnerable to an SQL injection attack. It's probably not neccessary here, but you might as well use the style that works for any user input.

CodePudding user response：

I want to filter only transactions done today (currentDate) and sum the total of the amount.

You need to apply filter() operation to retain only today's transactions in the pipeline.

And then apply map() to extract the transaction amount and reduce() to get the total.

public static void main(String[] args) {
    List<Transaction> transactions = // initialing the source list

    BigDecimal totalDailyTransaction = transactions.stream()
        .filter(transaction -> isRecentTransaction(transaction))
        .map(Transaction::getTransactionAmount)
        .reduce(BigDecimal.ZERO, BigDecimal::add);
}

public static boolean isRecentTransaction(Transaction transaction) {
    // date is stored like this: `2022-05-16 20:54:48.110`
    // we need the first part representing the current date in accordance with ISO-8601
    String date = transaction.getTransactionDate().split(" ")[0];
    return LocalDate.now().isEqual(LocalDate.parse(date));
}

As I understand from the snippet of code in the comment beneath the question (where you've mentioned Map<String, List<Transaction>>) the date attribute of the Transaction entity is of type String which isn't a bright idea. That's definitely a problem, although it's out of the scope of the question on how to build the stream pipeline. You might consider describing what prevents you from using LocalDateTime class in your entity with all related details, like which ORM you are using, etc. in the separate question.