[Android] Database trong Android – P1 Tạo database
Hello everyone, today I begin to guide you a series on Database trong Android. To be specific vivid and I will guide you to make application “Note” a complete user database.
The script in this series I will write in English. However, it is his English-style writing “Vietnamese translation” So you will easily follow.
HERE: Android studio 1.2.2
Android SDK 5.1.1
Min SDK: 4.0 (Android 4.0 above will be used apps)
Content
If in java or c # sql we are familiar with or mysql to store the data, we use sqlite in Android to store.
In the other series regularly leads the guy you use class SQLiteDatabase to manipulate, but I will instruct you to use a combination SQLiteOpenHelper to be able to perform the most comprehensive upgrade is in process application we can update the database easily.
Step 1: Create the object class is stored database
In this step we create the class often is the database tables. For example, the required class tb_note table Note.
package cachhoc.net.tut.demodatabase;
public class Note {
private long id;
private String title;
private String content;
public String getTitle() {
return title;
}
public Note setTitle(String title) {
this.title = title;
return this;
}
public long getId() {
return id;
}
public Note setId(long id) {
this.id = id;
return this;
}
public String getContent() {
return content;
}
public Note setContent(String content) {
this.content = content;
return this;
}
}
In the code above you pay attention to the methods set. His return is full Note (class it's always) this allows us to facilitate the set of values and reduce runtime for program.
Step 2: Initialize database
In this step we will implement the database. You create a class DatabaseHelper inheritance SQLiteOpenHelper as follows:
package cachhoc.net.tut.demodatabase;
import android.content.Context;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
public class DatabaseHelper extends SQLiteOpenHelper {
public static final String DATABASE_NAME = "note.db";
/**
* table note contain id, title, content
*/
public static final String TABLE_NOTE = "tb_note";
public static final String KEY_ID_NOTE = "id";
public static final String KEY_TITLE_NOTE = "title";
public static final String KEY_CONTENT_NOTE = "content";
/**
* string for create table note
*/
public static final String CREATE_TABLE_NOTE =
"CREATE TABLE " + TABLE_NOTE + "(" +
KEY_ID_NOTE + " INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL" +
", " + KEY_TITLE_NOTE + " TEXT NOT NULL" +
"," + KEY_CONTENT_NOTE + " TEXT NOT NULL" +
")";
/**
* value for update database
*/
public static final int DATA_VERSION = 1;
/**
* Sqlite database
*/
private SQLiteDatabase db;
public DatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATA_VERSION);
}
/**
* create db when app start, and only call when database don't create
* When database created, it will not call
*/
@Override
public void onCreate(SQLiteDatabase db) {
try {
db.execSQL(CREATE_TABLE_NOTE);
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* call when change DATA_VERSION
* help we update database
*/
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
/**
* open database
*/
public void open() {
try {
db = getWritableDatabase();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* close database
*/
public void close() {
if (db != null && db.isOpen()) {
try {
db.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
Here you can pay attention to the following points:
- Variable DATABASE_NAME: is the name of our database. Each application should have only one database.
-
Table tb_note our schools id, title and content respectively and we have string CREATE_TABLE_NOTE to create tables
-
Variable DATA_VERSION is the version database. When you want to change the database in the following versions we have to raise up to database version updates.
-
Variable db the object class SQLiteDatabase will be variable to manipulate the database processing instructions
-
Method onCreate overwritten from class SQLiteOpenHelper will help us create databse.
-
Method onUpgrade will help us update the database when DATA_VERSION change.
-
The method open, close to close and open database.
Step 3: Write methods basic database query
In this step we will write some basic methods to retrieve data, insert data, updated or deleted.
/************************* method work with database *******************/
/**
* get all row of table with sql command then return cursor
* cursor move to frist to redy for get data
*/
public Cursor getAll(String sql) {
open();
Cursor cursor = db.rawQuery(sql, null);
if (cursor != null) {
cursor.moveToFirst();
}
close();
return cursor;
}
/**
* insert contentvaluse to table
*
* @param values value of data want insert
* @return index row insert
*/
public long insert(String table, ContentValues values) {
open();
long index = db.insert(table, null, values);
close();
return index;
}
/**
* update values to table
*
* @return index row update
*/
public boolean update(String table, ContentValues values, String where) {
open();
long index = db.update(table, values, where, null);
close();
return index > 0;
}
/**
* delete id row of table
*/
public boolean delete(String table, String where) {
open();
long index = db.delete(table, where, null);
close();
return index > 0;
}
/************************* end of method work with database *******************/
You may notice some new features compared to sql and mysql
- In our method returns GetAll Cursor, it is like a pointer to point to the record (row) the original table and pointing to where it has not, so we need to set it to point to the first row with the command cursor.moveToFirst();
-
In insert mode we call db.insert(table, null, values), in which values are an object of class ContentValues content to insert into the table. We will learn how to use it in step 3.
-
Inserts, update and delete them returns an integer type long as records are inserted location, updated or deleted. If no public records are made (error) it will return is 0.
Step 4: Write query methods for tables
Once that's done 2 as we've finished creating and accessing the database and can skip this step if you want, however we will encounter some problems or lengthy treatment in another class when we call the method to query. So to make an easier way, we should create the query method for each particular table. Here is the method to retrieve the table tb_note.
/************************* method work with note table *******************/
/**
* get Note by sql command
*
* @param sql sql to get note
*/
public Note getNote(String sql) {
Note note = null;
Cursor cursor = getAll(sql);
if (cursor != null) {
note = cursorToNote(cursor);
cursor.close();
}
return note;
}
/**
* @param sql get all notes with sql command
* @return arraylist of note
*/
public ArrayList<Note> getListNote(String sql) {
ArrayList<Note> list = new ArrayList<>();
Cursor cursor = getAll(sql);
while (!cursor.isAfterLast()) {
list.add(cursorToNote(cursor));
cursor.moveToNext();
}
cursor.close();
return list;
}
/**
* insert note to table
*
* @param note note to insert
* @return id of note
*/
public long insertNote(Note note) {
return insert(TABLE_NOTE, noteToValues(note));
}
/**
* @param note note to update
* @return id of note update
*/
public boolean updateNote(Note note) {
return update(TABLE_NOTE, noteToValues(note), KEY_ID_NOTE + " = " + note.getId());
}
/**
* delete id row of table
*/
public boolean deleteNote(String where) {
return delete(TABLE_NOTE, where);
}
/**
* convert note to contentvalues
* don't put id of note because
* when insert id will auto create
* when update we don't update id
*/
private ContentValues noteToValues(Note note) {
ContentValues values = new ContentValues();
values.put(KEY_TITLE_NOTE, note.getTitle());
values.put(KEY_CONTENT_NOTE, note.getContent());
return values;
}
/**
* convert cursor to note
*/
private Note cursorToNote(Cursor cursor) {
Note note = new Note();
note.setId(cursor.getInt(cursor.getColumnIndex(KEY_ID_NOTE)))
.setTitle(cursor.getString(cursor.getColumnIndex(KEY_TITLE_NOTE)))
.setContent(cursor.getString(cursor.getColumnIndex(KEY_CONTENT_NOTE)));
return note;
}
/************************* end of method work note table *******************/
In the above code you've commented quite clear, you try to understand.
Full code
package cachhoc.net.tut.demodatabase;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import java.util.ArrayList;
public class DatabaseHelper extends SQLiteOpenHelper {
public static final String DATABASE_NAME = "note.db";
/**
* table note contain id, title, content
*/
public static final String TABLE_NOTE = "tb_note";
public static final String KEY_ID_NOTE = "id";
public static final String KEY_TITLE_NOTE = "title";
public static final String KEY_CONTENT_NOTE = "content";
/**
* string for create table note
*/
public static final String CREATE_TABLE_NOTE =
"CREATE TABLE " + TABLE_NOTE + "(" +
KEY_ID_NOTE + " INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL" +
", " + KEY_TITLE_NOTE + " TEXT NOT NULL" +
"," + KEY_CONTENT_NOTE + " TEXT NOT NULL" +
")";
/**
* value for update database
*/
public static final int DATA_VERSION = 1;
/**
* Sqlite database
*/
private SQLiteDatabase db;
public DatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATA_VERSION);
}
/**
* create db when app start, and only call when database don't create
* When database created, it will not call
*/
@Override
public void onCreate(SQLiteDatabase db) {
try {
db.execSQL(CREATE_TABLE_NOTE);
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* call when change DATA_VERSION
* help we update database
*/
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
/**
* open database
*/
public void open() {
try {
db = getWritableDatabase();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* close database
*/
public void close() {
if (db != null && db.isOpen()) {
try {
db.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/************************* method work with database *******************/
/**
* get all row of table with sql command then return cursor
* cursor move to frist to redy for get data
*/
public Cursor getAll(String sql) {
open();
Cursor cursor = db.rawQuery(sql, null);
if (cursor != null) {
cursor.moveToFirst();
}
close();
return cursor;
}
/**
* insert contentvaluse to table
*
* @param values value of data want insert
* @return index row insert
*/
public long insert(String table, ContentValues values) {
open();
long index = db.insert(table, null, values);
close();
return index;
}
/**
* update values to table
*
* @return index row update
*/
public boolean update(String table, ContentValues values, String where) {
open();
long index = db.update(table, values, where, null);
close();
return index > 0;
}
/**
* delete id row of table
*/
public boolean delete(String table, String where) {
open();
long index = db.delete(table, where, null);
close();
return index > 0;
}
/************************* end of method work with database *******************/
/************************* method work with note table *******************/
/**
* get Note by sql command
*
* @param sql sql to get note
*/
public Note getNote(String sql) {
Note note = null;
Cursor cursor = getAll(sql);
if (cursor != null) {
note = cursorToNote(cursor);
cursor.close();
}
return note;
}
/**
* @param sql get all notes with sql command
* @return arraylist of note
*/
public ArrayList<Note> getListNote(String sql) {
ArrayList<Note> list = new ArrayList<>();
Cursor cursor = getAll(sql);
while (!cursor.isAfterLast()) {
list.add(cursorToNote(cursor));
cursor.moveToNext();
}
cursor.close();
return list;
}
/**
* insert note to table
*
* @param note note to insert
* @return id of note
*/
public long insertNote(Note note) {
return insert(TABLE_NOTE, noteToValues(note));
}
/**
* @param note note to update
* @return id of note update
*/
public boolean updateNote(Note note) {
return update(TABLE_NOTE, noteToValues(note), KEY_ID_NOTE + " = " + note.getId());
}
/**
* delete id row of table
*/
public boolean deleteNote(String where) {
return delete(TABLE_NOTE, where);
}
/**
* convert note to contentvalues
* don't put id of note because
* when insert id will auto create
* when update we don't update id
*/
private ContentValues noteToValues(Note note) {
ContentValues values = new ContentValues();
values.put(KEY_TITLE_NOTE, note.getTitle());
values.put(KEY_CONTENT_NOTE, note.getContent());
return values;
}
/**
* convert cursor to note
*/
private Note cursorToNote(Cursor cursor) {
Note note = new Note();
note.setId(cursor.getInt(cursor.getColumnIndex(KEY_ID_NOTE)))
.setTitle(cursor.getString(cursor.getColumnIndex(KEY_TITLE_NOTE)))
.setContent(cursor.getString(cursor.getColumnIndex(KEY_CONTENT_NOTE)));
return note;
}
/************************* end of method work note table *******************/
}
In part 2 I will guide you to make the interface style Material Event Design and starts creating a complete app.
Posts made in the tutorial Database trong Android by nguyenvanquan7826
Tutorial Android
[rps-include post=”4210″ shortcodes=”false”]
Android basic part Hopefully there will be many more articles.
I want to ask is if the management wanted to add a different Note saved just did not want her telephone number with this note, then we have to create another DatabaseHelper or create a new public class but shared Note this Database ?
You can create 1 Other tables đk. But we rarely ng create multiple databases in 1 app
I got it. Thank you.
Can I use the data in the database as items for autocompleteTexview like listview
have too.
Brother asked me is there any way to get information out of her table as columns and the format of the column is not it ?