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
byteortinyintcolumns to store the 4 bytes associated with the IP address plus one byte for the subnet mask; - a
bigintcolumn which could store all 5 bytes using bit shifting; - a database specific type, like the PostgreSQL
inetorcidr.
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
