Does creating and closing database connections each time I need them have any effect on application performance?

I recently faced an

org.sqlite.SQLiteException: [SQLITE_BUSY] The database file is locked (database is locked)

error on my desktop application because I think I used a single connection to read and write data across my methods. So, I changed from using a single connection to creating a new connection each time I was reading and writing to my application. For example, in each method for reading my data, I did:

try {
    Connection connection = DriverManager.getConnection(url);
    // rest of my code
    connection.close();
}
catch (SQLException e) {
    e.printStackTrace();
}

Note, that it fixed my issue.

My question is, are there any effects of doing what I just described above regarding my app getting performance issues in the future? Is it a good practice to create a new connection even in the smallest modifications? Finally, is there any benefits or issues as the app grows to heavy database operations ?

Reading and writing data also required to close resources such as Statement and ResultSet. These resources needs to be closed after each query. If you forget to close resources then you face a resource leaks which is a case of the memory leaks problem. Each time you make a query to the database it is openning a transaction and ends it after the statement is executed if the auto-commit mode is on.

The Connection object should be closed at the end of the method. If you didn't close connection it will stay active and keep memory, it imposes a memory leaks (also known as resource leaks). In the java 8 and above this object implements AutoClosable interface and can be used with try-with-resources statement. The same is related to Statement and ResultSet that you should close accordingly. But if you close the connection explicitly then these objects are closed also.

try (Connection connection = db.getConnection();) {
 //your code here                         
}catch(SQLException e) {
 db.getErrorMessages(e);
}     

Transactions lock the database resources but they can impose a deadlock problem if the same resources are used by different threads. To avoid these and many other problems, that you may encounter while using JDBC, use connection per thread pattern and close resources after their usage.

You have two options:

1. Get a new connection each time you perform a query and close it afterwards along with all resources.
2. Use a data source to get a connection. This requires to configure a connection pool to your application.

Which method you use depends on user requirements of your application.