Use MySQL Sort in Java

Question

Short version

I am currently studying a problem with MySQL mappings and how they affect a set of values ​​(which is displayed using Hibernate, but it doesn't matter at the moment). I want to have a rowset using the same sort as MySQL. For example. I want foobar and fööbar to be considered equal, but foo bar and foobar to be considered different. Using the default Collator.getInstance () (with Collator.PRIMARY strength) does not work reliably, since there are still differences (primarily spaces). So, how do you get a Collator that behaves the same as MySQL for every possible row?

Long version

I want to have a unique index in the table where I store Set values ​​and make sure that Set contains only the values ​​that are allowed in DB and vice versa.

The table looks like this:

CREATE TABLE `MY_SET` (
  `entity_id` int  NOT NULL,
  `value` varchar(255)  NOT NULL,
  UNIQUE `entity-value`(`entity_id`, `value`)
) ENGINE = InnoDB DEFAULT CHARSET=latin1 DEFAULT COLLATION=;

Now, if I use simple strings and a HashSet to store my values, for example. how in

public class MyValues {
  private MyEntity _myEntity;
  private final HashSet<String> _values = new HashSet<String>();
}

One could add both the foobar set and the fööbar set to the set of values. Now, if Hibernate flushes Set to DB, MySQL will complain about duplicates of "foobar" and "fööbar" for a particular entity-value key. So I thought that I was completing the lines and using Collator to check the lines for equality:

public class MyValues {
  private MyEntity _entity;
  private final HashSet<CollatedString> _values = new HashSet<CollatedString>();
}

public static class CollatedString {
  private String _string;
  private CollationKey _key;

  public String getString() {
   return _string;
  }

  public void setString(final String string) {
   _string = string;
   _key = getCollator().getCollationKey(_string);
  }

  @Override
  public int hashCode() {
   return _key.hashCode();
  }

  @Override
  public boolean equals(final Object obj) {
   if (!(obj instanceof CollatedString)) {
    return false;
   }
   return _key.equals(((CollatedString) obj)._key);
  }

}

This works well for "foobar" and "fööbar":

final MyEntity e = new MyEntity();
final MyValues v = new MyValues();
v.setEntity(e);
v.getValues().add(new CollatedString("foobar"));
v.getValues().add(new CollatedString("fööbar"));
System.out.println("1 == " + v.getValues().size()); // prints 1 == 1

But it does not work for "foo bar" and "foobar", which MySQL considers different:

v.getValues().add(new CollatedString("foobar"));
v.getValues().add(new CollatedString("foo bar"));
System.out.println("2 == " + v.getValues().size()); // prints 2 == 1 (which is wrong)

, , getCollator():

public static final Collator getCollator() {
  // FIXME please help!
}

:

Answer 1

- . Collators , , - . . , / MySQL (/usr/share/mysql/charsets Ubuntu). RuleBasedCollator , , ( MySQL - , ) .