Apr 24, 2018 6:31:04 PM Thomas Dumont avatar   133

Access to data

The connection pools and db.properties

The declaration of a connection pool is made in the db.properties file located in the WEB-INF/conf directory.

By default, Lutece has a portal connection pool on the database containing portal data.

#portal.poolservice is not a mandatory property
portal.poolservice=fr.paris.lutece.util.pool.service.LuteceConnectionService
portal.driver=com.mysql.jdbc.Driver
portal.url=jdbc:mysql://localhost/lutece?autoReconnect=true&useUnicode=yes&characterEncoding=utf8
portal.user=root
portal.password=motdepasse
portal.initconns=2
portal.maxconns=50
portal.logintimeout=2
portal.checkvalidconnectionsql=SELECT 1
# <pool>.dialect is an optional property to specify the dialect for JPA provider.
#portal.dialect=org.hibernate.dialect.MySQLDialect

It is possible to declare other connection pools to other databases for some plugins.

Each connection pool can use the Lutece connection service, or the Tomcat server connection service :

Lutece connection service

db.properties

# Pool for a specific plugin database

<pool_name>.poolservice=fr.paris.lutece.util.pool.service.LuteceConnectionService
<pool_name>.driver=com.mysql.jdbc.Driver
<pool_name>.url=jdbc:mysql://localhost/quiz?autoReconnect=true&useUnicode=yes&characterEncoding=utf8
<pool_name>.user=
<pool_name>.password=
<pool_name>.initconns=1
<pool_name>.maxconns=3
<pool_name>.logintimeout=2000
<pool_name>.checkvalidconnectionsql=SELECT 1

The properties of a pool are as follows :

Name Description
poolservice (optional) The name of the Lutece class that will handle the connection requests on the pool. By default the service is that of Lutece, but it can be replaced by the service using the Tomcat pools: fr.paris.lutece.util.pool.service.TomcatConnectionService
driver The name of the JDBC driver class
url The JDBC connection URL to the database including the protocol, the port number, the name of the server and the base
user The name of the user of the database
password The password of the user of the database
initconns The number of connections to open when the pool is created
maxconns The maximum number of connections on the pool
logintimeout The delay in milliseconds to abort an attempt to connect to the database.
checkvalidconnectionsql (optional) This parameter contains the SQL query that verifies that a pool connection is valid. The default value is SELECT 1 which works for MySQL. For Oracle, this parameter must be set with the following query: SELECT SYSDATE FROM dual.

Tomcat connection service

db.properties

# Pool for a specific plugin database

<pool_name>.poolservice=fr.paris.lutece.util.pool.service.TomcatConnectionService
<pool_name>.ds=jdbc/<resource_name>

The properties of a pool are as follows :

Name Description
poolservice The name of the Lutece class that will handle the connection requests on the pool
ds The name of the resource corresponding to the pool in the Tomcat context

Tomcat configuration

In the Tomcat server.xml file (TOMCAT_HOME/conf), you have to add the Context tag :

<Server>
  …
  <Service>
     …
     <Engine>
        …	
	<Host>
    	   …	
	   <Context docBase="<absolute_path>" path="<context_name>" reloadable="true" >
               …   
	   </Context> 
   	</Host>
     </Engine>
  </Service>
</Server>
  • <absolute_path> is the absolute path of the webapp of the site (ex: "TOMCAT_HOME\webapps\lutece"),
  • <context_name> is the name of the context for Tomcat (ex: "/lutece").

For each existing connection pool, you must also add the following lines inside the <Context> ... </Context> tag defined above :

<Resource name="jdbc/<resource_name>"
                 auth="Container"
                 type="javax.sql.DataSource"
                 username="<user_name>"
                 password="<password>"
                 driverClassName="com.mysql.jdbc.Driver"	   
                 url="jdbc:mysql://<database_url>/<database_name>?autoReconnect=true&autoReconnectForPools=true&zeroDateTimeBehavior=convertToNull&useUnicode=yes&amp;characterEncoding=utf8"
                 initialSize="2"
                 maxActive="20"
                 removeAbandoned="true"
                 logAbandoned="true"
                 removeAbandonedTimeout="300"
/>
  • <resource_name> is the name of the resource defined for the particular pool in the db.properties file,
  • <user_name> is the login name for the database,
  • <password> is the login password for the database,
  • <database_url> is the access URL to the database server,
  • <database_name> is the name of the database.

The DAO pattern

As described in the General Architecture document, persistence in a DBMS is provided by objects that respect the DAO pattern defined in the J2EE specification. The implementation of a DAO is carried out using DAOUtil utility class .

DAOUtil

The DAOUtil utility class is used to create objects that provide an interface for all data access operations:

  • Retrieving a connection from a pool
  • Preparing an SQL statement
  • Definition of the variable elements of the SQL order
  • Execution of the order
  • Recovery and possible course of the results
  • Delivery of the connection in the poll

Here is an example of using a DAOUtil object:

private static final String SQL_QUERY_SELECT = "SELECT id_contact, description, email, contact_order FROM contact WHERE id_contact = ? ";

...                              
Contact load( int nContactId , Plugin plugin )
{
        DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT , plugin );
        daoUtil.setInt( 1 , nContactId );
        daoUtil.executeQuery();

        Contact contact = null;

        if ( daoUtil.first(  ) )
        {
            contact = new Contact();
            contact.setId( daoUtil.getInt( 1 ) );
            contact.setName( daoUtil.getString( 2 ) );
            contact.setEmail( daoUtil.getString( 3 ) );
            contact.setContactOrder( daoUtil.getInt( 4 ) );
        }
        daoUtil.free();
        return contact;
}