Hi,

     I am using Hibernate  to map java object to Paradox DBF files. Not the best use of Hibernate but I need to query the data in the DBF files in a nice easy way and Hibernate is the answer. The problem I am having is that my data does not have a unique identifier or even unique rows ! ie there are 3 fields in the database and there are several rows in this database that are identical. This is correct according to the database schema and its use. In order to use Hibernate I need a unique primary key, well my DBF driver (the HXTT JDBC driver) provides me with a recno() SQL function to return the record number of the current record. Brilliant ! I can use this as the primary key.

     I created the following mapping -

    private long recno;

    @Id
    @Column(name = "recno()")
    public long getRecno() {
        return recno;
    }

     Well that didn;t work because Hibernate/JPA compains about a parse error (probably the brackets) -

[2A000] Syntax error:  Stopped parse at ((id12)

     So I tried quoting the function -

    private long recno;

    @Id
    @Column(name = "[recno()]")
    public long getRecno() {
        return recno;
    }

     This doesn;t work either! The driver is generating good SQL now but because Hibernate is putting a table alias in front of the recno() function my DBF driver doesn;t understand it.

     After much digging I found the @Formula annotation so I tried the following -

    private long recno;

    @Basic
    @Formula(value = "recno()")
    public long getRecno() {
        return recno;
    }

     Note the use of the @Basic annotation. This works wonderfully but doesn;t get me an Id so I try -

    private long recno;

    @Id
    @Formula(value = "recno()")
    public long getRecno() {
        return recno;
    }

     All I get from this is the following stack trace -

java.lang.NullPointerException

at org.hibernate.cfg.Ejb3Column.forceNotNull(Ejb3Column.java:351)

at org.hibernate.cfg.AnnotationBinder.processElementAnnotations(AnnotationBinder.java:1975)

at org.hibernate.cfg.AnnotationBinder.processIdPropertiesIfNotAlready(AnnotationBinder.java:769)

at org.hibernate.cfg.AnnotationBinder.bindClass(AnnotationBinder.java:733)

at org.hibernate.cfg.AnnotationConfiguration.processArtifactsOfType(AnnotationConfiguration.java:636)

at org.hibernate.cfg.AnnotationConfiguration.secondPassCompile(AnnotationConfiguration.java:359)

at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1377)

at org.hibernate.cfg.AnnotationConfiguration.buildSessionFactory(AnnotationConfiguration.java:954)

     I guess this is because the @Id annotation from javax.persistence does not understand the @Formula and therefore can;t find the mapping.

     Does anyone know of a way to use a SQL function such as recno() as the @Id element in a Hibernate/JPA mapping?? I cannot modify the schema, structure, data or names of the DBF files I am dealing with!