When working with PostgreSQL and Java Persistence API (JPA), you might encounter a common error related to JSON data types. This error often appears as:
PSQLException: column is of type json but the expression is of type character varying
This article explores why this error occurs, a common scenario that triggers it, and how to resolve it.
1. Why the Error Occurs
PostgreSQL supports JSON and JSONB data types, which allow us to store JSON-formatted data. However, type mismatches can occur when using JPA to interact with these JSON columns. This typically happens when there’s an attempt to insert or update JSON data using a data type that PostgreSQL does not expect.
1.1 Common Scenarios Triggering the Error
- Incorrect JPA Entity Mapping: When an entity field is not correctly mapped to the JSON column in the PostgreSQL table.
- Incorrect Query Parameters: When using JPQL or native queries, make sure the parameters are correctly set. For JSON columns, we would need to cast parameters appropriately.
2. Producing the Error
To understand how this PSQLException occurs, we will create a simple example using JPA. This section will walk us through producing the error.
Before diving into the error, ensure you have the following setup:
- JPA Entity: A simple entity class mapped to a PostgreSQL JSON column.
- Persistence Configuration: A
persistence.xmlfile configured to connect to your PostgreSQL database. - Main Class: A class that will trigger the error by attempting to save data incorrectly.
SQL Schema Example
CREATE TABLE user_profile (
id SERIAL PRIMARY KEY,
profile_data JSON
);
Example JPA Entity
Let’s define an entity that maps a JSON column in a PostgreSQL database:
@Entity
@Table(name = "user_profile")
public class UserProfile implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(name = "profile_data", columnDefinition = "json")
private String profileData;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getProfileData() {
return profileData;
}
public void setProfileData(String profileData) {
this.profileData = profileData;
}
}
In this example, the profileData field is mapped to a PostgreSQL JSON column.
Main Class to Trigger the Error
We will create a main class that attempts to persist a UserProfile object with incorrectly formatted data:
public class JsonTypeMismatchExample {
public static void main(String[] args) {
EntityManagerFactory emf = Persistence.createEntityManagerFactory("UserProfilePU");
EntityManager em = emf.createEntityManager();
em.getTransaction().begin();
// Create a UserProfile instance with invalid JSON data
UserProfile userProfile = new UserProfile();
String jsonData = "{\"name\":\"John Doe\",\"age\":30}";
userProfile.setProfileData(jsonData);
// Attempt to persist the UserProfile entity
em.persist(userProfile);
em.getTransaction().commit();
em.close();
emf.close();
}
}
In this example, the profileData field is set to a JSON string. When you run the JsonTypeMismatchExample class, JPA will attempt to persist the UserProfile entity. However, since the profile_data column in PostgreSQL expects JSON-formatted data and we’re providing a string, the following error will be produced:
This error message indicates that PostgreSQL expected a JSON object, but received a plain string instead. The mismatch between the expected data type (JSON) and the provided data type (string) triggers this PSQLException.
3. Fixing the Error
To fix the PSQLException error related to JSON type mismatches in PostgreSQL using JPA, we can leverage the @JdbcTypeCode annotation from the Hibernate, depending on your Hibernate version. In this example, we are using the Hibernate version 6.5.2.final.
3.1 Use the @JdbcTypeCode Annotation in Your Entity
Next, modify your entity class to use the @JdbcTypeCode annotation from the Hibernate core library to map the JSON column.
@Entity
@Table(name = "user_profile")
public class UserProfile implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@JdbcTypeCode(SqlTypes.JSON)
@Column(name = "profile_data", columnDefinition = "json")
private String profileData;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getProfileData() {
return profileData;
}
public void setProfileData(String profileData) {
this.profileData = profileData;
}
}
This approach ensures that JSON data is correctly mapped and handled between our Java application and the PostgreSQL database.
Note: we can also use the @Type annotation. First, add the Hibernate Types library dependency to your pom.xml file:
<!-- Hibernate Types -->
<dependency>
<groupId>com.vladmihalcea</groupId>
<artifactId>hibernate-types-60</artifactId>
<version>2.21.1</version>
</dependency>
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-databind</artifactId>
<version>2.17.2</version>
</dependency>
This dependency enables the @Type annotation to correctly map JSON fields in our entity to PostgreSQL JSON columns. Next, modify the entity class to use the @Type annotation like this:
@Type(JsonType.class)
@Column(name = "profile_data", columnDefinition = "json")
private String profileData;
4. Using the @Query Annotation with Native SQL
If we need to insert or update JSON data using a native SQL query using the @Query annotation, we need to cast the expression to match the expected data type. This ensures that the JSON data is correctly interpreted by PostgreSQL.
Here’s an example DAO class that uses the @Query annotation without typecasting:
public class UserProfileDAO {
@PersistenceContext
private EntityManager entityManager;
public void insertProfileData(String jsonData) {
EntityTransaction transaction = entityManager.getTransaction();
try {
transaction.begin();
String sql = "INSERT INTO user_profile (profile_data) VALUES (:jsonData)";
Query query = entityManager.createNativeQuery(sql);
query.setParameter("jsonData", jsonData);
query.executeUpdate();
transaction.commit();
} catch (Exception e) {
if (transaction.isActive()) {
transaction.rollback();
}
throw e;
}
}
public void setEntityManager(EntityManager entityManager) {
this.entityManager = entityManager;
}
}
public class JsonTypeMismatchExample {
public static void main(String[] args) {
EntityManagerFactory emf = Persistence.createEntityManagerFactory("UserProfilePU");
UserProfileDAO userProfileDao = new UserProfileDAO();
userProfileDao.setEntityManager(emf.createEntityManager());
// Example JSON data
String jsonData = "{\"name\":\"Phil Collins\",\"age\":40}";
// Insert a new profile (ID will be auto-generated)
userProfileDao.insertProfileData(jsonData);
System.out.println("Profile data inserted successfully.");
}
}
When we run the above code snippet, we will get the following exception:
ERROR: ERROR: column "profile_data" is of type json but expression is of type character varying Hint: You will need to rewrite or cast the expression. Position: 49 Exception in thread "main" org.hibernate.exception.SQLGrammarException: JDBC exception executing SQL [INSERT INTO user_profile (profile_data) VALUES (?)] [ERROR: column "profile_data" is of type json but expression is of type character varying Hint: You will need to rewrite or cast the expression.
To fix this error, we need to cast the string to the JSON type before inserting it like this:
String sql = "INSERT INTO user_profile (profile_data) VALUES (CAST (:jsonData AS JSON))";
In the above SQL statement – CAST(:jsonData AS JSON), the CAST function converts the jsonData parameter, which is initially treated as a string in SQL, into the JSON type before it’s inserted into the profile_data column. This casting is crucial for preventing the type PSQLException mismatch error.
When we run the code with the updated insert statement, we will see the following output, confirming that the data has been successfully inserted.
Hibernate: INSERT INTO user_profile (profile_data) VALUES (CAST (? AS JSON)) Profile data inserted successfully.
5. Conclusion
In this article, we explored how to resolve PostgreSQL JSON type mismatch errors in JPA, specifically the common issue of inserting JSON data into a JSON column. By using the CAST function and leveraging the @JdbcTypeCode from the Hibernate core library and the @Type annotation from the Hibernate-types library, we ensured that JSON data is correctly handled and stored.
6. Download the Source Code
This article explored JPA PostgreSQL JSON type mismatch errors.
You can download the full source code of this example here: jpa postgresql json type mismatch errors
