StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>There are basically three approaches that can be used, some of which have already been discussed:</p> <ol> <li>Use an HQL query or <code>CreateCriteria</code>/<code>ICriteria</code> query; <em>downsides:</em> it is not part of the entities/DAL; <em>upsides:</em> it is flexible;</li> <li>Use a property mapping with a <code>formula</code>; <em>downsides:</em> it is not always feasible or possible, performance can degrade if not careful; <em>upsides:</em> it the calculation an integral part of your entities;</li> <li>Create a separate XML HBM mapping file and map to a separate (to be created) entity; <em>downsides:</em> it is not part of the base entities; <em>upsides:</em> you only call the SP when needed, full control of mapping / extra properties / extensions, can use partial or abstract classes to combine with existing entities.</li> </ol> <p>I'll briefly show an example of option 2 and 3 here, I believe option 1 has been sufficiently covered by others earlier in this thread.</p> <p>Option two, as in this example, is particularly useful when the query can be created as a subquery of a select statement and when all needed parameters are available in the mapped table. It also helps if the table is not mutable and/or is cached as read-only, depending on how heavy your stored procedure is.</p> <pre><code><class name="..." table="..." lazy="true" mutable="false> <cache usage="read-only" /> <id name="Id" column="id" type="int"> <generator class="native" /> </id> <property name="Latitude" column="Latitude" type="double" not-null="true" /> <property name="Longitude" column="Longitude" type="double" not-null="true" /> <property name="PrijsInstelling" formula="(dbo.DistanceBetween(@lat1, @lat2, Latitude, Longitude))" type="double" /> ... etc </class> </code></pre> <p>If the above is not possible due to restrictions in the mappings, problems with caching or if your current cache settings retrieve one by one instead of by bigger amounts and you cannot change that, you should consider an alternate approach, for instance a separate mapping of the whole query with parameters. This is quite close to the CreateSqlQuery approach above, but forces the result set to be of a certain type (and you can set each property declaratively):</p> <pre><code><sql-query flush-mode="never" name="select_Distances"> <return class="ResultSetEntityClassHere,Your.Namespace" alias="items" lock-mode="read" > <return-property name="Id" column="items_Id" /> <return-property name="Latitude" column="items_Latitude" /> <return-property name="Longitude" column="items_Longitude" /> <return-property name="Distance" column="items_Distance" /> </return> SELECT Items.*, dbo.DistanceBetween(@lat1, @lat2, Latitude, Longitude) AS Distance FROM Items WHERE UserId = :userId </sql-query> </code></pre> <p>You can call this query as follows:</p> <pre><code>List<ResultSetEntityClassHere> distanceList = yourNHibernateSession.GetNamedQuery("select_Distances") .SetInt32("userId", currentUserId) /* any params go this way */ .SetCacheable(true) /* it's usually good to cache */ .List<ResultSetEntityClassHere>(); /* must match the class of sql-query HBM */ </code></pre> <p>Depending on your needs, you can choose an approach. I personally use the following rule of thumb to decide what approach to use:</p> <ul> <li>Is the calculation light or can it be cached? <em>Use <code>formula</code> approach;</em></li> <li>Are parameters needed to be sent to the SP/SQL? <em>Use <code>sql-query</code> + mapping approach;</em></li> <li>Is the structure of the query (very) variable? <em>Use <code>ICriteria</code> or HQL approach through code.</em></li> </ul> <p>About the ordering of the data: when you choose the "formula" or the "sql-query mapping" approach you'll have to do the ordering when you retrieve the data. This is not different then with retrieving data through your current mappings.</p> <p><strong>Update:</strong> terrible edit-mistake corrected in the sql-query XML.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload