Hibernate query to return results of many-to-many DB relationship without the relationship being mapped

I am new to Hibernate when it comes to writing mapping files and understanding best practices for mapping entities that are not necessarily intended to be POJOs.

I have a many-to-many relationship defined in my database between role and message. The idea being that a role (think user group) can be assigned messages in this application and a message can be assigned to many roles. This is for providing a means of communicating messages to groups of users in an application.

I do not necessarily want my object model to directly reflect this many-to-many relationship. For example, I do not necessarily want my Role object to know anything about Messages and I don't really want Message to have a collection of Roles on it (though I could possibly be swayed on this if it seems this would be best). My thinking was that in this case I would prefer to have a service that would take

• for loading messages: a Set of Roles and return the applicable Messages
• for saving a message: a Set or Roles and the Message to which it applies

I currently have both Message and Role mapped, but I do not have the linking table message_rolemapped.

So, I have a couple of questions about how to query for Messages for Roles:

• Should I still create a mapping for the linking table? I believe that I've seen that I don't have to declare a name attribute on the class element in th hibernate mapping file; declaring the mapping without having to map it to a POJO (rather giving it an entity-name). I started to go down this path, but was unsure how to map the table after that because the table really just contains a composite key with each column in the primary key being the foreign key into th开发者_开发技巧e linked tables.

• So, since I was unsure about that, I actually am currently using a named query that looks like this:

  <sql-query name="loadRoleMessages">

  <![CDATA[
  select m.*
  from message m
  right join message_role mr on m.message_id=mr.message_id
  where
  m.effective_date <= :date
  and m.expiration_date >= :date
  and mr.role_id in (:roleIds)
  ]]>
  

  </sql-query>

(sorry, couldn't get the formatting to include the sql-query tags for some reason)

This is actually working, but I am getting a List<Object[]> returned from my query instead of a List<Message>. Where the Object[] represents the columns being returned. I understand this is because the query does not reference the Message entity and thus Hibernate doesn't know to create Message objects based on the query results. And from what I've found it doesn't appear to be possible to join an Entity to an non-entity table in the HQL.

So, I'm just looking for some input on (1) is there a preferred/best-practice for mapping two entities (Role/Message) that are related (many-to-many) in the database, but I don't necessarily want them to be mapped as such in the POJOs. If not, am I stuck with using my sql-query and then just manually building the Message POJO in the case of this query?

I went ahead and mapped the many-to-many relationship so that Message has a collection of Roles on it. I think this is ok and it should also simplify persisting Messages that are created for specific Roles so that the relationship is automatically persisted. I assume if I did map this relationship in this way that I would then have to manually insert the linked-table records as its own stand alone entity...which would not be desirable/pretty.

If there other angles I haven't considered yet, I'm still open for input, but for now I can live with Message having a collection of Roles mapped.