Database Library

[violet timber]

I'm looking to create or use an existing library that could simplify Database usage in plugins. Here is the list of the features I would like this library to have:
1 - Included like any maven/gradle dependency
2 - Connection information configurable by server owners within yaml/json/properties file inside /plugins/TheNameOfThePlugin/ folder.
3 - Support multiple database types (MySQL, H2, ...)
4 - Preset if no connection information configured: H2 local database
5 - Caching system
6 - Abstract DAO/Repository with a lot of method like "findBy" in order to write queries easily and quickly
7 - Transaction management to avoid having to manually handle transactions and concurrency.
8 - Database schema automatically created from @Entity classes
9 - Ability to register some Migration Scripts (extending some abstract class/interface with 2 methods: String getVersion() and void execute()) that will be automatically executed in asc order if the database version (a string saved in a config table), is lower that current migration script version.
10 - Of course, since it should be a library (and not a plugin), developers should be able to create @Entity class and DAO/Repositories in the plugin code itself (In other words they should be able to extends the library's database schema without any issue).

[wraith frost]

Hibernate

[violet timber]

Hibernate does not answer all those needs.

I started to create this library myself (since I could not find an already existing one) and, using hibernate and what I coded, I managed to do :
1, 2, 3, 4, 5, 8, 9, 10.
But I have big troubles with:
6 and 7.

I tried to use https://github.com/mohsen/jpa-generic-dao but I did not manage to handle transaction lifecycle correctly.

[wraith frost]

7 should be easy with hibernate. Auto persisting is a thing already. You yourself dont have to query any statements at all.

Hibernate abstracts all the database stuff away. You should not need to write any SQL statement yourself

[violet timber]

hsql is still a bit painful over time. That's why I started using this abstract DAO over hibernate. But this is where it started to be painful because :

• The transactions were not commited.
• A TransactionManager should probably be used to avoid concurrency issues when doing async requests.

Maybe the issue I'm facing could be quickly fixed but I don't understand transaction management well enough to find its cause and to fix it.

[thick verge]

I don't think such a single end-all-be-all library exists - even outside of the bukkit/spigot ecosystem. for the base jpa and orm you have hibernate (1), 3), 5) 8), 10)), for 9) you have flyway or liquidbase, for 6) & 7) you have spring data

while hibernate + probably also flyway/liquidbase will work in a spigot environment, spring obviously doesn't.

I could suggest you JOOQ which, while not providing DAOs directly, should make it a lot more straight forward

also "caching" here really depends on the viewpoint. Statement caching? result cache? object cache? hibernate+hikari cover some of these, but there still might be application level caching in front of them depending on your needs

[faint mauve]

Flyaway + HikariDB

[violet timber]

So I managed to do 6 and 7

I still have an issue when reloading plugins

maybe I will switch from c3p0 to HikariCP because of it ...

hibernate configuration:

{
  "hibernate.connection.driver_class": "org.h2.Driver",
  "cache.provider_class": "org.hibernate.cache.NoCacheProvider",
  "hibernate.connection.provider_class": "org.hibernate.c3p0.internal.C3P0ConnectionProvider",
  "hibernate.hbm2ddl.auto": "update",
  "hibernate.c3p0.max_size": "10",
  "hibernate.dialect": "org.hibernate.dialect.H2Dialect",
  "hibernate.c3p0.privilegeSpawnedThreads": "true",
  "hibernate.c3p0.debugUnreturnedConnectionStackTraces": "true",
  "hibernate.c3p0.min_size": "0",
  "hibernate.connection.password": "",
  "hibernate.connection.username": "",
  "hibernate.connection.url": "jdbc:h2:.\\plugins\\DatabaseLibTest\\database\\database;INIT\u003dCREATE SCHEMA IF NOT EXISTS PUBLIC",
  "hibernate.c3p0.timeout": "500",
  "hibernate.connection.autocommit": "false",
  "hibernate.show_sql": "false",
  "hibernate.current_session_context_class": "thread",
  "hibernate.c3p0.contextClassLoaderSource": "library",
  "hibernate.c3p0.unreturnedConnectionTimeout": "30"
}

    /**
     * Should be called when disabling DatabaseLib
     */
    public void onDisable() {
        if (sessionFactory != null) {
            if (sessionFactory.isOpen()) {
                getLogger().info("Retrieving datasource ...");
                DataSource dataSource = sessionFactory.getSessionFactoryOptions().getServiceRegistry().getService(ConnectionProvider.class).unwrap(DataSource.class);
                if (dataSource instanceof PooledDataSource) {
                    PooledDataSource pds = (PooledDataSource) dataSource;
                    try {
                        getLogger().info("Closing datasource ...");
                        pds.close();
                    } catch (SQLException e) {
                        throw new IllegalStateException("Could not close datasource connections", e);
                    }
                } else {
                    getLogger().severe("Not a c3p0 connection pool.");
                }
                getLogger().info("Closing session factory ...");
                sessionFactory.close();
                sessionFactory = null;
            }
        }
        libsPerPlugin.remove(this.plugin.getName());
        getLogger().info("DatabaseLib successfully disabled.");
    }

When stopping the server. A trace is created because c3p0 seems to try to close a datasource that is already close.
I tried not to close it but then I have another issue ...

I will definitly change from c3p0 to HikariCP hoping it will work better

[violet timber]

So funny how it was easy with HikariCP while it was a pain with c3p0

Closing thread