Search in sources :

Example 1 with DatabaseRow

use of com.google.refine.extension.database.model.DatabaseRow in project OpenRefine by OpenRefine.

the class SQLiteDatabaseService method executeQuery.

@Override
public DatabaseInfo executeQuery(DatabaseConfiguration dbConfig, String query) throws DatabaseServiceException {
    try {
        Connection connection = SQLiteConnectionManager.getInstance().getConnection(dbConfig);
        Statement statement = connection.createStatement();
        ResultSet queryResult = statement.executeQuery(query);
        ResultSetMetaData metadata = queryResult.getMetaData();
        int columnCount = metadata.getColumnCount();
        ArrayList<DatabaseColumn> columns = new ArrayList<>(columnCount);
        for (int i = 1; i <= columnCount; i++) {
            DatabaseColumn dc = new DatabaseColumn(metadata.getColumnName(i), metadata.getColumnLabel(i), DatabaseUtils.getDbColumnType(metadata.getColumnType(i)), metadata.getColumnDisplaySize(i));
            columns.add(dc);
        }
        int index = 0;
        List<DatabaseRow> rows = new ArrayList<>();
        while (queryResult.next()) {
            DatabaseRow row = new DatabaseRow();
            row.setIndex(index);
            List<String> values = new ArrayList<>(columnCount);
            for (int i = 1; i <= columnCount; i++) {
                values.add(queryResult.getString(i));
            }
            row.setValues(values);
            rows.add(row);
            index++;
        }
        DatabaseInfo dbInfo = new DatabaseInfo();
        dbInfo.setColumns(columns);
        dbInfo.setRows(rows);
        return dbInfo;
    } catch (SQLException e) {
        logger.error("SQLException::", e);
        throw new DatabaseServiceException(true, e.getSQLState(), e.getErrorCode(), e.getMessage());
    } finally {
        SQLiteConnectionManager.getInstance().shutdown();
    }
}
Also used : DatabaseInfo(com.google.refine.extension.database.model.DatabaseInfo) ArrayList(java.util.ArrayList) DatabaseRow(com.google.refine.extension.database.model.DatabaseRow) DatabaseColumn(com.google.refine.extension.database.model.DatabaseColumn)

Example 2 with DatabaseRow

use of com.google.refine.extension.database.model.DatabaseRow in project OpenRefine by OpenRefine.

the class SQLiteDatabaseService method getRows.

@Override
public List<DatabaseRow> getRows(DatabaseConfiguration dbConfig, String query) throws DatabaseServiceException {
    try {
        Connection connection = SQLiteConnectionManager.getInstance().getConnection(dbConfig);
        Statement statement = connection.createStatement();
        statement.setFetchSize(10);
        ResultSet queryResult = statement.executeQuery(query);
        ResultSetMetaData metadata = queryResult.getMetaData();
        int columnCount = metadata.getColumnCount();
        int index = 0;
        List<DatabaseRow> rows = new ArrayList<>();
        while (queryResult.next()) {
            DatabaseRow row = new DatabaseRow();
            row.setIndex(index);
            List<String> values = new ArrayList<>(columnCount);
            for (int i = 1; i <= columnCount; i++) {
                values.add(queryResult.getString(i));
            }
            row.setValues(values);
            rows.add(row);
            index++;
        }
        return rows;
    } catch (SQLException e) {
        logger.error("SQLException::", e);
        throw new DatabaseServiceException(true, e.getSQLState(), e.getErrorCode(), e.getMessage());
    }
}
Also used : ArrayList(java.util.ArrayList) DatabaseRow(com.google.refine.extension.database.model.DatabaseRow)

Example 3 with DatabaseRow

use of com.google.refine.extension.database.model.DatabaseRow in project OpenRefine by OpenRefine.

the class DBQueryResultImportReader method getRowsOfCells.

/**
 * @param startRow
 * @return
 * @throws IOException
 * @throws DatabaseServiceException
 */
private List<List<Object>> getRowsOfCells(int startRow) throws IOException, DatabaseServiceException {
    // logger.info("Entry getRowsOfCells::startRow:" + startRow);
    List<List<Object>> rowsOfCells = new ArrayList<List<Object>>(batchSize);
    String query = databaseService.buildLimitQuery(batchSize, startRow, dbQueryInfo.getQuery());
    // logger.info("batchSize::"  + batchSize +  " startRow::" + startRow + " query::" + query );
    List<DatabaseRow> dbRows = databaseService.getRows(dbQueryInfo.getDbConfig(), query);
    if (dbRows != null && !dbRows.isEmpty() && dbRows.size() > 0) {
        for (DatabaseRow dbRow : dbRows) {
            List<String> row = dbRow.getValues();
            List<Object> rowOfCells = new ArrayList<Object>(row.size());
            for (int j = 0; j < row.size() && j < dbColumns.size(); j++) {
                String text = row.get(j);
                if (text == null || text.isEmpty()) {
                    rowOfCells.add(null);
                } else {
                    DatabaseColumn col = dbColumns.get(j);
                    if (col.getType() == DatabaseColumnType.NUMBER) {
                        try {
                            rowOfCells.add(Long.parseLong(text));
                            continue;
                        } catch (NumberFormatException e) {
                        }
                    } else if (col.getType() == DatabaseColumnType.DOUBLE || col.getType() == DatabaseColumnType.FLOAT) {
                        try {
                            double d = Double.parseDouble(text);
                            if (!Double.isInfinite(d) && !Double.isNaN(d)) {
                                rowOfCells.add(d);
                                continue;
                            }
                        } catch (NumberFormatException e) {
                        }
                    }
                    rowOfCells.add(text);
                }
            }
            rowsOfCells.add(rowOfCells);
        }
    }
    end = dbRows.size() < batchSize + 1;
    // logger.info("Exit::getRowsOfCells::rowsOfCells:{}", rowsOfCells);
    return rowsOfCells;
}
Also used : DatabaseColumn(com.google.refine.extension.database.model.DatabaseColumn) ArrayList(java.util.ArrayList) List(java.util.List) ArrayList(java.util.ArrayList) DatabaseRow(com.google.refine.extension.database.model.DatabaseRow)

Example 4 with DatabaseRow

use of com.google.refine.extension.database.model.DatabaseRow in project OpenRefine by OpenRefine.

the class MariaDBDatabaseService method getRows.

@Override
public List<DatabaseRow> getRows(DatabaseConfiguration dbConfig, String query) throws DatabaseServiceException {
    try {
        Connection connection = MariaDBConnectionManager.getInstance().getConnection(dbConfig, false);
        Statement statement = connection.createStatement();
        ResultSet queryResult = statement.executeQuery(query);
        MariaDbResultSetMetaData metadata = (MariaDbResultSetMetaData) queryResult.getMetaData();
        int columnCount = metadata.getColumnCount();
        int index = 0;
        List<DatabaseRow> rows = new ArrayList<DatabaseRow>();
        while (queryResult.next()) {
            DatabaseRow row = new DatabaseRow();
            row.setIndex(index);
            List<String> values = new ArrayList<String>(columnCount);
            for (int i = 1; i <= columnCount; i++) {
                values.add(queryResult.getString(i));
            }
            row.setValues(values);
            rows.add(row);
            index++;
        }
        return rows;
    } catch (SQLException e) {
        logger.error("SQLException::", e);
        throw new DatabaseServiceException(true, e.getSQLState(), e.getErrorCode(), e.getMessage());
    }
}
Also used : DatabaseServiceException(com.google.refine.extension.database.DatabaseServiceException) SQLException(java.sql.SQLException) Statement(java.sql.Statement) Connection(java.sql.Connection) ResultSet(java.sql.ResultSet) ArrayList(java.util.ArrayList) DatabaseRow(com.google.refine.extension.database.model.DatabaseRow) MariaDbResultSetMetaData(org.mariadb.jdbc.MariaDbResultSetMetaData)

Example 5 with DatabaseRow

use of com.google.refine.extension.database.model.DatabaseRow in project OpenRefine by OpenRefine.

the class MySQLDatabaseService method getRows.

@Override
public List<DatabaseRow> getRows(DatabaseConfiguration dbConfig, String query) throws DatabaseServiceException {
    try {
        Connection connection = MySQLConnectionManager.getInstance().getConnection(dbConfig, false);
        Statement statement = connection.createStatement();
        statement.setFetchSize(10);
        ResultSet queryResult = statement.executeQuery(query);
        java.sql.ResultSetMetaData metadata = queryResult.getMetaData();
        int columnCount = metadata.getColumnCount();
        int index = 0;
        List<DatabaseRow> rows = new ArrayList<DatabaseRow>();
        while (queryResult.next()) {
            DatabaseRow row = new DatabaseRow();
            row.setIndex(index);
            List<String> values = new ArrayList<String>(columnCount);
            for (int i = 1; i <= columnCount; i++) {
                values.add(queryResult.getString(i));
            }
            row.setValues(values);
            rows.add(row);
            index++;
        }
        return rows;
    } catch (SQLException e) {
        logger.error("SQLException::", e);
        throw new DatabaseServiceException(true, e.getSQLState(), e.getErrorCode(), e.getMessage());
    }
}
Also used : SQLException(java.sql.SQLException) Statement(java.sql.Statement) Connection(java.sql.Connection) ArrayList(java.util.ArrayList) DatabaseRow(com.google.refine.extension.database.model.DatabaseRow) DatabaseServiceException(com.google.refine.extension.database.DatabaseServiceException) ResultSet(java.sql.ResultSet)

Aggregations

DatabaseRow (com.google.refine.extension.database.model.DatabaseRow)11 ArrayList (java.util.ArrayList)10 DatabaseServiceException (com.google.refine.extension.database.DatabaseServiceException)6 DatabaseColumn (com.google.refine.extension.database.model.DatabaseColumn)6 Connection (java.sql.Connection)6 ResultSet (java.sql.ResultSet)6 SQLException (java.sql.SQLException)6 Statement (java.sql.Statement)6 DatabaseInfo (com.google.refine.extension.database.model.DatabaseInfo)4 List (java.util.List)2 MariaDbResultSetMetaData (org.mariadb.jdbc.MariaDbResultSetMetaData)2 PgResultSetMetaData (org.postgresql.jdbc.PgResultSetMetaData)2 MariaDBDatabaseService (com.google.refine.extension.database.mariadb.MariaDBDatabaseService)1 MySQLDatabaseService (com.google.refine.extension.database.mysql.MySQLDatabaseService)1 PgSQLDatabaseService (com.google.refine.extension.database.pgsql.PgSQLDatabaseService)1 SQLiteDatabaseService (com.google.refine.extension.database.sqlite.SQLiteDatabaseService)1 BeforeTest (org.testng.annotations.BeforeTest)1 Test (org.testng.annotations.Test)1