README.md

MyJQL

A simple SQL query builder for MySQL

A simple tool to write queries programmatically taking advantage of a type safety system. Once you declare your tables with column types, then you can start writing queries with SQL, but using Java instead of plain text. This is not an ORM! The goal is to let you write the SQL that your already know, inside your programs, knowing that the resulting SQL won't be of any surprise.

• SELECT
• INSERT
• UPDATE
• DELETE
• MERGE
• Table declarations

Select

A simple select

SQL.create()
	.selectDistinct(Users.email, Users.password)
	.from(Users)
	.where(Users.username.equalUpper("ROBERT"))
	.toString();

translates into:

SELECT DISTINCT Users.email, Users.password 
FROM Users
WHERE Users.username = UPPER ('ROBERT')

Join tables

SQL.create()
	.selectDistinct(Users.email, Posts.title)
	.from(Posts)
	.innerJoin(Posts).on(Posts.userFk.equal(Users.pk))
        .where(Users.username.equalUpper("ROBERT21"))
	.toString();

translates into:

SELECT DISTINCT Users.email, Posts.title
FROM Posts
INNER JOIN Posts ON Posts.user_fk = Users.pk
WHERE Users.username = UPPER ('ROBERT21')

Date ranges

SQL.create()
	.selectDistinct(Users.username)
	.from(Users)
	.where(Users.birthDate.between("20011216", "20011218"))
	.toString();

translates into:

SELECT DISTINCT Users.username
FROM Users
WHERE Users.birth_date >= '20011216'
AND Users.birth_date <= '20011218'

Younger than / older than

SQL.create()
	.select(Posts.pk)
	.from(Posts)
	.where(Posts.modified.youngerThan(30))
	.and(Posts.modified.olderThan(15)
	.toString()

translates into:

SELECT Posts.pk
FROM Posts
WHERE TIMESTAMPDIFF (SECOND, Posts.modified, NOW()) < 30
AND TIMESTAMPDIFF (SECOND, Posts.modified, NOW()) > 15

Select count

SQL.create()
	.selectCount()
	.from(Posts)
	.where(Posts.userFk.equal(34))
	.toString();

translates into:

SELECT COUNT(*) FROM Posts WHERE Posts.user_fk = 34

Substitution variables

SQL.create()
	.select(Posts.pk)
	.from(Posts)
	.where(Posts.title.equal("?"))
	.toString();

translates into:

SELECT Posts.pk FROM Posts WHERE Posts.title = ?

Insert

String nullValue = null;
String notNullValue = "something";
        
SQL.create()
    .insertInto(Users)
    .columns(Users.email, Users.password, Users.creationTime, Users.address, Users.notes)
    .values("[email protected]", "?", DateTimeField::now, Nulls.nullable(nullValue), Nulls.nullable(notNullValue))
    .toString();

translates into:

INSERT INTO Users (email, password, creation_time) 
VALUES ('[email protected]', ?, now(), NULL, 'something')

Update

SQL.create()
    .update(Users)
    .set(Users.email, "[email protected]")
    .where(Users.email.equal("[email protected]"))
    .toString();

translates into:

UPDATE Users SET Users.email = '[email protected]' WHERE Users.email = '[email protected]'

Updates with conditionals

String newTitle = null;
String newMessage = "New message";
        
String statement = SQL.create()
    .update(Posts)
    .set(Posts.title, newTitle).onlyIf(newTitle != null)
    .set(Posts.message, newMessage).onlyIf(newMessage != null)
    .toString();

translates into:

UPDATE Posts SET Posts.message = 'New message'

Delete

Select select = SQL.create()
    .select(Users.pk)
    .from(Users)
    .where(Users.email.equal("[email protected]"));

String sql = SQL.create()
    .deleteFrom(Posts)
    .where(Posts.userFk.in(select))
    .toString();

translates into:

DELETE FROM Posts 
WHERE Posts.user_fk IN (
    SELECT Users.pk FROM Users WHERE Users.email = '[email protected]'
)

Merge

String upsert = new Merge()
        .mergeInto(Users)
        .on(Users.username.equal("roby456"), Users.email.equal("[email protected]"))
        .columns(Users.username, Users.email, Users.password)
        .values("roby456", "[email protected]", "newsecret")
        .toString();

translates into:

INSERT INTO Users(username, email, password)
VALUES ('roby456', '[email protected]', 'newsecret')
ON DUPLICATE KEY UPDATE Users.password='newsecret'

Tables declarations and usage

To use this library you have to define your tables in Java too. Nothing fancy here, just create a class that implements the Table interface. The toString() shall return the name of the table as defined in the schema. Every column has its own type too.

public class Users implements Table {
    public NumberField pk = new NumberField(this, "pk");
    public TextField username = new TextField(this, "username");
    public TextField password = new TextField(this, "password");
    public DateTimeField creationTime = new DateTimeField(this, "creation_time");

    public String toString() { return "Users"; }
}

Supported column types are:

• TextField
• NumberField
• DateField
• TimeField
• DateTimeField

Once you have declared your tables, you can create a Tables class like the following

package hijackit.myjql.schema;

public class Tables {
	public static Users Users = new Users();
	public static Posts Posts = new Posts();
}

and using static imports, you can write your queries like SQL:

import static hijackit.myjql.schema.Tables.Users;

String sql = SQL.create()
        .selectDistinct(Users.email, Users.password)
        .from(Users)
        .where(Users.username.equalUpper("ROBY456"))
        .toString();

Once you have your SQL statement, you can use it to run the queries with plain JDBC

Connection con = null;
PreparedStatement ps = null;
try {
    con = cp.getConnection();
    String statement = SQL.create()
        .insertInto(Users)
        .columns(Users.email, Users.password, Users.creationTime)
        .values("?", "?", DateTimeField::now)
        .toString();
    ps = con.prepareStatement(sql);
    ps.setString(1, "robert21")
    ps.setString(2, "secret")
    ps.executeUpdate();
} finally {
    close(ps, con);
}