2010-10-04

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.

2010-10-01

Using Querydsl In Eclipse

Querydsl usage in Eclipse is now easier than ever. Since Querydsl 2.0 we have improved APT support in Querydsl to support incremental code generation in IDEs. We have also improved the release bundles to streamline the APT configuration in Java IDEs like Eclipse.

The APT usage improvements affect the JPA, JDO and MongoDB modules. Instead of a set of screenshots we have prepared a screencast to showcase the new APT features in Querydsl in an Eclipse context.

To enable APT usage in Eclipse you need to
  • set the Enable annotation processing checkbox via Properties > Java Compiler > Annotation Processing,
  • add the APT option defaultOverwrite = true if you are using Eclipse Helios and
  • set the Factory Path to one of the apt-one-jar files included in your release bundle.

The screencast uses the Querydsl JPA module in combination with Hibernate and shows the creation of a Java project, the creation of two simple domain types and the creation of a minimal demo service which presents the simplicity of Querydsl query usage.