package apply;

import java.sql.*;
import java.util.*;
import com.ConnDB1;

public class NoteDB extends ConnDB1 implements INoteDB{
	//Connection connection;
    private ArrayList notedb;
   
    public NoteDB ()throws Exception
    {
        //
    }

    //
    
    public Note getNote (int id)
    {
        try
        {
        	ConnDB1.initialize();	// create database connection
            PreparedStatement preparedStatement = aConnection.prepareStatement (
            "SELECT noteId, title, content, postdate, filepath FROM Bnote " +
            "WHERE noteId = ?");
            preparedStatement.setInt (1, id);
            ResultSet resultSet = preparedStatement.executeQuery ();
            if (resultSet.next ())
            {
            	Note notes = new Note
                (
                    resultSet.getInt (1), resultSet.getString (2), resultSet.getString (3), resultSet.getString (4), resultSet.getString (5)
                );
                preparedStatement.close ();
                ConnDB1.terminate();
                return notes;
            }
            else
            {
                preparedStatement.close ();
                ConnDB1.terminate();
                return null;
            }
        }
        catch (SQLException e)
        {
        	ConnDB1.terminate();
            return null;
        }
    }

    public int addNote (Note notes)
    {
        int rowsAffected = 0;
        try
        {
        	ConnDB1.initialize();	// create database connection
            PreparedStatement preparedStatement = aConnection.prepareStatement (
            "INSERT INTO Bnote (title,content,postdate,filepath) VALUES (?,?,?,?)");
            preparedStatement.setString (1, notes.getTitle());
            preparedStatement.setString (2, notes.getContent());
            preparedStatement.setString (3, notes.getDate());
            preparedStatement.setString (4, notes.getPath());
            rowsAffected = preparedStatement.executeUpdate ();
            preparedStatement.close ();
            ConnDB1.terminate();
        }
        catch (SQLException e)
        {
        	ConnDB1.terminate();
            return 0;
        }
        return rowsAffected;
    }

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

    public int modifyNote (Note notes)
    {
        int rowsAffected = 0;
        try
        {
        	ConnDB1.initialize();	// create database connection
            PreparedStatement preparedStatement = aConnection.prepareStatement (
            "UPDATE Bnote SET title=?, content=?, postdate=?, filepath=? WHERE noteId =?");
            preparedStatement.setString (1, notes.getTitle());
            preparedStatement.setString (2, notes.getContent());
            preparedStatement.setString (3, notes.getDate());
            preparedStatement.setString (4, notes.getPath());
            preparedStatement.setInt (5, notes.getId ());
            rowsAffected = preparedStatement.executeUpdate ();
            preparedStatement.close ();
            ConnDB1.terminate();
        }
        catch (SQLException e)
        {
        	ConnDB1.terminate();
            return 0;
        }
        return rowsAffected;
    }

    public Collection getNote ()
    {
        notedb = new ArrayList ();
        try
        {
        	ConnDB1.initialize();	// create database connection
            PreparedStatement preparedStatement = aConnection.prepareStatement (
            "SELECT noteId, title, content, postdate, filepath FROM Bnote");
            ResultSet resultSet = preparedStatement.executeQuery ();
            while (resultSet.next ())
            {
                Note nt = new Note
                (
                    resultSet.getInt (1), resultSet.getString (2), resultSet.getString (3), resultSet.getString (4), resultSet.getString (5)
                );
                notedb.add(nt);
            }
            preparedStatement.close ();
        }
        catch (SQLException e)
        {
            return null;
        }
        ConnDB1.terminate();
        //Collections.sort(booktype);
        return notedb;
    }
    
    public boolean find(String key) 
	{    
    	boolean gotIt=false;
    	try
        {
    		ConnDB1.initialize();	// create database connection
            PreparedStatement preparedStatement = aConnection.prepareStatement (
            		"SELECT noteId FROM Bnote WHERE title = ?");
            preparedStatement.setString (1, key);
            ResultSet resultSet = preparedStatement.executeQuery ();
            if (resultSet.next ())
            {
            	gotIt=true;
            	preparedStatement.close ();
            	ConnDB1.terminate();
            }
            else
            {
                preparedStatement.close ();
                ConnDB1.terminate();
                //return isValid;
            }
        }
        catch (SQLException e)
        {
        	ConnDB1.terminate();
            //return gotIt;
        }
        return gotIt;
	}
    
    public boolean isModify(String key,int id) 
	{    
    	boolean modifyIt=false;
    	try
        {
    		ConnDB1.initialize();	// create database connection
            PreparedStatement preparedStatement = aConnection.prepareStatement (
            		"SELECT noteId FROM Bnote WHERE title = ? and noteId <> ?");
            preparedStatement.setString (1, key);
            preparedStatement.setInt (2, id);
            ResultSet resultSet = preparedStatement.executeQuery ();
            if (resultSet.next ())
            {
            	modifyIt=true;
            	preparedStatement.close ();
            	ConnDB1.terminate();
            }
            else
            {
                preparedStatement.close ();
                ConnDB1.terminate();
                //return isValid;
            }
        }
        catch (SQLException e)
        {
        	ConnDB1.terminate();
            //return gotIt;
        }
        return modifyIt;
	}
}