package gui;
import javax.swing.*;

import java.io.*;
import java.sql.*;
import java.util.Vector;

public class CategoryDA {
    private static ResultSet rs=null;
    private static Statement aStatement=null;
    private static Connection aConnection=null;
    static Vector Categories=new Vector();
    private static void getDBConnection()
    {
        try
        {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            aConnection=DriverManager.getConnection("jdbc:odbc:mydata","sa","sa");
        }catch(ClassNotFoundException e1)
        {
            JOptionPane.showMessageDialog(null, "驱动没找到，连接失败","Error",JOptionPane.ERROR_MESSAGE);
            
        }catch(SQLException e2)
        {
            JOptionPane.showMessageDialog(null, "用户名密码错误,连接失败","Error",JOptionPane.ERROR_MESSAGE);
            
        }
    }
    public static Vector getAll()
    {
        Vector <Category> Categories=new Vector();
        String strSQL;
        strSQL="SELECT * FROM Categories";
        try
        {
            getDBConnection();
            aStatement=aConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
            rs=aStatement.executeQuery(strSQL);
            while (rs.next())
            {
                Category aCategory=new Category();
                aCategory.setCategoryID(rs.getInt("CategoryID"));
                aCategory.setCategoryName(rs.getString("CategoryName"));
                aCategory.setDescription(rs.getString("Description"));
                byte [] buf1=rs.getBytes("Picture");
                aCategory.setPicture(buf1);
                
                Categories.add(aCategory);
            }
            rs.first();
        }
        catch(SQLException e)
        {
            JOptionPane.showMessageDialog(null, e.getMessage(),"Error",JOptionPane.ERROR_MESSAGE);
            
        }
        return Categories;
    }
    public static Category getCategory(ResultSet rs)
    {
        Category aCategory=new Category();
        try
        {
            aCategory.setCategoryID(rs.getInt("CategoryID"));
            aCategory.setCategoryName(rs.getString("CategoryName"));
            aCategory.setDescription(rs.getString("Description"));
            aCategory.setPicture(rs.getBytes("Picture"));
        }
        catch (SQLException e)
        {
            e.printStackTrace();
            JOptionPane.showMessageDialog(null, e.getMessage());
        }
        return aCategory;
    }
    public static Category GetCurrentCategory()
    {
        Category aCategory=new Category();
        try
        {
            rs.absolute(rs.getRow());
            aCategory=getCategory(rs);    
        }
        catch(SQLException e)
        {
            JOptionPane.showMessageDialog(null, e.getMessage(),"Error",JOptionPane.ERROR_MESSAGE);
        }
        return aCategory;
    }
    public static Category GetFirstCategory()
    {
        Category aCategory=new Category();
        try
        {
            rs.first();
            aCategory=getCategory(rs);
        } 
        catch(SQLException e)
        {
            JOptionPane.showMessageDialog(null, e.getMessage(),"Error",JOptionPane.ERROR_MESSAGE);
        }
        return aCategory;
    }
    public static Category GetNextCategory()
    {
        Category aCategory=new Category();
        try
        {
            if (rs.isLast())
            {
                JOptionPane.showMessageDialog(null, "已经是最后一条记录了","提示",JOptionPane.INFORMATION_MESSAGE);
                rs.absolute(rs.getRow());
            }
            else
            {
                rs.next();
                
            }
            aCategory=getCategory(rs);
        }catch(SQLException e)
        {
            JOptionPane.showMessageDialog(null, e.getMessage(),"Error",JOptionPane.ERROR_MESSAGE);
            
        }
        return aCategory;
    }
    public static Category getPrevCategory()
    {
        Category aCategory=new Category();
        try
        {
            if(rs.isFirst())
            {
                JOptionPane.showMessageDialog(null,"已经是第一条记录了","提示",JOptionPane.INFORMATION_MESSAGE);
            rs.absolute(rs.getRow());
            }
            else
            {
                rs.previous();
            }
            aCategory=getCategory(rs);
        }
        catch(SQLException e)
        {
            JOptionPane.showMessageDialog(null, e.getMessage(),"Error",JOptionPane.ERROR_MESSAGE);
            
        }
        return aCategory;
    }
    public static Category GetLastCategory()
    {
        Category aCategory=new Category();
        try
        {
            rs.last();
            aCategory=getCategory(rs);
        }
        catch(SQLException e)
        {
            JOptionPane.showMessageDialog(null, e.getMessage(),"Error",JOptionPane.ERROR_MESSAGE);
            
        }
        return aCategory;
    }
    public static void closeConneciton()
    {
        try
        {
            if(rs!=null)
            {
                rs.close();
            }
            if(aStatement!=null)
            {
                aStatement.close();
            }
            if(aConnection!=null&&!aConnection.isClosed())
            {
                aConnection.close();
            }
            
        }catch(SQLException e) 
        {
            JOptionPane.showMessageDialog(null, e.getMessage(),"Error",JOptionPane.ERROR_MESSAGE);
            
        }
        
    }
    public static void update (Category aCategory)
    {
        
        try
        {
            rs.updateString("CategoryName", aCategory.getCategoryName());
            rs.updateString("Description", aCategory.getDescription());
            rs.updateRow();
            
        }catch(SQLException e)
        {
            e.printStackTrace();
            JOptionPane.showMessageDialog(null, e.getMessage(),"Error",JOptionPane.ERROR_MESSAGE);
        }
    }
    public static void updatePhoto(byte[] buf)
    {
        
        try
        {
            rs.updateBytes("Picture", buf);
            rs.updateRow();
            JOptionPane.showMessageDialog(null, "修改成功","提示",JOptionPane.INFORMATION_MESSAGE);
            
            
        }catch(SQLException e)
        {
            JOptionPane.showMessageDialog(null, e.getMessage(),"Error",JOptionPane.ERROR_MESSAGE);
            
        }
    }
    public static void AddNew (Category aCategory)
    {
        try
        {
            rs.moveToInsertRow();
            rs.updateString("CategoryName", aCategory.getCategoryName());
            rs.updateString("Description", aCategory.getDescription());
            rs.insertRow();
            rs.last();
        }catch(SQLException e)
        {
            JOptionPane.showMessageDialog(null, e.getMessage());
        }
    }
    public static void delete()
    {
        try
        {
            rs.deleteRow();
            getAll();
        }catch(SQLException e)
        {
            JOptionPane.showMessageDialog(null, e.getMessage());
        }
    }
                       
}