Hi.
I have query
Scheme schemes[] = ao.find(Scheme.class, Query.select("PROJECT_KEY").distinct());
That transforms to
SELECT DISTINCT FIELD_ORDER,DATA_SCHEME,ID,PROJECT_KEY,ISSUE_TYPE_NAME FROM AO_D3A8FC_SCHEME
When i expect something like
SELECT DISTINCT PROJECT_KEY FROM AO_D3A8FC_SCHEME;
and finally produces error
Servlet.service() for servlet [action] in context with path [] threw exception [com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library: Database: - name:MySQL - version:5.7.17-0ubuntu0.16.04.1 - minor version:7 - major version:5 Driver: - name:MySQL Connector Java - version:mysql-connector-java-5.1.39 ( Revision: 3289a357af6d09ecc1a10fd3c26e95183e5790ad ) java.sql.SQLException: Invalid value for getInt() - 'IN'] with root cause java.sql.SQLException: Invalid value for getInt() - 'IN' at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:963) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:896) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:885) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860) at com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:2517) at com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:2583) at org.apache.commons.dbcp2.DelegatingResultSet.getInt(DelegatingResultSet.java:283) at org.apache.commons.dbcp2.DelegatingResultSet.getInt(DelegatingResultSet.java:283) at net.java.ao.types.IntegerType.pullFromDatabase(IntegerType.java:38) at net.java.ao.types.IntegerType.pullFromDatabase(IntegerType.java:13) at net.java.ao.EntityManager.find(EntityManager.java:678) at net.java.ao.EntityManager.find(EntityManager.java:621) at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.find(EntityManagedActiveObjects.java:134) at com.atlassian.activeobjects.osgi.TenantAwareActiveObjects.find(TenantAwareActiveObjects.java:291) ... 2 filtered
Is this some kind of AO bug or i doing something wrong?
Is there are something like workaround exist?
Looks like there are a couple of related bugs https://ecosystem.atlassian.net/browse/AO-687?jql=project%20%3D%20AO%20AND%20text%20~%20"distinct"
But as I’m looking at this - I think ActiveObjects is doing what it’s supposed to do (well minus the DISTINCT piece). The “FIELD_ORDER,DATA_SCHEME,ID,PROJECT_KEY,ISSUE_TYPE_NAME” is being pulled to populate the Scheme entity that’s returned back. Can you use the ao.findWithSql method?
public abstract T[] findWithSQL (Class<T> type, String keyField, String sql, Object... parameters)
Executes the specified SQL and extracts the given key field, wrapping each row into a instance of the specified type. The SQL itself is executed as a java.sql.PreparedStatement with the given parameters.
Example:
manager.findWithSQL(Person.class, "personID", "SELECT personID FROM chairs WHERE position < ? LIMIT ?", 10, 5);
The SQL is not parsed or modified in any way by ActiveObjects. As such, it is possible to execute database-specific queries using this method without realizing it. For example, the above query will not run on MS SQL Server or Oracle, due to the lack of a LIMIT clause in their SQL implementation. As such, be extremely careful about what SQL is executed using this method, or else be conscious of the fact that you may be locking yourself to a specific DBMS.
Parameters
type The type of the entities to retrieve.
keyField The field value to use in the creation of the entities. This is usually the primary key field of the corresponding table.
sql The SQL statement to execute.
parameters A varargs array of parameters to be passed to the executed prepared statement. The length of this array must match the number of parameters (denoted by the '?' char) in the criteria.
Returns
An array of entities of the given type which match the specified query.
Thanks for your reply.
I change data storage scheme so there is no need to use distinct.
Bu i will try your solution later.
I have a similar problem too, but I am trying to use distinct with streams:
The below works but for large data sets, it takes a long time:
ao.stream(MyAOTable.class, entity -> {
});
But the below doesnt work - I want to limit the rows by using distinct:
ao.stream(MyAOTable.class, Query.select("MY_COL").distinct(), entity -> {
});
It complains MY_COL needs to be an ID when it cant be an ID as some other column is already an ID. I would like to avoid anything that involves using direct SQL queries as the plugin is used in confluence instances having different databases.
Please advise.