Harry Potter and how to create MySQL fields which are auto incremented to use with ADF BC

MySQL has an auto_increment field, which can be used as a primary key. A sequence object when you think in Oracle database terms. But in the case of Oracle you can access to sequence values directly, but in the case of MySQL you don't have such a possibility.
If you want to have a field, which is auto incremented and still have the flexibility you can follow the steps below.

Create a table (counters) which stores a sequence name and last value.

CREATE TABLE `counters` (
  `counter_code` varchar(40) NOT NULL,
  `last_id` bigint(20) unsigned NOT NULL,
  `created_by` varchar(40) DEFAULT NULL,
  `creation_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_updated_by` varchar(40) DEFAULT NULL,
  `last_updated_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`counter_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Create a procedure, which increases the sequence value and returns the value as an out parameter.
Note, that the procedure updates the counters table, this means that it has to issue a lock, which maybe the source of a bottleneck.

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_next_sequence_id`(in counterCode varchar(128), out lastId  bigint(20) unsigned)
BEGIN
DECLARE rowCount integer;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET rowCount=0;
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
DECLARE EXIT HANDLER FOR SQLWARNING ROLLBACK;

SELECT
    COUNT(*)
INTO rowCount FROM
    counters
WHERE
    counter_code = counterCode;

if rowCount=0 then
START TRANSACTION;
INSERT INTO counters
(counter_code,last_id,created_by) VALUES(counterCode,1,'system');
COMMIT;
SET lastId =1;
else
START TRANSACTION;

UPDATE counters
SET
    last_id = (@cur_value:=last_id) + 1,
    last_updated_by = 'system'
WHERE
    counter_code = counterCode;
 
SELECT @cur_value + 1 INTO lastId;
   
   COMMIT;
end if;
END$$
DELIMITER ;

In your ADF Model Entity Object class create the following.

    @Override
    protected void create(AttributeList attributeList) {
        int nextVal = 0;
        super.create(attributeList);
        nextVal = getNextSequenceValue("NEWSEQ");
        setAttribute("ReportId", new BigDecimal(nextVal));
    }

    public int getNextSequenceValue(String sequenceNumber) {
        int nextVal = 0;
        DBTransactionImpl transaction = (DBTransactionImpl) getDBTransaction();
        CallableStatement statement = transaction.createCallableStatement("call get_next_sequence_id(?,?);", 0);
        try {
            statement.setString(1, sequenceNumber);
            statement.registerOutParameter(2, Types.INTEGER);
            statement.execute();
            nextVal = statement.getInt(2);
            statement.close();

        } catch (SQLException e) {
            throw new JboException(e);
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException e) {
                throw new JboException(e);
            }
        }
        return nextVal;
    }

Or you can create the same method in your AM and wire it to your attribute default value (choose "Expression") by the help of a groovy expression:adf.object.getDBTransaction().getRootApplicationModule().getNextSequenceValue("NEWSEQ")