与JPA无关的数据库字符串比较 [英] Database independent string comparison with JPA

问题描述

我正在使用JPA(Hibernate作为提供程序)和基础MySQL数据库.

我有一张桌子，上面有德国各个街道的名称.每个街道名都有一个唯一的号码.对于一项任务，我必须找出名称的编号.为此，我编写了一个JPQL查询，如下所示

"SELECT s FROM Strassencode s WHERE s.name = :name"

不幸的是，德国的街道名称只有小写和大写字母不同，例如"Am kleinen Feld"和"Am Kleinen Feld".显然其中一个是语法错误，但是作为街道名称，两个拼写都是允许的，而且两者的编号不同.

当我将JPQL查询发送到数据库时，我正在使用

query.getSingleResult();

由于这个事实，表中的每个街道名称都是唯一的.但是使用底层的MySQL数据库，我会得到一个NonUniqueResultException，因为默认情况下，一个mySQL数据库正在进行不区分大小写的比较.强制使用mySQL的常见方法是编写类似

的查询

SELECT 'abc' LIKE BINARY 'ABC';

如《 mySQL参考手册》第11.5.1章所述，但JPQL中没有相应的关键字.

我的第一次尝试是在班级中用

query.getSingleResult();

注释字段name

@Column(nullable = false, columnDefinition = "varbinary(128)")

像这样注释，数据库自动进行区分大小写的比较，因为其中一个字符串是二进制的.但是，当我使用这种解决方案时，当我从数据库中读取名称并将它们写入文件时会遇到麻烦，因为那样会误解ä，ö，ü之类的字母.

我知道，字段name应该具有uniqueConstraint，这是我的目标之一，但这只有在数据库将进行区分大小写的字符串比较的情况下才有可能.

有没有解决方案，我可以配置JPA进行区分大小写的字符串比较，而不必手动微调数据库?

解决方案

似乎无法配置JPA来解决此问题.但是我发现，不仅可以在表级别设置排序规则，还可以按照

但是，谢谢您的帮助

I'm working with JPA (Hibernate as provider) and an underlying MySQL database.

I have a table containing every name of streets in Germany. Every streetname has a unique number. For one task I have to find out the number of the name. To do this I wrote a JPQL-Query like the following

"SELECT s FROM Strassencode s WHERE s.name = :name"

Unfortunately there are streetnames in Germany which only differ in small and capital letters like "Am kleinen Feld" and "Am Kleinen Feld". Obviously one of them is gramatical wrong, but as a name for a street, both spellings are allowed and both of them have a different number.

When I send my JPQL-Query to the database, I'm using

query.getSingleResult();

because of the fact, that every streetname in the table is unique. But with the underlying MySQL-Database, I'll get a NonUniqueResultException, because a mySQL database is doing a case insensitive comparison by default. The common way to force mySQL is to write a query like

SELECT 'abc' LIKE BINARY 'ABC';

as discribed in chapter 11.5.1 in the mySQL Reference Manual, but there is no corresponding keyword in JPQL.

My first attempt was to annotade the field name in the class with

@Column(nullable = false, columnDefinition = "varbinary(128)")

Annotated like this, the database is automatically doing a case sensitive comparison, because one of the strings is binary. But when I use this solution, I'm running into troubles when I'm reading the names out of the databse, to write them into a file, because letters like ä, ö, ü are interpreted wrong then.

I know, that the field name should get a uniqueConstraint and it's one of my goals to do so, but this is only possible if the database will do a case sensitive string comparison.

Is there a solution, where I can configure JPA to do case sensitive string comparison without having to manually fine-tune the database?

解决方案

Seems like there is no way in configuring JPA to solve this problem. But what I found is, that it is possible to set the collation not only on the table-level, you can also set it for the whole database as discribed in the Reference Manual 12.1.10 CREATE DATABASE SYNTAX and 9.1.3.2. Database Character Set and Collation

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_specification] ...

create_specification:
    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name

I only had to create the database with:

CREATE DATABASE db_name CHARACTER SET latin1 COLLATE latin1_general_cs;

With this, I could put a uniqueConstraint on the field name and insert both "Am kleinen Feld" and "Am Kleinen Feld" and when I query for one of them, I'll only receive one.

However, thanks for the help

这篇关于与JPA无关的数据库字符串比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！