annotation to explicitly select a "long string" or "text" column type

[gavinking]

I believe it would be worth introducing a @Long or @LongString or @MaxLength annotation or something like that to make it easy for users to force the column type to text or varchar(max) or whatever, without the use of @Lob or JDBC-level CLOB APIs. On most dialects today, @Column(length=99999999) probably works, but it's inelegant, and likely doesn't work very consistently. And this seems like it's probably a common-enough thing to want.

[yrodiere]

Out of curiosity, what would be the equivalent in XML mapping?

I'm asking because we would have had a use for something similar in Hibernate Search, where we are currently forced to use XML mapping (the only solution for entities added by a third-party library on startup, without user configuration). We would have been able to reference a type with a name, but we wouldn't be able to use an annotation.

Though the use case in Hibernate Search is similar, but not exactly the same: we needed a binary type rather than a text type. We gave up and just used blob because support for binary types of undefined length without explicit indirection (non-blob) is rather inconsistent and sometimes even deprecated (DB2's LONGVARBINARY). But it seems to work well in at least some databases: there's bytea for postgresql, and I think something similar in a few other SGBDs.

[gavinking]

Alternatively, I was wondering if we could provide the feature through a JDBC type.

It's certainly an option. I figured the dedicated annotation would be a bit more comfortable for users, but I'm not attached to it. @JdbcType(LongStringJdbcType.class) isn't terrible. And yeah, they're not mutually exclusive.

Dunno, I don't have strong opinion.

[sebersole]

we would annotate it with @JdbcType(LongStringJdbcType.class)

XML has no facility for specifying JdbcType nor JavaType. So same problem really

[sebersole]

But we could surely add a new attribute to the XML mapping format, right?

For hbm.xml (which is considered deprecated btw) yes. For orm.xml, obviously no unless we start offering a JPA XSD extension (which is actually the plan anyway.

[gavinking]

So same problem really

Ah, OK.

[sebersole]

But we could surely add a new attribute to the XML mapping format, right?

For hbm.xml (which is considered deprecated btw) yes. For orm.xml, obviously no unless we start offering a JPA XSD extension (which is actually the plan anyway.

If we do decide to add a corollary to @LongString (or whatever) to XML mappings, then I think we need to go back and add corollaries for all of the annotations not yet supported there - and there are a lot of them.

[gavinking]

To make any sorta progress on this one we need to compile a list of the "text" or "long varchar" types for the main platforms. This is nontrivial, since the databases are very divergent in this area.

Tell me if these are right/wrong:

• Postgres: text
• MySQL: text???? that seems a bit arbitrary...
• SQL Server: varchar(MAX)
• Sybase: text
• DB2: varchar(32704) ???
• Oracle: VARCHAR2(4000) ???? (long is deprecated)
• H2: varchar(1048576) ??
• Spanner: string(max)?
• Cockroach: string

WDYT?

[gavinking]

Also, I'm not a fan of using "magic numbers".

Sure, I always avoid (perhaps irrationally) the use of MAX_VALUE to mean infinity.

But that's not what this is. Integer.MAX_VALUE is the literal maximum length of a string in Java, and the maximum length of a longtext on MySQL. It's not a number we're pulling out of our butts. It's the real actual physical limit.

[sebersole]

Also, I'm not a fan of using "magic numbers".

I literally started that suggestion with "not a huge fan of this.. but considering we (may) want XML compatibilty, what about a "magic number"" lol

Literally no-one is a "fan" of magic numbers ;)

[sebersole]

Because on some platforms (MySQL) there is a distinction between strings whose length fits in a 16-bit integer and those which don't. And since all Java Strings fit in a 32-bit integer, we never have to contemplate anything longer (and the same goes for most of the databases).

I assume the assumption is that these define a minimum size and we can up-size as needed, then fine.

I do still consider the XML case since @yrodiere brought it up. hbm.xml is simple - its a String and we can support any "magic" value we want there, e.g.

<column ... length="max"/>
<column ... length="16bit"/>
<column ... length="32bit"/>

But that still leaves the question of orm.xml - whether we want to allow this kind of distinction there and, if so, how we can have users indicate this via a simple int value. And I think we all agree that hand-coding the actual value of those constants is not user-friendly at best.

But like @gavinking said, these options are not necessarily mutually exclusive

[gavinking]

these options are not necessarily mutually exclusive

Right, I mean LongVarbinaryJdbcType already exists in H6, and after cleaning up the dialect support for LONGVARCHAR it will also work nicely cross platforms. (In H5 some dialects didn't even have sensible mappings for LONGVARCHAR IIRC.)

So @JdbcType(LongVarbinaryJdbcType.class) is one option that annotation users have.

However it seems to me that, @Column(length=LONG32) is nicer in certain ways.

In XML, we can interpret -1 or 32bit or whatever as meaning the same thing. That's just a question for the XML binder.

[gavinking]

I forgot: @JdbcTypeCode( Types.LONGVARCHAR ) is also a perfectly good option, and works fine with the work I've already done.

[sebersole]

@gavinking I agree that this is not "terrible":

@Column(length = VarcharJdbcType.MAX)
String text;

But I do think it somewhat defeats the purpose of the suggestion which is to allow seamless functionality with XML mappings. As you even pointed out, hand-coding that value in XML is error prone and, well, fugly.

as one would have to spell out Integer.MAX_VALUE

Yep ^^

questions for readers of the mapping i.e. Why this value?

I mean, that's the general point wrt magic numbers. Nothing really unique about it here

The workaround for people with orm.xml is: pick a really big number, say length='50000'. That's not terrible either.

Which is why I reversed course earlier and went with a negative number.

doesn't read like any sort of hack to me.

I wouldn't say hack... but how do users mapping with XML achieve this?

[gavinking]

We could also allow a negative number.

[sebersole]

Do we allow 2? One for 16 and one for 32 bit representations?

😁

[gavinking]

ffff, I mean, in principle yeah but.... ugh

[gavinking]

I mean, length='0' and length='-1'. :-/

[sebersole]

We already have a way to denote such types and it is the use of the JDBC types LONGVARBINARY and LONGVARCHAR

In many ways I like and agree with this, but 2 concerns:

1. this is viable for Hibernate-specific mappings (hbm.xml) and Hibernate-specific annotations. however, neither JPA annotations nor orm.xml allow specifying a JDBC type code. so how does this work in those cases?
2. in a sense it is confusing when I specify LONGVARCHAR e.g. and use a database that does in fact have an actual corresponding datatype (such as Oracle), but Hibernate decides to use a different datatype. not saying wrong per-se, but "surprising" from the user perspective.

[sebersole]

this is viable for Hibernate-specific mappings (hbm.xml) and Hibernate-specific annotations. however, neither JPA annotations nor orm.xml allow specifying a JDBC type code. so how does this work in those cases?

To play my own devil's advocate, we could look at this like "JPA does not define this capability, so we should not really care about this in strict JPA mapping". But that is a bit of a straw-man argument considering we generally push users to prefer annotations (most of which are JPA defined) over XML...

[sebersole]

All told, I say this... they all make sense in their own right, each with pros and cons. I think it makes sense to support multiple of them.

For annotation mappers, who are willing to use Hibernate annotations, I think @JdbcTypeCode( Types.LONGVARCHAR ) is a great option that requires no real work on our part other than to change up some Dialects in terms of which JdbcType they register for that code. I also think any of these are great:

@Long
String theString;

@Medium
String theString;

@Long( MAX )
String theString;

@Long( MEDIUM )
String theString;

@Long( BIT16 )
String theString;

@Long( BIT32 )
String theString;

They all really describe the same thing. max/medium versus 32bit/16bit really target on different levels of user.

---

For hbm.xml mappers, I don't much care if we recognize "max", "-1", "32bit", etc. It's really all just different shades of lipstick for the pig.

---

For pure JPA mappers (same for annotations and orm.xml since their DTD defines this as int explicitly) - I think we have to support magic numbers or something like the suggested Length constants (or both). It has to be int-based so that is pretty limiting in terms of possible solutions. I will say however that users who are unwilling to use Hibernate annotations are probably even more unwilling to use a Hibernate constant just to avoid using a Hibernate annotation. Granted, we could document the effect of using Short.MAX_VALUE and Integer.MAX_VALUE in the user-guide and call it a day..

[gavinking]

I think it makes sense to support multiple of them.

Right, I reached the exact same conclusion.

[gavinking]

I will say however that users who are unwilling to use Hibernate annotations are probably even more unwilling to use a Hibernate constant just to avoid using a Hibernate annotation.

Right, but those people can easily just copy the Length class into their own project.

Granted, we could document the effect of using Short.MAX_VALUE and Integer.MAX_VALUE in the user-guide and call it a day.

Sure, a big part of this is documenting stuff so that people stop abusing @Lob.

[sebersole]

Right, but those people can easily just copy the Length class into their own project.

So they don't want to use Hibernate annotations; they don't want to use Hibernate classes; but they would be willing to copy a Hibernate class into their application for a feature that only Hibernate supports?

[gavinking]

Believe me, that is exactly the sort of slightly-irrational thing people are often prepared to do. :-)

[sebersole]

Sadly, you are probably not too far off 🤣

[sebersole]

Sure, a big part of this is documenting stuff so that people stop abusing @lob.

+10000