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

How to recursively rename files with a specific suffix in Java?

The following code snippet show you how to recursively rename files with a specific suffix. In this example we are renaming a collection of resource bundles files which ends with _in.properties into _id.properties. The code snippet also count the number of files affected by the process. We use the Files.move() method to rename the file, if you want to copy the files instead of renaming them, then you can use the Files.copy() method.

Here is the complete code snippet:

package org.kodejava.io;

import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Stream;

public class RenameResourceBundles {
    public static void main(String[] args) {
        String startDirectory = "C:/Projects/Hello";
        AtomicInteger counter = new AtomicInteger(0);

        try (Stream<Path> paths = Files.walk(Paths.get(startDirectory))) {
            paths.filter(Files::isRegularFile)
                    .filter(path -> path.toString().endsWith("_in.properties"))
                    .forEach(path -> renameFile(path, counter));
        } catch (IOException e) {
            e.printStackTrace();
        }

        System.out.println("Total files renamed: " + counter.get());
    }

    private static void renameFile(Path path, AtomicInteger counter) {
        try {
            String newName = path.toString().replace("_in.properties", "_id.properties");
            Path newPath = Paths.get(newName);
            Files.move(path, newPath);
            System.out.println("Renamed: " + path + " to " + newPath);
            counter.incrementAndGet();
        } catch (IOException e) {
            System.out.println("Failed to rename: " + path);
            e.printStackTrace();
        }
    }
}

This code will recursively search through all subdirectories starting from the specified root directory and rename any files that end with _in.properties to _id.properties. The process prints the renamed file, and finally outputs the total number of files that were successfully renamed after traversing the directory tree.

The explanation of the code snippet above:

  • The Files.walk method is used to traverse the directory tree starting from the given directory.
  • The filter method is used to select only regular files that end with _in.properties.
  • The renameFile method handles the renaming of each file, replacing _in.properties with _id.properties.
  • An AtomicInteger named counter keeps track of the number of files renamed. AtomicInteger is used to handle the count in a thread-safe manner, which is useful if the code is ever modified to use parallel streams or multi-threading.
  • Inside the renameFile method, counter.incrementAndGet() is called each time a file is successfully renamed. This increments the counter by one.
  • After the Files.walk operation, the total number of renamed files is printed using System.out.println("Total files renamed: " + counter.get());.

What are the benefits of using parameterized log messages?

When creating a program in Java, we are mostly, if not always, add logging mechanism in our program. This log can be use to add debug information to our program, which can help us when problem occurs in our program. We usually add log message to the start of our methods to show the value of input parameters, or at the end of the methods before exiting to show the process results. We also add log information in the catch block of the try-catch statement to log messages related to exception that occurs in our program.

Here’s an example to illustrate the difference:

Concatenation approach:

logger.info("User " + username + " logged in at " + loginTime);

Parameterized approach:

logger.info("User {} logged in at {}", username, loginTime);

In Java, using parameterized log messages instead of concatenation offers several benefits:

Performance

Parameterized log messages can improve performance compared to string concatenation. When you concatenate strings, the JVM creates a new string object each time, which can lead to unnecessary memory allocation and garbage collection overhead. Parameterized messages, on the other hand, only evaluate the placeholders when the log message is actually logged, which can reduce memory usage and improve performance.

Readability

Parameterized log messages often result in cleaner and more readable code. By separating the log message template from the actual values, it’s easier to understand the intent of the log message and identify the dynamic values being logged.

Internationalization (i18n) and Localization (l10n)

Parameterized log messages make it easier to support internationalization and localization efforts. Since the placeholders in the log message template remain the same regardless of the language, translators can focus solely on translating the template and not worry about the dynamic values.

Prevention of unnecessary string manipulation

When concatenating strings for log messages, you may inadvertently perform unnecessary string manipulation operations (e.g., converting non-string values to strings). With parameterized messages, these operations are only performed if the log message is actually logged, reducing unnecessary computation.

Avoidance of potential formatting issues

When concatenating strings, you may encounter formatting issues, especially if the dynamic values contain special characters or formatting codes. Parameterized messages handle formatting automatically, ensuring that the logged values are properly formatted according to their data types.

Overall, using parameterized log messages can lead to more efficient, readable, and maintainable code in Java logging practices.

Below is another ilustration of adding log messages in our program:

package org.kodejava.util.logging;

import java.util.logging.Level;
import java.util.logging.Logger;

public class ExampleLogger {

    private static final Logger logger = Logger.getLogger(ExampleLogger.class.getName());

    public void performLogin(String username, String loginTime) {
        // Simulate login process
        boolean loginSuccessful = true; // Assume login is successful for demonstration

        if (loginSuccessful) {
            // Log successful login using parameterized message
            logger.log(Level.INFO, "User {0} logged in at {1}", new Object[]{username, loginTime});
        } else {
            // Log failed login using parameterized message
            logger.log(Level.WARNING, "Failed login attempt for user {0} at {1}", new Object[]{username, loginTime});
        }
    }

    public static void main(String[] args) {
        ExampleLogger exampleLogger = new ExampleLogger();
        exampleLogger.performLogin("john_doe", "2024-06-08 10:00:00");
    }
}

In this example:

  • We define a performLogin method that simulates a user login process. The method takes username and loginTime as parameters.
  • Inside the method, we set a boolean variable loginSuccessful to true for demonstration purposes (assuming the login is successful).
  • We then use the java.util.logging.Logger class to log the login event. We use parameterized log messages with placeholders {0} and {1} for username and loginTime, respectively.
  • Depending on whether the login is successful or not, we log the event at different levels (INFO for successful login and WARNING for failed login).
  • In the main method, we create an instance of ExampleLogger and call the performLogin method with sample values for username and loginTime.

This example demonstrates the usage of parameterized log messages in a complete method for logging login events in Java.