Can EJB-QL support the syntax "select count(*) from table"?

Another question:
I use Oracle as database server, and I have a table in which one field should be set to 'sysdate' when I modify that record, Is there any solution by using CMP? or I must use BMP?

No, EJB QL does no support functions like count, max, min, etc.
There is an extension in Weblogic 7, which lets you do these, as well as other things which are missing in EJB QL. These extensions are not in the EJB standard (yet?).

As to setting a field to "sysdate" when the record is modifed in CMP. I don't think there is a direct way of doing this within EJB QL. But depending on what your requirements are, there are couple of other ways of doing this:

1. Use a database trigger :-)

2. You can get the sysdate from the database using JDBC. Don't allow write access to your beans directly, instead have a layer on top which inserts the sysdate at the right time. You can use Value Objects and an update method, or maybe a Session Facade.

Hope that helps,

Daniel.

If I DO want to use the function like "select count(*)", Is there any alternative way?

You can always write your own methods which use JDBC.

I can use JDBC directly in BMP, but how about CMP?

Yes you can in CMP. I don't think it strictly follows the principles of CMP, but there is nothing stopping you from doing it.

Daniel.

Would you please give me some sample? Thanks!

Ok, here is a code sample. I've got a Home method and a method within the bean (nothing needs to be written in the deployment descriptors). The code implements the equivalent of a finder (ie returns an instance of the bean it lives in), but uses a MAX within the sql. The sql is executed using JDBC.

Daniel.

----------------------------------------------
Home Method: ErstkonsultationBDOBean.java
----------------------------------------------
public ErstkonsultationBDOLocal getMaxSomething(String input)
  throws EJBException;


----------------------------------------------
Bean: ErstkonsultationBDOBean.java
----------------------------------------------
public ErstkonsultationBDOLocal ejbHomeGetMaxSomething(String input)
  throws EJBException{
  try {
  
    // Set things up a bit, make a connection etc
    InitialContext ic = new InitialContext();
    DataSource ds = (DataSource)ic.lookup( "MyTxDatasource" );
    Connection connection = ds.getConnection();
    
    // Get the home for later use
ErstkonsultationBDOLocalHome erstkonsultationBDOLocalHome = (ErstkonsultationBDOLocalHome)_ctx.getEJBLocalHome());

    // Define the sql to execute
    String sql =
        " select max(ekn_id)" +
        " from ERSTKONSULTATION ";

    // Parse the sql (I think), and put in the input variables
    PreparedStatement statement = connection.prepareStatement(sql);
    statement.setString( 1, input);

    // Execute the sql
    ResultSet rs = statement.executeQuery();

    // Get the 1 returned value from sql (the primary key of Erstkonsulation)
    rs.next();
    Long pk = new Long(rs.getLong( "EKN_ID" )) ;
    
    // Now use the primary key to find the actual bean (This is a finder - I need to return a bean)
    ErstkonsultationBDOLocal erstkonsulation = erstkonsultationBDOLocalHome.findByPrimaryKey(pk);

    if ( rs != null ) rs.close();
    if ( statement != null ) statement.close();
    if ( connection != null ) connection.close();

    return erstkonsulation;
  }
  catch( Exception ex ) {
    throw new EJBException( ex );
  }
}

Thanks very much

You can try to write a DAO class to access the table.

Assuming your finder will return a collection, so you will get the number of rows from collection.size()