Skip to content

Jpa Postgresql Sequence Example Essay

Marking a field with the @GeneratedValuejavax.persistence.GeneratedValueJPA annotationProvides for the specification of generation strategies for the values of primary keys.See JavaDoc Reference Page... annotation specifies that a value will be automatically generated for that field. This is primarily intended for primary key fields but ObjectDB also supports this annotation for non-key numeric persistent fields as well. Several different value generation strategies can be used as explained below.

This page covers the following topics:

The Auto Strategy

ObjectDB maintains a special global number generator for every database. This number generator is used to generate automatic object IDs for entity objects with no primary key fields defined (as explained in the previous section).

The same number generator is also used to generate numeric values for primary key fields annotated by @GeneratedValuejavax.persistence.GeneratedValueJPA annotationProvides for the specification of generation strategies for the values of primary keys.See JavaDoc Reference Page... with the AUTOGenerationType.AUTOenum constantIndicates that the persistence provider should pick an appropriate strategy for the particular database.See JavaDoc Reference Page... strategy:

AUTOGenerationType.AUTOenum constantIndicates that the persistence provider should pick an appropriate strategy for the particular database.See JavaDoc Reference Page... is the default strategy, so the following definition is equivalent:

During a commit the AUTOGenerationType.AUTOenum constantIndicates that the persistence provider should pick an appropriate strategy for the particular database.See JavaDoc Reference Page... strategy uses the global number generator to generate a primary key for every new entity object. These generated values are unique at the database level and are never recycled, as explained in the previous section.

The Identity Strategy

The IDENTITYGenerationType.IDENTITYenum constantIndicates that the persistence provider must assign primary keys for the entity using a database identity column.See JavaDoc Reference Page... strategy is very similar to the AUTOGenerationType.AUTOenum constantIndicates that the persistence provider should pick an appropriate strategy for the particular database.See JavaDoc Reference Page... strategy:

The IDENTITYGenerationType.IDENTITYenum constantIndicates that the persistence provider must assign primary keys for the entity using a database identity column.See JavaDoc Reference Page... strategy also generates an automatic value during commit for every new entity object. The difference is that a separate identity generator is managed per type hierarchy, so generated values are unique only per type hierarchy.

The Sequence Strategy

The sequence strategy consists of two parts - defining a named sequence and using the named sequence in one or more fields in one or more classes. The @SequenceGeneratorjavax.persistence.SequenceGeneratorJPA annotationDefines a primary key generator that may be referenced by name when a generator element is specified for the GeneratedValue annotation.See JavaDoc Reference Page... annotation is used to define a sequence and accepts a name, an initial value (the default is 1) and an allocation size (the default is 50). A sequence is global to the application and can be used by one or more fields in one or more classes. The SEQUENCEGenerationType.SEQUENCEenum constantIndicates that the persistence provider must assign primary keys for the entity using a database sequence.See JavaDoc Reference Page... strategy is used in the @GeneratedValuejavax.persistence.GeneratedValueJPA annotationProvides for the specification of generation strategies for the values of primary keys.See JavaDoc Reference Page... annotation to attach the given field to the previously defined named sequence:

Unlike AUTOGenerationType.AUTOenum constantIndicates that the persistence provider should pick an appropriate strategy for the particular database.See JavaDoc Reference Page... and IDENTITYGenerationType.IDENTITYenum constantIndicates that the persistence provider must assign primary keys for the entity using a database identity column.See JavaDoc Reference Page..., the SEQUENCEGenerationType.SEQUENCEenum constantIndicates that the persistence provider must assign primary keys for the entity using a database sequence.See JavaDoc Reference Page... strategy generates an automatic value as soon as a new entity object is persisted (i.e. before commit). This may be useful when the primary key value is needed earlier. To minimize round trips to the database server, IDs are allocated in groups. The number of IDs in each allocation is specified by the allocationSizeSequenceGenerator.allocationSizeannotation element(Optional) The amount to increment by when allocating sequence numbers from the sequence.See JavaDoc Reference Page... attribute. It is possible that some of the IDs in a given allocation will not be used. Therefore, this strategy does not guarantee there will be no gaps in sequence values.

The Table Strategy

The TABLEGenerationType.TABLEenum constantIndicates that the persistence provider must assign primary keys for the entity using an underlying database table to ensure uniqueness.See JavaDoc Reference Page... strategy is very similar to the SEQUENCEGenerationType.SEQUENCEenum constantIndicates that the persistence provider must assign primary keys for the entity using a database sequence.See JavaDoc Reference Page... strategy:

ORM-based JPA providers (such as Hibernate, TopLink, EclipseLink, OpenJPA, JPOX, etc.) simulate a sequence using a table to support this strategy. ObjectDB does not have tables, so the TABLEGenerationType.TABLEenum constantIndicates that the persistence provider must assign primary keys for the entity using an underlying database table to ensure uniqueness.See JavaDoc Reference Page... and SEQUENCEGenerationType.SEQUENCEenum constantIndicates that the persistence provider must assign primary keys for the entity using a database sequence.See JavaDoc Reference Page... strategies are almost identical.

A tiny difference is related to the initial value attribute. Whereas the SEQUENCEGenerationType.SEQUENCEenum constantIndicates that the persistence provider must assign primary keys for the entity using a database sequence.See JavaDoc Reference Page... strategy maintains the next sequence number to be used the TABLEGenerationType.TABLEenum constantIndicates that the persistence provider must assign primary keys for the entity using an underlying database table to ensure uniqueness.See JavaDoc Reference Page... strategy maintains the last value that was used. The implication for the initialValue attribute is that if you want sequence numbers to start with 1 in the TABLEGenerationType.TABLEenum constantIndicates that the persistence provider must assign primary keys for the entity using an underlying database table to ensure uniqueness.See JavaDoc Reference Page... strategy initialValue=0 has to be specified in the @SequenceGeneratorjavax.persistence.SequenceGeneratorJPA annotationDefines a primary key generator that may be referenced by name when a generator element is specified for the GeneratedValue annotation.See JavaDoc Reference Page... annotation.

Throughout this post, we will create the basis of an application called QuestionMarks. The idea is that this application will enable users to practice and enhance their knowledge by answering a set of multiple choice questions. To provide a better organization, these questions will be grouped in different exams. For example, there could be an exam called JavaScript Interview that would hold a set of JavaScript related questions to help users to prepare for interviews. Of course, in this article, we won't build the whole application as it would take a lot of time and would make the article huge, but we will be able to see the technologies aforementioned in action.

Before diving into integrating these technologies, let's first take a look at their definition.

What Is Spring Data JPA?

Spring Data JPA is the Spring module that adds support and extends JPA. JPA (which stands for Java Persistence API) is a Java specification for accessing, persisting, and managing data between Java objects/classes and relational databases (e.g. PostgreSQL, MySQL, SQLServer, etc). The process of mapping object-oriented entities to entity-relationship models is also know as ORM (Object-Relation Mapping) and JPA is the contract defined by the Java community to manage such mappings.

As JPA is just a specification, we will need an implementation to do the dirty work for us (creating the SQL queries). Hibernate is the most popular implementation of the JPA specification, and actually, the specification itself was created based on Hibernate. Besides that, when we import Spring Data JPA on Spring, we also get Hibernate by default. Therefore, there is no reason to search for another JPA implementation.

For the sake of completeness, here is a list of existing Hibernate alternatives: Oracle TopLink, Apache OpenJPA, DataNucleus, and ObjectDB.

What Is Liquibase?

Liquibase is a tool that helps developers to source control the database. In other words, with Liquibase we can keep our database schema and data synced with our Java entities. This is achieved by creating, in our Java project, files that contain changesets to be run on the database. These changesets are instructions to change/refactor the database. We will see Liquibase and its changesets in action in a while.

What Is PostgreSQL?

Probably PostgreSQL does not need presentations, but for those who don't know, here it is: PostgreSQL is a powerful, open source object-relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness.

As a database server, its primary functions are to store data securely and return that data in response to requests from other software applications. It can handle workloads ranging from small single-machine applications to large internet-facing applications with many concurrent users.

Launching a Dockerized PostgreSQL Instance

Since we are going to need a PostgreSQL server running locally to test the integration of the tools in question, Docker might come in handy. Instead of installing PostgreSQL directly in our machine, we will use Docker to make this database disposable. Like this, if we need a newer version of PostgreSQL, or if we need a completely different database server, we won't need to struggle to update or uninstall PostgreSQL.

Of course we will need Docker installed on our development machine, but the process of installing it is really simple ( for MacOS check this link, for Windows this link, and for Ubuntu this link) and opens a whole world of disposable, containerized services (e.g. PostgreSQL, MySQL, NGINX, NodeJS, etc).

After having Docker installed on our machine, we can issue the following command to run a dockerized instance of PostgreSQL:

The command above achieves the following:

  • Runs a Docker container that contains PostgreSQL and name it as .
  • Defines that the local port must be bridged to the same port in the container.
  • Defines that a database called must be created in the PostgreSQL instance.
  • Defines that the password for the user is .

In case we need to stop, restart, or remove this Docker container, we can issue the following commands:

Bootstrapping a Spring Boot App

Next step is to bootstrap a Spring Boot application. We have two alternatives equally easy to do that. The first one is to use the Spring Initilizr website provided by Pivotal. The second options is to clone this GitHub repository and check out the branch.

Bootstrapping With Spring Initilizr

If we choose to bootstrap our application with Spring Initilizr, we will need to fill the form available with the following values:

  • Generate a Gradle Project with Java and Spring Boot 1.5.6.
  • Project Metadata Group: com.
  • Project Metadata Artifact: questionmarks.
  • Selected Dependencies: let's leave this empty.

Note that although during this blog post we will use Gradle, we could easily use Maven instead. Let's just keep in mind that if we choose Maven Project the dependency configuration will be different. Besides that, the Spring Boot version does not need to be 1.5.6. The examples here must probably work with older and newer versions.

After that we just need to import the new Spring Boot project in our preferred IDE (Integrated Development Environment).

Bootstrapping With GitHub

If we choose to bootstrap our application by cloning the GitHub repository provided, we will need to issue the following commands:

The three commands above will give us the same result of using the Spring Initilizr website (i.e. if we fill the website's form with the values shown above). After that, we just need to import the new Spring Boot project in our preferred IDE (Integrated Development Environment).

Importing Dependencies

Now that we have our basic Spring Boot application set, we can change our dependency management tool configuration (Gradle) to import the libraries that we will use. To do that, let's open the file and change it as follows:

The changes made in this file added:

  • A compile only dependency to Project Lombok, which will make our code look cleaner.
  • A compile dependency to Spring Boot Web Starter, which facilitates the development of RESTful APIs and adds a transitive dependency to Java Bean Validation.
  • A compile dependency to Spring Data JPA, which gives us Hibernate.
  • A compile dependency to Liquibase, which will help us manage the database.
  • A runtime dependency to PostgreSQL JDBC Driver, which will enable Hibernate to communicate with the database.

Mapping Entities With JPA

As we already have all the dependencies properly set on our application, let's start creating the entities (classes) that we want JPA/Hibernate to manage. We will create five entities. The first one will be called and we will create it in a new package called with the following code:

This class, although quite small and simple, has some interesting characteristics. The main one, for those who are not familiar with Lombok, is the annotation. As explained by the documentation, is a convenient shortcut annotation that bundles the features of , , , and together. In other words, generates all the boilerplate that is normally associated with simple POJOs (Plain Old Java Objects) and beans: getters for all fields, setters for all non-final fields, and appropriate toString, equals and hashCode implementations that involve the fields of the class, and a constructor that initializes all final fields, as well as all non-final fields with no initializer that have been marked with , in order to ensure the field is never null.

Besides this somewhat magical annotation, we also:

  • Added to mark this class as an entity that will be managed by JPA/Hibernate.
  • Added to indicate that the property is the primary key of this entity.
  • Added with the strategy to indicate that the primary key value will be assigned by the persistence provider (i.e. PostgreSQL).
  • Added to both and properties to avoid persisting empty data for these fields.

Most of the annotations used in the entity are also going to be used in the other entities, as they provide the basis for JPA/Hibernate to function. The next entity that we are going to create will be called . Let's create this class inside the package with the following code:

Besides the annotations that we already covered while creating the entity, makes use of two new annotations:

  • indicates to JPA/Hibernate that Many questions can exist ToOne exam.
  • indicates that there will be a column called , in the table that supports , to reference the exam that owns this question.

This basically means that an exam will have many questions and that there will be a foreign key in the table that points to the . Soon we will create these tables and these relationships in our database with the help of Liquibase.

The third entity that we will map is . As we are developing an application that provides multiple choice questions, we need to map these choices (alternatives) to keep track of which is the right one and also which alternative the user chooses while answering a question. Let's create the class in the package with the following code:

This class doesn't use any new annotation or any new feature. It simply uses the annotation to avoid the boilerplate code, the JPA annotations to mark it as a managed entity with a primary key, and the along with to indicate that many alternatives may exist to a single question.

Both the and entities have two properties in common. A text ( ) property to hold the of the alternative/question, and a numerical ( ) order that defines on what order the alternative will be shown in the question, or the question will be shown in the exam. Besides that, has a property called to indicate if it is the correct answer or not.

The fourth entity that we will create is going to be . We will create this class in the package with the following code:

Contrastingly to the other entities that we have created, does not have a numerical ( ) id, but a textual ( ) primary key. Besides that, it doesn't have usual properties like email, name, and password. These unusual characteristics have an explanation. As we don't want to handle sensitive data and we don't want to waste valuable time trying to write secure authentication mechanisms, we are going to use Auth0 to manage user authentication for us. Therefore, all we will have to do is to persist a unique id that will be sent for us through JWTs (JSON Web Tokens).

This approach will free us to focus on the core functionality while we rest assured that one of the most critical parts of our application (security and authentication) will be supported by a great team of experts: Auth0!

Finally, the last entity that we will create will be called . This class will represent the attempts that users make to answer a question.

Again, nothing particularly new about the implementation of this class. The only perceptible difference for the other entities is that this one has two properties marked as . Since many attempts will be made by one, and that these attempts will refer to different , we annotated both properties with . Besides that we also created a property to hold when the attempt was made ( ) and created a property called to indicate if the user answered the question correctly or not. With these properties we will be able to, in the future, provide some nice charts and some intelligence to our users.

As this was the last entity that we needed to create, we can now focus on creating the database schema that will support our application. We will solve this question by using Liquibase.

Managing the Database Schema With Liquibase

To manage the database structure of our application and to keep it synced with the entities that compose our system, we will use Liquibase. What is great about this tool is that it supports a wide variety of languages to manage the schema. For example, we can define and refactor the database by using XML, YAML, JSON, and SQL formats. Furthermore, Spring Boot provides a great support for Liquibase as we will see in this section.

Enough said, let's focus on solving the problem. First of all, we need to configure the database connection on our Spring Boot application. Spring Boot will provide this configuration both for JPA/Hibernate and for Liquibase. The properties to communicate with the database will be set in the file:

The first property, , defines the address of our database. As we are running a dockerized PostgreSQL container and are bridging the default PostgreSQL port between our machine and the Docker container, we can reach the database by passing . The second property defines the user that will communicate with the database, in this case. The third property defines as the password for (the same that we passed when creating our dockerized PostgreSQL container). The last property defines the class as the driver responsible for handling the communication.

With these properties set, we can work on the Liquibase configuration. This will be an easy task, we are simply going to tell Liquibase to apply all the changesets available in a specific folder. To do that let's create a master Liquibase file called in the folder. We will probably need to create the folder and its child as they are not provided by Spring Boot. The master file will have the following content:

Note that the value provided is relative to , and therefore we will need to create a folder called inside . In this new folder we are going to create a new file called . This SQL file will contain the commands to create the tables that will support the entities of our application:

That was the last change that we needed to make in our application to make Liquibase responsible for running refactorings in our database. Running our application now, through the IDE or through the , will result in the following output:

Therefore, as we can see, everything worked as expected. Liquibase managed to apply the schema defined in the , and the application started successfully. This means that Spring Boot was able to run the application and that JPA/Hibernate found the tables needed to support our entities.

Aside: Securing Spring Boot Apps With Auth0

Securing Spring applications with Auth0 is very easy and brings a lot of great features to the table. With Auth0, we only have to write a few lines of code to get a solid identity management solution, including single sign-on, user management, support for social identity providers (like Facebook, GitHub, Twitter, etc.), enterprise (Active Directory, LDAP, SAML, etc.), and our own database of users.

To learn the best way to secure Spring Security API endpoints with Auth0, take a look at this tutorial. Besides providing tutorials for backend technologies (like Spring), the Auth0 Docs webpage also provides tutorials for Mobile/Native apps and Single-Page applications.

Next Steps: Defining a RESTful API and Querying the Database

So far, we have defined five entities that will hold the data that flows on our application. We also integrated PostgreSQL, with the help of Spring Data JPA, to persist this data and configured Liquibase to automatically run scripts that keep our database synced up with the entities structure. What we need now is to start defining the RESTful endpoints of our API that will support external clients (e.g. web application and iOS/Android mobile apps). This feature will be addressed in another article that will be released soon. Stay tuned!