JarCastingsql-writer
Uses types, JPA annotations and a fluent interface to automate SQL's boring 80%, without getting in your way.
sql-writer is inspired by squel.js.
Installation
Requires Java 6 and Maven.
sql-writer is in Maven Central. Add the following to your POM:
<dependency>
<groupId>co.mewf.sqlwriter</groupId>
<artifactId>sql-writer</artifactId>
<version>0.1.0</version>
</dependency>
Usage
It is recommendend to only generate the SQL once, either by storing it in constants or in instance variables in a class that is only instantiated once.
The query builders are accessible from the Queries class as static methods.
import static co.mewf.sqlwriter.Queries.*;
select().from(User.class).sql(); // SELECT User.* FROM User
update(User.class).set("name", "birthDate").where().eq("id").sql(); // UPDATE User SET User.name = ?, User.birthDate = ? WHERE User.id = ?
insert(User.class).columns("name", "birthDate").sql(); // INSERT INTO User(name, birthDate) VALUES(?, ?)
delete(User.class).where().eq("id").sql(); // DELETE FROM User WHERE id = ?
Annotations such as @Table and @Column are taken into account.
@Table(name = "usr_tbl")
class User{
@Column(name = "usr_name")
private String name;
}
select().from(User.class).columns("name").sql(); // SELECT usr_tbl.usr_name FROM usr_tbl
As in JPA, the @Id annotation is required. It determines whether field or property access is used: place it on a field for field access or on a method for method access.
Joins
Joins are derived from the JPA annotations. Unidirectional and bidirectional @OneToOne, @OneToMany, @ManyToOne and @ManyToMany are supported. @JoinTable and @JoinColumn are also supported.
class Address {
@ManyToOne
private User user;
}
select().from(User.class).from(Address.class).sql(); // SELECT User.*, Address.* FROM User INNER JOIN Address ON User.id = Address.user_id
select().from(Address.class).join(User.class).sql(); // SELECT Address.* FROM User INNER JOIN Address ON User.id = Address.user_id
select().from(Address.class).leftJoin(User.class).sql(); // SELECT Address.* FROM User LEFT JOIN Address ON User.id = Address.user_id
select().from(Address.class).rightJoin(User.class).sql(); // SELECT Address.* FROM User RIGHT JOIN Address ON User.id = Address.user_id
In a bidirectional one-to-one relationship, the foreign key column is assumed to be on the owning table, ie. the one without a value for mappedBy in the OneToOne annotation.
In a many-to-many relationship, the default join table is the name of the owning table and the name of the inverse table, separated by an underscore.
Extra clauses
sql-writer tries to stay close to SQL syntax, but there is one significant departure: the context for WHERE and ORDER BY clauses is given by the preceding call to join or from.
select().from(User.class).where().eq("id").from(Address.class).where().eq("street").sql(); // SELECT User.*, Address.* FROM User INNER JOIN Address ON User.id = Address.user_id WHERE User.id = ? AND Address.street = ?
Shortcuts
When no columns are specified in an update or insert query, all columns are included. By default, certain columns are excluded: generated values, static or transient fields, @Column(insertable = false), @Column(updatable = false), etc.
License
sql-writer is copyright Moandji Ezana and licensed under the MIT License.
TODO
- Allow customisation of how column and table names are generated
- Add WhereBuilder methods that take a class. Join class to current context if not already joined.
- Support alternative SQL syntaxes
- Generate mappings for lightweight field-name safety