SQLite addition, deletion, modification, and query with code

1. Access the database

Run the following command to enter the database:

1. ADB

2. ADB Shell

3. CD Data

4. CD Data

5. CD [package name]

6. After creating a database

7. CD Databases

8. sqlite3 [database name]

Put the data inventory under/data/[package name]/databases/path.

 

Common commands

. Schema: View tables in the current database

View the data in the table select * from [tablename];

 

Ii. knowledge points

1. Public void execsql (string SQL );

Execute a single SQL statement, but cannot execute select and other SQL statements with returned values;

 

2. SQL statements

Statement: Create Table tabname (col1 type1 [not null] [primary key], col2 type2 [not null],...)

Note: tabname -- table name, col1, col2 -- field name (that is, column name), not null -- this field is not empty, primary key -- primary key (the unique identifier of a record ).

 

3. Public long insert (string table, string nullcolumnhack, contentvalues values)

Note: insert record, table -- table name, nullcolumnhack -- forcibly insert null value fields (column names), values -- data of a record row.

 

4. Public int Update (string table, contentvalues values, string whereclause, string [] whereargs)

Note: update data in the database; table -- Name of the table to be updated, values -- data to be updated, and whereclause -- Records meeting the whereclause clause will be updated, whereargs -- input a parameter for whereclause. (It can be understood that in the table, data is searched based on whereclause and whereargs and updated to values)

For example, update the names of all persons whose primary keys are greater than 20 in the person_inf table,

Contentvalues values = new contentvalues ();

Values. Put ("name", "new person name ");

Int result = dB. Update ("person_inf", values, "_ id & gt ;?", New integer [] {20 });

 

5. Public cursor query (string table, string [] columns, string selection, string [] selectionargs, string groupby, string having, string orderby)

Note: For details, see help.

 

6. oncreate method in sqliteopenhelper class

Public abstract void oncreate (sqlitedatabase dB );

When the getwritabledatabase () or getreadabledatabase () method of sqliteopenhelper is called to obtain the sqlitedatabase instance used to operate the database, if the database does not exist, the android system automatically generates a database and then calls the oncreta method, the oncreate method is called only when the database is generated for the first time.

That is, when getwritabledatabase () and getreadabledatabase () are used to obtain an instance, if the database does not exist, it is created. If the database does not exist, the oncreate method is automatically called when the instance is created for the first time. Therefore, you can override this method to initialize the data table structure and data.

 

3. The database version must be a positive number!

4. Master two types of database operations: sqlitedatabase and sqliteopenhelper!

5. Understanding how to create databases and tables

 

Vi. instance code

Package com. luocus. erseventh;

Import Android. content. contentvalues;
Import Android. content. context;
Import Android. database. cursor;
Import Android. database. SQLite. sqlitedatabase;
Import Android. database. SQLite. sqlitedatabase. cursorfactory;
Import Android. database. SQLite. sqliteopenhelper;

Public class databasehelper extends sqliteopenhelper {
Private Final Static string db_name = "erseventh. DB"; // Database Name
Private Final Static string table_name = "erseventhtable"; // table name
Public final static string field_id = "_ id"; // table column name, primary key
Public final static string field_event_name = "eventname"; // table column name
Public final static string field_remind_time = "remindtime"; // table column name
/** This constructor must exist in the subclass of sqliteopenhelper. context is the activity object, and name is the database name */
Public databasehelper (context, string name, cursorfactory factory,
Int version ){
Super (context, db_name, factory, version); // call the constructor of the parent class
// Todo auto-generated constructor stub
}
/** This constructor must exist in the subclass of sqliteopenhelper. context is the activity object, and name is the database name */
/** This function is executed when the database is created for the first time. It is automatically called when the getwritabledatabase () or getreadabledatabase () method is called */
Public void oncreate (sqlitedatabase dB ){
// Todo auto-generated method stub
String SQL = "CREATE TABLE" +
Table_name +
"(" +
Field_id +
"Integer primary key," + // note "Leading and trailing spaces !!!
Field_event_name +
"Varchar," +
Field_remind_time +
"Varchar )";
Db.exe csql (SQL );
// Db.exe csql ("create table evtesttable (_ id int primary key, eventname varchar (20), remindtime varchar (20 )");
// Db.exe csql ("create table eventname (_ id int primary key, eventname varchar (20 ))");
System. Out. println ("Create a table ");
}
/** This function is executed when the database is created for the first time. It is automatically called when the getwritabledatabase () or getreadabledatabase () method is called */
/** Insert */
Public void insert (string myeventname, string myremindtime)
{
Sqlitedatabase DB = This. getwritabledatabase ();
Contentvalues CV = new contentvalues ();
Cv. Put (field_event_name, myeventname );
Cv. Put (field_remind_time, myremindtime );
DB. insert (table_name, null, CV); // The second parameter is null, and the primary key auto-increment is 1.
DB. Close ();
System. Out. println ("insert data ");
}
/** Insert */
/** Modify the field_id to the ID of the record, and modify the field_event_name and field_remind_tim */
Public void Update (int id, string neweventname, string newremindtime)
{
Sqlitedatabase DB = This. getwritabledatabase ();
String where = field_id + "=? ";
String [] wherevalue = {INTEGER. tostring (ID )};
Contentvalues CV = new contentvalues ();
Cv. Put (field_event_name, neweventname );
Cv. Put (field_remind_time, newremindtime );
DB. Update (table_name, CV, where, wherevalue );
System. Out. println ("modify data ");
}
/** Modify the field_id to the ID of the record, and modify the field_event_name and field_remind_tim */
/** Delete: delete a record whose field_id is ID */
Public void Delete (int id)
{
Sqlitedatabase DB = This. getwritabledatabase ();
String where = field_id + "=? ";
String [] wherevalue = {INTEGER. tostring (ID )};
DB. Delete (table_name, where, wherevalue );
System. Out. println ("delete data ");
}
/** Delete: delete a record whose field_id is ID */

/** Delete a database? */
Public Boolean deletedbbyname (string dbname ){
Context context = NULL;
Context. deletedatabase (dbname );
Return false;
}
/** Delete a database? */
/** Obtain the cursor */
Public cursor getcursor ()
{
Sqlitedatabase DB = This. getwritabledatabase ();
Cursor cursor = dB. Query (table_name, null );
Return cursor;
}
/** Obtain the cursor */
/** Query: query records whose field_id is ID */
Public cursor query (int id)
{
Sqlitedatabase DB = This. getwritabledatabase ();
String where = field_id + "=? ";
String [] wherevalue = {INTEGER. tostring (ID )};
Cursor cursor = dB. Query
(
Table_name,
New String [] {"_ id", "eventname", "remindtime "},
Where,
Wherevalue,
Null, null, null
);
Return cursor;
}
/** Query: query records whose field_id is ID */
@ Override
Public void onupgrade (sqlitedatabase dB, int oldversion, int newversion ){
// Todo auto-generated method stub
}
} // End class databasehelper