How to set multiple parameters

Question

I was wondering how I could set multiple parameters to different values, so I can get 4 different results from the same query. Can you do a loop? If yes, then how would you do it, and can you use the same executeQuery?

mapinfo = c.prepareStatement(
        " SELECT TOP 1 M.ID, M.Name, COUNT(DISTINCT C.Name) 'Cities' , "
                + " COUNT(DISTINCT R2.IDfrom + R2.IDto) Roads, (SELECT AVG(R.Distance) 'Average' FROM ROAD R WHERE R.MapID = ?) Average, "
                + " MAX(R.Distance) 'Max Distance', C2.Name 'Start city', C1.Name 'End city' "
                + " FROM MAP M "
                + " LEFT JOIN ROAD R ON R.MapID = R.MapID "
                + " LEFT JOIN CITY C ON C.MapID = R.MapID "
                + " JOIN CITY C1 ON R.IDfrom = C1.ID "
                + " JOIN CITY C2 ON R.IDto = C2.ID "
                + " INNER JOIN ROAD R2 ON M.ID = R2.MapID "
                + " WHERE M.ID = ? AND C.MapID = ? AND R.MapID = ? AND R2.IDfrom < R2.IDto "
                + " GROUP BY M.ID, M.Name, R.MapID, C.MapID, C1.Name, C2.Name "
                + " ORDER BY 'Max Distance' DESC "
);

// Execute MapInfo and loop through the result

mapinfo.setInt(1, 1);
mapinfo.setInt(2, 1);
mapinfo.setInt(3, 1);
mapinfo.setInt(4, 1);

mapinfo.setInt(1, 2);
mapinfo.setInt(2, 2); //I wanna get a different result with setting the parameters to a differnt value
mapinfo.setInt(3, 2); 
mapinfo.setInt(4, 2);

mapresult = mapinfo.executeQuery();
while (mapresult.next()) {
    // retrieve result
    int mapid = mapresult.getInt(1);
    String mapname = mapresult.getString(2);
    int numOfCities = mapresult.getInt(3);
    int numOfRoads = mapresult.getInt(4);
    int maxDistance = mapresult.getInt(5);
    int average = mapresult.getInt(6);
    String start = mapresult.getString(7);
    String end = mapresult.getString(8);

    // write output
    Terminal.put("---------------------------------------\n" + "Map: " + mapname + " (" + mapid + ")\n"
            + "Cities: " + numOfCities + "\n" + "Roads: " + numOfRoads + "\n" + "Average Road Length: "
            + average + " km" + "\n" + "The longest road runs: " + maxDistance + " km" + "\n" + "Start: "
            + start + "\n" + "End: " + end + "\n" + "---------------------------------------");

}

mapresult.close();

Just run executeQuery multiple times and set the parameters in between? — dan1st
– dan1st, Commented Jun 17, 2021 at 14:47

rzwitserloot · Accepted Answer · 2021-06-17 14:50:03Z

2

mapinfo.setInt(2, 1); mapinfo.setInt(2, 2);

This just overwrites. You don't wanna do this.

I was wondering how I could set multiple parameters to different values so I can get 4 different results from the same query

First build the SQL query that gives you what you want, then translate it to java. In this case, that means completely redesigning the query.

Alternatively, run this one query, but run it 4 times in a row. Yes, you can re-use a preparedstatement:

for (int i = 0; i < 4; i++) {
    mapinfo.setInt(1, i + 1);
    mapinfo.setInt(2, i + 1);
    mapinfo.setInt(3, i + 1);
    mapinfo.setInt(4, i + 1);
    try (ResultSet rs = mapinfo.executeQuery()) {
        while (rs.next()) {
          // process a result row
        }
    }
}

answered Jun 17, 2021 at 14:50

rzwitserloot

107k6 gold badges74 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Oliver Over a year ago

Thanks sorted out my problem

Collectives™ on Stack Overflow

How to set multiple parameters

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related