How to persist data through JPA without needing a primary key

Question

I have data that flows through my application and normally it doesn't need to be bothered but to implement a new feature I need to store it temporarily (e.g. 1 hr). The data going in can be the exact same as something that is already in there so there is no need for a primary key. However, with JPA Entities need an Id but I don't need/want one. This is preventing me from getting it working.

This is through Spring using JPA. Since the data is moving in and out of the database frequently, the use of an auto generated ID is discouraged because it'll go through the IDs in a few years time. I have tried to make it embeddable to which it says I need to do a component scan to find where it is used but if I make it an entity then it gives me the error that it needs a primary key.

This is my entity that stores the data I need to persist.

@Entity
@Table(name = "quoteOrderHistory")
public class QuoteOrderHistory {

    @Column(name = "storeNumber")
    private int storeNumber;

    @Column(name = "invoiceNumber")
    private String invoiceNumber;

    @Column(name = "quoteSaleDate")
    private Date quoteSaleDate;

    @Column(name="orderTotal")
    private BigDecimal orderTotal;

    @Column(name="orderHistoryDate")
    private Timestamp orderHistoryDate;

    // Constructors, Getters and Setters

}

This is my repository to access the data.

@Repository
public interface QuoteOrderHistoryRepository extends JpaRepository<QuoteOrderHistory, Long> {

    @Query("DELETE FROM QuoteOrderHistory q WHERE q.orderHistoryDate > date")
    void deleteAllExpired(Date date);

    @Query("SELECT q FROM QuoteOrderHistory q WHERE q.storeNumber = ?1 AND q.invoiceNumber = ?2 ORDER BY q.orderHistoryDate DESC")
    List<QuoteOrderHistory> findAllByStoreAndInvoiceDesc(int storeNumber, String invoiceNumber);
}

I can't figure out to get this to work. Again a primary key isn't needed since it's suppose to support duplicate entries. If there is another way around this without using JPA then I'm all for it but currently it seems to be the easiest to persist the data. If you need anymore information just let me know. I also might be missing something that can be done to avoid this all together but I'm not that familiar with JPA. So all help is appreciated.

If you're using Hibernate5, you could use the UUIDGenerator. See https://stackoverflow.com/questions/6356834/using-hibernate-uuidgenerator-via-annotations — racraman, Jun 20 '19 at 23:31

score 2 · Answer 1 · answered Jun 20 '19 at 23:41

You shouldn't run out of IDs for a column if you use the correct size. Stop trying to fight your framework and just add an auto-incrementing column.

https://hashrocket.com/blog/posts/running-out-of-ids

Let's say business is so good that we are inserting 10,000 records per minute into our table. So, how long would it take to max out our sequence? 1750380517 years

From How large can an id get in postgresql

Name        Storage Size    Description                       Range
smallint    2 bytes         small-range integer               -32768 to +32767
integer     4 bytes         usual choice for integer          -2147483648 to +2147483647
bigint      8 bytes         large-range integer               -9223372036854775808 to 9223372036854775807
serial      4 bytes         autoincrementing integer          1 to 2147483647
bigserial   8 bytes         large autoincrementing integer    1 to 9223372036854775807

If you're desperate to not use an id column for some reason I cannot possibly comprehend, it looks like you can do it in JPA by making every column part of the primary key description, but then your deletes and updates will delete/update any number of records. I HAVE NOT TRIED THIS. I WOULD NOT IMPLEMENT THIS ON A PRODUCTION SERVER.

https://en.wikibooks.org/wiki/Java_Persistence/Identity_and_Sequencing#No_Primary_Key

Sometimes your object or table has no primary key. The best solution in this case is normally to add a generated id to the object and table. If you do not have this option, sometimes there is a column or set of columns in the table that make up a unique value. You can use this unique set of columns as your id in JPA. The JPA Id does not always have to match the database table primary key constraint, nor is a primary key or a unique constraint required.

If your table truly has no unique columns, then use all of the columns as the id. Typically when this occurs the data is read-only, so even if the table allows duplicate rows with the same values, the objects will be the same anyway, so it does not matter that JPA thinks they are the same object. The issue with allowing updates and deletes is that there is no way to uniquely identify the object's row, so all of the matching rows will be updated or deleted.

If your object does not have an id, but its table does, this is fine. Make the object an Embeddable object, embeddable objects do not have ids. You will need a Entity that contains this Embeddable to persist and query it.

They're faster to generate (no randomness required) but still guaranteed to be unique if you use the interface address. — chrylis -cautiouslyoptimistic-, Jun 21 '19 at 15:25
The standard default of random (type 4) UUID, which needs a lot of randomness for stuff like this. — chrylis -cautiouslyoptimistic-, Jun 21 '19 at 15:29
Oh sure. But why use a UUID when you can just use an autoincrement column? — Jazzepi, Jun 21 '19 at 15:40
UUID IDs can be generated independently by clients before insert, which can be useful in certain cases. — chrylis -cautiouslyoptimistic-, Jun 21 '19 at 17:00

score 0 · Accepted Answer · answered Jun 21 '19 at 18:11

Jazzepi stated was correct but I was strictly requested not to use an auto generated number as the ID. Therefore, people linked this here depicting using a UUID. This is the best choice for this problem since the objects in the database are timed to be in there no more than a few hours. Since this is the case, a UUID will never overflow and the likelihood of a repeated UUID inside of the table any given time is almost zero since most won't stay there.

New Entity class:

@Entity
@Table(name = "quoteOrderHistory")
public class QuoteOrderHistory {

    @Id
    @GeneratedValue(generator = "uuid")
    @GenericGenerator(name = "uuid", strategy = "org.hibernate.id.UUIDGenerator")
    @Column(name = "uuid", unique = true)
    private String uuid;

    @Column(name = "storeNumber")
    private int storeNumber;

    @Column(name = "invoiceNumber")
    private String invoiceNumber;

    @Column(name = "quoteSaleDate")
    private Date quoteSaleDate;

    @Column(name="orderTotal")
    private BigDecimal orderTotal;

    @Column(name="orderHistoryDate")
    private Timestamp orderHistoryDate;

    // Constructor, getters, setters

}

I really don't understand why you would want to use a UUID over an auto-incremented counter? You cannot run out of values with it. — Jazzepi, Jun 27 '19 at 22:02

How to persist data through JPA without needing a primary key

2 Answers2

Linked