Lieferbezirk

import java.sql.*;

public class Lieferbezirk {
    static Connection conn;
    String url = "jdbc:mysql://127.0.0.1:3306/venenumBonus";

    private String _lieferbezirkNummer;

    public Lieferbezirk(String lieferbezirk) {
        _lieferbezirkNummer = lieferbezirk;

        OpenDBConnection();
    }

    private void OpenDBConnection() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, "root", "root");

        } catch (SQLException e) {
            System.out.println("*** SQLException:\n" + e);
            e.printStackTrace();
        } catch (Exception e) {
            System.out.println("*** Exception:\n" + e); e.printStackTrace();
        }
    }

    private void CloseDBConnection() {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                System.out.println("*** SQLException:\n" + e);
                e.printStackTrace();
            } catch (Exception e) {
                System.out.println("*** Exception:\n" + e); e.printStackTrace();
            }
        }
    }

    public String getCurrentLieferbezirkNummer() {
        return _lieferbezirkNummer;
    }

    public int getLieferbezirkNummerCount() {
        String statement = "SELECT count(*) AS totalCount FROM lieferer WHERE plz = ?";

        return queryDatabase(statement);
    }

    public int getAnzahlAbgeschlosseneLieferungen() {
        String statement = "SELECT COUNT(*)AS totalCount FROM lieferer, liefererbestaetigung, bestellung WHERE lieferer.idLieferer = liefererbestaetigung.Lieferer_idLieferer AND liefererbestaetigung.Bestellung_idBestellung = bestellung.idBestellung AND lieferer.plz = ? AND bestellung.bestellstatus = 'abgeschlossen'";

        return queryDatabase(statement);
    }

    public int getDurchschnittlicheBestellsumme() {
        //String statement = "SELECT AVG(idBestellung) FROM lieferer WHERE plz = ? ";
        String statement = "SELECT count(*) AS totalCount FROM lieferer WHERE plz = ?";

        return queryDatabase(statement);
    }

    public int queryDatabase(String _statement) {
        PreparedStatement ps = null;
        int count = 0;

        String statement = _statement;

        try {
            ps = conn.prepareStatement(statement);
            ps.setString(1, getCurrentLieferbezirkNummer());
            ResultSet rs = ps.executeQuery();

            while(rs.next()) {
                count = rs.getInt("totalCount");
            }
            return count;

        } catch (SQLException e) {
            System.out.println("*** SQLException:\n" + e);
            e.printStackTrace();
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            CloseDBConnection();
        }

        return count;
    }
}