Use of SQLite database

SQLite database

• Lightweight relational database

• Api:sqliteopenhelper (abstract class, SQLite open Helper) required to create a database

  • Create Myoperhelper class, inherit from Sqliteoperhelper

  • You must define a construction method:

    //arg1:数据库文件的名字//arg2:游标工厂（传null，使用默认）//arg3:数据库版本（比如刚开始三个字段，然后需要四个版本号，这里给高。这一不能降级）public MyOpenHelper(Context context, String name, CursorFactory factory, int version){}

  • Called when the database is created: OnCreate method
  • Called when the database is upgraded: Onupgrade method
    
    • We can use the test framework described above to test database creation and upgrade using the below method

Create a database

//创建OpenHelper对象MyOpenHelper oh = new MyOpenHelper(getContext(), "person.db", null, 1);//获得数据库对象,如果数据库不存在，先创建数据库，后获得，如果存在，则直接获得SQLiteDatabase db = oh.getWritableDatabase();

• Getwritabledatabase (): Open a writable database
• Getreadabledatabase (): Open read-only database when disk space is low, otherwise open read-write database
• The database files created can be viewed using SQLite Expert.exe. This tool is very useful.

• Create a table when you create a database

  public void onCreate(SQLiteDatabase db) {    // TODO Auto-generated method stub    db.execSQL("create table person (_id integer primary key autoincrement, name char(10), phone char(20), money integer(20))");//primary主键，autoincrement自增长}

  (SQLite is a simplified string, here Char,integer, is for programmers to see, in fact, the inside is a string, the value will not be checked, but if the wrong data type is not saved)
  If the bottom pass is (' Zhang San ', ' 159874611 ', ' abc '), check the table, the actual deposit is 0, but will not be saved

Database additions and Deletions change SQL statements

• INSERT into person (name, phone, money) VALUES (' Zhang San ', ' 159874611 ', 2000);
• Delete from the person where name = ' John Doe ' and _id = 4;
• Update person Set money = 6000 where name = ' John Doe ';
• Select name, phone from person where name = ' Zhang San ';

Execute SQL statements to implement additions and deletions

    //插入    db.execSQL("insert into person (name, phone, money) values (?, ?, ?);", new Object[]{"张三", 15987461, 75000});    //查找    Cursor cs = db.rawQuery("select _id, name, money from person where name = ?;", new String[]{"张三"});

* This method is called before the test method executes

    protected void setUp() throws Exception {        super.setUp();        //                  获取虚拟上下文对象        oh = new MyOpenHelper(getContext(), "people.db", null, 1);    }

* Here is my test source

Database test public class TestCase extends Androidtestcase {//At this time the test framework has not been initialized, there is no virtual context object//private Myopenhelper Oh = new Myopenhe Lper (GetContext (), "people.db", NULL, 1);p rivate myopenhelper oh;private sqlitedatabase db;public void Test () {//getcon    Text (): Gets a virtual context myopenhelper Oh = new Myopenhelper (GetContext (), "people.db", NULL, 1);    If the database does not exist, first create the database, and then obtain a readable writable database object, if the database exists, directly open sqlitedatabase db = Oh.getwritabledatabase (); If the storage space is full, then the read-only database object is returned//Sqlitedatabase db = Oh.getreadabledatabase ();}    After the test framework is initialized, this method calls @overrideprotected void SetUp () throws Exception {Super.setup () before the test method executes;    Oh = new Myopenhelper (GetContext (), "people.db", NULL, 1); db = Oh.getwritabledatabase ();} After the test method has finished executing, this method calls @overrideprotected void TearDown () throws Exception {//TODO auto-generated method stub Super.tea    Rdown (); Db.close ();} public void Insert () {///Db.execsql ("Insert to person (name, salary, phone) values (?,?,?)", New object[]{"wife [1]", "     13000 ", 138438});// Db.execsql ("INSERT into person (name, salary, phone) values (?,?,?)", New object[]{"Son", 14000, "13888"}); Db.execsql ("INSERT into person (name, salary, phone) values (?,?,?)", New object[]{"small", 14000, "13888"});} public void Delete () {db.execsql ("delete from person where name =?", New object[]{"small"}); public void Update () {db.execsql ("update person set phone =?") WHERE name =? ", new object[]{186666," Son "});    public void Select () {cursor cursor = db.rawquery ("Select name, salary from", null);        while (Cursor.movetonext ()) {//Gets the value of the column by the column index String name = cursor.getstring (Cursor.getcolumnindex ("name"));        String salary = cursor.getstring (1);    SYSTEM.OUT.PRINTLN (name + ";" + salary);    }}public void Insertapi () {//encapsulates the data to be inserted into the Contentvalues object contentvalues values = new Contentvalues ();    Values.put ("Name", "Night Evil Tribe");    Values.put ("Phone", "15999");    Values.put ("Salary", 16000); Db.insert ("person", null, values);} public void Deleteapi () {   int i = db.delete ("person", "name =?")    and _id =? ", New string[]{" Son "," 3 "});    System.out.println (i);//return value I is the number of rows affected}public void Updateapi () {contentvalues values = new Contentvalues ();    Values.put ("Salary", 26000);    int i = db.update ("Person", values, "name =?", New string[]{"Night Evil Tribe"}); System.out.println (i);}    public void Selectapi () {CURSOR cursor = db.query (' person ', NULL, NULL, NULL, NULL, NULL, NULL, NULL);        while (Cursor.movetonext ()) {String name = cursor.getstring (Cursor.getcolumnindex ("name"));        String phone = cursor.getstring (Cursor.getcolumnindex ("Phone"));        String salary = cursor.getstring (Cursor.getcolumnindex ("salary"));    SYSTEM.OUT.PRINTLN (name + ";" + Phone + ";" + salary);        }}public void Transaction () {try{//Open transaction db.begintransaction ();        Contentvalues values = new Contentvalues ();        Values.put ("Salary", 12000);        Db.update ("person", values, "name =?", New string[]{"small"}); Values.clear ();        Values.put ("Salary", 16000);        Db.update ("person", values, "name =?", New string[]{"Son"});    int i = 3/0;//If no downstream code is executed, the SQL statement rolls back//sets the transaction execution success db.settransactionsuccessful ();    } finally{//Close the transaction, commit at the same time, if the transaction execution has been set successfully, then the SQL statement will take effect, and conversely, the SQL statement rollback db.endtransaction (); }}

}

Use API to implement additions and deletions

• Insert

  //以键值对的形式保存要存入数据库的数据ContentValues cv = new ContentValues();cv.put("name", "刘能");cv.put("phone", 1651646);cv.put("money", 3500);//返回值是改行的主键，如果出错返回-1long i = db.insert("person", null, cv);

• Delete

  //返回值是删除的行数int i = db.delete("person", "_id = ? and name = ?", new String[]{"1", "张三"});

• Modify

  ContentValues cv = new ContentValues();cv.put("money", 25000);int i = db.update("person", cv, "name = ?", new String[]{"赵四"});

• Inquire

  //arg1:要查询的字段//arg2:查询条件//arg3:填充查询条件的占位符Cursor cs = db.query("person", new String[]{"name", "money"}, "name = ?", new String[]{"张三"}, null, null, null);while(cs.moveToNext()){    //                          获取指定列的索引值    String name = cs.getString(cs.getColumnIndex("name"));    String money = cs.getString(cs.getColumnIndex("money"));    System.out.println(name + ";" + money);}

Transaction

• Ensure that multiple SQL statements either succeed at the same time or fail at the same time
• Most common case: bank transfer

• Transaction API

  try {    //开启事务    db.beginTransaction();    ...........    //设置事务执行成功    db.setTransactionSuccessful();} finally{    //关闭事务    //如果此时已经设置事务执行成功，则sql语句生效，否则不生效    db.endTransaction();}

Display data from the database to the screen

1. Arbitrarily inserting some data

   • Define Business Bean:Person.java

   • Read all data from a database

     Cursor cs = db.query("person", null, null, null, null, null, null);while(cs.moveToNext()){String name = cs.getString(cs.getColumnIndex("name"));String phone = cs.getString(cs.getColumnIndex("phone"));String money = cs.getString(cs.getColumnIndex("money"));//把读到的数据封装至Person对象Person p = new Person(name, phone, money);//把person对象保存至集合中people.add(p);

   • Display the data in the collection to the screen

      LinearLayout ll = (LinearLayout) findViewById(R.id.ll); for(Person p : people){ //创建TextView，每条数据用一个文本框显示 TextView tv = new TextView(this); tv.setText(p.toString()); //把文本框设置为ll的子节点 ll.addView(tv);

     }

   • Paging Query

     Cursor cs = db.query("person", null, null, null, null, null, null, "0, 10");

Listview

• Is the one that shows the entries for a row.
• MVC structure
  
  • M:model model layer, the data to be displayed ———— people collection
  • V:view view layer, the user sees the interface ———— ListView
  • C:control control layer, manipulating how data is displayed ———— adapter object
• Each entry is a View object

Baseadapter

• Two methods that must be implemented

  • First one

    //系统调用此方法，用来获知模型层有多少条数据@Overridepublic int getCount() {    return people.size();}

  • A second

    //系统调用此方法，获取要显示至ListView的View对象//position:是return的View对象所对应的数据在集合中的位置@Overridepublic View getView(int position, View convertView, ViewGroup parent) {    System.out.println("getView方法调用" + position);    TextView tv = new TextView(MainActivity.this);    //拿到集合中的元素    Person p = people.get(position);    tv.setText(p.toString());    //把TextView的对象返回出去，它会变成ListView的条目    return tv;}

• How many entries can be displayed on the screen, how many times the GetView method will be called, and when the screen is sliding down, GetView will continue to be called, creating more View objects to display to the screen

Cache of Entries

• When the entry is a screen, the system will cache the entry into memory, when the entry again into the screen, the system will re-call GetView when the cache entry as the Convertview parameter, but the incoming entry is not necessarily the previously cached entry, That is, it is possible for the system to pass the cache of any entry when it calls the GetView method to get the first entry

• Mainactivity.java

  public class Mainactivity extends Activity {list<person> personlist;        @Override protected void OnCreate (Bundle savedinstancestate) {super.oncreate (savedinstancestate);        Setcontentview (R.layout.activity_main);        Personlist = new arraylist<person> ();        Query the data of the database Myopenhelper oh = new Myopenhelper (this);        Sqlitedatabase db = Oh.getwritabledatabase ();        cursor cursor = db.query ("person", NULL, NULL, NULL, NULL, NULL, NULL, NULL);            while (Cursor.movetonext ()) {String _id = cursor.getstring (0);            String name = cursor.getstring (1);            String salary = cursor.getstring (2);            String phone = cursor.getstring (3);            Person p = new person (_id, name, phone, salary);        Personlist.add (P);        } linearlayout LL = (linearlayout) Findviewbyid (R.ID.LL); Display the data to screen for (person p:personlist) {//1. Each element in the collection is a new TextView TextView TV = New TextView (this);            2. Set the character's information to the text box content Tv.settext (p.tostring ());            Tv.settextsize (18);        3. Set the TextView as a sub-node of the linear layout ll.addview (TV); }    }}

Use of the SQLite database