{{announcement.body}}
{{announcement.title}}

Practical Guide: Anorm Using MySQL With Scala

DZone 's Guide to

Practical Guide: Anorm Using MySQL With Scala

In this article, we are going to explore the Anorm and will use MySQL as our database.

· Database Zone ·
Free Resource

Person coding on laptop

Practical Guide: Anorm Using MySQL With Scala

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:

Java


2. Ensure that Anorm and MySQL are configured properly in conf/application.conf:

Java


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:

Java


4. Connect to the database.

Java


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:

Java


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:

Java


Updating a Record

The following code snippet will update a record:

Java


Deleting a Record

The following code snippet will delete a record:

Java


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:

Java


The above code snippet will retrieve a record of books that matches the given condition.

The following code snippet will retrieve a record:

Java


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

MySQL Tutorial: A Beginners Guide to Learn MySQL

Top 5 Courses to Learn MySQL in 2019

Topics:
anorm, database, mysql, orm, play, scala, tutorial

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}