scalaquery with connection pooling

[SBoyle]

Newbie question...

Is there an example of using scalaquery with connection pooling? I
want to pool connections to Postgres.

Thanks.

[bryan hunt]

There is not direct support, however, this is how I did it in Java and
Scala

This example uses C3PO

In Java - the connection factory, it's a Java multiton, in that it
maintains a mapping between connection classes, implementing
AbstractDBConn and a holder containing a DatabaseConnection which is
held within a Holder class.


import java.sql.Connection;
import java.sql.SQLException;
import java.util.concurrent.ConcurrentHashMap;

import com.mchange.v2.c3p0.ComboPooledDataSource;

/**
* This is a Factory for JDBC Connection classes.
*/
public class DBCFactory {
private static volatile ConcurrentHashMap<Class<? extends
AbstractDBConn>, Holder> connections = new ConcurrentHashMap<Class<?
extends AbstractDBConn>, Holder>();
static class Holder {
private ComboPooledDataSource cpds;
public Connection getConnection() {
try {
return this.cpds.getConnection();
} catch (SQLException e) {
throw new UnsupportedOperationException(e);
}
}
Holder(Class<? extends AbstractDBConn> c) {
try {
AbstractDBConn newInstance = c.newInstance();
cpds = newInstance.getPool();
} catch (Exception e) {
throw new UnsupportedOperationException(e);
}
}
};

public synchronized static Connection getConnection(
Class<? extends AbstractDBConn> type) {
if (connections.get(type) == null) {
connections.put(type, new Holder(type));
}
return connections.get(type).getConnection();

}
}

import com.mchange.v2.c3p0.ComboPooledDataSource;

public abstract class AbstractDBConn {

public abstract ComboPooledDataSource getPool();

}

Back to Scala now....

import scala.Unit

import org.scalaquery.ql.extended.MySQLDriver
import org.scalaquery.ql.{Join, Query}
import org.scalaquery.session.Database.threadLocalSession
import org.scalaquery.ql.basic.{BasicTable => BTable}
import org.scalaquery.ql._
import org.scalaquery.ql.extended.MySQLDriver.Implicit._

object LiveDataProvider {
private[LiveDataProvider] lazy val db = new C3P0Database
//List all available topics in the system
lazy val animalsQ = for {a<- Animals if a.dangerous === false} yield
t
}

class LiveDataProvider extends BrainDataProvider with WithImpls {
def getDangerourAnimals():List[Animal] = {
LiveDataProvider.db.withSession {
animalsQ.list(topic.pk).head
}
}
}

import com.senti.db.AbstractDBConn
import com.mchange.v2.c3p0.ComboPooledDataSource
import org.scalaquery.session.Database

class CCDBConn extends AbstractDBConn {
def getPool(): ComboPooledDataSource = {
import com.senti.conf.Configuration
try {
val pool = new ComboPooledDataSource
pool setDriverClass classOf[com.mysql.jdbc.Driver].getName
// load the jdbc driver
val conf = Configuration getInstance "connection.properties"
pool setJdbcUrl("jdbc:mysql://" + conf
.getString(".host") + "/"
+ conf.getString("schema"))
pool setUser(conf.getString("username"))
pool setPassword(conf.getString("password"))
// the settings below are optional -- c3p0 can work with
defaults
pool setMinPoolSize 1
pool setAcquireIncrement 1 ;
pool setMaxPoolSize 3 ;
pool.getProperties.put("utf8", "true");
pool
}
catch {
case e: InstantiationException => {
println(e);
throw new RuntimeException(e)
}
case unknown => {
println("unhandled exception");
throw new RuntimeException(unknown)
}
}
}
}


import com.senti.db.AbstractDBConn
import com.mchange.v2.c3p0.ComboPooledDataSource
import org.scalaquery.session.Database

class C3P0Database extends Database {
import java.sql.Connection
import org.scalaquery.ql.extended.MySQLDriver
val driver = MySQLDriver
override def createConnection(): Connection = {
import DBCFactory
DBCFactory.getConnection(classOf[CCDBConn])
}
}

I know you are probably looking for a pure Scala solution but that's
the essence of what I cobbled together and it works well.

[SBoyle]

Thanks Bryan,

I'm not sure I fully understand your example but I was able to get it
to work with BoneCP (instead of c3p0). In your example, when do
connections get returned to the pool (I do not see a close()
anywhere)? Also, when does the pool itself get shutdown?

[Stefan Zeiger]

If the connection pool can provide a DataSource (which is supported by
BoneCP AFAICT), you should be able to use Database.forDataSource() in
ScalaQuery. This will close the Connection when a Session is released.
Depending on the database driver, configuring the pool to cache
PreparedStatements can be beneficial since ScalaQuery uses
PreparedStatements for everything but does not reuse them.

-sz

[bryan hunt]

Stefan,

Doesn't DataBase.withSession close the connection at the end of
execution?

def withSession[T](f: Session => T): T = {
val s = createSession()
try { f(s) } finally s.close()

[bryan hunt]

Pool shutsdown on web app redeploy or application shutdown. Built in
feature of C3P0.
Connections are closed automatically by Database.withSession:

Example code:

LiveDataProvider.db.withSession {
animalsQ.list(topic.pk).head
}

Scala query, function definition:

def withSession[T](f: Session => T): T = {
val s = createSession()
try { f(s) } finally s.close()
}

Regards,

Bryan Hunt

[Stefan Zeiger]

On 2011-03-15 14:00, bryan hunt wrote:
> Stefan,
>
> Doesn't DataBase.withSession close the connection at the end of
> execution?
>
> def withSession[T](f: Session => T): T = {
> val s = createSession()
> try { f(s) } finally s.close()
> }

Yes. If the Connection comes from a pooled DataSource, that will return
the Connection to the pool.

-sz

[SBoyle]

Thank you gentlemen. I'm using the BoneCPDataSource type, so I
believe that withSession should be returning connections to the pool.