Skip to content
Mirage-SQL Scala - the SQL Centric Database Access Library for Scala!
Branch: master
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
project
src
.gitignore
.travis.yml
README.md
build.sbt

README.md

Mirage-SQL Scala Build Status Join the chat at https://gitter.im/mirage-sql/mirage-sql

The SQL-Centric Database Access Library for Scala.

Introduction

Mirage-SQL Scala is wrapper written in Scala for the Mirage SQL framework.

Among other features, it provides a dynamic SQL-Template language called: 2WaySQL.

Template directives are embedded as SQL comments, so that the 2WaySQL template is also executable as raw a SQL at the same time. This makes SQL files both dynamic and simply testable at the same time.

To use Mirage-SQL Scala with an SBT based project, just add following dependency to your build.sbt.

libraryDependencies += "com.miragesql" %% "miragesql-scala" % "2.0.0"

Links:

  • A more detailed documentation will provided in the central Wiki.
  • User Support in English is provided in the Gitter Chatroom.
  • If you find any bugs or issues, please report them in the GitHub Issue Tracker.

Usage

A. 2WaySQL Dynamic Template

This is a simple example to query using Mirage-SQL Scala:

First, define the DTO which is mapped to ResultList as case class. It's possible to specify Option[T] as property type for nullable properties.

// A class which mapped to ResultList
case class Book(
  bookId: IdentityPk[Int],
  bookName: String,
  author: String,
  price: Option[Int]
)

Execute the SQL using SqlManager. mirage-scala provides dynamic SQL template called 2waySQL in Sql().

import com.miragesql.miragesql.scala._

Session.withTransaction { session =>
  val sqlManager: SqlManager = session.sqlManager

  val books: List[Book] = sqlManager.getResultList[Book](
    Sql("""
      SELECT BOOK_ID, BOOK_NAME, AUTHOR, PRICE
      FROM BOOK
      /*IF author!=null*/
        WHERE AUTHOR = /*author*/
      /*END*/
    """), Map("author"->"Naoki Takezoe"))

  books.foreach { book =>
    println("bookId: " + book.bookId)
    println("bookName: " + book.bookName)
    println("author: " + book.author)
    println("price: " + book.price)
    println("--")
  }
}

You can also use an external SQL file using SqlFile():

val result: List[Book] = sqlManager.getResultList[Book](
  SqlFile("META-INF/selectBooks.sql"),
  Map("author"->"Naoki Takezoe"))

With Mirage-SQL Scala, it's also possible to use Map[String, _] as result class / parameter class instead of the case class.

See also the Mirage SQL Documentation to learn more about it's usage.

B. SQL-less Updates

Mirage-SQL Scala also supports SQL-less select / update using an entity class.

If the primary key is set at the server-side, for example, it's auto incremented, You have to specify Auto for the primary key property.

val book: Book = Book(
  Auto,
  "Mirage in Action",
  "Naoki Takezoe",
  Some(20)
)

sqlManager.insertEntity(book);

If the primary key must be set by the application, you can use Id(value) to set the value to the primary key property.

val book: Book = Book(
  Id(1),
  "Mirage in Action",
  "Naoki Takezoe",
  Some(20)
)

sqlManager.insertEntity(book);

Batch updating is also available.

// batch inserting
sqlManager.insertBatch(book1, book2, book3)
sqlManager.insertBatch(books: _*)

// batch updating
sqlManager.updateBatch(book1, book2, book3)
sqlManager.updateBatch(books: _*)

// batch deleting
sqlManager.deleteBatch(book1, book2, book3)
sqlManager.deleteBatch(books: _*)

C. ResultSet Streams

To handle large data, create streams by stream() method and process each records by foreach() method.

sqlManager
  .stream(Sql("SELECT BOOK_ID, BOOK_NAME, AUTHOR, PRICE FROM BOOK"))
  .foreach[Book] { book =>
    println(book)
  }

If you would like to aggregate streaming values, you can use foldLeft method instead:

val sum = sqlManager
  .stream(Sql("SELECT BOOK_ID, BOOK_NAME, AUTHOR, PRICE FROM BOOK"))
  .foldLeft[Book, Int](0){ case (book, i) =>
    i + book.price
  }
You can’t perform that action at this time.