Using Querydsl SQL to handle persistence in Java programs

After several years working with Hibernate (since version 1.X - about 2001/2002) and then JPA, I'm quite convinced that for new projects I'd try a new approach: Querydsl SQL. Why? Well, Even though full ORM solutions like Hibernate have several advantages (managing relationships, an easier query language and so on), they also have their drawbacks. I found out that:

• What I really wanted is an easier way to manipulate databases / resultsets;
• It always selects all attributes when dealing with entities. I know you CAN select individual attributes, but this is more an exception than a rule. People tend to just read the entire record, and then accessing the needed attributes. Some argue that this is not something which impacts performance, but after some fine tuning on my current project, I realized that every gain matters;
• Pure OO in data manipulation is nice, but the impedance mismatch just can't be negleted. It will bite you sooner or later;
• You always end up with a few cases where native query is needed, or the performance is just not acceptable. I think that programs are coded by developers, but those who really needed to be pleased are the end users. And poor performance just produces bad mood on users;
• Even though JPA 2 has most of the features Hibernate has, it brings a problem: Just like most (all?) JCP specifications, it always has points left out of the specification. So, having a (relatively complex) system working with a JPA provider (say, Hibernate) and migrating it to another one (EclipseLink, OpenJPA, ...) is not failproof. This just leads to frustration...

Ok, I know no framework / library / technology is perfect, but I think Querydsl SQL is quite promising. Here are a few points:

• You have full power of native queries, with type-safe queries. Java classes are generated based on the database tables, so you have the full power of IDE's (autocomplete, finding references, code analysis...). This is a boost on productivity;
• Queries can return several types of data, like iterators, lists, maps or single objects. The projection type can be beans, arrays, tuples or custom expressions. Querydsl is very easy to extended;
• It can also handle data manipulation (inserts, updates and deletes). This kind of removes all cases one would need to touch the connection;
• Besides to generating the Q-types (Java classes representing the database tables), it is also possible to generate beans (DTOs) for the tables. This is nice for cases where you want all columns of the table, but optional. Using them can boost the productivity, as avoids having to create each bean by hand.

So, enough talking! Let's take a look on some code. The example here is of a simple blog: We have users, which can create posts and commenting existing posts. So, here is the DDL for MySQL:

drop table if exists comment;
drop table if exists post;
drop table if exists user;

create table user (
    id bigint not null,
    name varchar(100) not null,
    username varchar(20) not null,
    password varchar(20) not null,
    primary key (id)
) engine innodb;

create table post (
    id bigint not null,
    user_id bigint not null,
    title varchar(250) not null,
    date datetime not null,
    contents text not null,
    primary key (id),
    constraint fk_post_user foreign key (user_id) references user(id)
) engine innodb;

create table comment (
    id bigint not null,
    user_id bigint not null,
    post_id bigint not null,
    date datetime not null,
    comments text not null,
    primary key (id),
    constraint fk_comment_user foreign key (user_id) references user(id),
    constraint fk_comment_post foreign key (post_id) references post(id)
) engine innodb;

So, we need to invoke Querydsl to read the database tables and generate the Java classes. Beans will be generated as well:

Configuration configuration = new Configuration(new MySQLTemplates());
NamingStrategy namingStrategy = new DefaultNamingStrategy();
MetaDataExporter exporter = new MetaDataExporter();
exporter.setConfiguration(configuration);
exporter.setNamePrefix("Q");
exporter.setTargetFolder(new File("generated"));
exporter.setSerializer(new MetaDataSerializer("Q", namingStrategy));
exporter.setBeanSerializer(new BeanSerializer());
exporter.setNamingStrategy(namingStrategy);
exporter.setPackageName("demo.blog");
        
Connection connection = ... //Get connection
exporter.export(connection.getMetaData());

If you are in Eclipse, just refresh the project and add the generated folder as source folder. There you will find the QUser, QPost and QComment classes, as well as the beans: User, Post and Comment.

Before showing some data manipulation code, here are some methods used by the examples (the configuration can be created the same way as in the example above):

SQLDeleteClause delete(RelationalPath path) {
    return new SQLDeleteClause(
        getConnection(), getConfiguration(), path);
}

SQLQuery from(Expression from) {
    SQLQueryImpl query = new SQLQueryImpl(
        getConnection(), getConfiguration());
    query.from(from);
    return query;
}

SQLInsertClause insert(RelationalPath path) {
    return new SQLInsertClause(
        getConnection(), getConfiguration(), path);
}

SQLUpdateClause update(RelationalPath path) {
    return new SQLUpdateClause(
        getConnection(), getConfiguration(), path);
}

So, here are some examples for manipulating data:

QUser user = QUser.user; //Generated Q-type

// Create an user
User john = new User();
john.setName("John Smith");
john.setUsername("jsmith");
john.setPassword("john_secret");
Long johnId = insert(user)
    .populate(john)
    .executeWithKey(user.id);
john.setId(johnId);

// Create a post
QPost post = QPost.post;
Post newPost = new Post();
newPost.setDate(new Date());
newPost.setUserId(john.getId());
newPost.setTitle("A very interesting Java post!");
newPost.setContents("For more posts, visit http://freeit.inf.br");
Long postId = insert(post)
    .popupate(newPost)
    .executeWithKey(post.id);
newPost.setId(postId);

// Without using generated beans
Long maryId = 10L;
QComment comment = QComment.comment;
insert(comment)
    .set(comment.date, new Date())
    .set(comment.postId, post.getId())
    .set(comment.userId, maryId)
    .set(comment.comments, "Love your post... Keep on!")
    .execute();

// Then, john decides to edit the post title
update(post)
    .set(post.title, "Using Querydsl...")
    .set(post.contents, post.contents.concat("\\n\\n[updated]"))
    .where(post.id.eq(post.getId()))
    .execute();

// And Mary removes all her comments on all posts!
delete(comment)
    .where(comment.userId.eq(maryId)
    .execute();

Enough DML examples. Let's perform some queries (using the same user, post and comment variables from above):

//Listing comments using the generated bean
List<Comment> postComments = 
    from(comment)
    .where(comment.postId.eq(postId))
    .list(comment);

//Iterating through all users with comments
CloseableIterator<User> usersWithComments = 
    from(user)
    .rightJoin(comment.commentUserFk, user)
    .where(comment.id.isNotNull())
    .iterateDistinct(user);

On the last example, it's possible to see that even the foreign keys are imported into the model, and can be used on joins. You can also use subqueries, factory expressions to invoke custom SQL functions and so on. Visit www.querydsl.com for documentation and downloads.

So, here is my tip. If you are looking for an alternative in data access in Java, give Querydsl SQL a try.