How do I sum a BigDecimal property of a list of objects using Java Stream API?

If we have a list of objects, and we want to sum a BigDecimal property of these objects, we can achieve this using the Java Stream API. This API provides a clean and efficient way to process collections of objects. To sum the BigDecimal amounts, you can use the map and reduce methods of the Stream API.

As an example, we have a class named Transaction with a BigDecimal property named amount. We have a list of Transaction objects, and we want to calculate the total sum of the amount properties.

In the code snippet below we do the following:

  • Creating Transactions: We create a list of Transaction objects, each with a different BigDecimal amount.
  • Filter Transactions and its amount: We filter to exclude the null transaction and null transaction amount.
  • Mapping to Amounts: We use the map method to convert each Transaction object to its amount property.
  • Summing the Amounts: The reduce method takes two parameters: an identity value (BigDecimal.ZERO) and an accumulator function (BigDecimal::add). The accumulator function adds each BigDecimal in the stream to the running total.
  • Printing the Result: Finally, we print the total sum of the amounts.
package org.kodejava.stream;

import java.math.BigDecimal;
import java.util.Arrays;
import java.util.List;

public class BigDecimalSumExample {
    public static void main(String[] args) {
        // Create a list of transaction objects
        List<Transaction> transactions = Arrays.asList(
                new Transaction(new BigDecimal("10.50")),
                null,
                new Transaction(new BigDecimal("30.25")),
                new Transaction(null),
                new Transaction(new BigDecimal("11.49"))
        );

        // Sum the amount properties using stream
        BigDecimal totalAmount = transactions.stream()
                // Filter out null Transaction objects and Transaction objects
                // with null amounts
                .filter(t -> t != null && t.getAmount() != null)
                .map(Transaction::getAmount)
                .reduce(BigDecimal.ZERO, BigDecimal::add);

        // Print the result
        System.out.println("Total Amount: " + totalAmount);
    }

    static class Transaction {
        private final BigDecimal amount;

        public Transaction(BigDecimal amount) {
            this.amount = amount;
        }

        public BigDecimal getAmount() {
            return amount;
        }
    }
}

Below is another example, we want to sum just a List<BigDecimal> values. To sum the values we can use the reduce method as shown in the code snippet below.

package org.kodejava.stream;

import java.math.BigDecimal;
import java.util.Arrays;
import java.util.List;
import java.util.Objects;

public class BigDecimalListSumExample {
    public static void main(String[] args) {
        // Create a list of BigDecimal values
        List<BigDecimal> amounts = Arrays.asList(
                new BigDecimal("10.50"),
                new BigDecimal("20.75"),
                new BigDecimal("30.25"),
                null,
                new BigDecimal("11.49")
        );

        // Sum the BigDecimal values using stream
        BigDecimal totalAmount = amounts.stream()
                .filter(Objects::nonNull)
                .reduce(BigDecimal.ZERO, BigDecimal::add);

        // Print the result
        System.out.println("Total Amount: " + totalAmount);
    }
}

Using Java Stream API to sum a BigDecimal property of a list of objects or a list of BigDecimal values are both concise and efficient. The map and reduce methods streamline the process, making our code more readable and maintainable. This approach can be applied to various scenarios where we need to aggregate data from a list of objects.

How do I convert date string from one format to another format?

In the following code snippet we will see hot to change a date from one format to another format. For example from 2024-11-04 to 04-Nov-24 format in Java. We can use the DateTimeFormatter class from the java.time.format package to do the conversion.

The steps are:

  • Parse the original date string.
  • Format it to the desired pattern.

Here’s the complete code to do this:

package org.kodejava.datetime;

import java.time.LocalDate;
import java.time.format.DateTimeFormatter;

public class DateFormatConversion {
    public static void main(String[] args) {
        // The original date string
        String originalDate = "2024-11-04";

        // Define the input and output date formats
        DateTimeFormatter inputFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
        DateTimeFormatter outputFormatter = DateTimeFormatter.ofPattern("dd-MMM-yy");

        // Parse the original date
        LocalDate date = LocalDate.parse(originalDate, inputFormatter);

        // Format the date to the desired pattern
        String formattedDate = date.format(outputFormatter);

        // Print the formatted date
        System.out.println(formattedDate);
    }
}

Output:

04-Nov-24

In the code above we define two formatters, one for the original date format, and the second one is for the new date format. The input formatter matches the original date format (yyyy-MM-dd). The output formatter specifies the desired format (dd-MMM-yy).

We use the LocalDate.parse() method to parse the string of original date into a LocalDate object. Next, we use the LocalDate.format() method to convert into a new date format using the defined formatter object.

This approach uses java.time API introduced in Java 8, which is the recommended way to handle date and time in Java due to its immutability and thread-safety features.

How do I update records in MS Access database?

In this post, we will learn how to update records in a database using the Java Database Connectivity (JDBC) API. JDBC is a Java API which is used to connect and execute query in the database.

We will be working with an MS Access database file named musicdb.accdb for this example. Our goal is to update an album’s title and release date in our album table.

The following code snippet show you how to do it. The selectAlbum() method is just a helper method to show the album data before and after the data was updated.

package org.kodejava.jdbc;

import java.sql.*;
import java.time.LocalDate;
import java.time.Month;

public class MSAccessUpdate {
    public static void main(String[] args) {
        String url = "jdbc:ucanaccess://C:/Users/wayan/Temp/musicdb.accdb";
        try (Connection conn = DriverManager.getConnection(url)) {
            conn.setAutoCommit(false);
            selectAlbum(conn, 2L);

            String sql = """
                    update album
                        set title = ?,
                        release_date = ?
                    where id = ?
                    """;

            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, "A Hard Day's Night");
            ps.setDate(2, Date.valueOf(LocalDate.of(1964, Month.JULY, 10)));
            ps.setLong(3, 2);

            int rowCount = ps.executeUpdate();
            System.out.printf("%s (rows) updated.%n", rowCount);

            selectAlbum(conn, 2L);

            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * Executes a SQL query to select an album from the database based on the
     * provided ID.
     *
     * @param conn the Connection object representing the database connection
     * @param id   the ID of the album to be selected
     * @throws SQLException if a database access error occurs or the SQL query
     * is invalid
     */
    private static void selectAlbum(Connection conn, Long id) throws SQLException {
        String sql = "select * from album where id = ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setLong(1, id);

        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            System.out.printf("id=%s, title=%s, released_date=%s%n",
                    rs.getLong("id"), rs.getString("title"),
                    rs.getDate("release_date"));
        }
    }
}

The following is an output produced by the code snippet above:

id=2, title=With the Beatles, released_date=1963-11-22
1 (rows) updated.
id=2, title=A Hard Day's Night, released_date=1964-07-10

Maven Dependencies

<dependency>
    <groupId>io.github.spannm</groupId>
    <artifactId>ucanaccess</artifactId>
    <version>5.1.1</version>
</dependency>

Maven Central

How do I insert a record into Microsoft Access database and get the generated key?

In this tutorial, we’ll guide you through the process of inserting records into an MS Access Database. In the code snippet below we are using the UCanAccess JDBC driver, you can find the Maven dependencies at the end of this tutorial.

Setting Up The Connection

Firstly, we need to set up a connection to our MS Access database. This is done by specifying the path of your database file (.mdb or .accdb) in the JDBC URL and then getting a connection from DriverManager.

String url = "jdbc:ucanaccess://C:/Users/wayan/Temp/musicdb.accdb";
try (Connection connection = DriverManager.getConnection(url)) {
    ...
} catch (SQLException e) {
    e.printStackTrace();
}

Preparing the SQL Statement

Next step is to prepare our SQL insert statement. We use a PreparedStatement – it handles the tedious task of substituting the respective parameters into the placeholders ("?") in our SQL statement.

String sql = "insert into album (title, release_date, created) values (?, ?, ?)";

PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

Setting Parameters and Executing the SQL Statement

After defining our SQL statement with placeholders, we replace these placeholders with actual values using the setter methods (setInt(), setString(), setDate(), etc.) provided by the PreparedStatement class. Once, the placeholders have been replaced with actual values, we call executeUpdate() method on the PreparedStatement object to execute the SQL statement.

ps.setString(1, "With the Beatles");
ps.setDate(2, Date.valueOf(LocalDate.of(1963, Month.NOVEMBER, 22)));
ps.setTimestamp(3, Timestamp.valueOf(LocalDateTime.now()));
int rowCount = ps.executeUpdate();
System.out.println("rowCount = " + rowCount);

In the code snippet above we set all placeholders with values we want to insert into the database, and then the statement is executed. The new record gets added to the database. We also keep the rowCount returned by the executeUpdate() method.

Get the Generated Key Value

When creating the PreparedStatement object we also passes the Statement.RETURN_GENERATED_KEYS parameter, this will allow us to get the generated key from the statement object after executing the query.

ResultSet generatedKeys = ps.getGeneratedKeys();
long generatedKey = 1L;
while (generatedKeys.next()) {
    generatedKey = generatedKeys.getLong(1);
}
System.out.println("Generated Key = " + generatedKey);

The Entire Code Snippet

Here is the entire Java class.

package org.kodejava.jdbc;

import java.sql.*;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.Month;

public class MSAccessInsert {
    public static void main(String[] args) {
        String url = "jdbc:ucanaccess://C:/Users/wayan/Temp/musicdb.accdb";
        try (Connection connection = DriverManager.getConnection(url)) {
            String sql = "insert into album (title, release_date, created) values (?, ?, ?)";

            PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, "With the Beatles");
            ps.setDate(2, Date.valueOf(LocalDate.of(1963, Month.NOVEMBER, 22)));
            ps.setTimestamp(3, Timestamp.valueOf(LocalDateTime.now()));

            int rowCount = ps.executeUpdate();
            System.out.println("rowCount = " + rowCount);

            ResultSet generatedKeys = ps.getGeneratedKeys();
            long generatedKey = 1L;
            while (generatedKeys.next()) {
                generatedKey = generatedKeys.getLong(1);
            }
            System.out.println("Generated Key = " + generatedKey);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

The code above inserts a new record into the album table in the Access database. Remember to handle any SQLException that may be thrown while interacting with the database.

And that’s it! You have successfully inserted a record into an MS Access database using UCanAccess in Java. Whether you’re creating a small application or dealing with large-scale data management, UCanAccess is an easy, efficient way to work with MS Access databases in Java applications.

Maven Dependencies

<dependency>
    <groupId>io.github.spannm</groupId>
    <artifactId>ucanaccess</artifactId>
    <version>5.1.1</version>
</dependency>

Maven Central

How do I select a record from Microsoft Access Database?

UCanAccess is a pure Java JDBC Driver implementation which allows Java developers and JDBC client programs to read and write Microsoft Access database files (.mdb and .accdb).

In this tutorial, we will demonstrate how to configure a project with UCanAccess and create a simple Java application to select data from an MS Access database.

Establishing a Connection

Before you can interact with a database, you need to establish a connection to it. The following is an example of how to establish a connection using UCanAccess:

String url = "jdbc:ucanaccess://C:/Users/wayan/Temp/musicdb.accdb;";
try (Connection connection = DriverManager.getConnection(url)) {
    System.out.println("connection = " + connection);
    // Rest of the code goes here...
} catch (SQLException e) {
    e.printStackTrace();
}

In the code above, we create a connection URL using the absolute path to our Access database file. Then, we obtain a connection object by calling DriverManager.getConnection(url).

Fetching Data from the Database

Once the connection is established, we can execute SQL queries against the database. Here, let’s create a query to select some data:

String query = "select id, title, release_date from album where id = ?";

PreparedStatement ps = connection.prepareStatement(query);
ps.setLong(1, 1L);

ResultSet rs = ps.executeQuery();
while (rs.next()) {
    System.out.printf("id=%s, title=%s, released_date=%s%n",
            rs.getLong("id"), rs.getString("title"),
            rs.getDate("release_date"));
}

In this code, we create a PreparedStatement object, which enables us to execute parameterized SQL queries in a secure and efficient manner. Here, our SQL query includes a parameter, represented by the ? symbol, which we then set using setLong(). This would replace the ? with the value 1L.

We then execute our query by calling executeQuery() on the PreparedStatement object which returns a ResultSet object. This object represents the result set of the query.

We then loop through the result set and print each record using rs.next(), which is used to iterate through the ResultSet.

Full Code

package org.kodejava.jdbc;

import java.sql.*;

public class MSAccessSelect {
    public static void main(String[] args) {
        String url = "jdbc:ucanaccess://C:/Users/wayan/Temp/musicdb.accdb;";
        try (Connection connection = DriverManager.getConnection(url)) {
            System.out.println("connection = " + connection);

            String query = "select id, title, release_date from album where id = ?";

            PreparedStatement ps = connection.prepareStatement(query);
            ps.setLong(1, 1L);

            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                System.out.printf("id=%s, title=%s, released_date=%s%n",
                        rs.getLong("id"), rs.getString("title"),
                        rs.getDate("release_date"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Remember to handle SQL exceptions that might be thrown during the interaction with the database. And there you have it! Now you know how to work with MS Access Database using UCanAccess in Java. Happy coding!

Maven Dependencies

<dependency>
    <groupId>io.github.spannm</groupId>
    <artifactId>ucanaccess</artifactId>
    <version>5.1.1</version>
</dependency>

Maven Central