I am developing a system which uses a database access object (DAO) to link between the program and database. All connections must be made through this class, using getConnection(). However, I have a reset option which overrides the database file in the user's file system with one inside the application itself (i.e. in its jar when deployed). This reset() feature causes an exception saying that the database is already in use.
java.nio.file.FileSystemException: C:\iMProve\improveDB.accdb: The process cannot access the file because it is being used by another process.
With research, this means that the connections have not been closed properly. However, I will show you the whole code for this DAO class if you can spot possibly where lies the problem.
public class DAO {
private static final String dbDir = "C://iMProve";
private static final String dbName = "improveDB.accdb";
private static final String dbUrl = "jdbc:ucanaccess://" + dbDir + "//"+ dbName;
private ObservableList<Connection> allConnections = FXCollections.observableArrayList();
public DAO() { //constructor - called when object is made
try {
Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
} catch(ClassNotFoundException e) {
System.out.println("Cannot load ucanaccess driver");
e.printStackTrace();
}
File directory = new File(dbDir);
if(!directory.exists()) //create directory if not already
directory.mkdir();
File database = new File(dbDir + "//" + dbName);
if(!database.exists()) { //copy the database file into user's file system - if not already
try {
Files.copy(DAO.class.getResourceAsStream(dbName), database.toPath(), StandardCopyOption.REPLACE_EXISTING);
} catch(IOException ex) {ex.printStackTrace();}
}
}
public void reset() {
File database = new File(dbDir + "//" + dbName);
try {
Files.copy(DAO.class.getResourceAsStream(dbName), database.toPath(), StandardCopyOption.REPLACE_EXISTING);
} catch (IOException e) {
e.printStackTrace();
}
}
public Connection getConnection() { //create a connection to the database
Connection conn = null;
try {
conn = DriverManager.getConnection(dbUrl);
} catch (SQLException e) {
e.printStackTrace();
}
allConnections.add(conn);
return conn;
}
public void closeConnections() {
for(Connection conn: allConnections) {
if(conn!=null) {
try {
conn.close();
System.out.println("closed");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
}
The constructor simply copies the database from the application into the user's file system if not there already.
The getConnection() method connects to the database.
Calling reset() on its own produces that error, so by adding the connections to a static collection we can closing each and every connection, using closeConnections().
Note: These connections should already be closed anyways because we always a try-with-resources. For example:
try (
Connection conn = dao.getConnection();
PreparedStatement stmt = conn.prepareStatement("SELECT Title FROM Theory WHERE Grade <= ?");
) {
And although we have two levels for closing connections
- try-with-resources
closeConnections()method
They still are ineffective. When calling closeConnections(), we still get outputs of
closed
closed
even when connections have been used inside try-with-resources blocks.
And even with closeConnections(), we still get the same error:
java.nio.file.FileSystemException: C:\iMProve\improveDB.accdb: The process cannot access the file because it is being used by another process.
