/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package budgettst;

/**
 *
 * @author frih0812
 */

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSetMetaData;
import java.util.Date;

public class DbConnection
{
    private static String dbURL = "jdbc:derby://localhost:1527/EkonomiKoll;create=true;user=tst;password=tst";
    private static String tableName = "Utgifter";
    // jdbc Connection
    private static Connection conn = null;
    private static Statement stmt = null;

    public static void main(String[] args)
    {
        createConnection();
        //insertRestaurants(5, "LaVals", "Berkeley");
        //insertRestaurants(20, "Mias grill", "Umeå");
        //insertUtgifter(7, "Invito", "Umeå");
        //selectUtgifter();
        shutdown();
    }



    int id,summa;
    String kategori,notering;
    Date datum;

    public String Utgifter(Integer id, int summa, Date datum, String kategori,String notering)
    {
        this.id = id;
        this.summa = summa;
        this.datum = datum;
        this.kategori = kategori;
        this.notering = notering;
        //insertUtgifter(id,summa,datum,kategori,notering);
        String sqlSats="insert into Utgifter (id, summa, datum, kategori, notering) values ("+
        id +", "+ summa +", "+"'"+ datum +"', "+"'"+ kategori +"', "+"'"+ notering+"'"+")";
        return(sqlSats);
        //insertUtgifter(sqlSats);
    }

    /*public Utgifter()
    {
    }

    public Utgifter(Integer id)
    {
        this.id = id;
    }

    public Utgifter(Integer id, int summa, Date datum, String kategori)
    {
        this.id = id;
        this.summa = summa;
        this.datum = datum;
        this.kategori = kategori;
    }

    public Integer getId()
    {
        return id;
    }

    public void setId(Integer id)
    {
        this.id = id;
    }

    public int getSumma()
    {
        return summa;
    }

    public void setSumma(int summa)
    {
        this.summa = summa;
    }

    public Date getDatum()
    {
        return datum;
    }

    public void setDatum(Date datum)
    {
        this.datum = datum;
    }

    public String getNotering()
    {
        return notering;
    }

    public void setNotering(String notering)
    {
        this.notering = notering;
    }

    public String getKategori()
    {
        return kategori;
    }

    public void setKategori(String kategori)
    {
        this.kategori = kategori;
    }*/



    public static void createConnection()
    {
        try
        {
            Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
            //Get a connection
            conn = DriverManager.getConnection(dbURL);
            System.out.println("Hallelulja!");
        }
        catch (Exception except)
        {
            except.printStackTrace();
            System.out.println("ASS!!");
        }

    }

    public static void insertUtgifter(String sqlSats)
    {
        try
        {
            stmt = conn.createStatement();
            stmt.execute(sqlSats);
            stmt.close();
        }
        catch (SQLException sqlExcept)
        {
            sqlExcept.printStackTrace();
        }
        shutdown();
    }

    private static void selectUtgifter()
    {
        try
        {
            stmt = conn.createStatement();
            ResultSet results = stmt.executeQuery("select * from " + tableName);
            ResultSetMetaData rsmd = results.getMetaData();
            int numberCols = rsmd.getColumnCount();
            for (int i=1; i<=numberCols; i++)
            {
                //print Column Names
                System.out.print(rsmd.getColumnLabel(i)+"\t\t");
            }

            System.out.println("\n-------------------------------------------------");

            while(results.next())
            {
                int id = results.getInt(1);
                String restName = results.getString(2);
                String cityName = results.getString(3);
                System.out.println(id + "\t\t" + restName + "\t\t" + cityName);
            }
            results.close();
            stmt.close();
        }
        catch (SQLException sqlExcept)
        {
            sqlExcept.printStackTrace();
        }
    }

    private static void shutdown()
    {
        try
        {
            if (stmt != null)
            {
                stmt.close();
            }
            if (conn != null)
            {
                DriverManager.getConnection(dbURL + ";shutdown=true");
                conn.close();
            }
        }
        catch (SQLException sqlExcept)
        {

        }
    }
}