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

import java.sql.Connection;
import java.sql.Statement;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 *
 * @author h11ladem
 */
public class DAOBil {

    private Connection conn = null;
    private Statement stmt = null;
    String url;
    String username;
    String password;

    public DAOBil() {
        try {
            url = "jdbc:mysql://utb-mysql.du.se:3306/dbo06";
            username = "db06";
            password = "Oy9CkDSJ";

            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, username, password);

        } catch (ClassNotFoundException exp) {
            System.out.println(exp.getMessage());

        } catch (SQLException sqx) {
            System.out.println(sqx.getMessage());
        }
    }//end konstruktor

    private Connection getConnection() {

        try {
            if (conn == null || conn.isClosed()) {
                conn = DriverManager.getConnection(url, username, password);
            }
        } catch (SQLException sqx) {
            System.out.println(sqx.getMessage());
        }
        return conn;
    }

    private Statement getStatement() {
        try {

            if (stmt == null || stmt.isClosed()) {
                stmt = getConnection().createStatement();
            }
        } catch (SQLException sqx) {
            System.out.println(sqx.getMessage());
        }
        return stmt;


    }

    public void create(DTOBil dtobil) {
        try {

            try {

                conn = getConnection();
                //hämtar connection

                stmt = getStatement();
                // hämtar statement objekt



                //INSERT ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////


                String sqlInsert = "INSERT INTO bilar VALUES ('" + dtobil.regnummer + "','" + dtobil.bilmarke + "', '" + dtobil.modell + "'," + dtobil.ar
                        + "," + dtobil.pris + ",'" + dtobil.picURL + "')";

                stmt.executeUpdate(sqlInsert);


            }// end inre try
            finally {
                stmt.close();
                conn.close();

            }

        }//end try
        catch (SQLException sqex) {
            System.out.println(sqex.getMessage());
        }
    }

    public void update(DTOBil dtobil) {

        try {

            try {
                conn = getConnection();
                //hämtar connection

                stmt = getStatement();
                // hämtar statement objekt


                //UPDATE/////////////////////////////////////////////////////////////////////////////////////////////////////////////

                String sqlUpdate = "UPDATE bilar SET modell= '" + dtobil.modell + "', bilmarke ='" + dtobil.bilmarke + "',ar=" + dtobil.ar
                        + ",pris=" + dtobil.pris + ",bild='bildvolvo.jpg' " + "WHERE regnummer= '" + dtobil.regnummer + "'";


                stmt.executeUpdate(sqlUpdate);


            } finally {
                stmt.close();
                conn.close();

            }

        }//end try
        catch (SQLException sqex) {
            System.out.println(sqex.getMessage());
        }
    }

    public DTOBil read(String regnummer) {
        DTOBil dtobil = null;
        try {
            try {
                conn = getConnection();
                stmt = getStatement();


                //SELECT/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

                String sqlSelect = "SELECT * FROM bilar WHERE regnummer='" + regnummer + "'";

                ResultSet resultset = stmt.executeQuery(sqlSelect);
                resultset.next();
                dtobil = new DTOBil();

                dtobil.regnummer = resultset.getString("regnummer");
                dtobil.bilmarke = resultset.getString(2);
                dtobil.modell = resultset.getString(3);
                dtobil.ar = resultset.getInt(4);
                dtobil.pris = resultset.getInt(5);



            }//inre try
            finally {
                stmt.close();
                conn.close();
            }

        }//end try
        catch (SQLException sql) {
            System.out.println(sql.getMessage());
        }
        return dtobil;
    }

    public void delete(String regnummer) {

        try {
            try {
                conn = getConnection();
                stmt = getStatement();


                String sqlUpdate = "DELETE FROM bilar WHERE regnummer='" + regnummer + "'";

                stmt.executeUpdate(sqlUpdate);
            }//end inre try
            finally {
                stmt.close();
                conn.close();
            }



        }//try
        catch (SQLException sql) {
            System.out.println(sql.getMessage());
        }

    }

    public DTOBil[] getAllCars() {
        DTOBil dtobil = null;
        DTOBil[] dtobilar = null;

        try {
            try {
                conn = getConnection();
                stmt = getStatement();

                String sqlSelect = "SELECT * FROM bilar ORDER BY regnummer";
                ResultSet resultset = stmt.executeQuery(sqlSelect);
                resultset.last();

                int antalrader = resultset.getRow();
                dtobilar = new DTOBil[antalrader];
                int i = 0;

                resultset.beforeFirst();
                while (resultset.next()) {
                    dtobil = new DTOBil();

                    dtobil.regnummer = resultset.getString("regnummer");
                    dtobil.bilmarke = resultset.getString(2);
                    dtobil.modell = resultset.getString(3);
                    dtobil.ar = resultset.getInt(4);
                    dtobil.pris = resultset.getInt(5);
                    dtobilar[i] = dtobil;
                    i++;

                }//end while

            }//end inre
            finally {
                stmt.close();
                conn.close();
            }

        }//end try
        catch (SQLException sql) {
            System.out.println(sql.getMessage());
        }
        return dtobilar;

    }//end getAllCars

    public DTOBil[] getCarsByBrand(String marke) {
        DTOBil dtobil = null;
        DTOBil[] dtobilar = null;

        try {
            try {
                conn = getConnection();
                stmt = getStatement();

                String sqlSelect = "SELECT * FROM bilar WHERE marke='" + marke + "'";

                ResultSet resultset = stmt.executeQuery(sqlSelect);
                resultset.last();

                int antalrader = resultset.getRow();
                dtobilar = new DTOBil[antalrader];
                int i = 0;

                resultset.beforeFirst();
                while (resultset.next()) {
                    dtobil = new DTOBil();

                    dtobil.regnummer = resultset.getString("regnummer");
                    dtobil.bilmarke = resultset.getString(2);
                    dtobil.modell = resultset.getString(3);
                    dtobil.ar = resultset.getInt(4);
                    dtobil.pris = resultset.getInt(5);
                    dtobilar[i] = dtobil;
                    i++;
                }// end while

            }//end inre try
            finally {
                stmt.close();
                conn.close();
            }


        }//end try
        catch (SQLException sql) {
            System.out.println(sql.getMessage());
        }
        return dtobilar;
    }
}//end klassen