Can't move MS Access file after opening JDBC connection on it

Question

I've got a Spring Batch job that is reading a specified Access database file. I'm using uncanaccess JDBC library to do so. Once the job exits, whether the job completes successfully or not, I need to have my application move the access file to another folder. Currently I'm getting a java.nio.file.FileSystemException that states it can't be moved because it's used by another process. I'm assuming the other process is due to the fact that the JDBC connection was opened.

Exception in thread "main" com.mycompany.weeklyimport.FileException: Failed to move file [C:\temp\hold\temp.mdb] to [C:\temp\error\temp.mdb]
    at com.mycompany.weeklyimport.WeeklyImportApplication.moveFile(WeeklyImportApplication.java:365)
    at com.mycompany.weeklyimport.WeeklyImportApplication.main(WeeklyImportApplication.java:91)
Caused by: java.nio.file.FileSystemException: C:\temp\hold\temp.mdb -> C:\temp\error\temp.mdb: The process cannot access the file because it is being used by another process.

    at sun.nio.fs.WindowsException.translateToIOException(WindowsException.java:86)
    at sun.nio.fs.WindowsException.rethrowAsIOException(WindowsException.java:97)
    at sun.nio.fs.WindowsFileCopy.move(WindowsFileCopy.java:387)
    at sun.nio.fs.WindowsFileSystemProvider.move(WindowsFileSystemProvider.java:287)
    at java.nio.file.Files.move(Files.java:1395)
    at com.mycompany.weeklyimport.WeeklyImportApplication.moveFile(WeeklyImportApplication.java:363)
    ... 1 more

Here's my main program (that runs the Spring job via Spring Boot):

@SpringBootApplication
@EnableBatchProcessing
@Slf4j
public class WeeklyImportApplication extends DefaultBatchConfigurer {
    ...
    private static String inputFile;
    private static boolean exceptionEncountered = false;

    public static void main(String[] args) throws Throwable {
        handleArguments(args);

        ConfigurableApplicationContext context = new SpringApplicationBuilder(WeeklyImportApplication.class).listeners(new CustomLoggingConfigurationApplicationListener(logConfigurer)).run(args);

        if (exceptionEncountered) {
            moveFile("error");
        } else {
            moveFile("complete");
        }

        finished();
    }

    private static void moveFile(String folderName) {
        File file = new File(inputFile);
        File newPath = new File(file.getParentFile().getParentFile().getPath() + File.separator + folderName);

        if (!newPath.exists()) {
            if (!newPath.mkdirs()) {
                throw new FileException("Failed to create folder [" + newPath.getPath() + "]");
            }
        }

        File newFile = new File(newPath.getPath() + File.separator + file.getName());

        try {
            Files.move(file.toPath(), newFile.toPath(), StandardCopyOption.REPLACE_EXISTING);
        } catch (IOException ex) {
            throw new FileException("Failed to move file [" + file.getPath() + "] to [" + newFile.getPath() + "]", ex);
        }
    }

    ...

My datasource configuration. I'm assigning to a static variable as well to attempt to close the connection once Spring exits.

@Configuration
public class DataSourceConfiguration {

    public static SingleConnectionDataSource legacyDataSource;

    @Bean(name = "importDataSource")
    public DataSource importDataSource() {
        SingleConnectionDataSource dataSource = new SingleConnectionDataSource();
        dataSource.setDriverClassName(this.importDriver.trim());
        dataSource.setSuppressClose(true);
        dataSource.setUrl("jdbc:ucanaccess://" + WeeklyImportApplication.getInputFile());

        return dataSource;
    }

    ...

I've tried the following:

    DataSourceConfiguration.legacyDataSource.getConnection().close();
    DataSourceConfiguration.legacyDataSource.destroy();
    DataSourceConfiguration.legacyDataSource = null;

Somehow, something still has a lock on that file. Has anyone encountered anything like this or have any ideas on how to force the true close of the file read?

SOLVED

jamadei's answer below helped me get to this solution. Relevant solution code:

@Bean(name = "importDataSource")
public DataSource importDataSource() {
    SingleConnectionDataSource dataSource = new SingleConnectionDataSource();
    dataSource.setDriverClassName(this.importDriver.trim());
    dataSource.setSuppressClose(true);
    dataSource.setUrl("jdbc:ucanaccess://" + WeeklyImportApplication.getInputFile() + ";SingleConnection=true");

    importDataSource = dataSource;

    return dataSource;
}

public static void main(String[] args) throws Throwable {
    handleArguments(args);

    new SpringApplicationBuilder(WeeklyImportApplication.class).listeners(new CustomLoggingConfigurationApplicationListener(logConfigurer)).run(args);

    DataSourceConfiguration.importDataSource.setSuppressClose(false);
    DataSourceConfiguration.importDataSource.destroy();

    if (exceptionEncountered) {
        moveFile("error");
        System.exit(1);
    } else {
        moveFile("complete");
    }

    finished();
}

jamadei · Accepted Answer · 2015-02-19T17:45:00.907

3

It's not just a Spring matter but a UCanAccess optimisation/cache side effect. What you have done seems to be good, but it's not enough. The use of the SingleConnection=true parameter in the jdbc url should solve the issue.

edited Feb 19 '15 at 17:45

answered Feb 19 '15 at 17:21

jamadei

1,700
9
8

This helped me get to the solution. I had inadvertently posted the wrong datasource in the question above, so I edited and corrected. One other thing I was doing was I had the `setSuppressClose` option on the `DataSource` set to true. Once I set removed that statement AND applied your suggested parameter to the URL then I was able to move the file within my application. Thank you for your help. – IceBox13 Feb 19 '15 at 19:09
As a followup, once I took my exception out that I was throwing for testing purposes, I encountered an exception thrown from Spring due to the datasource being closed on a subsequent step that also needed that datasource, so I had to set suppress close back to true. I got around this at the shutdown of my application by setting it back to false and then calling `destroy()` on the datasource, prior to doing the file move. – IceBox13 Feb 19 '15 at 19:37

Can't move MS Access file after opening JDBC connection on it

1 Answers1