/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package pkgData;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;

/**
 *
 * @author schueler
 */
public class Database {

    private static final String url = "jdbc:oracle:thin:d4b06/d4b@192.168.128.152:1521:test";
    private String ip = null;
    private static Database db = null;
    private static final String USER = "schueler";
    private static final String PASSWD = "schueler";
    private Connection conn = null;

    private Database() {}

    public Database newInstance(String _ip) {
        if (db == null) {
            db = new Database();
            ip = _ip;
        }
        return db;
    }

    public void createConnection() throws SQLException {
        if(conn == null) {
            DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
        }

        conn = DriverManager.getConnection(url, USER, PASSWD);
    }

    public Collection<Quiz> getQuizzes() throws SQLException {
        ArrayList<Quiz> collQuiz = new ArrayList<>();
        String select = "select quid, bezeichnung from quiz order by bezeichnung";

        createConnection();

        Statement stmt = (Statement) conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        ResultSet rs = stmt.executeQuery(select);

        while(rs.next()) {
            Quiz quiz = new Quiz(rs.getInt("qid"), rs.getString("bezeichnung"));

            collQuiz.add(quiz);
        }

        conn.close();
        return collQuiz;
    }

    public void addQuiz(Quiz quiz) throws SQLException {
        String insert = "insert into quiz values(?,?)";

        createConnection();

        PreparedStatement stmt = conn.prepareStatement(insert);
        stmt.setInt(1, quiz.getIdQuiz());
        stmt.setString(2, quiz.getQuizName());
        stmt.executeUpdate();
        conn.close();
    }

    public void updateQuiz(Quiz quiz) throws SQLException {
        String update = "update quiz set bezeichnung = ?" + "where qid = ?";

        createConnection();

        PreparedStatement stmt = conn.prepareStatement(update);
        stmt.setString(2, quiz.getQuizName());
        stmt.executeUpdate();
        conn.close();
    }
}