Androide sqlitedatabase Database operation (RPM)

SQLite can parse most of the standard SQL statements:
Built-in statements: CREATE TABLE table name (primary key name integer primary key AutoIncrement (set to self-increment column), other column name and attribute) or (primary key name integer primary key) all declared in SQLite database as " The column for integer primary key is automatically recognized as a self-increment column.

Query statement: SELECT * from table name where conditional clause GROUP BY group clause having...order by sort clause DESC (descending) (ASC Ascending).

Paging statement: SELECT * FROM table name limit record number offset start position

or select * from table name limit start position, number of records

Insert statement: INSERT INTO Table name (field list) VALUES (Value list)

Updated Statement: Update table name set field name = value WHERE conditional clause

Delete statement: Delete from table name where conditional clause

Delete Table statement: Drop table if exists name

To add a field statement:

ALTER TABLE table name add Column Column Name field type

Modify Database name

ALTER TABLE name rename to new table name

In order to facilitate version management of the database, it is recommended to use the Sqliteopenhelper class when developing the project, which provides two important methods, namely

OnCreate (Sqlitedatabase db)--generates a database for the first time the software is used, and does not call this method once the database exists. The function is executed the first time the database is created, and the function is not called when the Databasehelper object is generated, but only when the Databasehelper object is implemented . When getreadabledatabase or getwritabledatabase is called, the OnCreate () function must be called if the database is created for the first time.

Onupgrade (sqlitedatabase db,int oldversion,int vewversion)--Updates the database table structure when upgrading the software, such as adding tables, column fields, and so on.

Note that before the software upgrade, it is best to back up the original data, after the new table is built, import the data into the new table.

By implementing these two methods, you can use his getwritabledatabase () and getreadabledatabase () to obtain the database.

Emphasis: When the database is first created, the system automatically calls Oncreater when the Databasehelper object is implemented Getreadabledatabase or when Getwritabledatabase is called. ) method (which can also be called in the program);

If users need to upgrade the database table structure, they need to call Onupgrade (sqlitedatabase db,int oldversion,int vewversion) to pass in a new version number.

Query operation: When using SQLite to query, it is best to use the placeholder "? "Instead of the values, pay attention to the direct use?" Instead of '? '.

For example:
Sqlitedatabase db=databasehelper.getwritabledatabase ();
Db.execsql ("Update person set name=?,age=?") Where personid=? ", New Obect{person.getname (), Person.getage (), Person.getid ()});
The Execsql () method is used to execute an SQL statement other than a lookup statement, and the lookup statement executes it with Rawquery () to put back a cursor. Of course, he also provides a packaged Java class method for us to manipulate. Specifically, you can view the usage of the Sqlitedatabase class in the document directly.

Insert operation:

Inserting a single row of records can be done in three ways:

First, encapsulate column-value pairs, call Db.insert (table, Nullcolumnhack, values) or db.insertorthrow (table, Nullcolumnhack, values) method inserts, The insert successfully returns the line number, which returns-1 on failure.

Contentvalues values=new contentvalues ();
Values.put (Myhelper.country, "China");
Values.put (myhelper.code,86);
Insert a row of data
Db.insert (Myhelper.td_name, myhelper.id, values);

If the values passed in are empty, then the data for each row of columns is inserted as null or 0 (if the column is an integer type, 0, and the other type is null).

Second, the organization SQL statement, executed with db.execsql (String sql) or Db.execsql (string sql,object[] data), has no return value.

String sql1= "INSERT INTO"
+myhelper.td_name+ "("
+myhelper.country+ ","
+myhelper.code+ ") Values"
+ "(?, 789)";
String[] data={"Japan"};
Db.execsql (SQL1, data);

Third, using update (string table, contentvalues values, String whereclause, string[] whereargs)

Update the row data, note that Whereclause is the where condition for the update, without the keyword where:

Sqlitedatabase db = This.getwritabledatabase ();
String where = book_id + "=?";
String[] Wherevalue = {integer.tostring (id)};

  contentvalues CV = new Contentvalues ();
  cv.put (Book_name, bookname);
  cv.put (Book_author, AUTHOR);
  db.update (TABLE_NAME, CV, where, wherevalue);