[FIXED] SQL Error Invalid column when using java servlet

Issue

This is my main page

Main_page

and for some reason I can search list by title, but I cannot get all list. The button param is readable as I've already checked. This is my code for getting all book the list in servlet:

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

This is my get all book code:

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;
}

This is my search book by title code:

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;
}

Here are my constants:

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

I'm showing the get by title one because that code is perfectly fine. Then, as you can see, the result set part in both method is completely the same, so there's no problem with the result part. So, I went to check in the DB. This is my table 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))

And my SQL query is not wrong check_get_all_query. Therefore, I can only think that the part that was wrong is the Quantity condition part. So, I try putting the value directly in the string rather than using setInt statement:

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);

The result is the same. So... I'm stuck. The log keeps showing that I have this error:

BookMarketController SQL: Invalid column name 'Quantity'.

Can someone tell me why?

Solution

You have missed a space after Status" so there is a StatusFrom column.

Use bind variables and a space at start of continuation lines:

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

Answered By - DuncG