弹簧状子句

java jdbc spring

2022-09-03 09:32:33

我正在尝试使用MapSqlParameterSource来创建使用Like子句的查询。

代码是这样的。包含它的函数接收 nameParam：

String namecount = "SELECT count(*) FROM People WHERE LOWER(NAME) LIKE :pname ";

String finalName= "'%" +nameParam.toLowerCase().trim() + "%'";

MapSqlParameterSource namedParams= new MapSqlParameterSource();

namedParams.addValue("pname", finalName);

int count= this.namedParamJdbcTemplate.queryForInt(namecount, namedParams);

这不能正常工作，当我应该收到数千个结果时，给我0-10个结果。我基本上希望最终的查询看起来像这样：

SELECT count(*) FROM People WHERE LOWER(NAME) LIKE '%name%'

但这显然没有发生。任何帮助将不胜感激。

编辑：

我还尝试将'%'放在SQL中，例如

 String finalName= nameParam.toLowerCase().trim();

 String namecount = "SELECT count(*) FROM People WHERE LOWER(NAME) LIKE '%:pname%' "

;

但这也不起作用。

答案 1

您不希望在 finalName 字符串两边加上引号。使用命名参数，您无需指定它们。这应该有效：

String namecount = "SELECT count(*) FROM People WHERE LOWER(NAME) LIKE :pname ";
String finalName= "%" + nameParam.toLowerCase().trim() + "%";

MapSqlParameterSource namedParams= new MapSqlParameterSource();
namedParams.addValue("pname", finalName);

int count= this.namedParamJdbcTemplate.queryForInt(namecount, namedParams);

答案 2

这个解决方案对我有用。我把“%”放在 Object[] 参数列表上：

    String sqlCommand = "SELECT customer_id, customer_identifier_short, CONCAT(RTRIM(customer_identifier_a),' ', RTRIM(customer_identifier_b)) customerFullName "
        + " FROM Customer "
        + " WHERE customer_identifier_short LIKE ? OR customer_identifier_a LIKE ? "
        + " LIMIT 10";

List<Customer> customers = getJdbcTemplate().query(sqlCommand, new Object[] { query + "%", query + "%"}, new RowMapper<Customer>() {

    public Customer mapRow(ResultSet rs, int i) throws SQLException {

        Customer customer = new Customer();
        customer.setCustomerFullName(rs.getString("customerFullName"));
        customer.setCustomerIdentifier(rs.getString("customer_identifier_short"));
        customer.setCustomerID(rs.getInt("customer_id"));                   

        return customer;
    }
});

return customers;