Dilemma while using hibernate
I'm using two tables for my current project(it will grow certainly).
First table has like loads of columns i.e.
id|name|surname|age|token |currentpackage|difference
1 |me | you | 22|xc2e2144124sd |100 |0
Second table :
id|mirror|token| connsize|price
81|server|xc2e2144124sd|100mbps |1000
These tables are full of data. There are some rows in the table1 that have same token value as the one in the table2.
I've googled for solution for quite alot, this is the best I found :
http://www.mkyong.com/hibernate/hibernate-one-to-one-relationship-example/ and http://www.vaannila.com/hibernate/hibernate-example/hibernate-mapping-one-to-one-1.html
But I don't think these apply to my case(or do they). In these examples the relation is key which is incremental, my token is just the way it is in the database.
My final goal is to be able to calculate the di开发者_JS百科fference between currentpackage and price
What would you recommend for this? I'm using java/hibernate/spring
Is there any other way to tell hibernate to link by something else other that primary key? some custom key like token in table 1? token is some kind relation between table one and table two. Its many(table1) to one(table2).
LATEST UPDATE
I'm trying to do it by using query to substract these two columns :
UPDATE packages tl
SET difference = (select (t2.price-t1.currentpackage)
from packages t1, mirrors t2
where t2.token= t1.token)
I got this error :
ERROR: more than one row returned by a subquery used as an expression
I must have tried many options but none worked
Tried this as well same result :
UPDATE packages X
SET difference = (select Y.price-X.currentpackage
FROM packages X
LEFT OUTER JOIN mirrors Y
ON Y.token = X.token)
This seems to be what I'm looking for but I'm not able to incorporate it in my query :
Subtract Values from Two Different Tables
You will not be able to map this relationship as many-to-one without making token
an identifier in the second table. That said, there are several things you could do:
- Do you even need to associate the tables? If your final goal is to calculate the difference between
currentpackage
andprice
as you said, seems like you could do that in a query. - If you do need to associate the tables, can you change your schema? I'm assuming
id
columns in both tables are primary keys. You can addsecond_id
column (with whatever appropriate name) to the first table that will be a foreign key to second table and map them as many-to-one. - If you can't add a new column, can you make
token
a primary key in the second table? Is it unique / not null? This is a last resort, really, as it will involve generatingid
value in the database and / or mapping custom insert in Hibernate to preserve existing table structure.
Edit (based on updated question):
This now has little to do with Hibernate, since the query you're trying to execute is (a) update and (b) SQL-based, but nevertheless... Assuming I understood you correctly and you're trying to update 1st table to contain difference between appropriate row in the second table and current row in the first table, you can do the following:
update packages
set difference = (select price
from mirrors m
where m.token = packages.token
) - currentpackage
Syntax may differ a little depending on your RDBMS; not all of them support correlated subqueries in update. Some support multiple table updates (like MySQL or Postgres), you can then write the query as:
update packages, mirrors
set difference = mirrors.price - currentpackage
where packages.token = mirrors.token
It is tricky to make Hibernate to join tables by something other than primary key of one of the tables. You can some times get around this by doing a join the way Hibernate likes and putting your "logical join" condition into the were clause.
And if there's no join condition that Hibernate would like and suites your logic, you would have to do a Cartesian product.
精彩评论