SQLite column with space in name

Question

I try to access a SQLLite database in my war in which some columns have space in their name.

I saw the previous question Read data from SQLite where column name contains spaces on SO but the solution doesn't work :

I know I'm supposed to escape the column names with either ``, "" or [] but none of this works for me, I get the following exception with every one of the escape characters, with space or no space in the column name :

java.sql.SQLException: no such column: '[class of worker]'
java.sql.SQLException: no such column: '"age"'

same with ` with doesn't escape well on SO

At the same time, the request works well with unescaped columns names without space (such as 'age' or 'education')

and the request in my code seem well formed :

SELECT DISTINCT `class of worker`, COUNT(*) as countt, ROUND(CAST(SUM(age) AS FLOAT)/COUNT(*),3) as avgage FROM census_learn_sql GROUP BY `class of worker` ORDER BY countt DESC

Here's the code accessing the db :

public Results getColumnValues(String column){

    column = "`" + column + "`"; //handle space in column names
    String sql = "SELECT DISTINCT "+column+", COUNT(*) as countt, ROUND(CAST(SUM(age) AS FLOAT)/COUNT(*),3) as avgage FROM census_learn_sql GROUP BY "
                    +column+" ORDER BY countt DESC";

    Results results = new Results();
    int count = 0;
    int clippedOutRows = 0;

    try (Connection conn = this.connect();
         Statement stmt  = conn.createStatement();
         ResultSet rs    = stmt.executeQuery(sql)){

        while (rs.next()) {
            if (count >= 100){
                clippedOutRows += rs.getInt("countt");
            }
            else {
                results.getResults().add(new RowResult(rs.getString(column),
                        rs.getInt("countt"),
                        rs.getDouble("avgage")));
            }

            count++;
        }

        results.setClippedOutRows(clippedOutRows);
        results.setClippedOutValues(count > 99 ? count - 99 : 0);
    } catch (SQLException e) {
        System.out.println(e.getMessage());
    }

    return results;
}

And here's my pom file dependencies :

<properties>
        <!-- maven-compiler-plugin -->
        <maven.compiler.target>1.8</maven.compiler.target>
        <maven.compiler.source>1.8</maven.compiler.source>
        <version.javaee>7.0</version.javaee>
        <version.resteasy>3.0.10.Final</version.resteasy>
        <version.jackson>2.4.1</version.jackson>
        <version.junit>4.11</version.junit>
        <version.assertj>1.6.1</version.assertj>
        <version.commons>3.2.1</version.commons>
    </properties>


    <dependencies>
        <dependency>
            <groupId>org.jboss.resteasy</groupId>
            <artifactId>resteasy-jaxrs</artifactId>
            <version>${version.resteasy}</version>
        </dependency>
        <dependency>
            <groupId>org.jboss.resteasy</groupId>
            <artifactId>resteasy-servlet-initializer</artifactId>
            <version>${version.resteasy}</version>
        </dependency>
        <dependency>
            <groupId>org.jboss.resteasy</groupId>
            <artifactId>resteasy-jackson-provider</artifactId>
            <version>${version.resteasy}</version>
        </dependency>
        <dependency>
            <groupId>javax</groupId>
            <artifactId>javaee-api</artifactId>
            <version>${version.javaee}</version>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>commons-collections</groupId>
            <artifactId>commons-collections</artifactId>
            <version>${version.commons}</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>${version.junit}</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.assertj</groupId>
            <artifactId>assertj-core</artifactId>
            <version>${version.assertj}</version>
            <scope>test</scope>
        </dependency>
        <!-- SQLite JDBC library -->
        <dependency>
           <groupId>org.xerial</groupId>
           <artifactId>sqlite-jdbc</artifactId>
           <version>3.8.11.2</version>
        </dependency>
    </dependencies>

I don't see what I'm doing wrong here, is it a dependency problem ?

Thanks in advance for your help

Answer 1

You are modifying the String value column to delimit the column name

column = "`" + column + "`"; //handle space in column names
String sql = "SELECT DISTINCT "+column+", COUNT(*) ...

but when you go to retrieve the value from the ResultSet you are using the delimited column name

rs.getString(column)

which is the equivalent of

rs.getString("`class of worker`")

and the ResultSet contains no column of that name: the name is just class of worker. In other words, the SQL command needs the delimiters, but the ResultSet#getString method does not.

So instead of modifying the column variable you should just put the backticks (or double quotes, or square brackets; SQLite supports all of them) in the sql string

// column = "`" + column + "`"; // do not handle space in column names here, do it below
String sql = "SELECT DISTINCT `" + column + "`, COUNT(*) ...

and then your rs.getString(column) should work fine.