databaseEngine (UCanAccess)

package database;

import com.healthmarketscience.jackcess.Database;
import com.healthmarketscience.jackcess.Database.FileFormat;
import com.healthmarketscience.jackcess.DatabaseBuilder;
import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import net.ucanaccess.jdbc.DBReference;
import net.ucanaccess.jdbc.JackcessOpenerInterface;
import net.ucanaccess.jdbc.Session;
import net.ucanaccess.jdbc.UcanaccessConnection;
import net.ucanaccess.jdbc.UcanaccessPreparedStatement;
import net.ucanaccess.jdbc.UcanaccessResultSet;
import sports.day.results.Preferences;


public class databaseEngine {

    UcanaccessConnection uCon;
    Connection con;
    Statement stmt;
    ResultSet rs;
    UcanaccessResultSet urs;

    public void connect(String dbFile, String pass) {
        String url;
        DBReference dbref = null;
        FileFormat ff;
        JackcessOpenerInterface jko = new Opener();
        Session sn = new Session();
        try {
            Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(databaseEngine.class.getName()).log(Level.SEVERE, null, ex);
        }
        url = "jdbc:ucanaccess://" + dbFile;
        ff = FileFormat.V2010;
        sn.setUser("");
        sn.setPassword(pass);
        try {
            dbref = new DBReference(getDbFile(dbFile), ff, jko, "");
        } catch (IOException | SQLException ex) {
            Logger.getLogger(databaseEngine.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {
            con = DriverManager.getConnection(url, "", pass);
            uCon = new UcanaccessConnection(dbref, con.getClientInfo(), sn);
            stmt = uCon.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
        } catch (SQLException err) {
            System.out.println(err.getMessage());
        }
    }

    public class Opener implements JackcessOpenerInterface {
        public Database open(File fl, String pwd) throws IOException {
            DatabaseBuilder dbd = new DatabaseBuilder(fl);
            dbd.setAutoSync(true);
            //only for encrypted mdb: dbd.setCodecProvider(new CryptCodecProvider(pwd));
            dbd.setReadOnly(false);
            return dbd.open();
        }
    }

    public ResultSet getTableMetaData(String table) {
        Preferences pref = new Preferences();
        connect(pref.getPreference("dbFileName"), pref.getPreference("dbPassword"));
        try {
            DatabaseMetaData meta = con.getMetaData();
            rs = meta.getTables(null, null, table, null);
        } catch (SQLException ex) {
            Logger.getLogger(databaseEngine.class.getName()).log(Level.SEVERE, null, ex);
        }
        return rs;
    }

    public UcanaccessPreparedStatement prepareStatement(String sql) {
        Preferences pref = new Preferences();
        connect(pref.getPreference("dbFileName"), pref.getPreference("dbPassword"));
        PreparedStatement pstmnt;
        UcanaccessPreparedStatement stmnt;
        try {
            pstmnt = con.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
            stmnt = new UcanaccessPreparedStatement(pstmnt, uCon);
            return stmnt;
        } catch (SQLException ex) {
            Logger.getLogger(databaseEngine.class.getName()).log(Level.SEVERE, null, ex);
        }
        closeConnection();
        return null;
    }

    public void updateDatabase(UcanaccessPreparedStatement stmnt) {
        try {
            stmnt.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(databaseEngine.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public UcanaccessResultSet fetchResults(UcanaccessPreparedStatement stmnt) {
        try {
            rs = stmnt.executeQuery();
            urs = new UcanaccessResultSet(rs, stmnt);
        } catch (SQLException ex) {
            Logger.getLogger(databaseEngine.class.getName()).log(Level.SEVERE, null, ex);
        }
        return urs;
    }

    public boolean testConnection(String filename, String pass) {
        connect(filename, pass);
        PreparedStatement pstmnt;
        UcanaccessPreparedStatement stmnt;
        try {
            pstmnt = con.prepareStatement("SELECT * FROM events", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
            stmnt = new UcanaccessPreparedStatement(pstmnt, uCon);
            rs = stmnt.executeQuery();
            urs = new UcanaccessResultSet(rs, stmnt);
        } catch (SQLException err) {
            System.out.println(err.getMessage());
            closeConnection();
            return false;
        }
        closeConnection();
        return true;
    }

    public boolean exists(String filePath) {
        return new File(filePath).exists();
    }

    public String getDbFilePath(String fileName) {
        File f = new File(fileName);
        String dbFilePath = (f.getAbsolutePath().substring(0, (int) (f.getAbsolutePath().length()) - 20).concat("Database/sportsdayData.accdb"));
        return dbFilePath;
    }

    public File getDbFile(String fileName) {
        File f = new File(fileName);
        return f;
    }

    public void closeConnection() {
        try {
            uCon.close();
            stmt.close();
            con.close();
            rs.close();
            urs.close();
        } catch (SQLException ex) {
            Logger.getLogger(databaseEngine.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}