3 Replies Latest reply on Jan 4, 2004 3:14 PM by Juha Lindfors

    Where to post code ? - entity cmd to generate primary key wh

    Mikkel Heisterberg Newbie

      I have created an entity command to generate primary keys when the entity bean contains more primary key columns than one (to work around the "Generation only supported with single PK field" error message). The entity command is an extension to the JDBCInsertPKCreateCommand.

      The problem is that some times your entity bean may have multiple primary key fields but only one of them should be auto-incremented (the others are used in the database schema as part of the primary key). I have written a simple solution where you pass the name of the field to be auto-incremented to the entity command.

      I will submit the code if anyone is interested...

      lekkim

        • 1. Re: Where to post code ? - entity cmd to generate primary ke
          ebaskoro Newbie

          Hi lekkim,

          Please show us your code. Thank you.

          Eki

          • 2. Re: Where to post code ? - entity cmd to generate primary ke
            Mikkel Heisterberg Newbie

            Sure.

            package dk.xreport.jboss.ejb.plugins.cmp.jdbc.keygen;
            
            import org.jboss.deployment.DeploymentException;
            
            import org.jboss.ejb.EntityEnterpriseContext;
            import org.jboss.ejb.plugins.cmp.jdbc.JDBCInsertPKCreateCommand;
            import org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager;
            import org.jboss.ejb.plugins.cmp.jdbc.JDBCUtil;
            import org.jboss.ejb.plugins.cmp.jdbc.bridge.JDBCCMPFieldBridge;
            import org.jboss.ejb.plugins.cmp.jdbc.metadata.JDBCEntityCommandMetaData;
            
            import java.sql.Connection;
            import java.sql.ResultSet;
            import java.sql.SQLException;
            import java.sql.Statement;
            
            import javax.ejb.CreateException;
            
            import javax.sql.DataSource;
            
            
            /**
             * Entity command to generate primary key for an entity even though the
             * entity has more than one primary key field. This is done by using the
             * same approach as the entity command that generates primary keys from
             * a sequence, but by specifically looking for the primary key field name
             * with the name specified as an attribute to the entity command
             * (attribute name: pk-name).
             * <p></p>
             * The entity command can also generate the SQL
             * to invoke dynamically at runtime by appending data from the specified field
             * (attribute name: sql-append-field) to the specified SQL.
             * <p></p>
             * This is used for incidents where each context has their own sequence called
             * SEQ_INCIDENT_CTX? where ? is the context number, e.g.:
             * SEQ_INCIDENT_CTX1 is the sequence for context 1
             * SEQ_INCIDENT_CTX2 is the sequence for context 2 etc.
             *
             *
             * <p></p>
             * Modified: $Date: 2004/01/02 22:43:52 $
             * @author $Author: lekkim $
             * @version $Revision: 1.1 $
             */
            public class JDBCMultiplePkSqlCreateCommand extends JDBCInsertPKCreateCommand {
             // declarations
             private String pkSQL = null; // the SQL to invoke to obtain next key
             private String pkName = null; // the name of the field to generate key for
             private String pkSQLAppendField = null; // name of field where we should append the value to the sql
             private JDBCCMPFieldBridge pkField;
            
             public void init(JDBCStoreManager manager) throws DeploymentException {
             super.init(manager);
             pkField = getGeneratedPKField();
             }
            
             protected JDBCCMPFieldBridge getGeneratedPKField() throws DeploymentException {
             // extract the pk field to be generated
             JDBCCMPFieldBridge pkField = null;
             JDBCCMPFieldBridge[] pkFields = entity.getPrimaryKeyFields();
            
             for(int i = 0; i < pkFields.length; ++i) {
             // is this the primary key we are looking for ?
             log.debug("Found table field: " + pkFields.getFieldName());
            
             if (pkFields.getFieldName().equals(pkName) && null == pkField) {
             log.debug("Found incident_id primary key: " + pkFields.getFieldName());
             pkField = pkFields;
             }
             }
             return pkField;
             }
            
             protected void initEntityCommand(JDBCEntityCommandMetaData entityCommand) throws DeploymentException {
             super.initEntityCommand(entityCommand);
            
             pkSQL = entityCommand.getAttribute("pk-sql");
             pkName = entityCommand.getAttribute("pk-name");
             pkSQLAppendField = entityCommand.getAttribute("sql-append-field");
            
             if (null == pkSQL || null == pkName) {
             throw new DeploymentException("pk-sql and pk-name attributes must be set for entity " + entity.getEntityName());
             }
            
             if (debug) {
             if (null != pkSQLAppendField) {
             log.debug("Generate PK sql is: " + pkSQL + ", append data from field: " + pkSQLAppendField);
             } else {
             log.debug("Generate PK sql is: " + pkSQL);
             }
             }
             }
            
             protected void generateFields(EntityEnterpriseContext ctx) throws CreateException {
             // call super
             super.generateFields(ctx);
            
             // declarations
             Connection con = null;
             Statement s = null;
             ResultSet rs = null;
             String sql_to_invoke = null;
            
             try {
             // see if we should compose SQL at runtime
             if (null != pkSQLAppendField) {
             // we should - append value from the specified field to the
             // supplied SQL
             if (debug) {
             log.debug("Should append data from field: " + pkSQLAppendField);
             }
            
             JDBCCMPFieldBridge[] fields = entity.getTableFields();
             for (int i=0; i<fields.length; i++) {
             if (fields.getFieldName().equals(pkSQLAppendField)) {
             // found the field - append value to SQL
             sql_to_invoke = pkSQL + fields.getValue(ctx);
             }
             }
             } else {
             // nope - use SQL as specified
             log.debug("Invoke SQL as specified...");
             sql_to_invoke = pkSQL;
             }
            
             if (debug) {
             log.debug("Executing SQL: " + sql_to_invoke);
             }
            
             // get connection
             DataSource dataSource = entity.getDataSource();
             con = dataSource.getConnection();
             s = con.createStatement();
            
             // execute SQL
             rs = s.executeQuery(sql_to_invoke);
            
             if (!rs.next()) {
             throw new CreateException("Error fetching next primary key value: result set contains no rows");
             }
            
             // set field value
             pkField.loadInstanceResults(rs, 1, ctx);
            
             if (debug) {
             JDBCCMPFieldBridge[] fields = entity.getTableFields();
             for (int i=0; i<fields.length; i++) {
             log.debug("Field " + i + " (" + fields.getFieldName() + ") = " + fields.getValue(ctx));
             }
             }
            
             } catch (SQLException e) {
             log.error("Error fetching the next primary key value", e);
             throw new CreateException("Error fetching the next primary key value:" + e);
             } finally {
             JDBCUtil.safeClose(rs);
             JDBCUtil.safeClose(s);
             JDBCUtil.safeClose(con);
             }
             }
            }
            
            


            • 3. Re: Where to post code ? - entity cmd to generate primary ke
              Juha Lindfors Master

              You can post it as a patch, it will increase its survival rate.