Introduction to sqlite
sqlite is a light embedded database, which is mostly used for mobile phones, car machines, etc. as for its principle, Baidu has many bosses to introduce him in detail, so I won't elaborate here.
Today, I mainly want to record the detailed use of sqlite. I mainly understand the addition, deletion, modification and query of sqlite through an example.
As shown in the figure, this is a simple user login registration. It mainly records the login registration of two kinds of users
sqlite is used to inherit the SQliteOpenHelper database class. Generally, the written database will not directly operate the data, which is the protection of data security. It can generally be regarded as a tool class, privatizing the constructor and adding an external access interface.
/**
* Database tool class (singleton mode)
*/
public class Stu_Tea_Sqlite extends SQLiteOpenHelper{
private static final String STU_TEA_COURSE ="stc.db";//database
private static final String TEACHER ="teacher"; // Teacher table
private static final String STUDENT ="student"; //Student list
private static final String COURSE="course"; // Class Schedule Card
private static final String STU_COURSE ="stu_course"; //Student course (student course selection: student, course number, course name, teacher, score / /)
private static final String TEA_COURSE_WORK ="tea_work"; //Assignment (course No., course No., assignment No., assignment name, assignment content)
private static final String STU_COURSE_WORK ="stu_work"; //Assignment (course number, assignment number, student, score / /)
private static SQLiteOpenHelper instance;
//External interface
public static synchronized SQLiteOpenHelper getInstance(Context context){
if(instance ==null){
instance =new Stu_Tea_Sqlite(context,STU_TEA_COURSE,null,2);
}
return instance;
}
//Privatized constructor
private Stu_Tea_Sqlite(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version) {
super(context, name, factory, version);
}
//Generally, it is executed only once, and the database is created for the first time
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
/**
* Teacher form: (employee No. _Tid, name T_name, password T_pass, email T_email, phone T_tel, gender T_sex, major t_professional)
* Standard writing_ id
* Primary key must be Integer
*/
sqLiteDatabase.execSQL("create table if not exists "+ TEACHER +"(_id Integer primary key autoincrement," +"_Tid varchar(7) not null,"+"T_name varchar(20) not null," +
"T_pass varchar(20) not null," + "T_email varchar(30) not null," + "T_tel varchar(15) not null,"+ "T_sex varchar(2) not null," +
"T_profession varchar(30) )");
/**
* Student form: (Student ID _Sid, name S_name, password S_pass, email S_email, phone S_tel, gender S_sex, major s_professional)
*/
sqLiteDatabase.execSQL("create table if not exists "+ STUDENT +"(_id Integer primary key autoincrement,"+"_Sid varchar(7) not null ," +"S_name varchar(20) not null," +
"S_pass varchar(20) not null," + "S_email varchar(30) not null," + "S_tel varchar(15) not null,"+ "S_sex varchar(2) not null," +
"S_profession varchar(30) )");
Then create the table in your oncreate function. The function sqLiteDatabase.execSQL() is called to create the table. The SQL syntax of creating the table is the same as our usual syntax.
It is important to note that for the creation of sqlite data table, its primary key must be of type iteger, and its standard writing method is to start with an underscore. There are no strict requirements for the following columns. The data type can be specific or replaced by TEXT, because it will be stored in the form of TEXT
public interface Manager_Date {
public void createDB(Context context);
public void StudentInsert(Student student, Context context);
public Student StudentQuery(String ID,Context context);
public void StudentUpdate(Student student,Context context);
public void TeacherAdd(Teacher teacher,Context context);
public Teacher TeacherQuery(String ID,Context context);
public void TeacherUpdate(Teacher teacher,Context context);
}
I have written an interface here, which is mainly to facilitate the call of data for my later functions. The interface will be called directly for the addition, deletion, modification and query of later data
A database can be created by calling public void createDB(Context context). Generally, it is called only when it is created for the first time
public interface Manager_Date {
public void createDB(Context context);//Database creation
public void StudentInsert(Student student, Context context);
public Student StudentQuery(String ID,Context context);
public void StudentUpdate(Student student,Context context);
public void TeacherAdd(Teacher teacher,Context context);
public Teacher TeacherQuery(String ID,Context context);
public void TeacherUpdate(Teacher teacher,Context context);
I wrote a database management class to realize the interface of database operation
public class Manager_SQL implements Manager_Date{
/**
* //Initializing the database will only be executed once
*/
@Override
public void createDB(Context context){
SQLiteOpenHelper openHelper=Stu_Tea_Sqlite.getInstance(context);
SQLiteDatabase database=openHelper.getWritableDatabase();
}
After calling the method, a database will be created, as shown in the figure:
For general database viewing, the database file. db you just created can be found under data / data / app (your project name) / databases in the Device File Explorer next to Android studio
(if you don't find that there is no Device File Explorer in your Android, you can baidu and how to open the view.)
Generally, if you want to see the specific data of the database, you can pull it out and download a special sqlite database viewing software. If you are as lazy as me to download and install, you can also view it in the database inspacer under Android studio, as shown in the figure above. However, this can only be viewed after starting the simulator and opening your sequencing.
OK, after the database table is created, you can add, delete, modify and query the database table.
/**
* Student data insertion
* Student form: (Student ID _Sid, name S_name, password S_pass, email S_email, phone S_tel, gender S_sex, major s_professional)
*/
@Override
public void StudentInsert(Student student,Context context) {
SQLiteOpenHelper openHelper=Stu_Tea_Sqlite.getInstance(context);
SQLiteDatabase database=openHelper.getWritableDatabase();
if(database.isOpen()){
ContentValues values=new ContentValues();
values.put("_Sid",student.getId());
values.put("S_name",student.getName());
values.put("S_pass",student.getPassword());
values.put("S_email",student.getEmail());
values.put("S_sex",student.getSex());
values.put("S_tel",student.getTelephone());
values.put("S_profession",student.getProfession());
Log.d("Insert:",student.getEmail());
Log.d("Insert:",student.getPassword());
Log.d("Insert:",student.getSex());
Log.d("Insert:",student.getProfession());
Long count=database.insert("student",null,values);
if(count!=-1){
Log.d("stu","Insert database succeeded"+count);
}
else {
Log.d("stu","Insert database failed"+count);
}
}
database.close();
}
/**
* query
* @param ID
* @param context
* @return
*/
@Override
public Student StudentQuery(String ID,Context context){
SQLiteOpenHelper openHelper=Stu_Tea_Sqlite.getInstance(context);
SQLiteDatabase database=openHelper.getReadableDatabase();
Student student=new Student();
if(database.isOpen()){
Cursor cursor=database.query("student",null,"_Sid like ?",new String[]{ID},null,null,null);
if(cursor.getCount()>0){
while (cursor.moveToNext()){
String id=cursor.getString(cursor.getColumnIndex("_Sid"));
String emailString=cursor.getString(cursor.getColumnIndex("S_email"));
String nameString = cursor.getString(cursor.getColumnIndex("S_name"));
String password = cursor.getString(cursor.getColumnIndex("S_pass"));
String sex=cursor.getString(cursor.getColumnIndex("S_sex"));
String profession=cursor.getString(cursor.getColumnIndex("S_profession"));
String tel=cursor.getString(cursor.getColumnIndex("S_tel"));
student.setId(id);
student.setEmail(emailString);
student.setName(nameString);
student.setPassword(password);
student.setTelephone(tel);
student.setSex(sex);
student.setProfession(profession);
}
}
else {
Log.d("cursor","Data does not exist");
}
cursor.close();
}
database.close();
return student;
}
/**
* Modify information
*/
@Override
public void StudentUpdate(Student student,Context context){
SQLiteOpenHelper openHelper=Stu_Tea_Sqlite.getInstance(context);
SQLiteDatabase database=openHelper.getWritableDatabase();
if(database.isOpen()){
ContentValues values=new ContentValues();
values.put("S_name", student.getName());
values.put("S_email", student.getEmail());
values.put("S_sex", student.getSex());
values.put("S_tel", student.getTelephone());
values.put("profession", student.getProfession());
database.update("student",values,"_Sid=?",new String[]{student.getId()});//Parameters: table name, package, condition and condition value
}
database.close();
}
The deletion of data is the same operation. Call database.delete() to delete the specified data. It is worth noting that the query operation of the database is to bind the data to the Cursor cursor. After moving the Cursor to query the data, remember to close the Cursor and the database, otherwise it will consume performance.
Write an article for the first time to record the summarized knowledge points, deficiencies, or better writing methods and optimization. Welcome to correct.