DAO.java

package database;

import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.fileupload.FileItem;





//import sit.nyp.edu.JavaBeans.Account;
//import sit.nyp.edu.database.DBController;
//import sit.nyp.edu.JavaBeans.Workshop;
//import sit.nyp.edu.database.DBController;
import bean.Feedback;
import bean.Products;
import bean.QuestionAnswer;


public class DAO {

	Connection con;
	public static String url = "jdbc:mysql://localhost/it3191db";
	public static String dbdriver = "com.mysql.jdbc.Driver";
	public static String username = "root";
	public static String password = "root";

	public DAO() throws Exception {
		try {
			Class.forName(dbdriver);
			con = DriverManager.getConnection(url, username, password);// Getting
																		// connection
																		// to
																		// database
			con.setAutoCommit(true);
		} catch (Exception ex) {
			System.out.println("Exception in DBAO " + ex);
			throw new Exception("Couldn't open connection to database: "
					+ ex.getMessage());
		}
	}

	public Products retrieveProducts (){
		Products products = new Products();

		String query = "Select ProductsID, ProductsName, ProductsDescription, ProductsUnitPrice, ProductsUnitInStock from products";

		try
		{
			// Use the connection to create a PreparedStatement object
			PreparedStatement ps = con.prepareStatement(query);
			ResultSet rs = ps.executeQuery();
			while(rs.next())
			{
				Products productObj = new Products();
				productObj.setProductsID(rs.getInt("ProductsID"));
				productObj.setProductsName(rs.getString("ProductsName"));
				productObj.setProductsDescription(rs.getString("ProductsDescription"));
				productObj.setProductsUnitPrice(rs.getString("ProductsUnitPrice"));
				productObj.setProductsUnitInStock(rs.getInt("ProductsUnitInStock"));

			}

			return products;
		} catch (Exception e) {
			e.printStackTrace();
			return products;
		}
	}
	public ArrayList <Products> retrieveAllProducts ()
	{
		ArrayList <Products> allProducts = new ArrayList<Products>();
		String query = "SELECT ProductsID, ProductsName, ProductsDescription, ProductsUnitPrice, ProductsUnitInStock from products";
		try
		{
			PreparedStatement ps = con.prepareStatement(query);
			ResultSet rs = ps.executeQuery();
			while(rs.next())
			{
				Products productObj = new Products();
				productObj.setProductsID(rs.getInt("ProductsID"));
				productObj.setProductsName(rs.getString("ProductsName"));
				productObj.setProductsDescription(rs.getString("ProductsDescription"));
				productObj.setProductsUnitPrice(rs.getString("ProductsUnitPrice"));
				productObj.setProductsUnitInStock(rs.getInt("ProductsUnitInStock"));
				System.out.println("Shane 2");
				allProducts.add(productObj);
			}
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
		return allProducts;
		}

	public boolean insertFeedback(Feedback feedback) {
		boolean result= false;
		String sql = "INSERT INTO feedback(FeedbackID, FeedbackContent, FeedbackItem, FeedbackPrice, FeedbackDelivery, FeedbackService, FeedbackRecommendation)";
		sql= sql + "Values(?,?,?,?,?,?,?)";


		PreparedStatement ps = null;

		try {

			ps= con.prepareStatement(sql);
			ps.setInt(1, feedback.getFeedbackID());
			ps.setString(2, feedback.getFeedbackContent());
			ps.setString(3, feedback.getFeedbackItem());
			ps.setString(4, feedback.getFeedbackPrice());
			ps.setString(5, feedback.getFeedbackDelivery());
			ps.setString(6, feedback.getFeedbackService());
			ps.setString(7, feedback.getFeedbackRecommendation());


			result = true;

			ps.executeUpdate();
			ps.close();


		}
		catch (Exception ex){
			ex.printStackTrace();
		}

		return result;
	}
	}