Home » Eclipse Projects » Eclipse Scout » SQL.selectInto(...) with IN clause and Grouping
| SQL.selectInto(...) with IN clause and Grouping [message #1728481] |
Mon, 04 April 2016 14:36  |
Eclipse User |
|
|
|
Hi there,
I'm currently investigating a weird problem.
I have got this SQL statement:
SQL.selectInto(
"SELECT ArticleID, SUM(IFNULL(Amount,0)), SUM(IFNULL(ArticlePiecesPerBunch,0)*IFNULL(Amount,0)) FROM OrdersDetails "
+ " WHERE OrderID IN (:{orderIDs}) GROUP BY ArticleID ORDER BY ArticleID "
+ " INTO :article, :amount, :pieces",
sums, new NVPair("orderIDs", orderIDs));
Where orderIDs is an ArrayList with selected Orders. When I invoke this SQL statements I get the orders that way (just printed ArticleID for brevity):
1023
1016
1046
2025
2085
2025
2185
3045
3095
3505
As you can see the article number "2025" is printed more than once, although it should be grouped together.
When I take the exact same SQL statement, paste it to MySQLWorkbench
SELECT ArticleID, SUM(IFNULL(Amount,0)), SUM(IFNULL(ArticlePiecesPerBunch,0)*IFNULL(Amount,0)) FROM BFZ.OrdersDetails WHERE OrderID IN (30, 27, 17) GROUP BY ArticleID ORDER BY ArticleID;
and then invoke it, I get this result
1016
1023
1046
2025
2085
2185
3045
3095
3505
As you can see its sorted and grouped correctly.
So I assume the statement processor of Scout is invoking the statement for every OrderID on its own (in this case 3 times) and then a List with the result of the 3 separate queries is returned.
Is there a way to make the SQL statement work as expected or do I have to manually group the result in code (which is very cumbersome).
Can any one prove or disprove my assumption?
Thanks,
Peter
|
|
| | | | |
| Re: SQL.selectInto(...) with IN clause and Grouping [message #1728723 is a reply to message #1728557] |
Wed, 06 April 2016 12:51  |
| Eclipse User |
|
|
|
I also did some tests on my side, using the PERSON table from the Contacts Demo application. (Derby Database):
Input Data:
+------------+------------+----------------+---------+
| first_name | last_name | city | country |
+------------+------------+----------------+---------+
| Anna | Mills | Arlington | US |
| Melody | Bergnaum | Portland | US |
| Bart | Zulauf | Arlington | US |
| Wilmer | Fay | Virginia Beach | US |
| Axel | Hahn | Chandler | US |
| Hermina | Borer | Birmingham | GB |
| Peter | Crist | Portland | US |
| Daisha | Rohan | London | GB |
| Lonnie | Marks | London | GB |
| Evert | Johnson | Birmingham | GB |
| Holly | Beier | London | GB |
| Grant | Reichel | London | GB |
| Xander | Spinka | Portland | US |
| Cara | Turcotte | Virginia Beach | US |
| Jade | McLaughlin | Portland | US |
+------------+------------+----------------+---------+
Statement 1:
Object[][] data = SQL.select("select city, count(1) from PERSON group by city");
The data object array contains the values:
+----------------+---+
| Arlington | 3 |
| Birmingham | 2 |
| Chandler | 2 |
| London | 6 |
| Portland | 4 |
| Virginia Beach | 3 |
+----------------+---+
Statement 2:
Object[][] data = SQL.select("select city, count(1) from PERSON where country in ('GB', 'US') group by city");
Same result as for statement 1.
Statement 3:
Object[][] data = SQL.select("select city, count(1) from PERSON where country in ('GB') group by city");
The data object array contains the values:
+------------+---+
| Birmingham | 2 |
| London | 6 |
+------------+---+
Statement 4:
List<String> countries = Arrays.asList("GB", "US");
Object[][] data = SQL.select("select city, count(1) from PERSON where country=:c group by city", new NVPair("c", countries));
Same result as for statement 1.
Statement 5:
List<String> countries = Arrays.asList("GB");
Object[][] data = SQL.select("select city, count(1) from PERSON where country=:c group by city", new NVPair("c", countries));
Same result as for statement 3.
Statement 6:
List<String> countries = Collections.emptyList();
Object[][] data = SQL.select("select city, count(1) from PERSON where country=:c group by city", new NVPair("c", countries));
The data object array is an empty array (data.length == 0):
It works exactly as expected.
-------------------
Now for the SelectInto part: you need to provide a Java Structure that is a compatible "batch INTO bind" (referring to the JavaDoc of ISqlService). If you have one of those, "For every row in the select, one row is created and filled up".
=> java.util.List is not one of those. The reason is simple: there in not enough information (do not forget the java type erasure of the generics parameter). The SqlService, needs to know how to create a new list element for each row in the SQL result set.
You can use:
* XxxxArrayHolder (like LongArrayHolder as told by Samuel)
* In particular BeanArrayHolder (see my example below)
* If you are working with FormData, the your TableFieldBeanData classes (or TableFieldData classes) corresponding to the table-fields of your form.
* PageData classes.
Example 1:
List<String> countries = Arrays.asList("GB", "US");
BeanArrayHolder<PersonInCityPojo> data = new BeanArrayHolder<PersonInCityPojo>(PersonInCityPojo.class);
SQL.selectInto("select city, count(1) from PERSON where country=:c group by city INTO :result.cityName, :result.personCount", new NVPair("c", countries), new NVPair("result", data));
//Continue with data.getBeans() and/or data.getBeanCount()
... and you will get the expected result.
Where PersonInCityPojo is a simple java bean (or POJO):
public class PersonInCityPojo {
private String cityName;
private int personCount;
public String getCityName() {
return cityName;
}
public void setCityName(String cityName) {
this.cityName = cityName;
}
public int getPersonCount() {
return personCount;
}
public void setPersonCount(int personCount) {
this.personCount = personCount;
}
}
Using curly brackets in the Input-Binds is not a good idea. Quoting the JavaDoc of ISqlService:
Quote:Input Binds:
(...) :{name} is a batch value. For every value in the array the statement is executed once.
=> I think that you can use this construct for insert statements.
Again, I know that the documentation of the SQL Service is far from perfect. Feel free to contribute improvements (or ideas).
|
|
|
Goto Forum:
Current Time: Sun Aug 31 17:54:50 EDT 2025
Powered by FUDForum. Page generated in 0.04082 seconds |
Home