I am exploring EJB 2.1 CMP and just thinking what if there is a need to write direct SQL queries in the business methods in the CMP ?

> any restriction from the specs not to write direct SQL queries
  
> any implications on container managing the transactions

> or for that matter any other reason that would recommend not to write direct SQL queries in the CMP.

Please share your thoughts on this.

You can write SQL queries. Just remember to make the DataSource available to your bean's environment.

However, there are several reasons this isn't a great idea in general. For example:
- One of the goals of CMP is to make your code less dependent on the database (in theory if not always in practice). By coding your own SQL you're cutting against this.
- If you do any updates in your SQL, you'll play havoc with your EJB container's entity bean cache, unless you invalidate it using proprietary APIs

Transaction management should work fine if it's delimited by a session facade, not the entity beans. This is a best practice anyway.

You're probably better to put the SQL in DAO's behind session beans.

Regards,
Rod Johnson, author of Expert One-on-One J2EE Design and Development

I do not believe that there are any restrictions as long as your SQL is addressing tables that are not subject to CMP by any other bean. For example, you may have dependent objects that are best managed by SQL and the use of an ordinary Java object.

What I have never been clear on is whether a SQL query (as opposed to create, update & delete) on a CMP managed table is potentially harmful. What, for example, are the transactional implications of doing so?