Android SQLite Database Summary

Sqlite

SQLite is an ultra-lightweight embedded database with a size of only hundreds of KB, but its syntax supports standard SQL syntax and also adheres to the acid transactions of the database, so developers who have learned other databases can easily master their use.

SQL syntax is not introduced, directly look at the use of Android

sqliteopenhelper--Packaged Database Operation helper class, need to rewrite

overriding method

OnCreate: Initialize database, CREATE TABLE, add initial data

Onupgrade: Database operations when database version upgrade, such as backup to delete database, etc.

Common methods

Getreadabledatabase () Get Sqlitedatabase object, manipulate database

Getwritabledatabase () Get Sqlitedatabase object, manipulate database

Difference: When disk space is full or not writable, the1 method will get a read-only database object, and the 2 method will error, under normal circumstances, access to read-write database objects.

ImportAndroid.content.Context;Importandroid.database.sqlite.SQLiteDatabase;ImportAndroid.database.sqlite.SQLiteOpenHelper; Public classDBHelperextendsSqliteopenhelper {Private Static FinalString name= "my";//Database name    Private Static Final intVersion=1;//Version number//When overriding a construction method, select one of the few parameters     PublicDBHelper (Context context) {//1: Context 2: Database name 3: Cursor Creation factory 4: Database version version can only be integer 1 2 3..        Super(Context, Name,NULL, version); }    //initialization of the database Sqlitedatabase database operations Object//typically only when the first run and the version update are called@Override Public voidonCreate (Sqlitedatabase db) {//CREATE DATABASE primary key default auto-incrementDb.execsql ("CREATE TABLE student (" + "_id integer NOT null primary key AutoIncrement," + "NA Me varchar, "+" phone varchar (one), "+" gender varchar (2)) "); //Add a test dataDb.execsql ("INSERT into student values (null,?,?,?)"                ,Newobject[]{"Little Black", "12345678901", "male"}); }    /*** When the version upgrade is called * Modify version 2 to indicate that the upgrade will call this method *@paramDB Database Operations Object *@paramoldversion old version number *@paramnewversion new version number*/@Override Public voidOnupgrade (Sqlitedatabase db,intOldversion,intnewversion) {    }}

The creation of an Android database is an object that needs to be created to create

Create a database class object in the Activity class

Create a good data object to manipulate the data. The difference between obtaining two methods of acquisition through Sqlitedatabase has already been mentioned.

 　　　　// Create Data        DBHelper helper =new dbhelper (this);         // invoking data manipulation objects        Sqlitedatabase dbwrite=helper.getwritabledatabase ();        Sqlitedatabase dbread=helper.getreadabledatabase ();

Sqlitedatabase provides us with a number of ways to manipulate data.

Delete: (int) Delete (String table,string whereclause,string[] whereargs)

Table: Tables Name

Whereclause:where conditional column name placeholder id=?

Whereargs: Array of parameter values

Added: (long) Insert (String table,string nullcolumnhack,contentvalues values)

Nullcolumnhack: Empty column

Contentvalues values: Data added by key value to storage key is column value

The bottom of the Insert method is by stitching the string in a way that if contentvalues is empty, the concatenation of the SQL statement cannot be executed will result in an error so give an empty column when Contentvalues is empty you can also take a look at the source code

Update: (int) update (string table, contentvalues values, String whereclause, string[] whereargs)

parameter meaning ibid.

Query: (Cursor) query (Boolean distinct,string table, string[] columns, string selection, string[] Selectionargs, string group By, string has, string-by-clause, string limit)

The return value is a cursor. A query with more simple queries will write a simple SQL statement

Boolean distinct to repeat

String Table Table Name

string[] Columns the column to query

String Selection Query criteria

string[] Selectionargs Query parameter value

String groupby Grouping

String Having grouping condition

Sort by String by order

String Limit Paging Query restrictions

Close database: (void) Close ()

Executes an SQL statement: (void) execsql (String sql)

Query Query sql: (Cursor) rawquery (String sql, string[] selectionargs)

Transaction

        Try {            db.begintransaction (); // Open Transaction             // db.update ();             // Db.insert ();            // If you don't set things up, finally, they roll        back. Catch (Exception e) {            e.printstacktrace ();        } finally {            db.endtransaction ();        }    

Paste the code. Execute SQL statements and encapsulated methods

Inquire

   Private voidquery () {StringBuffer sb=NewStringBuffer ("SELECT * from student where 1=1"); //Parameter CollectionList<string> params=NewArraylist<>(); if(!textutils.isempty (ID)) {Sb.append ("and _id=?");        Params.add (ID); }        if(!textutils.isempty (phone)) {Sb.append ("and phone=?");        Params.add (phone); }        if(!textutils.isempty (name)) {Sb.append ("and name=?");        Params.add (name); }        if(!Textutils.isempty (gender)) {Sb.append ("and gender=?");        Params.add (gender); } sqlitedatabase DB=helper.getreadabledatabase (); String [] Projection=NewString [Params.size ()];        Params.toarray (projection); //return value CursorsCursor cursor=Db.rawquery (sb.tostring (), projection);
Determines whether the cursor is empty and whether there is a value while(cursor!=NULL&&Cursor.movetonext ()) {            //Getcolumnindex Gets the column's subscript cursor.getxxxx () Gets the value of the specified columnString name=cursor.getstring (Cursor.getcolumnindex ("name")); Integer ID=cursor.getint (Cursor.getcolumnindex ("_id")); }    }

Let it be empty if you don't have the parameters. Traversing the data is just the while loop.

Cursor C = db.query ("student",null,null,null,null,null, null ); // querying and obtaining cursors

Update

  Private voidUpdate () {StringBuffer sb=NewStringBuffer ("Update student Set"); List params=NewArrayList (); if(!textutils.isempty (phone)) {Sb.append ("Phone=?");        Params.add (phone); }        if(!textutils.isempty (name)) {Sb.append ("Name=?");        Params.add (name); }        if(!Textutils.isempty (gender)) {Sb.append ("Gender=?");        Params.add (gender); }        if(Params.size ()!=0){            //Update operation stitching String at the end there is a "," need to remove//Delete the last one ","Sb.setlength (Sb.length ()-1); Sb.append ("Where 1=1"); //update the row data with the ID designation            if(!textutils.isempty (ID)) {Sb.append ("and _id=?");            Params.add (ID); }Else{Toast.maketext ( This, "Please fill in the id", Toast.length_short). Show (); return; } sqlitedatabase DB=helper.getwritabledatabase (); Object [] o=Newobject[params.size ()]; Params.toarray (o);//to store the data in the specified arrayDb.execsql (sb.tostring (), O); }    }

New Contentvalues (); // Instantiate contentvaluescv.put ("name", "123"); // add the fields and content you want to change String whereclause = "phone=?"; // Modify Condition String[] Whereargs = {"12312313213"}; // Modify the parameters of the condition db.update ("Student", Cv,whereclause,whereargs); // Perform modifications

Delete

   Private voidDelete () {getalltext (); //Splicing SQL statementsStringBuffer sb=NewStringBuffer ("Delete from student where 1=1"); //List of saved parametersList params=NewArrayList (); //dynamic stitching of judging conditions        if(!textutils.isempty (ID)) {Sb.append ("and _id=?");        Params.add (ID); }        if(!textutils.isempty (phone)) {Sb.append ("and phone=?");        Params.add (phone); }        if(!textutils.isempty (name)) {Sb.append ("and name=?");        Params.add (name); }        if(!Textutils.isempty (gender)) {Sb.append ("and gender=?");        Params.add (gender); } sqlitedatabase DB=helper.getwritabledatabase (); if(Params.size ()!=0) {Object [] o=Newobject[params.size ()]; Params.toarray (o);//to store the data in the specified array//Perform the deleteDb.execsql (sb.tostring (), O); }Else{db.execsql (sb.tostring ()); } toast.maketext ( This, "Delete Complete", Toast.length_short). Show (); }

String whereclause = "name=?"; // conditions to remove String[] Whereargs = {"123"}; // Delete the condition parameter db.delete ("Student", Whereclause,whereargs); // Perform the delete

Increase

  Private voidInsert () {//saving data to an object arrayObject [] o=NewObject[]{name,phone,gender}; //Get database Operations ObjectSqlitedatabase db=helper.getwritabledatabase (); //sql:sql statement Bingargs: Parameter arrayDb.execsql ("INSERT into student values (null,?,?,?)", O); //Close ConnectionDb.close (); Toast.maketext ( This, "Increased success", Toast.length_short). Show (); }

New Contentvalues (); // instantiate a contentvalues to load the data to be inserted cv.put ("name", "123"); Db.insert ("student",null, CV); // Perform an insert operation

Android SQLite Database Summary