Getting started with Android Sqlite instances

A simple example is used to learn Sqlite. In the student course selection system, the first requirement is that the student can select a course. After the course selection is completed, the selected course can be queried.

First, design three tables, student, course, and course selection. The student table stores the student information, the curriculum stores the course information, and the course selection table stores the courses that the student has selected. The table creation statement can be tested using the SQLite Expert software.

Create a new database in Sqlite Expert

Switch to SQL tag

Execute the following statements respectively.

     Students(id   , name        Subjects(id   , name        Subject_Select(id    , student_id   Students(id), subject_id   Subjects(id)， unique_check text unique not null)

Three tables are successfully created, indicating that the SQL statement is correct.

Next, use the code to create a database.

    = = context.openOrCreateDatabase("select.db", Context.MODE_PRIVATE,    version =  currentVersion = 1 (version >= 0        mDatabase.execSQL("VACUUM" "create table if not exists Students(id integer primary key, name text not null)""create table if not exists Subjects(id integer primary key, name text not null)""create table if not exists Subject_Select(id integer primary key, "                + "student_id integer references Students(id), " + "subject_id integer references Subjects(id),"                + "unique_check text unique not null)"

Because the Singleton is used, you need to rewrite the application:

  TestApplication  

Add application in AndroidManifest. xml

The code structure is:

Run the program to create a database in the private directory

Add the code for inserting student and subject to TestSqlite.

      insertStudentInfo( (name ==  -1 (mInsertStudentInfoStatement == = mDatabase.compileStatement("insert or ignore into Students values (?,?)"12  insertSubjectInfo( (name ==  -1 (mInsertSubjectInfoStatement == = mDatabase.compileStatement("insert or ignore into Subjects values (?,?)"12

These two operations do not use execSQL but use SQLiteStatement, which can improve efficiency.

Insert the course selection Code as follows:

      insertSubjectSelectInfo( student_id,  (mInsertSubjectSelectStatement == ="insert or ignore into Subject_Select(student_id, subject_id, unique_check) values (?,?,?)"= student_id + "_" +123

 

The primary key id of the Subject_Select table is automatically generated. student_id and subject_id Add the references constraint, which must be data in the Students and Subjects tables. Unique_check adds the unique constraint. The content is a string assembled by student_id and subject_id to prevent repeated data insertion.

Test in MainActiviy

MainActivity 1, "Xiao Ming" 2, "Xiao Bai" 1, "Mathematics" 2, "Chinese" 3, "English" 4, "physical" 5, "chemistry" 1, 11, 31, 42, 22, 42, 5

Query the name of the selected course by student id:

     List<String> getSelectSubjectNameByStudentId(<String> list =  ArrayList<String>=  String[] { id + ""= mDatabase.rawQuery("select subjects.name from subjects,subject_select " +                "where subject_select.student_id = ? " +                "and subject_select.subject_id = subjects.id"0

 

Note that when splicing SQL statements, you can directly splice the parameters into strings. For example:

Cursor cursor2 = mDatabase.rawQuery("select name from Students where id = " + id, );

Yes.

Cursor cursor2 = mDatabase.rawQuery("select name from Students where id = ?",  String[] { id + "" });

In general, the two methods have the same effect, but the advantage of the second method is that you do not need to consider escape characters. strings like \ % $ &/"can be used directly, the first method should consider escaping, otherwise it cannot be recognized normally.

Code for test query:

        List<String> list = TestSqlite.Instance().getSelectSubjectNameByStudentId(1

So far, a simple database has been designed. After further improvement, the software has been released, and a new requirement has been raised during the development of 2.0, with the course scores required, in this case, a new field score needs to be added to the Subject_Select table. We need to retain the previous data, upgrade the database on the basis of 1.0, and add new functions. This requires database reconstruction.

What we need to do is

1) Rename Subject_Select as Subject_Select_Obsolete

2) create a Subject_Select as needed

3) copy the data of Subject_Select_Obsolete to Subject_Select

4) Delete Subject_Select_Obsolete

The SQL statement is

  Subject_Select rename  Subject_Select_Obsolete

  Subject_Select(id   , student_id   Students(id), subject_id   Subjects(id),unique_check    , score )

  Subject_Select (id,student_id,subject_id,unique_check)  id,student_id,subject_id,unique_check  Subject_Select_Obsolete

  Subject_Select_Obsolete

First, make a judgment in the Code:

       version =  currentVersion = 2 (version >= 0 1        mDatabase.execSQL("VACUUM"

In the above Code, if the database version is 0, it indicates that a database does not exist. directly create a table. If the database version is 1, it indicates that an old version of the database is available and the database must be upgraded. Whether it is an upgrade or a new creation, the database version is set to 2.

The Database Upgrade Code is:

     "alter table Subject_Select rename to Subject_Select_Obsolete""create table Subject_Select(id integer primary key, student_id integer references Students(id), subject_id integer references Subjects(id),unique_check text unique not null, score real)""insert into Subject_Select (id,student_id,subject_id,unique_check) select id,student_id,subject_id,unique_check from Subject_Select_Obsolete""drop table Subject_Select_Obsolete"