MySQL data type UNSIGNED INT is mapped to signed INT in Java

[FractalizeR]

MySQL has UNSIGNED modifier, which makes following type unsigned. Jooq generator maps such types still to int, which is signed in Java. Technically binary representation of both is still 4 bytes, but if converted to string, you might get different values.

Is that ok?

[Lukas Eder]

Good point. According to the MySQL documentation, virtually any
numeric type can be modified to be "unsigned":
http://dev.mysql.com/doc/refman/5.5/en/numeric-type-overview.html

In Java, the only unsigned numeric type is "char", which is an
unsigned "short" (or mediumint in MySQL). Of course, char's every-day
semantic is not really numeric. The only real potential of mapping the
concept of "unsigned numerics" from MySQL to Java is to choose the
next bigger datatype in Java. This means:

- tinyint maps to byte, whereas tinyint unsigned maps to short
- mediumint maps to short, whereas mediumint unsigned maps to int
- int maps to int, whereas int unsigned maps to long
- etc...

What do you think? At least, a 130 tinyint unsigned in MySQL would
find a proper representation in Java, instead of being overflowed to
-126

[FractalizeR]

Yes, I also thought about choosing next bigger datatype, but that brings some complications:

1. What would we do with UNSIGNED BIGINT? It's already 8 bytes long and unsigned. Expand it to float?
2. If we choose to go for next bigger datatype, I believe jooq will have to do checks when user issues queries so that you wouldn't be able to provide a value of 0xFFFF to tiny int column, because database behavior differs on such actions. MySQL usually sometimes just silently truncate the entered value to the nearest acceptable. And such checks introduce complications and unnecessary logic into jooq. They also slow down query generation.

I would suggest the following:

 - Store all values in Java native types in binary form equal to the one database uses. E.g. store 130 as ((byte) 0x82) or -126
 - Have jooq-generator store UNSIGNED flag in classes
 - On query generation the text representation of the field will be generated depending on this flag (so that (byte) 0x82) will become "130" in case of UNSIGNED=true and "-126" in case UNSIGNED=false).
 - Introduce helper functions to do conversion of unsigned numbers greater than their range into their lower range equivalents like

public class UnsignedTinyInt {
   public static byte parse(short value) {
      if(value > 0xFF || value < 0) {
         throw new OutOfRangeException(...);
      }
      return (byte) value;
   }
}

 - Indicate in documentation, that if you are dealing with unsigned database fields, you have to be VERY careful providing a value in Java and a link to helper classes documentation.

What do you think?

[Lukas Eder]

1. java.math.BigInteger...
2. Personally, I don't care too much about absolute type-safety,
because Java does not have that. In PL/SQL (or in XSD or many other
type systems) you can specify positive integers between 5 and 10. In
Java, you just... cannot. So the next bigger data type should be good
enough

2011/5/10 FractalizeR <Fract...@yandex.ru>:

[FractalizeR]

Well, ok. But such a move looks like a compatibility break. At least for me because everywhere in code I have userId INT UNSIGNED as an int type like jooq uses internally. It will also require to rewrite all manually created typed fields I suppose.

[Lukas Eder]

I understand compatibility concerns. But in the end, the best suited
solution should be chosen, even if that means some manual adaptations
in client code. On the other hand, I'm not sure about the best
solution...

1. Mapping to the next-higher type leads to incompatibility, such as
the one you're concerned with. Besides, it will be possible to try to
insert (short) 300 into a tinyint unsigned column, which will lead to
runtime issues. While this is a good solution for fetching data, it is
a bad one for insertion.

2. Not mapping at all leads to confusion, because Java does not
explicitly support unsigned types. While this is the most correct
solution, it's also the least user-friendly.

Maybe, option Nr. 2 is better and then I could add utility methods to
Record, Store and Result, in order to retrieve unsigned numbers
according to operations as documented here:
http://mindprod.com/jgloss/unsigned.html

A similar thing could be done for inserting data, as you described...

[FractalizeR]

Well, I vote for #2 ;)

I would feel myself more comfortable if types in jooq would remind those of database. I think manual entering data with id's over Integer.MAX_VALUE is a pretty rare case so users will not feel uncomfortable by using utility methods too often.

[Lukas Eder]

Hi Vladislav,

Unfortunately, I still don't have the perfect solution to this problem. While solution #2 seems the best option, I'm actually a bit reluctant of implementing such a thing myself. But there is more traction to this issue now, as I've had another user requesting the same thing:
https://github.com/lukaseder/jOOQ/issues/3

So I'm looking for a more robust solution #3. I'm hoping that there is actually a library implementing unsigned numbers extending java.lang.Number and wrapping all operations:
http://stackoverflow.com/questions/8193031/is-there-a-java-library-for-unsigned-number-types

So solution #3 would then be to introduce 4 new types in jOOQ that can correctly handle unsigned numbers: UByte, UShort, UInteger, ULong. Those numbers would be bound to PreparedStatement and read from ResultSet as String. When extending java.lang.Number, their signed equivalent can be obtained easily, using .byteValue(), .shortValue(), .intValue(), .longValue()

Let's see if such a library exists.

Cheers
Lukas

[Lukas Eder]

Discussions and implementation prototypes have made progress, so I'm
going to implement solution #3 involving new wrapper types. This
decision is documented here:
https://github.com/lukaseder/jOOQ/issues/3#issuecomment-2802645

The wrapper types will be open-sourced independently:
http://code.google.com/p/joou/

jOOU with U for Unsigned. There will be another funny face ;-)
I'm hoping to get arithmetic and bitwise operations implemented by
other contributors, not necessarily involved with jOOQ. See also this
question here:
http://stackoverflow.com/questions/8193031/is-there-a-java-library-for-unsigned-number-type-wrappers

Cheers
Lukas

2011/11/19 Lukas Eder <lukas...@gmail.com>:

[er...@heimdalldata.com]

Old thread, but relevant update on this--the problem in a more general sense is that MySQL's Java driver is upgrading the actual java type on an unsigned value to account for the higher range of values, BUT it is not changing the SQL type reported.  As such, it will convert an Unsigned Integer to Long, but the SQL type remains  type 4, or per Java to SQL standard "Integer".  This results in issues where the SQL type is interpreted, which includes in the Java class CachedRowSetImpl.  As such, this isn't just a JOOQ issue to resolve, but may touch many different packages that use the CachedRowSetImpl, which includes Spring I believe. 

[Lukas Eder]

Interesting, thanks for your update, Erik. I'm not sure if I caught the message: Does jOOQ still have this issue today? jOOQ doesn't use any of those JDBC extension types, including CachedRowSetImpl. Also, jOOQ doesn't use ResultSetMetaData, unless when using "plain SQL" queries. The code generator directly reverse engineers the dictionary views, such that type information is hard-wired to the generated schema classes...

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.