Method to create the sql query?

Question

Is it possible to create java method which created this SQL query programatically depends on the arrayList size since the arrayList size is 22 sometimes?

I appreciate any help!

private String create_sql_order_query(ArrayList<String> nameNumber) {

String sql_find_order2 = "SELECT s2.stop_id  FROM stops s1"
        + " JOIN stops s2 ON s1.stop_id = s2.stop_id - 1"
        + " WHERE CONCAT(s1.name, s2.name) = CONCAT(?,?)";

String sql_find_order3 = "SELECT s3.stop_id  FROM stops s1"
        + " JOIN stops s2 ON s1.stop_id = s2.stop_id - 1"
        + " JOIN stops s3 ON s2.stop_id = s3.stop_id - 1"
        + " WHERE CONCAT(s1.name, s2.name, s3.name) = CONCAT(?,?,?)";

String sql_find_order4 = "SELECT s4.stop_id  FROM stops s1"
        + " JOIN stops s2 ON s1.stop_id = s2.stop_id - 1"
        + " JOIN stops s3 ON s2.stop_id = s3.stop_id - 1"
        + " JOIN stops s4 ON s3.stop_id = s4.stop_id - 1 "
        + " WHERE CONCAT(s1.name, s2.name, s3.name, s4.name) = CONCAT(?,?,?,?)";

String sql_find_order5 = "SELECT s5.stop_id  FROM stops s1"
        + " JOIN stops s2 ON s1.stop_id = s2.stop_id - 1"
        + " JOIN stops s3 ON s2.stop_id = s3.stop_id - 1"
        + " JOIN stops s4 ON s3.stop_id = s4.stop_id - 1 "
        + " JOIN stops s5 ON s4.stop_id = s5.stop_id - 1"
        + " WHERE CONCAT(s1.name, s2.name, s3.name, s4.name) = CONCAT(?,?,?,?,?)";
  .
  .
  .
  .
return "";

}

score 1 · Accepted Answer · answered Jul 03 '15 at 07:02

Feel free to improve, but i think you get the idea:

private String createSqlStatement(int size) {
    StringBuilder sb = new StringBuilder();

    sb.append("SELECT s").append(size + 1).append(".stop_id  FROM stops s1 ").append("\n");
    for (int i = 1; i <= size; i++) {
        sb.append("  JOIN stops s").append(i + 1).append(" ON s").append(i).append(".stop_id = s").append(i + 1).append(".stop_id - 1")
                .append("\n");
    }
    sb.append(" WHERE CONCAT(");
    for (int i = 1; i <= size; i++) {
        sb.append("s").append(i).append(".name,");
    }
    sb.deleteCharAt(sb.length() - 1);
    sb.append(") = CONCAT(");
    for (int i = 0; i <= size; i++) {
        sb.append("?,");
    }
    sb.deleteCharAt(sb.length() - 1);
    sb.append(");");

    return sb.toString();

}

score 0 · Answer 2 · answered Jul 03 '15 at 07:06

Probably not the most efficient way, but easy to figure out...

ArrayList<String> nameNumber = new ArrayList<String>();
        nameNumber.add("foo");
        nameNumber.add("bar");
        nameNumber.add("baz");

        String sql_find_order = "SELECT s" + nameNumber.size() + ".stop_id  FROM stops s1";
        String putInWhere1 = "";
        String putInWhere2 = "";
        for(int i = 0; i < nameNumber.size(); i++) {
            sql_find_order += " JOIN stops s" + (i + 2) + " ON s1.stop_id = s2.stop_id - 1";
            putInWhere1 += ", s" + (i + 1) + ".name";
            putInWhere2 += ", ?";
        }

        sql_find_order += " WHERE CONCAT(" + putInWhere1.substring(2) + ") = CONCAT(" + putInWhere2.substring(2) + ")";
        System.out.println(sql_find_order);

Result

SELECT s3.stop_id  FROM stops s1 JOIN stops s2 ON s1.stop_id = s2.stop_id - 1 JOIN stops s3 ON s1.stop_id = s2.stop_id - 1 JOIN stops s4 ON s1.stop_id = s2.stop_id - 1 WHERE CONCAT(s1.name, s2.name, s3.name) = CONCAT(?, ?, ?)

I would use a StringBuilder instead of String concatenation (see http://stackoverflow.com/questions/18453458/string-builder-vs-string-concatenation) — griFlo, Jul 03 '15 at 07:16
Yes, you're right. Like I said it's not the most efficient way, but an easy approach. — dly, Jul 03 '15 at 07:17

Method to create the sql query?

2 Answers2