//
//  AddressesDB.java
//

package apply;

import java.sql.*;
import java.util.*;

import com.ConnDB1;

public class AddressesDB extends ConnDB1 implements IAddressesDB{
	//Connection connection;
    private ArrayList addrdb;
   
    public AddressesDB ()throws Exception
    {
        //
    }

    //
      
    public Address getGuest (int id)
    {
        try
        {
        	ConnDB1.initialize();	// create database connection
            PreparedStatement preparedStatement = aConnection.prepareStatement (
            "SELECT id, username, rname, email, ptitle, pcontent, postdate FROM Bguest " +
            "WHERE gid = ?");
            preparedStatement.setInt (1, id);
            ResultSet resultSet = preparedStatement.executeQuery ();
            if (resultSet.next ())
            {
            	Address addr = new Address
                (
                    resultSet.getInt (1), resultSet.getString (2), resultSet.getString (3),resultSet.getString (4), 
                    resultSet.getString (5),resultSet.getString (6), resultSet.getString (7)
                );
                preparedStatement.close ();
                ConnDB1.terminate();
                return addr;
            }
            else
            {
                preparedStatement.close ();
                ConnDB1.terminate();
                return null;
            }
        }
        catch (SQLException e)
        {
        	ConnDB1.terminate();
            return null;
        }
    }

    public int addGuest (Address addr)
    {
        int rowsAffected = 0;
        try
        {
        	ConnDB1.initialize();	// create database connection
            PreparedStatement preparedStatement = aConnection.prepareStatement (
            "INSERT INTO Bguest (username, rname, email, ptitle, pcontent, postdate) VALUES (?, ?, ?, ?, ?, ?)");
            preparedStatement.setString (1, addr.getUsername());
            preparedStatement.setString (2, addr.getRname());
            preparedStatement.setString (3, addr.getEmail());
            preparedStatement.setString (4, addr.getTitle());
            preparedStatement.setString (5, addr.getContent());
            preparedStatement.setString (6, addr.getPostdate());
            rowsAffected = preparedStatement.executeUpdate ();
            preparedStatement.close ();
            ConnDB1.terminate();
        }
        catch (SQLException e)
        {
        	ConnDB1.terminate();
            return 0;
        }
        return rowsAffected;
    }

    public int deleteGuest (int id)
    {
        int rowsAffected = 0;
        try
        {
        	ConnDB1.initialize();	// create database connection
            
        	   PreparedStatement preparedStatement = aConnection.prepareStatement (
        	   "DELETE FROM Bguest WHERE gid = ?");
               preparedStatement.setInt (1, id);
               rowsAffected = preparedStatement.executeUpdate ();
               preparedStatement.close ();
               ConnDB1.terminate();
        }
        catch (SQLException e)
        {
        	ConnDB1.terminate();
            return 0;
        }
        return rowsAffected;
    }

    public int modifyAddress (Address addr)
    {
        int rowsAffected = 0;
        try
        {
        	ConnDB1.initialize();	// create database connection
            PreparedStatement preparedStatement = aConnection.prepareStatement (
            "UPDATE Bguest SET username=?, rname=?, email=?, ptitle=?, pcontent=?, postdate=? WHERE gid =?");
            preparedStatement.setString (1, addr.getUsername());
            preparedStatement.setString (2, addr.getRname());
            preparedStatement.setString (3, addr.getEmail());
            preparedStatement.setString (4, addr.getTitle());
            preparedStatement.setString (5, addr.getContent());
            preparedStatement.setString (6, addr.getPostdate());
            preparedStatement.setInt (7, addr.getId ());
            rowsAffected = preparedStatement.executeUpdate ();
            preparedStatement.close ();
            ConnDB1.terminate();
        }
        catch (SQLException e)
        {
        	ConnDB1.terminate();
            return 0;
        }
        return rowsAffected;
    }

    public Collection getAddress ()
    {
        addrdb = new ArrayList ();
        try
        {
        	ConnDB1.initialize();	// create database connection
            PreparedStatement preparedStatement = aConnection.prepareStatement(
            		"SELECT gid, username, rname, email, ptitle, pcontent, postdate FROM Bguest"
            );
            ResultSet resultSet = preparedStatement.executeQuery ();
            while (resultSet.next ())
            {
            	Address addr = new Address
                (
                    resultSet.getInt (1), resultSet.getString (2), resultSet.getString (3),resultSet.getString (4),
                    resultSet.getString (5),resultSet.getString (6), resultSet.getString (7)
                );
                addrdb.add(addr);
            }
            preparedStatement.close ();
        }
        catch (SQLException e)
        {
            return null;
        }
        ConnDB1.terminate();
        //Collections.sort(booktype);
        return addrdb;
    }
}