Page tree
Skip to end of metadata
Go to start of metadata

Since Play SQL 2.11, we provide the details for the library C3P0 (older examples used DBCP). C3P0 is more modern and supports PLAYSQL-135 - Getting issue details... STATUS .

 

Here's a small tutorial to declare a JNDI datasource. This is useful, for example, for the Play SQL add-on.

Create your Postgres user

bash$ psql -U postgres -W postgres -h localhost

                CREATE USER myuser WITH PASSWORD 'mypassword';
                CREATE DATABASE mydatabasename OWNER myuser;
                -- Play SQL requires permissions to create a schema per space
                GRANT ALL PRIVILEGES ON DATABASE mydatabasename to myuser;

Note: In the following excerpts, please change mydatabasename, myuser, mypassword for the values you've chosen above.

Declare the JNDI datasource in <install-dir>/conf/server.xml:

Add the resource inside the <Context> element:

<Resource name="jdbc/myjndiname" auth="Container"
    driverClass="org.postgresql.Driver"
    jdbcUrl="jdbc:postgresql://localhost:5432/r"
    user="r"
    password="r"                                                     
    factory="org.apache.naming.factory.BeanFactory"
    type="com.mchange.v2.c3p0.ComboPooledDataSource"
  
    />

More information on the configuration of the connection pool can be found on the C3P0 website.

 Example of server.xml file...

Note: Please change mydatabasename, myuser, mypassword for values of your choice.

<Server port="8000" shutdown="SHUTDOWN" debug="0">
    <Service name="Tomcat-Standalone">
        <Connector className="org.apache.coyote.tomcat4.CoyoteConnector" port="8090" minProcessors="5"
                   maxProcessors="75"
                   enableLookups="false" redirectPort="8443" acceptCount="10" debug="0" connectionTimeout="20000"
                   useURIValidationHack="false" URIEncoding="UTF-8"/>
        <Engine name="Standalone" defaultHost="localhost" debug="0">
            <Host name="localhost" debug="0" appBase="webapps" unpackWARs="true" autoDeploy="false">
                <Context path="" docBase="../confluence" debug="0" reloadable="false" useHttpOnly="true">
                    <Manager pathname="" />
                    <Resource name="jdbc/myjndiname" auth="Container"
                        driverClass="org.postgresql.Driver"
                        jdbcUrl="jdbc:postgresql://localhost:5432/databasename"
                        user="the-db-user"
                        password="???"                                                     
                        factory="org.apache.naming.factory.BeanFactory"
                        type="com.mchange.v2.c3p0.ComboPooledDataSource"
                    /> 
                    <Resource name="mail/Session"
                      auth="Container"
                      type="javax.mail.Session"
                      mail.smtp.host="smtp.gmail.com"
                      mail.smtp.port="465"
                      mail.smtp.auth="true"
                      mail.smtp.user="???@gmail.com"
                      password="???"
                      mail.smtp.starttls.enable="true"
                      mail.smtp.socketFactory.class="javax.net.ssl.SSLSocketFactory"
                    />
                </Context>
            </Host>
        </Engine>

        <Connector port="8443" maxHttpHeaderSize="8192"
           maxThreads="150" minSpareThreads="25" maxSpareThreads="75"
           enableLookups="false" disableUploadTimeout="true"
           acceptCount="100" scheme="https" secure="true"
           clientAuth="false" sslProtocol="TLS" SSLEnabled="true"
           URIEncoding="UTF-8" keystorePass="somepassword"/>
    </Service>
</Server>

Copy the driver into <install-dir>/lib/

Download the driver from http://jdbc.postgresql.org/download.html - In Play SQL, we most often use postgresql-9.4-1201.jdbc41.jar.

Check the Java version

Some customers are still using Java 6, even if not supported anymore by Confluence and even if Oracle itself has dropped support since November 2012. In this case, please check your Postgresql library is compatible with Java 6.

  • Play SQL does not support Java 6 anymore.
  • You can see this information in Confluence's System Information page.
  • On this page, if Confluence says "1.7.0_65", the "7" means Java 7. If Confluence says "1.6.0_26", you are using Java 6.
Example - Checking your driver supports JVM 6
$ unzip ~/.m2/repository/org/postgresql/postgresql/9.2-1003-jdbc4/postgresql-9.2-1003-jdbc4.jar
...
$ file org/postgresql/Driver.class
org/postgresql/Driver.class: compiled Java class data, version 51.0
$ # It means this driver isn't suitable for Java 6.


Restart Confluence

(tick) Done!

 

  • No labels