/*
* Copyright 2002-2009 the original author or authors.
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package org.springframework.flex.samples.product;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.sql.DataSource;
/**
*
* @author Christophe Coenraets
* @author Jeremy Grelle
*/
public class ProductDAO implements IProductDAO {
private final DataSource dataSource;
public ProductDAO(DataSource dataSource) {
this.dataSource = dataSource;
}
public List<Product> findAll() {
List<Product> list = new ArrayList<Product>();
Connection c = null;
try {
c = this.dataSource.getConnection();
Statement s = c.createStatement();
ResultSet rs = s.executeQuery("SELECT * FROM product ORDER BY name");
while (rs.next()) {
list.add(new Product(rs.getInt("id"), rs.getString("name"), rs.getString("description"), rs.getString("image"),
rs.getString("category"), rs.getDouble("price"), rs.getInt("qty")));
}
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
return list;
}
public List<Product> findByName(String name) {
List<Product> list = new ArrayList<Product>();
Connection c = null;
try {
c = this.dataSource.getConnection();
PreparedStatement ps = c.prepareStatement("SELECT * FROM product WHERE UPPER(name) LIKE ? ORDER BY name");
ps.setString(1, "%" + name.toUpperCase() + "%");
ResultSet rs = ps.executeQuery();
while (rs.next()) {
list.add(new Product(rs.getInt("id"), rs.getString("name"), rs.getString("description"), rs.getString("image"),
rs.getString("category"), rs.getDouble("price"), rs.getInt("qty")));
}
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
return list;
}
public Product findById(int id) {
Product product = new Product();
Connection c = null;
try {
c = this.dataSource.getConnection();
PreparedStatement ps = c.prepareStatement("SELECT * FROM product WHERE id=?");
ps.setInt(1, id);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
product = new Product();
product.setProductId(rs.getInt("id"));
product.setName(rs.getString("name"));
product.setDescription(rs.getString("description"));
product.setImage(rs.getString("image"));
product.setCategory(rs.getString("category"));
product.setPrice(rs.getDouble("price"));
product.setQty(rs.getInt("qty"));
}
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
}
return product;
}
public Product create(Product product) {
Connection c = null;
PreparedStatement ps = null;
try {
c = this.dataSource.getConnection();
ps = c.prepareStatement("INSERT INTO product (name, description, image, category, price, qty) VALUES (?, ?, ?, ?, ?, ?)",
new String[] { "ID" });
ps.setString(1, product.getName());
ps.setString(2, product.getDescription());
ps.setString(3, product.getImage());
ps.setString(4, product.getCategory());
ps.setDouble(5, product.getPrice());
ps.setInt(6, product.getQty());
ps.executeUpdate();
ResultSet rs = ps.getGeneratedKeys();
rs.next();
// Update the id in the returned object. This is important as this value must be returned to the client.
int id = rs.getInt(1);
product.setProductId(id);
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
}
return product;
}
public boolean update(Product product) {
Connection c = null;
try {
c = this.dataSource.getConnection();
PreparedStatement ps = c.prepareStatement("UPDATE product SET name=?, description=?, image=?, category=?, price=?, qty=? WHERE id=?");
ps.setString(1, product.getName());
ps.setString(2, product.getDescription());
ps.setString(3, product.getImage());
ps.setString(4, product.getCategory());
ps.setDouble(5, product.getPrice());
ps.setInt(6, product.getQty());
ps.setInt(7, product.getProductId());
return ps.executeUpdate() == 1;
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
public boolean remove(Product product) {
Connection c = null;
try {
c = this.dataSource.getConnection();
PreparedStatement ps = c.prepareStatement("DELETE FROM product WHERE id=?");
ps.setInt(1, product.getProductId());
int count = ps.executeUpdate();
return count == 1;
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
public boolean removeById(int id) {
Connection c = null;
try {
c = this.dataSource.getConnection();
PreparedStatement ps = c.prepareStatement("DELETE FROM product WHERE id=?");
ps.setInt(1, id);
int count = ps.executeUpdate();
return count == 1;
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
}