SQLite is an open source relational database, to achieve self-contained, zero configuration, transactional SQL database engine. Its features are highly portable, easy to use, compact, efficient and reliable. And SQLite is the most widely deployed SQL database engine in the world. SQLite source code is not subject to copyright restrictions. If this book describes the basic structure of SQLite and several operation modes of SQLite, the most important is the ADB Shell command operation and SQL statements, and in the development or LitePal open-source ORM framework used more, it is really very convenient to use.
<! — more –>
The basic structure of SQLite
The interface consists of the SQLite C API. The basis for the program to interact with SQLite is the API written in C language. JDBC is just a JNI call.
In the compiler, the lexer and parser translate SQL into a syntax tree, and the Code Generator generates the assembly Code of SQLite based on the syntax tree, which is delivered to the virtual machine for execution.
The SQLite assembly code is executed by the SQLite virtual machine. The virtual machine is responsible for the interaction between SQL and data access. For more information about Virtual machines, check out The Virtual Database Engine of SQLite.
For more information about the SQLite Architecture, see the Architecture of SQLite website, which describes more details.
SQLite data type
| type | Type specification |
|---|---|
| NULL | This value is null |
| VARCHAR(n) | For strings of variable length whose maximum length is n, n cannot exceed 4000 |
| CHAR(n) | String of fixed length n, n cannot exceed 254 |
| INTEGER | Values are identified as integers and can be stored as 1,2,3,4,5,6,7,8 depending on their size |
| REAL | All values are floating numeric values, stored as 8-byte IEEE floating tag Ordinal Numbers |
| TEXT | Values are text strings, stored using the database encoding (tutf-8, UTF-16BE or UTF-16-LE) |
| BLOB | The value is a BLOB data block, stored in the input data format. How to input how to store, do not change the format |
| DATA | Contains the year, month, and date |
| TIME | Includes hours, minutes, seconds |
Operate SQLite in Android
The SQLite Expert Personal 4 tool can be used for visualizing SQLite on Windows. You can download it from the following address: http://www.sqliteexpert.com/v4/SQLiteExpertPersSetup64.exe. SQLite can be operated through a graphical interface, and can also be operated through SQL statements:
CREATE TABLE STU_INFO (ID ID PRIMARY KEY AUTOINCREMENT, NAME VARCHAR (30) NOT NULL, AGE UNSIGNED, TABLE STU_INFO (ID PRIMARY KEY AUTOINCREMENT, NAME VARCHAR (30) NOT NULL, AGE UNSIGNED, Gender varchar(2) not null) # insert into stu_info(name, age, gender) values ('Mike', 24, 'female '); Insert into stu_info(name, age, gender) values ('Jone', 26, 'male '); Insert into stu_info(name, age, gender) values ('Tom', 28, 'female '); # select * from stu_info; Delete from stu_info where id = 13; Update stu_info set name = 'Jack' where id = 10; update stu_info set name = 'Jack' where id = 10; Select * from stu_info where age = 24;Now let’s focus on how to use it on Android. SQLiteOpenHelper: An Android database helper class for creating and opening databases, and managing database creation and versioning.
SQLiteDatabase: For managing and manipulating SQLite databases, almost all database operations will eventually be done by this class.
The ADB Shell operates SQLite
Open the CMD window, type the ADB shell, find the SQLite file, and use sqlite3 + SQLite file name to enter the SQLite shell:
| The command | role |
|---|---|
| .database | Display database information |
| .tables | Display table name |
| .schema | Command to view the SQL command when creating the data table |
| .schema table_name | SELECT * FROM table_name WHERE table_name = table_name |
SQLiteDatabase + SQL statement
SQL SQL SQL SQL SQL SQL SQL SQL SQL SQL SQL SQL SQL SQL SQL SQL SQL SQL SQL SQL SQL SQL If it is inserted into the data, modify data, deleting data is done with sqLiteDatabase. ExecSQL (insertSQL/updateSQL/deleteSQL) to complete, and query data using sqLiteDatabase. RawQuery () method to finish, It’s a bit more cumbersome because you need to concatenate SQL to use.
main_activity.xml
<? The XML version = "1.0" encoding = "utf-8"? > <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android" xmlns:tools="http://schemas.android.com/tools" android:layout_width="match_parent" android:layout_height="match_parent" android:orientation="vertical" android:padding="20dp" tools:context=".MainActivity"> <EditText android:id="@+id/et_name" Android :hint=" name "android:layout_width="match_parent" android:layout_height="wrap_content"/> <EditText Android :id="@+id/et_age" android:hint=" age" android:inputType="number" android:layout_width="match_parent" android:id="@+id/et_age" android:hint=" age" android:inputType="number" android:layout_width="match_parent" android:layout_height="wrap_content"/> <RadioGroup android:id="@+id/rg_sex" android:orientation="horizontal" android:layout_width="match_parent" android:layout_height="wrap_content"> <RadioButton android:id="@+id/rb_male" Android :layout_width="wrap_content" android:layout_height="wrap_content"/> <RadioButton Android :id="@+id/rb_female" android:text=" female" android:layout_width="wrap_content" android:layout_height="wrap_content"/> </RadioGroup> <LinearLayout android:orientation="horizontal" android:layout_width="match_parent" Android :layout_height="wrap_content"> <Button android:id="@+id/btn_add" android:text=" add" android:onClick=" OperatorData" android:layout_width="wrap_content" android:layout_height="wrap_content"/> <Button android:id="@+id/btn_update" Android :onClick=" OperatorData "android:layout_width="wrap_content" android:layout_height="wrap_content"/> <Button android:id="@+id/btn_delete" android:onClick="operatorData" Android :layout_width="wrap_content" android:layout_height="wrap_content"/> </LinearLayout> <EditText Android :id="@+id/et_id" android:hint=" change/delete id" android:inputType="number" android:layout_width="match_parent" android:layout_width="match_parent" android:layout_height="wrap_content"/> <ListView android:id="@+id/lv_data" android:layout_width="match_parent" android:layout_height="wrap_content"/> </LinearLayout>MainActivity.java
public class MainActivity extends AppCompatActivity { private static final String TAG = "MainActivity"; private EditText etId; private EditText etName; private EditText etAge; private String sex; private SQLiteDatabase sqLiteDatabase; private ListView lvData; Private static final int REQUEST_EXTERNAL_STORAGE = 1001; // check permissions, Public static void VerifyStoragePermissions (Activity Activity) {int writePermission = ActivityCompat.checkSelfPermission(activity, Manifest.permission.WRITE_EXTERNAL_STORAGE); int readPermission = ActivityCompat.checkSelfPermission(activity, Manifest.permission.READ_EXTERNAL_STORAGE); if (writePermission ! = PackageManager.PERMISSION_GRANTED || readPermission ! = PackageManager. PERMISSION_GRANTED) {/ / if no permission need to apply for permission to ActivityCompat dynamically. RequestPermissions (activity, / / permission to an array of new String [] {the Manifest. Permission. READ_EXTERNAL_STORAGE, Manifest. Permission. WRITE_EXTERNAL_STORAGE}, // request code REQUEST_EXTERNAL_STORAGE); } } @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); etId = findViewById(R.id.et_id); etName = findViewById(R.id.et_name); etAge = findViewById(R.id.et_age); // RadioGroup rgSex = findViewById(R.id.rg_sex); lvData = findViewById(R.id.lv_data); verifyStoragePermissions(this); /* * Construct parameters: * 1, context * 2, database name, The default location of the application of private directory (internal storage database folder) * 4 * 3, CursorFactory type, the database version * / String path = Environment. External.getexternalstoragedirectory () + "/sqlite_demo.db"; SQLiteOpenHelper helper = new SQLiteOpenHelper(this, path, null, 1){Override public void onCreate(SQLiteDatabase DB){Toast.maketext (MainActivity. This, "create database ", Toast.LENGTH_SHORT).show(); // If you don't have a database in place, Db.execSQL (" CREATE TABLE STU_INFO (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME VARCHAR (30) NOT NULL, TABLE STU_INFO (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME VARCHAR (30) NOT NULL, TABLE STU_INFO (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME VARCHAR (30) NOT NULL, age integer,gender varchar(2) not null)"); } @Override public void onUpgrade(SQLiteDatabase DB, int oldVersion, int oldVersion) Int newVersion) {toast.maketext (mainactivity.this, "database update ", toast.length_short).show(); }}; / / access to database objects sqLiteDatabase = helper. GetWritableDatabase (); / / set of radio buttons to monitor rgSex. SetOnCheckedChangeListener (new RadioGroup. OnCheckedChangeListener () {@ Override public void OnCheckedChanged (RadioGroup group, int checkeId){switch (checkeId){case R.id.rb_female: sex = "female "; break; Case R.id.rb_male: sex = "male "; break; }}}); flushStuData(); } private void flushStuData() { List<StudentInfo> stuList = new ArrayList<>(); String selectSQL = "select * from stu_info"; Cursor cursor = sqLiteDatabase.rawQuery(selectSQL, new String[]{}); cursor.moveToFirst(); while (! cursor.isAfterLast()){ int id = cursor.getInt(0); String name = cursor.getString(1); int age = cursor.getInt(2); String sex = cursor.getString(3); stuList.add(new StudentInfo(id, name, age, sex)); cursor.moveToNext(); } cursor.close(); lvData.setAdapter(new StuInfoAdapter(this, stuList)); } public void operatorData(View view) { int viewId = view.getId(); switch (viewId) { case R.id.btn_add: If (textutils.isEmpty (sex)) {toast.maketext (mainactivity.this, "Please select gender ", toast.length_short).show(); return; } String insertSQL = String.format(Locale.CHINA,"insert into stu_info(name, age, gender) values ('%s', %d, '%s')", etName.getText().toString(), Integer.parseInt(etAge.getText().toString()), sex); Log.i(TAG, "operatorData: insertSQL = " + insertSQL); sqLiteDatabase.execSQL(insertSQL); FlushStudata (); // FlushStudata (); Toast.maketext (MainActivity. This, "Add Successfully ", Toast.length_short).show(); break; case R.id.btn_update: String idStr = etId.getText().toString(); If (TexTutils.IsEmpty (idStr)){Toast.maketext (MainActivity. This, "Please enter ID", Toast.length_short).show(); return; } int id = Integer.parseInt(idStr); String updateSQL = String.format(Locale.CHINA, "update stu_info set name = '%s', age=%d, gender='%s' where id = %d", etName.getText().toString(), Integer.parseInt(etAge.getText().toString()), sex, id); Log.i(TAG, "operatorData: updateSQL = " + updateSQL); sqLiteDatabase.execSQL(updateSQL); FlushStudata (); // FlushStudata (); Toast.maketext (MainActivity. This, "Updated successfully ", Toast.length_short).show(); break; case R.id.btn_delete: String deleteIdStr = etId.getText().toString(); If (textutils.isEmpty (deleteIdStr)){toast.maketext (mainactivity. this, "Please enter ID", toast.length_short).show(); return; } String deleteSQL = String.format(Locale.CHINA, "delete from stu_info where id = %d", Integer.parseInt(deleteIdStr)); Log.i(TAG, "operatorData: deleteSQL = " + deleteSQL); sqLiteDatabase.execSQL(deleteSQL); FlushStudata (); // FlushStudata (); Toast.maketext (mainactivity.this, "delete succeeded ", toast.length_short).show(); break; } } } class StudentInfo { public int id; public String name; public int age; public String sex; public StudentInfo() { } public StudentInfo(int id, String name, int age, String sex) { this.id = id; this.name = name; this.age = age; this.sex = sex; }}StuInfoAdapter.java
public class StuInfoAdapter extends BaseAdapter { private List<StudentInfo> stuList; private Activity context; public StuInfoAdapter(Activity context, List<StudentInfo> stuList) { this.stuList = stuList; this.context = context; } @Override public int getCount() { return stuList.size(); } @Override public Object getItem(int position) { return stuList.get(position); } @Override public long getItemId(int position) { return position; } @Override public View getView(int position, View convertView, ViewGroup parent) { ViewHolder viewHolder; if(convertView == null){ LayoutInflater inflater = context.getLayoutInflater(); convertView = inflater.inflate(R.layout.stu_item, null); viewHolder = new ViewHolder(); viewHolder.tvName = convertView.findViewById(R.id.tv_item_name); viewHolder.tvId = convertView.findViewById(R.id.tv_item_id); viewHolder.tvAge = convertView.findViewById(R.id.tv_item_age); viewHolder.tvSex = convertView.findViewById(R.id.tv_item_sex); convertView.setTag(viewHolder); }else { viewHolder = (ViewHolder) convertView.getTag(); } StudentInfo studentInfo = stuList.get(position); viewHolder.tvId.setText(String.valueOf(studentInfo.id)); viewHolder.tvName.setText(studentInfo.name); viewHolder.tvSex.setText(studentInfo.sex); viewHolder.tvAge.setText(String.valueOf(studentInfo.age)); return convertView; } // ViewHolder static class ViewHolder{ TextView tvId; TextView tvName; TextView tvSex; TextView tvAge; }}It’s best not to forget to declare read and write permissions (as well as dynamic permission requests since Android6.0), so the final result looks like this:
Static methods used in Android SQLiteDatabase openOrCreateDatabase (String path, SQLiteDatabae. CursorFactory factory) open or create a database. It will automatically detect the existence of the database, if it exists, then open, does not exist to create a database; An SQLiteDatabase object is returned on success, otherwise a FileNotFoundException is thrown.
It is also important to note that querying data returns a Cursor. When we use the sqliteDatabase.query () method, we get a Cursor object that points to each of the data. It provides a number of query methods, as follows:
| Method names | Methods described |
|---|---|
| getCount() | Obtain the total number of data items |
| isFirst() | Determine if it is the first record |
| isLast() | Determine if it is the last record |
| moveToFirst() | Move to the first record |
| moveToLast() | Move to the last record |
| move(int offset) | Moves to the specified record |
| moveToNext() | Move to the next record |
| moveToPrevious() | Move to the previous record |
| getColumnIndexOrThrow(String columnName) | Get the column index based on the column name |
| getInt(int columnIndex) | Gets the value of type int for the specified column index |
| getString(int columnIndex) | Gets the String value of the specified column miniature |
Refer to the use of the flushStudata () method in the code example:
private void flushStuData() { List<StudentInfo> stuList = new ArrayList<>(); // Parameter explanation: QUERY (" STU_INFO ", NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, null, null); if(cursor.moveToFirst()){ do{ int id = cursor.getInt(0); String name = cursor.getString(1); int age = cursor.getInt(2); String sex = cursor.getString(3); stuList.add(new StudentInfo(id, name, age, sex)); } while (cursor.moveToNext()); } cursor.close(); lvData.setAdapter(new StuInfoAdapter(this, stuList)); }The database is manipulated through an API
SQLiteDatabase also provides insert(), delete(), update(), query() methods for inserting, deleting, updating, and querying. This API method requires you to write an SQL statement, and you just need to pass in the corresponding parameters to complete the CRUD operation. Again, from the above example, the only changes are in the data:
/ /... Private void flushStudata () {List< studentInfo > stuList = new ArrayList<>(); // Parameter explanation: QUERY (" STU_INFO ", NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, null, null); if(cursor.moveToFirst()){ do{ int id = cursor.getInt(0); String name = cursor.getString(1); int age = cursor.getInt(2); String sex = cursor.getString(3); stuList.add(new StudentInfo(id, name, age, sex)); } while (cursor.moveToNext()); } cursor.close(); lvData.setAdapter(new StuInfoAdapter(this, stuList)); } public void operatorData(View view) { int viewId = view.getId(); Switch (viewID) {// Add case R.id.btn_add: // ContentValues Values = new ContentValues(); // Key - value values.put("name", etName.getText().toString()); values.put("age", Integer.parseInt(etAge.getText().toString())); values.put("gender", sex); Long InfoID = sqliteDatabase. insert("stu_info", null, values); FlushStudata (); // FlushStudata (); Toast.maketext (mainactivity.this, "add successfully, Id =" + infoId, toast.length_short).show(); break; // update case R.id.btn_update: String idStr = etid.gettext ().toString(); ContentValues updateValues = new ContentValues(); // Key - value updateValues.put("name", etName.getText().toString()); updateValues.put("age", Integer.parseInt(etAge.getText().toString())); updateValues.put("gender", sex); int info = sqLiteDatabase.update("stu_info", updateValues, "id=?" , new String[]{idStr}); FlushStudata (); // FlushStudata (); Toast.maketext (mainactivity.this, "updated successfully, affects the number of lines:" + info, toast.length_short).show(); break; // delete case R.id.btn_delete: String deleteIdStr = etid.gettext ().toString(); int delete = sqLiteDatabase.delete("stu_info", "id=?", new String[]{deleteIdStr}); FlushStudata (); // FlushStudata (); Toast.maketext (mainactivity.this, "delete succeeded, affect the number of lines:" + delete, toast.length_short).show(); break; }} / /...
Use LitePal to manipulate SQLite
SQLiteDatabase (SQLite) is not easy to use. For someone like me who is used to using the ORM framework, SQLiteDatabase is too complicated to use. So let’s take a look at the open source framework LitePal. Used up after himself to try to build a wheel, you can refer to: https://github.com/huyongli/T…
Environment set up
First introduce dependencies:
Implementation 'org. Litepal. Android: core: 1.4.1'Next you need to configure the litepal.xml file. Right-click app/ SRC /main — >New — >Directory to create an assets Directory. Inside assets, create a New file named litepal.xml:
<? The XML version = "1.0" encoding = "utf-8"? > <litepal> <! --> <dbname value="BookStore"/> <! > <version value="1"/> <! >< list></list> </litepal>Finally, we need to modify the code in AndroidManifest.xml:
<! -- This is the key sentence: android:name="org.litepal.LitePalApplication" --> <application android:name="org.litepal.LitePalApplication" android:allowBackup="true" android:icon="@mipmap/ic_launcher" android:label="@string/app_name" android:roundIcon="@mipmap/ic_launcher_round" android:supportsRtl="true" android:theme="@style/AppTheme"> <activity android:name=".MainActivity"> <intent-filter> <action android:name="android.intent.action.MAIN" /> <category android:name="android.intent.category.LAUNCHER" /> </intent-filter> </activity> </application>Now that the LitePal is configured, let’s start using it!
To build libraries built table
Now let’s declare a JavaBean, which is the data we want to store:
public class Book {
private int id;
private String name;
private String author;
public Book(){ }
public Book(int id, String author, String name) {
this.id = id;
this.author = author;
this.name = name;
}
// Getter and Setter ...
}And configures its mapping model in the configuration file:
<? The XML version = "1.0" encoding = "utf-8"? > <litepal> <! --> <dbname value="BookStore"/> <! > <version value="1"/> <! > <list> <mapping class="cn.tim.litepal_demo.Book"/> </list> </litepal>Create a database when the Activity starts:
SQLiteDatabase = litepal.getDatabase (); SQLiteDatabase = litepal.getDatabase ();
Even though there are three tables, Android_MateData table is still ignored. Table_Schema table is used internally by LitePal, and can also be ignored directly. Book table is automatically generated according to the configured Book class, is it convenient?
Database upgrade
In addition, LitePal is a good solution to the problem of database upgrade. SQLiteOpenHelper is used to upgrade the database. We need to drop the previous table and then create it again. This is actually a very serious problem because it can cause data loss. Every time you upgrade the database, all the data in the previous table is lost. Although this can be avoided with complex logical controls, it is costly to maintain. With Litepal, this is no longer an issue. Updating your database with Litepal is very, very easy. You don’t have to think about any logic at all.
For example, add another price field to the book table, and create a new category table:
public class Book {
private int id;
private String name;
private String author;
private int price;
// ...
}
public class Category {
private int id;
private String name;
private long count;
// ...
}You also need to configure the mapping model:
<? The XML version = "1.0" encoding = "utf-8"? > <litepal> <! --> <dbname value="BookStore"/> <! --> <version value="2"/> <! > < List > < Mapping class=" CN.Tim.LitePal_Demo.Book"/> < Mapping class=" CN.Tim.LitePal_Demo.Category"/> </ List > </litepal>
As you can see, a new Price column has been added to the Book table, and a new Category table has been created.
CRUD operations
Let’s see how convenient it is to use LitePal to CRUD data:
First you need to have the data model object, the defined JavaBeans, inherit the DataUpport
public class Book extends DataSupport {
...
}Let’s verify the CRUD of LitePal framework directly by looking at the log log:
public class MainActivity extends AppCompatActivity { private static final String TAG = "MainActivity"; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); SQLiteDatabase = litepal.getDatabase (); SQLiteDatabase = litepal.getDatabase (); // add data Book Book = new Book("Think In Java", "Tim", 58); boolean saveRet = book.save(); Log.i(TAG, "onCreate: saveRet = " + saveRet); new Book("Think In C/C++", "Tom", 38).save(); Log. I (TAG, "onCreate: add data successfully "); List<Book> bookList = datasupport.findAll (book.class); Book[] books = new Book[bookList.size()]; bookList.toArray(books); Log.i(TAG, "onCreate: books = " + Arrays.toString(books)); Int delete = dataUpport. delete(book.class, books[0].getId()); Log. I (TAG, "onCreate: delete data successfully, delete = "+ delete); // BookList = DataUpport.findAll (book.class); books = new Book[bookList.size()]; bookList.toArray(books); Log.i(TAG, "onCreate: books = " + Arrays.toString(books)); Book cppBook = new Book("Think In C/C++", "Tom", 28); int update = cppBook.update(2); Log. I (TAG, "onCreate: changed data successfully, update = "+ update); // BookList = DataUpport.findAll (book.class); books = new Book[bookList.size()]; bookList.toArray(books); Log.i(TAG, "onCreate: books = " + Arrays.toString(books)); }}
The above demonstrates the specific operation, in fact, there are a lot of advanced query methods, here will not be repeated, if you can refer to the author’s blog “Android database master secret”.
Blog: https://zouchanglin.cn/2020/1…