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 :

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 :

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;
}