/*

Written by Kęstutis Rutkauskas aka kestaz
Cocde licensed under BSD license

Little Music CD Database v.0.0.1 
To organize your music albums .. 
Uses Mysql

Simple mysql table looks like this:
mysql> describe diskai;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment | 
| title | varchar(100)     | YES  |     | NULL    |                | 
| album | varchar(100)     | YES  |     | NULL    |                | 
| year  | int(11)          | YES  |     | NULL    |                | 
+-------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

*/

package programa;

import java.awt.* ; 
import java.awt.event.*;
import java.util.Vector;
import javax.swing.* ;
import javax.swing.table.AbstractTableModel;
import java.sql.*;


public class Main {

    public static void main(String[] args) {
        
        App window = new App() ; 
        
    }

}

class App {
   
    protected JTable table ; 
    protected JMenuBar menuBar ; 
    protected JMenu menu, submenu, aboutMenu ; 
    protected JMenuItem menuItem, aboutItem ;
    protected JLabel statusBar ; 
    protected JButton addButton ; 
    protected JButton deleteButton ;
    private JFrame aboutFrame ; 
    protected InteractiveTabelModel tableModel ; 
    protected MysqlConnection conn ; 
    
    
    public String[] columnNames = { "Artist", "Album", "Year" }  ;
   
    App()
    {
        JFrame frame = new JFrame() ; 
        Container content = frame.getContentPane() ; 
        frame.setTitle("Tiny CD Database v.0.0.1") ;
        frame.setSize(400, 200) ; 
        frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        content.setLayout(new BorderLayout());        
        tableModel = new InteractiveTabelModel(columnNames) ; 
        table = new JTable() ; 
        table.setModel(tableModel) ;
        menuBar = new JMenuBar() ; 
        menu  = new JMenu("File") ; 
        aboutMenu = new JMenu("Help") ; 
        aboutItem = new JMenuItem("About") ; 
        aboutItem.addActionListener(
                new ActionListener()
                {
                    public void actionPerformed(ActionEvent e)
                    {
                        JFrame appFrame = getFrame() ; 
                        if (!appFrame.isVisible())
                        {
                        AppAbout() ; 
                        }
                    }
                }
                ) ; 
        aboutMenu.add(aboutItem) ; 
        menuBar.add(menu) ; 
        menuBar.add(aboutMenu) ; 
        menuItem = new JMenuItem("Exit") ; 
        menuItem.addActionListener(
                new ActionListener()
                {
                    public void actionPerformed(ActionEvent e)
                    {
                           System.exit(0)  ;
                    }
                }
                ) ; 
        menu.add(menuItem)  ;
        statusBar = new JLabel("Tiny CD Database v.0.0.1") ;
        JPanel rightPanel = new JPanel() ; 
        JScrollPane scrollPane = new JScrollPane(table) ; 
        //table.setFillsViewportHeight(true);   
        addButton = new JButton("Add CD") ; 
        addButton.addActionListener(
                new ActionListener()
                {
                    public void actionPerformed(ActionEvent e)
                    {
                         AddItem() ; 
                    }
                }
                ) ; 
        deleteButton = new JButton("Delete CD") ; 
        deleteButton.addActionListener(
                new ActionListener()
                {
                    public void actionPerformed(ActionEvent e)
                    {
                        
                       int row = table.getSelectedRow()  ;
                       //tableModel.removeRow(row) ;
                       CdRecord tmp = (CdRecord)(tableModel.dataVector.get(row)) ; 
                       conn.deleteItem(tmp.getId()) ; 
                       conn.setJTable(tableModel) ;
                    }
                }
                ) ; 
        rightPanel.setLayout(new GridBagLayout())  ; 
        GridBagConstraints c = new GridBagConstraints() ; 
        c.insets = new Insets(2,2,2,2) ;
        c.fill = GridBagConstraints.BOTH ; 
        c.gridx = 0 ; 
        c.gridy = 0 ; 
        c.gridheight = 3 ; 
        c.weightx = 1.0 ; 
        c.weighty = 1.0 ;
        rightPanel.add(scrollPane, c) ; 
        c.fill = GridBagConstraints.HORIZONTAL ; 
        c.gridx = 1 ; 
        c.gridheight = 1 ; 
        c.weightx = 0 ;      
        c.weighty = 0 ; 
        rightPanel.add(addButton, c) ; 
        c.gridy = 1 ; 
        rightPanel.add(deleteButton,c) ;  
        statusBar = new JLabel("Tiny CD Database v.0.0.1") ; 
        c.gridx = 0 ; 
        c.gridy = 3 ;
        rightPanel.add(statusBar, c) ; 
        content.add(rightPanel) ; 
        
        conn = new MysqlConnection() ; 
        conn.connectMysql("your_hostname", your_port) ; 
        conn.setJTable(tableModel) ;
        //conn.closeConn()  ;
        
        frame.pack(); 
        frame.setLocationRelativeTo(null) ;
        frame.setJMenuBar(menuBar) ; 
        frame.setVisible(true) ; 
        
    }
    
    
    public JFrame getFrame()
    {
       final JFrame aboutFrame  = new JFrame() ; 
        return aboutFrame ;
    }
    protected void AppAbout()
    {
        aboutFrame = getFrame() ; 
        JPanel panel = new JPanel(); 
        aboutFrame.setSize(400,200) ;
        aboutFrame.setLocationRelativeTo(null) ;
        aboutFrame.setTitle("About CD Database") ; 
        aboutFrame.setDefaultCloseOperation(JFrame.HIDE_ON_CLOSE);
        aboutFrame.setResizable(false) ; 
        Container content = aboutFrame.getContentPane() ;
        panel.setLayout(new GridBagLayout()) ;
        GridBagConstraints c = new GridBagConstraints() ; 
        c.fill = GridBagConstraints.BOTH ; 
        c.gridx = 0 ; 
        c.gridy = 0 ; 
        c.gridheight = 1 ; 
        JLabel aboutLabel = new JLabel("<html>CD Database v.0.0.1<br>Written by Kęstutis Rutkauskas<br>Licensed under BSD license</html>") ; 
        panel.add(aboutLabel, c) ; 
        JButton closeButton = new JButton("Close") ; 
        closeButton.addActionListener(
                new ActionListener()
                {
                    public void actionPerformed(ActionEvent e)
                    {
                         aboutFrame.setVisible(false) ; 
                    }
                }
                ) ; 
        c.fill = GridBagConstraints.RELATIVE ; 
        c.insets = new Insets(10,0,0,0) ; 
        c.gridy = 1 ; 
        panel.add(closeButton, c) ; 
        content.add(panel) ; 
        aboutFrame.setVisible(true); 
    }
    
    protected JFrame addItemForm  ; 
    
    protected void AddItem()
    {
       addItemForm = new JFrame() ; 
       JPanel panel = new JPanel() ; 
       addItemForm.setSize(400,200) ;
       addItemForm.setLocationRelativeTo(null) ;
       addItemForm.setTitle("Add Item") ; 
       addItemForm.setDefaultCloseOperation(JFrame.HIDE_ON_CLOSE);
       addItemForm.setResizable(false) ;
       Container content = addItemForm.getContentPane() ; 
       panel.setLayout(new GridBagLayout()) ; 
       GridBagConstraints c = new GridBagConstraints() ;
       final JTextField titleField = new JTextField(20) ; 
       final JTextField artistField = new JTextField(20) ; 
       final JTextField yearField = new JTextField(20) ;
       JButton addButton = new JButton("Add") ;
       addButton.addActionListener(
                new ActionListener()
                {
                    public void actionPerformed(ActionEvent e)
                    {
                          boolean tt = false ;
                          String title =  titleField.getText() ; 
                          String artist = artistField.getText() ; 
                          String tempYear = yearField.getText() ;
                          int year = 0 ; 
                          if (title.length() == 0 || artist.length() == 0 || tempYear.length() == 0)
                          {
                              tt = true ; 
                              JOptionPane.showMessageDialog(null, "Fill all text fields!") ;
                          }
                          else      
                            try 
                             {
                              year = Integer.parseInt(tempYear) ; 
                              }
                              catch (NumberFormatException t)
                              {
                               tt = true ;
                              JOptionPane.showMessageDialog(null, "Year must be number. Try again!") ;
                             }
                          if (tt == false)
                          {
                            CdRecord tmp = new CdRecord() ; 
                            tmp.setAlbum(title)  ;
                            tmp.setArtist(artist) ; 
                            tmp.setYear(year) ; 
                          
                            conn.addItem(tmp, tableModel) ; 
                            conn.setJTable(tableModel) ;
                            //tableModel.addToModel(artist, title, year) ; 
                            addItemForm.setVisible(false) ; 
                          }
                    }
                 }
          
                ) ; 
       JButton cancelButton = new JButton("Cancel") ;
       cancelButton.addActionListener(
                new ActionListener()
                {
                    public void actionPerformed(ActionEvent e)
                    {
                         addItemForm.setVisible(false) ;
                    }
                }
                ) ; 
       JLabel titleLabel = new JLabel("Title: ") ;
       JLabel artistLabel = new JLabel("Artist: ") ;
       JLabel yearLabel = new JLabel("Year: ") ;
       c.fill = GridBagConstraints.HORIZONTAL ; 
       c.gridx = 0 ; 
       c.gridy = 0 ; 
       c.gridheight  = 1 ; 
       panel.add(titleLabel, c) ; 
       c.gridy = 1 ; 
       panel.add(artistLabel, c) ; 
       c.gridy = 2 ;
       panel.add(yearLabel, c ) ;
       c.gridx = 1 ;
       c.gridy = 0 ; 
       c.insets = new Insets (10, 0, 0, 0);
       panel.add(titleField, c) ; 
       c.gridy = 1 ; 
       panel.add(artistField, c) ;
       c.gridy = 2 ;
       panel.add(yearField, c) ;
       c.gridx = 2 ; 
       c.gridy = 0 ; 
       c.insets = new Insets (10, 10, 0, 0);
       panel.add(addButton, c) ;
       c.gridy = 1 ; 
       panel.add(cancelButton, c) ;  
       content.add(panel) ; 
       addItemForm.setVisible(true) ; 
    }
    
    
}

class InteractiveTabelModel extends  AbstractTableModel {

    
    protected String[] columnNames ; 
    protected  Vector dataVector ; 
    
    
    public InteractiveTabelModel(String [] column)
    {
        columnNames = column ; 
        dataVector = new Vector() ; 
     
    }
    public int getRowCount() {
        
        return dataVector.size() ; 
    }

    public int getColumnCount() {
        return columnNames.length ;
    }
    
    public void setDataVector(Vector tmp)
    {
        dataVector = tmp ;
        fireTableDataChanged() ;  
    }
    public int addToModel(String Artist, String Album, int year)
    {
        CdRecord item = new CdRecord() ; 
        
        item.setAlbum(Album) ; 
        item.setArtist(Artist) ; 
        item.setYear(year) ; 
        
        dataVector.add(item) ; 
        
        fireTableDataChanged() ; 
        return 0 ; 
    }

    public Object getValueAt(int rowIndex, int columnIndex) {
        
        CdRecord record  = (CdRecord) dataVector.get(rowIndex) ; 
        switch(columnIndex)
        {
         
            case 0:
                return record.getArtist() ; 
            case 1:
                return record.getAlbum() ; 
            case 2: 
                return record.getYear() ; 
            default:
                return new Object() ; 
        }
           

    }
   
    @Override
    public String getColumnName (int column)
    {
        return columnNames[column] ; 
    }
    
    public void removeRow(int row)
    {
        dataVector.removeElementAt(row) ;
        fireTableDataChanged();
    }
    
}


class MysqlConnection
{
    public Connection conn = null ; 
    
    public Connection getConnection() 
    {
        return conn ; 
    }
    public void connectMysql(String hostname, int port)
    {
        try 
                
        {
            Class.forName("com.mysql.jdbc.Driver").newInstance() ; 
            conn = DriverManager.getConnection("jdbc:mysql://your_hostname/table_name", "your_login", "your_password") ;
        }
        catch (Exception e)
        {
            System.out.println(e.getMessage()) ; 
            JOptionPane.showMessageDialog(null, "Can't connect to mysql database at " + hostname) ; 
            
        }
        
    }
    
    public void setJTable(InteractiveTabelModel tableModel)
    {
        
        try
        {
       
          Statement s = conn.createStatement() ; 
          s.executeQuery("SELECT id, title, album, year FROM diskai") ; 
          ResultSet rs = s.getResultSet() ; 
        int count = 0 ; 
        
        Vector dataVector = new Vector() ; 
        
        while (rs.next())   
        {
         
            int id = rs.getInt("id") ; 
            String title = rs.getString("title")  ;
            String album = rs.getString("album")  ;
            int year = rs.getInt("year")  ;
            
            CdRecord item = new CdRecord() ; 
            item.setAlbum(album) ; 
            item.setArtist(title) ; 
            item.setId(id) ; 
            item.setYear(year) ; 
            dataVector.add(item) ; 
            ++count ; 
         }
        
        rs.close() ; 
        s.close() ;
        tableModel.setDataVector(dataVector) ; 
         
        }
        catch (Exception e){
            
            System.out.println(e.getMessage())  ;
        }
      
    }
    
    private Connection conn() throws SQLException {
        if (conn == null ||conn.isClosed()) {
         connectMysql("aa", 11);   
        }
    
        return conn;
    }
    
    public void addItem(CdRecord tmp, InteractiveTabelModel tableModel )
    {
      
        
        try {
            
        //Statement s = conn.createStatement() ; 
        int count = 0 ; 
        PreparedStatement pstmt = conn().prepareStatement("INSERT INTO diskai (title,album, year) VALUES(?,?,?)") ;
        
        pstmt.setString(1, tmp.getArtist()) ; 
        pstmt.setString(2,tmp.getAlbum()) ;
        pstmt.setInt(3,tmp.getYear()) ; 
        
        pstmt.executeUpdate() ;  
        
       
        pstmt.close() ; 
        setJTable(tableModel) ;
        
      
        }
        catch (Exception e)
        {
            System.out.println(e.getMessage())  ; 
        }
    }
       
    
    public void deleteItem(int id)
    {
        
        
        try {
            Statement s = conn().createStatement() ; 
            int count = 0 ; 
            s.executeUpdate("DELETE FROM diskai WHERE id=" + id +";") ;
            s.close() ; 
           
        }
       catch (Exception e)
       {
           System.out.println(e.getMessage()) ; 
       }
    
    }
    public void closeConn()
    {
          if (conn != null)
           {
               try
               {
                   conn.close() ; 
                   
               }   
               catch (Exception e)
               {
                 System.out.println(e.getMessage()) ;    
               }
           }
          
          
    }
}

class CdRecord { 
    
    protected int id ; 
    protected String album ; 
    protected String artist ;
    protected int year ; 
    
    public CdRecord() {
        
        id = 0 ; 
        
        album = "" ; 
        artist = "" ;
        year = 0 ;
    }
    
    public void setYear(int year)
    {
        this.year = year ; 
    }
    
    public void setArtist(String artist)
    {
        this.artist = artist ; 
    }
    
    public void setAlbum(String album)
    {
        this.album = album ; 
    }
    
    public int getYear()
    {
        return year ; 
    }
    
    public String getArtist()
    {
        return artist ;
        
    }
    
    public String getAlbum()
    {
        return album ; 
    }
    
    public void setId(int id)
    {
        this.id = id ;
    }
    
    public int getId()
    {
        return id ; 
    }
}