play framework and postgresql multiple schemas

Question

In my search for a way to deal with multi tenancy for play framework and postgresql schemas,i found this article Here.
The problem is that i cannot figure out the way that i can execute plain sql statements in play(i am totally noob),can any one help please?.

Answer 1

In play you have the DB object that helps you getting the raw jdbc objects. If your query is a simple update without parameters you can do

DB.execute(simpleUpdate)

if your query is a simple select without parameters you can do

DB.executeQuery(simpleSelect)

if you have something more complex, with parameters, you can then use PreparedStatement by getting directly the connexion

PreparedStatement deleteStmt = DB.getConnection().prepareStatement(myStatement);
try {
    deleteStmt.setString(1, myString);
    deleteStmt.executeUpdate();
} finally {
    deleteStmt.close();
}

Answer 2

We shard Postgres database using schemas. This is solution we've came up with. We add multiple DB connections in application.conf and initialize connection with custom SQL.

# application.conf
db.default.driver=org.postgresql.Driver
db.default.url="jdbc:postgresql://localhost/mytest"
db.default.user=your_user
db.default.password=passwd
db.default.initSQL="SET timezone = 'UTC';"

db.tp0.driver=org.postgresql.Driver
db.tp0.url="jdbc:postgresql://localhost/mytest"
db.tp0.user=your_user
db.tp0.password=passwd
db.tp0.initSQL="SET search_path TO tp0, public;SET timezone = 'UTC';"

Java code

EbeanServer tp0 = Ebean.getServer("tp0");
List<moneylog> res = tp0.find(moneylog.class).findList();