Re: generator adds extra trailing comma to insert statement in mapper XML

[Jeff Butler]

I just looked in the code to try to figure this out. It looks like
this could happen if you have an identity field that is at the end of
the list of columns (maybe the last column in the table). I'll write
some code to guard against this for the next release, but that does
not appear to be the case in your table.

In your case, HOLDING_ID is not an identity field. An identity field
has it's value automatically generated on insert by the database (an
autonumber field). That is not the case here - you have a field that
is set from a sequence and the <selectKey> should run BEFORE the
statement executes. You should change the <generatedKey>
configuration - remove the identity=true attribute and set type=pre.

Jeff Butler


On Wed, Sep 5, 2012 at 9:37 AM, Paul Krause <paulkr...@alum.mit.edu> wrote:
> I am using MyBatis 3.1.1.
>
> Recently, I have started getting an extra trailing comma at the end of some
> the insert statements generated by MyBatis Generator. I am unable to figure
> out what change introduced this behavior. Does anyone have a clue?
>
> The generated XML looks like this (with unrelated lines removed for clarity)
>
> <insert id="insert" parameterType="com.mycompany.Holding">
> <!--
> WARNING - @mbggenerated
> This element is automatically generated by MyBatis Generator, do not
> modify.
> -->
> <selectKey keyProperty="holdingId" order="AFTER" resultType="String">
> <include refid="selectIdFromSequence" />
> </selectKey>
> insert into HOLDING (ACC_ID, CANCEL_DATE, CLOSE_DATE,
> ACCRUED_INTEREST, )
> values (#{accId,jdbcType=DECIMAL}, #{cancelDate,jdbcType=TIMESTAMP},
> #{closeDate,jdbcType=TIMESTAMP},
> #{accruedInterest,jdbcType=FLOAT}, )
> </insert>
>
>
> The generator config XML looks like this
>
>
> <table tableName="HOLDING" enableCountByExample="false"
> enableDeleteByExample="false" enableSelectByExample="false"
> enableUpdateByExample="false">
> <property name="rootInterface" value="CustomHoldingMapper"/>
> <property name="rootClass" value="AbstractHolding"/>
> <generatedKey column="HOLDING_ID" identity="true" type="post"
> sqlStatement="&lt;include refid=&quot;selectIdFromSequence&quot; />" />
> <columnOverride column="ACC_ID" javaType="String" />
> <columnOverride column="ACCRUED_INTEREST" javaType="double"/>
> <columnOverride column="CANCEL_DATE" javaType="java.sql.Timestamp" />
> <columnOverride column="CLOSE_DATE" javaType="java.sql.Date" />
> </table>
>
> Thanks,
> Paul
>

[Paul Krause]

Actually, I was using a trigger to generate the id.  Originally, I was reading the generated key from the table, but later changed it to read directly it from the sequence because some of my tables had no unique keys other than the generated one.  Changing the selectKey attributes as you suggested solved the trailing comma issue (and allowed me to get rid of the triggers), but I am concerned that moving away from identity fields will complicate the porting issue (I need to able run on both Oracle and SQL Server).

Thanks,

Paul

[Jeff Butler]

I see.

If you need to be portable, I'd suggest using a sequence directly on
both Oracle and SQL Server because it is fully supported on both.
Then you don't have to deal with the trigger to make a "faked
identity" column.

Jeff Butler

[Paul Krause]

On Thursday, September 6, 2012 12:51:56 PM UTC-4, Jeff Butler wrote:

If you need to be portable, I'd suggest using a sequence directly on
both Oracle and SQL Server because it is fully supported on both.
Then you don't have to deal with the trigger to make a "faked
identity" column.

But how can I make this work with selectKey?  If databaseId was an attribute on the sql element, I could

just do this

  <sql id="selectIdFromSequence" databaseId="oracle">

  select HOLDING_ID_SEQUENCE.NEXTVAL from DUAL

  </sql> 

  <sql id="selectIdFromSequence" databaseId="sqlserver">

  select NEXTVAL for HOLDING_ID_SEQUENCE

  </sql> 

  

But it looks like the databaseId attribute is only accepted by the select element,

which doesn't help.

[Paul Krause]

Never mind. It looks like sequences are only found in SQL Server 2012, which is not our target platform. I guess I will go back to faking the identity fields in Oracle, being careful not to let the identity field be the last field in the table.

I'll go ahead and create a feature request to add the databaseId attribute the sql, insert, and update elements anyway, because it seems like a useful thing to have. Unless there is some technical constraint that makes it unfeasible. There isn't, is there?

[Paul Krause]

On Saturday, September 8, 2012 2:46:17 PM UTC-4, Paul Krause wrote:

I'll go ahead and create a feature request to add the databaseId attribute the sql, insert, and update elements anyway, because it seems like a useful thing to have. 

http://code.google.com/p/mybatis/issues/detail?id=667 

[Jeff Butler]

Paul,

I've finally had some time to look at this further.

The <selectKey> element allows the databaseId attribute. I can change
the generator to allow you to specify multiple <generatedKey> elements
for different database ids. This way you can configure the generator
to generate different "get identity value" calls for different
databases. Seems to me this could be a good fix to the issue you've
raised here.

Jeff Butler