SQL

Write the query where you can see it, mark the values that come from Java, and the compiler parses it while it builds. What you get back is a class whose constructor takes those values and whose getStatement(connection) hands you a prepared statement.

Because the values are bound rather than concatenated, a query cannot be assembled out of user input — SQL injection is not defended against here, it is unrepresentable.

Writing one

package examples.simplesql;

import org.jmod.dsl.sql.SQLQuery;

public external SelectExample extends SQLQuery<SimpleConf> {
select * from sqlexample where sqle_primary = #[prim]<int>
}

PreparedStatement ps = new SelectExample(7).getStatement(connection);
ResultSet rs = ps.executeQuery();

Each #[name]<Type> becomes a constructor parameter and a bound placeholder, with the JDBC setter chosen from the Java type. An array becomes an IN list of the right length at runtime — something JDBC cannot do on its own — and an empty one raises SQLException rather than producing invalid SQL.

select nickname from users where nickname in #[names]<String[]>

Checking against your schema

Point a configuration class at your CREATE TABLE statements and the compiler checks the query against them. No database is involved, and nothing is vendor-specific:

public class Conf extends SQLConfiguration {
    public boolean SQLMOD_NS_AWARE = true;
    public String SQLMOD_NS_URI = "file://./schema.sql";
}

Q.jmod: column emial does not exist in the database schema

Q.jmod: type incompatibility: #[id]<String> is not compatible with column usr_id (INT)

The second one is the reason to bother: nothing about that Java is wrong on its own — a String id compiles, binds and quietly returns no rows — and only the schema makes it an error.

Set SQLMOD_LIVE_TEST (with the JDBC driver, URL and login) and the query is also run against a real database while you build, with stand-in values. That catches what a schema file cannot: a view that no longer exists, a permission, a dialect your server does not accept. Both checks are off by default, and the credentials never reach the compiled program.

Worth knowing

One statement per external type, and it has to be one the parser accepts. Schema checking covers the tables, columns and bound values a query names — it is not a query planner, and it will not tell you the join is wrong.