Contents

SQLite là gì ?

SQLite là một cơ sở dữ liệu quan hệ, mã nguồn mở, nó được tích hợp sẵn trên hệ điều hành Android, vì vậy bạn có thể sử dụng nó bất cứ lúc nào, và không cần phải cấu hình gì thêm.

Thông thường với các cơ sở dữ liệu như Oracle, MySQL,.. bạn cần phải có thư viện điều khiển (Driver libary), và tạo kết nối JDBC, tuy nhiên với SQLite điều đó là không cần thiết.

Các bước để làm việc với Database SQLite

Tạo class DataBaseUtil extends từ SQLiteOpenHelper.
Sau khi extends class của bạn từ SQLiteOpenHelper bạn cần phải ghi đè lên hai phương thức onCreate() và onUpgrage()
- onCreate() – Những là nơi mà chúng ta cần phải viết để tạo bảng. Điều này được gọi là khi cơ sở dữ liệu được tạo ra.
- onUpgrade() – Phương thức này được gọi là khi cơ sở dữ liệu được nâng cấp như thay đổi cấu trúc bảng, thêm giàng buộc cho cơ sở dữ liệu, v..v.

public class DataBaseUtil extends SQLiteOpenHelper {

public DataBaseUtil(Context context) {

    super(context, DATABASE_NAME, null, DATABASE_VERSION);

}

@Override

publicvoidonCreate(SQLiteDatabase db) {

    // Script to create table.

    String script = "CREATE TABLE "+ TABLE_NOTE + "("

            + COLUMN_NOTE_ID + " INTEGER PRIMARY KEY,"+ COLUMN_NOTE_TITLE + " TEXT,"

            + COLUMN_NOTE_CONTENT + " TEXT"+ ")";

    // Execute script.

    db.execSQL(script);

}

@Override

publicvoidonUpgrade(SQLiteDatabase db, intoldVersion, intnewVersion) {

    // Drop table

    db.execSQL("DROP TABLE IF EXISTS "+ TABLE_NOTE);

    // Recreate

    onCreate(db);

}

}

Tạo một project & thiết kế giao diện

Tạo một project mới để thực hành với các ví dụ.

Xây dựng ứng dụng quản lý môn học đơn giản

Note.Class

import java.io.Serializable;

public class Note implements Serializable{
private int id;
private String title;
private String content;
private String ngay;

public Note() {

}

public String getOuput(){
return getTitle()+"\n "+ getContent()+" "+getNgay();
}

public Note(String title, String content, String ngay) {
this.title = title;
this.content = content;
this.ngay = ngay;
}

public int getId() {
return id;
}

public void setId(int id) {
this.id = id;
}

public String getContent() {
return content;
}

public void setContent(String content) {
this.content = content;
}

public String getNgay() {
return ngay;
}

public void setNgay(String ngay) {
this.ngay = ngay;
}

public String getTitle() {
return title;
}

public void setTitle(String title) {
this.title = title;
}

@Override
public String toString() {
return title + "\n" + content + "\n" + ngay;
}

public Note(int id, String title, String content, String ngay) {

this.id = id;
this.content = content;
this.ngay = ngay;
this.title = title;
}

}

DataBaseUtil.class

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

import java.util.ArrayList;

public class DataBaseUtil extends SQLiteOpenHelper {
private static String dbName = "dbnotes";
private static int version = 1;
// Contacts table name
private static final String tblNote = "Notes";

// Contacts Table Columns names
private static final String KEY_ID = "id";
private static final String KEY_NAME = "name";
private static final String KEY_TITLE = "title";
private static final String KEY_DATECREATE = "datecreate";

public DataBaseUtil(Context context) {

super(context, dbName, null, version);
}

@Override
public void onCreate(SQLiteDatabase db) {

//tao bang
String CREATE_CONTACTS_TABLE = "CREATE TABLE " + tblNote +
"("
+ KEY_ID + " INTEGER PRIMARY KEY," + KEY_NAME + " TEXT," + KEY_TITLE + " TEXT, " + KEY_DATECREATE + " TEXT" +
")";
//String n = "insert into Notes(id,title,content,ngay) values (4,'Hoc Android','tai itplus',9/9/2009)";
db.execSQL(CREATE_CONTACTS_TABLE);
// db.execSQL(n);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// Drop older table if existed
db.execSQL("DROP TABLE IF EXISTS " + tblNote);

// Create tables again
onCreate(db);

}

public ArrayList<Note> getAllNotes() {
ArrayList<Note> noteList = new ArrayList<Note>();
// Select All Query
String selectQuery1 = "SELECT * FROM " + tblNote+"where phone ?"; // tra ve mot mang 2 chieu

String selectQuery = "SELECT * FROM " + tblNote; // tra ve mot mang 2 chieu

SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);
// looping through all rows and adding to list
if (cursor.moveToFirst()) { //neu con trỏ đến vị trí đầu tiên
do {
Note note = new Note();
note.setId(Integer.parseInt(cursor.getString(0)));
note.setContent(cursor.getString(1));
note.setTitle(cursor.getString(2));
note.setNgay(cursor.getString(3));
// Adding contact to list
noteList.add(note);
} while (cursor.moveToNext());
}

// return contact list
return noteList;
}
public ArrayList<Note> getWhereList(String s) {
ArrayList<Note> noteList = new ArrayList<Note>();
// Select All Query
String selectQuery1 = "SELECT * FROM " + tblNote+" WHERE "+KEY_TITLE+" LIKE '%"+s+"%'"; // tra ve mot mang 2 chieu

String selectQuery = "SELECT * FROM " + tblNote; // tra ve mot mang 2 chieu
//select * from listnote where KEY_NAME like 'hoc%' // % la tat ca ky tu bat dau bang hoc
SQLiteDatabase db = this.getWritableDatabase();
//
Cursor cursor = db.rawQuery(selectQuery1, null);
// looping through all rows and adding to list
if (cursor.moveToFirst()) { //neu con trỏ đến vị trí đầu tiên
do {
Note note = new Note();
note.setId(Integer.parseInt(cursor.getString(0)));
note.setContent(cursor.getString(1));
note.setTitle(cursor.getString(2));
note.setNgay(cursor.getString(3));
// Adding contact to list
noteList.add(note);
} while (cursor.moveToNext());
}

// return contact list
return noteList;
}
public long addNote(Note note) throws Exception {

SQLiteDatabase db = null;
long id;
try {
//open connection
db = this.getWritableDatabase();

ContentValues values = new ContentValues();

values.put(KEY_NAME, note.getContent());
values.put(KEY_TITLE, note.getTitle());
values.put(KEY_DATECREATE, note.getNgay());

// Inserting Row, return id
id = db.insert(tblNote, "", values); //nếu chưa put vào cột nà thì sẽ viết " " vào cột đó

} catch (Exception ex) {
throw new Exception(ex.getMessage());
} finally {
db.close(); // Closing database connection
}
return id;
}

public int updateNoteById(String id, Note note) {

SQLiteDatabase db = this.getWritableDatabase();

ContentValues values = new ContentValues();

values.put(KEY_NAME, note.getContent());
values.put(KEY_TITLE, note.getTitle());
values.put(KEY_DATECREATE, note.getNgay());
int value=db.update(tblNote, values, KEY_ID + "=?",
new String[]{(id)});
// updating row
//update notes set...where id = 123;
db.close();
return value;
}

// Updating single Note
public int updateNote(Note note) {

SQLiteDatabase db = this.getWritableDatabase();

ContentValues values = new ContentValues();

values.put(KEY_NAME, note.getContent());
values.put(KEY_TITLE, note.getTitle());
values.put(KEY_DATECREATE, note.getNgay());
int value=db.update(tblNote, values, KEY_ID + " = ?",
new String[]{String.valueOf(note.getId())});
// updating row
//update notes set...where id = 123;
db.close();
return value;
}

// Deleting single Note
public void deleteNote(int id) {
SQLiteDatabase db = this.getWritableDatabase();
db.delete(tblNote, KEY_ID + " = ?", new String[]{String.valueOf(id)});
//update note set name="abc","xyz","ggg" where id=1 ande code 3
db.close();
}

}

MainActivity.class

import android.content.Context;
import android.content.DialogInterface;
import android.content.Intent;
import android.os.Bundle;
import android.os.Handler;
import android.support.annotation.Nullable;
import android.support.v7.app.AlertDialog;
import android.support.v7.app.AppCompatActivity;
import android.view.ContextMenu;
import android.view.Menu;
import android.view.MenuInflater;
import android.view.MenuItem;
import android.view.View;
import android.widget.AdapterView;
import android.widget.ArrayAdapter;
import android.widget.ListView;
import android.widget.Toast;

import java.util.ArrayList;

public class MainActivity extends AppCompatActivity {
public static final int addnumber = 1;
private int index;
private ListView lvNote;
private ArrayList<Note> arrNote;
private ArrayAdapter<Note> arrayAdapter;
private DataBaseUtil db;
private boolean doubleBackToExitPressedOnce = false;

@Override
protected void onCreate(@Nullable Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.listview_demo);
db = new DataBaseUtil(this);
arrNote = db.getAllNotes();
lvNote = (ListView) findViewById(R.id.listView);
arrayAdapter = new ArrayAdapter<Note>(this, android.R.layout.simple_list_item_1, arrNote);
lvNote.setAdapter(arrayAdapter);

lvNote.setOnItemClickListener(new AdapterView.OnItemClickListener() {
@Override
public void onItemClick(AdapterView<?> adapterView, View view, int i, long l) {
Note note = arrNote.get(i);
Toast.makeText(getApplicationContext(), note.getOuput(), Toast.LENGTH_SHORT).show();
}
});
registerForContextMenu(lvNote);
}

@Override
public void onCreateContextMenu(ContextMenu menu, View v, ContextMenu.ContextMenuInfo menuInfo) {
super.onCreateContextMenu(menu, v, menuInfo);
MenuInflater inflater = getMenuInflater();
inflater.inflate(R.menu.context_menu, menu);

}

@Override
public boolean onCreateOptionsMenu(Menu menu) {
MenuInflater inflater = getMenuInflater();
inflater.inflate(R.menu.option_menu, menu);
return super.onCreateOptionsMenu(menu);
}

@Override
public boolean onContextItemSelected(MenuItem item) {
AdapterView.AdapterContextMenuInfo info = (AdapterView.AdapterContextMenuInfo) item.getMenuInfo(); //tra ve posison
index = info.position;
int id = item.getItemId();
switch (id) {
case R.id.ct_sua:
Intent intent = new Intent(MainActivity.this, AddActivity.class);
intent.putExtra("flag", "1");
final Note note = arrNote.get(index);
intent.putExtra("note_edit", note);
startActivityForResult(intent, addnumber);
break;
case R.id.ct_xoa:
AlertDialog.Builder builder = new AlertDialog.Builder(this); //AlertDialog
builder.setMessage("ban co muon xoa khong?");
builder.setNegativeButton("Bo qua", new DialogInterface.OnClickListener() {
@Override
public void onClick(DialogInterface dialog, int which) {
dialog.dismiss();
}
});
builder.setPositiveButton("Dong y", new DialogInterface.OnClickListener() {

@Override
public void onClick(DialogInterface dialog, int which) {
//xoa ban ghi trong csdl
//xoa tren giao dien
//cap nhat giao dien

int t = arrNote.get(index).getId();
db.deleteNote(t);
arrNote.remove(index);
arrayAdapter.notifyDataSetChanged();
Toast.makeText(getApplicationContext(), "Xoa thanh cong!", Toast.LENGTH_SHORT).show();
}
});
AlertDialog dialog = builder.create();
dialog.show();
break;

}
return super.onContextItemSelected(item);

}

@Override
public boolean onOptionsItemSelected(MenuItem item) {
int id = item.getItemId();
switch (id) {
case R.id.help:
Toast.makeText(getApplicationContext(), "help", Toast.LENGTH_SHORT).show();
return true;
case R.id.update:
Toast.makeText(getApplicationContext(), "Check Update", Toast.LENGTH_SHORT).show();
return true;
case R.id.setting:

Toast.makeText(getApplicationContext(), "Setting", Toast.LENGTH_SHORT).show();
return true;
case R.id.ct_add:

Intent intent = new Intent(MainActivity.this, AddActivity.class);
intent.putExtra("flag", "0");
startActivityForResult(intent, addnumber);

return true;
case R.id.ct_search:
Toast.makeText(getApplicationContext(), "search", Toast.LENGTH_SHORT).show();

return true;
default:
return super.onContextItemSelected(item);

}

}

@Override
protected void onActivityResult(int requestCode, int resultCode, Intent data) {
super.onActivityResult(requestCode, resultCode, data);
if (requestCode == addnumber) {
if (resultCode == 2) {
Note note = (Note) data.getSerializableExtra("data");
// db.addNote(note);//call addNote method of DatabaseUtil to insert into SQLite
arrNote.add(note);
arrayAdapter.notifyDataSetChanged();

} else if (resultCode == 3) {
Note note = (Note) data.getSerializableExtra("data");
arrNote.set(index, note);
arrayAdapter.notifyDataSetChanged();

}

}
}
////////////////////////////////////////////
public void backSystem() {
moveTaskToBack(true);
android.os.Process.killProcess(android.os.Process.myPid());
System.exit(1);
// super.onBackPressed();
// finish();
}

@Override
public void onBackPressed() {

if (doubleBackToExitPressedOnce) {

///////////////////
AlertDialog.Builder builder = new AlertDialog.Builder(this); //AlertDialog
builder.setMessage("Do you want to exit?");
builder.setNegativeButton("No", new DialogInterface.OnClickListener() {
@Override
public void onClick(DialogInterface dialog, int which) {
dialog.dismiss();
}
});
builder.setPositiveButton("Ok", new DialogInterface.OnClickListener() {

@Override
public void onClick(DialogInterface dialog, int which) {

backSystem();
}
});
AlertDialog dialog = builder.create();
dialog.show();
////////

}

this.doubleBackToExitPressedOnce = true;
// Toast.makeText(this, "Please click BACK again to exit", Toast.LENGTH_SHORT).show();

new Handler().postDelayed(new Runnable() {

@Override
public void run() {
doubleBackToExitPressedOnce = false;
}
}, 2000);

}

}

AddActivity.class

import android.app.DialogFragment;
import android.content.Intent;
import android.os.Bundle;
import android.support.annotation.Nullable;
import android.support.v7.app.AppCompatActivity;
import android.util.Log;
import android.view.Menu;
import android.view.MenuInflater;
import android.view.MenuItem;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;
import android.widget.Toast;

public class AddActivity extends AppCompatActivity {
EditText ed_title, ed_content, ed_id, ed_ngay;
Button btn_save, btn_exit;
DataBaseUtil db;
String id;
TextView tv_ngay;

@Override
protected void onCreate(@Nullable Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
Intent i = getIntent();
final String flag = i.getStringExtra("flag");
if(flag.equals("1"))
setTitle("Edit Item");
else setTitle("Add Item");
setContentView(R.layout.addactivity);
db = new DataBaseUtil(this);
ed_title = (EditText) findViewById(R.id.ed_title);

ed_content = (EditText) findViewById(R.id.ed_content);
// ed_ngay = (EditText) findViewById(R.id.ed_ngay);
btn_save = (Button) findViewById(R.id.btn_save);

tv_ngay = (TextView) findViewById(R.id.tv_ngay);
//đưa dữ liệu lên mh

if (flag.equals("1")) {
Note note = (Note) i.getSerializableExtra("note_edit");
id = note.getId() + "";
ed_title.setText(note.getTitle());
ed_content.setText(note.getContent());
tv_ngay.setText(note.getNgay());

}
tv_ngay.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
DialogFragment newFragment = new TimePickerFragment();
newFragment.show(getFragmentManager(), "TimePicker");
}
});
btn_save.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {

String title = ed_title.getText().toString();
String content = ed_content.getText().toString();
String ngay = tv_ngay.getText().toString();
if (title.equals("")) {
ed_title.setError("Use must input Title!");
ed_title.requestFocus();
} else {
Intent intent = getIntent();

//them csdl // loi sua o day
if (flag.equals("0")) {
Note n = new Note(1, title, content, ngay);
long idNote;
try {
idNote = db.addNote(n);
n.setId((int) idNote);
intent.putExtra("data", n);
} catch (Exception e) {
Log.e("Add", e.getMessage());
}
setResult(2, intent);
Toast.makeText(getApplicationContext(), "Add Success!", Toast.LENGTH_SHORT).show();
} else if (flag.equals("1")) {
Note n = new Note(Integer.parseInt(id), title, content, ngay);
db.updateNote(n);
intent.putExtra("data", n);
setResult(3, intent);
Toast.makeText(getApplicationContext(), "Save Success!", Toast.LENGTH_SHORT).show();
}
// Intent i = new Intent(AddActivity.this, MainActivity.class);
//startActivity(i);
finish();
}
}
});

}

@Override
public boolean onCreateOptionsMenu(Menu menu) {
MenuInflater inflater = getMenuInflater();
inflater.inflate(R.menu.option_menu2, menu);
return super.onCreateOptionsMenu(menu);
}

@Override
public boolean onOptionsItemSelected(MenuItem item) {
int id = item.getItemId();
switch (id) {
case R.id.help:
Toast.makeText(getApplicationContext(), "help", Toast.LENGTH_SHORT).show();
return true;
case R.id.update:
Toast.makeText(getApplicationContext(), "Check Update", Toast.LENGTH_SHORT).show();
return true;
case R.id.setting:
Toast.makeText(getApplicationContext(), "Setting", Toast.LENGTH_SHORT).show();
return true;
case R.id.home:
//Intent intent = new Intent(AddActivity.this, MainActivity.class);
// startActivity(intent);
finish();
default:
return super.onContextItemSelected(item);

}

}
}

Code Layout

listview_demo.xml
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical">
<ListView
android:id="@+id/listView"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:background="@drawable/bg_list" />
</LinearLayout>

addactivity.xml
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="350sp"
android:layout_height="180sp"
android:background="@color/color_trangsua"
android:orientation="vertical"
android:weightSum="1">

<EditText
android:id="@+id/ed_title"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="Nhap title"
android:textAlignment="center"
android:textColor="#000"
android:textColorHint="@color/year"
/>

<EditText
android:id="@+id/ed_content"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="Nhap content"
android:textAlignment="center"
android:textColor="#000"
android:textColorHint="@color/year"
/>







<TextView
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:id="@+id/tv_ngay"
android:text="Choise Date"
android:textSize="18dp"
android:textColor="@color/year"

android:textAlignment="center"
android:layout_marginLeft="5dp"
android:layout_weight="0.04"
/>

<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal"
android:gravity="center"
>
<Button
android:id="@+id/btn_save"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="Save"

/>

</LinearLayout>
</LinearLayout>

context_menu.xml
<?xml version="1.0" encoding="utf-8"?>
<menu xmlns:android="http://schemas.android.com/apk/res/android">
<item
android:id="@+id/ct_sua"
android:title="Sua" />
<item
android:id="@+id/ct_xoa"

android:title="Xoa" />

</menu>

option_menu.xml
<?xml version="1.0" encoding="utf-8"?>
<menu
xmlns:app="http://schemas.android.com/apk/res-auto"
xmlns:android="http://schemas.android.com/apk/res/android">

<item
android:id="@+id/help"

android:title="Help"></item>
<item
android:id="@+id/update"
android:title="Check Update"></item>
<item
android:id="@+id/setting"
android:title="Setting"></item>
<item android:id="@+id/ct_search"
android:title="Add" app:showAsAction="always"
android:icon="@android:drawable/ic_menu_search"
></item>
<item android:id="@+id/ct_add"
android:title="Add" app:showAsAction="always"
android:icon="@android:drawable/ic_input_add"
></item>

</menu>

Chạy ứng dụng

admin

Xin chào, diendanhocweb.com là nơi mình sẽ chia sẻ những thủ thuật, kiến thức về website mà mình tìm hiểu được. Hy vọng, nó sẽ giúp ích cho mọi người.