Learning Sqlite of Android Together

Keywords: Android Database SQL SQLite

Summary

Android provides completely friendly support for Sqlite, and within the application, any database can be accessed by name. It is recommended to create data tables through subclasses of SQLiteOpenHelpe and override the onCreate() method. This article mainly explains the simple application of Sqlite in Andriod development (add, delete and check), which is only for learning and sharing.

Knowledge Points

  1. SQLiteOpenHelper manages help classes (abstract classes) for creating databases and version management.
  2. Method of creating data table by onCreate
  3. ExcSQL executes a non-Select statement without returning information.
  4. SQLiteDatabase manages Sqlite data objects and displays provide a set of methods to manage the database (CRUD).
  5. SimpleCursor Adapter is a simple adapter for adapting Cursor data to controls.
  6. ContentValues store a set of data values (key-value s).

Example rendering

As shown in the following figure:

Database Help Class

The code is as follows:

 1 package com.hex.demosqlite;
 2 
 3 import android.content.Context;
 4 import android.database.sqlite.SQLiteDatabase;
 5 import android.database.sqlite.SQLiteOpenHelper;
 6 
 7 /**
 8  * Created by Administrator on 2019/4/4.
 9  */
10 public class DatabaseHelper extends SQLiteOpenHelper {
11 
12     private static final String DB_NAME="persons.db";
13 
14     private static final int DB_VERSION=1;
15 
16     public DatabaseHelper(Context context) {
17         super(context, DB_NAME, null, DB_VERSION);
18     }
19 
20     /**
21      * Create database, specify database name, version number
22      * @param db
23      */
24     @Override
25     public void onCreate(SQLiteDatabase db) {
26         String sql="create table person(_id integer primary key autoincrement not null, name char(10),nickname char(10))";
27         db.execSQL(sql);
28     }
29 
30     /**
31      * Database upgrade
32      * @param db
33      * @param oldVersion
34      * @param newVersion
35      */
36     @Override
37     public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
38         if(newVersion>oldVersion){
39             onCreate(db);
40         }
41     }
42 }

Insert

The code is as follows:

 1  /**
 2      * Newly added
 3      *
 4      * @param v
 5      */
 6     public void add(View v) {
 7         /** //Normally this method is not used because the execSQL method does not return a value
 8          String sql="insert into person(name,nickname)values('Songjiang','timely rain';
 9          db.execSQL(sql);
10          */
11         ContentValues values = new ContentValues();
12         values.put("name", "Song Jiang");
13         values.put("nickname", "Timely rain");
14         //The return value is the line number of the inserted line
15         long result = db.insert("person", null, values);
16         if (result > 0) {
17             Toast.makeText(this, "Insert success", Toast.LENGTH_SHORT).show();
18         } else {
19             Toast.makeText(this, "Insert failure", Toast.LENGTH_SHORT).show();
20         }
21     }

Query

The code is as follows:

 1   /**
 2      * query
 3      *
 4      * @param v
 5      */
 6     public void query(View v) {
 7         /*
 8         String sql="select * from person where name=?";
 9         Cursor cursor = db.rawQuery(sql,new String[]{"Song Jiang (});
10         */
11         //columns If null, all columns are represented
12         Cursor cursor = db.query("person", null, "name=?", new String[]{"Song Jiang"}, null, null, null);
13         while (cursor.moveToNext()) {
14             int nameIndex = cursor.getColumnIndex("name");
15             int nickIndex = cursor.getColumnIndex("nickname");
16             String nameValue = cursor.getString(nameIndex);
17             String nickValue = cursor.getString(nickIndex);
18             String s = "name=" + nameValue + ",nickname=" + nickValue;
19             Toast.makeText(this, s, Toast.LENGTH_SHORT).show();
20         }
21         adaper.swapCursor(cursor);
22         adaper.notifyDataSetChanged();
23     }

Update

The code is as follows:

 1     /**
 2      * To update
 3      *
 4      * @param v
 5      */
 6     public void update(View v) {
 7         // String sql="update person set nickname=? where name=?";
 8 
 9         ContentValues values = new ContentValues();
10         values.put("nickname", "Timely rain 2");
11         //Number of rows affected by UPDATE statement
12         int result = db.update("person", values, "name=?", new String[]{"Song Jiang"});
13         if (result > 0) {
14             Toast.makeText(this, "Update success", Toast.LENGTH_SHORT).show();
15         } else {
16             Toast.makeText(this, "Update failed", Toast.LENGTH_SHORT).show();
17         }
18     }

Delete

The code is as follows:

 1   /**
 2      * delete
 3      *
 4      * @param v
 5      */
 6     public void delete(View v) {
 7         //String sql="delete person where name=?";
 8 
 9         //Number of rows affected by deleted statements
10         int result = db.delete("person", "name=?", new String[]{"Song Jiang"});
11         if (result > 0) {
12             Toast.makeText(this, "Delete successful", Toast.LENGTH_SHORT).show();
13         } else {
14             Toast.makeText(this, "No deletion was successful", Toast.LENGTH_SHORT).show();
15         }
16     }

Remarks

A journey of a thousand miles begins with a single step!

Posted by duane on Fri, 05 Apr 2019 22:12:30 -0700