How to use Liferay Service Builder with remote DB and JOINs?

Question

My portlet connects to a remote database and performs a complex query with several JOINs. I asked in the forum how to use connection pooling in my portlet and they just answered that I should use Service Builder anyway, because SB would take care of this... so my question is, whether this is correct and how I have to use Service Builder to do my aforementioned use case. Thank you!

Answer 1

Finally, I figured out a way to use connection pooling. I do not use a Service Builder, but the c3p0 pooling library.

ConnectionPool.java

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import com.liferay.portal.kernel.log.Log;
import com.liferay.portal.kernel.log.LogFactoryUtil;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.mchange.v2.c3p0.DataSources;

public class ConnectionPool {

    private static Log _log = LogFactoryUtil.getLog(ConnectionPool.class);

    private static ConnectionPool _instance = new ConnectionPool();

    private ComboPooledDataSource _cpds;
    private Properties _props;

    public static void cleanUp(Connection con) {
        _instance._cleanUp(con);
    }

    public static void cleanUp(Connection con, Statement s) {
        _instance._cleanUp(con, s);
    }

    public static void cleanUp(Connection con, Statement s, ResultSet rs) {
        _instance._cleanUp(con, s, rs);
    }

    public static void cleanUp(Statement s) {
        _instance._cleanUp(s);
    }

    public static void destroy() throws SQLException {
        _instance._destroy();
    }

    public static Connection getConnection() throws SQLException {
        return _instance._getConnection();
    }

    public static Properties getProperties() {
        return _instance._props;
    }

    private ConnectionPool() {
        try {

            // Properties

            ClassLoader classLoader = getClass().getClassLoader();
            _props = new Properties();
            _props.load(classLoader.getResourceAsStream("connection-pool.properties"));

            _props.list(System.out);

            // Pooled data source
            String driverClass = _props.getProperty("driver.class");
            String jdbcUrl = _props.getProperty("jdbc.url");
            String user = _props.getProperty("user");
            String password = _props.getProperty("password");

            int minPoolSize = 5;

            try {
                minPoolSize = Integer.parseInt(_props.getProperty("min.pool.size"));
            } catch (Exception e) {
            }

            int maxPoolSize = 5;

            try {
                maxPoolSize = Integer.parseInt(_props.getProperty("max.pool.size"));
            } catch (Exception e) {
            }

            int acquireIncrement = 5;

            try {
                acquireIncrement = Integer.parseInt(_props.getProperty("acquire.increment"));
            } catch (Exception e) {
            }

            _cpds = new ComboPooledDataSource();

            _cpds.setDriverClass(driverClass);
            _cpds.setJdbcUrl(jdbcUrl);
            _cpds.setUser(user);
            _cpds.setPassword(password);

            _cpds.setMinPoolSize(minPoolSize);
            _cpds.setMaxPoolSize(maxPoolSize);
            _cpds.setAcquireIncrement(acquireIncrement);
        } catch (Exception e) {
            _log.error(e);
        }
    }

    private void _cleanUp(Connection con) {
        _cleanUp(con, null, null);
    }

    private void _cleanUp(Connection con, Statement s) {
        _cleanUp(con, s, null);
    }

    private void _cleanUp(Connection con, Statement s, ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException sqle) {
            _log.error(sqle);
        }

        try {
            if (s != null) {
                s.close();
            }
        } catch (SQLException sqle) {
            _log.error(sqle);
        }

        try {
            if (con != null) {
                con.close();
            }
        } catch (SQLException sqle) {
            _log.error(sqle);
        }
    }

    private void _cleanUp(Statement s) {
        try {
            if (s != null) {
                s.close();
            }
        } catch (SQLException sqle) {
            _log.error(sqle);
        }
    }

    private void _destroy() throws SQLException {
        DataSources.destroy(_cpds);
    }

    private Connection _getConnection() throws SQLException {
        return _cpds.getConnection();
    }

}

connection-pool.properties

driver.class=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://host/DB
user=123
password=123

min.pool.size=5
max.pool.size=20
acquire.increment=5

Usage

conn = ConnectionPool.getConnection();