Android Learning Notes---database SQLite

Sqlite is a lightweight database that adheres to the acid (atomicity, consistency, isolation, durability) associated database management system and is used in embedded development. SQLite data type: typelessness (Type of object), you can save any type of data to any column of any table you want to save, but it also supports common types such as: Null,varchar,text,integer,blog,clob .... Wait a minuteThe only exception is: Integer primary key This field can only store 64 as an integer In Android, a Sqliteopenhelper abstract class is provided that is used to manage the database version that is commonly used in this class: OnCreate This method is called when the database is created (the first time the connection gets the database object is executed) Onupgrade This method is called when the database is updated (when the version number is changed) the OnOpen database executes every time the database is opened (oh, every time you open it, after the OnCreate, Onupgrade method1. Use Sqlitedatabase to manipulate the SQLite databaseAndroid provides a class called Sqlitedatabase, which encapsulates APIs that manipulate databases, which can be used to add (Create), query (Retrieve), update, and delete (delete) data. These actions are referred to as (CRUD)the Execsql () method can perform SQL statements that have changed behavior, such as INSERT, delete, update, and create table; the Rawquery () method is used to execute a SELECT statement (querying query). first create the database class:

1  Public classMysqliteopenhelperextendsSqliteopenhelper {2 3     /**4 * Database name5      */6     Private Static FinalString name = "Database.db";7     8     /**9 * Database version numberTen      */ One     Private Static Final intVersion = 1; A      -      PublicMysqliteopenhelper (Context context) { -         Super(Context, Name,NULL, version); the     } -  -     /** - * Called when database is created +      */ - @Override +      Public voidonCreate (Sqlitedatabase db) { A         //CREATE TABLE Database atString sql = "CREATE TABLE database (_id integer PRIMARY key autoincrement,name varchar (), age varchar (20))"; - db.execsql (SQL); -     } -  -     /** - * Called when database is updated in      */ - @Override to      Public voidOnupgrade (Sqlitedatabase db,intOldversion,intnewversion) { +         //TODO auto-generated Method Stub -  the     } *  $}

Sqliteopenhelper is a helper class for sqlitedatabase that manages the creation of databases and the updating of versions. It is generally established that a class inherits it and implements its OnCreate and Onupgrade methods

Method name	Method description
Public Mysqliteopenhelper (Context context, String name, Cursorfactory Factory, int version)	Construction method, generally passing some parameters
OnCreate (Sqlitedatabase db)	Called when a database is created
Onupgrade (sqlitedatabase db,int oldversion, int newversion)	Called when the version is updated
Getreadabledatabase ()	Get a readable sqlitedatabase
Getwritabledatabase ()	Get writable Sqlitedatabase

To create a database:

1 /** 2      * Database Help class 3      */ 4     Private Mysqliteopenhelper Helper; 5 6      Public Databaseutils (Context context) {7         New Mysqliteopenhelper (context); 8     }

Database additions and deletions change:

1 /**2 * Insert data into the database3      */4      Public voidInsert () {5Sqlitedatabase db =NULL;6         Try {7db = Helper.getwritabledatabase ();//get a writable sqlitedatabase8Db.begintransaction ();//Open Transaction9String sql = "INSERT into database values (NULL, ' Zhangsan ', ' 20 ')"; TenDb.execsql (SQL);//EXECUTE Statement OneDb.settransactionsuccessful ();//calling this method commits the current transaction when it executes to the Endtransaction () method, and if this method is not called, the transaction is rolled back A}finally{ -             if(db! =NULL){ -Db.endtransaction ();//Flag of the end of the transaction, whether the transaction is committed or rolled back the             } -         } - db.close (); -     } +      -     /** + * Update data in the database A      */ at      Public voidUpdate () { -Sqlitedatabase db = Helper.getwritabledatabase ();//get a writable sqlitedatabase -Contentvalues values =Newcontentvalues (); -Values.put ("name", "Lisi"); -Db.update ("Database", values, "name =?",NewString []{"Zhangsan"}); - db.close (); in     } -      to     /** + * Query Database -      */ the      Public voidquery () { *Sqlitedatabase db = Helper.getreadabledatabase ();//get a readable sqlitedatabase $String sql = "Select Name,age from Database";Panax Notoginseng          -cursor cursor = db.rawquery (SQL,NULL); the          while(Cursor! =NULL&& Cursor.movetonext ()) {//cursor is not NULL to prevent a null pointer exception MoveToNext () moves to the next line +String name = cursor.getstring (0);//get the value of the first column, the index of the first column starts at 0 A             intAge = Cursor.getint (1);//get the value of the second column theSystem.out.println ("Name:" + name + "," + "Age:" +Age ); +         } -          $ cursor.close (); $ db.close (); -          -     } the      -     /**Wuyi * Delete data from the database the      */ -      Public voidDelete () { WuSqlitedatabase db = Helper.getwritabledatabase ();//get a writable sqlitedatabase -Db.delete ("Database",NULL,NULL); About db.close (); $}

The cursor is a result set cursor that is used for random access to the result set, similar to the ResultSet effect in JDBC. Use the MoveToNext () method to move a cursor from the current row to the next row, or true if it has been moved to the last row and the result is false. The cursor also uses the usual movetoprevious () method (used to move the cursor from the current row to the previous row, if it has been moved to the first row of the result set, the return value is false, otherwise true), Movetofirst () Method (used to move the cursor to the first row of the result set, if the result set is empty, the return value is false, otherwise true), and the Movetolast () method (used to move the cursor to the last row of the result set, or true if the result set is empty);

manipulating SQLite databases with transactions Sqlitedatabase db = null;try {db = Helper.getwritabledatabase ();db.begintransaction ();//Open TransactionString sql = "INSERT into database values (NULL, ' Zhangsan ', '") ";db.execsql (SQL);db.execsql ("Update database set name=?") Where age=? ", New object[]{" Wangwu ", ());db.settransactionsuccessful ();//Call this method to commit the current transaction when it executes to the Endtransaction () method, and if this method is not called, the transaction is rolled back}finally{if (db! = null) {db.endtransaction ();//The end of the transaction flag that determines whether the transaction is committed or rolled back           }      }db.close ();Using the Sqlitedatabase BeginTransaction () method to open a transaction, the program executes to the Endtransaction () method when the flag of the transaction is checked for success if the program executes to Endtransaction () Method before the Settransactionsuccessful () method is called to set the transaction flag to successful, the transaction is committed, and the transaction is rolled back if the settransactionsuccessful () method is not called

Android Learning Notes---database SQLite