使用JDBC来编写一个查询
问题描述:
我与JDBC和PostgreSQL的工作,我必须做到以下几点:使用JDBC来编写一个查询
/*
* Return list of film titles whose length is equal to or greater
* than the minimum length, and less than or equal to the maximum
* length.
*/
public List<String> getFilmTitlesBasedOnLengthRange(Connection connection,
int minLength, int maxLength) {
List<String> result = new LinkedList<String>();
return result;
}
我试图通过编写PostgreSQL的(不知道的查询首先要解决此问题,如果它甚至正确):
SELECT title
FROM dv_film A, dv_film B
WHERE (A.length >= (COUNT(length) ASC LIMIT 1)) AND (B.length >= (COUNT(length) DESC LIMIT 1));
的dv_film关系是:
CREATE TABLE dv_film (
film_id integer,
title varchar(50),
description text,
length smallint,
rating mpaa_rating
);
我在试图解决这个问题的尝试是:
public List<String> getFilmTitlesBasedOnLengthRange(Connection connection,
int minLength, int maxLength) {
List<String> result = new LinkedList<String>();
PreparedStatement st = conn.prepareStatement("SELECT title FROM dv_film A, dv_film B WHERE (? >= (COUNT(length) ASC LIMIT 1)) AND (? >= (COUNT(length) DESC LIMIT 1));");
return result;
}
答
您的语句:
PreparedStatement st = conn.prepareStatement("SELECT title FROM dv_film A, dv_film B WHERE (? >= (COUNT(length) ASC LIMIT 1)) AND (? >= (COUNT(length) DESC LIMIT 1));");
在这里,您试图列名传递给PreparedStatement和不能设置列名的PreparedStatement值。您只能将 列值设置为PreparedStatement值
这里有多个问题。 SQL似乎很困惑,我会使用BETWEEN来做这件事,在这里似乎多余的别名是不必要的。首先简化这个工作,然后查看一个jdbc教程。 – 2015-02-24 17:02:21