**Issue Update
So it seems the problems described below are not with the query or using the LAST_VALUE / PARTITION. The whole time I've been testing this new query with JPA through JUnit. In my JUnit setup I have the following setup / teardown...
@Before
public void init() {
myDao.setEntityManager(entityManager);
entityManager.getTransaction().begin();
populateTestData();
}
@After
public void cleanUp() {
entityManager.flush();
entityManager.getTransaction().rollback();
}
private void populateTestData() {
for(MyModelObject modelObject: DataStore.getAllDummyData()){
myDao.persist(modelObject);
}
}
If I create an new, but identical class to MyModelObject, say MyModelObject2 and persist with MyModelObject, but retrieve with MyModelObject2, then everything works properly and I get the query results I'd expect.
Obviously I don't want to have a duplicate class as a hack for this, but this leads me to believe the funky results are due to the objects I'm persisting being managed in the persistence context for the life of the test. It doesn't matter if I use persist() or merge(), I get the same results.
Can anyone advise on how I could properly handle this situation? It seems I need to avoid the managed objects to see the actual query results needed. Perhaps there needs to be a commit of the data first, before retrieving results? How could I modify the JUnit init() and cleanUp() above to allow me to insert some dummy data, run a test that's not affected by the managed objects in the transaction, and then cleanup/rollback the dummy data?
The Issue
I have a query I'm testing in SQL Developer Lite for use in creating a hibernate native query. The issue I'm having is when I run the query in the SQL Developer Tool, it works as I'd expect. But running the same exact query through Hibernate gives me different results. It's almost like the Hibernate version doesn't care about the LAST_VALUE function and/or the PARTITION BY clause.
Here's the query I'm running in SQL Dev Lite
SELECT
LAST_VALUE(serviceid) OVER (PARTITION BY logid ORDER BY serviceid RANGE
BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) serviceid,
logid, type, startdate, stopdate, agent, userid, lastupdated
FROM (
SELECT * FROM SERVICELOGS WHERE logid IN ('2314-4523-8897-0923-8734')
ORDER BY lastupdatedtime DESC
)
ORDER BY lastupdatedtime DESC
Here's the result
You'll see in this example that serviceid has been populated for all rows... If I remove the LAST_VALUE and PARTITION then it'd return a resultset the same as what's in my next example.
**************************************************
| ServiceID | LogID | ...
**************************************************
| 1234567 | 2314-4523-8897-0923-87 | ..
| 1234567 | 2314-4523-8897-0923-87 | ..
| 1234567 | 2314-4523-8897-0923-87 | ..
| 1234567 | 2314-4523-8897-0923-87 | ..
| 1234567 | 2314-4523-8897-0923-87 | ..
| 1234567 | 2314-4523-8897-0923-87 | ..
| 1234567 | 2314-4523-8897-0923-87 | ..
| 1234567 | 2314-4523-8897-0923-87 | ..
| 1234567 | 2314-4523-8897-0923-87 | ..
| 1234567 | 2314-4523-8897-0923-87 | ..
| 1234567 | 2314-4523-8897-0923-87 | ..
| 1234567 | 2314-4523-8897-0923-87 | ..
| 1234567 | 2314-4523-8897-0923-87 | ..
| 1234567 | 2314-4523-8897-0923-87 | ..
| 1234567 | 2314-4523-8897-0923-87 | ..
Now here's the query from the hibernate side...
Here I'm just building the query string in a helper class..
private static final StringBuilder BASIC_QUERY_STRING = new StringBuilder();
static {
BASIC_QUERY_STRING
.append(" SELECT ")
.append(" LAST_VALUE(serviceid) OVER (PARTITION BY logid ORDER BY serviceid RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) serviceid,")
.append(OTHER_FIELD_NAMES)
.append(" FROM ( SELECT * FROM SERVICELOGS WHERE logid IN ('2314-4523-8897-0923-87') ORDER BY lastupdated DESC) ORDER BY lastupdated DESC")
}
Here's my DAO method for executing the query..
@Override
@Transactional(readOnly = true)
public Collection<MyModelObject> runQuery(final MyOptionalParams queryParams) throws IllegalArgumentException {
List<MyModelObject> result = getJpaTemplate().execute(new JpaCallback<List<MyModelObject>>() {
@Override
@SuppressWarnings("unchecked")
public List<MyModelObject> doInJpa(final EntityManager em) throws PersistenceException {
Query query = MyQueryFactory.createQueryWithParams(queryParams, em);
return query.getResultList();
}
});
return result;
}
In MyQueryFactory.createQueryWithParams(queryParams, em)
This is basically what's happening, there's some other stuff for populating params, but doesn't have any effect on the straight query with any params just hardcoded in the query string I'm running as a test..
Query query = em.createNativeQuery(BASIC_QUERY_STRING.toString(), MY_JOIN_MAPPING);
And the weird result...
Notice the serviceid column now, it's as if I just didn't use the LAST_VALUE and PARTITION. My goal with using the last_value and partition is to get a result set without the 0's for the serviceid. Unfortunately I don't have a lot of space for showing more table data, however there is also a lastupdated column that I'm ordering this by, it's a basic timestamp, the idea is to "bubble up" data from the various rows in this table and then select the top row with all the data from each row summarized into one result. I need my serviceid to fill in that column based on the partition with logid.
**************************************************
| ServiceID | LogID | ...
**************************************************
| 0 | 2314-4523-8897-0923-87 | ..
| 0 | 2314-4523-8897-0923-87 | ..
| 0 | 2314-4523-8897-0923-87 | ..
| 0 | 2314-4523-8897-0923-87 | ..
| 0 | 2314-4523-8897-0923-87 | ..
| 0 | 2314-4523-8897-0923-87 | ..
| 0 | 2314-4523-8897-0923-87 | ..
| 0 | 2314-4523-8897-0923-87 | ..
| 0 | 2314-4523-8897-0923-87 | ..
| 0 | 2314-4523-8897-0923-87 | ..
| 1234567 | 2314-4523-8897-0923-87 | ..
| 1234567 | 2314-4523-8897-0923-87 | ..
| 1234567 | 2314-4523-8897-0923-87 | ..
| 1234567 | 2314-4523-8897-0923-87 | ..
| 1234567 | 2314-4523-8897-0923-87 | ..
In Summary
I know there's prob a million different ways to analyze and sort data, my big question is though why do these two queries act different when it's the same query!? ..aside from the obvious with one being run in SQL Developer and the other being run from Hibernate. So why can't hibernate seem to handle the LAST_VALUE / PARTITION BY??? I'm not seeing any errors, I'm just not seeing the results I'd expect to see based on running the same query in SQL Developer..
Side Note
Just to see if hibernate was plain ignoring the last_value / partition by, I intentionally messed up the last_value and partition to see if it saw it at all...
Example: Instead of this..
LAST_VALUE(serviceid) OVER (PARTITION BY logid ORDER BY serviceid RANGE
BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) serviceid, ...
I did something like this..
LAST_VALUE(serviceid) OVER (PARTITION BY logid ORDER BY serviceid RANGE
BETWEEN *UNBOUND* PRECEDING AND *UNBOUND* FOLLOWING) serviceid, ...
And of course that does produce an error... FYI I only added the "*" to denote above where I edited, however I just removed the "ed" off of UNBOUNDED to see if it was even using it, it just seems like hibernate or something in between doesn't care to use it..
Additional Side Note
Here's a couple props from my persistance.xml.. I don't have a problem connecting or running any queries, I just don't understand why I'm not seeing the same results from hibernate as I am in SQL Developer..
<property name="hibernate.connection.url" value="jdbc:oracle:thin:@localhost:1521:xe"/>
<property name="hibernate.dialect" value="org.hibernate.dialect.OracleDialect"/>
<property name="hibernate.hbm2ddl.auto" value="validate"/>
