SQLite and Android Insert/Updates on SQLiteDatabas

问题:

Pretend I have a table with 2 columns. _id and name. _id is the primary key and I do not want to set this value manually. I want to perform an insert of name="john," and let the program create my own _id. I am unclear what "index" to use when inserting and how many question marks to use. Does this code do the job? Should the index for john be 1 or 2?

String TABLENAME = "table";

SQLiteStatement statement = db.compileStatement("INSERT INTO "+TABLENAME+" VALUES(?);");    

statement.bindString(1,"john");

statement.executeInsert();

Next, say I want to manually set my own _id value. Would I change the code to:

String TABLENAME = "table";

SQLiteStatement statement = db.compileStatement("INSERT INTO "+TABLENAME+" VALUES(?,?);");  

statement.bindLong(1,666);  //Manual _id.

statement.bindString(2,"john");

statement.executeInsert();

回答1:

Your first example where you provide only the name will not work:

sqlite> create table test (i integer primary key autoincrement, j text);
sqlite> insert into test values ('asd');
Error: table test has 2 columns but 1 values were supplied
sqlite> insert into test values (null, 'asd');
sqlite> select * from test;
1|asd
sqlite> insert into test (j) values ('asd');
sqlite> select * from test;
1|asd
2|asd

so you need to identify the name column as the destination of the sole value this way, (or as you mentioned in your comment pass null):

SQLiteStatement statement = db.compileStatement("INSERT INTO "+TABLENAME+" (name) VALUES(?);"); 

Your second example should work fine.

This would apply to some table created this way:

create table SomeTable (_id integer primary key autoincrement, name text)

回答2:

Then

    SQLiteStatement statement = db.compileStatement("INSERT INTO "+TABLENAME+" VALUES(null,?);");
    statement.bindString(1,"john");

Should also work.