[jira] Commented: (OPENJPA-236) No apparent way to generate all required SQL within a class derived from FullClassStrategy

["David Ezzio (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/OPENJPA-236?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12511545 ] 

David Ezzio commented on OPENJPA-236:
-------------------------------------

OpenJPA does not have an example of the full (or flat or vertical) class mapping where every line of SQL used by OpenJPA is generated by user defined code.

Such an example is not easy (and probably not possible) to create because the various class strategies are vestiges that do little in normal SQL generation. Although they provide customizing methods to generate the SQL for some operations, the classes suffer from several shortcomings.

    1. Some SQL generation functions do not call the various class strategies and their customizing methods. For example, lazily loading a one-to-many relationship calls only the isPrimaryKeyObjectId method of the FullClassStrategy class and its superclasses.
        
    2. The class mappings do little in the generation of the default SQL. The real work is done elsewhere.
        
    2. It is not clear how to implement customizing methods for a modestly complex object model.
    
    3. It is not clear whether all required parameters are supplied to the customizing method to handle all likely mapping situations.

    4. The level of OpenJPA skill required to be successful in customizing the generated SQL is much higher than should be expected of our users.

There are various reasons why applications designers may want to override all (or any particular) SQL generated by OpenJPA. For example, they may need to call stored procedures.

In addition, application developers may need to vet the generated SQL or substitute other SQL for it. The various class mapping strategies do not provide a "here's the SQL I plan to use, tell me what I should use" method.

A good example (and test case) of the functionality required would use a data model with table names and column names different from the JPA defaults without any annotations or metadata to inform OpenJPA of the differences. Instead, the user defined SQL generation would ensure that the SQL used contained the appropriate names.

The problem involves generating the custom SQL and then, in the case of select statements, using the result set returned.


> No apparent way to generate all required SQL within a class derived from FullClassStrategy
> ------------------------------------------------------------------------------------------
>
>                 Key: OPENJPA-236
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-236
>             Project: OpenJPA
>          Issue Type: New Feature
>          Components: kernel
>    Affects Versions: 0.9.7
>            Reporter: David Ezzio
>         Attachments: OpenJPABugCustomSQL.zip
>
>
> FullClassStrategy has several optional methods for overriding OpenJPA's generation of SQL. However, in a simple case, there is no apparent way to generate the required SQL within a class derived from FullClassStrategy.
> The model consists of two classes, BizA which has a one-way, one-to-many relationship to BizB. The relationship is mapped with a FK within the BizB table.
> The custom class strategy BizBMapping attempts to insert a BizB record and is able to do so, provided that it is not contained within a BizA collection. Otherwise, the attempt to persist a BizA with a contained BizB yields a SQL exception because OpenJPA generates additional SQL that conflicts with the SQL generated within BizBMapping.customInsert. Even if OpenJPA did not generate conflicting insert statement, there is no apparent way to get the value for the A_ID column within the BizBMapping.customInsert method.
> This issue is critical for users who want to substitute their own SQL (such as calls to stored procedures) for all of the OpenJPA generated SQL.
> Synopsis of the output log:
> INSERT INTO BIZB (ID, INFO) VALUES (?, ?) [params=(String) B22047141, (String) random]
> INSERT INTO BizA (id, info) VALUES (?, ?) [params=(String) A32968849, (String) random]
> INSERT INTO BizB (A_ID) VALUES (?) [params=(String) A32968849]
> SQLException: Column 'ID'  cannot accept a NULL value. 

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.