Connection Pools - Xyna-Factory/xyna GitHub Wiki

Table of Contents General Information Add a new Connection Pool in Xyna Factory Examples Administrate Connection Pools Get Information about Connection Pools Connection Pools in Code Snippets

The term "Connection Pooling" generally means the re-usage of physical database connections. As building up a database connection is a resource intensive operation it is better to keep open connections in a cache (Connection Pool). Instead of opening and closing physical connections to the database for every request, the server can access already established connections from the pool (logical connection). With this method relatively many clients can work on few database connections. Connection Pools in Xyna Factory are managed by CLI-Commands. This article will explain most of them but they can also be viewed on this page.

The following CLI-Command adds a new connection pool to Xyna Factory:

 ./xynafactory.sh '''addconnectionpool''' -connectstring <arg> -name <arg> 
      -password <arg> -size <arg> -type <arg> -user <arg> 
      [-retries <arg>] [-pooltypespecifics <args>]

This is an example of the command with filled in arguments:

 ./xynafactory.sh addconnectionpool -connectstring 10.0.10.62:3306:xyna -type MySQL 
      -name CONNCETION_POOL_NAME -password xyna -user xyna -size 3 
      -retries 1 -pooltypespecifics connectTimeout=10 socketTimeout=300 validationTimeout=10

The command sets up a connection with the database named "xyna" on the machine with the address "10.0.10.62" that listens to port "3306". The connectstring can vary depending on the type of database you use. The same command would look like this for an Oracle database:

 ./xynafactory.sh addconnectionpool -connectstring jdbc:oracle:thin:@10.0.10.62:3306:xyna -type Oracle
      -name CONNECTION_POOL_NAME -password xyna -user xyna -size 3 
      -retries 1 -pooltypespecifics connectTimeout=10 socketTimeout=300 validationTimeout=10

In order to start, shutdown or remove a Connection Pool, use the following commands:

 ./xynafactory.sh '''startconnectionpool''' -name <arg>
 ./xynafactory.sh '''shutdownconnectionpool''' [-n] -name <arg>
 ./xynafactory.sh '''removeconnectionpool''' [-n] -name <arg>

To modify an existing Pool, use:

 ./xynafactory.sh '''modifyconnectionpool''' [-connectstring <arg>] [-f] -name <arg> [-password <arg>]

It is possible to refresh a Connection Pool. This will close and recreate all connections in the specified Pool. To refresh a Connection Pool, use:

 ./xynafactory.sh '''refreshconnectionpool''' -connectionPoolName <arg> [-f]

In order to aquire a list of Connection Pool Types that are ready to use by Xyna Factory, type:

 ./xynafactory.sh '''listconnectionpooltypes'''

This command will show you a list of all Connection Pools and information about the type, state and size of the Pool:

 ./xynafactory.sh '''listconnectionpools'''

This command shows you more detailed information:

 ./xynafactory.sh '''listconnectionpoolinfo'''

Shows you detailed information about every connection in all Connection Pools:

 ./xynafactory.sh '''listconnectionpoolstatistics'''

There are different ways in Xyna that allow you to access data from a database, e.g. Query-Services. It is also possible to directly use a connection of a Connection Pool in a Code Snippet:

<syntaxhighlight lang="java"> //First, instantiate a new SQLUtilsLogger com.gip.xyna.utils.db.SQLUtilsLogger sqlUtilsLogger = new com.gip.xyna.utils.db.SQLUtilsLogger() { private Exception lastException; public void logException(Exception e) { lastException = e; internalLog(org.apache.log4j.Level.ERROR, e.getClass().getName() + ": " + e.getMessage(), e); throw new RuntimeException(e); } public void logSQL(String sql) { internalLog(org.apache.log4j.Level.DEBUG, sql, null); } private void internalLog(org.apache.log4j.Level level, String str, Throwable t) { logger.log(com.gip.xyna.utils.db.SQLUtils.class.getName(), level, str, t); } }; //Instantiate the class SQLUtils com.gip.xyna.utils.db.SQLUtils sqlUtils = null; //This list will be filled with the XMOM-Datatype you want List<MyType> myTypeList = new ArrayList<MyType>(); try { //Get the Instance of the Connection Pool Management com.gip.xyna.xnwh.pools.ConnectionPoolManagement poolMgmt = com.gip.xyna.xnwh.pools.ConnectionPoolManagement.getInstance(); //Get the Connection Pool you need com.gip.xyna.utils.db.ConnectionPool pool = poolMgmt.getConnectionPool("MyPool"); String clientinfo = "clientinfo"; long timeoutMillis = 10000; //Identify the SQLUtils Instance with your Connection Pool sqlUtils = new com.gip.xyna.utils.db.SQLUtils(pool.getConnection(timeoutMillis, clientinfo), sqlUtilsLogger); if(sqlUtils == null) { throw new RuntimeException("SQLUtils null"); } try { //The query-method fills the list with all entries from the table "tableName" of the database "databasename". //You can modify the sql-String to your needs myTypeList=sqlUtils.query("Select * from databaseName.tableName)", new com.gip.xyna.utils.db.Parameter(), new com.gip.xyna.utils.db.ResultSetReader() { public MyType read(java.sql.ResultSet rs) throws java.sql.SQLException { MyType mt = new MyType(); MyType.setFirstMember(rs.getString("FIRSTMEMBER")); MyType.setSecondMember(rs.getString("SECONDMEMBER")); return mt; } }); } finally { sqlUtils.closeConnection(); } } catch (Exception e) { throw new RuntimeException(e); } </syntaxhighlight>