14

Locally I use mysql, so when I use 

 @Column(columnDefinition = "TEXT")

Unfortunately application have to run at different databases too, I've not been told which ones just "at all".

Is it possible to make something with similar effect, but working at most of databases? How can I achieve it?

akuzma
  • 1,592
  • 6
  • 22
  • 49

4 Answers4

19

What about something like this:

@Lob
private String someString;

I think it's more portable and should effectively generate TEXT or LONGTEXT data type.

Paweł Wyrwiński
  • 1,393
  • 9
  • 13
  • beaware, longtext can be up to 4GiB http://stackoverflow.com/questions/13932750/tinytext-text-mediumtext-and-longtext-maximum-storage-sizes – robie2011 May 08 '17 at 13:22
  • Also, please be aware that, on postgresql, `@Lob` may cause your database to store the data separately from the table as a large object; that may not necessarily be what you want. [See this example to understand what I mean](http://www.solewing.org/blog/2015/08/hibernate-postgresql-and-lob-string/) – Haroldo_OK Sep 04 '18 at 12:50
  • type `clob` is unsupported in ``PostgreSQL`` so you should do this: ```create domain clob as text;``` – Hamid Sep 01 '21 at 08:32
2

If you use @LOB you'll have ORA-00932 exception with SQL containing DISTINCT (http://ora-00932.ora-code.com/)

I think that the solution depends on the bytes size of your string. If its < 4000 bytes, you can try using @Column(name = "...", length = 4000)

I suggest 4000 bytes for DB comptibility reason :

  • In oracle Oracle 8 to Oracle 11g, type VARCHAR2 max size is 4000 (A4 page format)
  • In DB2 10 for z/OS, type VARCHAR max size is 32704
  • In SQL Server 2012, type TEXT max size is 2,147,483,647 bytes
Salim Hamidi
  • 20,731
  • 1
  • 26
  • 31
1

Similiar Problem I was getting error while uploading an image into the mysql database, @Column(columnDefinition = “TEXT”).
You can use in postgresql as well as mysql database's.

Just I've changed to

@Lob
@Column(name=imageFiie)
private String image;

Note: When working with PostgresSQL we may, periodically, need to store a string with an arbitrary length.

For this, PostgreSQL provides three character types:

  1. CHAR(n)
  2. VARCHAR(n)
  3. TEXT. Unfortunately, the TEXT type is not part of the types that are managed by the SQL standard. This means that if we want to use JPA annotations in our persistence entities, we may have a problem.

This is because the JPA specification makes use of the SQL standard. Consequently, it doesn't define a simple way to handle this type of object using, for example, a @Text annotation.

Luckily, we have a couple of possibilities for managing the TEXT data type for a PostgreSQL database:

We can use the @Lob annotation Alternatively, we can also use the @Column annotation, combined with the columnDefinition attribute

Prasenjit Mahato
  • 1,174
  • 15
  • 10
0

I had the same problem and it was resolved only adding a parameter to MySQL Url on application.properties:

spring.datasource.url=jdbc:mysql://localhost:3306/database?jdbcCompliantTruncation=false

The parameter is jdbcCompliantTruncation=false

Looks like a workaround but only it works...

EHoltz
  • 21
  • 5