source: main/trunk/model-sites-dev/cambridge-museum/collect/waikato-independent/pre-import/EditableDatabaseTable/src/jquery/datatables/model/DataRepository.java@ 34511

package jquery.datatables.model;

import java.sql.*;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

public class DataRepository
{
    private static String JDBC_URI      = null;
    private static String JDBC_DB_TABLE = null;
    
    static {
    try {
        // Included to trigger maven including the necessary Java class and native files
        // in the generated 'with-dependencies.jar' file

        // Looks like this step could be avoided if more details are stored in the maven
        // pom.xml file:
        //    https://stackoverflow.com/questions/16725377/no-suitable-driver-found-sqlite
        
        Class.forName("org.sqlite.JDBC");
    }
    catch (Exception e) {
        e.printStackTrace();
    }
    
    }
    
    /// <summary>
    /// Singleton collection of companies
    /// </summary>
    private static List<RecordHashmap> RecordDataList = null;

    private static ArrayList<String> _columnLabels = null; 
    
    protected static ArrayList<String> initJDBCColumnLabels(Connection connection, String table_name)
    {
    // Read in column labels into ArrayList
    ArrayList<String> column_labels = null;

    try {
        Statement st = connection.createStatement();
        ResultSet rs = st.executeQuery("SELECT * from " + table_name);
        
        ResultSetMetaData rs_md = rs.getMetaData();
        
        int columnCount = rs_md.getColumnCount();                     

        column_labels = new ArrayList<String>(columnCount);
    
        for (int i=1; i<=columnCount; i++) {
        String heading = rs_md.getColumnLabel(i);
        column_labels.add(heading); // add even if null, so can keep count
        if (heading == null) {
            System.err.println("*** Encountered null column label at column postion: " + i);
        }
        }
    }
    catch (Exception e) {
        e.printStackTrace();
    }
    
    return column_labels;
    }
    
    public static ArrayList<String> getColumnHeadings()
    {
    return _columnLabels;
    }
    
    public static void init(String jdbc_uri, String jdbc_db_table)
    {
    JDBC_URI = jdbc_uri;
    JDBC_DB_TABLE = jdbc_db_table;

    System.err.println("Opening: '" + JDBC_URI + "' ...");
    try (Connection connection = DriverManager.getConnection(JDBC_URI)) {
        System.err.println("... connnected");

        _columnLabels =  initJDBCColumnLabels(connection, JDBC_DB_TABLE);

        connection.close();
    }
    catch (SQLException ex) {
        ex.printStackTrace();
    }
    }
    

    /// <summary>
    /// Method that returns all companies used in this example
    /// </summary>
    /// <returns>List of companies</returns>
    public static List<RecordHashmap> GetRecords()
    {
    if (RecordDataList == null) {
        System.err.println("Opening: '" + JDBC_URI + "' to query ...");
        
        try (Connection connection = DriverManager.getConnection(JDBC_URI)) {
        System.err.println("... connnected");

        if (_columnLabels == null) {
            System.err.println("Error: Column Labels should already be initialized, but are null");
            return null;
        }
        else {
            int column_count = _columnLabels.size();

            if (column_count>=2) {
            Statement input_statement = connection.createStatement();
            ResultSet input_resultset = input_statement.executeQuery("SELECT * from " + JDBC_DB_TABLE);
            
            RecordDataList = new LinkedList<RecordHashmap>();
            
            int line_count = 0;
            
            while (input_resultset.next()) {
                
                int id = input_resultset.getInt(1);
                RecordHashmap record_hashmap = new RecordHashmap(id, column_count);
                
                for (int i=2; i<=column_count; i++) {
                String val = input_resultset.getString(i);
                String key = _columnLabels.get(i-1); // columnLabels arraylist is from 0, not 1 like sql columns
                
                record_hashmap.setFieldValue(key,val);
                }
                
                RecordDataList.add(record_hashmap);
                
                line_count++;
                if ((line_count % 1000) == 0) {
                System.out.println("Processed lines " + (line_count - 999) + "-" + line_count);
                }
                
                if (line_count == 5000) {
                // ******
                System.err.println("******* During development, artifically cutting off the Database read in at 5000 entries"); 
                break;
                }
            }
            }
            else {
            System.err.println("Warning: ignoring database table as it only has one column to it");
            }
            
        }
        
        connection.close();
        }
        catch (SQLException ex) {
            ex.printStackTrace();
        }

    }
    
        return RecordDataList;
    }

}