Reputation: 484
I want to create instances of a class which will have access to the underlying Embedded derby database and pass this class to each bundle binding to my database bundle using declarative services.
I have seen in the derby documentation that sharing one connection for multiple threads has many pitfalls. So I was thinking to create a connection for each instance of the class I am creating. Since I only want a very simple way to just create multiple connections and manage them, using "MiniConnectionPoolManager" here seems like a good option. The sample code for derby is shown below:
org.apache.derby.jdbc.EmbeddedConnectionPoolDataSource dataSource = new org.apache.derby.jdbc.EmbeddedConnectionPoolDataSource();
dataSource.setDatabaseName("c:/temp/testDB");
dataSource.setCreateDatabase("create");
MiniConnectionPoolManager poolMgr = new MiniConnectionPoolManager(dataSource, maxConnections);
...
Connection connection = poolMgr.getConnection();
...
connection.close();
But the documentation does not cover many things plus I am a beginner in using Database. My questions are:
When I am creating a new class that will need database connection to perform insert,update & other actions. Shall I pass the 'poolMgr' and call poolMgr.getConnection() from the newly created class?
When should I close this connection? I don't know for how long the bundle (user) will use the new class so shall I save the newly created connection in a private global variable and force the user to execute unregister class where I could then close the connection? Or shall I just close all connections when my database bundle is being deactivated.
Other suggestions are also appreciated to manage different classes accessing one database. Thank you in advance.
Edit:
The main class in my database bundle is always active as long as the application is running. It is the bundles requesting for an instance of a new class(performing database operation) that come and go. And also since it will be deployed in embedded system, I can only use small footprint applications.
Upvotes: 2
Views: 1283
Reputation: 6046
You should get a connection from a connection pool when you need it and close the connection as soon as you can. It is the job of the connection pool to re-use connections, not yours.
In other words: Do not keep a connection alive until your consumer bundle is deactivated.
Connection pools normally implement DataSource interface, you should use the pools via it. In that case you can replace the pool implementation easily without changing your code. E.g:
@Component
public class MyComponent {
// Connection pool based DataSource
@Reference
DataSource dataSource;
public void myFunction() {
try (Connection c = dataSource.getConnection()) {
// Database operations
} catch (SQLException e) {
// TODO
}
}
}
When you find yourself repeating the same code many times (getting connection, catching SQLException), you can write a simple component that accepts functional interfaces. E.g.:
@Component
@Service
public class SQLHelper {
@Reference // This is a connection pool DataSource
private DataSource dataSource;
public <R> R execute(Callback<R> callback) {
try (Connection c = dataSource.getConnection()) {
return callback.call(c);
} catch (SQLException e) {
throw new UncheckedSQLException(e);
}
}
}
Your functional interface would look like this:
public interface Callback<R> {
R call(Connection connection);
}
And you would use it like this:
sqlHelper.execute((Connection c) -> {
// Do some stuff with the connection
});
Using transactions
If you want to use atomic transactions, I suggest that you should use org.apache.derby.jdbc.EmbeddedXADataSource together with org.apache.commons.dbcp.managed.BasicManagedDataSource from commons-dbcp. After that, you can handle transactions via JTA.
It is hard to use the JTA API directly. You should choose a library that helps you propagating transactions.
A small guide based on Declarative Services:
Now you have everything to write your code. Your component would similar to the following:
@Component
@Service
public class MyComponent {
@Reference
private DataSource dataSource;
@Reference
private TransactionHelper th;
public void myFunction() {
th.required(() -> {
try (Connection c = dataSource.getConnection()) {
// My SQL statements
} catch (SQLException e) {
// TODO
}
}
}
}
In case you do not need transaction handling, you can:
A more complex guide (that also takes care of schema creation and uses OO based queries) is available at http://cookbook.everit.org/persistence/index.html.
Update
You do not have to get a connection for every SQL statement. You should get a connection, execute as many SQL statements that you can within a "moment" and than call close on the connection.
MiniConnectionPoolManager might be a great solution for embedded devices as it is really "mini". The only issue is that it does not implement the DataSource interface so your business code shuold directly use the MiniCPM classes. By doing that, it will be much harder to switch to other Connection pool if you find a bug or you need a more complex pool later.
If you decide to use MiniCPM, I suggest that you should write a component that implements DataSource and delegates the getConnection() function to a MiniCPM instance. E.g.:
@Component
@Service
public class MiniCPMDataSourceComponent implements DataSource {
@Reference
protected ConnectionPoolDataSource cpDataSource;
private MiniConnectionPoolManager wrapped;
@Activate
public void activate() {
this.wrapped = new MiniConnectionPoolManager(cpDataSource);
}
@Override
public Connection getConnection() {
return wrapped.getConnection();
}
@Override
public Connection getConnection(String user, String password) {
throw new UnsupportedOperationException();
}
@Deactivate
public void deactivate() {
wrapped.dispose();
}
}
You can decorate this component with configuration possibilities like the max connection number and timeout (that is supported by MiniCPM). If you use the service that is provided by this component, you will be able to switch the connection pool without changing your business code. Also, your business bundle will not be wired directly to MiniCPM.
Upvotes: 4