Add, delete, modify, and query SQLite Databases

Add, delete, modify, and query SQLite Databases

I. SQLite database introduction:

SQLite is a lightweight relational database. its official website is http://www.sqlite.org /.

The SQLite database file is stored in the following directory of the mobile device: data> Application name> databases. For example:

Ii. Using the SQLite database, the prime minister needs to understand these two classes: SQLiteOpenHelper SQLiteDatabase.

1: SQLiteOpenHelper introduction:

SQLiteOpenHelper: A helper class to manage database creation and version management. (A help class for managing database creation and database version management ).

 

Constructors ):

SQLiteOpenHelper (Context context, String name, SQLiteDatabase. CursorFactory factory, int version)

Create a helper object to create, open, and/or manage a database.

SQLiteOpenHelper (Context context, String name, SQLiteDatabase. CursorFactory factory, int version, DatabaseErrorHandler errorHandler)

Create a helper object to create, open, and/or manage a database. The first parameter specifies the Context. The parameter name specifies the database name. factory indicates the database operation factory, and version indicates the database version. Use a subclass to inherit the real SQLiteOpenHelper and construct a method. Call this method to directly create the class and specify parameters. Public Methods: 2: SQLiteDatabase: Exposes methods to manage a SQLite database. (open methods to manage SQLite databases), inherited from the parent class: android. database. sqlite. SQLiteClosable can use SQLiteOpenHelper. getSQLiteDatabase obtains instantiation: for example:

SQLiteDatabase dbWrite = db.getWritableDatabase();

SQLiteDatabase detailed introduction can refer to Google method API: http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html

　　

Iii. Case study:

Create a DB class that inherits SQLiteOpenHelper. The factory is set to null and the version is set to 1. The Code is as follows:

(Note: The SQLite database must create a primary key for the table and specify the primary key in the form of "_ id)

1 package activity. cyq. sqlitelearn; 2 3 import android. content. context; 4 import android. database. sqlite. SQLiteDatabase; 5 import android. database. sqlite. SQLiteOpenHelper; 6 7 8 public class DB extends SQLiteOpenHelper {9 public DB (Context context) {10 super (context, "db", null, 1); 11/* db: database Name null: Operation database Factory 1: Database Version Number */12} 13 14 @ Override15 public void onCreate (SQLiteDatabase db) {16 db.exe cSQL ("Create Table user (_ id integer primary key autoincrement, username text default none, password text default none )"); 17} 18 19 @ Override20 public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion) {21 22} 23}

The layout of the Case page is as follows. You can use the add data button to add the user name and password to the database.

The layout XML file is as follows: activity_main.xml itemview. xml

Activity_main.xml is as follows:

1 <LinearLayout xmlns: android = "http://schemas.android.com/apk/res/android" 2 xmlns: tools = "http://schemas.android.com/tools" 3 android: layout_width = "match_parent" 4 android: layout_height = "match_parent" 5 android: orientation = "vertical" 6 android: paddingBottom = "@ dimen/activity_vertical_margin" 7 android: paddingLeft = "@ dimen/activity_horizontal_margin" 8 android: paddingRight = "@ dimen/activity_horizontal_margin" 9 android: paddingTop = "@ dimen/activity_vertical_margin" 10 tools: context = ". mainActivity "> 11 12 <LinearLayout13 android: layout_width =" match_parent "14 android: layout_height =" wrap_content "15 android: orientation =" horizontal "> 16 17 <TextView18 android: layout_width = "wrap_content" 19 android: layout_height = "wrap_content" 20 android: layout_weight = "1" 21 android: text = "username:"/> 22 23 <EditText24 android: id = "@ + id/usernameEdit" 25 android: layout_width = "wrap_content" 26 android: layout_height = "wrap_content" 27 android: layout_weight = "6"/> 28 29 <TextView30 android: layout_width = "wrap_content" 31 android: layout_height = "wrap_content" 32 android: layout_weight = "1" 33 android: text = "Password:"/> 34 35 <EditText36 android: id = "@ + id/passwordEdit" 37 android: layout_width = "wrap_content" 38 android: layout_height = "wrap_content" 39 android: layout_weight = "6"/> 40 41 <Button42 android: id = "@ + id/addDataBtn" 43 android: layout_width = "wrap_content" 44 android: layout_height = "wrap_content" 45 android: layout_weight = "1" 46 android: text = "add data"/> 47 48 </LinearLayout> 49 50 <RelativeLayout51 android: layout_width = "match_parent" 52 android: layout_height = "wrap_content"> 53 54 <LinearLayout55 android: layout_width = "match_parent" 56 android: layout_height = "wrap_content" 57 android: layout_centerInParent = "true"> 58 59 <TextView60 android: layout_width = "match_parent" 61 android: layout_height = "wrap_content" 62 android: layout_centerInParent = "true" 63 android: text = "data presentation" 64 android: textSize = "20dp"/> 65 </LinearLayout> 66 </RelativeLayout> 67 68 <LinearLayout69 android: layout_width = "match_parent" 70 android: layout_height = "wrap_content"> 71 72 <TextView73 android: layout_width = "wrap_content" 74 android: layout_height = "wrap_content" 75 android: layout_weight = "1" 76 android: text = "username" 77 android: textSize = "20dp"/> 78 79 <TextView80 android: layout_width = "wrap_content" 81 android: layout_height = "wrap_content" 82 android: layout_weight = "1" 83 android: text = "password" 84 android: textSize = "20dp"/> 85 </LinearLayout> 86 87 <ListView88 android: id = "@ android: id/list" 89 android: layout_width = "match_parent" 90 android: layout_height = "wrap_content" 91 android: layout_weight = "10" 92 android: background = "@ color/material_blue_grey_800"> </ListView> 93 94 95 </LinearLayout>

Itemview. xml:

 1 <?xml version="1.0" encoding="utf-8"?> 2 <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android" 3     android:layout_width="match_parent" 4     android:layout_height="match_parent" 5     android:orientation="horizontal"> 6  7     <TextView 8         android:id="@+id/usernameItem" 9         android:layout_width="wrap_content"10         android:layout_height="wrap_content"11         android:layout_weight="1"12         android:text="username"13         android:textSize="20dp" />14 15     <TextView16         android:id="@+id/passwordItem"17         android:layout_width="wrap_content"18         android:layout_height="wrap_content"19         android:layout_weight="1"20         android:text="password"21         android:textSize="20dp" />22 23 24 </LinearLayout>

 

The MainActivity code is as follows:

　　

1 package activity. cyq. sqlitelearn; 2 3 4 import android. app. alertDialog; 5 import android. app. listActivity; 6 import android. content. contentValues; 7 import android. content. dialogInterface; 8 import android. database. cursor; 9 import android. database. sqlite. SQLiteDatabase; 10 import android. OS. bundle; 11 import android. view. view; 12 import android. widget. adapterView; 13 import android. widget. button; 14 Import android. widget. editText; 15 import android. widget. simpleCursorAdapter; 16 17 public class MainActivity extends ListActivity implements View. onClickListener, AdapterView. onItemLongClickListener {18 19 private EditText username; 20 private EditText password; 21 private Button addData; 22 private SQLiteDatabase dbWrite, dbRead; 23 private SimpleCursorAdapter adapter; 24 private DB db; 25 26 27 @ Overri De28 protected void onCreate (Bundle savedInstanceState) {29 super. onCreate (savedInstanceState); 30 setContentView (R. layout. activity_main); 31 32 username = (EditText) findViewById (R. id. usernameEdit); 33 password = (EditText) findViewById (R. id. passwordEdit); 34 addData = (Button) findViewById (R. id. addDataBtn); 35 addData. setOnClickListener (this); 36/* create database */37 db = new DB (MainActivity. this); 38 dbWrite = Db. getWritableDatabase (); 39 dbRead = db. getReadableDatabase (); 40 41 adapter = new SimpleCursorAdapter (MainActivity. this, R. layout. itemview, null, new String [] {"username", "password"}, 42 new int [] {R. id. usernameItem, R. id. passwordItem}); 43 setListAdapter (adapter); 44 refresh (); 45 46 getListView (). setOnItemLongClickListener (this); 47 48} 49 50/* refresh ListView data */51 public void refresh () {52 Cursor cursor = DbRead. query ("user", null, null); 53 adapter. changeCursor (cursor); 54} 55 56/* add data Click Event */57 @ Override58 public void onClick (View v) {59 String usernameData = username. getText (). toString (); 60 String passwordData = password. getText (). toString (); 61 ContentValues values = new ContentValues (); 62 values. put ("username", usernameData); 63 values. put ("password", passwordData ); 64 dbWrite. insert ("user", null, values); 65 values. clear (); 66 67 refresh (); 68} 69 70/* Click Event of the edit item button */71 72 73/* handle the long-pressed event of ListViewItem */74 @ Override75 public boolean onItemLongClick (AdapterView <?> Parent, View view, final int position, long id) {76 77 new AlertDialog. builder (MainActivity. this ). setTitle ("warning "). setMessage ("whether to delete this data "). setIcon (R. drawable. delete) 78. setPositiveButton ("yes", new DialogInterface. onClickListener () {79 @ Override80 public void onClick (DialogInterface dialog, int which) {81 Cursor c = adapter. getCursor (); 82 // c. moveToPosition (position );/*???? */83 int item_id = c. getInt (c. getColumnIndex ("_ id"); 84 dbWrite. delete ("user", "_ id =? ", New String [] {item_id +" "}); 85 refresh (); 86} 87 }). setNegativeButton ("no", null) 88. show (); 89 90 91 return true; 92} 93}