[关闭]
@linux1s1s 2017-01-22T16:54:14.000000Z 字数 9819 阅读 2726

Android SQLite 封装初步一

AndroidSQL 2016-01


在完成封装SQLite之前,先了解一下SQLite的基本知识,在后续博客中会进一步完成SQLite封装。

POJO

直接看Demo,首先看POJO类

  1. public class Order {
  2. public int id;
  3. public String customName;
  4. public int orderPrice;
  5. public String country;
  6. public Order() {
  7. }
  8. public Order(int id, String customName, int orderPrice, String country) {
  9. this.id = id;
  10. this.customName = customName;
  11. this.orderPrice = orderPrice;
  12. this.country = country;
  13. }
  14. }

SQLiteHelper

紧接着定义一个SQLiteHelper类,这个类主要建数据库和建表用。

  1. public class OrderDBHelper extends SQLiteOpenHelper{
  2. private static final int DB_VERSION = 1;
  3. private static final String DB_NAME = "myTest.db";
  4. public static final String TABLE_NAME = "Orders";
  5. public OrderDBHelper(Context context) {
  6. super(context, DB_NAME, null, DB_VERSION);
  7. }
  8. @Override
  9. public void onCreate(SQLiteDatabase sqLiteDatabase) {
  10. // create table Orders(Id integer primary key, CustomName text, OrderPrice integer, Country text);
  11. String sql = "create table if not exists " + TABLE_NAME + " (Id integer primary key, CustomName text, OrderPrice integer, Country text)";
  12. sqLiteDatabase.execSQL(sql);
  13. }
  14. @Override
  15. public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) {
  16. String sql = "DROP TABLE IF EXISTS " + TABLE_NAME;
  17. sqLiteDatabase.execSQL(sql);
  18. onCreate(sqLiteDatabase);
  19. }
  20. }

DAO

然后建立DAO类,做数据转换和数据处理,这个类将数据库操作的所有对外方法封装了一层,对外公开的方法无外乎:

“增删查改”。对于“增删改”这类对表内容变换的操作,我们需先调用getWritableDatabase(),在执行的时候可以调用通用的execSQL(String sql)方法或对应的操作API:insert()、delete()、update()。而对“查”,需要调用getReadableDatabase(),这时就不能使用execSQL方法了,得使用query()或rawQuery()方法。

  1. public class OrderDao {
  2. private static final String TAG = "OrdersDao";
  3. // 列定义
  4. private final String[] ORDER_COLUMNS = new String[] {"Id", "CustomName","OrderPrice","Country"};
  5. private Context context;
  6. private OrderDBHelper ordersDBHelper;
  7. public OrderDao(Context context) {
  8. this.context = context;
  9. ordersDBHelper = new OrderDBHelper(context);
  10. }
  11. /**
  12. * 判断表中是否有数据
  13. */
  14. public boolean isDataExist(){
  15. int count = 0;
  16. SQLiteDatabase db = null;
  17. Cursor cursor = null;
  18. try {
  19. db = ordersDBHelper.getReadableDatabase();
  20. // select count(Id) from Orders
  21. cursor = db.query(OrderDBHelper.TABLE_NAME, new String[]{"COUNT(Id)"}, null, null, null, null, null);
  22. if (cursor.moveToFirst()) {
  23. count = cursor.getInt(0);
  24. }
  25. if (count > 0) return true;
  26. }
  27. catch (Exception e) {
  28. Log.e(TAG, "", e);
  29. }
  30. finally {
  31. if (cursor != null) {
  32. cursor.close();
  33. }
  34. if (db != null) {
  35. db.close();
  36. }
  37. }
  38. return false;
  39. }
  40. /**
  41. * 初始化数据
  42. */
  43. public void initTable(){
  44. SQLiteDatabase db = null;
  45. try {
  46. db = ordersDBHelper.getWritableDatabase();
  47. db.beginTransaction();
  48. db.execSQL("insert into " + OrderDBHelper.TABLE_NAME + " (Id, CustomName, OrderPrice, Country) values (1, 'Arc', 100, 'China')");
  49. db.execSQL("insert into " + OrderDBHelper.TABLE_NAME + " (Id, CustomName, OrderPrice, Country) values (2, 'Bor', 200, 'USA')");
  50. db.execSQL("insert into " + OrderDBHelper.TABLE_NAME + " (Id, CustomName, OrderPrice, Country) values (3, 'Cut', 500, 'Japan')");
  51. db.execSQL("insert into " + OrderDBHelper.TABLE_NAME + " (Id, CustomName, OrderPrice, Country) values (4, 'Bor', 300, 'USA')");
  52. db.execSQL("insert into " + OrderDBHelper.TABLE_NAME + " (Id, CustomName, OrderPrice, Country) values (5, 'Arc', 600, 'China')");
  53. db.execSQL("insert into " + OrderDBHelper.TABLE_NAME + " (Id, CustomName, OrderPrice, Country) values (6, 'Doom', 200, 'China')");
  54. db.setTransactionSuccessful();
  55. }catch (Exception e){
  56. Log.e(TAG, "", e);
  57. }finally {
  58. if (db != null) {
  59. db.endTransaction();
  60. db.close();
  61. }
  62. }
  63. }
  64. /**
  65. * 执行自定义SQL语句
  66. */
  67. public void execSQL(String sql) {
  68. SQLiteDatabase db = null;
  69. try {
  70. if (sql.contains("select")){
  71. Toast.makeText(context, R.string.strUnableSql, Toast.LENGTH_SHORT).show();
  72. }else if (sql.contains("insert") || sql.contains("update") || sql.contains("delete")){
  73. db = ordersDBHelper.getWritableDatabase();
  74. db.beginTransaction();
  75. db.execSQL(sql);
  76. db.setTransactionSuccessful();
  77. Toast.makeText(context, R.string.strSuccessSql, Toast.LENGTH_SHORT).show();
  78. }
  79. } catch (Exception e) {
  80. Toast.makeText(context, R.string.strErrorSql, Toast.LENGTH_SHORT).show();
  81. Log.e(TAG, "", e);
  82. } finally {
  83. if (db != null) {
  84. db.endTransaction();
  85. db.close();
  86. }
  87. }
  88. }
  89. /**
  90. * 查询数据库中所有数据
  91. */
  92. public List<Order> getAllDate(){
  93. SQLiteDatabase db = null;
  94. Cursor cursor = null;
  95. try {
  96. db = ordersDBHelper.getReadableDatabase();
  97. // select * from Orders where Id=1 (注意其中的空格和不能空格的地方)
  98. cursor = db.query(OrderDBHelper.TABLE_NAME, ORDER_COLUMNS, null, null, null, null, null);
  99. if (cursor.getCount() > 0) {
  100. List<Order> orderList = new ArrayList<Order>(cursor.getCount());
  101. while (cursor.moveToNext()) {
  102. orderList.add(parseOrder(cursor));
  103. }
  104. return orderList;
  105. }
  106. }
  107. catch (Exception e) {
  108. Log.e(TAG, "", e);
  109. }
  110. finally {
  111. if (cursor != null) {
  112. cursor.close();
  113. }
  114. if (db != null) {
  115. db.close();
  116. }
  117. }
  118. return null;
  119. }
  120. /**
  121. * 新增一条数据
  122. */
  123. public boolean insertDate(){
  124. SQLiteDatabase db = null;
  125. try {
  126. db = ordersDBHelper.getWritableDatabase();
  127. db.beginTransaction();
  128. // insert into Orders(Id,CustomName,OrderPrice,Country)values(7,"Jne",700,"China");
  129. ContentValues contentValues = new ContentValues();
  130. contentValues.put("Id", 7);
  131. contentValues.put("CustomName", "Jne");
  132. contentValues.put("OrderPrice", 700);
  133. contentValues.put("Country", "China");
  134. db.insertOrThrow(OrderDBHelper.TABLE_NAME, null, contentValues);
  135. db.setTransactionSuccessful();
  136. return true;
  137. }catch (SQLiteConstraintException e){
  138. Toast.makeText(context, "主键重复", Toast.LENGTH_SHORT).show();
  139. }catch (Exception e){
  140. Log.e(TAG, "", e);
  141. }finally {
  142. if (db != null) {
  143. db.endTransaction();
  144. db.close();
  145. }
  146. }
  147. return false;
  148. }
  149. /**
  150. * 删除一条数据 此处删除Id为7的数据
  151. delete from Orders where Id=7 (注意空格和非空格)
  152. */
  153. public boolean deleteOrder() {
  154. SQLiteDatabase db = null;
  155. try {
  156. db = ordersDBHelper.getWritableDatabase();
  157. db.beginTransaction();
  158. // delete from Orders where Id = 7
  159. db.delete(OrderDBHelper.TABLE_NAME, "Id = ?", new String[]{String.valueOf(7)});
  160. db.setTransactionSuccessful();
  161. return true;
  162. } catch (Exception e) {
  163. Log.e(TAG, "", e);
  164. } finally {
  165. if (db != null) {
  166. db.endTransaction();
  167. db.close();
  168. }
  169. }
  170. return false;
  171. }
  172. /**
  173. * 修改一条数据 此处将Id为6的数据的OrderPrice修改了800
  174. update Orders set OrderPrice=800 where Id=6
  175. */
  176. public boolean updateOrder(){
  177. SQLiteDatabase db = null;
  178. try {
  179. db = ordersDBHelper.getWritableDatabase();
  180. db.beginTransaction();
  181. ContentValues cv = new ContentValues();
  182. cv.put("OrderPrice", 800);
  183. db.update(OrderDBHelper.TABLE_NAME,
  184. cv,
  185. "Id = ?",
  186. new String[]{String.valueOf(6)});
  187. db.setTransactionSuccessful();
  188. return true;
  189. }
  190. catch (Exception e) {
  191. Log.e(TAG, "", e);
  192. }
  193. finally {
  194. if (db != null) {
  195. db.endTransaction();
  196. db.close();
  197. }
  198. }
  199. return false;
  200. }
  201. /**
  202. * 数据查询 此处将用户名为"Bor"的信息提取出来
  203. select from Oreders where CustomName='Bor'
  204. */
  205. public List<Order> getBorOrder(){
  206. SQLiteDatabase db = null;
  207. Cursor cursor = null;
  208. try {
  209. db = ordersDBHelper.getReadableDatabase();
  210. cursor = db.query(OrderDBHelper.TABLE_NAME,
  211. ORDER_COLUMNS,
  212. "CustomName = ?",
  213. new String[] {"Bor"},
  214. null, null, null);
  215. if (cursor.getCount() > 0) {
  216. List<Order> orderList = new ArrayList<Order>(cursor.getCount());
  217. while (cursor.moveToNext()) {
  218. Order order = parseOrder(cursor);
  219. orderList.add(order);
  220. }
  221. return orderList;
  222. }
  223. }
  224. catch (Exception e) {
  225. Log.e(TAG, "", e);
  226. }
  227. finally {
  228. if (cursor != null) {
  229. cursor.close();
  230. }
  231. if (db != null) {
  232. db.close();
  233. }
  234. }
  235. return null;
  236. }
  237. /**
  238. * 统计查询 此处查询Country为China的用户总数
  239. select count(Id) from Orders where Country='China'
  240. */
  241. public int getChinaCount(){
  242. int count = 0;
  243. SQLiteDatabase db = null;
  244. Cursor cursor = null;
  245. try {
  246. db = ordersDBHelper.getReadableDatabase();
  247. cursor = db.query(OrderDBHelper.TABLE_NAME,
  248. new String[]{"COUNT(Id)"},
  249. "Country = ?",
  250. new String[] {"China"},
  251. null, null, null);
  252. if (cursor.moveToFirst()) {
  253. count = cursor.getInt(0);
  254. }
  255. }
  256. catch (Exception e) {
  257. Log.e(TAG, "", e);
  258. }
  259. finally {
  260. if (cursor != null) {
  261. cursor.close();
  262. }
  263. if (db != null) {
  264. db.close();
  265. }
  266. }
  267. return count;
  268. }
  269. /**
  270. * 比较查询 此处查询单笔数据中OrderPrice最高的
  271. select Id, CustomName, Max(OrderPrice) as OrderPrice, Country from Orders
  272. */
  273. public Order getMaxOrderPrice(){
  274. SQLiteDatabase db = null;
  275. Cursor cursor = null;
  276. try {
  277. db = ordersDBHelper.getReadableDatabase();
  278. cursor = db.query(OrderDBHelper.TABLE_NAME, new String[]{"Id", "CustomName", "Max(OrderPrice) as OrderPrice", "Country"}, null, null, null, null, null);
  279. if (cursor.getCount() > 0){
  280. if (cursor.moveToFirst()) {
  281. return parseOrder(cursor);
  282. }
  283. }
  284. }
  285. catch (Exception e) {
  286. Log.e(TAG, "", e);
  287. }
  288. finally {
  289. if (cursor != null) {
  290. cursor.close();
  291. }
  292. if (db != null) {
  293. db.close();
  294. }
  295. }
  296. return null;
  297. }
  298. /**
  299. * 将查找到的数据转换成Order类
  300. */
  301. private Order parseOrder(Cursor cursor){
  302. Order order = new Order();
  303. order.id = (cursor.getInt(cursor.getColumnIndex("Id")));
  304. order.customName = (cursor.getString(cursor.getColumnIndex("CustomName")));
  305. order.orderPrice = (cursor.getInt(cursor.getColumnIndex("OrderPrice")));
  306. order.country = (cursor.getString(cursor.getColumnIndex("Country")));
  307. return order;
  308. }
  309. }

TestCase

最后用个简单的Activity测试一下上面的DAO,界面的初始化操作,这里就省略了,给出单击回调的测试代码

  1. public class SQLBtnOnclickListener implements View.OnClickListener{
  2. @Override
  3. public void onClick(View view) {
  4. switch (view.getId()){
  5. case R.id.executeButton:
  6. showSQLMsg.setVisibility(View.GONE);
  7. String sql = inputSqlMsg.getText().toString();
  8. if (! TextUtils.isEmpty(sql)){
  9. ordersDao.execSQL(sql);
  10. }else {
  11. Toast.makeText(OrderActivity.this, R.string.strInputSql, Toast.LENGTH_SHORT).show();
  12. }
  13. refreshOrderList();
  14. break;
  15. case R.id.insertButton:
  16. showSQLMsg.setVisibility(View.VISIBLE);
  17. showSQLMsg.setText("新增一条数据:\n添加数据(7, \"Jne\", 700, \"China\")\ninsert into Orders(Id, CustomName, OrderPrice, Country) values (7, \"Jne\", 700, \"China\")");
  18. ordersDao.insertDate();
  19. refreshOrderList();
  20. break;
  21. case R.id.deleteButton:
  22. showSQLMsg.setVisibility(View.VISIBLE);
  23. showSQLMsg.setText("删除一条数据:\n删除Id为7的数据\ndelete from Orders where Id = 7");
  24. ordersDao.deleteOrder();
  25. refreshOrderList();
  26. break;
  27. case R.id.updateButton:
  28. showSQLMsg.setVisibility(View.VISIBLE);
  29. showSQLMsg.setText("修改一条数据:\n将Id为6的数据的OrderPrice修改了800\nupdate Orders set OrderPrice = 800 where Id = 6");
  30. ordersDao.updateOrder();
  31. refreshOrderList();
  32. break;
  33. case R.id.query1Button:
  34. showSQLMsg.setVisibility(View.VISIBLE);
  35. StringBuilder msg = new StringBuilder();
  36. msg.append("数据查询:\n此处将用户名为\"Bor\"的信息提取出来\nselect * from Orders where CustomName = 'Bor'");
  37. List<Order> borOrders = ordersDao.getBorOrder();
  38. for (Order order : borOrders){
  39. msg.append("\n(" + order.id + ", " + order.customName + ", " + order.orderPrice + ", " + order.country + ")");
  40. }
  41. showSQLMsg.setText(msg);
  42. break;
  43. case R.id.query2Button:
  44. showSQLMsg.setVisibility(View.VISIBLE);
  45. int chinaCount = ordersDao.getChinaCount();
  46. showSQLMsg.setText("统计查询:\n此处查询Country为China的用户总数\nselect count(Id) from Orders where Country = 'China'\ncount = " + chinaCount);
  47. break;
  48. case R.id.query3Button:
  49. showSQLMsg.setVisibility(View.VISIBLE);
  50. StringBuilder msg2 = new StringBuilder();
  51. msg2.append("比较查询:\n此处查询单笔数据中OrderPrice最高的\nselect Id, CustomName, Max(OrderPrice) as OrderPrice, Country from Orders");
  52. Order order = ordersDao.getMaxOrderPrice();
  53. msg2.append("\n(" + order.id + ", " + order.customName + ", " + order.orderPrice + ", " + order.country + ")");
  54. showSQLMsg.setText(msg2);
  55. break;
  56. default:
  57. break;
  58. }
  59. }
  60. }

TIPS 本文转载和修改自Android SQLite详解

添加新批注
在作者公开此批注前,只有你和作者可见。
回复批注