Generator-jhipster: Issue in Postgress database with blob column

[outis10]

Hi, when I create a Entity with blob attribute I got an error on create entity

SQL Error: 0, SQLState: 42804
[ERROR] org.hibernate.engine.jdbc.spi.SqlExceptionHelper - ERROR: the column «archivo» 
is of type bytea but the expression is  oid type....

jhipster with Mysql runs fine

I replace @Lob with deprecated type
@Type(type="org.hibernate.type.BinaryType")

and work well but in this reference;
link to stackoverflow.com! I read a better aproach to do this but when I put the sentence
hibernate.jdbc.use_streams_for_binary=false in application-dev.yml
hibernate properties still won´t work

Thanks in advance

[andidev]

Okey so your suggestion is to change the annotation from @Lob to @Type(type="org.hibernate.type.BinaryType") for postgress database option?

[outis10]

Hi, I use these solution because it was the first that ran succefully. I know these is not a portable aproach. and solution is deprecated.

In that case my suggestion is to try solution these issue with a better aproach to maintan the portabilitiy

In another site I found that solution without modification of @Lob:

public class CustomPostgreSQLDialect extends PostgreSQLDialect {
        public CustomPostgreSQLDialect() {
           super();
          registerColumnType(Types.BLOB, "bytea");
        }
     }

And I try these

public class FixedH2Dialect extends H2Dialect {
    public FixedH2Dialect() {
        super();
        registerColumnType( Types.FLOAT, "real" );
        registerColumnType(Types.BLOB, "bytea");
    }
}

but again the app throws an exception.
PSQLException: Bad value for type long : \x6665636861496e ...

So I will be working with @Type solution for these proyect.

[jboenisch]

I solved this issue by using a custom dialect which also supports UUID:

public class PostgresUuidDialect extends PostgreSQL9Dialect {

    public PostgresUuidDialect() {
        super();

        registerColumnType(Types.BLOB, "bytea");
    }

    @Override
    public SqlTypeDescriptor remapSqlTypeDescriptor(SqlTypeDescriptor sqlTypeDescriptor) {
        if (sqlTypeDescriptor.getSqlType() == java.sql.Types.BLOB) {
            return BinaryTypeDescriptor.INSTANCE;
        }
        return super.remapSqlTypeDescriptor(sqlTypeDescriptor);
    }

    @Override
    public void contributeTypes(final TypeContributions typeContributions, final ServiceRegistry serviceRegistry) {
        super.contributeTypes(typeContributions, serviceRegistry);
        typeContributions.contributeType(new JHipsterPostgresUUIDType());
    }

    protected static class JHipsterPostgresUUIDType extends PostgresUUIDType {

        @Override
        public String getName() {
            return "uuid-jhipster";
        }

        @Override
        protected boolean registerUnderJavaType() {
            return true;
        }
    }
}

I used a similar solution to support UUID with H2:

public class H2UuidDialect extends H2Dialect {

    public H2UuidDialect() {
        super();
        registerColumnType(Types.FLOAT, "real");
    }

    @Override
    public void contributeTypes(final TypeContributions typeContributions, final ServiceRegistry serviceRegistry) {
        super.contributeTypes(typeContributions, serviceRegistry);
        typeContributions.contributeType(new JHipsterH2UUIDType());
    }

    protected static class JHipsterH2UUIDType extends AbstractSingleColumnStandardBasicType<UUID> {

        public BoLapsH2UUIDType() {
            super(VarcharTypeDescriptor.INSTANCE, UUIDTypeDescriptor.INSTANCE);
        }

        @Override
        public String getName() {
            return "uuid-jhipster";
        }

        @Override
        protected boolean registerUnderJavaType() {
            return true;
        }
    }
}

If you like the solution feel free to add it to the next release. Blobs work automatically, for UUID columns the @Type(type = "uuid-jhipster") must be used.

These new dialects must be used in the application*.yml configuration files.

[andidev]

So I can confirm this issue and also that it can be fixed as @jboenisch mentions by adding a custom PesgresSQLDialect

public class FixedPostgreSQL82Dialect extends PostgreSQL82Dialect {

    public FixedPostgreSQL82Dialect() {
        super();
        registerColumnType(Types.BLOB, "bytea");
    }

    @Override
    public SqlTypeDescriptor remapSqlTypeDescriptor(SqlTypeDescriptor sqlTypeDescriptor) {
        if (sqlTypeDescriptor.getSqlType() == java.sql.Types.BLOB) {
            return BinaryTypeDescriptor.INSTANCE;
        }
        return super.remapSqlTypeDescriptor(sqlTypeDescriptor);
    }
}

We have done this with the h2 datavase FixedH2Dialect dialect so I am guessing it is okay to the same with postgres. I'm going to do a PR this soon.

[tomvanrossom]

I think there is still an issue with storing and retrieving the entityValue of an EntityAuditEvent.

With the @Lob annotation, all my *IntTest are running fine with the FixedH2Dialect
Without the annotation on entityValue, all the integrations tests fail:

Wrong column type in *.PUBLIC.JHI_ENTITY_AUDIT_EVENT for column entity_value

Without the annotation running my server against Postgres-db with the FixedPostgreSQL82Dialect, the entityValue is stored and retrieve correctly.
But with the annotation, server and postgress, the entityValue contains only a number. Not a json string!

So H2 needs the @Lob, but Postgress can't handle :(

Any suggestion? Thanks!

Postgresql: 9.5.1.1; generator-jhipster: 2.27.2;