Win a copy of Modern JavaScript for the Impatient this week in the Server-Side JavaScript and NodeJS forum!

Robert Fry

Greenhorn
+ Follow
since Aug 22, 2008
Cows and Likes
Cows
Total received
0
In last 30 days
0
Total given
0
Likes
Total received
0
Received in last 30 days
0
Total given
0
Given in last 30 days
0
Forums and Threads
Scavenger Hunt
expand Ranch Hand Scavenger Hunt
expand Greenhorn Scavenger Hunt

Recent posts by Robert Fry

Paul Sturrock wrote:Its usually the responsibility of the driver to support SSL if the database supports it, so you shouldn't have to do any socket programming yourself. The driver will also hide the mechanics of the connection from you (well, it is an deliberate layer of abstraction after all ) so you can't get at it via normal JDBC code. I don't know if there are drivers that supprt SSL for MySQL, but the driver it does supply is open source so you could possible extend it to do the SSL stuff?



Well, there is apparently SSL support in MySQL, but it looks a bit ropey judging by the people posting in forums trying vainly to get it to work.

For interests sake there are some instructions here :-

http://dev.mysql.com/doc/refman/5.0/en/secure-create-certs.html
http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-using-ssl.html

I'm going to try and get a prototype going next...

Typical, I post, and then I find something.

Apparently the MySQL drivers do support SSL themselves so I can fiddle with Properties objects to get it to work.

However there seem to be problems with it judging by unfortunates tearing their hair out on the net over this functionality, so maybe I'll be back...
I'm not quite sure where to put this, sockets & networking or here? I figured here.

Is it possible to obtain a JDBC Connection object for SQL operations via a Socket object? I know there is a socket object(s) buried inside that Connection, hence all the socket exceptions you get if it fails to connect. But the DriverManager creates the socket internally. The problem is that I want to use an SSLSocket so the connection to the remote DB is encrypted, using a specific protocol no less. I did read some stuff on encrypted SQL connections - I noticed Oracle allows you to specify encryption as part of the vendor provided stuff you can access via DriverManager properties - but that doesn't appear to be an option with MySQL.

It doesn't seem to be an easy task to do this - far as I can tell it would involve extending some JDBC classes and getting right into the guts of hairy code. But maybe I'm wrong.

Am I?

The alternative is to just use the SSL connection to get to the server and then the server can be rigged up to connect to the database on localhost the usual way and pass serialised objects back and forth I suppose, but that seems immensely ugly to me.

Unfortunately the internet fails because all I get is talk about SocketExceptions when hunting for clues.
OK, I fixed it! I found the solution to be quite odd.

Basically the problem was in the saving. Session.saveOrUpdate() was being called on the floor, and that was causing the error.

What I actually needed to do was first called Session.merge() and then after that call Session.saveOrUpdate(). I think this is very odd but that is definitely what is needed, minus merge gives you the error, and minus saveorupdate means it doesn't persist, despite what merge implies.

I was hinted at this route by looking in the good book, Java Persistence with Hibernate, which implies that only merge or update are acceptable ways to reattach detached objects. As saveOrUpdate was neither of these (I thought it would count as update hence why I used it here) I investigated further.
I was doing some other stuff but am now back my good friend, Hibernate.

Curiouser and curiouser, this is a bit of test code



And this spits out

Session contains deleted object? false
List of saved floor Tx objects. []
Is this also in the cache? false
Found two representations of same collection: com.preciselyso.planview.datamodel.Floor.txInFloor (this one being dropped by the other thread)

Gah. It isn't in it, yet it complains it is. What is it doing..?
Oh nooo this dreaded exception. I've looked on the internet for help on this one and found only great scratching of heads. Including on here in the past, but I shall try anyway!

First off, the relevant annotations.



(Odd with the cascade annotations but that worked before, one of them is presumably unncessary).



The problem - I'm trying to code up an undo command, to undo deleting a floor.
First off the deletion bit works fine. Its just a Session.delete call on a floor object.

The undo code works by keeping a reference to the (detached) floor object. Should the user want to undo the deletion, essentially the code just calls Session.saveOrUpdate on the detached, saved object, which should reattach the object to the persistence context. This works with other persistent objects so I presume this is indeed the way to go.

However when I undo a floor object deletion, I get the exception, "Found two representations of the same collection", the collection in question being "txes" in the code above.
It seems to be a session caching issue because the SQL back end doesn't have any entries in it (the deletion itself works fine, as mentioned), its just that the persistence context refuses to 'let go' of the collection in the floor. Even calling Session.clear() does not resolve the problem. Even more oddly the collection doesn't actually have any objects in it necessarily, so its empty, yet the error still occurs.

I'm presuming that this is a mapping problem (again - ) with the cascading options.

Looking at what it produces...

Hibernate: insert into FLOOR (FLOOR_ID, BUILDING_ID, FLOOR_TREEINDEX, FLOOR_IMAGE_SOURCE, FLOOR_NAME, FLOOR_ORIGIN_X, FLOOR_ORIGIN_Y, FLOOR_SCALE_MULTIPLIER) values (null, ?, ?, ?, ?, ?, ?, ?)
Hibernate: call identity()
Hibernate: delete from FLOOR where FLOOR_ID=?
Found two representations of same collection: com.preciselyso.planview.datamodel.Floor.txInFloor

The undo code itself isnt very interesting - essentially all that goes on is a copy of the deleted object has saveOrUpdate called on it. The txes array in the detached object is empty, though not null, if you follow my meaning...
It's me again with my transmitters and sensors, lucky y'all - back from dealing with SSL (much easier!).

In general Hibernate seems to be working, but... not 100% yet. I have some problems with transactions now, it looks like.

A bit of backdrop as to the code - as this is a Swing app the Hibernate updates are going on in a thread of their own so they don't lag the GUI. Hibernate tasks (save_or_update and delete) are queued onto a ConcurrentLinkedQueue and that thread runs in a loop, sucking tasks off that queue and processing them as quickly as possible.

The transaction code that seems to be iffy is this :-



After this chunk of code a Session.saveOrUpdate() method is called on 'loc', within the scope of a single Transaction.

Payload is a Transmitter object with the following mapping :-



Sometimes there is already a TransmitterLocation associated with payload, sometimes not. Normally this works fine, I can see it the thread chuntering away in the background, there are no errors, everything persists, it all looks good. However, if I spam a whole bunch of operations, sometimes (not reliably but often) an exception is thrown at Transaction.commit().

org.hibernate.AssertionFailure: null id in com.preciselyso.planview.datamodel.TransmitterLocation entry (don't flush the Session after an exception occurs)

Now... you have to bear in mind that the actual update being run by Hibernate is happening 15-20 seconds down the line because its so amazingly slow. As the object being queued up is still live in the application, I presume that these errors happen because by the time Hibernate has worked through the queue to update the object, its state may have changed to the point that the update isn't valid anymore. If I order a TransmitterLocation to be saved, but in the 15 seconds that elapses between that and it actually being persisted I change my mind and delete it, I think that gives me the above error.

So this threading model apparently is a poor choice! My first thought was to populate the queue with cloned objects rather than the live ones, but deep cloning this stuff would be a nightmare due to all the cross linking. I'm curious what strategies others have used to deal with this issue? I also wonder if a shallow clone would be enough - the SQL which failed before the above exception is...

Hibernate: insert into TX_PLANPLACE (TX_PLANPLACE_ID, FLOOR_ID, TX_PLANPLACE_ORIGINAL_SIZE, TX_ID, TX_PLANPLACE_X, TX_PLANPLACE_Y) values (null, ?, ?, ?, ?, ?)
Hibernate: call identity()

ie its all on TX_PLANPLACE so all within a single object, so a shallow clone might do, no? Problem is these exceptions are unpredictable runtime exceptions caused by me flogging the queue, so 'I tested it and it worked fine for me' is not the same as 'this is the way to do it'. Hence the need for advice.
Alright, defeated!

The Easy Way.

Aha, something has happened. I fiddled some more (more flush()) and now I see :-

Old value : 12.324219
Old value : 66.45703
Old value : 18.1
Old value : 42.0
Old value : 21.2
Old value : 51.0
Old value : 21.152344
Old value : 51.36328
Old value : 0.0
DB read value : 12.324219
DB read value : 66.359375
DB read value : 18.1
DB read value : 42.0
DB read value : 21.2
DB read value : 51.0
DB read value : 21.152344
DB read value : 51.36328
DB read value : 0.0
Merged value 12.324219
Merged value 66.45703
Merged value 18.1
Merged value 42.0
Merged value 21.2
Merged value 51.0
Merged value 21.152344
Merged value 51.36328
Merged value 0.0

So it's read in the different value finally. Though merge() clearly isn't the method I want to use to reattach...

Paul Sturrock wrote:


I've actually followed some advice and took most of the transactions out.


Transactions for reads are unnecessary. Transactions for data manipulation are mandatory. The only (sort of) exception to this is unless you are running in auto commit mode, which be default you are not. Using auto commit you are using transaction implicitly.

Generation (of any kind) in Hibernate only happens when you perform an insert or update. If you are not running in a transaction, the generation stuff will never commit.



Right, OK. This is what I was running. Two transactions just for my own peace of mind.



The things I learned from this are

a) the evict() command really does work, the sensors are most definitely detached. (discovered with some attempts to save those objects without reattaching them)
b) the cache seems to keep its hands on the sensor objects despite the evict. The intermediate list, newsensorlist, has the same values as the original sensorlist, so it's already out of kilter with the database at that point - so the merging bit later on is irrelevant.
c) Yet despite this, show_sql being true does reveal that there is indeed a single SQL SELECT query being sent to the database.

Not that the numbers mean much but this is the output this generates (the numbers in the SQL database have indeed changed over the course of this refresh, so if it was working at least some of the 'read from DB' values should be different).

Old value : 12.324219
Old value : 66.11719
Old value : 18.1
Old value : 42.0
Old value : 21.2
Old value : 51.0
Old value : 21.042969
Old value : 51.36328
Old value : 0.0
Hibernate: select this_.Sensor_ID as Sensor1_4_6_, this_.Sensor_AlmHi as Sensor2_4_6_, this_.Sensor_AlmLo as Sensor3_4_6_, this_.Sensor_PncHi as Sensor4_4_6_, this_.Sensor_PncLo as Sensor5_4_6_, this_.TX_ID as TX7_4_6_, this_.SType_ID as SType8_4_6_, this_.Sensor_LastValue as Sensor6_4_6_, transmitte2_.TX_ID as TX1_5_0_, transmitte2_.TX_STATUS1 as TX2_5_0_, transmitte2_.TX_LASTRECD as TX3_5_0_, transmitte2_.TX_LATEAFTER as TX4_5_0_, transmitte2_.TX_LOCATION as TX5_5_0_, transmitte2_.RX_ID as RX7_5_0_, transmitte2_.TX_ADDRESS as TX6_5_0_, transmitte2_.TXTYPE_ID as TXTYPE8_5_0_, receiver3_.RX_ID as RX1_6_1_, receiver3_.RX_NAME as RX2_6_1_, transmitte4_.TXTYPE_ID as TXTYPE1_0_2_, transmitte4_.TXTYPE_NAME as TXTYPE2_0_2_, transmitte5_.TX_PLANPLACE_ID as TX1_7_3_, transmitte5_.FLOOR_ID as FLOOR5_7_3_, transmitte5_.TX_PLANPLACE_ORIGINAL_SIZE as TX2_7_3_, transmitte5_.TX_ID as TX6_7_3_, transmitte5_.TX_PLANPLACE_X as TX3_7_3_, transmitte5_.TX_PLANPLACE_Y as TX4_7_3_, floor6_.FLOOR_ID as FLOOR1_3_4_, floor6_.BUILDING_ID as BUILDING2_3_4_, floor6_.FLOOR_TREEINDEX as FLOOR3_3_4_, floor6_.FLOOR_IMAGE_SOURCE as FLOOR4_3_4_, floor6_.FLOOR_NAME as FLOOR5_3_4_, floor6_.FLOOR_ORIGIN_X as FLOOR6_3_4_, floor6_.FLOOR_ORIGIN_Y as FLOOR7_3_4_, floor6_.FLOOR_SCALE_MULTIPLIER as FLOOR8_3_4_, sensortype7_.STYPE_ID as STYPE1_1_5_, sensortype7_.SType_EU as SType2_1_5_, sensortype7_.SType_Format as SType3_1_5_, sensortype7_.SType_MaxValue as SType4_1_5_, sensortype7_.SType_MinValue as SType5_1_5_, sensortype7_.SType_Type as SType6_1_5_ from Sensor this_ left outer join TX transmitte2_ on this_.TX_ID=transmitte2_.TX_ID left outer join RX receiver3_ on transmitte2_.RX_ID=receiver3_.RX_ID left outer join TX_TYPE transmitte4_ on transmitte2_.TXTYPE_ID=transmitte4_.TXTYPE_ID left outer join TX_PLANPLACE transmitte5_ on transmitte2_.TX_ID=transmitte5_.TX_ID left outer join FLOOR floor6_ on transmitte5_.FLOOR_ID=floor6_.FLOOR_ID left outer join Sensor_Type sensortype7_ on this_.SType_ID=sensortype7_.STYPE_ID
DB read value : 12.324219
DB read value : 66.11719
DB read value : 18.1
DB read value : 42.0
DB read value : 21.2
DB read value : 51.0
DB read value : 21.042969
DB read value : 51.36328
DB read value : 0.0
Merged value 12.324219
Merged value 66.11719
Merged value 18.1
Merged value 42.0
Merged value 21.2
Merged value 51.0
Merged value 21.042969
Merged value 51.36328
Merged value 0.0

Paul Sturrock wrote:
Well to be fair to Hibernate, the only annotations or mappings that are ignored are those that could contradict or undermine the fetch strategy defined in your HQL. Either the annotations had to be ignored or part of the HQL had to be ignored, you can't have both honoured. I'll not defend how well documented this is however.



OK.


In your previous post you had wrapped your read in a transaction. Are you flushing the session etc. in that same transaction?



I've actually followed some advice and took most of the transactions out.

The last bunch of attempts were all variants on something like this



with a few changes. s.refresh, s.persist, s.merge, I've tried a lot of things.

But thats essentially the 'detach, reload all of them in one query, try to reattach' idea I was trying.
Confidence in these annotations have waned a bit as it turns out that apparently at least some of them aren't actually paid attention by Hibernate anyway - so as it doesn't appear to working as the book says, I'll ask here to see if this is another ignored @annotation or if I'm actually doing it wrong.

I have the following @Generated attribute on a class :-



The value in there is provided by the SQL database not the app which is using Hibernate. Though it isn't something like a primary key, and it isn't set by the SQL DB itself but by another application working elsewhere, it is essentially a DB generated value which every so often needs to be loaded into memory - without an inordinate number of database hits, as there could be a lot of these.

Every minute that value needs to be reread from the SQL and loaded into the objects in memory (held in a List<Sensor>). And here is where it all falls down.

1) Session.refresh() creates one database hit per sensor - that takes far too long, even given that this is threaded, so can't be used - besides, its wild inefficiency for something so easy is irksome.

2) I tried detaching all the sensor objects from the persistence context, flushing everything, reading a brand new (and separate) List of sensors so they are back in the persistence context, and then reattaching the old sensor references with persist. This doesn't seem to work as despite the @Generated tag Hibernate seems to want to always use the values in memory (which are now stale) over the updated generated values in the database despite the generated tag (and despite that they are set read only!).



I'm pondering just setting the sensor value to a transient field and just doing it all myself without Hibernate involved at all - just do all the refreshing stuff personally. But that does seem a bit silly given thats why Hibernate is supposed to be doing for me.

I've not seen very much in the documentation or on the web regarding getting data attributes from database to app rather than from app to database. Is session.refresh() really the only option Hibernate presents here?

Paul Sturrock wrote:Ah. When you said your were using fetch stuff, I assuming you were using it in your HQL. One annoying feature of Hibernate is if you use an HQL query the majority of your fetching annotations/mappings will be ignored. Try using a Criteria query or the "eager fetch join" stuff in your HQL.



Well, thats a revelation!

I'm using something like this now :-



That seems to work a lot better. It's down to 7 queries on startup now - still takes a while but at least it doesn't hang for a minute anymore.

I shall play some more and see if I can reduce it still further.

Thanks for your help!

Paul Sturrock wrote:OK. Well what should be happening is behind the scenes Hibernate will "Hydrate" the object, where by your mapped type will be populated based on the results on whatever JDBC result set you are using. Given your initial HQL query:

will translate into a SQL query like:

you can be sure (give or take the implementation specifics of whatever database/driver you are using) the state of your ResultSet should not need Hibernate to go back to the database. My guess is the extra selects you are seeing are the result of your association mapping. Can you post your annotated POJOs?

Also, there will be an overhead because you wrap everything in a transaction. I'd ditch that part an only use transactions when updating the data.



Thanks for the reply.

Here are the mapped objects. I've cut everything except the annotated bits as there is a lot of code on them which is nothing to do with Hibernate. Bear in mind that a lot of the fetch types, lazy loading being set or not, @Generated annotations, batch sizes and that sort of thing are only there as a result of me hacking at it today so don't really mean much.









(TransmitterLocation is a class as originally a transmitter could be in multiple places - that requirement is gone now but may resurface, so that one-to-one relation there might change.)







The code I'm using to initialise and refresh things is currently all just a variant on



ie "from X".

When you actually fire it up and it starts loading, with show_sql enabled you see stuff like this :-

Hibernate: select transmitte0_.TX_ID as TX1_5_, transmitte0_.TX_STATUS1 as TX2_5_, transmitte0_.TX_LASTRECD as TX3_5_, transmitte0_.TX_LATEAFTER as TX4_5_, transmitte0_.TX_LOCATION as TX5_5_, transmitte0_.RX_ID as RX7_5_, transmitte0_.TX_ADDRESS as TX6_5_, transmitte0_.TXTYPE_ID as TXTYPE8_5_ from TX transmitte0_
Hibernate: select receiver0_.RX_ID as RX1_6_0_, receiver0_.RX_NAME as RX2_6_0_ from RX receiver0_ where receiver0_.RX_ID=?
Hibernate: select transmitte0_.TXTYPE_ID as TXTYPE1_0_0_, transmitte0_.TXTYPE_NAME as TXTYPE2_0_0_ from TX_TYPE transmitte0_ where transmitte0_.TXTYPE_ID in (?, ?, ?, ?, ?)
Hibernate: select transmitte0_.TX_PLANPLACE_ID as TX1_7_6_, transmitte0_.FLOOR_ID as FLOOR5_7_6_, transmitte0_.TX_PLANPLACE_ORIGINAL_SIZE as TX2_7_6_, transmitte0_.TX_ID as TX6_7_6_, transmitte0_.TX_PLANPLACE_X as TX3_7_6_, transmitte0_.TX_PLANPLACE_Y as TX4_7_6_, floor1_.FLOOR_ID as FLOOR1_3_0_, floor1_.BUILDING_ID as BUILDING2_3_0_, floor1_.FLOOR_TREEINDEX as FLOOR3_3_0_, floor1_.FLOOR_IMAGE_SOURCE as FLOOR4_3_0_, floor1_.FLOOR_NAME as FLOOR5_3_0_, floor1_.FLOOR_ORIGIN_X as FLOOR6_3_0_, floor1_.FLOOR_ORIGIN_Y as FLOOR7_3_0_, floor1_.FLOOR_SCALE_MULTIPLIER as FLOOR8_3_0_, transmitte2_.TX_ID as TX1_5_1_, transmitte2_.TX_STATUS1 as TX2_5_1_, transmitte2_.TX_LASTRECD as TX3_5_1_, transmitte2_.TX_LATEAFTER as TX4_5_1_, transmitte2_.TX_LOCATION as TX5_5_1_, transmitte2_.RX_ID as RX7_5_1_, transmitte2_.TX_ADDRESS as TX6_5_1_, transmitte2_.TXTYPE_ID as TXTYPE8_5_1_, receiver3_.RX_ID as RX1_6_2_, receiver3_.RX_NAME as RX2_6_2_, sensorlist4_.TX_ID as TX7_8_, sensorlist4_.Sensor_ID as Sensor1_8_, sensorlist4_.Sensor_ID as Sensor1_4_3_, sensorlist4_.Sensor_AlmHi as Sensor2_4_3_, sensorlist4_.Sensor_AlmLo as Sensor3_4_3_, sensorlist4_.Sensor_PncHi as Sensor4_4_3_, sensorlist4_.Sensor_PncLo as Sensor5_4_3_, sensorlist4_.TX_ID as TX7_4_3_, sensorlist4_.SType_ID as SType8_4_3_, sensorlist4_.Sensor_LastValue as Sensor6_4_3_, sensortype5_.STYPE_ID as STYPE1_1_4_, sensortype5_.SType_EU as SType2_1_4_, sensortype5_.SType_Format as SType3_1_4_, sensortype5_.SType_MaxValue as SType4_1_4_, sensortype5_.SType_MinValue as SType5_1_4_, sensortype5_.SType_Type as SType6_1_4_, transmitte6_.TXTYPE_ID as TXTYPE1_0_5_, transmitte6_.TXTYPE_NAME as TXTYPE2_0_5_ from TX_PLANPLACE transmitte0_ inner join FLOOR floor1_ on transmitte0_.FLOOR_ID=floor1_.FLOOR_ID inner join TX transmitte2_ on transmitte0_.TX_ID=transmitte2_.TX_ID left outer join RX receiver3_ on transmitte2_.RX_ID=receiver3_.RX_ID left outer join Sensor sensorlist4_ on transmitte2_.TX_ID=sensorlist4_.TX_ID left outer join Sensor_Type sensortype5_ on sensorlist4_.SType_ID=sensortype5_.STYPE_ID left outer join TX_TYPE transmitte6_ on transmitte2_.TXTYPE_ID=transmitte6_.TXTYPE_ID where transmitte0_.TX_ID=?

etc.etc.etc.


There are a lot of joins in there, as I've been playing with FetchType. It does make it a bit faster doing it that way, but its still far too slow.

SensorType and maybe TransmitterLocation could be (should be?) @Embeddable I imagine, not sure if that is involved with this problem though.

Paul Sturrock wrote:I'm not sure I follow your design. Are you intending to update every Sensor and Transmitter object whenever your user uses your application?

From the JavaDocs forSession.refresh() say:


It is inadvisable to use this to implement long-running sessions that span many business tasks. This method is, however, useful in certain special circumstances. For example

* where a database trigger alters the object state upon insert or update
* after executing direct SQL (eg. a mass update) in the same session
* after inserting a Blob or Clob



Are you doing any of the above? You say your application is single user, so I'm guessing no?



No, not doing any of those.

First off, some of the persistent objects are actually going into a JTree, so the first thing that happens is they all have to be loaded so the tree can be built up. The JTree building process means the data in the objects needs to be in there (so transmitters can go in the right place and have the right LED colour depending on their status), which means step 1 is loading pretty much everything in the SQL DB into memory so that JTree can be built . ATM I'm using queries like the above to do this - loading up all Transmitters or whatever into a list and then building up the tree - but they always seem to be painfully slow.

The other big problem re. slowness is that the data in the SQL database is shared and is updated by another program - this app I'm working on is concerned with displaying that data.

So the data in memory periodically needs refreshing from the SQL database. As it isn't particularly time critical stuff, a refresher thread runs every sixty seconds at the moment. Still, that means every sixty seconds it has to poll every transmitter and every sensor in the database and update each object with the new values, if any - there will likely be of the order of a hundred or so objects of type Transmitter or Sensor which need to have their values updated. Also the connection is fairly slow, at least the one used for testing is (deliberate worst case scenario) , so having a hundred single statement updates every minute really isn't good, even when it's all threaded - the connection is just jammed with constant SQL as it works through the list.