/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package guitest;
import java.sql.*;
import javax.swing.JOptionPane;
import javax.swing.table.DefaultTableModel;
/**
 *
 * @author Malmo
 */
public class SauDatabase {

    private String input;

    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    ResultSetMetaData md = null;



    int col;
    String sqlStmt;
    String dbURL;
    String username;
    String password;






    public SauDatabase(){


        try{
            dbURL = "jdbc:mysql://localhost:3306/Sau";
            username = "root";
            password ="";
            sqlStmt = "";
            col = 0;

            Class.forName("com.mysql.jdbc.Driver");


        }
        catch (ClassNotFoundException ex)
    {
        System.err.println("Failed to load mysql driver");
        System.err.println(ex);
    }


    }




    public DefaultTableModel returDTM() throws SQLException{

        DefaultTableModel tableLol = null;
        tableLol = new DefaultTableModel(tablePrintOfDatabase(), getTableColumnNames());




        return tableLol;
    }


    public void executeQuery(String query){

        try{
            conn = DriverManager.getConnection(dbURL,username,password);
            stmt = conn.createStatement();


            stmt.execute(query);
        }
        catch(SQLException ex){
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
        }
        finally
        {
            if(stmt != null){
                try{
                    stmt.close();
                }
                catch(SQLException ex){
                    /* ignore */
                }
                stmt = null;
            }
            if(conn != null){
                try{
                    conn.close();
                }
                catch (SQLException ex){
                    /*ignore*/
                }
                conn = null;
            }
        }

    }


    public String getInput(){
        return sqlStmt;
    }

    public void setInput(String input){
        sqlStmt = input;
    }


    public Object[][] tablePrintOfDatabase() throws SQLException{
        Connection localConn = null;
        Statement localStmt = null;
        ResultSet localRs = null;
        ResultSetMetaData localMd = null;
        int rowCounter = 0;

        localConn = DriverManager.getConnection(dbURL,username,password);
        localStmt = localConn.createStatement();
        localRs = localStmt.executeQuery("SELECT * FROM sauInfo");
        localMd = localRs.getMetaData();

        Object[][] db_Data = new Object[getRows()][localMd.getColumnCount()];

        while(localRs.next()){
            db_Data[rowCounter][0] = localRs.getString(1);
            db_Data[rowCounter][1] = localRs.getString(2);
            db_Data[rowCounter][2] = localRs.getString(3);
            db_Data[rowCounter][3] = localRs.getString(4);
            rowCounter++;

        }

        localConn.close();
        localStmt.close();
        localRs.close();
        localMd = null;

        return db_Data;
    }

    public String[] getTableColumnNames() throws SQLException{
        String[] colNames;
        Connection localConn = null;
        Statement localStmt = null;
        ResultSet localRs = null;
        ResultSetMetaData localMd = null;

        localConn = DriverManager.getConnection(dbURL,username,password);
        localStmt = localConn.createStatement();
        localRs = localStmt.executeQuery("SELECT * FROM sauInfo");
        localMd = localRs.getMetaData();

        colNames = new String[localMd.getColumnCount()];

        for(int i = 0 ; i < colNames.length; i++){
            colNames[i] = localMd.getColumnName(i+1);
        }

        return colNames;

    }




    /*
     * IKKE I BRUK !!!!
     */
    /*
    public void table() throws SQLException{
        conn = DriverManager.getConnection(dbURL,username,password);
        stmt = conn.createStatement();
        rs = stmt.executeQuery("SELECT * FROM sauInfo");
        md = rs.getMetaData();

        String[][] tableView = new String[getRows()][md.getColumnCount()];

        for(int i = 0; i < tableView[0].length;i++){
            tableView[0][i] = md.getColumnName(i+1); //tabell fra databasen er ikke null-indeksert, starter dermed fra 1 og oppover.
        }
        System.out.println(tableView[0][0]+ "\t\t" + tableView[0][1]+ "\t\t" + tableView[0][2]+ "\t\t" + tableView[0][3]);
        while(rs.next()){
             System.out.println(rs.getString(1) + "\t\t" + rs.getString(2) + "\t\t\t" + rs.getString(3) + "\t\t" + rs.getString(4));

        }
    }

    */


    //Henter antall rader i databasen, teller antall ID!
    public int getRows() throws SQLException{
        Connection localConn = null;
        Statement localStmt = null;
        ResultSet localRs = null;
        int localRows = -1;
        try{
            localConn = DriverManager.getConnection(dbURL,username,password);
            localStmt = localConn.createStatement();
            localRs = localStmt.executeQuery("SELECT COUNT(*) FROM sauInfo;");
            localRs.next();
            localRows = localRs.getInt(1);
        }
        catch (SQLException ex){
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
        }
         finally
        {
            if(localStmt != null){
                try{
                    localStmt.close();
                }
                catch(SQLException ex){
                    /* ignore */
                }
                localStmt = null;
            }
            if(localConn != null){
                try{
                    localConn.close();
                }
                catch (SQLException ex){
                    /*ignore*/
                }
                localConn = null;
            }
            if(localRs != null)
            {
                try{
                    localRs.close();
                }
                catch(SQLException ex){
                    /*ignore*/
                }
                localRs = null;
            }
        }


        return localRows;

    }



}