Cơ sở dữ liệu trong Android phần 2
Sử dụng SQLiteOpenHelper để quản lý phiên bản DB, xây dựng CRUD tập trung cho ứng dụng Android
SQLiteOpenHelper trong Android
Mặc dù bạn vẫn có thể sử dụng cách kết nối, truy vấn dữ liệu như ở ví dụ trước Sử dụng SQLite trong Android, tuy nhiên với lớp SQLiteOpenHelper giúp việc tập trung hoá code, quản lý – tạo / nâng cấp DB rõ ràng hơn.
Bạn chỉ cần triển khai lớp SQLiteOpenHelper và viết các lệnh tập trung CRUD trên các bảng dữ liệu quản lý. Trong đối tượng triển khai SQLiteOpenHelper nếu muốn có SQLiteDatabase thì gọi các phương thức:
|
1 2 3 4 5 |
<span class="com">//Khi cần đọc dữ liệu (SELECT)</span> <span class="typ">SQLiteDatabase</span><span class="pln"> db </span><span class="pun">=</span><span class="pln"> getReadableDatabase</span><span class="pun">();</span> <span class="com">//Khi cần cập nhật dữ liệu (CREATE, DELETE, UPDATE, INSERT)</span> <span class="typ">SQLiteDatabase</span><span class="pln"> db </span><span class="pun">=</span><span class="pln"> getWritableDatabase</span><span class="pun">();</span> |
Triển khai tối thiểu SQLiteOpenHelper tham khảo đoạn code sau:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
<span class="kwd">class</span> <span class="typ">MyDBHelper</span> <span class="kwd">extends</span> <span class="typ">SQLiteOpenHelper</span> <span class="pun">{</span> <span class="kwd">private</span> <span class="kwd">static</span> <span class="kwd">final</span> <span class="typ">String</span><span class="pln"> DATABASE_NAME </span><span class="pun">=</span> <span class="str">"dbname.db"</span><span class="pun">;</span> <span class="kwd">private</span> <span class="kwd">static</span> <span class="kwd">final</span> <span class="kwd">int</span><span class="pln"> DATABASE_VERSION </span><span class="pun">=</span> <span class="lit">1</span><span class="pun">;</span> <span class="kwd">public</span> <span class="typ">MyDBHelper</span><span class="pun">(</span><span class="typ">Context</span><span class="pln"> context</span><span class="pun">)</span> <span class="pun">{</span> <span class="kwd">super</span><span class="pun">(</span><span class="pln">context</span><span class="pun">,</span><span class="pln"> DATABASE_NAME</span><span class="pun">,</span> <span class="kwd">null</span><span class="pun">,</span><span class="pln"> DATABASE_VERSION</span><span class="pun">);</span> <span class="pun">}</span> <span class="lit">@Override</span> <span class="kwd">public</span> <span class="kwd">void</span><span class="pln"> onCreate</span><span class="pun">(</span><span class="typ">SQLiteDatabase</span><span class="pln"> db</span><span class="pun">)</span> <span class="pun">{</span> <span class="com">// Phương thức này tự động gọi nếu storage chưa có DATABASE_NAME</span> <span class="com">// Ban viết code để tạo DB</span> <span class="pun">}</span> <span class="lit">@Override</span> <span class="kwd">public</span> <span class="kwd">void</span><span class="pln"> onUpgrade</span><span class="pun">(</span><span class="typ">SQLiteDatabase</span><span class="pln"> db</span><span class="pun">,</span> <span class="kwd">int</span><span class="pln"> oldVersion</span><span class="pun">,</span> <span class="kwd">int</span><span class="pln"> newVersion</span><span class="pun">)</span> <span class="pun">{</span> <span class="com">//Phương thức này tự động gọi khi đã có DB trên Storage, nhưng phiên bản khác</span> <span class="com">//với DATABASE_VERSION</span> <span class="com">//Bạn cần thực hiện các thao tác để nâng cấp phiên bản</span> <span class="pun">}</span> <span class="kwd">void</span><span class="pln"> doSomething</span><span class="pun">()</span> <span class="pun">{</span> <span class="typ">SQLiteDatabase</span><span class="pln"> db </span><span class="pun">=</span><span class="pln"> getWritableDatabase</span><span class="pun">();</span> <span class="com">//...</span> <span class="pun">}</span> <span class="pun">}</span> |
Ví dụ SQLiteOpenHelper
Tiến hành cải tiến ví dụ phần trước, Sử dụng SQLite trong Android, mọi thao tác với DB sẽ tập trung ở lớp triển khai SQLiteOpenHelper có tên ProductDbHelper, nôi dung như sau:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 |
<span class="kwd">package</span><span class="pln"> net</span><span class="pun">.</span><span class="pln">xuanthulab</span><span class="pun">.</span><span class="pln">sqlitetutorial</span><span class="pun">;</span> <span class="kwd">import</span><span class="pln"> android</span><span class="pun">.</span><span class="pln">content</span><span class="pun">.</span><span class="typ">Context</span><span class="pun">;</span> <span class="kwd">import</span><span class="pln"> android</span><span class="pun">.</span><span class="pln">database</span><span class="pun">.</span><span class="typ">Cursor</span><span class="pun">;</span> <span class="kwd">import</span><span class="pln"> android</span><span class="pun">.</span><span class="pln">database</span><span class="pun">.</span><span class="pln">sqlite</span><span class="pun">.</span><span class="typ">SQLiteDatabase</span><span class="pun">;</span> <span class="kwd">import</span><span class="pln"> android</span><span class="pun">.</span><span class="pln">database</span><span class="pun">.</span><span class="pln">sqlite</span><span class="pun">.</span><span class="typ">SQLiteOpenHelper</span><span class="pun">;</span> <span class="kwd">import</span><span class="pln"> android</span><span class="pun">.</span><span class="pln">support</span><span class="pun">.</span><span class="pln">annotation</span><span class="pun">.</span><span class="typ">Nullable</span><span class="pun">;</span> <span class="kwd">import</span><span class="pln"> android</span><span class="pun">.</span><span class="pln">util</span><span class="pun">.</span><span class="typ">Log</span><span class="pun">;</span> <span class="kwd">import</span><span class="pln"> java</span><span class="pun">.</span><span class="pln">util</span><span class="pun">.</span><span class="typ">ArrayList</span><span class="pun">;</span> <span class="kwd">import</span><span class="pln"> java</span><span class="pun">.</span><span class="pln">util</span><span class="pun">.</span><span class="typ">List</span><span class="pun">;</span> <span class="kwd">public</span> <span class="kwd">class</span> <span class="typ">ProductDbHelper</span> <span class="kwd">extends</span> <span class="typ">SQLiteOpenHelper</span> <span class="pun">{</span> <span class="kwd">private</span> <span class="kwd">static</span> <span class="kwd">final</span> <span class="typ">String</span><span class="pln"> TAG </span><span class="pun">=</span> <span class="str">"ProductDbHelper"</span><span class="pun">;</span> <span class="kwd">private</span> <span class="kwd">static</span> <span class="kwd">final</span> <span class="typ">String</span><span class="pln"> DATABASE_NAME </span><span class="pun">=</span> <span class="str">"myproduct.db"</span><span class="pun">;</span> <span class="kwd">private</span> <span class="kwd">static</span> <span class="kwd">final</span> <span class="kwd">int</span><span class="pln"> DATABASE_VERSION </span><span class="pun">=</span> <span class="lit">1</span><span class="pun">;</span> <span class="kwd">private</span> <span class="kwd">static</span> <span class="kwd">final</span> <span class="typ">String</span><span class="pln"> TABLE_PRODUCT </span><span class="pun">=</span> <span class="str">"product"</span><span class="pun">;</span> <span class="kwd">public</span> <span class="typ">ProductDbHelper</span><span class="pun">(</span><span class="lit">@Nullable</span> <span class="typ">Context</span><span class="pln"> context</span><span class="pun">)</span> <span class="pun">{</span> <span class="kwd">super</span><span class="pun">(</span><span class="pln">context</span><span class="pun">,</span><span class="pln"> DATABASE_NAME</span><span class="pun">,</span> <span class="kwd">null</span><span class="pun">,</span><span class="pln"> DATABASE_VERSION</span><span class="pun">);</span> <span class="pun">}</span> <span class="com">// Phương thức này tự động gọi nếu storage chưa có DATABASE_NAME</span> <span class="lit">@Override</span> <span class="kwd">public</span> <span class="kwd">void</span><span class="pln"> onCreate</span><span class="pun">(</span><span class="typ">SQLiteDatabase</span><span class="pln"> db</span><span class="pun">)</span> <span class="pun">{</span> <span class="typ">Log</span><span class="pun">.</span><span class="pln">i</span><span class="pun">(</span><span class="pln">TAG</span><span class="pun">,</span> <span class="str">"Create table"</span><span class="pun">);</span> <span class="typ">String</span><span class="pln"> queryCreateTable </span><span class="pun">=</span> <span class="str">"CREATE TABLE "</span> <span class="pun">+</span><span class="pln"> TABLE_PRODUCT </span><span class="pun">+</span> <span class="str">" ( "</span> <span class="pun">+</span> <span class="str">"id INTEGER PRIMARY KEY AUTOINCREMENT, "</span> <span class="pun">+</span> <span class="str">"name VARCHAR (255) NOT NULL, "</span> <span class="pun">+</span> <span class="str">"price DECIMAL DEFAULT (0)"</span> <span class="pun">+</span> <span class="str">")"</span><span class="pun">;</span><span class="pln"> db</span><span class="pun">.</span><span class="pln">execSQL</span><span class="pun">(</span><span class="pln">queryCreateTable</span><span class="pun">);</span> <span class="pun">}</span> <span class="com">//Phương thức này tự động gọi khi đã có DB trên Storage, nhưng phiên bản khác</span> <span class="com">//với DATABASE_VERSION</span> <span class="lit">@Override</span> <span class="kwd">public</span> <span class="kwd">void</span><span class="pln"> onUpgrade</span><span class="pun">(</span><span class="typ">SQLiteDatabase</span><span class="pln"> db</span><span class="pun">,</span> <span class="kwd">int</span><span class="pln"> oldVersion</span><span class="pun">,</span> <span class="kwd">int</span><span class="pln"> newVersion</span><span class="pun">)</span> <span class="pun">{</span> <span class="com">//Xoá bảng cũ</span><span class="pln"> db</span><span class="pun">.</span><span class="pln">execSQL</span><span class="pun">(</span><span class="str">"DROP TABLE IF EXISTS "</span> <span class="pun">+</span><span class="pln"> TABLE_PRODUCT</span><span class="pun">);</span> <span class="com">//Tiến hành tạo bảng mới</span><span class="pln"> onCreate</span><span class="pun">(</span><span class="pln">db</span><span class="pun">);</span> <span class="pun">}</span> <span class="com">//Lấy toàn bộ SP</span> <span class="kwd">public</span> <span class="typ">List</span><span class="pun"><</span><span class="typ">Product</span><span class="pun">></span><span class="pln"> getAllProducts</span><span class="pun">()</span> <span class="pun">{</span> <span class="typ">List</span><span class="pun"><</span><span class="typ">Product</span><span class="pun">></span><span class="pln"> products </span><span class="pun">=</span> <span class="kwd">new</span> <span class="typ">ArrayList</span><span class="pun"><>();</span> <span class="typ">SQLiteDatabase</span><span class="pln"> db </span><span class="pun">=</span><span class="pln"> getReadableDatabase</span><span class="pun">();</span> <span class="typ">Cursor</span><span class="pln"> cursor </span><span class="pun">=</span><span class="pln"> db</span><span class="pun">.</span><span class="pln">rawQuery</span><span class="pun">(</span><span class="str">"SELECT id, name, price from product"</span><span class="pun">,</span> <span class="kwd">null</span><span class="pun">);</span> <span class="com">//Đến dòng đầu của tập dữ liệu</span><span class="pln"> cursor</span><span class="pun">.</span><span class="pln">moveToFirst</span><span class="pun">();</span> <span class="kwd">while</span> <span class="pun">(!</span><span class="pln">cursor</span><span class="pun">.</span><span class="pln">isAfterLast</span><span class="pun">())</span> <span class="pun">{</span> <span class="kwd">int</span><span class="pln"> productID </span><span class="pun">=</span><span class="pln"> cursor</span><span class="pun">.</span><span class="pln">getInt</span><span class="pun">(</span><span class="lit">0</span><span class="pun">);</span> <span class="typ">String</span><span class="pln"> productName </span><span class="pun">=</span><span class="pln"> cursor</span><span class="pun">.</span><span class="pln">getString</span><span class="pun">(</span><span class="lit">1</span><span class="pun">);</span> <span class="kwd">int</span><span class="pln"> productPrice </span><span class="pun">=</span><span class="pln"> cursor</span><span class="pun">.</span><span class="pln">getInt</span><span class="pun">(</span><span class="lit">2</span><span class="pun">);</span><span class="pln"> products</span><span class="pun">.</span><span class="pln">add</span><span class="pun">(</span><span class="kwd">new</span> <span class="typ">Product</span><span class="pun">(</span><span class="pln">productID</span><span class="pun">,</span><span class="pln"> productName</span><span class="pun">,</span><span class="pln"> productPrice</span><span class="pun">));</span><span class="pln"> cursor</span><span class="pun">.</span><span class="pln">moveToNext</span><span class="pun">();</span> <span class="pun">}</span><span class="pln"> cursor</span><span class="pun">.</span><span class="pln">close</span><span class="pun">();</span> <span class="kwd">return</span><span class="pln"> products</span><span class="pun">;</span> <span class="pun">}</span> <span class="com">//Lấy một SP biết ID</span> <span class="kwd">public</span> <span class="typ">Product</span><span class="pln"> getProductByID</span><span class="pun">(</span><span class="kwd">int</span><span class="pln"> ID</span><span class="pun">)</span> <span class="pun">{</span> <span class="typ">Product</span><span class="pln"> product </span><span class="pun">=</span> <span class="kwd">null</span><span class="pun">;</span> <span class="typ">SQLiteDatabase</span><span class="pln"> db </span><span class="pun">=</span><span class="pln"> getReadableDatabase</span><span class="pun">();</span> <span class="typ">Cursor</span><span class="pln"> cursor </span><span class="pun">=</span><span class="pln"> db</span><span class="pun">.</span><span class="pln">rawQuery</span><span class="pun">(</span><span class="str">"SELECT id, name, price from product where id = ?"</span><span class="pun">,</span> <span class="kwd">new</span> <span class="typ">String</span><span class="pun">[]{</span><span class="pln">ID </span><span class="pun">+</span> <span class="str">""</span><span class="pun">});</span> <span class="kwd">if</span> <span class="pun">(</span><span class="pln">cursor</span><span class="pun">.</span><span class="pln">getCount</span><span class="pun">()</span> <span class="pun">></span> <span class="lit">0</span><span class="pun">)</span> <span class="pun">{</span><span class="pln"> cursor</span><span class="pun">.</span><span class="pln">moveToFirst</span><span class="pun">();</span> <span class="kwd">int</span><span class="pln"> productID </span><span class="pun">=</span><span class="pln"> cursor</span><span class="pun">.</span><span class="pln">getInt</span><span class="pun">(</span><span class="lit">0</span><span class="pun">);</span> <span class="typ">String</span><span class="pln"> productName </span><span class="pun">=</span><span class="pln"> cursor</span><span class="pun">.</span><span class="pln">getString</span><span class="pun">(</span><span class="lit">1</span><span class="pun">);</span> <span class="kwd">int</span><span class="pln"> productPrice </span><span class="pun">=</span><span class="pln"> cursor</span><span class="pun">.</span><span class="pln">getInt</span><span class="pun">(</span><span class="lit">2</span><span class="pun">);</span><span class="pln"> product </span><span class="pun">=</span> <span class="kwd">new</span> <span class="typ">Product</span><span class="pun">(</span><span class="pln">productID</span><span class="pun">,</span><span class="pln"> productName</span><span class="pun">,</span><span class="pln"> productPrice</span><span class="pun">);</span> <span class="pun">}</span><span class="pln"> cursor</span><span class="pun">.</span><span class="pln">close</span><span class="pun">();</span> <span class="kwd">return</span><span class="pln"> product</span><span class="pun">;</span> <span class="pun">}</span> <span class="com">//Cập nhật</span> <span class="kwd">void</span><span class="pln"> updateProduct</span><span class="pun">(</span><span class="typ">Product</span><span class="pln"> product</span><span class="pun">)</span> <span class="pun">{</span> <span class="typ">SQLiteDatabase</span><span class="pln"> db </span><span class="pun">=</span><span class="pln"> getWritableDatabase</span><span class="pun">();</span><span class="pln"> db</span><span class="pun">.</span><span class="pln">execSQL</span><span class="pun">(</span><span class="str">"UPDATE product SET name=?, price = ? where id = ?"</span><span class="pun">,</span> <span class="kwd">new</span> <span class="typ">String</span><span class="pun">[]{</span><span class="pln">product</span><span class="pun">.</span><span class="pln">name</span><span class="pun">,</span><span class="pln"> product</span><span class="pun">.</span><span class="pln">price </span><span class="pun">+</span> <span class="str">""</span><span class="pun">,</span><span class="pln"> product</span><span class="pun">.</span><span class="pln">productID </span><span class="pun">+</span> <span class="str">""</span><span class="pun">});</span> <span class="pun">}</span> <span class="com">//Chèn mới</span> <span class="kwd">void</span><span class="pln"> insertProduct</span><span class="pun">(</span><span class="typ">Product</span><span class="pln"> product</span><span class="pun">)</span> <span class="pun">{</span> <span class="typ">SQLiteDatabase</span><span class="pln"> db </span><span class="pun">=</span><span class="pln"> getWritableDatabase</span><span class="pun">();</span><span class="pln"> db</span><span class="pun">.</span><span class="pln">execSQL</span><span class="pun">(</span><span class="str">"INSERT INTO product (name, price ) VALUES (?,?)"</span><span class="pun">,</span> <span class="kwd">new</span> <span class="typ">String</span><span class="pun">[]{</span><span class="pln">product</span><span class="pun">.</span><span class="pln">name</span><span class="pun">,</span><span class="pln"> product</span><span class="pun">.</span><span class="pln">price </span><span class="pun">+</span> <span class="str">""</span><span class="pun">});</span> <span class="pun">}</span> <span class="com">//Xoá sản phẩm khỏi DB</span> <span class="kwd">void</span><span class="pln"> deleteProductByID</span><span class="pun">(</span><span class="kwd">int</span> <span class="typ">ProductID</span><span class="pun">)</span> <span class="pun">{</span> <span class="typ">SQLiteDatabase</span><span class="pln"> db </span><span class="pun">=</span><span class="pln"> getWritableDatabase</span><span class="pun">();</span><span class="pln"> db</span><span class="pun">.</span><span class="pln">execSQL</span><span class="pun">(</span><span class="str">"DELETE FROM product where id = ?"</span><span class="pun">,</span> <span class="kwd">new</span> <span class="typ">String</span><span class="pun">[]{</span><span class="typ">String</span><span class="pun">.</span><span class="pln">valueOf</span><span class="pun">(</span><span class="typ">ProductID</span><span class="pun">)});</span> <span class="pun">}</span> <span class="pun">}</span> |
Khi áp dụng, trong các Activity tạo ra đối tượng dạng:
|
1 |
<span class="pln">productDbHelper </span><span class="pun">=</span> <span class="kwd">new</span> <span class="typ">ProductDbHelper</span><span class="pun">(</span><span class="kwd">this</span><span class="pun">);</span> |
Ví dụ khi nạp các sản phẩm trong MainActivity, phiên bản loadDbProduct giờ trở thành
|
1 2 3 4 |
<span class="kwd">private</span> <span class="kwd">void</span><span class="pln"> loadDbProduct</span><span class="pun">()</span> <span class="pun">{</span><span class="pln"> listProduct</span><span class="pun">.</span><span class="pln">clear</span><span class="pun">();</span><span class="pln"> listProduct</span><span class="pun">.</span><span class="pln">addAll</span><span class="pun">(</span><span class="pln">productDbHelper</span><span class="pun">.</span><span class="pln">getAllProducts</span><span class="pun">());</span> <span class="pun">}</span> |
