How to create multiline strings for sql queries that are readable and maintainable and fast?

I have some SQL commands that I am trying to figure out the best way to have them in code so that:
1. They are very readable
2. They would be easy to update
3. They won't be performance overhead due to many string construction.

I have something like the following that did not turn out too good.

    public class SQLHandler {         private static final String CUSTOMER_TABLE = "customer_table";         private static final String CUSTOMER_ID = "id";         private static final String CUSTOMER_FIRST_NAME = "first_name";           private static final String CUSTOMER_LAST_NAME = "last_name";        private static final String CUSTOMER_TELEPHONE = "customer_telephone";         private static final String REP_ID = "customer_representative_id";             private static final String REP_TABLE = "representative_table";         private static final String REP_ID = "id";         private static final String REP_FIRST_NAME = "first_name";           private static final String LAST_LAST_NAME = "last_name";        private static final String DEPT_ID = "rep_dep_id";          public static ArrayList<Representatives> getRepresentatives(int customerId) {          StringBuilder sb = new StringBuilder();        sb.append("SELECT")           .append(REP_TABLE).append(".").append(REP_ID)          .append(",")           .append(REP_TABLE)          .append(".")           .append(REP_FIRST_NAME).append(" FROM")        .append(CUSTOMER_TABLE).append(" JOIN ").append(REP_TABLE)           .append("ON").append(REP_TABLE).append(".")             .append(REP_ID).append("=").append(CUSTOMER_TABLE)           .append(".").append(REP_ID)         .append(" AND")         .append(CUSTOMER_TABLE).append(".").append(CUSTOMER_ID)          .append("=").append(String.valueOf(customerId));        // do query       }    }  

As you can see none of the (3) are met.
I can't easily update the query and if I saw it again I wouldn't remember exactly what was it.
How can I improve this? (Couldn't format it properly in post)

Replay

My suggestion is:

public class SQLHandler {  

    private String sql=
    "SELECT \n"+
    "   c.customer_representative_id, \n"+
    "   c.first_name \n"+
    "FROM \n"+
    "   customer_table c JOIN representative_table r ON \n"+
    "   r.customer_representative_id=c.id \n"+
    "WHERE \n"+
    "   customer_table.id=? \n";

    // methods and so on...

}

The "\n" characters at the end of everyline makes for a nice printing for debugging purposes.

Later user a PreparedStatement to substitute "?" thus preventing sql injection:

PreparedStatement ps = con.prepareStatement(sql);
ps.setInt(customed_id);
ResultSet rs = ps.executeQuery();
/// etc...



Another alternative is reading the SQL String from a file that can be done with the Properties class. Such file can have multiple properties, each one being a different SQL query or other kinds of configuration values you want.

Sample file (the " \" is to allow multiline value for a property but it's read as a single line)

SQL1=\
SELECT \
    c.customer_representative_id, \
    c.first_name \
FROM \
    customer_table c JOIN representative_table r ON \
    r.customer_representative_id=c.id \
WHERE \
    customer_table.id=? \

Which can be loaded like this:

import java.util.Properties;
///...
Properties prop = new Properties();
prop.load(new FileInputStream("sql_queries.conf"));
String sql = prop.getProperty("SQL1");

Then user a PreparedStatement ...

Bonus suggestion:

Create views with the joins that you'll be doing frequently, so the queries look simpler in the code or in ther property files, like:

SELECT
    v.customer_representative_id,
    v.first_name,
        //other columns
FROM
    my_reps_view v
WHERE
    v.customer_table.id=?

If you don't want the overhead of a "fluent"-style Java query builder (like jOOQ or something), I've used static strings for queries formatted like so:

public class SQLHandler {
    private static final String REPRESENTATIVES_QUERY =
        " SELECT" +
        "   representative_table.id," +
        "   representative_table.first_name," +
        " FROM" +
        "   customer_table" +
        " JOIN representative_table ON" +
        "   representative_table.id = customer_table.id" +
        " WHERE" +
        "   customer_table.id = ?";

    public static ArrayList<Representatives> getRepresentatives(int customerId) {
        // do query
    }
}

Category: java Time: 2016-07-29 Views: 1

Related post

iOS development

Android development

Python development

JAVA development

Development language

PHP development

Ruby development

search

Front-end development

Database

development tools

Open Platform

Javascript development

.NET development

cloud computing

server

Copyright (C) avrocks.com, All Rights Reserved.

processed in 0.154 (s). 13 q(s)