开发者

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:

  1. Do you even need to associate the tables? If your final goal is to calculate the difference between currentpackage and price as you said, seems like you could do that in a query.
  2. 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 add second_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.
  3. 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 generating id 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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜