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

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author ugnich
 */
public class SQLHelpers {

    private static final Logger logger = Logger.getLogger(SQLHelpers.class.getName());

    public static int execute(Connection sql, String query) {
        int ret = -1;
        PreparedStatement stmt = null;
        try {
            stmt = sql.prepareStatement(query);
            ret = stmt.executeUpdate();
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "sql exception", e);
        } finally {
            Utils.finishSQL(null, stmt);
        }
        return ret;
    }

    public static int executeInt(Connection sql, String query, int param) {
        int ret = -1;
        PreparedStatement stmt = null;
        try {
            stmt = sql.prepareStatement(query);
            stmt.setInt(1, param);
            ret = stmt.executeUpdate();
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "sql exception", e);
        } finally {
            Utils.finishSQL(null, stmt);
        }
        return ret;
    }

    public static int getInt(Connection sql, String query, int defvalue) {
        int ret = defvalue;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = sql.prepareStatement(query);
            rs = stmt.executeQuery();
            if (rs.first()) {
                ret = rs.getInt(1);
            }
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "sql exception", e);
        } finally {
            Utils.finishSQL(rs, stmt);
        }
        return ret;
    }

    public static int getInt(Connection sql, String query, int param, int defvalue) {
        int ret = defvalue;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = sql.prepareStatement(query);
            stmt.setInt(1, param);
            rs = stmt.executeQuery();
            if (rs.first()) {
                ret = rs.getInt(1);
            }
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "sql exception", e);
        } finally {
            Utils.finishSQL(rs, stmt);
        }
        return ret;
    }

    public static int getInt(Connection sql, String query, String param, int defvalue) {
        int ret = defvalue;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = sql.prepareStatement(query);
            stmt.setString(1, param);
            rs = stmt.executeQuery();
            if (rs.first()) {
                ret = rs.getInt(1);
            }
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "sql exception", e);
        } finally {
            Utils.finishSQL(rs, stmt);
        }
        return ret;
    }

    public static String getString(Connection sql, String query, int param) {
        String ret = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = sql.prepareStatement(query);
            stmt.setInt(1, param);
            rs = stmt.executeQuery();
            if (rs.first()) {
                ret = rs.getString(1);
            }
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "sql exception", e);
        } finally {
            Utils.finishSQL(rs, stmt);
        }
        return ret;
    }

    public static String getString(Connection sql, String query, String param) {
        String ret = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = sql.prepareStatement(query);
            stmt.setString(1, param);
            rs = stmt.executeQuery();
            if (rs.first()) {
                ret = rs.getString(1);
            }
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "sql exception", e);
        } finally {
            Utils.finishSQL(rs, stmt);
        }
        return ret;
    }

    public static List<Integer> getArrayInteger(Connection sql, String query, int param) {
        List<Integer> ret = new ArrayList<>();

        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = sql.prepareStatement(query);
            stmt.setInt(1, param);
            rs = stmt.executeQuery();
            rs.beforeFirst();
            while (rs.next()) {
                ret.add(rs.getInt(1));
            }
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "sql exception", e);
        } finally {
            Utils.finishSQL(rs, stmt);
        }

        return ret;
    }
}