CODEWITHSUNDEEP
javasql
Mr Asker
Mr Asker

Reputation: 2380

Method to create the sql query?

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 "";

}

Upvotes: 0

Views: 68

Answers (2)

dly
dly

Reputation: 1088

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(?, ?, ?)

Upvotes: 0

griFlo
griFlo

Reputation: 2164

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();

}

Upvotes: 1

Related Questions