JarCastingMirage-SQL Scala 
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
}
