samedi 3 octobre 2015

ormlite JdbcPooledConnectionSource correct usage

We are developing a new desktop application in JavaFx wherein for offline storage we are using SQLite and for orm we are using ormlite.

I want to implement DB connection pooling wherein a fixed number of connections should be set at the start and should be used, released and reused as required. Also, it would be good if we can make use of "readonly" and "writeonly" connections appropriately to maximize performance.

This is what we have written so far.

public class DAO {
    private static JdbcPooledConnectionSource connectionSource;

    private static DAO instance = null;

    private DAO() throws SQLException {
        try {
            final File path = SystemUtils.getDatabaseFile();
            final String DATABASE_URL = Constants.DATABASE_URL + path.getAbsolutePath();

            Class.forName(Constants.DATABASE_DRIVER);

            connectionSource = new JdbcPooledConnectionSource(DATABASE_URL);
            //connectionSource.setMaxConnectionAgeMillis(5 * 60 * 1000);
            connectionSource.setCheckConnectionsEveryMillis(5000);
            connectionSource.setMaxConnectionsFree(5);
            connectionSource.initialize();
            init();
        } catch (ClassNotFoundException cnfe) {
            cnfe.printStackTrace();
        } catch (IOException ex) {
            ex.printStackTrace();
        }   
    }

    private void init() throws ClassNotFoundException, SQLException {
        TableUtils.createTableIfNotExists(connectionSource, Customer.class);
        TableUtils.createTableIfNotExists(connectionSource, Address.class);
        TableUtils.createTableIfNotExists(connectionSource, Location.class);
        TableUtils.createTableIfNotExists(connectionSource, City.class);
        TableUtils.createTableIfNotExists(connectionSource, Area.class);
        TableUtils.createTableIfNotExists(connectionSource, Category.class);
        TableUtils.createTableIfNotExists(connectionSource, Product.class);
        TableUtils.createTableIfNotExists(connectionSource, AddonCategory.class);
        TableUtils.createTableIfNotExists(connectionSource, ProductAddon.class);

    }

    public synchronized <D extends Dao<T, ?>, T> D getDao(Class<T> cls) throws SQLException {
        Dao<T, ?> dao = DaoManager.createDao(connectionSource, cls);
        D daoImpl = (D) dao;

        return daoImpl;
    }

    public synchronized static DAO getInstance() throws SQLException {
        if (instance == null) instance = new DAO();
        return instance;
    }
}

The problem here is everytime we are creating table (TableUtils.createTableIfNotExists) the pooled connection source is making a new connection and not reusing the one earlier used/created.

Not finding enough code examples on Internet on how to correctly use JdbcPooledConnectionSource.

Aucun commentaire:

Enregistrer un commentaire