CODEWITHSUNDEEP
android-sqliteandroid-room
Cheok Yan Cheng
Cheok Yan Cheng

Reputation: 42710

Android Room: How to auto generate primary id, without using "autoGenerate = true" (To avoid SQLite keyword AUTOINCREMENT)?

Background

Just recently, I learn that using AUTOINCREMENT in SQLite is not encouraged.

https://www.sqlite.org/autoinc.html

The keyword AUTOINCREMENT is in fact confusing. As, what it really mean is the algorithm method for generating id automatically.

Even if we do not use the keyword AUTOINCREMENT, and do not provide an id during insertion, the id will still be auto generated.

For instance,

DROP TABLE cities;

CREATE TABLE cities (
   id INTEGER NOT NULL PRIMARY KEY,
   name TEXT NOT NULL
);

INSERT INTO cities (name)
VALUES( 'San Jose');

INSERT INTO cities (name)
VALUES( 'New York');

The following table will still be generated even without AUTOINCREMENT keyword

id  name
========
1   San Jose
2   New York

I want to achieve the same behavior in Android Room

Instead of using the following (which will apply SQLite AUTOINCREMENT)

@PrimaryKey(autoGenerate = true)
@ColumnInfo(name = "id")
private long id;

I use in my entity class

@PrimaryKey
@ColumnInfo(name = "id")
private long id;

However, I notice that I need to explicitly assign a non zero positive value to id, only the insertion will be success.

If the value for id is 0 or -1, then the insertion will not succeed

I expect we need not to assign id manually, even without using Room autoGenerate = true.

But, it seems like that doesn't work as expected. May I know why it is so?

Upvotes: 2

Views: 4534

Answers (2)

MikeT
MikeT

Reputation: 56948

I expect we need not to assign id manually, even without using Room autoGenerate = true.

It is not necessary to use autogenerate = true, and have the same generation of values. You can use (another way is shown later):-

@PrimaryKey
@ColumnInfo(name = "id")
private Long id; /* or private Long = null; */

Noting the use of the Long object rather than the long primitive. You then set id to null for a value to be generated or have suitable constructors.

Room sees the null, in the case of PRIMARY KEY and a non-primitive integer type, and omits the column from the generated SQL.

As an example based upon your schema consider :-

@Entity
class Cities {
   @PrimaryKey
   private Long id;
   private String name;

   @Ignore
   public Cities(String name) {
      this.name = name;
   }
   @Ignore
   public Cities(Long id, String name) {
      this.id = id;
      this.name = name;
   }
   public Cities(){}

   public Long getId() {
      return id;
   }

   public void setId(Long id) {
      this.id = id;
   }

   public String getName() {
      return name;
   }

   public void setName(String name) {
      this.name = name;
   }
}

With a convenience @Insert e.g. :-

@Dao
interface CitiesDao {
    @Insert
    long insert(Cities city);
}

Alternately you could utilise an @Query to utilise an INSERT SQL that suits. e.g. the following will insert a row just given the name with a generated id. :-

@Query("INSERT INTO cities (name) VALUES(:name)")
long insert(String name);
  • The important factor is including the specific column(s) without the id column.

An example of using the above (2 methods) :-

    citiesdao.insert(new Cities("San Jose"));
    citiesdao.insert(new Cities("New York"));
    citiesdao.insert(new Cities(100L,"San Francisco"));
    citiesdao.insert(new Cities("Nevada"));
    citiesdao.insert("Dallas");
  • you probably would not want to use the third that inserts with a specific id (or could perhaps use onConflict = IGNORE).

The above results in (via App Inspection) :-

enter image description here

Upvotes: 4

Lajos Arpad
Lajos Arpad

Reputation: 76561

You don't need to achieve the same in Android room. In SQLite, a column with the type of INTEGER PRIMARY KEY is an alias for the ROWID, except if you specify WITHOUT ROWID.

So, upon insert your id will receive an unused integer unless specified otherwise.

The reason for not recommending the AUTOINCREMENT keyword is that unless it's a PRIMARY KEY, then it is unlikely necessary to accept the costs in terms of CPU, memory usage, disk space and I/O overhead. However, using a PRIMARY KEY is in fact a good idea. Note that the page you have shared never claims you should not use a PRIMARY KEY, it just discourages the usage of the AUTOINCREMENT keyword.

Upvotes: 0

Related Questions