使用java servlet时SQL错误无效列

Question

这是我的主页

Main_page

，出于某种原因，我可以通过标题搜索列表，但我无法获得所有列表。按钮参数是可读的，因为我已经检查过了。这是我在servlet中获取所有预订列表的代码:

searchResults = bookDao.loadBookFromDB();
System.out.println("nGet all book: ");
bookDao.showList(searchResults);

这是我的get all book代码:

public ArrayList<BookDTO> loadBookFromDB() throws SQLException, NamingException {
try {
connection = MyDB.createConnection();
if (connection != null) {
String sql = "Select Id, Title, Author, ImageUrl, Quantity, Price, CategoryId, CreatedDate, Status"
+ "From " + MyConstants.TBL_BOOK
+ " Where Quantity >= ? "
+ " And Status = " + MyConstants.STATUS_STRING_ACTIVE;
statement = connection.prepareStatement(sql);
statement.setInt(1, MyConstants.MIN_BOOK_QUANTITY);
result = statement.executeQuery();
ArrayList<BookDTO> searchResult = null;
while (result.next()) {
String title = result.getString("Title");
String author = result.getString("Author");
String imageUrl = result.getString("ImageUrl");
String status = result.getString("Status");
int id = result.getInt("Id");
int price = result.getInt("Price");
int quantity = result.getInt("Quantity");
int categoryId = result.getInt("CategoryId");
Date createDate = result.getDate("CreatedDate");
if (searchResult == null) {
searchResult = new ArrayList<>();
}
searchResult.add(new BookDTO(id, categoryId, quantity, price, title, author, status, imageUrl, createDate));
}
return searchResult;
}
} finally {
checkConnection();
}
return MyConstants.FOUND_NO_LIST;
}

这是我的搜索书标题代码:

public ArrayList<BookDTO> getBookByLikeTitle(String searchValue) throws SQLException, NamingException {
try {
connection = MyDB.createConnection();
if (connection != null) {
String sql = "Select Id, Title, Author, ImageUrl, Quantity, Price, CategoryId, CreatedDate, Status "
+ "From " + MyConstants.TBL_BOOK
+ "Where Title like ?";
statement = connection.prepareStatement(sql);
statement.setString(1, "%" + searchValue + "%");
result = statement.executeQuery();
ArrayList<BookDTO> searchResult = null;
while (result.next()) {
String title = result.getString("Title");
String author = result.getString("Author");
String imageUrl = result.getString("ImageUrl");
String status = result.getString("Status");
int id = result.getInt("Id");
int price = result.getInt("Price");
int quantity = result.getInt("Quantity");
int categoryId = result.getInt("CategoryId");
Date createDate = result.getDate("CreatedDate");
if (searchResult == null) {
searchResult = new ArrayList<>();
}
searchResult.add(new BookDTO(id, categoryId, quantity, price, title, author, status, imageUrl, createDate));
}
return searchResult;
}
} finally {
checkConnection();
}
return MyConstants.FOUND_NO_LIST;
}

以下是我的常量:

public final static String TBL_BOOK = "Book ";
public final static String STATUS_STRING_ACTIVE = "Active";
public final static int MIN_BOOK_QUANTITY = 1;

我展示了getby title 1因为代码很好。然后，如您所见，两个方法中的结果集部分是完全相同的，因此结果部分没有问题。所以我去数据库查了一下。这是我的表create:

Create table [Book] (
[Id] int NOT NULL IDENTITY(1,1) primary key,
[Title] varchar(200),
[Author] varchar(50),
[ImageUrl] varchar(MAX),
[Quantity] int,
[Price] int,
[CategoryId] int,
[CreatedDate] datetime,
[Status] varchar(10))

和我的SQL查询不是错误的check_get_all_query。所以我只能认为错误的部分是Quantity条件的一部分。因此，我尝试将值直接放在字符串中，而不是使用setInt语句:

String sql = "Select Id, Title, Author,ImageUrl, Quantity, Price, CategoryId, CreatedDate, Status"
+ "From " + MyConstants.TBL_BOOK
+ "Where Quantity >= " + MyConstants.MIN_BOOK_QUANTITY 
+ "And Status = " + MyConstants.STATUS_STRING_ACTIVE;
statement = connection.prepareStatement(sql);

结果是一样的。所以…我卡住了。日志一直显示我有这个错误:

BookMarketController SQL:无效的列名'Quantity'.

谁能告诉我为什么?

Answer 1

您在Status"后面错过了一个空格，因此有一个StatusFrom列。

在连续行开始处使用绑定变量和空格:

String selectAll =
"Select Id, Title, Author,ImageUrl, Quantity, Price, CategoryId, CreatedDate, Status";
String sql = selectAll  
+ " Where Quantity >= ? And Status = ?";