0

I have an action in struts2 that will query the database for an object and then copy it with a few changes. Then, it needs to retrieve the new objectID from the copy and create a file called objectID.txt.

Here is relevant the code:

Action Class:

ObjectVO objectVOcopy = objectService.searchObjects(objectId);
//Set the ID to 0 so a new row is added, instead of the current one being updated
objectVOcopy.setObjectId(0);
Date today = new Date();
Timestamp currentTime = new Timestamp(today.getTime());
objectVOcopy.setTimeStamp(currentTime);
//Add copy to database
objectService.addObject(objectVOcopy);
//Get the copy object's ID from the database
int newObjectId = objectService.findObjectId(currentTime);
File inboxFile = new File(parentDirectory.getParent()+"\\folder1\\folder2\\"+newObjectId+".txt");

ObjectDAO

//Retrieve identifying ID of copy object from database
List<ObjectVO> object = getHibernateTemplate().find("from ObjectVO where timeStamp = ?", currentTime);
return object.get(0).getObjectId();

The problem is that more often than not, the ObjectDAO search method will not return anything. When debugging I've noticed that the Timestamp currentTime passed to it is usually about 1-2ms off the value in the database. I have worked around this bug changing the hibernate query to search for objects with a timestamp within 3ms of the one passed, but I'm not sure where this discrepancy is coming from. I'm not recalculating the currentTime; I'm using the same one to retrieve from the database as I am to write to the database. I'm also worried that when I deploy this to another server the discrepancy might be greater. Other than the objectID, this is the only unique identifier so I need to use it to get the copy object.

Does anyone know why this is occuring and is there a better work around than just searching through a range? I'm using Microsoft SQL Server 2008 R2 btw.

Thanks.

user1287523
  • 967
  • 3
  • 14
  • 31

2 Answers2

3

Precision in SQL Server's DATETIME data type does not precisely match what you can generate in other languages. SQL Server rounds to the nearest 0.003 - this is why you can say:

DECLARE @d DATETIME = '20120821 23:59:59.997';
SELECT @d;

Result:

2012-08-21 23:59:59.997

Then try:

DECLARE @d DATETIME = '20120821 23:59:59.999';
SELECT @d;

Result:

2012-08-22 00:00:00.000

Since you are using SQL Server 2008 R2, you should make sure to use the DATETIME2 data type instead of DATETIME.

That said, @RedFilter makes a good point - why are you relying on the time stamp when you can use the generated ID instead?

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • I don't have the ability to change the database table, but you're saying that if I search within the current range of 3ms I won't ever miss returning and object that should be returned? – user1287523 Aug 21 '12 at 16:12
  • I've found this http://stackoverflow.com/questions/4307635/undesired-rounding-of-datetime-in-sql-server and it looks like I'll need to look within 3ms – user1287523 Aug 21 '12 at 16:15
1

This feels wrong.

Other than the objectID, this is the only unique identifier

Databases have the concept of a unique identifier for a reason. You should really use that to retrieve an instance of your object.

You can use the get method on the Hibernate session and take advantage of the session and second level caches as well.

With your approach you execute a query everytime you retrieve your object.

Alex Barnes
  • 7,174
  • 1
  • 30
  • 50
  • The problem is I'm adding a new object and I have no knowledge of what the objectID will be. That's why I need to go into the database and find it – user1287523 Aug 21 '12 at 16:13
  • @user1287523 SQL Server can tell you what the generated objectID was using SCOPE_IDENTITY(). This is much more reliable than using a timestamp - while unlikely, isn't it *possible* that two users will insert a row at the same time (or within 4ms)? Surely your `DATETIME` column isn't unique? The problem is, I have no idea how to do this using Hibernate - can Hibernate call a stored procedure? If so then the solution is easy. – Aaron Bertrand Aug 21 '12 at 16:23
  • I'm having trouble following the supplied code so the exact use case is still unclear. I would wholeheartedly agree that timestamps are not a good candidate for a unique identifier. That's what a unique identifiers are for. – Alex Barnes Aug 21 '12 at 16:36
  • Yes, as you say it is very unlikely but theoretically possible given our business logic that there can be two entries within the timestamp range. I'll look into the SCOPE_IDENTITY(), there has to be some way to do it with hibernate – user1287523 Aug 21 '12 at 16:38
  • Yep, figured it out. When I call `objectService.addObject(objectVOcopy);` it used to call `getHibernateTemplate().saveOrUpdate(objectVOcopy);` I changed it to `objectVOcopy = getHibernateTemplate().merge(objectVOcopy)` which seems to be properly adding the row to the database and then returning the row back to the original object. Thanks everyone! – user1287523 Aug 21 '12 at 17:24