Increment field based on foreign key (JPA, Hibernate, Oracle DB)

An @OrderColumn annotation can be used.

public @interface OrderColumn

Specifies a column that is used to maintain the persistent order of a list. The persistence provider is responsible for maintaining the order upon retrieval and in the database. The persistence provider is responsible for updating the ordering upon flushing to the database to reflect any insertion, deletion, or reordering affecting the list.

The OrderColumn annotation is specified on a OneToMany or ManyToMany relationship or on an element collection. The OrderColumn annotation is specified on the side of the relationship that references the collection that is to be ordered. The order column is not visible as part of the state of the entity or embeddable class.

The OrderBy annotation should be used for ordering that is visible as persistent state and maintained by the application. The OrderBy annotation is not used when OrderColumn is specified.

The order column must be of integral type. The persistence provider maintains a contiguous (non-sparse) ordering of the values of the order column when updating the association or element collection. The order column value for the first element is 0.

The only drawback is that the number for the first element is 0, not 1

An example:

@Entity
@Table(name="STUDENT")
@SequenceGenerator(name="student_seq", initialValue=1, 
                   allocationSize=10,sequenceName="STUDDENT_SEQ")
public class Student {

    @Id
    @GeneratedValue(strategy=GenerationType.SEQUENCE,generator="student_seq")
    private Long Id;

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

    @OneToMany(cascade = CascadeType.ALL, mappedBy="student")
    @OrderColumn(name="FOLLOWUP_NUMBER")
    private List<Book> books = new ArrayList<>();

    ...............
    ...............
}

@Entity
@SequenceGenerator(name="book_seq", initialValue=1, 
                   allocationSize=10, sequenceName="BOOK_SEQ")
public class Book {

    @Id
    @GeneratedValue(strategy=GenerationType.SEQUENCE,generator="book_seq")
    private Long id;

    @ManyToOne
    @JoinColumn(name="STUDENT_ID")
    private Student student;

    @Column(name="TITLE", nullable=false)
    private String title;

    ..................
    ..................
}

And this simple test case:

EntityTransaction tr = em.getTransaction();

tr.begin();

for (int j = 1; j < 10; j++) {
    Student student = new Student("Student name " + j);
    for (int i = 1; i <= 10; i++) {
        student.getBooks().add(new Book("Some book" + i, student));
    }
    em.persist(student);
}

tr.commit();

generates the following tables (tested on Oracle 12c and Hibernate 5.2):

select * from student;
   ID NAME               
----- --------------------
    1 Student name 1      
    2 Student name 2      
    3 Student name 3      
    4 Student name 4
    .......
    .......

select * from book
order by student_id, followup_number;
   ID TITLE                STUDENT_ID FOLLOWUP_NUMBER
----- -------------------- ---------- ---------------
    1 Some book1                    1          0
    2 Some book2                    1          1
    3 Some book3                    1          2
    4 Some book4                    1          3
    5 Some book5                    1          4
    6 Some book6                    1          5
    7 Some book7                    1          6
    8 Some book8                    1          7
    9 Some book9                    1          8
   10 Some book10                   1          9
   11 Some book1                    2          0
   12 Some book2                    2          1
   13 Some book3                    2          2
   14 Some book4                    2          3
   15 Some book5                    2          4
   16 Some book6                    2          5
   17 Some book7                    2          6
   18 Some book8                    2          7
   19 Some book9                    2          8
   20 Some book10                   2          9
   21 Some book1                    3          0
   22 Some book2                    3          1
   23 Some book3                    3          2
   ........
   ........

A performance might be another disadvantage, because for each book Hibernate generates two SQL commands - one insert, and then one update of FOLLOWUP_NUMBER column.
What is worse, when some book is removed, then Hibernate generates all numbers for a given student again and fires updates of FOLLOWUP_NUMBER column for all remaining books.

EDIT

But what bothers me is "The order column is not visible as part of the state of the entity or embeddable class.". The data is just as I'd like it in the DB, but it isn't reflected onto my entity. While I'd like to make use of this FOLLOWUP_NUMBER

I don't know if this is possibe in JPA, but the Hibernate extension @Formula can be used in this case:

class Book{
    .......
    .......

    @Formula(value="FOLLOWUP_NUMBER")
    private Long followUpNumber;

    public Long getFollowUpNumber() {
        return followUpNumber;
    }

    ........
    ........
}

This calculated field can be even queried, for example the below test case:

Query query = em.createQuery(
   "Select b FROM Book b JOIN b.student s " +
   "WHERE s.name = :studentname AND b.followUpNumber = :follownbr"
);
query.setParameter("studentname", "Student name 6");
query.setParameter("follownbr", Long.valueOf(4));
Book book = (Book)query.getSingleResult();

System.out.println("Found book = " + book.getTitle());
System.out.println("book follow nbr= " + book.getFollowUpNumber());

gives the following result:

Found book = Some book5
book follow nbr= 4

and Hibernate uses the below SQL to find that book:

    select
        book0_.id as id1_0_,
        book0_.STUDENT_ID as STUDENT_ID3_0_,
        book0_.TITLE as TITLE2_0_,
        book0_.FOLLOWUP_NUMBER as formula0_ 
    from
        Book book0_ 
    inner join
        STUDENT student1_ 
            on book0_.STUDENT_ID=student1_.Id 
    where
        student1_.NAME=? 
        and book0_.FOLLOWUP_NUMBER=?