through a simple example to learn Sqlite, student elective system, a demand that students can begin elective, elective has been completed can query to select the class.
designed first three tables, students, curriculum, course. Student table stores information about students, curriculum, curriculum information storage, table storage elective courses the student has chosen. Construction of the table can be SQLite Expert statement to test the software.
in Sqlite Expert to create a new database
switch to the SQL tab
to execute the following statement
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 unique, student_id integer references Students(id), subject_id integer references Subjects(id)， unique_check text unique not null)
successfully created three tables, SQL statements without error
then use the code to create the database
public class TestSqlite {
private static TestSqlite mInstance;
public static TestSqlite Instance() {
return TestSqlite.mInstance;
}
private final SQLiteDatabase mDatabase;
protected TestSqlite(Context context) {
TestSqlite.mInstance = this;
mDatabase = context.openOrCreateDatabase("select.db", Context.MODE_PRIVATE, null);
migrate();
}
private void migrate() {
final int version = mDatabase.getVersion();
final int currentVersion = 1;
if (version >= currentVersion) {
return;
}
mDatabase.beginTransaction();
switch (version) {
case 0:
createTables();
break;
}
mDatabase.setTransactionSuccessful();
mDatabase.setVersion(currentVersion);
mDatabase.endTransaction();
// 整理数据库
mDatabase.execSQL("VACUUM");
}
private void createTables() {
mDatabase.execSQL("create table if not exists Students(id integer primary key, name text not null)");
mDatabase.execSQL("create table if not exists Subjects(id integer primary key, name text not null)");
mDatabase.execSQL("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)");
}
}
due to the use of a single case, to rewrite the application:
public class TestApplication extends Application {
@Override
public void onCreate() {
super.onCreate();
new TestSqlite(getApplicationContext());
}
}
in AndroidManifest.xml to add application
<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:tools="http://schemas.android.com/tools"
package="com.example.testsqlite"
android:versionCode="1"
android:versionName="1.0" >
<uses-sdk
android:minSdkVersion="4"
android:targetSdkVersion="8"
tools:ignore="OldTargetApi" />
<application
android:name="com.example.database.TestApplication"
android:allowBackup="true"
android:icon="@drawable/ic_launcher"
android:label="@string/app_name"
android:theme="@style/AppTheme" >
<activity
android:name="com.example.testsqlite.MainActivity"
android:label="@string/app_name" >
<intent-filter>
<action android:name="android.intent.action.MAIN" />
<category android:name="android.intent.category.LAUNCHER" />
</intent-filter>
</activity>
</application>
</manifest>
code structure:
run the program, it will create a database in a private directory
in TestSqlite add the code into the student and the subject
private SQLiteStatement mInsertStudentInfoStatement;
public long insertStudentInfo(long id, String name) {
if (name == null) {
return -1;
}
if (mInsertStudentInfoStatement == null) {
mInsertStudentInfoStatement = mDatabase.compileStatement("insert or ignore into Students values (?,?)");
}
mInsertStudentInfoStatement.bindLong(1, id);
mInsertStudentInfoStatement.bindString(2, name);
return mInsertStudentInfoStatement.executeInsert();
}
private SQLiteStatement mInsertSubjectInfoStatement;
public long insertSubjectInfo(long id, String name) {
if (name == null) {
return -1;
}
if (mInsertSubjectInfoStatement == null) {
mInsertSubjectInfoStatement = mDatabase.compileStatement("insert or ignore into Subjects values (?,?)");
}
mInsertSubjectInfoStatement.bindLong(1, id);
mInsertSubjectInfoStatement.bindString(2, name);
return mInsertSubjectInfoStatement.executeInsert();
}
these two operations do not use execSQL instead use SQLiteStatement, which can improve efficiency.
Insert Course code:
private SQLiteStatement mInsertSubjectSelectStatement;
public long insertSubjectSelectInfo(long student_id, long subject_id) {
if (mInsertSubjectSelectStatement == null) {
mInsertSubjectSelectStatement = mDatabase
.compileStatement("insert or ignore into Subject_Select(student_id, subject_id, unique_check) values (?,?,?)");
}
String uniqueCheck = student_id + "_" + subject_id;
mInsertSubjectSelectStatement.bindLong(1, student_id);
mInsertSubjectSelectStatement.bindLong(2, subject_id);
mInsertSubjectSelectStatement.bindString(3, uniqueCheck);
return mInsertSubjectSelectStatement.executeInsert();
}
Subject_Select table's primary key id generated automatically, student_id and subject_id added references to constrain, and Subjects Students must be data in the table. unique_check added a unique constraint, the content makes up for the use of subject_id student_id and a string to prevent inserting duplicate data <p>
be tested in MainActiviy
public class MainActivity extends Activity {
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
TestSqlite.Instance().insertStudentInfo(1, "小明");
TestSqlite.Instance().insertStudentInfo(2, "小白");
TestSqlite.Instance().insertSubjectInfo(1, "数学");
TestSqlite.Instance().insertSubjectInfo(2, "语文");
TestSqlite.Instance().insertSubjectInfo(3, "英语");
TestSqlite.Instance().insertSubjectInfo(4, "物理");
TestSqlite.Instance().insertSubjectInfo(5, "化学");
TestSqlite.Instance().insertSubjectSelectInfo(1, 1);
TestSqlite.Instance().insertSubjectSelectInfo(1, 3);
TestSqlite.Instance().insertSubjectSelectInfo(1, 4);
TestSqlite.Instance().insertSubjectSelectInfo(2, 2);
TestSqlite.Instance().insertSubjectSelectInfo(2, 4);
TestSqlite.Instance().insertSubjectSelectInfo(2, 5);
}
}
through the student's chosen course name query id:
public List<String> getSelectSubjectNameByStudentId(long id) {
List<String> list = new ArrayList<String>();
String[] args = new String[] { id + "" };
Cursor cursor = mDatabase.rawQuery("select subjects.name from subjects,subject_select " +
"where subject_select.student_id = ? " +
"and subject_select.subject_id = subjects.id", args);
while (cursor.moveToNext()) {
list.add(cursor.getString(0));
}
return list;
}
Use the cursor in
One thing to note, in the splicing SQL statement, you can directly fight to the string parameter, for example:
Cursor cursor2 = mDatabase.rawQuery("select name from Students where id = " + id, null); can
Cursor cursor2 = mDatabase.rawQuery("select name from Students where id = ?", new String[] { id + "" }); Under normal circumstances, these two effects is the same wording, but the second method has the advantage of not considering the escape character, like \% $ & / "This string can be used directly, while the first One way to consider escape, or can not properly be identified.
test query code:
List<String> list = TestSqlite.Instance().getSelectSubjectNameByStudentId(1);
for (String item : list) {
Log.i(getClass().getName(), item);
}
Thus, a simple database on the design is completed, and after further improvement, the software on the line, in the development of 2.0, when there has been a new demand, to have courses scores, which requires Subject_Select table to add a new field score. But also to keep the previous data, on the basis of 1.0 upgrade, but also add new features, which requires the database refactoring.
we have to do is
1) will Subject_Select rename Subject_Select_Obsolete
2) according to the new demand created a Subject_Select
3) will Subject_Select_Obsolete data copied to Subject_Select
4) Remove Subject_Select_Obsolete
SQL statement 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
first to be judged in the code:
private void migrate() {
final int version = mDatabase.getVersion();
final int currentVersion = 2;
if (version >= currentVersion) {
return;
}
mDatabase.beginTransaction();
switch (version) {
case 0: {
createTables();
break;
}
case 1: {
updateTaples1();
break;
}
default:
break;
}
mDatabase.setTransactionSuccessful();
mDatabase.setVersion(currentVersion);
mDatabase.endTransaction();
// 整理数据库
mDatabase.execSQL("VACUUM");
}
In the above code, if the database version is 0, indicating that the database does not exist, create a table directly, if the database version is 1, indicating that the old version of the database, the database should be upgraded. Whether the upgrade or a new creation, version of the database are set to 2.
database upgrade code is:
private void updateTaples1() {
mDatabase.execSQL("alter table Subject_Select rename to Subject_Select_Obsolete");
mDatabase.execSQL("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)");
mDatabase.execSQL("insert into Subject_Select (id,student_id,subject_id,unique_check) select id,student_id,subject_id,unique_check from Subject_Select_Obsolete");
mDatabase.execSQL("drop table Subject_Select_Obsolete");
}
. 
