package com.oreilly.ajax;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class ProductManager
{
static public String imagePath;
public static String getImagePath()
{
return imagePath;
}
public static void setImagePath(String imagePath)
{
ProductManager.imagePath = imagePath;
}
static public Product getProduct(String productName)
{
Product product = new Product();
Connection con = DatabaseConnector.getConnection();
String sqlString = "";
try
{
sqlString = "SELECT * FROM PRODUCTS WHERE PRODUCT_NAME='" + productName + "';";
Statement select = con.createStatement();
ResultSet result = select.executeQuery(sqlString);
if(result.next())
{ // process results one row at a time
product.setProductName(result.getString("PRODUCT_NAME"));
product.setDescription(result.getString("DESCRIPTION"));
product.setFilename(result.getString("FILENAME"));
product.setPrice(result.getFloat("PRICE"));
product.setProductId(result.getInt("PRODUCT_ID"));
}
}
catch(Exception e)
{
System.out.println("exception caught getting Product" + sqlString + " " + e.getMessage());
}
finally
{
if(con != null)
{
try
{
con.close();
}
catch(SQLException e)
{
}
}
}
return product;
}
static public boolean updateProductById(int id, String column, String newValue)
{
boolean result = false;
String sqlString = "UPDATE PRODUCTS SET "+column+ " = '"+newValue+"' WHERE PRODUCT_ID='" + id + "';";
Connection con = DatabaseConnector.getConnection();
try
{
Statement select = con.createStatement();
result = select.execute(sqlString);
}
catch(Exception e)
{
System.out.println("exception caught updating Product" + sqlString + " " + e.getMessage());
}
finally
{
if(con != null)
{
try
{
con.close();
}
catch(SQLException e)
{
}
}
}
return result;
}
static public Product getProductById(String productID)
{
Product product = new Product();
Connection con = DatabaseConnector.getConnection();
String sqlString = "";
try
{
sqlString = "SELECT * FROM PRODUCTS WHERE PRODUCT_ID='" + productID + "';";
Statement select = con.createStatement();
ResultSet result = select.executeQuery(sqlString);
if(result.next())
{ // process results one row at a time
product.setProductName(result.getString("PRODUCT_NAME"));
product.setDescription(result.getString("DESCRIPTION"));
product.setFilename(result.getString("FILENAME"));
product.setPrice(result.getFloat("PRICE"));
product.setProductId(result.getInt("PRODUCT_ID"));
}
}
catch(Exception e)
{
System.out.println("exception caught getting Product" + sqlString + " " + e.getMessage());
}
finally
{
if(con != null)
{
try
{
con.close();
}
catch(SQLException e)
{
}
}
}
return product;
}
static public boolean deleteRow(String productID)
{
Product product = new Product();
Connection con = DatabaseConnector.getConnection();
String sqlString = "";
boolean result = false;
try
{
sqlString = "DELETE FROM PRODUCTS WHERE PRODUCT_ID='" + productID + "';";
Statement select = con.createStatement();
result = select.execute(sqlString);
}
catch(Exception e)
{
System.out.println("exception caught deleteing Product" + sqlString + " " + e.getMessage());
}
finally
{
if(con != null)
{
try
{
con.close();
}
catch(SQLException e)
{
}
}
}
return result;
}
public static String addProduct()
{
// move the product photo to the specified directory. The directory path should be stored in a resource file.
//http://jakarta.apache.org/commons/fileupload/using.html
// File
Connection con = DatabaseConnector.getConnection();
try
{
PreparedStatement ps = con.prepareStatement("INSERT INTO PRODUCTS (PRODUCT_NAME) values(?)");
ps.setString(1,"New Product");
ps.executeUpdate();
}
catch(Exception e)
{
System.out.println("exception caught inserting New Product into product table");
}
finally
{
if(con != null)
{
try
{
con.close();
}
catch(SQLException e)
{
}
}
}
return getJSONProducts();
}
public static String getJSONProducts()
{
return getProducts();
}
public static String getProducts()
{
Product product = new Product();
String title = "products";
Connection con = DatabaseConnector.getConnection();
StringBuffer jsonString = new StringBuffer("\r\n{\"" + title + "\": [");
String sqlString = "";
try
{
sqlString = "SELECT * FROM PRODUCTS ORDER BY PRODUCT_ID DESC;";
Statement select = con.createStatement();
ResultSet result = select.executeQuery(sqlString);
while (result.next())
{ // process results one row at a time
product.setProductName(result.getString("PRODUCT_NAME"));
product.setDescription(result.getString("DESCRIPTION"));
product.setFilename(result.getString("FILENAME"));
product.setPrice(result.getFloat("PRICE"));
product.setProductId(result.getInt("PRODUCT_ID"));
jsonString.append("\r\n{\"description\":\"" + product.getDescription() +
"\",\"name\":\"" + product.getProductName() +
"\",\"filename\":\"" + product.getFilename() +
"\", \"price\":\"" + product.getPrice() +
"\", \"id\":\"" + product.getProductId() +
"\",\"imagepath\":\"" + imagePath +"/" + product.getFilename() + "\"},");
}
int lastCharIndex = jsonString.length();
jsonString.deleteCharAt(lastCharIndex - 1);
jsonString.append("\r\n]}");
}
catch(Exception e)
{
System.out.println("exception caught getting Product" + sqlString + " " + e.getMessage());
}
finally
{
if(con != null)
{
try
{
con.close();
}
catch(SQLException e)
{
}
}
}
return jsonString.toString();
}
}
|