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

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;
  7.     public Order() {
  8.     }
  10.     public Order(int id, String customName, int orderPrice, String country) {
  11.         this.id = id;
  12.         this.customName = customName;
  13.         this.orderPrice = orderPrice;
  14.         this.country = country;
  15.     }
  16. }

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";
  6.     public OrderDBHelper(Context context) {
  7.         super(context, DB_NAME, null, DB_VERSION);
  8.     }
  10.     @Override
  11.     public void onCreate(SQLiteDatabase sqLiteDatabase) {
  12.         // create table Orders(Id integer primary key, CustomName text, OrderPrice integer, Country text);
  13.         String sql = "create table if not exists " + TABLE_NAME + " (Id integer primary key, CustomName text, OrderPrice integer, Country text)";
  14.         sqLiteDatabase.execSQL(sql);
  15.     }
  17.     @Override
  18.     public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) {
  19.         String sql = "DROP TABLE IF EXISTS " + TABLE_NAME;
  20.         sqLiteDatabase.execSQL(sql);
  21.         onCreate(sqLiteDatabase);
  22.     }
  23. }

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

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.                 }
  14.                 refreshOrderList();
  15.                 break;
  17.             case R.id.insertButton:
  18.                 showSQLMsg.setVisibility(View.VISIBLE);
  19.                 showSQLMsg.setText("新增一条数据：\n添加数据(7, \"Jne\", 700, \"China\")\ninsert into Orders(Id, CustomName, OrderPrice, Country) values (7, \"Jne\", 700, \"China\")");
  20.                 ordersDao.insertDate();
  21.                 refreshOrderList();
  22.                 break;
  24.             case R.id.deleteButton:
  25.                 showSQLMsg.setVisibility(View.VISIBLE);
  26.                 showSQLMsg.setText("删除一条数据：\n删除Id为7的数据\ndelete from Orders where Id = 7");
  27.                 ordersDao.deleteOrder();
  28.                 refreshOrderList();
  29.                 break;
  31.             case R.id.updateButton:
  32.                 showSQLMsg.setVisibility(View.VISIBLE);
  33.                 showSQLMsg.setText("修改一条数据：\n将Id为6的数据的OrderPrice修改了800\nupdate Orders set OrderPrice = 800 where Id = 6");
  34.                 ordersDao.updateOrder();
  35.                 refreshOrderList();
  36.                 break;
  38.             case R.id.query1Button:
  39.                 showSQLMsg.setVisibility(View.VISIBLE);
  40.                 StringBuilder msg = new StringBuilder();
  41.                 msg.append("数据查询：\n此处将用户名为\"Bor\"的信息提取出来\nselect * from Orders where CustomName = 'Bor'");
  42.                 List<Order> borOrders = ordersDao.getBorOrder();
  43.                 for (Order order : borOrders){
  44.                     msg.append("\n(" + order.id + ", " + order.customName + ", " + order.orderPrice + ", " + order.country + ")");
  45.                 }
  46.                 showSQLMsg.setText(msg);
  47.                 break;
  49.             case R.id.query2Button:
  50.                 showSQLMsg.setVisibility(View.VISIBLE);
  51.                 int chinaCount = ordersDao.getChinaCount();
  52.                 showSQLMsg.setText("统计查询：\n此处查询Country为China的用户总数\nselect count(Id) from Orders where Country = 'China'\ncount = " + chinaCount);
  53.                 break;
  55.             case R.id.query3Button:
  56.                 showSQLMsg.setVisibility(View.VISIBLE);
  57.                 StringBuilder msg2 = new StringBuilder();
  58.                 msg2.append("比较查询：\n此处查询单笔数据中OrderPrice最高的\nselect Id, CustomName, Max(OrderPrice) as OrderPrice, Country from Orders");
  59.                 Order order = ordersDao.getMaxOrderPrice();
  60.                 msg2.append("\n(" + order.id + ", " + order.customName + ", " + order.orderPrice + ", " + order.country + ")");
  61.                 showSQLMsg.setText(msg2);
  62.                 break;
  64.             default:
  65.                 break;
  66.         }
  67.     }
  68. }

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

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