如何在 Java Spring 中执行预构建查询

Question

我需要在java spring中执行预构建的SQL查询，我创建的查询如下，

String query = "select * from device where";

if (status != null) {

    query += " status = "+status;

}

if (cinema != "") {

    query += " and cinema_code = \'"+cinema+"\'";

}

if (content_profile != "") {

    query += " and content_profile = \'"+content_profile+"\'";

}

if (mac != "") {

    query += " and mac = \'"+mac+"\'";

}

构建查询：

select * 

from device 

where status = 2 

  and cinema_code = 'AL10' 

  and content_profile = 'signage'

Answer 1

您可以使用 Spring Data JPA 规范进行动态查询。

Answer 2

假设您已经配置了 Spring 数据源，您可以使用以下命令执行 Spring 本机查询：EntityManager em = emf.createEntityManager();List<Object> results = em.createNativeQuery(query);您还应该更新您的查询，因为当状态为空时您可以轻松获得SQLException。如果发生这种情况，您将得到一个无效的查询：select *from device&nbsp;where and cinema_code = 'AL10' and content_profile = 'signage'尝试使用这个初始查询："select * from device where 1=1 "使用上面的内容，无论是否执行第一个 if 或根本不执行任何 if，查询都将是正确的。

Answer 3

如果你不需要 JPA，你可以使用Spring JDBC执行查询：String query = "select * from device where status = 2 and cinema_code = 'AL10' and content_profile = 'signage'";List<Device> devices = jdbcTemplate.queryForObject(    query, new Object[] { }, new DeviceRowMapper());映射器可以如下所示：public class DeviceRowMapper implements RowMapper<Device> {    @Override    public Employee mapRow(ResultSet rs, int rowNum) throws SQLException {        Device device = new Device();        device.setId(rs.getInt("ID"));        ...        return device;    }}如何在提供url时配置连接然而正如评论中提到的。最好不要连接字符串参数。您的查询构建可以通过这种方式完成。String query = "select * from device where";List parameters =  new ArrayList();boolean wasParameter = false;if(status != null) {    query += " status = ? ";    parameters.add(status);    wasParameter = true;}if(cinema != "") {    query += (wasParameter ? " and ": "") +" cinema_code = ? ";    parameters.add(cinema);    wasParameter = true;}if(content_profile != "") {    query += (wasParameter ? " and ": "") +" content_profile = ? ";    parameters.add(content_profile);    wasParameter = true;}if(mac != "") {    query += (wasParameter ? " and ": "") +" mac = ? ";    parameters.add(mac);}Object[] array = parameters.toArray(new Object[0]);并执行查询：List<Device> devices = jdbcTemplate.queryForObject(    query, array, new DeviceRowMapper());