Android programming operation Embedded Relational SQLite database example detailed _android

An example of this paper is to analyze the method of Android programming operation embedded Relational SQLite database. Share to everyone for your reference, specific as follows:

SQLite features

A relational database SQLite is embedded in the 1.Android platform, unlike other databases where SQLite store data without distinguishing between types

For example, if a field is declared as an integer, we can also deposit a string, a field declared as a Boolean, or we can deposit a floating-point number.

Only 64-bit integers can be stored unless the primary key is defined as Integer

2. You can create a table for a database without specifying a data type, for example:

Copy Code code as follows:

CREATE Tableperson (id INTEGER PRIMARY KEY, name)

3.SQLite support most of the standard SQL statements, additions, deletions, and search statements are common, paging query statements and MySQL are the same

SELECT * Fromperson LIMIT OFFSET
select * Fromperson LIMIT 20,10

Creating a Database

1. Define Class inheritance Sqliteopenhelper
2. Declare constructor, 4 parameters
3. Rewrite the OnCreate () method
4. Rewrite the upgrade () method

Example:

package cn.itcast.sqlite;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
public class DBOpenHelper extends SQLiteOpenHelper {
  / **
   * Create OpenHelper
   * @param context
   * @param name database name
   * @param factory cursor factory
   * @param version database version, do not set to 0, if it is 0, the database will be created every time
   * /
  public DBOpenHelper (Context context, String name, CursorFactory factory, int version) {
    super (context, name, factory, version);
  }
  / **
   * Called when the database is first created
   * /
  public void onCreate (SQLiteDatabase db) {
    db.execSQL ("CREATE TABLE person (id INTEGER PRIMARY KEY AUTOINCREMENT, name)");
  }
  / **
   * Called when the database version changes
   * /
  public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL ("ALTER TABLE person ADD balance");
  }
}
public void testCreateDB () {
  DBOpenHelper helper = new DBOpenHelper (getContext (), "itcast.db", null, 2);
  helper.getWritableDatabase (); // create database
}

CRUD Operations

1. Unlike JDBC Access database, Operation SQLite database does not need to load the driver, does not need to obtain the connection, directly may use

After you get the Sqlitedatabase object, you can execute the SQL statement directly from the object:

Sqlitedatabase.execsql ()
sqlitedatabase.rawquery ()

The difference between 2.getReadableDatabase () and Getwritabledatabase ()

After viewing the source code we find that Getreadabledatabase () normally returns the database that Getwritabledatabase () is only open when an exception is thrown

3. Database Object Caching

The Getwritabledatabase () method remembers this database object with a member variable at the end, and then the next time you open it to determine if it is reused

4.SQLiteDatabase encapsulates insert (), delete (), update (), query () Four methods and can also manipulate the database

These methods encapsulate some of the SQL statements and stitch through the parameters

There are two ways to perform crud operations, the first way to write your own SQL statements, and the second is to perform actions by using the Sqlitedatabase class to invoke the response

The Execsql () method can perform SQL statements such as INSERT, delete, update, and CreateTable, and the Rawquery () method executes the SELECT statement.

Example of the first approach:

package cn.itcast.sqlite.service;
import java.util.ArrayList;
import java.util.List;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import cn.itcast.sqlite.DBOpenHelper;
import cn.itcast.sqlite.domain.Person;
public class SQLPersonService {
  private DBOpenHelper helper;
  public SQLPersonService (Context context) {
    helper = new DBOpenHelper (context, "itcast.db", null, 2); // Initialize the database
  }
  / **
   * Insert a Person
   * @param p Person to insert
   * /
  public void insert (Person p) {
    SQLiteDatabase db = helper.getWritableDatabase (); // Get to the database
    db.execSQL ("INSERT INTO person (name, phone, balance) VALUES (?,?)", new Object [] {p.getName (), p.getPhone ()});
    db.close ();
  }
  / **
   * Delete by ID
   * @param id ID of PERSON to delete
   * /
  public void delete (Integer id) {
    SQLiteDatabase db = helper.getWritableDatabase ();
    db.execSQL ("DELETE FROM person WHERE id =?", new Object [] {id});
    db.close ();
  }
  / **
   * Update Person
   * @param p Person to update
   * /
  public void update (Person p) {
    SQLiteDatabase db = helper.getWritableDatabase ();
    db.execSQL ("UPDATE person SET name = ?, phone = ?, balance =? WHERE id =?", new Object [] {p.getName (), p.getPhone (), p.getBalance (), p. getId ()});
    db.close ();
  }
  / **
   * Find by ID
   * @param id ID to check
   * @return corresponding object, return null if not found
   * /
  public Person find (Integer id) {
    SQLiteDatabase db = helper.getReadableDatabase ();
    Cursor cursor = db.rawQuery ("SELECT name, phone, balance FROM person WHERE id =?", New String [] {id.toString ()});
    Person p = null;
    if (cursor.moveToNext ()) {
      String name = cursor.getString (cursor.getColumnIndex ("name"));
      String phone = cursor.getString (1);
      Integer balance = cursor.getInt (2);
      p = new Person (id, name, phone, balance);
    }
    cursor.close ();
    db.close ();
    return p;
  }
  / **
   * Query all Person objects
   * @return Person object collection, if not found, returns a List with size () 0
   * /
  public List <Person> findAll () {
    SQLiteDatabase db = helper.getReadableDatabase ();
    Cursor cursor = db.rawQuery ("SELECT id, name, phone, balance FROM person", null);
    List <Person> persons = new ArrayList <Person> ();
    while (cursor.moveToNext ()) {
      Integer id = cursor.getInt (0);
      String name = cursor.getString (1);
      String phone = cursor.getString (2);
      Integer balance = cursor.getInt (3);
      persons.add (new Person (id, name, phone, balance));
    }
    cursor.close ();
    db.close ();
    return persons;
  }
  / **
   * Query data on a page
   * @param page page number
   * @param size records per page
   * @return
   * /
  public List <Person> findPage (int page, int size) {
    SQLiteDatabase db = helper.getReadableDatabase ();
    Cursor cursor = db.rawQuery ("SELECT id, name, phone, balance FROM person LIMIT?,?" //
        , new String [] {String.valueOf ((page-1) * size), String.valueOf (size)});
    List <Person> persons = new ArrayList <Person> ();
    while (cursor.moveToNext ()) {
      Integer id = cursor.getInt (0);
      String name = cursor.getString (1);
      String phone = cursor.getString (2);
      Integer balance = cursor.getInt (3);
      persons.add (new Person (id, name, phone, balance));
    }
    cursor.close ();
    db.close ();
    return persons;
  }
  / **
   * Get the number of records
   * @return records
   * /
  public int getCount () {
    SQLiteDatabase db = helper.getReadableDatabase ();
    Cursor cursor = db.rawQuery ("SELECT COUNT (*) FROM person", null);
    cursor.moveToNext ();
    return cursor.getInt (0);
  }
}

The second way example:

/ **
 * Insert a Person
 * @param p Person to insert
 * /
public void insert (Person p) {
  SQLiteDatabase db = helper.getWritableDatabase ();
  ContentValues values = new ContentValues ();
  values.put ("name", p.getName ());
  values.put ("phone", p.getPhone ());
  values.put ("balance", p.getBalance ());
  // The first parameter is the table name, the second parameter is the name of a column specified when an empty record is to be inserted, and the third parameter is the data
  db.insert ("person", null, values);
  db.close ();
}
/ **
 * Delete by ID
 * @param id ID of PERSON to delete
 * /
public void delete (Integer id) {
  SQLiteDatabase db = helper.getWritableDatabase ();
  db.delete ("person", "id =?", new String [] {id.toString ()});
  db.close ();
}
/ **
 * Update Person
 * @param p Person to update
 * /
public void update (Person p) {
  SQLiteDatabase db = helper.getWritableDatabase ();
  ContentValues values = new ContentValues ();
  values.put ("id", p.getId ());
  values.put ("name", p.getName ());
  values.put ("phone", p.getPhone ());
  values.put ("balance", p.getBalance ());
  db.update ("person", values, "id =?", new String [] {p.getId (). toString ()});
  db.close ();
}
/ **
 * Find by ID
 * @param id ID to check
 * @return corresponding object, return null if not found
 * /
public Person find (Integer id) {
  SQLiteDatabase db = helper.getReadableDatabase ();
  Cursor cursor = db.query ("person", new String [] {"name", "phone", "balance"}, "id =?", New String [] {id.toString ()}, null, null , null);
  Person p = null;
  if (cursor.moveToNext ()) {
    String name = cursor.getString (cursor.getColumnIndex ("name"));
    String phone = cursor.getString (1);
    Integer balance = cursor.getInt (2);
    p = new Person (id, name, phone, balance);
  }
  cursor.close ();
  db.close ();
  return p;
}
/ **
 * Query all Person objects
 * @return Person object collection, if not found, returns a List with size () 0
 * /
public List <Person> findAll () {
  SQLiteDatabase db = helper.getReadableDatabase ();
  Cursor cursor = db.query ("person", new String [] {"id", "name", "phone", "balance"}, null, null, null, null, "id desc");
  List <Person> persons = new ArrayList <Person> ();
  while (cursor.moveToNext ()) {
    Integer id = cursor.getInt (0);
    String name = cursor.getString (1);
    String phone = cursor.getString (2);
    Integer balance = cursor.getInt (3);
    persons.add (new Person (id, name, phone, balance));
  }
  cursor.close ();
  db.close ();
  return persons;
}
/ **
 * Query data on a page
 * @param page page number
 * @param size records per page
 * @return
 * /
public List <Person> findPage (int page, int size) {
  SQLiteDatabase db = helper.getReadableDatabase ();
  Cursor cursor = db.query (//
      "person", new String [] {"id", "name", "phone", "balance"}, null, null, null, null, null, (page-1) * size + "," + size) ;
  List <Person> persons = new ArrayList <Person> ();
  while (cursor.moveToNext ()) {
    Integer id = cursor.getInt (0);
    String name = cursor.getString (1);
    String phone = cursor.getString (2);
    Integer balance = cursor.getInt (3);
    persons.add (new Person (id, name, phone, balance));
  }
  cursor.close ();
  db.close ();
  return persons;
}
/ **
 * Get the number of records
 * @return records
 * /
public int getCount () {
  SQLiteDatabase db = helper.getReadableDatabase ();
  Cursor cursor = db.query (//
      "person", new String [] {"COUNT (*)"}, null, null, null, null, null);
  cursor.moveToNext ();
  return cursor.getInt (0);
}

Transaction management

1. You can control transactions using the related methods defined in the Sqlitedatabase class when you use the SQLite database

BeginTransaction () Open transaction
Settransactionsuccessful () Set Transaction Success Token
Endtransaction () End transaction

2.endTransaction () needs to be put into a finally execution, otherwise the transaction will only end automatically when it times out, reducing database concurrency efficiency

Example:

public void remit (int from, int to, int amount) {
   SQLiteDatabase db = helper.getWritableDatabase ();
   // start transaction
   try {
     db.beginTransaction ();
     db.execSQL ("UPDATE person SET balance = balance-? WHERE id =?", new Object [] {amount, from});
     System.out.println (1/0);
     db.execSQL ("UPDATE person SET balance = balance +? WHERE id =?", new Object [] {amount, to});
     // set transaction flag
     db.setTransactionSuccessful ();
   } finally {
     // end transaction
     db.endTransaction ();
   }
   db.close ();
}  

I hope this article will help you with the Android program.