package framework.database;_x000D_
_x000D_
import java.lang.reflect.Constructor;_x000D_
import java.sql.Connection;_x000D_
import java.sql.Driver;_x000D_
import java.sql.DriverManager;_x000D_
import java.sql.PreparedStatement;_x000D_
import java.sql.ResultSet;_x000D_
import java.sql.ResultSetMetaData;_x000D_
import java.sql.SQLException;_x000D_
import java.util.ArrayList;_x000D_
import java.util.Enumeration;_x000D_
import java.util.HashMap;_x000D_
_x000D_
public class Database {_x000D_
_x000D_
    protected Connection conn;_x000D_
    protected String connectionUrl;_x000D_
    protected String username;_x000D_
    protected String password; // TODO: kijken in hoeverre het haalbaar is het wachtwoord hier weg te laten_x000D_
_x000D_
    @SuppressWarnings({ "unchecked", "rawtypes" })_x000D_
    public Database(String driver, String connectionUrl, String username, String password)_x000D_
    {_x000D_
        try {_x000D_
            // Kijken of de driver bestaat_x000D_
            Class DriverClass = Class.forName(driver);_x000D_
            _x000D_
            Constructor constructorClass = DriverClass.getConstructor();_x000D_
            Driver dbDriver = (Driver) constructorClass.newInstance();_x000D_
            DriverManager.registerDriver(dbDriver);_x000D_
            _x000D_
        } catch (Exception e) {_x000D_
            // TODO Exception-handling_x000D_
            e.printStackTrace();_x000D_
        }_x000D_
        _x000D_
        this.connectionUrl = connectionUrl;_x000D_
        this.username = username;_x000D_
        this.password = password;_x000D_
    }_x000D_
_x000D_
    /**_x000D_
     * Maakt connectie naar de databank_x000D_
     * _x000D_
     * @return_x000D_
     * @throws SQLException_x000D_
     */_x000D_
    public Connection getConnection() throws SQLException_x000D_
    {_x000D_
        if(this.conn == null || this.conn.isClosed())_x000D_
            this.conn = DriverManager.getConnection(connectionUrl,username,password);_x000D_
        _x000D_
        return this.conn;_x000D_
    }_x000D_
_x000D_
    public PreparedStatement prepareStatement(String query) throws SQLException_x000D_
    {_x000D_
        return conn.prepareStatement(query);_x000D_
    }_x000D_
_x000D_
    public NamedParamStatement namedParamStatement(String query) throws SQLException_x000D_
    {_x000D_
        return new NamedParamStatement(getConnection(), query);_x000D_
    }_x000D_
_x000D_
    /**_x000D_
     * Geeft alle rows van een result in een handige ArrayList_x000D_
     * _x000D_
     * @param res_x000D_
     * @return_x000D_
     * @throws SQLException_x000D_
     */_x000D_
    public ArrayList<DatabaseRow> getAllRows(ResultSet res) throws SQLException_x000D_
    {_x000D_
        ArrayList<DatabaseRow> rows = new ArrayList<DatabaseRow>();_x000D_
_x000D_
        // Cursor naar -1 zetten._x000D_
        res.beforeFirst();_x000D_
                _x000D_
        // Kijken of er op index 0 iets zit._x000D_
        if(res.next()){_x000D_
            // Blijkbaar zijn er rows_x000D_
            // Cursor terug naar -1 zetten alle rows aflopen._x000D_
            res.beforeFirst();_x000D_
            _x000D_
            // Pas als we aan de EOL zitten van de resultset stoppen we_x000D_
            while(!res.isAfterLast()) {_x000D_
                // Row ophalen en toevoegen aan Arraylist_x000D_
                DatabaseRow row = this.getRow(res);_x000D_
                if(row != null)_x000D_
                    rows.add(row);_x000D_
            }_x000D_
        }_x000D_
_x000D_
        res.close();_x000D_
_x000D_
        return rows;_x000D_
    }_x000D_
_x000D_
    /**_x000D_
     * Haalt de volgende row uit een resultset een geeft dit terug in een Hashmap<Kolomnaam,Object>_x000D_
     * _x000D_
     * @param res_x000D_
     * @return_x000D_
     * @throws SQLException_x000D_
     */_x000D_
    public DatabaseRow getRow(ResultSet res) throws SQLException_x000D_
    {_x000D_
        HashMap<String, Object> row = new HashMap<String, Object>();_x000D_
_x000D_
        // MetaData is nodig, daarin zit het aantal kolommen en hoe ze heten_x000D_
        ResultSetMetaData metaData = res.getMetaData();_x000D_
        int columnCount = metaData.getColumnCount();_x000D_
_x000D_
        ArrayList<String> columns = new ArrayList<String>();_x000D_
_x000D_
        // Alle kolommen aflopen en hun naam bijhouden in de Array._x000D_
        for (int columnNr=1; columnNr<=columnCount; columnNr++){_x000D_
            String columnName = metaData.getColumnName(columnNr);_x000D_
            columns.add(columnName);_x000D_
        }_x000D_
_x000D_
        // Springen naar de volgende row_x000D_
        if (res.next()){_x000D_
            // Alle kolommen in deze row aflopen_x000D_
            for (String columnName:columns) {_x000D_
                // De waarde van deze kolom ophalen_x000D_
                Object rowData = res.getObject(columnName);_x000D_
                // Toevoegen aan de hashmap_x000D_
                row.put(columnName,rowData);_x000D_
            }_x000D_
        }_x000D_
        _x000D_
        if(row.size() <= 0)_x000D_
            return null;_x000D_
_x000D_
        return new DatabaseRow(row);_x000D_
    }_x000D_
    _x000D_
    public void closeConnection() {_x000D_
        try {_x000D_
            conn.close();_x000D_
            _x000D_
            // This manually deregisters JDBC driver, which prevents Tomcat 7_x000D_
            // from complaining about memory leaks wrto this class_x000D_
            // http://stackoverflow.com/a/5315467/1306509_x000D_
            Enumeration<Driver> drivers = DriverManager.getDrivers();_x000D_
            while (drivers.hasMoreElements()) {_x000D_
                Driver driver = drivers.nextElement();_x000D_
                DriverManager.deregisterDriver(driver);_x000D_
            }_x000D_
_x000D_
        } catch (SQLException e) {_x000D_
            // TODO Exception-handling_x000D_
            e.printStackTrace();_x000D_
        }_x000D_
    }_x000D_
}_x000D_

// MetaData is nodig, daarin zit het aantal kolommen en hoe ze heten_x000D_
package framework.database;_x000D_
_x000D_
import java.lang.reflect.Constructor;_x000D_
import java.sql.Connection;_x000D_
import java.sql.Driver;_x000D_
import java.sql.DriverManager;_x000D_
import java.sql.PreparedStatement;_x000D_
import java.sql.ResultSet;_x000D_
import java.sql.ResultSetMetaData;_x000D_
import java.sql.SQLException;_x000D_
import java.util.ArrayList;_x000D_
import java.util.Enumeration;_x000D_
import java.util.HashMap;_x000D_
_x000D_
public class Database {_x000D_
_x000D_
    protected Connection conn;_x000D_
    protected String connectionUrl;_x000D_
    protected String username;_x000D_
    protected String password; // TODO: kijken in hoeverre het haalbaar is het wachtwoord hier weg te laten_x000D_
_x000D_
    @SuppressWarnings({ "unchecked", "rawtypes" })_x000D_
    public Database(String driver, String connectionUrl, String username, String password)_x000D_
    {_x000D_
        try {_x000D_
            // Kijken of de driver bestaat_x000D_
            Class DriverClass = Class.forName(driver);_x000D_
            _x000D_
            Constructor constructorClass = DriverClass.getConstructor();_x000D_
            Driver dbDriver = (Driver) constructorClass.newInstance();_x000D_
            DriverManager.registerDriver(dbDriver);_x000D_
            _x000D_
        } catch (Exception e) {_x000D_
            // TODO Exception-handling_x000D_
            e.printStackTrace();_x000D_
        }_x000D_
        _x000D_
        this.connectionUrl = connectionUrl;_x000D_
        this.username = username;_x000D_
        this.password = password;_x000D_
    }_x000D_
_x000D_
    /**_x000D_
     * Maakt connectie naar de databank_x000D_
     * _x000D_
     * @return_x000D_
     * @throws SQLException_x000D_
     */_x000D_
    public Connection getConnection() throws SQLException_x000D_
    {_x000D_
        if(this.conn == null || this.conn.isClosed())_x000D_
            this.conn = DriverManager.getConnection(connectionUrl,username,password);_x000D_
        _x000D_
        return this.conn;_x000D_
    }_x000D_
_x000D_
    public PreparedStatement prepareStatement(String query) throws SQLException_x000D_
    {_x000D_
        return conn.prepareStatement(query);_x000D_
    }_x000D_
_x000D_
    public NamedParamStatement namedParamStatement(String query) throws SQLException_x000D_
    {_x000D_
        return new NamedParamStatement(getConnection(), query);_x000D_
    }_x000D_
_x000D_
    /**_x000D_
     * Geeft alle rows van een result in een handige ArrayList_x000D_
     * _x000D_
     * @param res_x000D_
     * @return_x000D_
     * @throws SQLException_x000D_
     */_x000D_
    public ArrayList<DatabaseRow> getAllRows(ResultSet res) throws SQLException_x000D_
    {_x000D_
        ArrayList<DatabaseRow> rows = new ArrayList<DatabaseRow>();_x000D_
_x000D_
        // Cursor naar -1 zetten._x000D_
        res.beforeFirst();_x000D_
                _x000D_
        // Kijken of er op index 0 iets zit._x000D_
        if(res.next()){_x000D_
            // Blijkbaar zijn er rows_x000D_
            // Cursor terug naar -1 zetten alle rows aflopen._x000D_
            res.beforeFirst();_x000D_
            _x000D_
            // Pas als we aan de EOL zitten van de resultset stoppen we_x000D_
            while(!res.isAfterLast()) {_x000D_
                // Row ophalen en toevoegen aan Arraylist_x000D_
                DatabaseRow row = this.getRow(res);_x000D_
                if(row != null)_x000D_
                    rows.add(row);_x000D_
            }_x000D_
        }_x000D_
_x000D_
        res.close();_x000D_
_x000D_
        return rows;_x000D_
    }_x000D_
_x000D_
    /**_x000D_
     * Haalt de volgende row uit een resultset een geeft dit terug in een Hashmap<Kolomnaam,Object>_x000D_
     * _x000D_
     * @param res_x000D_
     * @return_x000D_
     * @throws SQLException_x000D_
     */_x000D_
    public DatabaseRow getRow(ResultSet res) throws SQLException_x000D_
    {_x000D_
        HashMap<String, Object> row = new HashMap<String, Object>();_x000D_
_x000D_
        // MetaData is<SUF>
        ResultSetMetaData metaData = res.getMetaData();_x000D_
        int columnCount = metaData.getColumnCount();_x000D_
_x000D_
        ArrayList<String> columns = new ArrayList<String>();_x000D_
_x000D_
        // Alle kolommen aflopen en hun naam bijhouden in de Array._x000D_
        for (int columnNr=1; columnNr<=columnCount; columnNr++){_x000D_
            String columnName = metaData.getColumnName(columnNr);_x000D_
            columns.add(columnName);_x000D_
        }_x000D_
_x000D_
        // Springen naar de volgende row_x000D_
        if (res.next()){_x000D_
            // Alle kolommen in deze row aflopen_x000D_
            for (String columnName:columns) {_x000D_
                // De waarde van deze kolom ophalen_x000D_
                Object rowData = res.getObject(columnName);_x000D_
                // Toevoegen aan de hashmap_x000D_
                row.put(columnName,rowData);_x000D_
            }_x000D_
        }_x000D_
        _x000D_
        if(row.size() <= 0)_x000D_
            return null;_x000D_
_x000D_
        return new DatabaseRow(row);_x000D_
    }_x000D_
    _x000D_
    public void closeConnection() {_x000D_
        try {_x000D_
            conn.close();_x000D_
            _x000D_
            // This manually deregisters JDBC driver, which prevents Tomcat 7_x000D_
            // from complaining about memory leaks wrto this class_x000D_
            // http://stackoverflow.com/a/5315467/1306509_x000D_
            Enumeration<Driver> drivers = DriverManager.getDrivers();_x000D_
            while (drivers.hasMoreElements()) {_x000D_
                Driver driver = drivers.nextElement();_x000D_
                DriverManager.deregisterDriver(driver);_x000D_
            }_x000D_
_x000D_
        } catch (SQLException e) {_x000D_
            // TODO Exception-handling_x000D_
            e.printStackTrace();_x000D_
        }_x000D_
    }_x000D_
}_x000D_
