优化 10 列中的搜索并将其应用于 Spring Boot

Question

在我的交通应用程序中，有一个数据库表，其中包含有关电车站的信息。对于每个电车站，最多可以为其分配11个字符串(每个字符串在单独的列中(，这些字符串描述了有轨电车从平台离开的方向。

下面的查询说明了用户键入电车站名称和方向的情况，以便选择特定平台。该查询处理用户输入小写/仅拉丁字符，以便也找到ĄŚĆ用于asc输入。

SELECT id
FROM  stations_directions
WHERE 
((offline IS NULL) OR (offline <> 1)) 
AND 
(name = ?1) 
AND 
(
translate(lower(main_dir), 'ąćęłńóśźż', 'acelnoszz') = ?2 
OR 
translate(lower(add_dir1), 'ąćęłńóśźż', 'acelnoszz') = ?2 
OR 
translate(lower(add_dir2), 'ąćęłńóśźż', 'acelnoszz') = ?2 
OR 
translate(lower(add_dir3), 'ąćęłńóśźż', 'acelnoszz') = ?2 
OR 
translate(lower(add_dir4), 'ąćęłńóśźż', 'acelnoszz') = ?2 
OR 
translate(lower(add_dir5), 'ąćęłńóśźż', 'acelnoszz') = ?2 
OR 
translate(lower(add_dir6), 'ąćęłńóśźż', 'acelnoszz') = ?2 
OR 
translate(lower(add_dir7), 'ąćęłńóśźż', 'acelnoszz') = ?2 
OR 
translate(lower(add_dir8), 'ąćęłńóśźż', 'acelnoszz') = ?2 
OR 
translate(lower(add_dir9), 'ąćęłńóśźż', 'acelnoszz') = ?2    
OR 
translate(lower(add_dir10), 'ąćęłńóśźż', 'acelnoszz') = ?2
)    

如您所见，查询很长且重复。我正在寻找如何通过更改查询或数据库来优化它的方法。

另一个问题是如何在 Spring Boot JPA 中使用这样的查询？使用 JPQL 不可能将方法用作lower()。我尝试了EntityManager和createNativeQuery()但没有成功：

EntityManager em;
public String findId(String name, String direction, String sql) {
Query q = em.createNativeQuery(sql);
q.setParameter(1,name);
q.setParameter(2,direction);
return q.getSingleResult().toString();
}

Answer 1

你应该改用：

create extension unaccent;
create table stations (
station_id serial primary key,
name text not null,
offline boolean not null
);
create table station_directions (
station_id int not null references stations(station_id),
direction text not null,
primary_key(station_id, direction)
);

和一个查询：

select station_id from stations
join station_directions using station_id
where
name=?
and not offline
and lower(unaccent(direction))=?;