Today’s modern application frameworks come with apromise of easy sql data access. There is no wonder why we have so many frameworks that make it easier to issue queries and handle transactions. SQL is the lingua franca of most applications when it comes to databases.
Play comes with the JDBC plugin. We encountered the JDBC plugin previously in order to modify our database schema.
The first step is to include the jdbc and the evolutions module.
libraryDependencies += evolutions libraryDependencies += jdbc
Then we shall define the connection string needed. We will use a simple h2 database. The configuration is added at the application.conf.
db.default.driver=org.h2.Driver
db.default.url=”jdbc:h2:/tmp/defaultdatabase”
Then we add a script that creates the users table.
# Users schema
# --- !Ups
CREATE TABLE users (
id bigint(20) NOT NULL AUTO_INCREMENT,
email varchar(255) NOT NULL,
first_name varchar(255) NOT NULL,
last_name varchar(255) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY (email)
);
# --- !Downs
DROP TABLE users;Before creating our repository class let’s check what the jdbc plugin provides us with.
We have the plain getConnection method, responsible for returning a jdbc connection. This is similar to the DataSource.getConnection from Java. Thus pay extra attention since you must close the connection.
val connection = db.getConnection() connection.close()
Next method is withConnection. By using withConnection you get Play to manage the connection for you. All you have to do is pass a block of code with jdbc actions.
def fetchUsers(): List[User] = {
db.withConnection { conn =>
val stmt = conn.createStatement
var rs = stmt.executeQuery("SELECT*FROM users");
val listBuffer = ListBuffer[User]()
while(rs.next()) {
listBuffer.append(User(Option(rs.getLong("id")),rs.getString("email"),rs.getString("first_name"),rs.getString("last_name")))
}
listBuffer.toList
}
}As you can see above, we’ve just returned back a list of our user entries.
And last but not least withTransaction. You’ve guessed right, what you receive back is a connection with autocommit set to false.
def addUser(user:User): User = {
db.withTransaction { conn =>
val stmt = conn.createStatement
val insertQuery = "INSERT INTO users ( email, first_name, last_name) VALUES( '"+user.email+"', '"+user.firstName+"','"+user.lastName+"') "
stmt.executeUpdate(insertQuery,Statement.RETURN_GENERATED_KEYS)
val resultSet = stmt.getGeneratedKeys;
if(resultSet.next()) {
val id = resultSet.getLong(1);
new User(Option(id),user.email,user.firstName,user.lastName)
} else {
throw new Exception("User not persisted properly")
}
}
}In the above example a user is persisted. In case of failure we throw an exception and the transaction is rolled back.
To sum up we have just checked how to access a sql database using play. Also we have checked the extra functions that play api provides apart from the familiar jdbc api. That’s all for now! Feel free to check the code on github.
| Reference: | SQL Data Access in Play using Scala from our JCG partner Emmanouil Gkatziouras at the gkatzioura blog. |
