The complex relationship between tables in NHibernate

I am writing Fluent NHibernate for an outdated Oracle database. The challenge is that tables have composite primary keys. If I were free, I would reconsider the relationship and automatically generate primary keys, but other applications should write to and read from one database, so I can’t do this.

These are the two tables that I will focus on:

alt text

Data examples

Trips table: 1, 10:00, 11:00 ... 1, 12:00, 15:00 ... 1, 16:00, 19:00 ... 2, 12:00, 13:00 ... 3, 9:00, 18:00 ... Faults table: 1, 13:00 ... 1, 23:00 ... 2, 12:30 ... 

In this case, the vehicle 1 made three trips and has two errors. The first error occurred during the second trip, and the second error occurred when the car was resting. Vehicle 2 had one trip during which an error occurred.

Limitations

Rides of the same car never intersect. Thus, tables have an optional one-to-many relationship , because each error occurs either during the trip or not. If I wanted to join them in SQL, I would write:

 select ... from Faults left outer join Trips on Faults.VehicleId = Trips.VehicleId and Faults.FaultTime between Trips.TripStartTime and Trips.TripEndTime 

and then I would get a data set where each error appears exactly once (one-to-many, as I said).

Please note that there is no "Vehicles" table, and I do not need it. But I created a view that contains all VehicleIds from both tables, so I can use it as a connection table.

What am I really looking for?

The tables are huge because they span years of data, and each time I need only a few hours.

So, I need a comparison and criteria that will run something like the following SQL under:

 select ... from Faults left outer join Trips on Faults.VehicleId = Trips.VehicleId and Faults.FaultTime between Trips.TripStartTime and Trips.TripEndTime where Faults.FaultTime between :p0 and :p1 

Do you have any ideas on how to achieve it?

Note 1: At this time, the application should not be written to the database, so saving is not required, although if the mapping supports persistence, it may help at some point in the future.

Note 2: I know this is difficult, so if you give me a great answer, you will be rewarded properly :)

Thank you for reading this long question, and now I hope only for the best :)

+4
source share
5 answers

Current recommendation

Given the additional information in the comments, I now suggest trying the following class mappings instead of using any special SQL solutions mentioned later in this answer:

 <class name="Fault" table="Faults"> <composite-id> <key-property name="VehicleId" /> <key-property name="FaultTime" /> <key-property name="FaultType" /> <generator class="assigned" /> </id> <many-to-one name="Trip" class="Trip"> <!-- Composite Key of Trip is calculated on the fly --> <formula>VehicleId</formula> <formula> ( SELECT TripStartTime FROM Trips t WHERE VehicleId = t.VehicleId AND FaultTime BETWEEN t.TripStartTime AND t.TripEndTime ) </formula> </many-to-one> ... </class> <class name="Trip" table="Trips"> <composite-id> <key-property name="VehicleId" /> <key-property name="TripStartTime" /> </composite-id> ... </class> 

Using this mapping, you can load and query Fault objects as you like.

Deprecated Suggestions

Initially, I was considering a custom SQL query (named). You can enter the following query into the mapping file to load the Fault objects for this vehicle:

 <sql-query name="LoadFaultsAndTrips" xml:space="preserve"> <return class="Fault" alias="f"/> <return-join alias="t" property="f.Trip"/> SELECT {f.*} , {t.*} FROM Faults f LEFT OUTER JOIN Trips t ON f.VehicleId = t.VehicleId AND f.FaultTime BETWEEN t.TripStartTime AND t.TripEndTime WHERE f.VehicleId = ? </sql-query> 

If you need to load the Faults collection onto the Vehicle object without explicit requests, you can try the following mapping construct in XML:

 <class name="Vehicle"> <id name="VehicleId" type="..."> <generator class="..." /> </id> ... <bag name="Faults" table="Faults" inverse="true"> <key column="VehicleId" /> <loader query-ref="VehicleFaultsLoader" /> </bag> ... </class> <sql-query name="VehicleFaultsLoader" xml:space="preserve"> <load-collection role="Vehicle.Faults" alias="f" /> <return-join alias="t" property="f.Trip"/> SELECT {f.*} , {t.*} FROM Faults f LEFT OUTER JOIN Trips t ON f.VehicleId = t.VehicleId AND f.FaultTime BETWEEN t.TripStartTime AND t.TripEndTime WHERE f.VehicleId = ? </sql-query> 

The key point here is to define a custom collection loader for the Faults collection of the Vehicle class and define a custom SQL query that receives the primary key of the Vehicle as parameter. I haven't used fluent NHibernate yet, but I'm afraid I can't help you with this part of the question.

Cheers, Gerka.

+3
source

You, for example, sql, are syntactically the same as

 select ... from Faults left join Trips on Faults.VehicleId = Trips.VehicleId where Faults.VehicleId is null or (Faults.FaultTime between Trips.TripStartTime and Trips.TripEndTime) 

keeping in mind you can create a regular map such as (smooth)

 HasMany< Trip >( fault => fault.Trips ) .KeyColumn( "VehicleId" ) .Table( "Trips" ) .LazyLoad( ) .Cascade.Delete( ) .AsSet() 

and then, using any form of query that suits you, be it hql, icriteria, icriteriaover or linq, do your standard query with the where clause as above.

in linq, which will be:

 IList<Trip> results = ( fault in Session.Query< Entities.Faults > join trip in Session.Query< Entities.Trips > on fault.VehicleId equals trip.VehicleId into trip where fault.FaultTime > startTime && fault.FaultTime < endTime && // Here is the rest of the join criteria expressed as a where criteria ( trip == null || ( fault.FaultTime > trip.TripStartTime && fault.FaultTime < trip.TripEndTime ) ) select fault ).ToList(); 

If necessary, I can give an example in ICriteria or IQueryOver.

Of course, this is only work because of the example you presented, it can be rewritten as a where clause if there is a result. If you need the real world, then sql is more complicated, you need to think about whether the desired sql can be rewritten during archiving of the same result.

+2
source

I am new to NH and know only NH rudiments, so when I got into this situation, I wrote a stored procedure and then called it via NH. In the end, I will find an all-NH solution, and then reorganize the code and remove the need for a saved proc.

Another approach that might work is to simply write the HQL you need.

+1
source

I will make a suggestion if you are using NHibernate 3, try Linq to NH. Using Linq, you can specify manual / arbitrary relationships for one-time execution or use channels if you think they will be reused (or if you want to make a left / right connection, you need to specify it if it is an isser member, you do not need indicate a connection in general, it is all inferred from comparisons) and is the logic of business logic, and not the logic of perseverance.

As a quick example, this would be something like this:

 var result = ( fault in Session.Query< Entities.Faults > join trip in Session.Query< Entities.Trips > on fault.VehicleId equals trip.VehicleId into trip where fault.FaultTime > startTime && fault.FaultTime < endTime && fault.FaultTime > trip.TripStartTime && fault.FaultTime < trip.TripEndTime select fault ).ToList(); 

I wrote it by hand so that it is not perfect, but close enough. This should do exactly what you need and allow you to modify it as you wish, without changing your mappings.

+1
source

If you already know what query you want to execute the database, why not just execute the query directly using your own DAO class? Why worry about NHibernate abstraction, if that only gets in the way?

0
source

Source: https://habr.com/ru/post/1334415/


All Articles