SQLiteManager.java

/*
 * 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 ocr3;

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 javafx.scene.control.Alert;
import javafx.scene.control.Alert.AlertType;



/**
 *
 * @author gsb
 */
public class SQLiteManager {
    private static Connection connection;
    private static boolean hasData = false;
    
    public static void showWholeDataFromTable(String catagoryName, String dbName){
        //SQLiteTest sqliteTest = new SQLiteTest();
        ResultSet resultSet;
         
        try {
            resultSet = getWholeDataFromTable(catagoryName, dbName);

            while (resultSet.next()) {
                   System.out.println(resultSet.getString("characterKey") 
                           + " " + resultSet.getString("character"));
                }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    private static ResultSet getWholeDataFromTable(String catagoryName, String dbName) throws SQLException, ClassNotFoundException{
        if(connection == null){
            getConnection(dbName);
        }
        
        Statement statement = connection.createStatement();
        ResultSet rSet = statement.executeQuery(
//                statement.executeQuery("SELECT characterKey FROM "+catagoryName);
                "SELECT characterKey, character FROM "+catagoryName+" ORDER BY characterKey");
        return rSet;
    }
    
    //Before calling it, you should verify that the table exists
    public static ArrayList<String[]> getAllRowsFromTable(String tableName, String dbName) throws SQLException, ClassNotFoundException{
        ResultSet res = getWholeDataFromTable(tableName, dbName);
        
        ArrayList<String[]> rowList = new ArrayList<>();
        while (res.next()) {
                   String cKey = res.getString("characterKey");
                   String c = res.getString("character");
                   String[] row = {cKey, c};
                   rowList.add(row);
                }
        return rowList;
    }
    
    private static void getConnection(String dbName) throws ClassNotFoundException, SQLException{
        //SQLite driver
        Class.forName("org.sqlite.JDBC");
        // database path, if it's new database, it will be created in the project folder
        connection = DriverManager.getConnection("jdbc:sqlite:"+dbName);
//        createCatagoryTable(catagoryName);
        
    }
    
    public static void addCharacterToDB(String catagoryName, String dbName,
            String characterKey, String character) 
            throws ClassNotFoundException, SQLException {
        if(connection == null) {
            // get connection
            getConnection(dbName);
        }
         PreparedStatement prep = connection
                   .prepareStatement("INSERT INTO "+catagoryName+" VALUES(?,?,?);");
                 prep.setString(2, characterKey);
                 prep.setString(3, character);
                 prep.execute();

    }
    
    private static void initialize(String catagoryName) throws SQLException {
        if( !hasData ) {
            hasData = true;
            // check for database table
            Statement state = connection.createStatement();
            ResultSet res = state.executeQuery("SELECT name FROM sqlite_master WHERE "
                    + "type='table' AND name='"+catagoryName+"'");
            if( !res.next()) {
                System.out.println("Building the table with prepopulated values.");
                // need to build the table
                 Statement state2 = connection.createStatement();
                 state2.executeUpdate("create table "+catagoryName+"(id integer,"
                   + "characterKey varchar(255)," + "character varchar(200)," 
                         + "primary key (id));");
            }

        }
    }
    
    public static void createCatagoryTable(String catagoryName, String dbName) throws SQLException, ClassNotFoundException{
        if(connection == null){
            getConnection(dbName);
        }
        Statement statement = connection.createStatement();
        statement.executeUpdate("create table "+catagoryName+"(id integer,"
                   + "characterKey varchar(255)," + "character varchar(200)," 
                         + "primary key (id));");
        
        //Check if the table actually created
        boolean result = catagoryTableExists(catagoryName, dbName);
        if(result) System.out.println("Table really created!");
        else System.out.println("Sorry, table creation failed for no reason.");
    }
    
    //checks whether a table exists, if not create a table
    public static boolean catagoryTableExists(String catagoryName, String dbName) throws SQLException, ClassNotFoundException {
        if(connection == null){
            getConnection(dbName);
        }
        
        
        Statement state = connection.createStatement();
        ResultSet res = state.executeQuery("SELECT name FROM sqlite_master WHERE "
                + "type='table' AND name='"+catagoryName+"'");
        if( !res.next()) {
            System.out.println("No table found in this catagory.");
            Alert alert = new Alert(AlertType.INFORMATION);
            alert.setContentText("No table found in this catagory");
            alert.showAndWait();
            return false;
        } else return true;
    }
    
    public static void deleteTableFromDB(String catagoryName, String dbName)throws SQLException, ClassNotFoundException{
        if(connection == null){
            getConnection(dbName);
        }
        Statement state = connection.createStatement();
        state.executeUpdate("DROP TABLE IF EXISTS "+catagoryName);
    }
    
    public static void deleteRowFromTable(String chKey, String table, String dbName) throws SQLException, ClassNotFoundException{
        
        if(!chKey.isEmpty()){
            if(connection == null){
                getConnection(dbName);
            }
            Statement state = connection.createStatement();
            state.executeUpdate("DELETE FROM "+table+" WHERE characterKey='"+chKey+"'");
        }
        
    }
    
    public static ArrayList<String> getAllTableNamesFromDB(String dbName) throws SQLException, ClassNotFoundException{
        if(connection == null){
            getConnection(dbName);
        }
        
        //We give a choicebox for all tables
        ArrayList<String> tableList = new ArrayList<>();
        
        Statement state = connection.createStatement();
        ResultSet res = state.executeQuery("SELECT * FROM sqlite_master WHERE "
                + "type='table' ORDER BY name ASC");
        
        while(res.next()){
            tableList.add(res.getString("name"));
        }
        
        if(tableList.isEmpty()){
            System.out.println("No table found in the database.");
            Alert alert = new Alert(AlertType.INFORMATION);
            alert.setContentText("No table found in the database!");
            alert.showAndWait();
        }
        
//        while(!res.next()){
//            System.out.println("No table found in the database.");
//            Alert alert = new Alert(AlertType.INFORMATION);
//            alert.setContentText("No table found in the database!");
//            alert.showAndWait();
//        }
//        
//        else if(res.next()){
//            tableList.add(res.getString("name"));
//        }
//        
        return tableList;
    }
    
    public static String getCharacterFromDB(String catagoryName, String dbName,
            String characterKey) 
            throws ClassNotFoundException, SQLException{
        String character = "";
        ResultSet resultSet = getWholeDataFromTable(catagoryName, dbName);
        
        //String res = resultSet.getArray(character).;
        
        
        while (resultSet.next()) {
               
               String rowId = resultSet.getRow()+"";
               String ckey = resultSet.getString("characterKey");
               String c = resultSet.getString("character");
               
              // System.out.println("DB row "+rowId+". "+ckey+" "+c);
              // System.out.println("Image CharacterKey: "+characterKey);
               //if(c)
               
               if(characterKey.equals(ckey)){
                   character = c;
                   return character;
               }
               
            }
        
        return character;
    }
     
}