优化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))=?;