SQLite database storage
Write in front
SQLite is a lightweight relational database. Its operation speed is very fast and occupies very few resources. It usually only needs a few hundred K of memory, so it is especially suitable for mobile devices. SQLite not only supports the standard SQL syntax, but also follows the ACID transaction of the database, so as long as you have used other relational databases before, you can get started with SQLite quickly. SQLite is much simpler than ordinary databases. It can be used without even setting a user name and password. Android embeds this extremely powerful database into the system, which makes a qualitative leap in the function of local persistence.
After all, file storage and shared preferences storage are only suitable for saving some simple data and key value pairs. When you need to store a large number of complex relational data, you will find the above two storage methods difficult to cope with. For example, our mobile phone's SMS program may have many sessions, each session contains many pieces of information, and most sessions may correspond to a contact in the phone book. It's hard to imagine how to use files or shared preferences to store these large and structurally complex data? But you can do it with a database. So let's take a quick look at how the SQLite database in Android is used.
1. What you need to know
- Android provides a SQLiteOpenHelper to help us manage the database more conveniently
Helper class, with which you can easily create and upgrade the database. - First of all, you should know that SQLiteOpenHelper is an abstract class, which means that if we want to use it,
You need to create your own help class to inherit it. There are two abstract methods in SQLiteOpenHelper, which are
onCreate() and onUpgrade(), we must override these two methods in our own help class, and then
Methods to implement the logic of creating and upgrading databases. - There are also two very important instance methods in SQLiteOpenHelper, getReadableDatabase() and
getWritableDatabase(). Both methods can create or open an existing database if it already exists
Open directly, otherwise create a new database) and return an object that can read and write to the database. no
Similarly, when the database is not writable (for example, the disk space is full), the pair returned by the getReadableDatabase() method
The image will open the database in a read-only manner, and the getWritableDatabase() method will have an exception.
Here we want to create a file called bookstore DB, and then create a new Book in this database
Table, which contains columns such as id (primary key), author, price, number of pages and book title. Of course, you still need to use to create a database table
Statement, here is also to test your basic SQL skills. The table creation statement of Book table is as follows:
create table Book ( id integer primary key autoincrement, author text, price real, pages integer, name text)
As long as you have a little knowledge of SQL, the above table building statements should not be difficult for you.
Unlike other databases, SQLite has many complex data types. Its data types are very simple. Integer represents integer, real represents floating point, text represents text type and blob represents binary type. In addition, in the above table creation statement, we also use the primary key to set the id column as the primary key, and use the autoincrement keyword to indicate that the id column is self growing.
2. Code demonstration
1. Create SQLiteOpenHelper inheritance classes, such as
package com.example.attendance_demo.DB;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.widget.Toast;
import androidx.annotation.Nullable;
public class MyDatabaseHelper extends SQLiteOpenHelper {
public static final String CREATE_Employees = "create table employee ("
+ "id text primary key, "
+ "name text unique, "
+ "password text, "
+ "department text, "
+ "post text)";
public static final String CREATE_Depart = "create table Depart ("
+ "id text primary key, "
+ "name text)";
public static final String CREATE_JiXiao = "create table Jixiao ("
+ "num integer primary key autoincrement, "
+ "eid text, "
+ "week text, "
+ "month text, "
+ "grade text, "
+ "quanqin text)";
private Context mContext;
public MyDatabaseHelper(Context context, String name, SQLiteDatabase.CursorFactory
factory, int version) {
super(context, name, factory, version);
mContext = context;
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(CREATE_Employees);
db.execSQL(CREATE_Depart);
db.execSQL(CREATE_JiXiao);
Toast.makeText(mContext, "Create succeeded", Toast.LENGTH_SHORT).show();
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
}
2. Call in MainActivity
MyDatabaseHelper dbHelper = new MyDatabaseHelper(this, "Co.db", null, 1); SQLiteDatabase db = dbHelper.getWritableDatabase(); SQLiteDatabase db2 = dbHelper.getReadableDatabase();
3. Write:
ContentValues values = new ContentValues();
values.put("id","1");
values.put("name","Admin");
values.put("password","123456");
values.put("department", (byte[]) null);
values.put("post","2");
db.insert("employee",null,values);
values.clear();
values.put("id","2");
..................................................................
db.insert("employee",null,values);
4. Query and password verification
final String user_str = id.getText().toString();
final String psw_str = pwd.getText().toString();
if (user_str.equals("") || user_str.equals("")) {
Toast.makeText(MainActivity.this, "Password cannot be empty!", Toast.LENGTH_SHORT).show();
}else {
Cursor cursor = db.query("employee", new String[]{"password","name","post"}, "id=?", new String[]{user_str}, null, null, null);
if(cursor.moveToNext()){
String psw_query=cursor.getString(cursor.getColumnIndex("password"));
name_query = cursor.getString(cursor.getColumnIndex("name"));
partSelector = cursor.getString(cursor.getColumnIndex("post"));
if(psw_str.equals(psw_query)){
db.close();
db2.close();
dbHelper.close();
Toast.makeText(MainActivity.this, "Login successful, welcome"+ name_query +"!", Toast.LENGTH_SHORT).show();
}else{
Toast.makeText(MainActivity.this, "Wrong password or user does not exist!", Toast.LENGTH_SHORT).show();
}
5. Write listview method:
MyDatabaseHelper moh = new MyDatabaseHelper(this,"Co.db",null,1);
SQLiteDatabase sd = moh.getReadableDatabase();
ArrayList employeelist = new ArrayList<>();
//Scan the database and put the database information into the student list
Cursor cursor = sd.rawQuery("select * from Jixiao",null);
while (cursor.moveToNext()){
int id = cursor.getInt(cursor.getColumnIndex("num"));
String eid = cursor.getString(cursor.getColumnIndex("eid"));
String grade = cursor.getString(cursor.getColumnIndex("grade"));
String quanqin = cursor.getString(cursor.getColumnIndex("quanqin"));
employeeInfo st = new employeeInfo(id,eid,grade,quanqin); //student_info save data for one entry
employeelist.add(st);//Add each row of the database to the array
}
//Get the ListView and display the studentlist information to the ListView through the Adapter
//Set an adapter for ListView, and getCount() returns the number of data; getView() sets an entry for each row
lv = findViewById(R.id.employee_content_evaluate);
lv.setAdapter(new BaseAdapter() {
@Override
public int getCount() {
return employeelist.size();
}
//Each entry in the ListView is a view object
@Override
public Object getItem(int position) {
return null;
}
@Override
public long getItemId(int position) {
return 0;
}
@Override
public View getView(int position, View convertView, ViewGroup parent) {
View view;
//For the optimization of ListView, create a new view when convertView is empty; When convertView is not empty, it means it is rolled out
//The screen is put into the view in the Recycler. If other layout s are needed, use inflate() for the same view and findViewBy()
if (convertView==null){
view=View.inflate(getBaseContext(),R.layout.listviewadapter,null);
}else {
view = convertView;
}
return view
}
3. Write at the end
Finally, there was nothing