/*
* xulfaces : bring XUL power to Java
*
* Copyright (C) 2005 Olivier SCHMITT
* This library is free software; you can redistribute it and/or
* modify it under the terms of the GNU Lesser General Public
* License as published by the Free Software Foundation; either
* version 2.1 of the License, or (at your option) any later version.
*
* This library is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
* Lesser General Public License for more details.
*
* You should have received a copy of the GNU Lesser General Public
* License along with this library; if not, write to the Free Software
* Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
*/
package org.xulfaces.rubis.admin.dao.jdbc;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import org.xulfaces.rubis.admin.dao.CategoryDAO;
import org.xulfaces.rubis.model.Category;
import org.xulfaces.rubis.model.Item;
import org.xulfaces.rubis.model.User;
public class JDBCCategoryDAO extends JDBCAbstractDAO implements CategoryDAO {
public JDBCCategoryDAO() throws Exception {
super();
}
public Collection<Category> getCategories() {
Collection<Category> categories = new ArrayList<Category>();
StringBuffer sqlBuffer = new StringBuffer();
sqlBuffer.append("SELECT * FROM categories");
ResultSet resultSet = null;
try {
resultSet = excecuteScrollableQuery(sqlBuffer.toString());
while (resultSet.next()) {
Category category = new Category();
category.setId(new Integer(resultSet.getInt("ID")));
category.setName(resultSet.getString("NAME"));
categories.add(category);
sqlBuffer = new StringBuffer();
sqlBuffer.append("SELECT count(*) as nbItems FROM items WHERE CATEGORY=");
sqlBuffer.append(category.getId());
ResultSet countResultSet = excecuteScrollableQuery(sqlBuffer.toString());
if(countResultSet.next()){
int count = countResultSet.getInt(1);
category.setNbItems(count);
}
closeConnection(countResultSet);
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
finally {
closeConnection(resultSet);
}
return categories;
}
public Category loadCategory(Integer id) {
return null;
}
public Collection<Item> loadItemsForCategory(Integer id) {
Collection<Item> items = new ArrayList<Item>();
StringBuffer sqlBuffer = new StringBuffer();
sqlBuffer.append("SELECT i.*,u.FIRSTNAME as FIRSTNAME,u.LASTNAME as LASTNAME FROM ITEMS as i,USERS as u where i.SELLER=u.ID and i.CATEGORY=");
sqlBuffer.append(id);
ResultSet resultSet = null;
try {
resultSet = excecuteScrollableQuery(sqlBuffer.toString());
while (resultSet.next()) {
Item item = new Item();
item.setId(new Integer(resultSet.getInt("ID")));
item.setName(resultSet.getString("NAME"));
item.setBuyNow(resultSet.getFloat("BUY_NOW"));
item.setDescription(resultSet.getString("DESCRIPTION"));
item.setEndDate(resultSet.getDate("END_DATE"));
item.setStartDate(resultSet.getDate("START_DATE"));
User seller = new User();
seller.setId(new Integer(resultSet.getInt("SELLER")));
seller.setFirstname(resultSet.getString("FIRSTNAME"));
seller.setLastname(resultSet.getString("LASTNAME"));
item.setSeller(seller);
items.add(item);
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
finally {
closeConnection(resultSet);
}
return items;
}
public void saveNewCategory(Category category) {
StringBuffer sqlBuffer = new StringBuffer();
try {
ResultSet resultSet = this.excecuteQuery("SELECT MAX(ID) as MAX_CAT_ID FROM categories");
if(resultSet.next()){
long id = resultSet.getLong("MAX_CAT_ID");
sqlBuffer.append("INSERT INTO categories (ID,NAME) VALUES (");
id = id + 1;
sqlBuffer.append(id);
sqlBuffer.append(",'");
sqlBuffer.append(category.getName());
sqlBuffer.append("')");
excecuteUpdateQuery(sqlBuffer.toString());
category.setId(new Integer((int) id));
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public void saveCategory(Category category) {
StringBuffer sqlBuffer = new StringBuffer();
try {
sqlBuffer.append("UPDATE categories SET NAME='");
sqlBuffer.append(category.getName());
sqlBuffer.append("'");
sqlBuffer.append(" WHERE ID=");
sqlBuffer.append(category.getId());
excecuteUpdateQuery(sqlBuffer.toString());
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public void deleteCategory(Integer id) {
StringBuffer sqlBuffer = new StringBuffer();
try {
sqlBuffer.append("DELETE FROM categories WHERE ID=");
sqlBuffer.append(id);
excecuteUpdateQuery(sqlBuffer.toString());
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}