Use SQLite database under Android

features of the 1.SQLite database
Android phone comes with a small, suitable for use in mobile phones
Data types are not distinguished (except for primary keys)
SQL statements are almost the same as MySQL
SQLite does not use a JDBC connection and uses Android's own API
Each database corresponds to a file
* 2. Create a database
Define class inheritance Sqliteopenhelper, implement OnCreate (), Onupgrade ()
Create the Class object, call Getwritabledatabse () or GETREADABLEDATABSE ()
Scenario 1: Database file does not exist, create file, open database connection (Get Sqlitedatabase object), execute OnCreate () method
Scenario 2: Database file exists, version number is not changed, open database connection
Scenario 3: The database file exists, the version number is promoted, the database is upgraded, the database connection is opened, and the Onupgrade () method is executed

Scenario 4: The database file exists, the version number is reduced, the Ondowngrade () method is executed, and an exception is thrown by default in the method

Code: Mysqliteopenhelper.java

Package Com.oterman.mysqlite;import Android.content.context;import Android.database.sqlite.sqlitedatabase;import Android.database.sqlite.sqlitedatabase.cursorfactory;import Android.database.sqlite.sqliteopenhelper;public Class Mysqliteopenhelper extends Sqliteopenhelper {/** * Because the parent class does not have a default parameterless constructor, it is necessary to display the write-out constructor, and then to call the parent class with arguments to the constructor; The parameter 1:context represents the application's environment and is used to determine the location of the database file; * Parameter 2: The name of the database file; * Parameter 3: The factory used to create the cursor of the result set, the default pass in NULL; * Parameter 4: The version number of the data, starting from 1; * @param context * @param version */public mysqliteopenhelper (context Context,int version) {Super (context, "myfirstdb.db", null,version) ;} Public Mysqliteopenhelper (Context context) {Super (context, "myfirstdb.db", null,1);} /** * If the database file does not exist, call the method; */@Overridepublic void OnCreate (Sqlitedatabase db) {System.out.println ("database Created");d B.execsql (" CREATE TABLE account (_id Integer primary key autoincrement,name varchar (40));} /** * Database file exists, the version number is changed, the method is called; */@Overridepublic void Onupgrade (sqlitedatabase db, int oldversion, int newversion) {System . OUT.PRINTLN ("Database Upgrade");}}

* 3. Create a table or modify a table
The Execsql () method of the Sqlitedatabase class can execute an SQL statement
If you want to create a database when you create some tables, then this operation can be executed in the OnCreate () method
If you want to do something like modifying tables to add tables when the database is upgraded, you can do this in the Onupgrade () method.
4. Adding and deleting changes
Execsql () method can be used for the operation of adding and pruning
Rawquery () Performs a query operation, gets the cursor, calls MoveToNext () to determine if it contains data, calls GetString (), GetInt (), and other methods to obtain data
Insert (), delete (), update (), query () Four methods internal is also called Execsql () and Rawquery (), they are used in contentprovider more convenient (tomorrow)
* 5. Transaction management
BeginTransaction () Open transaction
Settransactionsuccessful () Set Transaction Success Token
Endtransaction () ends the transaction.

At the end of the transaction, the action before the last success token is committed, and the action after the successful tag is rolled back

Code: Accout.java

Package Domain;public class Account {Private integer id;p rivate String name;private Integer balance;p ublic account (Integ ER ID, String name, Integer balance) {super (); this.id = Id;this.name = Name;this.balance = balance;} Public account () {super ();} Public Integer GetId () {return ID;} Public account (String name, Integer balance) {super (); this.name = Name;this.balance = balance;} public void SetId (Integer id) {this.id = ID;} Public String GetName () {return name;} public void SetName (String name) {this.name = name;} Public Integer GetBalance () {return balance;} public void Setbalance (Integer balance) {this.balance = balance;} @Overridepublic String toString () {return "account [id=" + ID + ", name=" + name + ", balance=" + Balance + "]";}}

Accountdao.java

Package Com.oterman.dao;import Java.util.arraylist;import Java.util.list;import android.content.context;import Android.database.cursor;import Android.database.sqlite.sqlitedatabase;import Com.oterman.mysqlite.mysqliteopenhelper;import domain. Account;public class Accountdao {private Context context;public Accountdao (context context) {This.context = context;} public void Insert (account a) {//Get database; Mysqliteopenhelper helper=new mysqliteopenhelper (context); Sqlitedatabase db=helper.getwritabledatabase ();//Operation database; Db.execsql ("INSERT into account values (null,?,?)", New Object []{a.getname (), A.getbalance ()});//close database; Db.close ();} Delete record; public void Delete (int i) {mysqliteopenhelper helper=new mysqliteopenhelper (context); Sqlitedatabase db=helper.getwritabledatabase ();//Get the database; Db.execsql ("Delete from account where _id=?", New Object[]{i}) ;d b.close ();} Modify the database; public void Update () {Mysqliteopenhelper helper=new mysqliteopenhelper (context); Sqlitedatabase db=helper.getwritabledatabase ();d b.execsql ("Update AccoUNT set balance=? where _id<? ", New object[]{1000,9});d b.close ();} query database; public account query (int i) {//Get database; Mysqliteopenhelper helper=new mysqliteopenhelper (context); Sqlitedatabase db=helper.getwritabledatabase ();//Execute query statement; get result set; Cursor c=db.rawquery ("Select Name,balance from account where _id=? ", New string[]{i+" "}); Account A=null;while (C.movetonext ()) {String name=c.getstring (0); Int. balance=c.getint (1); A=new account (I,name, balance);} return A;} query all; public list<account> Queryall () {mysqliteopenhelper helper=new mysqliteopenhelper (context); Sqlitedatabase db=helper.getwritabledatabase (); List<account> list=new arraylist<account> (); String sql= "SELECT * from Account"; Cursor c=db.rawquery (SQL, NULL), while (C.movetonext ()) {int id=c.getint (0); String name=c.getstring (C.getcolumnindex ("name")), int balance=c.getint (C.getcolumnindex ("balance")), List.add (new Account (Id,name,balance));} C.close ();d b.close (); return list;} Demo transaction public void trans (int fromid,int toid, int amount) {MySQliteopenhelper helper=new mysqliteopenhelper (context); Sqlitedatabase db=helper.getwritabledatabase (); String sql1= "Update account set balance=balance-?" where _id=? "; String sql2= "Update account set balance=balance+?" where _id=? "; Try{db.begintransaction ();//open transaction; Db.execsql (SQL1, New Object[]{amount,fromid});d B.execsql (SQL2, New object[]{ Amount,toid});d b.settransactionsuccessful ();//You can set multiple marker points; group commits; If no exception occurs before the mark Point, all previous SQL operations are committed; Db.execsql (SQL1, new Object[]{amount,fromid});d B.execsql (SQL2, New object[]{amount,toid});d b.settransactionsuccessful ();d B.execsql ( SQL1, New Object[]{amount,fromid}); int i=1/0;db.execsql (SQL2, New object[]{amount,toid}); Db.settransactionsuccessful ();//Mark point; When an exception occurs, all content of the mark point that is the first mark point is rolled back;}finally{db.endtransaction ();d b.close ();}}

Use SQLite database under Android