Use SQLite to add, delete, modify, and query on android, and use sqliteandroid

Use SQLite to add, delete, modify, and query on android, and use sqliteandroid
Use SQLite to implement addition, deletion, modification, and query on androidMethod:

Directly use the database.exe cSQL () method to input a complete SQL statement.

This method is applicable to complex SQL statements, such as multi-table queries.

This method is applicable to addition, deletion, and modification. This method does not return values, but queries need to return data, so it is not applicable.

Add instance:

Database.exe cSQL ("insert into student (name, age) values ('lil', 45 )");

The database. rawQuery () method is used for query. We also directly enter the complete SQL statement.

Instance query:

Cursor cursor = database. rawQuery ("select id, name, age from student where name =? ", New String [] {" Xiao Li "});

 

II. The principle of directly encapsulated APIS is that parameters are spelled into SQL statements in the system.

Applicable to simple SQL statements

Add: return operation row

Long row = database. insert ("student", null, values );

Delete: returns the number of affected rows.

Int n = database. delete ("student", "name =? And age =? ", New String [] {" meal "," 20 "});

Change: The number of affected rows is returned.

Int n = database. update ("student", values, "name =? ", New String [] {" meal "});

Query: returns the data table cursor (note that the query is not a select statement)

Cursor cursor = database. rawQuery ("select id, name, age from student where name =? ", New String [] {" Xiao Li "});

 

Iii. Others

1. The database connection is limited, so you must close the connection when it is used up.

2. Write the data we need to operate into ContentValues

3. When querying data, the cursor uses the cursor. moveToNext () method to determine whether the next row has a value.

4. There are two ways to obtain the column index:

Obtain

Int id = cursor. getInt (cursor. getColumnIndex ("id "));

Obtain the column number directly.

String name = cursor. getString (1 );

 

Code:

Com. fry. database. MySQLiteOpenHelper

1 package com. fry. database; 2 3 import android. content. context; 4 import android. database. sqlite. SQLiteDatabase; 5 import android. database. sqlite. SQLiteDatabase. cursorFactory; 6 import android. database. sqlite. SQLiteOpenHelper; 7 import android. util. log; 8 9 public class MySQLiteOpenHelper extends SQLiteOpenHelper {10 11/* 12*1. context 13*2. Database Name 14*3. factory15 * 4. version database file version 16*/17 18 public MySQLiteOpenHelper (Context context, int version) {19 super (context, "fanfan3.db", null, version ); 20 // TODO Auto-generated constructor stub21} 22 23/* call 24 * (non-Javadoc) 25 * @ see android after the database file is created successfully. database. sqlite. SQLiteOpenHelper # onCreate (android. database. sqlite. SQLiteDatabase) 26 */27 @ Override28 public void onCreate (SQLiteDatabase db) {29 Log. d ("fanfan", "onCreate"); 30 db.exe cSQL ("create table student (id integer primary key autoincrement, name TEXT, age INTEGER )"); 31} 32 33/* Call 34 * (non-Javadoc) 35 * @ see android after the database file is updated. database. sqlite. SQLiteOpenHelper # onUpgrade (android. database. sqlite. SQLiteDatabase, int, int) 36 */37 @ Override38 public void onUpgrade (SQLiteDatabase arg0, int arg1, int arg2) {39 Log. d ("fanfan", "onUpgrade"); 40} 41 42}

Database

Com. fanfan. test. Test

1 package com. fanfan. test; 2 3 import com. fry. database. mySQLiteOpenHelper; 4 5 import android. annotation. suppressLint; 6 import android. content. contentValues; 7 import android. database. cursor; 8 import android. database. sqlite. SQLiteDatabase; 9 import android. test. androidTestCase; 10 import android. util. log; 11 12 public class Test extends AndroidTestCase {13 public void test1 () {14 MySQLiteOpenHe Lper helper = new MySQLiteOpenHelper (getContext (), 1); 15 SQLiteDatabase database = helper. getReadableDatabase (); 16 helper. getWritableDatabase (); 17} 18 19 // The API provided by the system can solve simple problems, but for example, the system may not be able to solve the problem of multi-table queries, we can use execSQL to write SQL statements 20 public void insert () {21 MySQLiteOpenHelper helper = new MySQLiteOpenHelper (getContext (), 1); 22 SQLiteDatabase database = helper. getReadableDatabase (); 23 // ContentValues values = New ContentValues (); 24 // values. put ("name", "meal"); 25 // values. put ("age", 20); 26 // long row = database. insert ("student", null, values); 27 // Log. d ("insert", "the row number just inserted is:" + row); 28 // if (row =-1) {29 // String s = "insertion failed"; 30 // Log. d ("insert", s); 31 //} 32 // write SQL 33 database.exe cSQL ("insert into student (name, age) by yourself without using system APIs) values ('lil', 45) "); 34 // closes the database connection 35 database. close (); 36} 37 38 p Ublic void delete () {39 MySQLiteOpenHelper helper = new MySQLiteOpenHelper (getContext (), 1); 40 SQLiteDatabase database = helper. getReadableDatabase (); 41 // returns the number of affected rows 42 int n = database. delete ("student", "name =? And age =? ", New String [] {43" meal "," 20 "}); 44 Log. d ("delete", "the number of affected rows returned is:" + n); 45 // close the database connection 46 database. close (); 47} 48 49 public void update () {50 MySQLiteOpenHelper helper = new MySQLiteOpenHelper (getContext (), 1); 51 SQLiteDatabase database = helper. getReadableDatabase (); 52 ContentValues values = new ContentValues (); 53 values. put ("name", ""); 54 // return the affected number of rows 55 56 // change the record that meets the filtering conditions 57 int n = dat Abase. update ("student", values, "name =? ", 58 new String [] {" meal "}); 59 // change all records in the Table 60 // int n = database. update ("student", values, null, null); 61 Log. d ("update", "the number of affected rows returned is:" + n); 62 // close the database connection 63 database. close (); 64} 65 66 public void select () {67 MySQLiteOpenHelper helper = new MySQLiteOpenHelper (getContext (), 1); 68 // create a database connection 69 SQLiteDatabase = helper. getReadableDatabase (); 70/* 71 * distinct = true remove duplicate data table name c Selection filtering condition 72 * selsetionArgs of the column queried by olumn specify the value of the filtering condition parameter groupBy group having grouping filtering orderBy sorting for example, "age asc" 73 * limit page 74 */75 // select * from student where 76 // return a result set 77 // Cursor cursor = database. query (true, "student", new 78 // String [] {"id", "name", "age"}, null, null); 79 80 // if multiple tables are queried, the database cannot return the 81 Cursor cursor = database because database.exe cSQL does not return the value. rawQuery (82 "s Elect id, name, age from student where name =? ", 83 new String [] {" Xiao Li "}); 84 85 // whether the next row contains data 86 while (cursor. moveToNext () {87 // obtain the index 88 for the specific column // obtain 89 int id = cursor through the index. getInt (cursor. getColumnIndex ("id"); // The id is 90 in column 0th. // you can obtain 91 String name = cursor by using the column number. getString (1); 92 int age = cursor. getInt (2); 93 String ans = "id:" + id + "name:" + name + "age:" 94 + age; 95 Log. d ("select", ans); 96} 97 // close database connection 98 database. close (); 99} 100}

JUnit test addition, deletion, modification, and query