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

public class HSQLDBConnector {

    private static String driver = "org.hsqldb.jdbcDriver";

    private static String TEST_DB = "bdd_test";

    private static String dbName;

    private static HSQLDBConnector hsqldbConnector;

    private String nomBDD;

    // ***********************************************************************
    private HSQLDBConnector() {
    }

    public static HSQLDBConnector getInstance() {
        if (hsqldbConnector == null) {
            hsqldbConnector = new HSQLDBConnector();
        }
        return hsqldbConnector;
    }

    public void setBDD(String nomBDD) {
        this.nomBDD = nomBDD;
    }

    // ***********************************************************************
    private Connection getConnection() throws ClassNotFoundException,
            SQLException {
        if (nomBDD == null || nomBDD.trim().equals("")) {
            nomBDD = TEST_DB;
        }
        dbName = "./datas/" + nomBDD;
        Class.forName(driver);
        return DriverManager.getConnection("jdbc:hsqldb:file:" + dbName, "sa",
                "");
    }

    private void close(Connection connection, Statement statement,
            ResultSet resultSet) {
        try {
            if (connection != null) {
                connection.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (resultSet != null) {
                resultSet.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private void close(Connection connection, ResultSet resultSet) {
        close(connection, null, resultSet);
    }

    private void close(Connection connection, Statement statement) {
        close(connection, statement, null);
    }

    // ***********************************************************************
    public boolean existe(String nomTable) {
        boolean existe = false;
        Connection connection = null;
        ResultSet tables = null;
        try {
            connection = getConnection();
            DatabaseMetaData dmd = connection.getMetaData();
            tables = dmd.getTables(connection.getCatalog(), null, nomTable,
                    null);
            existe = tables.next();
            tables.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(connection, tables);
        }
        return existe;
    }

    // ***********************************************************************
    public void creerTableClients() {
        Connection connection = null;
        Statement st = null;
        try {
            connection = getConnection();
            st = connection.createStatement();
            int erreur = st
                    .executeUpdate("CREATE TABLE TABLE_CLIENT "
                            + "(prenom VARCHAR(50), nom VARCHAR(50), adresse VARCHAR(100))");
            if (erreur == -1)
                System.out.println("Erreur BDD !");
            System.out.println("On a créé la table 'table_client'.");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(connection, st);
        }
    }

    // ***********************************************************************
    public void insererUnClientDansBDD(String prenom, String nom, String adresse) {
        Connection connection = null;
        Statement st = null;
        try {
            connection = getConnection();
            st = connection.createStatement();
            int erreur = st.executeUpdate("INSERT INTO TABLE_CLIENT VALUES ( '"
                    + prenom + "', '" + nom + "', '" + adresse + "' )");
            if (erreur == -1)
                System.out.println("Erreur BDD !");
            System.out.println("On a inséré une ligne dans 'table_client'.");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(connection, st);
        }
    }

    // ***********************************************************************
    public void afficherTousLesClientsVersConsole() {
        String prenom, nom, adresse;
        Connection connection = null;
        Statement statement = null;
        try {
            connection = getConnection();
            statement = connection.createStatement();
            String query = "SELECT * FROM TABLE_CLIENT";
            ResultSet rs = statement.executeQuery(query);
            System.out.println("----------------------------------------");
            while (rs.next()) {
                prenom = rs.getString(1);
                nom = rs.getString(2);
                adresse = rs.getString(3);
                System.out.println(prenom + " : " + nom + " : " + adresse);
            }
            System.out.println("----------------------------------------");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(connection, statement);
        }
    }
    // ***********************************************************************
}