java - 如何搜索电影并更喜欢标题中包含搜索词的结果而不是描述中包含搜索词的结果

Question

我有以下内容来搜索电影

select * from movie where title like ? or cast like ? or director like ? or description like ?  order by votes desc limit 40 

如何按标题中包含搜索词的电影进行排序然后进行投票？

某事like order by termInTile, votes desc limit 40

String resultSql = "select * from movie where title like ? or cast like ? or director like ? or description like ?  order by votes desc limit 40 ";
statment = connect.prepareStatement(resultSql);
statment.setString(1, "%" + term + "%");
statment.setString(2, "%" + term + "%");
statment.setString(3, "%" + term + "%");
rs = statment.executeQuery();

while (rs.next()) {
    if (movies == null) {
        movies = new ArrayList<Movie>();
    }
    movies.add(getMovieFromRs(rs));
}

Answer 1

您可以使用 order by子句:

select *
from movie
where title like ? or cast like ? or director like ? or description like ? 
order by (title like ?) desc,
         votes desc
limit 40;

title like ?将返回一个 boolean 值，其中 1 表示真值。 desc将把这些放在第一位。请注意，调用查询时必须包含附加参数。或者使用命名参数，这样您就可以多次引用单个参数。