JOOQ-scala-mappingss

All the JOOQ power, in the scala world.

Why?

---

JOOQ is a great tool for accessing SQL stores. It's expressive and powerful. But sometimes you will find yourself building some stuff that could be facilitated for simple use cases. That's the motivation to start this project, using scala macros.

Installation

---

Add this definition to the build.sbt

libraryDependencies ++= Seq(
    "org.jooq"                     %  "jooq"               % "3.6.2",
    "org.jooq"                     %  "jooq-meta"          % "3.6.2",
    "com.github.gabadi.scalajooq" %% "jooq-scala-mappings" % "0.0.3"
)

Important: Depends on scala 2.11 (there is no scala 2.10 version, at least today), and jooq 3.6.1 or greater with the scala codegen.

Functionalities

---

1. Record to entities mappings
2. Entity to record mappings
3. Scala option support
4. Implicit type conversion
5. Embedded entities
6. Base DAO functionality
7. Base query generator
8. One to one / many to one support

More to come...

Record to entity mapping

Given this mysql table:

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

A mapping to this case class

case class User(id: java.lang.Long,
                firstName: String, 
                lastName: String) {
}

Is as simple as:

    val r = dsl.newRecord(Tables.USER)
    r setId 1l
    r setFirstName "Any name"
    r setLastName "Any lastname"

    val meta = com.github.gabadi.scalajooq.JooqMeta.metaOf[tables.User, UserRecord, User]
    
    val user = meta toEntity r

Note: tables.User and UserRecord were autogenerated by the jooq scala codegen.

Note: To generate the dsl instance, see the JOOQ documentation

This is not magic, this is not reflection, this is a macro. But what do I win? The answer is that now, all the runtime exceptions will be compilation exceptions. So, if for example, you have a typo in the user class like this:

case class User(id: java.lang.Long,
                firsName: String, 
                lastName: String) {
}

During compilation you'll see a message like this:

Mappings error:
 com.github.gabadi.scalajooq.User.firsName expects a db.test.public.tables.records.UserRecord.FIRS_NAME column, but doesn't exists

Entity to record mapping

The other way is valid too

    val entity = User(0, "name", "last")
    
    val meta = com.github.gabadi.scalajooq.JooqMeta.metaOf[tables.User, UserRecord, User]
    
    val record = meta toRecord entity
    record.store()

Note: This the method toRecord depends in an implicit DSLContext instance

Scala option support

Option values, one important feature in scala, is not supported for JOOQ until version 4.0. But with JOOQ-scala-mapping, giving this class:

case class UserOption(id: java.lang.Long,
                      firstName: Option[String], 
                      lastName: Option[String]) {
}

Anyone can make a mapping like this:

    val r = dsl.newRecord(Tables.USER)
    r setId 1l

    val meta = com.github.gabadi.scalajooq.JooqMeta.metaOf[tables.User, UserRecord, User]
    
    val user = meta toEntity r
    println(user)

With this result:

    User(1, None, None)

And in the other hand, JOOQ-scala-mappings will never let you have an Entity with a null field. Instead of that, an exception will be thrown when you try to generate that entity.

###Implicit type conversion The current user as defined, has a small issue. If you see the id field, you'll see that it's not a scala Long, it's a java Long. So, what if you want a standard scala Long?, or Int? or Boolean? For that, you have implicit conversions. That means that for JOOQ-scala-mappings this two entities are valid representations for the user table

case class User1(id: java.lang.Long,
                 firsName: String, 
                 lastName: String) {
}
case class User2(id: Long,
                 firsName: String, 
                 lastName: String) {
}

###Embedded entities Ok, you need to represent a complex entity, with some other embedded entity inside... Something like this:

case class Name(first: String, last: String)

case class Profile(name: Name)

case class FullUser(id: Long, profile: Profile)

This can be automatically mapped to a table like this:

CREATE TABLE `full_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `profile_name_first` varchar(50),
  `profile_name_last` varchar(50),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

See that the FullUser has an Id and we can call the Profile an embedded entity inside the FullUser, and the Name inside the Profile may be another embedded entity.
And that hierarchy can be mapped with something like this:

 val nameMeta = JooqMeta.namespacedMetaOf[tables.FullUser, FullUserRecord, Name]("profileName")
 val profileMeta = JooqMeta.namespacedMetaOf[tables.FullUser, FullUserRecord, Profile]("profile")
 val userMeta = JooqMeta.metaOf[tables.FullUser, FullUserRecord, FullUser]

Where the namespacedMetaOf does the magic If this seams to be too much work, we can simple do:

 val userMeta = JooqMeta.metaOf[tables.FullUser, FullUserRecord, FullUser]

And with only that, all the other macros will be auto generated.

###Base DAO functionality Now, it could be nice to have the most basic functionalities already implemented. For that there is a DAO with the most necessary methods. The usage is as simple as:

  implicit lazy val userMeta = JooqMeta.metaOf[tables.User, UserRecord, User]

  lazy val userDAO = new DefaultJooqDAO[UserRecord, User]() {}

By doing so, you will have this operations already implemented:

findAll(): List[User]
insert(user: User): Long
insert(user: User, more: User*): Unit
insertAll(users: Seq[User]): Unit
update(user: User): Int
update(user: User, more: User*): Int
updateAll(users: Seq[User]): Int
delete(user: User): Int
delete(user: User, more: User*): Int
deleteAll(users: Seq[User]): Int
deleteAll(): Int
deleteById(id: Long): Int
deleteById(id: Long, more: Long*): Int
deleteByIds(ids: Seq[Long]): Int
findById(id: Long): Option[User]
findByIds(id: Long, more: Long*): List[User]
findByIds(ids: Seq[Long]): List[User]

This dao implementation, works for entities with a single primaryKey, with type Long or Int and autoincremental. In case of, for example, a String primaryKey, can be resolved with:

  implicit lazy val userMeta = JooqMeta.metaOf[tables.UserByCode, UserByCodeRecord, UserByCode]

  lazy val userDAO = new JooqDAO[UserByCodeRecord, String, UserByCode]() {}

Where the second type parameter in the JooqDAO indicates that this table has a String as a primaryKey.
Another use case, is that the User.id is not autogenerated, for example an UUID. In that case, this can resolved simply doing

  implicit lazy val userMeta = JooqMeta.metaOf[tables.User, UserRecord, User]

  lazy val userDAO = new DefaultJooqDAO[UserRecord, User]() {
    override lazy val createFields = userMeta.fields
  }

This means: During creation, use all the fields of the user. Does not ignore the primaryKey like the default implementation.
Another use case, may be enhanced functionality, like a saveOrUpdate that depends on the SQL implementation.
This is an extension for saveOrUpdate in a MySQL store

  class MySQLDefaultDAO [Rec <: UpdatableRecord[Rec], Entity] extends DefaultJooqDAO[Rec, Entity] {
    def saveOrUpdate(e: Entity)(implicit dsl: DSLContext): Unit = {
      val createRecord = attached(e)
      val updateRecord = attached(e).into(updateFields :_*)
      dsl.insertInto(table).set(createRecord).onDuplicateKeyUpdate().set(updateRecord).execute()
    }

    def saveOrUpdate(e: Seq[Entity])(implicit dsl: DSLContext): Unit = e.foreach(saveOrUpdate)

    def saveOrUpdate(e: Entity, en: Entity*)(implicit dsl: DSLContext): Unit = saveOrUpdate(en :+ e)
  }

Now all the DAOs that extends MySQLDefaultDAO instead of DefaultJooqDAO will have the saveOrUpdate functionality.
Important Note:
All the methods definitions in the default dao, actually receives one more implicit parameter. That parameter is the Jooq DSLContext used for the dao operations. So the whole usage example should be:

    val configuration = new DefaultConfiguration()
      ...
    implicit val dsl = DSL using configuration
      ...
    userDAO insert User(0l, "name", "last")

###Base query generator This feature is trivial in the beginning, but combined with the one-to-one, many-to-one, etc support, may be very powerful.
With this, a query maybe autogenerated. So, for example:

  val userMeta = JooqMeta.metaOf[tables.User, UserRecord, User]
  println(userMeta.query)

Generates this code:

select 
  "PUBLIC"."USER"."ID", 
  "PUBLIC"."USER"."FIRST_NAME", 
  "PUBLIC"."USER"."LAST_NAME"
from "PUBLIC"."USER"

NOTE: This will autogenerate the joins for the x-to-x support ###One to one / many to one support With this feature, it's possible to fetch eagerly a joined entity.
For example, giving this schema:

CREATE TABLE `city` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(50),
  `state_id` bigint(20),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `state` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(50),
  `country_id` bigint(20),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `country` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(50),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

This can be mapped with this entities and mappers

case class City(id: Long, name: String, state: State)

case class State(id: Long, name: String, country: Country)

case class Country(id: Long, name: String)

...

implicit lazy val countryMeta = JooqMeta.metaOf[tables.Country, CountryRecord, Country]
implicit lazy val stateMeta = JooqMeta.metaOf[tables.State, StateRecord, State]
implicit lazy val cityMeta = JooqMeta.metaOf[tables.City, CityRecord, City]

This JooqMetas may be used with the JooqDAOs, or simply doing:

   println(cityMeta.query)

will be autogenerated the following query

select 
  "PUBLIC"."CITY"."ID", 
  "PUBLIC"."CITY"."NAME", 
  "PUBLIC"."STATE"."ID", 
  "PUBLIC"."STATE"."NAME", 
  "PUBLIC"."COUNTRY"."ID", 
  "PUBLIC"."COUNTRY"."NAME", 
  "PUBLIC"."STATE"."COUNTRY_ID", 
  "PUBLIC"."CITY"."STATE_ID"
from "PUBLIC"."CITY"
  join "PUBLIC"."STATE"
  on "PUBLIC"."CITY"."STATE_ID" = "PUBLIC"."STATE"."ID"
  join "PUBLIC"."COUNTRY"
  on "PUBLIC"."STATE"."COUNTRY_ID" = "PUBLIC"."COUNTRY"."ID"

IMPORTANT: Only the fetching is done eagerly, but there is no cascade functionality.

###Next functionalities

1. Custom join field
2. Many to many functionality
3. Basic query generator

###Limitations

1. Does not support a two way relation. Something like this:

case class Profile(firstName: String, lastName: String)

case class User(id: Long, profile: Profile)

In this case, the User knowns the Profile. And the Profile knowns the User. So the macro can never be resolved. 2. The current version, does not support an the same entity to be referenced twice in the same entity, like in this example.

case class City(id: Long, name: String)

case class Route(from: City, to: City)

###Contributing

Please see CONTRIBUTING

###Contributors

• Ricardo Tellechea