Skip to main content

Hibernate & Postgresql

If you are using Hibernate 3.5 or above to talk to Postgresql database, have you ever tried to store a byte array?

Let's take an example.

Here is the mapping which will store and read byte[] from the database.
@Lob
@Column(name = "image")
private byte[] image;

Here is the JPA mapping file configuration.
<persistence version="2.0" 
    xmlns="http://java.sun.com/xml/ns/persistence" 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">

  <persistence-unit name="testPU" transaction-type="JTA">
    <provider>org.hibernate.ejb.HibernatePersistence</provider>
    <jta-data-source>test</jta-data-source>
    <properties>
    </properties>
  </persistence-unit>

</persistence>

When you try to save your entity you will get the following exception.
org.postgresql.util.PSQLException: ERROR: column "image" is of type bytea but expression is of type oid
  Hint: You will need to rewrite or cast the expression.

This is because Postgresql handles large objects differently for performance reasons. You can find more details on how Postgresql handles large objects at here and here.

If you dont have time to read all these, here is solution.

Step-1. Add the following to the persistence.xml file
<properties>
    <property name="hibernate.jdbc.use_streams_for_binary" value="true"/>

</properties>
Step-2. Remove the @Lob annotation from you Entity class.
This is important as not removing this will give you the same exception as before.

Software used:

  • hibernate-release-4.1.9.Final
  • Postgreql 9.1

You can find more solutions and details at the following links:
  1. http://stackoverflow.com/questions/4488693/hibernate-postgresql-column-x-is-of-type-oid-but-expression-is-of-type-byte
  2. http://virgo47.wordpress.com/2008/06/13/jpa-postgresql-and-bytea-vs-oid-type/
  3. http://in.relation.to/15492.lace
  4. https://hibernate.atlassian.net/browse/HHH-4876



Labels:

Comments

Post a Comment

Popular posts from this blog

Using Nginx as proxy server for Keycloak

I have used Keycloak  in its very early stage ( when it is was in 2.x version). But now it has come a long way (at this time of writing it is in 21.x) In this article let's configure Keycloak behind Nginx. Here are the points to consider.  If you want to configure Apache2 as a proxy server for your java application, please check  this article . We are going to use a domain name other than localhost Anything other than localhost will require Keycloak to run in production mode which requires SSL configurations etc. Or it requires a proxy server. Lets begin. Requirements Keycloak distribution Ubuntu 22.04 server Configuring Keycloak 1. Download Keycloak from here . 2. Extract it using tar -xvzf  keycloak-21.0.1.tar.gz 3. Create a script file called keycloak.sh with the following contents #!/bin/bash export KEYCLOAK_ADMIN=<admin-username-here> export KEYCLOAK_ADMIN_PASSWORD=<admin-password-here> nohup keycloak-21.0.0/bin/kc.sh start-dev --proxy edge --hos...
1 comment
Read more

Installing GoDaddy certificate in Wildfly/Keycloak

In the previous post we saw how to set up Keycloak . Here we will see how to generate and install GoDaddy.com certificate in Keycloak. The steps are similar for Wildfly as well. Step 1: Generate CSR file Run the following commands in your terminal. <mydomain.com> has to be replaced with your actual domain name. keytool -genkey -alias mydomain_com -keyalg RSA -keysize 2048 -keystore mydomain_com.jks keytool -certreq -alias mydomain_com -file mydomain_com.csr -keystore mydomain_com.jks Step 2: Generate certificate Upload  mydomain_com . csr  file content into GoDaddy.com, generate and download certificate for tomcat server (steps to generating SSL certificate is beyond the scope of this article). If you unzip the file, you will see the following files. gd_bundle-g2-g1.crt ..5f8c...3a89.crt   #some file with alphanumeric name gdig2.crt Files 1 and 2 are of our interest. Third file is not required. Step 3: Import certificate to key...
3 comments
Read more

HTTP/2 Java Server using Embedded Jetty - Part 1

Have you ever wanted to configure internal Java servers using HTTP/2 (or H2 in short), because of the following advantages? Internal clients making concurrent calls, can now use a single connection by utilizing the multiplexing nature of HTTP/2. Server will/can use less threads as there are less connections overall. Servers can also push resources/data and can communicate using websockets But what if you don’t want to go through tedious process of setting up SSL/TLS certificates? HTTP/2 insists on SSL/TLS as no browser supports HTTP/2 on insecure mode. Or what if you want to save every bit of CPU and time by skipping encryption and decryption? You are at the right place. Since everyone nowadays is moving towards (or talking about) micro services, we will also configure Jetty HTTP/2 server in embedded mode. What more? We are also going to configure our server to use Asynchronous Servlets and Asynchronous IO (aka non-blocking IO or NIO) in Part-2 of this article. ...
1 comment
Read more

How to retry a method call in Spring or Quarkus?

Have you ever come across a situation where you wanted to retry a method invocation automatically? Let's say you are calling a stock ticker service for a given stock and get a transient error. Since it is a transient error, you will try again and it may work in second attempt. But what if it doesn't? Well, you will try third time. But how many times can you try like that? More importantly after how much time will you retry? Imagine if you have a handful of methods like this. Your code will become convoluted with retry logic. Is there a better way? Well, if you are using spring/spring boot, you are in luck. Here is how you can do that using spring. Let's write our business service as follows. import java.time.LocalDateTime; import java.util.concurrent.CompletableFuture; import lombok.extern.slf4j.Slf4j; import org.springframework.retry.annotation.Backoff; import org.springframework.retry.annotation.Retryable; import org.springframework.scheduling.annotation.Async; import...
Post a Comment
Read more