Saturday, 2 April 2016

Type safe queries in Java with Query DSL

When writing applications you will at some need to write to a database. In the java world, you can do it directly with JDBC, JDBI or Spring JdbcTemplate or you can use a full blown orm like hibernate.  Query DSL (SQL version) is a middle ground not orm and not pure JDBC. Below is how to set it up.

Add the following dependencies to your pom:
<dependency>
  <groupId>com.querydsl</groupId> 
  <artifactId>querydsl-sql</artifactId>  
  <version>4.0.3</version>
</dependency>

Add the following plugin to the build section:
<build>
    <plugins>
      <plugin>
        <groupId>com.querydsl</groupId>
        <artifactId>querydsl-maven-plugin</artifactId>
        <version>4.0.3</version>
        <executions>
          <execution>
            <goals>
              <goal>test-export</goal>
            </goals>
          </execution>
        </executions>
        <configuration>
          <!-- Driver to use to connect to the db -->
          <jdbcDriver>com.mysql.jdbc.Driver</jdbcDriver>
          <!-- Database connection url -->
          <jdbcUrl>jdbc:mysql://localhost:3306/search_app</jdbcUrl>
          <!-- Database user -->
          <jdbcUser>root</jdbcUser>
          <!-- Database password -->
          <jdbcPassword>riz123</jdbcPassword>
          <!-- Tell query dsl that generated classes should be under this package -->
          <packageName>app.domain</packageName>
          <!-- Source where to put the generated classes -->
          <targetFolder>${project.basedir}/src/main/java</targetFolder>
          <sourceFolder>${project.basedir}/src/main/java</sourceFolder>
        </configuration>
        <dependencies>
          <!-- database driver dependencies -->
          <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.38</version>
          </dependency>
        </dependencies>
      </plugin>
    </plugins>
</build>

Now running mvn test will generate your classes to query the database.


Now querying becomes very easy and type safe for example to select columns from message table you would do:
q.select(message.id, message.ref, message.body)
 .from(message)
 .where(message.ref.eq(ref).and(message.sent.isNull))
 .orderBy(message.position.asc())
 .limit(1)
 .fetchOne()

No comments:

Post a Comment