Search in sources :

Example 1 with Data

use of dna.dataStructures.Data in project dna by leifeld.

the class SqlConnection method getAllData.

/**
 * @return     Data object.
 */
public Data getAllData() {
    Data data = new Data();
    data.setSettings(getAllSettings());
    data.setDocuments(getAllDocuments());
    data.setCoders(getAllCoders());
    data.setCoderRelations(getAllCoderRelations());
    data.setRegexes(getAllRegexes());
    data.setStatementLinks(getAllStatementLinks());
    data.setStatementTypes(getAllStatementTypes());
    ArrayList<Statement> statements = new ArrayList<Statement>();
    try {
        String myQuery = "SELECT * FROM STATEMENTS";
        PreparedStatement preStatement = (PreparedStatement) connection.prepareStatement(myQuery);
        ResultSet result = preStatement.executeQuery();
        if (result.next()) {
            do {
                int id = result.getInt("ID");
                int documentId = result.getInt("DocumentId");
                int start = result.getInt("Start");
                int stop = result.getInt("Stop");
                int statementTypeId = result.getInt("StatementTypeId");
                int coder = result.getInt("Coder");
                Date date = data.getDocument(documentId).getDate();
                StatementType st = data.getStatementTypeById(statementTypeId);
                LinkedHashMap<String, Object> values = new LinkedHashMap<String, Object>();
                Iterator<String> keyIterator = st.getVariables().keySet().iterator();
                while (keyIterator.hasNext()) {
                    String key = keyIterator.next();
                    String value = st.getVariables().get(key);
                    String tableExtension = "";
                    if (value.equals("boolean")) {
                        tableExtension = "BOOLEAN";
                    } else if (value.equals("integer")) {
                        tableExtension = "INTEGER";
                    } else if (value.equals("short text")) {
                        tableExtension = "SHORTTEXT";
                    } else if (value.equals("long text")) {
                        tableExtension = "LONGTEXT";
                    }
                    String myQuery2 = "SELECT * FROM DATA" + tableExtension + " WHERE StatementId = " + id + " AND VariableId = (SELECT ID FROM VARIABLES WHERE StatementTypeId = " + statementTypeId + " AND Variable = '" + key + "')";
                    PreparedStatement preStatement2 = (PreparedStatement) connection.prepareStatement(myQuery2);
                    ResultSet result2 = preStatement2.executeQuery();
                    if (result2.next()) {
                        do {
                            values.put(key, result2.getObject("Value"));
                        } while (result2.next());
                    }
                    result2.close();
                    preStatement2.close();
                    if (values.size() == 0 || values.get(key) == null) {
                        // Fix errors here if no statement contents availabe
                        System.err.print("Statement " + id + ": variable \"" + key + "\" was not saved... ");
                        String query = "SELECT ID FROM VARIABLES WHERE (StatementTypeId = " + statementTypeId + " AND Variable = '" + key + "')";
                        int varId = (int) executeQueryForObject(query);
                        String replacementValue = "0";
                        if (value.equals("short text") || value.equals("long text")) {
                            replacementValue = "''";
                        }
                        String statement = "INSERT INTO DATA" + tableExtension + " (StatementId, VariableId, StatementTypeId, Value) " + "Values (" + id + ", " + varId + ", " + statementTypeId + ", " + replacementValue + ")";
                        executeStatement(statement);
                        if (value.equals("short text") || value.equals("long text")) {
                            values.put(key, "");
                        } else {
                            values.put(key, 0);
                        }
                        System.err.println("The problem has been fixed. Please review this statement.");
                    }
                }
                Statement statement = new Statement(id, documentId, start, stop, date, statementTypeId, coder, values);
                statements.add(statement);
            } while (result.next());
        }
        result.close();
        preStatement.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    data.setStatements(statements);
    data.setAttributes(getAllAttributes());
    return data;
}
Also used : SQLException(java.sql.SQLException) PreparedStatement(java.sql.PreparedStatement) Statement(dna.dataStructures.Statement) ArrayList(java.util.ArrayList) Data(dna.dataStructures.Data) PreparedStatement(java.sql.PreparedStatement) Date(java.util.Date) LinkedHashMap(java.util.LinkedHashMap) StatementType(dna.dataStructures.StatementType) ResultSet(java.sql.ResultSet)

Aggregations

Data (dna.dataStructures.Data)1 Statement (dna.dataStructures.Statement)1 StatementType (dna.dataStructures.StatementType)1 PreparedStatement (java.sql.PreparedStatement)1 ResultSet (java.sql.ResultSet)1 SQLException (java.sql.SQLException)1 ArrayList (java.util.ArrayList)1 Date (java.util.Date)1 LinkedHashMap (java.util.LinkedHashMap)1