Cleanest way to build an SQL string in Java

Question

I want to build an SQL string to do database manipulation (updates, deletes, inserts, selects, that sort of thing) - instead of the awful string concat method using millions of "+"'s and quotes which is unreadable at best - there must be a better way.

I did think of using MessageFormat - but its supposed to be used for user messages, although I think it would do a reasonable job - but I guess there should be something more aligned to SQL type operations in the java sql libraries.

Would Groovy be any good?

Lake

Answer 1

First of all consider using query parameters in prepared statements:

PreparedStatement stm = c.prepareStatement("UPDATE user_table SET name=? WHERE id=?");
stm.setString(1, "the name");
stm.setInt(2, 345);
stm.executeUpdate();

The other thing that can be done is to keep all queries in properties file. For example in a queries.properties file can place the above query:

update_query=UPDATE user_table SET name=? WHERE id=?

Then with the help of a simple utility class:

public class Queries {

    private static final String propFileName = "queries.properties";
    private static Properties props;

    public static Properties getQueries() throws SQLException {
        InputStream is = 
            Queries.class.getResourceAsStream("/" + propFileName);
        if (is == null){
            throw new SQLException("Unable to load property file: " + propFileName);
        }
        //singleton
        if(props == null){
            props = new Properties();
            try {
                props.load(is);
            } catch (IOException e) {
                throw new SQLException("Unable to load property file: " + propFileName + "\n" + e.getMessage());
            }           
        }
        return props;
    }

    public static String getQuery(String query) throws SQLException{
        return getQueries().getProperty(query);
    }

}

you might use your queries as follows:

PreparedStatement stm = c.prepareStatement(Queries.getQuery("update_query"));

This is a rather simple solution, but works well.

Appreciative

I prefer to use a clean SQL builder like this one: mentabean.soliveirajr.com – Subordinary

May I suggest you put the InputStream inside of the if (props == null) statement so that you don't instantiate it when it is not needed. – Defoliant

What is c in the first and last code snippet? – Monachism

@AlexanderTerp it is an active Connection – Estrade

Answer 2

For arbitrary SQL, use jOOQ. jOOQ currently supports SELECT, INSERT, UPDATE, DELETE, TRUNCATE, and MERGE. You can create SQL like this:

String sql1 = DSL.using(SQLDialect.MYSQL)  
                 .select(A, B, C)
                 .from(MY_TABLE)
                 .where(A.equal(5))
                 .and(B.greaterThan(8))
                 .getSQL();

String sql2 = DSL.using(SQLDialect.MYSQL)  
                 .insertInto(MY_TABLE)
                 .values(A, 1)
                 .values(B, 2)
                 .getSQL();

String sql3 = DSL.using(SQLDialect.MYSQL)  
                 .update(MY_TABLE)
                 .set(A, 1)
                 .set(B, 2)
                 .where(C.greaterThan(5))
                 .getSQL();

Instead of obtaining the SQL string, you could also just execute it, using jOOQ. See

http://www.jooq.org

(Disclaimer: I work for the company behind jOOQ)

Playgoer

would this not in many cases be a poor solution as you are unable let the dbms parse the statement beforehand with different values for "5", "8", etc.? I guess executing with jooq would solve it? – Dannadannel

@Vegard: You have full control over how jOOQ should render bind values in its SQL output: jooq.org/doc/3.1/manual/sql-building/bind-values. In other words, you get to chose whether to render "?" or whether to inline bind values. – Playgoer

aye, but in regard to clean ways to build sql, this would be a bit messy code in my eyes if you are not using JOOQ to execute. in this example you set A to 1, B to 2 etc, but you have to do it one more time when you execute if you are not executing with JOOQ. – Dannadannel

@Vegard: Nothing keeps you from passing a variable to the jOOQ API, and rebuild the SQL statement. Also, you can extract bind values in their order using jooq.org/javadoc/latest/org/jooq/Query.html#getBindValues(), or named bind values by their names using jooq.org/javadoc/latest/org/jooq/Query.html#getParams(). My answer just contains a very simplistic example... I'm not sure if this responds to your concerns, though? – Playgoer

you have a very good point, using those methods should lead to cleaner code than what I originally thought of. What I also love about jOOQ is the ability to write SQLs somewhat typesafe. – Dannadannel

Its a costly solution. – Shopkeeper

Don't forget to add a disclaimer stating you're the CEO of the company behind jOOQ. ;) – Maggee

@Stephan: Indeed, thanks for the hint. I've retroactively added that disclaimer to most answers, but there are still 1-2 out there which haven't been amended yet. – Playgoer

@ChitrangSharma: Please ask a new question – Playgoer

Answer 3

One technology you should consider is SQLJ - a way to embed SQL statements directly in Java. As a simple example, you might have the following in a file called TestQueries.sqlj:

public class TestQueries
{
    public String getUsername(int id)
    {
        String username;
        #sql
        {
            select username into :username
            from users
            where pkey = :id
        };
        return username;
    }
}

There is an additional precompile step which takes your .sqlj files and translates them into pure Java - in short, it looks for the special blocks delimited with

#sql
{
    ...
}

and turns them into JDBC calls. There are several key benefits to using SQLJ:

• completely abstracts away the JDBC layer - programmers only need to think about Java and SQL
• the translator can be made to check your queries for syntax etc. against the database at compile time
• ability to directly bind Java variables in queries using the ":" prefix

There are implementations of the translator around for most of the major database vendors, so you should be able to find everything you need easily.

Faxan

This one is outdated now, as per wikipedia. – Barrett

At the time of writing (Jan 2016) SQLJ is referred to on Wikipedia as "outdated" without any references. Has it been officially abandoned? If so, I'll stick a warning at the top of this answer. – Faxan

NB The technology is still supported for example in the latest version of Oracle, 12c. I'll admit it's not the most modern standard, but it still works and has some benefits (such as compile-time verification of queries against the DB) that are not available in other systems. – Faxan

Answer 4

I am wondering if you are after something like Squiggle (GitHub). Also something very useful is jDBI. It won't help you with the queries though.

Stair

Answer 5

I would have a look at Spring JDBC. I use it whenever I need to execute SQLs programatically. Example:

int countOfActorsNamedJoe
    = jdbcTemplate.queryForInt("select count(0) from t_actors where first_name = ?", new Object[]{"Joe"});

It's really great for any kind of sql execution, especially querying; it will help you map resultsets to objects, without adding the complexity of a complete ORM.

Strictly

how can I get real executed sql query? I want to log it. – Concurrence

Answer 6

I have been working on a Java servlet application that needs to construct very dynamic SQL statements for adhoc reporting purposes. The basic function of the app is to feed a bunch of named HTTP request parameters into a pre-coded query, and generate a nicely formatted table of output. I used Spring MVC and the dependency injection framework to store all of my SQL queries in XML files and load them into the reporting application, along with the table formatting information. Eventually, the reporting requirements became more complicated than the capabilities of the existing parameter mapping frameworks and I had to write my own. It was an interesting exercise in development and produced a framework for parameter mapping much more robust than anything else I could find.

The new parameter mappings looked as such:

select app.name as "App", 
       ${optional(" app.owner as "Owner", "):showOwner}
       sv.name as "Server", sum(act.trans_ct) as "Trans"
  from activity_records act, servers sv, applications app
 where act.server_id = sv.id
   and act.app_id = app.id
   and sv.id = ${integer(0,50):serverId}
   and app.id in ${integerList(50):appId}
 group by app.name, ${optional(" app.owner, "):showOwner} sv.name
 order by app.name, sv.name

The beauty of the resulting framework was that it could process HTTP request parameters directly into the query with proper type checking and limit checking. No extra mappings required for input validation. In the example query above, the parameter named serverId would be checked to make sure it could cast to an integer and was in the range of 0-50. The parameter appId would be processed as an array of integers, with a length limit of 50. If the field showOwner is present and set to "true", the bits of SQL in the quotes will be added to the generated query for the optional field mappings. field Several more parameter type mappings are available including optional segments of SQL with further parameter mappings. It allows for as complex of a query mapping as the developer can come up with. It even has controls in the report configuration to determine whether a given query will have the final mappings via a PreparedStatement or simply ran as a pre-built query.

For the sample Http request values:

showOwner: true
serverId: 20
appId: 1,2,3,5,7,11,13

It would produce the following SQL:

select app.name as "App", 
       app.owner as "Owner", 
       sv.name as "Server", sum(act.trans_ct) as "Trans"
  from activity_records act, servers sv, applications app
 where act.server_id = sv.id
   and act.app_id = app.id
   and sv.id = 20
   and app.id in (1,2,3,5,7,11,13)
 group by app.name,  app.owner,  sv.name
 order by app.name, sv.name

I really think that Spring or Hibernate or one of those frameworks should offer a more robust mapping mechanism that verifies types, allows for complex data types like arrays and other such features. I wrote my engine for only my purposes, it isn't quite read for general release. It only works with Oracle queries at the moment and all of the code belongs to a big corporation. Someday I may take my ideas and build a new open source framework, but I'm hoping one of the existing big players will take up the challenge.

Seaware

Answer 7

I tend to use Spring's Named JDBC Parameters so I can write a standard string like "select * from blah where colX=':someValue'"; I think that's pretty readable.

An alternative would be to supply the string in a separate .sql file and read the contents in using a utility method.

Oh, also worth having a look at Squill: https://squill.dev.java.net/docs/tutorial.html

Atlanta

I assume you mean you're using BeanPropertySqlParameterSource? I almost agree with you, the class I just mentioned is cool when using strictly beans but otherwise I'd recommend using custom ParameterizedRowMapper to construct objects. – Iosep

Not quite. You can use any SqlParameterSource with Named JDBC Parameters. It suited my needs to use a MapSqlParameterSource, rather than the bean variety. Either way, it's a good solution. The RowMappers, however, deal with the other side of the SQL puzzle: turning resultsets into objects. – Atlanta

Answer 8

I second the recommendations for using an ORM like Hibernate. However, there are certainly situations where that doesn't work, so I'll take this opportunity to tout some stuff that i've helped to write: SqlBuilder is a java library for dynamically building sql statements using the "builder" style. it's fairly powerful and fairly flexible.

Retouch

Answer 9

Why do you want to generate all the sql by hand? Have you looked at an ORM like Hibernate Depending on your project it will probably do at least 95% of what you need, do it in a cleaner way then raw SQL, and if you need to get the last bit of performance you can create the SQL queries that need to be hand tuned.

Doley

Answer 10

You can also have a look at MyBatis (www.mybatis.org) . It helps you write SQL statements outside your java code and maps the sql results into your java objects among other things.

Diamine

Answer 11

Google provides a library called the Room Persitence Library which provides a very clean way of writing SQL for Android Apps, basically an abstraction layer over underlying SQLite Database. Bellow is short code snippet from the official website:

@Dao
public interface UserDao {
    @Query("SELECT * FROM user")
    List<User> getAll();

    @Query("SELECT * FROM user WHERE uid IN (:userIds)")
    List<User> loadAllByIds(int[] userIds);

    @Query("SELECT * FROM user WHERE first_name LIKE :first AND "
           + "last_name LIKE :last LIMIT 1")
    User findByName(String first, String last);

    @Insert
    void insertAll(User... users);

    @Delete
    void delete(User user);
}

There are more examples and better documentation in the official docs for the library.

There is also one called MentaBean which is a Java ORM. It has nice features and seems to be pretty simple way of writing SQL.

Selfconfidence

As per the Room documentation : Room provides an abstraction layer over SQLite to allow fluent database access while harnessing the full power of SQLite. So, it's not a generic ORM library for RDBMS. It's primarily meant for Android Apps. – Edwyna

Answer 12

Read an XML file.

You can read it from an XML file. Its easy to maintain and work with. There are standard STaX, DOM, SAX parsers available out there to make it few lines of code in java.

Do more with attributes

You can have some semantic information with attributes on the tag to help do more with the SQL. This can be the method name or query type or anything that helps you code less.

Maintaince

You can put the xml outside the jar and easily maintain it. Same benefits as a properties file.

Conversion

XML is extensible and easily convertible to other formats.

Use Case

Metamug uses xml to configure REST resource files with sql.

Shopkeeper

You can use yaml or json if you like them. They are better than storing in a plain properties file – Shopkeeper

The question is how to BUILD SQL. To build SQL, if you need to use XML, Parser, Validation, etc. it's overburden. Most of the early attempts that involved XML to build SQL are being turned away in favor of Annotation. The accepted answer by Piotr Kochański is simple and elegant and to the point - solves the problem and maintainable. NOTE: There is NO alternate way to maintain a better SQL in a different language. – Edwyna

I deleted my previous comment I don't see a reason to make use of XML. , as I couldn't edit it. – Edwyna

Answer 13

If you put the SQL strings in a properties file and then read that in you can keep the SQL strings in a plain text file.

That doesn't solve the SQL type issues, but at least it makes copying&pasting from TOAD or sqlplus much easier.

Demakis

Answer 14

How do you get string concatenation, aside from long SQL strings in PreparedStatements (that you could easily provide in a text file and load as a resource anyway) that you break over several lines?

You aren't creating SQL strings directly are you? That's the biggest no-no in programming. Please use PreparedStatements, and supply the data as parameters. It reduces the chance of SQL Injection vastly.

Windbag

But if you are not exposing a web page to the public - is SQL Injection a relevant issue? – Lake

SQL Injection is always relevant, because it can happen accidentally as well as by intent. – Prosthodontist

@Lake - you might not be exposing the web page to the public now, but even code that will "always" be internal often ends up getting some kind of external exposure some point further down the line. And it's both faster and more secure to do it right first time round than have to audit the entire codebase for issues later... – Karinakarine

Even a PreparedStatement needs to be created from a String, no? – Clothier

Yes, but it's safe to build a PreparedStatement from a String, as long as you build a safe PreparedStatement. You probably should write a PreparedStatementBuilder class to generate them, to hide the mess of concatenating things. – Windbag

This article would be useful, to understand how to handle 'SQL Injection' issue. – Edwyna