A simple yet powerful helper class to provide the most simplistic way to create & drop tables and insert & select data based on scala case classes in Crate DB (https://crate.io) | Requires the Crate Java Client (https://bintray.com/crate/crate/crate-client/view)

build.sbt

name := "CreateObjectTest"

scalaVersion := "2.11.6" // Should be able to support lower versions

resolvers += "Crate bintray" at "http://dl.bintray.com/crate/crate"

libraryDependencies ++= Seq(
  "io.crate" % "crate-client" % "0.49.3"
)

CrateObject.scala

import java.io.IOException
import io.crate.client.CrateClient

/**
 * CrateObject supplies methods
 * for objects to store them in
 * a Crate Database
 *
 * @author Liam Haworth
 * @version 1.0
 */
abstract class CrateObject[T] {
  
  /**
   * Generates and executes a SQL statement designed to
   * build a table for the object in the selected schema
   *
   * @param schema The schema to create the table under
   * @throws java.io.IOException Thrown when the method fails to successfully execute the sql statement
   * @return Boolean Returns true if a new table was created
   */
  @throws(classOf[IOException])
  def create(schema: String)(implicit crateClient: CrateClient): Boolean = {
    if(crateClient == null)
      throw new IOException("Requires implicit object crateClient to be initialized")

    if(exists(schema)(crateClient))
      return false

    val tableName = this.getClass.getSimpleName.toLowerCase
    var tableColumns = Map.empty[String, String]

    this.getClass.getDeclaredFields.foreach { f =>
      f.getType match {
        case t if t == classOf[String]      => tableColumns += f.getName -> "string"
        case t if t == classOf[Int]         => tableColumns += f.getName -> "integer"
        case t if t == classOf[Integer]     => tableColumns += f.getName -> "integer"
        case t if t == classOf[Boolean]     => tableColumns += f.getName -> "boolean"
        case t if t == classOf[Short]       => tableColumns += f.getName -> "short"
        case t if t == classOf[Double]      => tableColumns += f.getName -> "double"
      }
    }

    val sqlStatement = s"create table $schema.$tableName (${tableColumns.mkString(", ").replace(" ->", "")})"

    try {
      crateClient.sql(sqlStatement)
      Thread.sleep(1000) // Have to wait for Crate to accept the request and build the table
      exists(schema)(crateClient)
    }
    catch {
      case ex: Exception =>
        throw new IOException(s"Failed to make table for $schema.$tableName", ex)
    }
  }

  /**
   * Checks for the existence of a table for
   * this object in the selected schema
   *
   * @param schema Schema to check under
   * @param crateClient Crate client for connection to database
   * @return Boolean Returns true if the table exists, false if it doesn't
   */
  def exists(schema: String)(implicit crateClient: CrateClient): Boolean = {
    val sqlResult = crateClient.sql(s"select * from information_schema.tables where table_name='${this.getClass.getSimpleName.toLowerCase}' and schema_name='$schema'").get()

    sqlResult.rowCount() == 1
  }

  /**
   * Drops the table related to this object if it exists
   *
   * @param schema The schema the table should exist under
   * @param crateClient Crate client for connection to database
   * @throws java.io.IOException Thrown when the table couldn't be dropped due to an error
   * @return Boolean Returns true if the table was dropped
   */
  @throws(classOf[IOException])
  def drop(schema: String)(implicit crateClient: CrateClient): Boolean = {
    if(crateClient == null)
      throw new IOException("Requires implicit object crateClient to be initialized")

    if(!exists(schema)(crateClient))
      return false

    try {
      crateClient.sql(s"drop table $schema.${this.getClass.getSimpleName.toLowerCase}")
      true
    }
    catch {
      case ex: Exception =>
        throw new IOException(s"Failed to drop table $schema.${this.getClass.getSimpleName.toLowerCase}", ex)
    }
  }

  /**
   * Insets a object into its table in the selected schema
   *
   * @param schema The schema the object should be stored under
   * @param crateClient Crate client for connection to database
   * @throws java.io.IOException Thrown when the object couldn't be stored in the database
   */
  @throws(classOf[IOException])
  def insert(schema: String)(implicit crateClient: CrateClient): Unit = {
    if(crateClient == null)
      throw new IOException("Requires implicit object crateClient to be initialized")

    if(!exists(schema)(crateClient))
      throw new IOException(s"Table for $schema.${this.getClass.getSimpleName.toLowerCase} does not exist yet!")

    var insertData = Map.empty[String, String]

    this.getClass.getDeclaredFields.foreach { f =>
      f.setAccessible(true)
      insertData += f.getName -> s"'${f.get(this).toString}'"
    }

    val sqlStatement = s"insert into $schema.${this.getClass.getSimpleName.toLowerCase}(${insertData.keys.mkString(", ")}) values(${insertData.values.mkString(", ")})"

    try {
      crateClient.sql(sqlStatement)
    }
    catch {
      case ex: Exception =>
        throw new IOException(s"Failed to insert object into $schema.${this.getClass.getSimpleName.toLowerCase}", ex)
    }
  }

  /**
   * Run a select on the objects table a makes a list of the object from the results
   *
   * @param schema The schema to find the table under
   * @param conditional A SQL conditional to use when selecting
   * @param crateClient Crate client for the connection to database
   * @throws java.io.IOException Thrown when a failure occurred when selecting a list of objects from the database
   * @return
   */
  @throws(classOf[IOException])
  def select(schema: String, conditional: String = "limit 100")(implicit crateClient: CrateClient): List[T] = {
    if(crateClient == null)
      throw new IOException("Requires implicit object crateClient to be initialized")

    if(!exists(schema)(crateClient))
      throw new IOException(s"Table for $schema.${this.getClass.getSimpleName.toLowerCase} does not exist yet!")

    try {
      val sqlResult = crateClient.sql(s"select * from $schema.${this.getClass.getSimpleName.toLowerCase} $conditional").get()
      var selectResult = List.empty[T]

      for(args <- sqlResult.rows())
        selectResult = selectResult :+ this.getClass.getConstructors()(0).newInstance(args: _*).asInstanceOf[T]

      selectResult
    }
    catch {
      case ex: Exception =>
        throw new IOException(s"Failed to select objects from $schema.${this.getClass.getSimpleName.toLowerCase}", ex)
    }
  }
}

Test.scala

case class TestingObject(a: String, b: Int, c: Boolean) extends CrateObject

object Test extends App {

  println("Making crate client")
  implicit val crateClient = new CrateClient("localhost:4300") //TODO: Change this to your own crate server

  println("\nMaking testing object")
  val testObject = TestingObject("bla", 333, true)

  println("\nCalling #crate(test)")
  println(s"Result: ${testObject.create("test")}")

  println("\nCalling #exits(test)")
  println(s"Result: ${testObject.exists("test")}")

  println("\nCalling #insert(test)")
  println(s"Result: ${testObject.insert("test")}")

  println("\nCalling #select(test)")
  println(s"Result: ${testObject.select("test")}")

  println("\nCalling #drop(test)")
  println(s"Result: ${testObject.drop("test")}")
}