How does Table Grid Editor manage data source connections
Under the hood, Table grid Editor (TGE) uses Hibernate as an ORM and c3p0 connection pool manager to manage connections to databases (DBs).
Hibernate, SessionFactories, Connection Pools and Sessions
Hibernate allows to talk to a DB through Sessions, which are acquired from so called Session Factories. A SessionFactory defines where (to which DB) and how (via what connection, connection pooling properties) do the Sessions talk to DBs. Also each SessionFactory is assigned with a connection pool, from which the connections are acquired from. The Sessions allow one to execute queries to the DB.
In order to boost performance, TGE reuses the SessionFactories for the grids. For each unique grid configuration, the SessionFactory (and thus a connection pool as well as Hibernate configuration) is stored in memory. The SessionFactory store is called TGE Hibernate Session Factory Cache.
Each time a user opens a page with a TGE field, or uses JQL search with TGE JQL functions, or defines a datasource spec, a request is sent to the JIRA server, which then builds a grid according to the grid configuration (or datasource specification). When the grid is built, the configurations for hibernate (and thus to the connection pool manager) are created. Whenever the grid needs any data from it's datasources, the Hibernate Session Factories are looked up in TGE Hibernate Session Factory Cache:
if the SessionFactory is found, it is used to create Sessions to talk to the database. Also, the SessionFactory gets assigned with a timestamp, set to current time
if the SessionFactory is not found, it is created from the configurations and stored to the TGE Hibernate Session Factory Cache. Also, the SessionFactory gets assigned with a timestamp, set to current time
then, each of the SessionFactories (in the TGE Hibernate Session Factory Cache) is checked whether current time minus 15 minutes is more than the timestamp (was the SessionFactory used within the last 15 minutes)
if current time minus 15 minutes is lesser than or equal to the timestamp (SessionFactory was used within the last 15 minutes), nothing happens
if current time minus 15 minutes is more than the timestamp (SessionFactory was not used within the last 15 minutes), the SessionFactory is closed (which implicitly asks the connection pool manager to close connections) and removed from the TGE Hibernate Session Factory Cache.
then, the SessionFactory is used to create a Session to talk to a datasource. When the Session is closed, it releases the connection, so that other Sessions could use it.
The default Hibernate configuration is the following:
#############################################################
# Default Connection pooling properties
#############################################################
# Initial number of database connections
hibernate.c3p0.min_size=1
# Maximum number of database connections to open
hibernate.c3p0.max_size=50
#Seconds a Connection can remain pooled but unused before being discarded. Zero means idle connections never expire.
hibernate.c3p0.maxIdleTime=300
#the size of c3p0's global PreparedStatement cache.
hibernate.c3p0.maxStatements=500
#A Connection older than maxConnectionAge will be destroyed and purged from the pool.
hibernate.c3p0.maxConnectionAge=600
hibernate.c3p0.numHelperThreads=3
#Seconds before c3p0's thread pool will try to interrupt an apparently hung task.
hibernate.c3p0.maxAdministrativeTaskTime=20
hibernate.c3p0.acquireRetryAttempts=2
hibernate.c3p0.breakAfterAcquireFailure=true
# Idle time before a c3p0 pooled connection is validated (in seconds)
hibernate.c3p0.idle_test_period=3000
hibernate.show_sql=true
hibernate.format_sql = true
# automatic schema update
hibernate.hbm2ddl.auto=update
hibernate.default_entity_mode=dynamic-map
hibernate.current_session_context_class=thread
An administrator may override/ add/ any of the default Hibernate/ c3p0 configuration properties by adding the hibernate configuration properties as follows:
to the grid configuration:
Initial Grid configuration############################################ # General grid properties section #---------------------------------------- # gd.columns is used to define the columns which make up the grid # gd.tablename and gd.ds are used to specify where the data of the grid should be stored # It is possible to store the grid data in an external database # gd.columns=foo datasource.names=foobar ds.foobar.connection.type=mysql ds.foobar.connection.url=jdbc:mysql://localhost:3306/foo_bar ds.foobar.connection.username=foo ds.foobar.connection.password=bar gd.tablename=actions gd.ds=foobar ############################################ # Column section for the Foo column #---------------------------------------- # col.foo=FooConfiguration with overridden hibernate properties
############################################ # General grid properties section #---------------------------------------- # gd.columns is used to define the columns which make up the grid # gd.tablename and gd.ds are used to specify where the data of the grid should be stored # It is possible to store the grid data in an external database # gd.columns=foo datasource.names=foobar ds.foobar.connection.type=mysql ds.foobar.connection.url=jdbc:mysql://localhost:3306/foo_bar ds.foobar.connection.username=foo ds.foobar.connection.password=bar ## A NEW HIBERNATE PROPERTY ADDED (overrides the default hibernate.c3p0.max_size=50) ds.foobar.hibernate.c3p0.max_size=1 gd.tablename=actions gd.ds=foobar ############################################ # Column section for the Foo column #---------------------------------------- # col.foo=Footo the datasource specification:
Hibernate Properties field: