SQL queries in Scala

In a previous blog post I showed you how Querydsl and Scala works with JPA, JDO and Mongodb. This blog post shows you how to use Scala queries on top of Querydsl SQL.

If you are using Maven you need the following two dependencies :

<dependency>
      <groupId>com.mysema.querydsl</groupId>
      <artifactId>querydsl-scala</artifactId>
      <version>${querydsl.version}</version>
</dependency>

<dependency>
      <groupId>com.mysema.querydsl</groupId>
      <artifactId>querydsl-sql</artifactId>
      <version>${querydsl.version}</version>
</dependency>

Like with Querydsl SQL for Java you need to generate Query types to be able to construct your queries. The following two unit test methods show how this is done :

val templates = new HSQLDBTemplates()
  var connection: Connection = _

  @Before
  def setUp() {
    // set up the JDBC connection for JDBC metadata access
    Class.forName("org.hsqldb.jdbcDriver")
    val url = "jdbc:hsqldb:mem:testdb"
    connection = DriverManager.getConnection(url, "sa", "")
  }

  @Test
  def Generation_without_Beantypes() {
    val namingStrategy = new DefaultNamingStrategy()
    val serializer = new ScalaMetaDataSerializer("Q", namingStrategy)
    val exporter = new MetaDataExporter("Q", "test", new File("target/gen1"), namingStrategy, serializer)
    exporter.setCreateScalaSources(true)
    exporter.export(connection.getMetaData())
  }

  @Test
  def Generation_with_Beantypes() {
    val namingStrategy = new DefaultNamingStrategy()
    val serializer = new ScalaMetaDataSerializer("Q", namingStrategy)
    val beanSerializer = new ScalaBeanSerializer()
    val exporter = new MetaDataExporter("Q", "com.mysema.example", new File("target/gen2"), namingStrategy, serializer, beanSerializer)
    exporter.setCreateScalaSources(true)
    exporter.export(connection.getMetaData())
  }

The MetaDataExporter can be used with and without Bean type generation. If you just want to write SQL queries and you don't need a JavaBean based model for projection, insertion and update, you can use it without bean projection. If you want to mirror your SQL schema into a minimal domain model and use it ORM-like then generate the bean types.

Make sure that the target folder (target/gen1 and target/gen2 in the two examples) is in your source path. And replace com.mysema.example with a target package of your choice.

Now you can use the generated query types for querying :

@Test
  def Querying() {
    val survey = new QSurvey("survey")
    val employee = new QEmployee("employee")

    assertEquals("abc", query from survey where (survey.id eq 1) 
      .uniqueResult survey.name)

    assertEquals("def", query from survey where (survey.id eq 2) 
      .uniqueResult survey.name)

    assertEquals("Bob", query from employee 
      .where (employee.lastname eq "Smith") 
      .uniqueResult employee.firstname)

    assertEquals("John", query from employee 
      .where (employee.lastname eq "Doe") 
      .uniqueResult employee.firstname)
  }

  def query = new SQLQueryImpl(connection, templates)

Querydsl SQL for Scala doesn't use the $-sign based escape syntax for expression construction nor alias variables. Instead code generation is used to output the Query model as a set of Scala classes that reflect the tables, columns and keys of your relational schema.

You can instantiate the generated Q-types instead. Alternatively you can also use a more SQL-like syntax:

val survey = QSurvey as "survey"

For more details on how to use Querydsl SQL see the related section in the latest revision of the Reference Documentation.

Recent released have changed the Querydsl Scala API. Visit the Reference Documentation for updated documentation.