Android Operation SQLite Basic usage

I. Introduction to SQLite

1.SQLite Introduction

SQLite is a lightweight database, is to comply with acid-related database management system, it is designed to be embedded, and has been used in many embedded products, it occupies very low resources, in embedded devices, may only need hundreds of K of memory is enough. It can support Windows/linux/unix and other mainstream operating systems, and can be combined with many programming languages, such as Tcl, PHP, Java, C + +,. NET, as well as the ODBC interface, it is faster than the two open source world-famous database management systems, such as Mysql and PostgreSQL.

Features of 2.SQLite:

• Lightweight
  
  

Unlike the database software for SQLite and C/s mode, it is a database engine within the process, so there is no client and server for the database. With SQLite, you can use only one of its dynamic libraries to enjoy its full functionality. and the dynamic library size is also very small, in version 3.6.11 for example, under Windows 487KB, Linux under 347KB.

• No "install" required
  
  

SQLite's core engine itself does not rely on third-party software, and it does not require "installation" to use it. A little bit like that green software.

• Single File
  
  

All information in the database, such as tables, views, and so on, is contained within a single file. This file can be freely copied to other directories or other machines.

• Cross-platform/portability
  
  

In addition to the mainstream operating system Windows,linux, SQLite also supports some other infrequently used operating systems.

• Weakly-typed fields
  
  

Data in the same column can be of different types

• Open source
  
  

This!!!!!!!!!!!! that we all know.

3.SQLite Data types

Regular data uses fixed static data types, and SQLite uses Dynamic data types, which are automatically judged based on the value of the deposit. SQLite has the following five commonly used data types:

Null: This value is a null value

VARCHAR (N): A string whose length is not fixed and its maximum length is n, and N cannot exceed 4000.

CHAR (n): string with a fixed length of n, N cannot exceed 254.

Integer: The value is identified as an integer and can be stored sequentially as 1,2,3,4,5,6,7,8, depending on the size of the value.

REAL: All values are floating numeric values that are stored as 8-byte IEEE floating tag ordinals.

Text: The value is a literal string, stored using database encoding (TUTF-8, utf-16be, or Utf-16-le).

Blob: The value is a BLOB data block that is stored in the input data format. How to enter on how to store it without changing the format.

DATA: Contains the year, month, date.

Time: Contains hours, minutes, seconds.

It is believed that children's shoes that have studied the database are not unfamiliar to these data types!!!!!!!!!!

Two. Introduction of Sqlitedatabase

Android provides an API to create and be a database with SQLite. Sqlitedatabase represents a database object and provides some methods for manipulating the database. With the Sqlite3 tool in the Android SDK directory, we can use it to create databases, create tables, and execute some SQL statements. The following are common methods of sqlitedatabase.

Common methods of Sqlitedatabase

Method name	Method represents meaning
Openorcreatedatabase (String path,sqlitedatabase.cursorfactory Factory)	Open or create a database
Insert (String table,string nullcolumnhack,contentvalues values)	Insert a record
Delete (String table,string whereclause,string[] whereargs)	Delete a record
Query (String table,string[] columns,string selection,string[] selectionargs,string groupby,string having,String )	Query a record
Update (String table,contentvalues values,string whereclause,string[] whereargs)	Modify a record
Execsql (String sql)	Execute an SQL statement
Close ()	Close the database

Google's name for these methods is very graphic. For example Openorcreatedatabase, we can see from the literal English meaning that this is a way to open or create a database.




1. Open or create a database

Use Sqlitedatabase's static method Openorcreatedatabase (String path,sqlitedatabae.cursorfactory Factory) in Android to open or create a database. It automatically detects the existence of the database, opens it if it exists, creates a database if it does not exist, and then returns an Sqlitedatabase object if the creation succeeds, or throws an exception filenotfoundexception.

Here is the code to create the database named "Stu.db":
Openorcreatedatabase (String path,sqlitedatabae.cursorfactory Factory)
Parameter 1 path to database creation

Parameter 2 is generally set to null.

2. Create a table

The steps to create a table are simple:

• Write the SQL statement that created the table
• Call Sqlitedatabase's Execsql () method to execute the SQL statement
  
  

The following code creates a user table with properties listed as: ID (primary key and auto increment), sname (student name), Snumber (school number)

private void CreateTable (Sqlitedatabase db) {//CREATE TABLE SQL statement String stu_table= "CREATE TABLE usertable (_id integer PRIMARY Key A Utoincrement,sname text,snumber text) "; Execute SQL Statement db.execsql (stu_table); }

3. Inserting data
There are two methods of inserting data:
①sqlitedatabase Insert (String table,string nullcolumnhack,contentvalues values) method,
Parameter 1 table name,
Parameter 2 default value for empty column
Parameter 3 A map of the contentvalues type that encapsulates the column name and column values;
② writes SQL statements that insert data, calling Sqlitedatabase's Execsql () method directly to execute
The code for the first method:

The code for the second method:

4. Delete data

There are two ways of deleting data:

① Call Sqlitedatabase's Delete (String table,string whereclause,string[] Whereargs) method
Parameter 1 Table name
Parameter 2 Delete condition
Parameter 3 Deleting an array of condition values

② writes the Delete SQL statement and calls Sqlitedatabase's Execsql () method to perform the deletion.

The code for the first method:

The code for the second method:

private void Delete (Sqlitedatabase db) {   //delete SQL statement   String sql = "Delete from stu_table where _id = 6";   Execute SQL statement   db.execsql (SQL);   }   

5. Modify the data

There are two ways of modifying data:

① Call Sqlitedatabase's update (String table,contentvalues values,string whereclause, string[] Whereargs) method
Parameter 1 Table name
Parameter 2 is the key value pair for the row and column contentvalues type Key-value
Parameter 3 update criteria (WHERE clause)
Parameter 4 update Condition array

② writes the updated SQL statement, calling Sqlitedatabase's execsql to perform the update.

The code for the first method:

private void Update (Sqlitedatabase db) {   //instanced content value contentvalues values = new Contentvalues ();   Add content Values.put in Values   ("Snumber", "101003");   Modify   the condition String whereclause = "id=?";   Modify add parameter   string[] whereargs={string.valuesof (1)};   Modify   db.update ("usertable", Values,whereclause,whereargs);   }   

The code for the second method:

private void Update (Sqlitedatabase db) {   //Modify SQL statement   String sql = "Update stu_table set snumber = 654321 where id = 1 ";   Execute SQL   db.execsql (SQL);   }  

6. Query data

Querying data in Android is done through the cursor class, and when we use the Sqlitedatabase.query () method, we get a cursor object that points to every piece of data. It provides a number of methods for querying, as follows:

Public Cursor query (String table,string[] columns,string selection,string[] selectionargs,string groupby,string have , String orderby,string limit);

The meaning of each parameter is explained:

Parameter table: Table name

Parameter columns: array of column names

Parameter selection: Conditional sentence, equivalent to where

Parameter Selectionargs: Conditional clause, parameter array

Parameter groupby: grouping columns

Parameters having: grouping conditions

Parameter order BY: Row sequence

Parameter limit: Paging query restrictions

Parameter cursor: The return value, equivalent to the result set resultset

The cursor is a cursor interface that provides methods for traversing query results, such as Move pointer method Move (), Get column value Method GetString (), and so on.

Cursor Cursor Common methods

Method name	Method description
GetCount ()	Get total number of data items
IsFirst ()	Determine if the first record
Islast ()	Determine if the last record
Movetofirst ()	Move to the first record
Movetolast ()	Move to the last record
Move (int offset)	Move to a specified record
MoveToNext ()	Move to Next record
Movetoprevious ()	Move to previous record
Getcolumnindexorthrow (String columnName)	Get column index based on column name
getInt (int columnindex)	Gets the int type value of the specified column index
getString (int columnindex)	Gets the string type value for the specified column miniature

The following is the cursor to query the database data, the specific code is as follows:

private void query (Sqlitedatabase db) {   //query gets cursors cursor cursor   = db.query ("Usertable", Null,null,null,null, Null,null);     Determines whether the cursor is empty   if (Cursor.movetofirst () {   //traversal cursor for   (int i=0;i<cursor.getcount (); i++) {   cursor.move (i);   Get ID   int id = cursor.getint (0);   Get user name   String username=cursor.getstring (1);   Get password   String password=cursor.getstring (2);   Output user Information System.out.println (id+ ":" +sname+ ":" +snumber);}}}   

7. Delete the specified table
Write the SQL statement that inserts the data, calling Sqlitedatabase's Execsql () method directly to execute

private void Drop (Sqlitedatabase db) {   //delete table SQL statement   String sql = "drop table stu_table";   Execute SQL   db.execsql (SQL);   }  

Three.  sqliteopenhelper
The class is an auxiliary class sqlitedatabase. This class primarily generates a    database and manages the version of the database. When calling the method Getwritabledatabase () or Getreadabledatabase () method of this class in the program, if there is no data at that time, then the Android system will automatically generate a database. Sqliteopenhelper is an abstract class, and we usually need to inherit it and implement the 3 functions inside it:

1.onCreate (sqlitedatabase)

This method is called when the database is first generated. That is, it is only called when the database is created, and of course there are other situations where we generate database tables in this method.

2.  onupgrade (sqlitedatabase,int,int)  
When the database needs to be upgraded, the Android system will invoke this method on its own initiative. In general, we delete the data table in this method, and create a new data table, of course, whether we need to do other operations, depends entirely on the needs of the application.

3.  onopen (sqlitedatabase):

This is a callback function when the database is opened, and is generally not used very often in programs.

has written so much, instead of using actual examples to illustrate the above. The following example of the operation of the database to create a database, create tables and database additions and deletions to change the operation.
This instance has two classes:
Com.lingdududu.testSQLite Debug Class
com.lingdududu.testsqlitedb   Database helper class

Sqliteactivity.java

Package Com.lingdududu.testsqlite;import Com.lingdududu.testsqlitedb.studbhelper;import android.app.Activity; Import Android.content.contentvalues;import Android.database.cursor;import Android.database.sqlite.SQLiteDatabase ; Import Android.os.bundle;import Android.view.view;import Android.view.view.onclicklistener;import android.widget.button;/** @author Lingdududu*/public class Sqliteactivity extends activity {/** Called when the activity I s first created. *///Declare individual buttons private button createbtn;private button insertbtn;private button updatebtn;private button querybtn;private button Deletebtn;private button modifybtn; @Overridepublic void OnCreate (Bundle savedinstancestate) {super.oncreate ( Savedinstancestate); Setcontentview (r.layout.main);//Call Creatview method Creatview ();//setlistener method Setlistener (); }//method of obtaining a Button object by Findviewbyid private void Creatview () {createbtn = (Button) Findviewbyid (r.id.createdatabase); updatebtn = (Button) Findviewbyid (r.id.updatedatabase); insertbtn = (Button) Findviewbyid (r.id.Insert); modifybtn = (Button) Findviewbyid (r.id.update), querybtn = (button) Findviewbyid (r.id.query);d eletebtn = (Button) Findviewbyid (r.id.delete);} The method of registering the listener for the button private void Setlistener () {Createbtn.setonclicklistener (New Createlistener ()); Updatebtn.setonclicklistener (New Updatelistener ()); Insertbtn.setonclicklistener (new Insertlistener ()); Modifybtn.setonclicklistener (New Modifylistener ()); Querybtn.setonclicklistener (new Querylistener ()); Deletebtn.setonclicklistener (New Deletelistener ());} Methods for creating a database class Createlistener implements onclicklistener{@Overridepublic void OnClick (View v) {// Create Studbhelper object Studbhelper dbhelper = new Studbhelper (sqliteactivity.this, "stu_db", null,1);// Get a readable Sqlitedatabase object Sqlitedatabase db =dbhelper.getreadabledatabase ();} }//Update Database Method class Updatelistener implements onclicklistener{@Overridepublic void OnClick (View v) {//Database version update, From the original 1 to 2StuDBHelper dbhelper = new Studbhelper (sqliteactivity.this, "stu_db", null,2); Sqlitedatabase db =dbhelper.getreadabledatabase ();}}//method of inserting data class Insertlistener implements onclicklistener{@Overridepublic void OnClick (View v) {Studbhelper DBHelper = New Studbhelper (Sqliteactivity.this, "stu_db", null,1);//Get a writable database Sqlitedatabase db =dbhelper.getwritabledatabase ( );//Generate Contentvalues Object//key: Column name, value: contentvalues CV = new Contentvalues (),//to store data for Contentvalues object, Key-value pair mode cv.put ("id", 1), Cv.put ("Sname", "Xiaoming"), Cv.put ("Sage", "cv.put");//Call the Insert method, Insert data into Database Db.insert ("stu_table", NULL, CV);//Close Database Db.close ();} }//method for querying data class Querylistener implements onclicklistener{@Overridepublic void OnClick (View v) {Studbhelper DBHelper = New Studbhelper (Sqliteactivity.this, "stu_db", null,1);//Get a writable database Sqlitedatabase db =dbhelper.getreadabledatabase ( );///Parameter 1: Table name//Parameter 2: The column//parameter to be displayed 3:where clause//Parameter 4:where clause corresponding to the condition value//Parameter 5: Group//parameter 6:having condition//Parameter 7: Sort by cursor cursor = Db.query ( "Stu_table", new string[]{"id", "sname", "Sage", "Ssex"}, "Id=?", New string[]{"1"}, NULL, NULL, NULL); while ( Cursor.movetonext ()) {String name = CursoR.getstring (Cursor.getcolumnindex ("sname")); String age = cursor.getstring (Cursor.getcolumnindex ("sage")); String sex = cursor.getstring (Cursor.getcolumnindex ("Ssex")); SYSTEM.OUT.PRINTLN ("Query------->" + "Name:" +name+ "" + "Age:" +age+ "" + "Gender:" +sex);} Close database Db.close ();} }//method of modifying data class Modifylistener implements onclicklistener{@Overridepublic void OnClick (View v) {Studbhelper DBHelper = New Studbhelper (Sqliteactivity.this, "stu_db", null,1);//Get a writable database Sqlitedatabase db =dbhelper.getwritabledatabase ( ); Contentvalues CV = new Contentvalues () cv.put ("Sage", "all");//where clause "?" is the placeholder symbol, corresponding to the following "1", String whereclause= "id=?"; String [] Whereargs = {string.valueof (1)};//parameter 1 is the table name to update//Parameter 2 is a Contentvaleus object//Parameter 3 is a WHERE clause db.update ("stu_table", CV , Whereclause, Whereargs);}} Method of deleting data class Deletelistener implements onclicklistener{@Overridepublic void OnClick (View v) {Studbhelper DBHelper = New Studbhelper (Sqliteactivity.this, "stu_db", null,1);//Get a writable database Sqlitedatabase db =dbhelper.getreadabledatabase ( ); StrinG whereclauses = "id=?"; String [] Whereargs = {string.valueof (2)};//Call Delete method, delete data Db.delete ("Stu_table", whereclauses, Whereargs);}}

Studbhelper.java

Package com.lingdududu.testSQLiteDb;  Import Android.content.Context;  Import Android.database.sqlite.SQLiteDatabase;  Import Android.database.sqlite.SQLiteDatabase.CursorFactory;  Import Android.database.sqlite.SQLiteOpenHelper;    Import Android.util.Log;  public class Studbhelper extends Sqliteopenhelper {private static final String TAG = "Testsqlite";    public static final int VERSION = 1; Must have a constructor public studbhelper (context context, String name, Cursorfactory factory, int version) {Super (context, name,  Factory, version); }//When creating the database for the first time, call the method public void OnCreate (Sqlitedatabase db) {String sql = "CREATE TABLE stu_table (ID int,sname  varchar (a), Sage int,ssex varchar (10)) ";  Output log information for database creation log.i (TAG, "CREATE Database------------->");  The Execsql function is used to execute the SQL statement db.execsql (SQL); }//When updating the database, execute the method public void Onupgrade (sqlitedatabase db, int oldversion, int newversion) {//output update the log information of the database LOG.I (T  AG, "Update Database------------->");  }  }

Main.xml

<?xml version= "1.0" encoding= "Utf-8"?> <linearlayout xmlns:android= "http://schemas.android.com/apk/res/  Android "android:orientation=" vertical "android:layout_width=" fill_parent "android:layout_height=" Fill_parent " > <textview android:layout_width= "fill_parent" android:layout_height= "wrap_content" android:text= "@string/he Llo "/> <button android:id=" @+id/createdatabase "android:layout_width=" fill_parent "android:layout_height=" WR Ap_content "android:text=" CREATE Database "/> <button android:id=" @+id/updatedatabase "android:layout_width=" Fill_paren T "android:layout_height=" wrap_content "android:text=" Update Database "/> <button android:id=" @+id/insert "Android:la Yout_width= "Fill_parent" android:layout_height= "wrap_content" android:text= "Insert Data"/> <button android:id= "@+id /update "android:layout_width=" fill_parent "android:layout_height=" wrap_content "android:text=" Update data "/> <Butt On android:id= "@+id/query" android:layouT_width= "Fill_parent" android:layout_height= "wrap_content" android:text= "query data"/> <button android:id= "@+id/de Lete "android:layout_width=" fill_parent "android:layout_height=" wrap_content "android:text=" delete data "/> </Linear  Layout>

The program runs:

To view the database using the ADB command:

1. Enter the ADB shell enter in the Command Line window, enter the Linux command line, and now you can use the Linux command.

2.ls Enter, show all things, which have a data.

3.CD data Enter, then LS enter, CD data enter, LS Enter after you will see a lot of com ..., that is the application package name on the system, find the package name of your database program, and then enter.

4. Go in after looking at all, will see there is databases, enter databases, show all will find your database name, here is used "stu_db".

5.sqlite3 stu_db Enter your database, and then ". Schema" will see all of the application's tables and the build table statements.

6. You can then use the standard SQL statements to view the database you just generated and to perform additions and deletions to the data.
Note: LS,CD and other commands are basic Linux commands, do not know the students can see the information about this.

Here are a few of the ADB commands that are commonly used in SQLite:

View
. Database displays information about the databases;
. tables display table name;
The. Schema command can view the SQL commands when the data table is created;
. Schema table_name the command to view SQL when creating table table_name;

Inserting records
INSERT INTO table_name values (field1, Field2, field3 ...);

Inquire
SELECT * FROM table_name; View all records in the TABLE_NAME table;
SELECT * FROM table_name where field1= ' xxxxx '; Querying records that meet specified criteria;

Delete
Drop table_name; Delete a table;
Drop index_name; Delete index;
-------------------------------------------query, insert, delete and other operations database statements remember not to miss out;----------------------------------------

# sqlite3 stu_db
Sqlite3 stu_db
SQLite version 3.6.22
Enter '. Help ' for instructions
Enter SQL statements terminated with a ";"
Sqlite>. Schema
. Schema
CREATE TABLE android_metadata (locale TEXT);
CREATE TABLE stu_table (id int,sname varchar (), Sage int,ssex varchar (10)); ---> Created tables
Sqlite> select * from Stu_table;
SELECT * from Stu_table;
1|xiaoming|21|male
Sqlite>

Inserting data

sqlite> INSERT INTO stu_table values (2, ' xiaohong ', +, ' female ');

The inserted data remembers to correspond to attribute one by one in the table
INSERT into stu_table values (2, ' xiaohong ', ' female ');
Sqlite> select * from Stu_table;
SELECT * from Stu_table;
1|xiaoming|21|male
2|xiaohong|20|female--------------> inserted data
Sqlite>

When you click the Edit Data button

Sqlite> select * from Stu_table;
SELECT * from Stu_table;
1|xiaoming|23|male--------------> Age was modified to 23
2|xiaohong|20|female
Sqlite>


When you click the Delete Data button

Sqlite> select * from Stu_table;
SELECT * from Stu_table;
1|xiaoming|23|male id=2 data has been deleted.

Android Operation SQLite Basic usage