DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones AWS Cloud
by AWS Developer Relations
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones
AWS Cloud
by AWS Developer Relations
Building Scalable Real-Time Apps with AstraDB and Vaadin
Register Now

Trending

  • The Role of AI and Programming in the Gaming Industry: A Look Beyond the Tables
  • Top Six React Development Tools
  • Writing a Vector Database in a Week in Rust
  • Building the World's Most Resilient To-Do List Application With Node.js, K8s, and Distributed SQL

Trending

  • The Role of AI and Programming in the Gaming Industry: A Look Beyond the Tables
  • Top Six React Development Tools
  • Writing a Vector Database in a Week in Rust
  • Building the World's Most Resilient To-Do List Application With Node.js, K8s, and Distributed SQL
  1. DZone
  2. Coding
  3. Languages
  4. Practical Guide: Anorm Using MySQL With Scala

Practical Guide: Anorm Using MySQL With Scala

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

Swapnil Gosavi user avatar by
Swapnil Gosavi
·
Dec. 17, 19 · Tutorial
Like (2)
Save
Tweet
Share
12.79K Views

Join the DZone community and get the full member experience.

Join For Free

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
xxxxxxxxxx
1
 
1
libraryDependencies +=("org.playframework.anorm" %% "anorm" % "2.6.4")


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

Java
xxxxxxxxxx
1
 
1
db.default.driver= com.mysql.jdbc.Driver
2
db.default.url="jdbc:mysql://localhost/bookStore"
3
db.default.user=testuser
4
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:

Java
xxxxxxxxxx
1
 
1
# --- First database schema
2
# --- !Ups schema creation statements
3
CREATE TABLE book (
4
id INT PRIMARY KEY,
5
bookname VARCHAR(255) NOT NULL
6
author VARCHAR(255) NOT NULL
7
);
8
# --- !Downs schema destruction statements
9
DROP TABLE IF EXISTS book;


4. Connect to the database.

Java
xxxxxxxxxx
1
 
1
import play.api.db._
2
DB.withConnection { implicit connection
3
//Here comes the queries to be executed
4
// Note : all sql statement needs database connection as an
5
// implicit parameter to be executed
6
}


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
xxxxxxxxxx
1
 
1
// bookStore is the name of the database
2
DB.withConnection("bookStore")
3
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:

Java
xxxxxxxxxx
1
 
1
DB.withConnection { implicit c =>
2
     SQL("INSERT INTO book(id, bookname,author) VALUES ({id}, {bookname},{author});")
3
        .on('id -> book.id, 'bookname -> book.name,'author -> book.author).executeInsert()
4
   }


Updating a Record

The following code snippet will update a record:

Java
xxxxxxxxxx
1
 
1
DB.withConnection { implicit c =>
2
      SQL("UPDATE  book SET bookname = {bookname} WHERE id = {id}")
3
       .on('bookname -> book.bookname, 'id -> book.id).executeUpdate()
4
   }


Deleting a Record

The following code snippet will delete a record:

Java
xxxxxxxxxx
1
 
1
DB.withConnection { implicit c =>
2
      SQL("DELETE FROM book WHERE id={id};")
3
       .on('id -> id).executeUpdate()
4
   }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:

Java
xxxxxxxxxx
1
 
1
DB.withConnection { implicit c =>
2
      SQL("SELECT * FROM book WHERE id={id};")
3
       .on('id -> id).executeQuery().singleOpt(defaultParser)
4
    }


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

The following code snippet will retrieve a record:

Java
xxxxxxxxxx
1
 
1
DB.withConnection { implicit c =>
2
     SQL("SELECT * FROM book;").executeQuery().list(defaultParser)
3
   }


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

Database MySQL Data Types sql Java (programming language) Scala (programming language)

Opinions expressed by DZone contributors are their own.

Trending

  • The Role of AI and Programming in the Gaming Industry: A Look Beyond the Tables
  • Top Six React Development Tools
  • Writing a Vector Database in a Week in Rust
  • Building the World's Most Resilient To-Do List Application With Node.js, K8s, and Distributed SQL

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com

Let's be friends: