jdbc - how to select query in one string

Question

I am trying to fetch the select queries in one string. This is what I want. I tried different ways, but could not solve. This is my code:

ResultSet rs = st.executeQuery("select city_ID from user where name = '"    + name + "'");

        String sql = "";
        while (rs.next()) {
            sql += "select * from city where ID = " +rs.getInt(1) + ",";
        }
        sql = sql.substring(0, sql.length()-1);     
        PreparedStatement stmt = con.prepareStatement(sql);
        rs = stmt.executeQuery();

I did this, but there are errors. How can I solve it? Basically, I want to add many queries in one string, and execute that string. Then I need them in something like resultSet.

The ID of the city is the same than the one of the user ? Are you sure of your schema ? — Denys Séguret, May 14 '13 at 15:19
BTW, it's easy to reduce this to one query but it looks more like a basic SQL question than a Java or JDBC one. — Denys Séguret, May 14 '13 at 15:20
Are you looking for this? http://stackoverflow.com/questions/10797794/multiple-queries-executed-in-java-in-single-statement — skandigraun, May 14 '13 at 15:20
javax.servlet.ServletException: "com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select * from city where ID = 2' at line 1" this is the error. — Burak Keceli, May 14 '13 at 15:22
@skandigraun at that question his queries are select and insert, but in my question I am doing just selection — Burak Keceli, May 14 '13 at 15:24
one quick suggestion , use preparesStatements , or else you will be viable to SQL injections — Hussain Akhtar Wahid 'Ghouri', May 14 '13 at 15:28

Simon Dorociak · Accepted Answer · 2013-05-14T15:58:41.143

I'm little confused from what are you trying to achieve but i guess you need this:

select * from city as c inner join user as u on (c.ID = u.city_ID) 
where u.name = ?;

Then you need only execute this one query and you have desired results.

But also as @dystroy mentioned if you want to use your approach you need at first fetch ids from User into some List<Integer> and then create some List<String> that will hold your queries or use IN clause in your query.

Pseudo-code:

// getting ids from User

List<Integer> cityIds = new ArrayList<Integer>();
String query = "select city_ID from user where name = ?";
PreparedStatement ps = con.prepareStatement(query);
ps.setString(1, name);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
   cityIds.add(rs.getInt("city_ID"));
}

// dynamically create your query

StringBuilder builder = new StringBuilder("select * from city where ID in (");
for (int i = 0; i < cityIds.size(); i++) {
   builder.append("?");
   if (i < cityIds.size() - 1) {
      builder.append(",");
   }
}
builder.append(")");

// creating PreparedStatement

ps = con.prepareStatement(builder.toString());

// adding params to query

int index = 1;
for (Integer id: cityIds) {
   ps.setInt(index, id);
   index++;
}

ResultSet rs = ps.executeQuery();
while (rs.next()) {
   // do your stuff
}

Note: I recommend you to use parametrized statements which provide much more safer dealing with database, are more human-readable and efficient.

I doubt the ID of the user is the same than the ID of the city (yes I know that what OP seemed to try). — Denys Séguret, May 14 '13 at 15:21

jdbc - how to select query in one string

1 Answers1