数码营电子:基于微型控制器的编程、电路设计与电子产品制做。邮箱:shumaying@hotmail.com

Handbook

From 数码营电子实验室
Jump to navigation Jump to search

Create a Database Android Application in Android Studio 3.0[edit]

操作过程中,会遇到的问题:

1、SQL语句书写不完整,程序运行时,提示错误:

android.database.sqlite.SQLiteException: table Student has no column named StudentName (code 1): , while compiling: INSERT INTO Student(StudentName,StudentID) VALUES (?,?)

原因:SQL语句本身是一个字符串String,嵌入到代码里面时,注意变量和其余的字符需要区别开来,中间用“+”隔开。例如:sql语句:

CREATE TABLE persons ( 
   studentID INTEGER PRIMARY KEY,
   studentName TEXT
   
);

在java文件的代码中写成:

String CREATE_TABLE = "CREATE TABLE" + TABLE_NAME + "(" + COLUMN_ID +   "INTEGER PRIMARYKEY," + COLUMN_NAME + "TEXT )";

再执行语句:

db.execSQL(CREATE_TABLE);

2、注意:对sql语句进行修改,后,需要删除虚拟机上运行的app,清理掉已生成的table,重新运行app,才能看到最新效果。这样onCreate()方法会重新执行。因为 MYSQLite 数据库里的table 是储存在本地设备里面的,不会自行更新。

参考资料:When is SQLiteOpenHelper onCreate() / onUpgrade() run?.

Background[edit]

SQLite[edit]

SQLite is a relational database management system (RDBMS). If most RDBMSs such as MySQL, Oracle, etc. are standalone server processes, then SQLite is embedded because it is provided in the form of a library that is linked in applications.

Like other RDBMSs, data is accessed in a SQLite database by using Structured Query Language (SQL).

Android SQLite Java Classes[edit]

Cursor: a class provides access to the results of a database query. Its methods include:

  1. close(): release all resources used by cursor and close it.
  2. getCount(): returns the number of rows contained within the result set.
  3. moveToFirst(): moves to the first row in the result set.
  4. moveToLast(): moves to the last row in the result set.
  5. moveToNext(): moves to the next row in the result set.
  6. move(): moves by a specified offset from the current position in the result set.
  7. get<type>() (such as getInt(), getDouble(), so on): returns the value of the specified <type> contained at the specified column index of the row at the current cursor position.

SQLiteDatabase provides the primary interface between the application code and underlying SQLite database. Its methods include:

  1. insert(): inserts a new row into a database table.
  2. delete(): deletes rows from a database table
  3. query(): performs a specified database query and returns matching results via a Cursor object.
  4. execSQL(): executes a single SQL Statement that does not return result data.
  5. rawQuery(): executes an SQL query statement and returns matching results in the form of a Cursor object.

SQLiteOpenHelper is designed to make it easier to create and update databases. Its methods include:

  1. onCreate(): called when the database is created for the first time.
  2. onUpgrade(): called in the event that the application code contains a more recent database version number reference.
  3. onOpen(): called when the database is opened.
  4. getWritableDatabase(): opens or creates a database for reading and writing.
  5. getReadableDatabase(): creates or opens a database for reading only.
  6. close(): closes the database.

ContentValues allows key/value pairs to be declared consisting of table column identifiers and the values to be stored in each column. Its methods include:

put(): adds a value to the set.


Create a Database Android Application[edit]

application UI

We will create a database Android application that has a UI as follows:

My application will interact with a database named StudentDB.db, which contains a single table named Student. The Student table schema will look like this:

Column Data Type
StudentID Integer/Primary Key/Auto Increment
StudentName Text
application UI

The application will consist of an activity and a database handler class (MyDBHandler class). The database handler will be a subclass of SQLiteOpenHelper and will provide an abstract layer between the underlying SQLite database and the activity class. A third class (Student class) will need to be implemented to hold the database entry data as it is passed between the activity and the handler. My application model can be shown in the following figure:

Data Model Class[edit]

The Student class contains fields, constructors, and properties as follows:

Student Class
Fields Constructors Properties
StudentID Students() setID(), getID()
StudentName Student(id, studentname) setStudentId(), setStudentName()

We create the Student class in the Android Studio 3.0 by selecting app > java.

Right-click the myfirstdatabase package and selecting New > Java Class.

Type Student in the Name item, maintain the default options, and click the OK button.

Adding the following lines of code for the Student class:

 public class Student {
// fields
private int studentID;
private String studentName;
// constructors
public Student() {}
public Student(int id, String studentname) {
  this.studentID = id;
  this.studentName = studentname;
 }
 // properties
public void setID(int id) {
 this.studentID = id;
}
public int getID() {
 return this.studentID;
}
public void setStudentName(String studentname) {
 this.studentName = studentname;
}
public String getStudentName() {
 return this.studentName;
}
}

Data Handler Class[edit]

The MyDBHandler class is a subclass of SQLiteOpenHelper class

The database handler class is a subclass of SQLiteOpenHelper class, named MyDBHandler, as in the following figure:

The MyDBHandler class contains fields, constructors, and methods, as follows:

MyDBHandler
Fields Constructors Methods
DATABASE_VERSION MyDBHandler() onCreate(), onUpdate()
DATABASE_NAME addHandler(), deleteHandler()
COLUMN_ID updateHandler(), findHandler()
COLUMN_NAME loadHandler()

Steps for creating the MyDBHandler class like the Student class and its code can look like this:

public class MyDBHandler extends SQLiteOpenHelper {
//information of database
private static final int DATABASE_VERSION = 1;
private static final String DATABASE_NAME = "studentDB.db";
public static final String TABLE_NAME = "Student";
public static final String COLUMN_ID = "StudentID";
public static final String COLUMN_NAME = "StudentName";
//initialize the database
public MyDBHandler(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
 super(context, DATABASE_NAME, factory, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {}
@Override
public void onUpgrade(SQLiteDatabase db, int i, int i1) {}
public String loadHandler() {}
public void addHandler(Student student) {}
public Student findHandler(String studentname) {}
public boolean deleteHandler(int ID) {}
public boolean updateHandler(int ID, String name) {}
}

We must also use import statements, as follows:

import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.content.Context;
import android.content.ContentValues;
import android.database.Cursor

Initialize the Database[edit]

The database can be initialized in the constructor of the MyDBHandler class. The code of this constructor looks like this:

public MyDBHandler(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
super(context, DATABASE_NAME, factory, DATABASE_VERSION);
}

Create the Student Table[edit]

The SQL statement creates a table:

CREATE TABLE table_name(

column1 datatype,
column2 datatype,
column3 datatype,
....
);

We need to convert the SQL statement to a string and implement it by using the execSQL() method of a SQLiteDatabase object. All of these statements will be put inside the onCreate method of the handler class, as follows:

public void onCreate(SQLiteDatabase db) {
String CREATE_TABLE = "CREATE TABLE" + TABLE_NAME + "(" + COLUMN_ID +
 "INTEGER PRIMARYKEY," + COLUMN_NAME + "TEXT )";
db.execSQL(CREATE_TABLE);
}

Our application can load all of the students from the database, add a new student to the database, remove a student from the database, find a student from the database and modify the information of a student from the database. Therefore, we need to add corresponding methods to the handler class.

Load Data[edit]

To load data, we use a SQL query statement:

SELECT * FROM table_name;

The result of above SQL statement is a table. We use the rawQuery() method of a SQLiteDatabase object to implement SQL statement and display result via a Cursor object. The following code will demonstrate the loadHandler method:

public String loadHandler() {
String result = "";
String query = "Select*FROM" + TABLE_NAME;
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(query, null);
while (cursor.moveToNext()) {
 int result_0 = cursor.getInt(0);
 String result_1 = cursor.getString(1);
 result += String.valueOf(result_0) + " " + result_1 +
  System.getProperty("line.separator");
}
cursor.close();
db.close();
return result;
}

Add a New Record[edit]

To add a new record to the database, we must use the ContentValues object with the put() method that is used to assign data to ContentsValues object and then use insert() method of SQLiteDatabase object to insert data to the database. The addHandler method can look like this:

public void addHandler(Student student) {
ContentValues values = new ContentValues();
values.put(COLUMN_ID, student.getID());
values.put(COLUMN_NAME, student.getStudentName());
SQLiteDatabase db = this.getWritableDatabase();
db.insert(TABLE_NAME, null, values);
db.close();
}

Find Information by Condition[edit]

To find information in the database by condition, we must use the SQL query statement as follows:

SELECT * FROM table_name WHERE column_name;

In the next step, we save the result that is returned from implementation of the rawQuery() method of the SQLiteDatabase object into a Cursor object and find the matching result in this object. The code of the findHandler method looks like this:

public Student findHandler(Stringstudentname) {
Stringquery = "Select * FROM " + TABLE_NAME + "WHERE" + COLUMN_NAME + " = " + "'" + studentname + "'";
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(query, null);
Student student = new Student();
if (cursor.moveToFirst()) {
 cursor.moveToFirst();
 student.setID(Integer.parseInt(cursor.getString(0)));
 student.setStudentName(cursor.getString(1));
 cursor.close();
} else {
 student = null;
}
db.close();
return student;
}

Delete a Record by Condition[edit]

To delete a record by condition, we must use a SQL query statement, as follows:

SELECT * FROM table_name WHERE column_name;

We will save the result that is returned from the implementation of the rawQuery() method of the SQLiteDatabase object into a Cursor object and find the matching result in this object. In the final step, we use the delete() method of the SQLiteDatabase object to delete the record. The code of the deleteHandler method looks like:

public boolean deleteHandler(int ID) {
booleanresult = false;
Stringquery = "Select*FROM" + TABLE_NAME + "WHERE" + COLUMN_ID + "= '" + String.valueOf(ID) + "'";
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(query, null);
Student student = new Student();
if (cursor.moveToFirst()) {
 student.setID(Integer.parseInt(cursor.getString(0)));
 db.delete(TABLE_NAME, COLUMN_ID + "=?",
  newString[] {
   String.valueOf(student.getID())
  });
 cursor.close();
 result = true;
}
db.close();
return result;
}

Update Information of a Record[edit]

To update the information of a record, we can use the ContentValues object and the update() method of the SQLiteDatabase object. Use the updateHandler() method as follows:

public boolean updateHandler(int ID, String name) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues args = new ContentValues();
args.put(COLUMN_ID, ID);
args.put(COLUMN_NAME, name);
return db.update(TABLE_NAME, args, COLUMN_ID + "=" + ID, null) > 0;
}

Main Activity[edit]

So far, let’s look at our application model again:

And our user interface:

Controls used in above UI include:

Controls(控件类型) text atrribute(文字显示值) id attribute(id值)
TextView Student ID txt_studentld
TextView Student Name txt_studentname
EditText edt_studentid
EditText edt_studentname
Button LOAD DATA btn_load
Button ADD btn_add
Button FIND btn_find
Button DELETE btn_delete
Button UPDATE btn_update
TextView list txt_list

In the string.XML file:

<resources>
   <string name="app_name">MyFirstDataBase</string>
   <string name="studentid">Student ID</string>
   <string name="studentname">Student Name</string>
   <string name="load">LOAD DATA</string>
   <string name="add">ADD</string>
   <string name="find">FIND</string>
   <string name="update">UPDATE</string>
   <string name="delete">DELETE</string>
   <string name="list">LIST</string>
</resources>

The code for the click event of the buttons and results is as follows:

Code for the ADD button:

public void addStudent(View view) {
MyDBHandler dbHandler = new MyDBHandler(this, null, null, 1);
       int id = Integer.parseInt(studentID.getText().toString());
       String name = studentName.getText().toString();
       Student student = new Student(id, name);
       dbHandler.addHandler(student);
       studentID.setText("");
       studentName.setText("");
}

In the activity_main.XML file:

<Button
android:id="@+id/btn_add"
android:onClick="addStudent"
android:text="@string/add"
.... />

Code for the FIND button:

public void findStudent(View view) {
MyDBHandler dbHandler = new MyDBHandler(this, null, null, 1);
       Student student =
               dbHandler.findHandler(studentName.getText().toString());
       if (student != null) {
           list.setText(String.valueOf(student.getID()) + " " + student.getStudentName() + System.getProperty("line.separator"));
           studentID.setText("");
           studentName.setText("");
       } else {
           list.setText("No Match Found");
           studentID.setText("");
           studentName.setText("");
       }
}

Code for the LOAD DATA button:

public void loadStudents(View view) {
MyDBHandler dbHandler = new MyDBHandler(this, null, null, 1);
       list.setText(dbHandler.loadHandler());
       studentID.setText("");
       studentName.setText("");
}

In the string.XML file:

<Button
android:id="@+id/btn_load"
android:onClick="loadStudents"
android:text="@string/load"
......
/>

Code for the DELETE button:

public void removeStudent(View view) {
MyDBHandler dbHandler = new MyDBHandler(this, null, null, 1);
       boolean result = dbHandler.deleteHandler(Integer.parseInt( studentID.getText().toString()));
       if (result) {
           studentID.setText("");
           studentName.setText("");
           list.setText("Record Deleted");
       } else
           studentID.setText("No Match Found");
}

Code for the UPDATE button:

public void updateStudent(View view) {
MyDBHandler dbHandler = new MyDBHandler(this, null, null, 1);
       boolean result = dbHandler.updateHandler(Integer.parseInt(
               studentID.getText().toString()), studentName.getText().toString());
       if (result) {
           studentID.setText("");
           studentName.setText("");
           list.setText("Record Updated");
       } else
           studentID.setText("No Match Found");
}

Conclusion:

In this article, I have introduced how to create a simple database Android application in Android Studio 3.0. I hope that this article is useful for beginners who are learning Android programming. The original address for this article

Qrcode for gh a3b9e9962ce8 1280.jpg