Practical Guide: Anorm Using MySQL With Scala
In this article, we are going to explore the Anorm and will use MySQL as our database.
Join the DZone community and get the full member experience.
Join For Free
In this article, we are going to explore the Anorm. Play includes a simple data access layer called Anorms that uses plain SQL to interact with the database and provides an API to parse and transform the resulting datasets. We will be using MySQL as our database.
You need to perform the following steps to utilize Anorm:
1. To start with Anorm, you need to include the following external dependency of Anorm in your build.sbt file:
xxxxxxxxxx
libraryDependencies +=("org.playframework.anorm" %% "anorm" % "2.6.4")
2. Ensure that Anorm and MySQL are configured properly in conf/application.conf:
xxxxxxxxxx
db.default.driver= com.mysql.jdbc.Driver
db.default.url="jdbc:mysql://localhost/bookStore"
db.default.user=testuser
db.default.password=test123
3. Anorm requires a SQL schema creation/destruction script since it doesn’t do auto schema creation. Create a new file named “conf/evolutions/default/1.sql” containing:
xxxxxxxxxx
# --- First database schema
# --- !Ups schema creation statements
CREATE TABLE book (
id INT PRIMARY KEY,
bookname VARCHAR(255) NOT NULL
author VARCHAR(255) NOT NULL
);
# --- !Downs schema destruction statements
DROP TABLE IF EXISTS book;
4. Connect to the database.
xxxxxxxxxx
import play.api.db._
DB.withConnection { implicit connection
//Here comes the queries to be executed
// Note : all sql statement needs database connection as an
// implicit parameter to be executed
}
There are one of the several ways to connect to the databases. If you need to write a method to run a single query, define your method with db.withConnection. If you need to create a method that executes several queries as part of a transaction, use db.withTransaction.
You may also like: The Right Way to Use Spark and JDBC
If we need to connect to the specific database other than the default, just pass the database name as the string parameter to the withConnection method.
For example:
xxxxxxxxxx
// bookStore is the name of the database
DB.withConnection("bookStore")
DB.withTransaction("bookStore")
In general, there are three things you need to do to run a SQL query with Anorm:
- Connect to the database. You can do this with or without a transaction
- Create your SQL query using Anorm’s SQL string interpolator
- Call a method like executeInsert or executeUpdate to run the query
The following sections display various database-oriented operations using Anorm.
Creating a New Record
The following code snippet will create a new record:
xxxxxxxxxx
DB.withConnection { implicit c =>
SQL("INSERT INTO book(id, bookname,author) VALUES ({id}, {bookname},{author});")
.on('id -> book.id, 'bookname -> book.name,'author -> book.author).executeInsert()
}
Updating a Record
The following code snippet will update a record:
xxxxxxxxxx
DB.withConnection { implicit c =>
SQL("UPDATE book SET bookname = {bookname} WHERE id = {id}")
.on('bookname -> book.bookname, 'id -> book.id).executeUpdate()
}
Deleting a Record
The following code snippet will delete a record:
xxxxxxxxxx
DB.withConnection { implicit c =>
SQL("DELETE FROM book WHERE id={id};")
.on('id -> id).executeUpdate()
}Querying a record
SQL SELECT Queries
For SQL SELECT queries, you’ll also need to create a RowParser when your query returns a custom data type such as a User or Book. Before getting into the examples, there are a few things to know about SELECT queries with Anorm:
- The Anorm single method returns a single value, but it throws an exception if your query returns no rows.
- The singleOpt method returns a single value as an Option
- You can select simple values like Int and String as well as your own custom data types
- If your query will return a custom data type like User or Book, you need to use a RowParser
The following code snippet will retrieve a record:
xxxxxxxxxx
DB.withConnection { implicit c =>
SQL("SELECT * FROM book WHERE id={id};")
.on('id -> id).executeQuery().singleOpt(defaultParser)
}
The above code snippet will retrieve a record of books that matches the given condition.
The following code snippet will retrieve a record:
xxxxxxxxxx
DB.withConnection { implicit c =>
SQL("SELECT * FROM book;").executeQuery().list(defaultParser)
}
The above code snippet will retrieve a record of all books, which are present in the book table.
References
1. https://www.playframework.com/documentation/2.7.x/Anorm
2. https://playframework.github.io/anorm/#executing-sql-queries
Further Reading
Opinions expressed by DZone contributors are their own.
Trending
-
Designing a New Framework for Ephemeral Resources
-
What to Pay Attention to as Automation Upends the Developer Experience
-
Reactive Programming
-
Logging Best Practices Revisited [Video]
Comments