confused about Populating AutoCompleteTextView Suggestions with a Room Database

Issue

I am a newbie and trying to implement an autoComplete function in my android app in JAVA(not Kotlin cause I haven't learned about it) with this tutorial:

link

Users can search city names in the autocomplete Text view, and when they type, suggestions should appear on the dropdown list based on what they typed, e.g If they typed "N", then the suggestions should show "New York", "New castle" etc.

The suggestions should be prepopulated with an existing database of thousands of city info(data are already inside), I saved this city_db.db file in my assets folder.

I have several questions:

1. When writing My DAO.java class, I should include my query commands, and I only include the query part because I only need to read from the database, not write in, so I wrote:

package com.example.roomtest;
import androidx.room.Dao;
import androidx.room.Query;
@Dao
public interface DAO {
    //this class contains methods that accesses the database
    @Query("SELECT * city_name FROM city")
}

but it just shows the red line and tells me "Annotations are not allowed here".

and I'm not sure if this is the right query command, shouldn't it be like

"SELECT * city_name FROM city where like "what the user typed%" "?

2. I am not sure if I need more classes for this auto-complete function, Now I have these classes:

1.MainActivity.java

2.Entity.java

3.Dao.java

4.Database.java

Do I need more classes to implement this auto-complete function? I think I'm missing something.

Heres's my code in all the classes:

package com.example.roomtest;
    import androidx.appcompat.app.AppCompatActivity;
    import android.os.Bundle;
    import android.widget.AutoCompleteTextView;
    import android.widget.EditText;
    
    public class MainActivity extends AppCompatActivity {
        private AutoCompleteTextView at;
    
    
        @Override
        protected void onCreate(Bundle savedInstanceState) {
            super.onCreate(savedInstanceState);
            setContentView(R.layout.activity_main);
    
            at = findViewById(R.id.att);
        }
    }

package com.example.roomtest;
    import android.content.Context;
    import androidx.room.Room;
    import androidx.room.RoomDatabase;
    
    @androidx.room.Database(entities = Entity.class,version = 1)
    //must include the entity associated with database
    public abstract class Database extends RoomDatabase {
        //must be abstract
        public abstract DAO dao();
        //must declare this
        //check if a database instance is existing
        //if not, create one and return it
        private static volatile Database db;
        static Database getDatabase(final Context context){
            if(db==null){
                synchronized (Database.class){
                    if(db == null){
                        db =Room.databaseBuilder(context.getApplicationContext(),
                                Database.class, "Database")
                                .createFromAsset("city_db.db").build();
                    }
                }
            }
            return db;
        }
    }

package com.example.roomtest;
    
    import androidx.room.ColumnInfo;
    import androidx.room.PrimaryKey;
    
    
    @androidx.room.Entity(tableName = "city")
    public class Entity {
        //define data types in this class
        @PrimaryKey
        public int id;
        @ColumnInfo(name = "city_name")
        public String name;
    
        public String state;
        public String country;
        public String lon;
        public String lat;
    }

package com.example.roomtest;
    import androidx.room.Dao;
    import androidx.room.Query;
    @Dao
    public interface DAO {
        //this class contains methods that accesses the database
        @Query("SELECT * city_name FROM city")
    }

my gradle dependencies:

dependencies {
    //room components
    def room_version = "2.3.0"
    implementation "androidx.room:room-runtime:$room_version"
    annotationProcessor "androidx.room:room-compiler:$room_version"
    testImplementation "androidx.room:room-testing:$room_version"

    implementation 'androidx.appcompat:appcompat:1.3.1'
    implementation 'com.google.android.material:material:1.4.0'
    implementation 'androidx.constraintlayout:constraintlayout:2.1.1'
    testImplementation 'junit:junit:4.+'
    androidTestImplementation 'androidx.test.ext:junit:1.1.3'
    androidTestImplementation 'androidx.test.espresso:espresso-core:3.4.0'
}

Also here are the attributes for my database:

I am sorry for the long post, but I'm just too confused about all the works needed for this simple function, If anyone knows a good tutorial about this, please kindly share with me, Thank you.

Solution

You need a comma between column names (* denoting all columns so no need for *,city_name).

That is SELECT * FROM city.... is the equivalent, in your case, of saying SELECT id,city_name,state,country,lon,lat FROM city ....

Whilst SELECT *,city_name .... is saying SELECT id,city_name,state,country,lon,lat,city_name FROM city ....

Although valid SQL why get the same value twice (rhetorical)? Furthermore which (even though they are the same) city_name should Room use(rehtorical)? It's ambiguous and has the potential to result in errors (I think room handles this and picks the last)

You want either :-

@Dao
public interface DAO {
    //this class contains methods that accesses the database
    @Query("SELECT * FROM city WHERE city_name LIKE :cityName")
    List<Entity> getCity(String cityName); //<<<< The method that is called from the code
}

This will return a List i.e. a list of Entity objects

Or :-

@Dao
public interface DAO {
    //this class contains methods that accesses the database
    @Query("SELECT city_name FROM city WHERE city_name LIKE :cityName")
    List<String> getCityName(cityName);
}

• this returns a List where each string will just be the city_name's
• you could have both

when invoking if the string passed (cityName) was "New%" then you would get New York, Newcastle etc (i.e. the % is a wild character for 1 or more characters _ for a single character)

Example

Using your code, with some minor changes, the above compiles and runs OK.

The changes as suggested above have been included (both) so theDAO class used was :-

@Dao
public interface DAO {

    //this class contains methods that accesses the database
    @Query("SELECT * FROM city WHERE city_name LIKE :cityName")
    List<Entity> getCitiesByName(String cityName);

    @Query("SELECT city_name FROM city WHERE city_name LIKE :cityName")
    List<String> getCityNamesByName(String cityName);
}

I made a few changes to the Database class to a) allow running on the main thread (added .allowMainThreadQueries()) and b) to not create from an assets file (commented out .createFromAsset("city_db.db")) as per :-

@androidx.room.Database(entities = Entity.class,version = 1)
//must include the entity associated with database
public abstract class Database extends RoomDatabase {
    //must be abstract
    public abstract DAO dao();
    //must declare this
    //check if a database instance is existing
    //if not, create one and return it
    private static volatile Database db;
    static Database getDatabase(final Context context){
        if(db==null){
            synchronized (Database.class){
                if(db == null){
                    db = Room.databaseBuilder(context.getApplicationContext(),
                            Database.class, "Database")
                            //.createFromAsset("city_db.db")
                            .allowMainThreadQueries()
                            .build();
                }
            }
        }
        return db;
    }
}

The MainActivity used was :-

public class MainActivity extends AppCompatActivity {

    Database db;
    DAO dao;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        db = Database.getDatabase(this);
        dao = db.dao();
        dao.getCitiesByName("Somewhere%");
        dao.getCityNamesByName("Nowhere%");
    }
}

Thus even though nothing is done with the results (no data inside the database) the above opens the database (creating it as .createFromAsset is not used).

You can see this as per App Inspection (aka Database Inspector) in Android Studio :-

As such you code, bar the copy of the assets file works when amended as suggested.

Ongoing issues

According to your screenshot, then you will likely have ongoing issues when it comes to the the .build().

Room EXPECTS the copied database to exactly match the schema that is derived from the Entities (for the entities defined in the entities parameter of the @Database annotation).

As one example, the screen shot shows that the name of the city is in the column(field) named name, whilst in your entity the equivalent column names, as per the entity Entity is city_name. Room build will fail. Likewise for coord.lon and coord.lat which are lon and lat.

Furthermore, the column definitions (columns types and constraints such as NOT NULL) must also match, otherwise the Room build will fail.

As such you MUST ensure that the schemas match. This may require using one of available tools to convert the database accordingly (it is probably much easier to convert the database being copied than to trying to match an existing database due to the limitations imposed by Room).

The starting point would be the SQL that Room generates when you compile with a valid/usable @Database that includes the entities.

e.g. in your case in the generated java (visible from the Android View in Android Studio) in the class named the same as the class that has the @Database annotation but suffixed with _IMPL.

So as your @Database class is named Database then there will be a class Database_IMPL.

Within the class there will be a method createAllTables this has the SQL for all the tables (any SQL that deal's with room_ can be ignored).

E.g. yours will be something like :-

You will very likely need to convert the database using an SQLite tool such as DB Browser for SQLite, SQlite Studio, DBeaver, Navicat For SQlite to use the schema.

Roughly speaking use the SQL as obtained from the generated java to create the table and then copy the data.

The conversion itself would be pretty simple. First create a copy of the city_db.db file.

Open either (as you prefer in your preferred SQLite tool, for demonstration

• I've used Navicat and used a query to generate the an example of the original (with just a couple of countries)) as per :-

  CREATE TABLE IF NOT EXISTS city (id INTEGER PRIMARY KEY, name TEXT,state TEXT, country TEXT, `coord.lon` REAL, `coord.lat`);
  INSERT INTO city VALUES
      (3904809,'Santa Rita','','BO',-63.3499,-17.966),
      (3904890,'Santa Elenta','','BO',-64.7833,-20.5496)
  ;
  
  - don't use the above
  
  

Then use the following SQL noting that this will delete the original table (don't want an oversized asset file). This will fail if run a second time as the original has effectively been overridden so the original column names don't exist.

DROP TABLE IF EXISTS room_city;

/*As copied from the App's generated java */
/* BUT table name changed to room_city from city */
CREATE TABLE IF NOT EXISTS `room_city` (`id` INTEGER NOT NULL, `city_name` TEXT, `state` TEXT, `country` TEXT, `lon` TEXT, `lat` TEXT, PRIMARY KEY(`id`));
INSERT INTO room_city SELECT id,name AS city_name, state, country, `coord.lon` AS lon, `coord.lat` AS lat FROM city;
/* To clean up so as to not include the original in the asset
    you don't want the asset hold twice the data that it needs to
    !!!!!NOTE!!!!! you should use a copy of the original city database
*/
DROP TABLE IF EXISTS city;
ALTER TABLE `room_city` RENAME TO `city`;
VACUUM;
/* Below not needed but good to check all is as expected */
SELECT * FROM city;
SELECT * FROm sqlite_master;

• Note that this assumes that the original database is as per the screen shot.

• the first SELECT after the VACUUM shows:-

• 

• The second shows the schema:-

• 

  • it matches the schema EXPECTED by Room

The database and connection should be closed (open checked and closed again to double check that the file to be copied is saved).

It's then a mattter of copying the file into the assets folder (you may need to create this) ensuring that it is named (can be renamed) city_db.db. :-

In the case of the demo from above being utilised the Database class was changed to now include the line .createFromAsset("city_db.db") // <<<<< reinstated

and MainActivity was changed to be :-

public class MainActivity extends AppCompatActivity {

    Database db;
    DAO dao;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        db = Database.getDatabase(this);
        dao = db.dao();

        for(Entity city: dao.getCitiesByName("%")) { //* <<<<<< changed to show all as only 2 cities in demo
            Log.d("CITYINFO-1","CityName is " + city.name + " state/county is " + city.state + " etc.....");
        }
        for(String cityname: dao.getCityNamesByName("%")) { //* <<<<<< changed to show all as only 2 cities in demo
            Log.d("CITYINFO-2","CityName is " + cityname);
        }
        dao.getCityNamesByName("New%");
    }
}

When run the log shows :-

2021-10-23 15:44:37.480 D/CITYINFO-1: CityName is Santa Rita state/county is  etc.....
2021-10-23 15:44:37.480 D/CITYINFO-1: CityName is Santa Elenta state/county is  etc.....
2021-10-23 15:44:37.482 D/CITYINFO-2: CityName is Santa Rita
2021-10-23 15:44:37.482 D/CITYINFO-2: CityName is Santa Elenta

So the database works.

What happens if you don't convert?

Again based upon the screen shot then the App fail with :-

2021-10-23 15:50:53.826 6345-6345/a.a.so69683821cities E/AndroidRuntime: FATAL EXCEPTION: main
    Process: a.a.so69683821cities, PID: 6345
    java.lang.RuntimeException: Unable to start activity ComponentInfo{a.a.so69683821cities/a.a.so69683821cities.MainActivity}: java.lang.IllegalStateException: Pre-packaged database has an invalid schema: city(a.a.so69683821cities.Entity).
     Expected:
    TableInfo{name='city', columns={country=Column{name='country', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, city_name=Column{name='city_name', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, lon=Column{name='lon', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, id=Column{name='id', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=1, defaultValue='null'}, state=Column{name='state', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, lat=Column{name='lat', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}}, foreignKeys=[], indices=[]}
     Found:
    TableInfo{name='city', columns={country=Column{name='country', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, name=Column{name='name', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, id=Column{name='id', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=1, defaultValue='null'}, state=Column{name='state', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, coord.lat=Column{name='coord.lat', type='', affinity='1', notNull=false, primaryKeyPosition=0, defaultValue='null'}, coord.lon=Column{name='coord.lon', type='REAL', affinity='4', notNull=false, primaryKeyPosition=0, defaultValue='null'}}, foreignKeys=[], indices=[]}
        at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2913)

where the EXPECTED is what Room expects the schema to be according to the Entity class BUT Room FOUND the mismatching schema from the asset.

Answered By - MikeT