How to use “size” as a field name in a hibernate/j

Question

I have two JPA entities : VirtualLot and VirtualFiles. VirtualFiles extends VirtualInode. There is a ManyToMany relation between VirtualLot and VirtualFiles described as "A VirtualLot can contain VirtualFiles" and "A VirtualFile can be associated with multiple VirtualLots" The entities are defined as follows :

VirtualFile.java

@Entity
@Table(name = "FIL_FILE")
public class VirtualFile extends VirtualInode {
    //[...]
}

VirtualInode.java

@Entity
@Table(name = "INO_INODE")
@Inheritance(strategy = InheritanceType.JOINED)
public class VirtualInode implements Serializable {
    private Long id; /* The PK */
    private long size = 0L; /* The size */
    // [...]

    /**
     * Default constructor
     */
    public VirtualInode() {
        super();
    }

    /**
     * @return the id
     */
    @Id
    @Column(name = "INO_ID", nullable = false)
    public Long getId() {
        return id;
    }

    /**
     * @return the size
     */
    @Column(name = "INO_SIZE", nullable = false)
    public long getSize() {
        return size;
    }

    //[...]
}

VirtualLot.java

@Entity
@Table(name = "VLT_VIRTUAL_LOT")
public class VirtualLot implements Serializable {
    private Long id; /* The PK */
    private Collection<VirtualFile> files;

    /**
     * Default constructor
     */
    public VirtualLot() {
        super();
    }

    /**
     * @return the id
     */
    @Id
    @Column(name = "VLT_ID", nullable = false)
    public Long getId() {
        return id;
    }


    /**
     * @return the files
     */
    @ManyToMany
    @JoinTable(name = "R001_FIL_VLT", joinColumns = @JoinColumn(name = "VLT_ID", referencedColumnName = "VLT_ID"), inverseJoinColumns = @JoinColumn(name = "INO_ID", referencedColumnName = "INO_ID"))
    public Collection<VirtualFile> getFiles() {
        return files;
    }
    //[...]
}

Here I want to sum the size of the VirtualFiles contained in a given VirtualLot using a Spring Data JPA repository : VirtualLotRepository. The query is defined as follow :

public interface VirtualLotRepository extends JpaRepository<VirtualLot, Long>
{
    @Query("select sum(f.size) from VirtualLot l join l.files f where l.id = ?1")
    long sumFilesSize(long lotId);
}

My problem is that Hibernate/JPA messes with the "size" keyword, it interprets it as a SIZE function defined by hibernate (http://docs.jboss.org/hibernate/orm/4.1/devguide/en-US/html/ch11.html#ql-collection-expressions)

The resulted query was :

   select
        sum((select
            count(virtualfil2_.VLT_ID) 
        from
            R001_FIL_VLT files1_,
            FIL_FILE virtualfil2_ 
        inner join
            INO_INODE virtualfil2_1_ 
                on virtualfil2_.INO_ID=virtualfil2_1_.INO_ID 
        where
            virtuallot0_.VLT_ID=files1_.VLT_ID 
            and files1_.INO_ID=virtualfil2_.INO_ID)) as col_0_0_ 
    from
        VLT_VIRTUAL_LOT virtuallot0_ 
    inner join
        R001_FIL_VLT files1_ 
            on virtuallot0_.VLT_ID=files1_.VLT_ID 
    inner join
        FIL_FILE virtualfil2_ 
            on files1_.INO_ID=virtualfil2_.INO_ID 
    inner join
        INO_INODE virtualfil2_1_ 
            on virtualfil2_.INO_ID=virtualfil2_1_.INO_ID 
    where
        virtuallot0_.VLT_ID=?

which (obviously) doesn't work. I want it to be this :

   select
        sum(virtualfil2_1_.INO_SIZE)
    from
        VLT_VIRTUAL_LOT virtuallot0_ 
    inner join
        R001_FIL_VLT files1_ 
            on virtuallot0_.VLT_ID=files1_.VLT_ID 
    inner join
        FIL_FILE virtualfil2_ 
            on files1_.INO_ID=virtualfil2_.INO_ID 
    inner join
        INO_INODE virtualfil2_1_ 
            on virtualfil2_.INO_ID=virtualfil2_1_.INO_ID 
    where
        virtuallot0_.VLT_ID=?

Which works in my SQL scratchpad (with a proper '?' value).

Is it possible to tell Hibernate/JPA that size is the size property of my VirtualInode/VirtualFile entity and not the SIZE function ?

I have already tried double-quotes escaping and that doesn't work. I use an Oracle10g dialect.

EDIT : If I add a duplicate property fsize to my VirtualInode entity and use it in my request, it works, but that not the solution I'm looking for.

VirtualInode.java

@Column(name = "INO_SIZE", nullable = false, updatable = false, insertable = false)
public long getFsize() {
    return getSize();
}

public void setFsize(final long size) {
    setSize(size);
}

VirtualLotRepository.java

@Query("select sum(f.fsize) from VirtualLot l join l.files f where l.id = ?1")
long sumFilesSize(long lotId);

Answer 1

size is a reserved keyword like default or for in Java. Therefore either change your name for your variable in perhabs nodeSize or use the @Column(name = "nodeSize") Annotation with the name attribute to give a basic column a special name.

Answer 2

I'm not sure about this solution, but have a try:

Just change your @Query using the following:

@Query(value = "select sum(f.size) from VirtualLot l join l.files f where l.id = ?1", nativeQuery = true)

The nativeQuery parameter is supposed to change your query into a Oracle native query.

See this stackoverflow topic and the links provided there as reference.

Hope this will fix your problem.