How to Get the Most Out of PostgreSQL by Using Custom Hibernate Types
13 December 202125 April 2019 | Software
This article was written by Vlad Mihalcea, Java Champion and author of the Amazon bestseller High-Performance Java Persistence book. We are delighted to share his insights on getting the most out of PostgreSQL by using custom Hibernate Types as part of our continued partnership in enriching the skills of our Java development team.
Introduction
PostgreSQL is a very advanced relational database system, offering a wide range of column types to choose from.
Apart from the SQL standard types, such as numeric types (e.g. int
, bigint
) or string-based types (e.g. character
, character varying
), PostgreSQL supports many additional types, like inet
, hstore
or json
as well as array
, enum
or range
.
Although Hibernate has built-in support for many common types, allowing you to persist entity properties that map to the standard SQL types, a complex enterprise application that uses PostgreSQL might need to take advantage of all the aforementioned specialized types.
Fortunately, Hibernate allows you to build custom types, and many PostgreSQL-specific types are already supported by the hibernate-types open-source project.
In this article, we are going to see how we can map json
, hstore
, array
, inet
, range
, and PostgreSQL-specific enum
column types with Hibernate.
The Maven dependency
Being an open-source project, the hibernate-types
dependencies can be fetched automatically from the Maven Central repository.
Based on your current Hibernate version you need to choose one of the following dependencies.
For the Hibernate 5.4, 5.3, and 5.2 versions, you need to use the hibernate-types-52
Maven dependency artifact:
<dependency>
<groupId>com.vladmihalcea</groupId>
<artifactId>hibernate-types-52</artifactId>
<version>${hibernate-types.version}</version>
</dependency>
Hibernate 5.1, and 5.0 versions require you to use the hibernate-types-5
Maven dependency artifact:
<dependency>
<groupId>com.vladmihalcea</groupId>
<artifactId>hibernate-types-5</artifactId>
<version>${hibernate-types.version}</version>
</dependency>
For the Hibernate 4.3 version, you need to use the hibernate-types-43
Maven dependency artifact:
<dependency>
<groupId>com.vladmihalcea</groupId>
<artifactId>hibernate-types-43</artifactId>
<version>${hibernate-types.version}</version>
</dependency>
And for the Hibernate 4.2, and 4.1 versions, you need to use the hibernate-types-4
Maven dependency artifact:
<dependency>
<groupId>com.vladmihalcea</groupId>
<artifactId>hibernate-types-4</artifactId>
<version>${hibernate-types.version}</version>
</dependency>
So, as you can see, the hibernate-types
project supports a wide range of Hibernate versions.
Mapping PostgreSQL custom Hibernate Types
How to map JSON column types
Because JSON types are so widely used in current enterprise applications, the SQL:2016 standard added support for JSON column types.
To see how we can store JSON data using Hibernate, consider the following weather_forecast
database table.
Notice the type of the data
and location
columns is jsonb
.
Since the location
column value has a pre-defined internal structure, we can map it to the following Location
Java class:
public class Location implements Serializable {
private double longitude;
private double latitude;
public Location() {
}
public Location(
double longitude,
double latitude) {
this.longitude = longitude;
this.latitude = latitude;
}
public double getLongitude() {
return longitude;
}
public void setLongitude(double longitude) {
this.longitude = longitude;
}
public double getLatitude() {
return latitude;
}
@Override
public boolean equals(Object o) {
if (this == o)
return true;
if (o == null || getClass() != o.getClass())
return false;
Location that = (Location) o;
return Double.compare(that.longitude, longitude) == 0 &&
Double.compare(that.latitude, latitude) == 0;
}
@Override
public int hashCode() {
return Objects.hash(longitude, latitude);
}
}
However, for the data
column, we would like to use a String
on the Java side. This is because the internal structure is variable, and we don’t want to use a JsonNode
entity attribute since it’s not Serializable
and the associated JSON entity property is immutable.
Therefore, to map the weather_forecast
table as a JPA entity, we can use the following entity mapping:
@Entity(name = "WeatherForecast")
@Table(name = "weather_forecast")
@TypeDef(typeClass = JsonBinaryType.class, name = "jsonb")
public class WeatherForecast {
@Id
private Long id;
@Type(type = "jsonb")
@Column(columnDefinition = "jsonb")
private Location location;
@Type(type = "jsonb")
@Column(columnDefinition = "jsonb")
private String data;
public Long getId() {
return id;
}
public WeatherForecast setId(Long id) {
this.id = id;
return this;
}
public Location getLocation() {
return location;
}
public WeatherForecast setLocation(Location location) {
this.location = location;
return this;
}
public String getData() {
return data;
}
public WeatherForecast setData(String data) {
this.data = data;
return this;
}
}
Both the location
and data
entity attributes are mapped with the Hibernate-specific @Type
annotation which uses the JsonBinaryType
class offered by the hibernate-types
project.
What’s also worth noticing is that the setters use a Fluent-style API which makes the entity creation much more compact and readable as illustrated by the following code snippet:
entityManager.persist(
new WeatherForecast()
.setId(1L)
.setLocation(new Location(
-0.13,
51.51
))
.setData(
"{ " +
" \"id\":2643743," +
" \"name\":\"London\"," +
" \"weather\":[ " +
" { " +
" \"main\":\"Drizzle\"," +
" \"description\":\"light intensity drizzle\"," +
" }" +
" ]" +
"}"
)
);
When persisting the WeatherForecast
entity above, Hibernate generates the following SQL INSERT statement:
Query:["
INSERT INTO weather_forecast (
data,
location,
id
)
VALUES (
?,
?,
?
)
"],
Params:[(
{"id":2643743,"name":"London","weather":[{"id":300,"main":"Drizzle","description":"light intensity drizzle"}]},
{"longitude":-0.13,"latitude":51.51},
1
)]
And, when fetching the WeatherForecast
entity, we can see that the JSON columns are properly retrieved and the associated Java entity attributes are populated successfully.
WeatherForecast forecast = entityManager.find(
WeatherForecast.class,
1L
);
assertEquals(
-0.13,
forecast.getLocation().getLongitude(),
0.001
);
assertEquals(
51.51,
forecast.getLocation().getLatitude(),
0.001
);
assertTrue(
forecast.getData().contains(
"light intensity drizzle"
)
);
Therefore, the JsonBindaryType
supports both String
and custom object types on the Java side which are persisted as JSON in the database.
Besides the JsonBinaryType
, the hibernate-types
project supports a JsonStringType
for database systems that need the JSON object to be passed as a String at the JDBC level (e.g. MySQL).
How to map PostgreSQL hstore column types
Even before supporting JSON, PostgreSQL has been providing support for storing Map-based data via the hstore
column type.
To see how we can store a Map
of values using Hibernate, consider the following temperature_range
database table.
The data
column of the weather_forecast
table is of the hstore
type and the following example illustrates how to map the weather_forecast
table as a JPA entity.
@Entity(name = "WeatherForecast")
@Table(name = "weather_forecast")
@TypeDef(typeClass = PostgreSQLHStoreType.class, name = "hstore")
public static class WeatherForecast {
@Id
private Long id;
@Type(type = "hstore")
@Column(columnDefinition = "hstore")
private Map<String, String> data = new HashMap<>();
public Long getId() {
return id;
}
public WeatherForecast setId(Long id) {
this.id = id;
return this;
}
public Map<String, String> getData() {
return data;
}
public WeatherForecast setData(Map<String, String> data) {
this.data = data;
return this;
}
}
The @TypeDef
annotation registers the PostgreSQLHStoreType
which we can further reference using the Hibernate @Type
annotation.
When persisting the following WeatherForecast
entity:
Map<String, String> data = new HashMap<>();
data.put("temperature", "25");
data.put("sky", "cloudy");
entityManager.persist(
new WeatherForecast()
.setId(1L)
.setData(data)
);
Hibernate executes the SQL INSERT statement below:
Query:["
INSERT INTO weather_forecast (
data,
id
)
VALUES (
?,
?
)"],
Params:[("sky"=>"cloudy", "temperature"=>"25",
1
)]
When fetching the WeatherForecast
entity, we can see that the data
entity attribute is properly retrieved from the database:
WeatherForecast weatherForecast = entityManager.find(
WeatherForecast.class,
1L
);
assertEquals(
"25",
weatherForecast.getData().get("temperature")
);
assertEquals(
"cloudy",
weatherForecast.getData().get("sky")
);
The hstore
PostgreSQL column type defines a set of operators (e.g. ->
, =>
, ?
, ?&
, ?|
, @>
, <@
, and #=
) as illustrated by the following example which uses the ->
get value for key operator.
WeatherForecast weatherForecast = (WeatherForecast) entityManager
.createNativeQuery(
"select wf.* " +
"from weather_forecast wf " +
"where " +
" wf.data -> 'temperature' = :temp ", WeatherForecast.class)
.setParameter("temp", "25")
.getSingleResult();
assertNotNull(weatherForecast);
How to map array column types
Although not supported by all major relational database systems, PostgreSQL provides support for array column types.
To see how we can store an SQL array using Hibernate, consider the following temperature_readings
database table.
The temps
column is an array of integer values. The following example illustrates how to map the temperature_readings
as a JPA entity.
@Entity(name = "TemperatureReadings")
@Table(name = "temperature_readings")
@TypeDef(typeClass = IntArrayType.class, defaultForType = int[].class)
public class TemperatureReadings {
@Id
private Long id;
@Column(columnDefinition = "int[]")
private int[] temps;
public Long getId() {
return id;
}
public TemperatureReadings setId(Long id) {
this.id = id;
return this;
}
public int[] getTemps() {
return temps;
}
public TemperatureReadings setTemps(int[] temps) {
this.temps = temps;
return this;
}
}
The temps
entity attribute is persisted using the IntArrayType
provided by the hibernate-types
project. The defaultForType
attribute of the @TypeDef
annotation instructs Hibernate to use the IntArrayType
for every Java int[]
entity property.
When persisting the TemperatureReadings
entity below:
entityManager.persist(
new TemperatureReadings()
.setId(1L)
.setTemps(new int[] {17, 23, 27, 25, 21})
);
Hibernate will generate the following SQL INSERT statement:
Query:["
INSERT INTO temperature_readings (
temps,
id
)
VALUES (
?,
?
)
"],
Params:[(
{"17","23","27","25","21"},
1
)]
And, when fetching the TemperatureReadings
entity, we can see that the temps
Java array is properly populated from the associated table record:
TemperatureReadings readings = entityManager.find(
TemperatureReadings.class,
1L
);
assertArrayEquals(
new int[] {17, 23, 27, 25, 21},
readings.getTemps()
);
Besides the IntArrayType
, the hibernate-types
project supports a LongArrayType
, a StringArrayType
and an EnumArrayType
as well.
How to map PostgreSQL inet column types
When it comes to storing an IP address along with its subnet mask, there are several column options to choose from:
- a
VARCHAR(18)
column to store the String representation of the IP address; - 5
byte
ortinyint
columns to store the 4 bytes associated with the IP address plus one byte for the subnet mask; - a
bigint
column which could store all 5 bytes using bit shifting; - a database specific type, like the PostgreSQL
inet
orcidr.
Not only that the inet
PostgreSQL type is rather compact, requiring only 7 bytes, but we can also use IP-specific operators like <
(e.g. less than), >
(e.g. greater than), &&
(e.g. contains).
To see how we can store an IP address using Hibernate, consider the following weather_station
database table.
The ip
column is of the type inet
, and the following example illustrates how to map the weather_station
as a JPA entity.
@Entity(name = "WeatherStation")
@Table(name = "weather_station")
@TypeDef(typeClass = PostgreSQLInetType.class, defaultForType = Inet.class)
public class WeatherStation {
@Id
private Long id;
@Column(columnDefinition = "inet")
private Inet ip;
@Column(columnDefinition = "decimal")
private double temp;
public Long getId() {
return id;
}
public WeatherStation setId(Long id) {
this.id = id;
return this;
}
public Inet getIp() {
return ip;
}
public WeatherStation setIp(Inet ip) {
this.ip = ip;
return this;
}
public double getTemp() {
return temp;
}
public WeatherStation setTemp(double temp) {
this.temp = temp;
return this;
}
}
The ip
entity attribute is persisted using the PostgreSQLInetType
provided by the hibernate-types
project. The defaultForType
attribute of the @TypeDef
annotation instructs Hibernate to use the PostgreSQLInetType
for every Java Inet
entity property.
The Inet
class is also provided by hibernate-types
and looks as follows:
public class Inet implements Serializable {
private final String address;
public Inet(String address) {
this.address = address;
}
public String getAddress() {
return address;
}
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (o == null || getClass() != o.getClass()) return false;
return Objects.equals(address, Inet.class.cast(o).address);
}
@Override
public int hashCode() {
return Objects.hash(address);
}
public InetAddress toInetAddress() {
try {
String host = address.replaceAll("\\/.*$", "");
return Inet4Address.getByName(host);
} catch (UnknownHostException e) {
throw new IllegalStateException(e);
}
}
}
Now, when persisting the following WeatherStation
entity:
entityManager.persist(
new WeatherStation()
.setId(1L)
.setIp(new Inet("192.168.0.100/24"))
.setTemp(23.5)
);
Hibernate generates the following SQL INSERT statement:
Query:["
INSERT INTO weather_station (
ip,
temp,
id
)
VALUES (
?,
?,
?
)"],
Params:[(
192.168.0.100/24,
23.5,
1
)]
When fetching the WeatherStation
entity, we can see that the ip
entity attribute is properly retrieved from the database:
WeatherStation forecast = entityManager.find(
WeatherStation.class,
1L
);
assertEquals(
"192.168.0.100/24",
forecast.getIp().getAddress()
);
As previously explained, the main advantage of using an inet
column is that we can use PostgreSQL IP address specific operators, like the contains (e.g. &&
) operator to check if a given IP address is contained in the provided network address.
WeatherStation station = (WeatherStation) entityManager
.createNativeQuery(
"SELECT * " +
"FROM weather_station s " +
"WHERE " +
" s.ip && CAST(:ip AS inet) = true", WeatherStation.class)
.setParameter("ip", "192.168.0.1/24")
.getSingleResult();
assertEquals(
"192.168.0.100/24",
station.getIp().getAddress()
);
How to map PostgreSQL range column types
PostgreSQL provides built-in support for range types, which can be either bound or unbound.
To see how we can store a range of values using Hibernate, consider the following temperature_range
database table.
The degrees
column of the temperature_range
table is of the int4range
type, and the following example illustrates how to map the temperature_range
as a JPA entity.
@Entity(name = "TemperatureRange")
@Table(name = "temperature_range")
@TypeDef(typeClass = PostgreSQLRangeType.class, defaultForType = Range.class)
public class TemperatureRange {
@Id
private Long id;
@Column(columnDefinition = "int4range")
private Range<Integer> degrees;
public Long getId() {
return id;
}
public TemperatureRange setId(Long id) {
this.id = id;
return this;
}
public Range<Integer> getDegrees() {
return degrees;
}
public TemperatureRange setDegrees(Range<Integer> degrees) {
this.degrees = degrees;
return this;
}
}
The defaultForType
attribute of the @TypeDef
annotation instructs Hibernate to use the PostgreSQLRangeType
for every Java Range
entity property.
The Range
class is also provided by hibernate-types
, and it simplifies the creation and the usage of range types as demonstrated by the following example:
entityManager.persist(
new TemperatureRange()
.setId(1L)
.setDegrees(Range.closedOpen(10, 17))
);
When persisting the TemperatureRange
entity above, Hibernate generates the following SQL INSERT statement:
Query:["
INSERT INTO temperature_range (
degrees,
id
)
VALUES (
?,
?
)"],
Params:[(
[10,17),
1
)]
When fetching the TemperatureRange
entity, we can see that the degrees
entity attribute is properly retrieved from the database:
TemperatureRange temperatureRange = entityManager.find(
TemperatureRange.class,
1L
);
assertEquals(
Range.closedOpen(10, 17),
temperatureRange.getDegrees()
);
Apart from allowing us to define the upper and lower upper boundaries of the value interval, the range types provide range-specific PostgreSQL operators (e.g. &&
, <@
, @>
, <<
, >>
, -|-
, &<
, and &>
) as illustrated by the following example which uses the @>
contains operator.
TemperatureRange temperatureRange = (TemperatureRange) entityManager
.createNativeQuery(
"select tr.* " +
"from temperature_range tr " +
"where " +
" tr.degrees @> :temp ", TemperatureRange.class)
.setParameter("temp", 15)
.getSingleResult();
How to map PostgreSQL enum column types
The JPA EnumType
defines two strategies for persisting a Java enum:
ORDINAL
– the enum ordinal value is persisted in the associated database columnSTRING
– the enum name is persisted in the associated database column
The ORDINAL
persisting strategy is more compact since we can use a smallint
column to store the enum values. On the other hand, the ORDINAL
strategy is not very readable, especially for other applications that use the same database schema.
While the STRING
persisting strategy is readable, it’s not compact since it needs a character varying storage whose length is the one given by the longest enum value to be stored.
Luckily, PostgreSQL supports enum column types natively which occupy 4 bytes and are as readable as a String-based enum value.
The following example shows you how to create a PostgreSQL enum:
CREATE TYPE temperature_scale AS ENUM ('CELSIUS', 'FAHRENHEIT')
To see how we can use the temperature_scale
enum, consider the following thermometer
database table.
To map the TemperatureScale
Java Enum to the temperature_scale
PostgreSQL-specific enum type, we can use the PostgreSQLEnumType
from the hibernate-types
project.
@Entity(name = "Thermometer")
@Table(name = "thermometer")
@TypeDef(typeClass = PostgreSQLEnumType.class, name = "pgsql_enum")
public class Thermometer {
@Id
private Long id;
private double degrees;
@Enumerated(EnumType.STRING)
@Column(columnDefinition = "temperature_scale")
@Type(type = "pgsql_enum")
private TemperatureScale scale;
public Long getId() {
return id;
}
public Thermometer setId(Long id) {
this.id = id;
return this;
}
public double getDegrees() {
return degrees;
}
public Thermometer setDegrees(double degrees) {
this.degrees = degrees;
return this;
}
public TemperatureScale getScale() {
return scale;
}
public Thermometer setScale(TemperatureScale scale) {
this.scale = scale;
return this;
}
}
The TemperatureScale
enum which mirrors the temperature_scale
PostgreSQL-specific enum looks as follows:
public enum TemperatureScale {
CELSIUS,
FAHRENHEIT
}
This way, when persisting the Thermometer
entity:
entityManager.persist(
new Thermometer()
.setId(1L)
.setDegrees(23)
.setScale(TemperatureScale.CELSIUS)
);
Hibernate can use the String-based TemperatureScale
enum value. Even if on the database side, the column type is temperature_scale
:
Query:["
INSERT INTO thermometer (
degrees,
scale,
id)
VALUES (
?,
?,
?
)"],
Params:[(
23.0,
CELSIUS,
1
)]
And, when fetching the Temperature
entity, we can see that the scale
Java enum is properly populated:
Thermometer thermometer = entityManager.find(
Thermometer.class,
1L
);
assertSame(
TemperatureScale.CELSIUS,
thermometer.getScale()
);
Conclusion
If you are using PostgreSQL, you definitely take advantage of all these database-specific types that are both compact and provide advanced querying functionality.
The advantage of using Hibernate is that you can easily create database-specific Types that are not natively supported, and, most of the time, you might not even have to create your own types as the hibernate-types project already supports a great variety of such custom Hibernate types to choose from.
About the Author

Vlad Mihalcea
Vlad is a Java Champion, the creator of Hypersistence Optimizer, and a top contributor to the Hibernate project. He is passionate about enterprise systems, data access frameworks, and distributed systems. Vlad wrote hundreds of articles about Hibernate on his blog, and he has gold badges for the Java, Spring, JPA, and Hibernate tags on StackOverflow. He's also the author of the High-Performance Java Persistence book and video course series.