How do I use an alternate JPA prov...

Why does MANUAL flushMode not work...

Seam is designed to complement a persistence architecture based on either Hibernate or JPA, both of which are object-relational mapping (ORM) frameworks. Out of the box, Seam has facilities for bootstrapping the persistence unit and managing the lifecycle of persistence manager sessions over the span of one or more page requests.

However, that doesn't mean that you can't use the underlying JDBC APIs (i.e., manual SQL). In fact, there are many different ways you can get access to a JDBC Connection object so that you can execute queries directly. In all case, keep in mind that you are likely going to have to responsible for closing the connection.

Be mindful of that fact that if you mix direct JDBC calls with an ORM persistence manager, you may get inconsistent results because the direct SQL can cause the first-level cache of the persistence manager to become out of sync.

Option 1: Retrieve the connection from the persistence manager

The Hibernate Session gives you access to the JDBC Connection object:

Session sess = ...;
try {
   Connection conn = sess.connection();
   PreparedStatement stmt = null;
   try {
      stmt = conn.prepareStatement("SQL STATEMENT HERE");
      stmt.execute();
      ResultSet rs = stmt.getResultSet();
      if (rs.next()) {
         // process results
      }
   } catch (SQLException e) {
      e.printStackTrace();
   } finally {
      if (stmt != null) {
         try {
            stmt.close();
         } catch (SQLException e) {}
      }
      try {
         conn.close();
      } catch (SQLException e) {}
   }
} catch (SQLException e) {
   e.printStackTrace();
}

If you are using JPA, you can get to the Hibernate Session as follows:

EntityManager em = ...;
Session sess = (Session) em.getDelegate();

Option 2: Use a Java EE resource injection of DataSource

If your Seam component is a Java EE component (e.g., an EJB session bean) you can inject a JCA DataSource, from which you can get a JDBC Connection. You identify the DataSource by its JNDI name. In this case, we are injecting the default HSQL DataSource from JBoss AS:

@Name("jdbcBusinessBean")
@Stateless
public class JDBCBusinessBean implements JDBCBusiness {
   @Resource(mappedName = "java:/DefaultDS")
   DataSource defaultDS;

   public void executeQuery() {
      try {
         Connection conn = defaultDS.getConnection();
         PreparedStatement stmt = null;
         ...
      } catch (SQLException e) {
         e.printStackTrace();
      }
   }
}

You can then execute this method from a JSF command button:

<h:commandButton action="#{jdbcBusinessBean.executeQuery}" value="Execute"/>

You can find another example of this idea in this code snippet

Note than in Java EE 6, you can use a producer field to promote the DataSource as a top-level component that can be injected. See the Java EE component environment resources chapter of the Weld reference guide for details.

Option 3: Create a JDBC Connection management bean

The idea here is to create a Seam component that uses DriverManager to create JDBC Connection objects. You can then inject this manager component to get a connection whenever you need one.

Option 4: Use the Spring bridge to inject a Spring JDBCTemplate

The last approach is likely the most complex to setup, but the easiest for development. Simply setup the Spring integration as described in the Spring Framework integration chapter in the Seam reference guide. Then, create a Spring bean that is also a Seam component. You can then inject this bean into any Seam component, thus allowing you to use JDBC from a Seam application.