Fork me on GitHub

Data Access

Connection pool and db.properties

Lutece has by default two connection pools on the database containing the portal data (portal and shared). More pools can be declared for further plugins needs.

The pool declaration is done into a db.properties situated into the WEB-INF/conf folder.

# Pool for the quiz plugin database

quiz.poolservice=fr.paris.lutece.util.pool.service.LuteceConnectionService
quiz.driver=org.gjt.mm.mysql.Driver
quiz.url=jdbc:mysql://localhost/quiz?autoReconnect=true
quiz.user=
quiz.password=
quiz.initconns=1
quiz.maxconns=3
quiz.logintimeout=2000
quiz.checkvalidconnectionsql=SELECT 1   

The properties of a pool are as follows :

Name Description
poolservice (optional) The class name which will try to connect to the pool. By default the service is a Lutece service, but can be replaced by a Tomcat pool service : fr.paris.lutece.util.pool.service.TomcatConnectionService
driver The JDBC class name
url The URL of the JDBC connection to the database containing namely the protocol, optional port number, server name and that of the database
user The name of the user of the database
password The user's password to the database
initconns The number of connections to initialize at pool creation
maxconns The maximum number of connections on the pool
logintimeout The delay expressed in milli seconds to give up a database connection.
checkvalidconnectionsql (optional) This parameter contains the SQL request which allows to verifying whether the connection pool is valid. The default value is SELECT 1 for MySQL. For Oracle, this parameter must be defined as the following request :SELECT SYSDATE FROM dual.

The DAO pattern

As described in the General Architecture, the persistence of the relational database is ensured by the objects defined in the J2EE specification.The DAO objects may be generated by the codewizard plugin Since the 1.3 version , DAO objects are designed with the help of DAOUtil objects.

DAOUtil object

The DAOUtil class allows to create the objects which provide an interface to every data access operations :

  • Getting a connection from a pool
  • Preparing an SQL order
  • Definition of variable elements of the SQL order
  • Execution of the order
  • Fetching and possible scan of results
  • Releasing the connection in the pool

Here is an example use of the DAO Util 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;
}