Need Help on autogenerated Id Column

[Mahmood Lebbai]

Team,

   I have following table structure

 create table EREPNoticeRequest (

        NOTICEID bigint not null ,

        CaseId not null

        primary key ( NOTICEID  )

    );

I tried to use the sculptor design model to generate the table structure like the above.

Following is my btdesign

ApplicationPart CorrespondencePart

{

Module correspondence

{

basePackage = gov.utah.erep.osi.correspondence

Entity EREPNoticeRequest

{

databaseTable="EREPNoticeRequest"

!auditable

!optimisticLocking

scaffold

Long noticeId key databaseColumn="NOTICEID" databaseType="BIGINT"

Long caseId databaseColumn="CASEID"

 

}

}

}

When I compile above bt design file , sculptor generates the "Id" column by default , I would like to use our own defined column ( In this Case NoticeId) instead of automatic generated column.

Please help us , how to avoid using default generation of Id column and use the column name defined in the Entity to take the key/ identity column in Entity Model?.

I'm using DB2 as database and after enabled hbm2dll plugin in pom and made following settings in scylptor-generated.properties , following ddl has been generated but the default generated Id  column seems to be not correct. Please help us to resolve the issue.

Sculptor-generated.properties

db.product=db2

generate.ddl=false

db.db2.maxNameLength=27

db.db2.hibernate.dialect=org.hibernate.dialect.DB2Dialect

db.db2.onDeleteCascade=true

db.db2.type.Boolean=CHAR(1)

db.db2.type.boolean=CHAR(1)

db.db2.type.Integer=INTEGER

db.db2.length.Integer=31

db.db2.type.int=INTEGER

db.db2.length.int=31

db.db2.type.Long=BIGINT

db.db2.length.Long=63

db.db2.type.long=BIGINT

db.db2.length.long=63

db.db2.type.Date=DATE

db.db2.type.java.util.Date=DATE

db.db2.type.DateTime=TIMESTAMP

db.db2.type.Timestamp=TIMESTAMP

db.db2.type.BigDecimal=NUMERIC

db.db2.type.Double=NUMERIC

db.db2.type.double=NUMERIC

db.db2.type.String=VARCHAR

db.db2.length.String=100

db.db2.length.Enum=40

db.db2.type.discriminatorType.STRING=VARCHAR

db.db2.length.discriminatorType.STRING=31

db.db2.type.discriminatorType.CHAR=CHAR(1)

db.db2.type.discriminatorType.INTEGER=INTEGER

db.db2.type.Clob=CLOB

db.db2.type.Blob=BLOB

Special Case xpt file:

«AROUND templates::jpa::JPA::persistenceUnitAdditionalProperties FOR Application»

     <property name="hibernate.hbm2ddl.auto" value="create-drop"/>

«ENDAROUND»

 «AROUND *::hibernateConnectionDatasourceProperties FOR Application»

     <prop key="hibernate.hbm2ddl.auto">create-drop</prop>

     «targetDef.proceed()»

 «ENDAROUND»

Generated DDL file

 create table EREPNoticeRequest (

        ID bigint generated by default as identity,

        CASEID bigint not null,

        NOTICEID bigint not null unique,

        primary key (ID)

    );

 

Regards,

Mahmood

[Mahmood Lebbai]

Can anyone help me on the below issue

[Patrik Nordwall]

What happens if you use
Long id databaseColumn="NOTICEID"

Instead of

Long noticeId key databaseColumn="NOTICEID" databaseType="BIGINT"

/Patrik

[Mahmood Lebbai]

Thanks Patrick. I tried that but the generated SQL is as follows.

create table EREPNoticeRequest (
NOTICEID bigint generated by default as identity,
CASEID bigint not null,
UUID varchar(255) not null unique,
primary key (NOTICEID)
);

I would like not to generate UUID and as well as NOTICEID column
identity SQL script is not generated as expected. Thanks for helping
me on this issue.

Regards,
Mahmood

On Apr 5, 7:29 am, Patrik Nordwall <patrik.nordw...@gmail.com> wrote:
> What happens if you use
>   Long id databaseColumn="NOTICEID"
>
> Instead of
>   Long noticeId key databaseColumn="NOTICEID" databaseType="BIGINT"
>
> /Patrik
>

[Patrik Nordwall]

uuid comes from that you have not defined any property as natural key. I think that is explained in Adv Tutorial. Related to equals/hashCode.

I can look closer when I'm at a real computer.

/Patrik

[Mahmood Lebbai]

1. Thanks I had resolved UUID issue by specifying noticeid as key. But
Id column generated is not seems to be correct on DB2.

NOTICEID bigint generated by default as identity,

2. How to specify the length for Clob or Blob Column. I have Clob
column with different size , how to specify the clob column in
btdesign with length attribute?.

On Apr 5, 9:52 am, Patrik Nordwall <patrik.nordw...@gmail.com> wrote:
> uuid comes from that you have not defined any property as natural key. I think that is explained in Adv Tutorial. Related to equals/hashCode.
>
> I can look closer when I'm at a real computer.
>
> /Patrik
>

[Patrik Nordwall]

On Thu, Apr 5, 2012 at 6:19 PM, Mahmood Lebbai <mahmood...@connvertex.com> wrote:

1. Thanks I had resolved UUID issue by specifying noticeid as key. But
Id column generated is not seems to be correct on DB2.

  NOTICEID bigint generated by default as identity,

In Sculptor the id property has some very special meaning. It is always an autogenerated value. Used as primary key and foreign key relations. I think you will have big trouble trying to change that part. I would recommend that you use id as default, but then add noticeid as natural key, as you started. What is the problem with that, except that you get an extra column?

 

2. How to specify the length for Clob or Blob Column. I have Clob
column with different size , how to specify the clob column in
btdesign with length attribute?.

You can define the length of Clob in sculptor-generator.properties

db.db2.length.Clob=999

That will generate annotation

@Column(name = "TEXT", nullable = false, length = 999)

[Mahmood Lebbai]

On Fri, Apr 6, 2012 at 8:58 AM, Patrik Nordwall <patrik....@gmail.com> wrote:

On Thu, Apr 5, 2012 at 6:19 PM, Mahmood Lebbai <mahmood...@connvertex.com> wrote:

1. Thanks I had resolved UUID issue by specifying noticeid as key. But
Id column generated is not seems to be correct on DB2.

  NOTICEID bigint generated by default as identity,

In Sculptor the id property has some very special meaning. It is always an autogenerated value. Used as primary key and foreign key relations. I think you will have big trouble trying to change that part. I would recommend that you use id as default, but then add noticeid as natural key, as you started. What is the problem with that, except that you get an extra column?

<<Mahmood>> Thanks again for the reponse. We had a requirement to change the legacy application into new technology wherein we cannot change the database structure due to various reason. Hence we donot have an option to change the database but we can use sculptor model to design the structure of entity and their relation according to existing model design. 

 

2. How to specify the length for Clob or Blob Column. I have Clob
column with different size , how to specify the clob column in
btdesign with length attribute?.

<<Mahmood>> Thanks again. I had set the clob length in sculptor properties but I have different clob length needs to be set , it should be nice if we can set the length in entity properties like the one we define for string. 

[Pavel Tavoda]

I'm not sure but if I remember right you can do also something like:

Entity Some {
Long id databasaColumn="someDbColumn";
...

Pavel

[Oliver Ringel]

It seems there is room for optimizations and improvements handling IDs and LOBs.  

It should be possible to use sculptor with legacy systems, so we should take care of
column mappings and datatypes.

 

In your case a quick workaround could help you with the CLOB problem.

 

Add

    generate.jpa.annotation.columnDefiniton=true

to your sculptor-generator-properties file.

 

This will add the database type definitions to all column annotations

(not optimal but it’s only a workaround).

 

Then add databaseType=”CLOB(999)” to your desired attributes.

The result is an annotation that looks like

   @Column (…, columnDefinition=”CLOB(999)”)

 

As an alternative you can solve your problem by adding a hint (hint=”length=999”) to your

clob attributes and override

    templates::domain::DomainObjectAttributeAnnotation::columnAnnotations

to make use of this hint.

 

What do you think about this ID definition.

 

   Long id key databaseColumn="NOTICEID"

 

This will add a id attribute and map it to a column named NOTICEID.

It will not generate a uuid attribute. Instead it will use the existing id attribute.

(I know that is not the intention of the key attribute).

 

Regards, Oliver