安卓开发_数据存储技术_sqlite

 1  2 1、轻量级
 3 SQLite和C\S模式的数据库软件不同，它是进程内的数据库引擎，因此不存在数据库的客户端和服务器。使用SQLite一般只需要带上它的一个动态库，就可以享受它的全部功能。而且那个动态库的尺寸也相当小。
 4 2、独立性
 5 SQLite数据库的核心引擎本身不依赖第三方软件，使用它也不需要“安装”，所以在使用的时候能够省去不少麻烦。
 6 3、隔离性
 7 SQLite数据库中的所有信息（比如表、视图、触发器）都包含在一个文件内，方便管理和维护。
 8 4、跨平台
 9 SQLite数据库支持大部分操作系统，除了我们在电脑上使用的操作系统之外，很多手机操作系统同样可以运行，比如Android、Windows Mobile、Symbian、Palm等。
10 5、多语言接口
11 SQLite数据库支持很多语言编程接口，比如C\C++、Java、Python、dotNet、Ruby、Perl等，得到更多开发者的喜爱。
12 6、安全性
13 SQLite数据库通过数据库级上的独占性和共享锁来实现独立事务处理。这意味着多个进程可以在同一时间从同一数据库读取数据，但只有一个可以写入数据。在某个进程或线程向数据库执行写操作之前，必须获得独占锁定。在发出独占锁定后，其他的读或写操作将不会再发生。

 1 public long insert (String table, String nullColumnHack, ContentValues values) 
 2 Added in API level 1
 3 Convenience method for inserting a row into the database.
 4 
 5 Parameters
 6 table  the table to insert the row into 
 7 nullColumnHack  optional; may be null. SQL doesn't allow inserting a completely empty row without naming at least one column name. If your provided values is empty, no column names are known and an empty row can't be inserted. If not set to null, the nullColumnHack parameter provides the name of nullable column name to explicitly insert a NULL into in the case where your values is empty. 
 8 values  this map contains the initial column values for the row. The keys should be the column names and the values the column values 
 9 
10 Returns
11 the row ID of the newly inserted row, or -1 if an error occurred 

 1 public int delete (String table, String whereClause, String[] whereArgs) 
 2 Added in API level 1
 3 Convenience method for deleting rows in the database.
 4 
 5 Parameters
 6 table  the table to delete from 
 7 whereClause  the optional WHERE clause to apply when deleting. Passing null will delete all rows. 
 8 whereArgs  You may include ?s in the where clause, which will be replaced by the values from whereArgs. The values will be bound as Strings. 
 9 
10 Returns
11 the number of rows affected if a whereClause is passed in, 0 otherwise. To remove all rows and get a count pass "1" as the whereClause. 

 1 public int update (String table, ContentValues values, String whereClause, String[] whereArgs) 
 2 Added in API level 1
 3 Convenience method for updating rows in the database.
 4 
 5 Parameters
 6 table  the table to update in 
 7 values  a map from column names to new column values. null is a valid value that will be translated to NULL. 
 8 whereClause  the optional WHERE clause to apply when updating. Passing null will update all rows. 
 9 whereArgs  You may include ?s in the where clause, which will be replaced by the values from whereArgs. The values will be bound as Strings. 
10 
11 Returns
12 the number of rows affected 

 1 public Cursor query (String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy) 
 2 Added in API level 1
 3 Query the given table, returning a Cursor over the result set.
 4 
 5 Parameters
 6 table  The table name to compile the query against. 
 7 columns  A list of which columns to return. Passing null will return all columns, which is discouraged to prevent reading data from storage that isn't going to be used. 
 8 selection  A filter declaring which rows to return, formatted as an SQL WHERE clause (excluding the WHERE itself). Passing null will return all rows for the given table. 
 9 selectionArgs  You may include ?s in selection, which will be replaced by the values from selectionArgs, in order that they appear in the selection. The values will be bound as Strings. 
10 groupBy  A filter declaring how to group rows, formatted as an SQL GROUP BY clause (excluding the GROUP BY itself). Passing null will cause the rows to not be grouped. 
11 having  A filter declare which row groups to include in the cursor, if row grouping is being used, formatted as an SQL HAVING clause (excluding the HAVING itself). Passing null will cause all row groups to be included, and is required when row grouping is not being used. 
12 orderBy  How to order the rows, formatted as an SQL ORDER BY clause (excluding the ORDER BY itself). Passing null will use the default sort order, which may be unordered. 
13 
14 Returns
15 A Cursor object, which is positioned before the first entry. Note that Cursors are not synchronized, see the documentation for more details.
16 See Also
17 Cursor 

 1 package com.example.demosql;
 2 
 3 import android.content.Context;
 4 import android.database.sqlite.SQLiteDatabase;
 5 import android.database.sqlite.SQLiteOpenHelper;
 6 
 7 public class DBHelper extends SQLiteOpenHelper{
 8 
 9     public DBHelper(Context context)
10     {
11         super(context, "mydata.db", null, 1);
12     }
13     @Override
14     public void onCreate(SQLiteDatabase db) {
15         //建立数据表
16         db.execSQL("create table mydatabase(_id integer primary key,name text,age text)");
17 
18     }
19 
20     @Override
21     public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
22         //版本更新
23         if(newVersion>oldVersion)
24         {
25             db.execSQL("drop table if exists mydatabase");
26         }
27         
28     }
29 
30 }

  1 package com.example.demosql;
  2 
  3 import android.app.Activity;
  4 import android.content.ContentValues;
  5 import android.database.Cursor;
  6 import android.database.sqlite.SQLiteDatabase;
  7 import android.os.Bundle;
  8 
  9 public class MainActivity extends Activity {
 10     
 11     private DBHelper dbhelper ;
 12     private SQLiteDatabase sqldb;
 13     private int num = 1;
 14     @Override
 15     protected void onCreate(Bundle savedInstanceState) {
 16         super.onCreate(savedInstanceState);
 17         setContentView(R.layout.activity_main);
 18         dbhelper = new DBHelper(this);
 19         //添加数据
 20         addData("Alice");
 21         selectData();
 22         System.out.println("---------------------------");
 23         //修改数据
 24         updataData();
 25         selectData();
 26         System.out.println("---------------------------");
 27         //删除数据
 28         deleteData();
 29         selectData();
 30         System.out.println("---------------------------");
 31         //添加数据
 32         addData("Tom");
 33         addData("Mark");
 34         selectData();
 35         clearData();
 36         selectData();
 37         System.out.println("---------------------------");
 38 
 39     }
 40     
 41     //添加数据
 42     public void addData(String name)
 43     {
 44         sqldb = dbhelper.getWritableDatabase();
 45         ContentValues value = new ContentValues();
 46         value.put("name", name);
 47         value.put("age", "18");
 48         //第一个参数是要修改的数据库名称，第三个参数是数据源（数组格式）,返回成功添加的数据条数
 49         int num = (int) sqldb.insert("mydatabase", null, value);
 50         if(num>0)
 51             System.out.println("添加数据成功");
 52         //关闭数据库
 53         sqldb.close();
 54     }
 55     //删除数据
 56     public void deleteData()
 57     {
 58         sqldb = dbhelper.getWritableDatabase();
 59         String[] name = {"Alice"};
 60         //第一个参数是修改的数据库名称，第二个参数是删除数据要符合的条件，第三个参数是修改的数据源（数组格式）,对应第二个参数?的位置
 61         int num = sqldb.delete("mydatabase", "name=?", name);
 62         if(num>0)
 63             System.out.println("删除数据成功");
 64         //关闭数据库
 65         sqldb.close();
 66     }
 67     //修改数据
 68     public void updataData()
 69     {
 70         sqldb = dbhelper.getWritableDatabase();
 71         ContentValues value = new ContentValues();
 72         value.put("name", "Alice");
 73         value.put("age", "28");
 74         String names[] = {"Alice"};
 75         //第一个参数为要修改的数据库名称，第二个参数为数据源，第三个参数为修改要符合的条件，第四个参数是对应第三个参数？位置的值
 76         int num = sqldb.update("mydatabase", value, "name=?",names );
 77         if(num>0)
 78             System.out.println("修改数据成功");
 79         sqldb.close();
 80     }
 81     //查询数据
 82     public void selectData()
 83     {
 84         //注意，查询不用修改 getReadableDatabase（）而不是getWritableDatabase（）
 85         sqldb = dbhelper.getReadableDatabase();
 86         String columns[] = {"name","age"};
 87         //第一个参数为数据库名称，第二个参数为要查询的字段，
 88         Cursor cursor = sqldb.query("mydatabase", columns, null, null, null, null, null);
 89         System.out.println("此时的数据为：");
 90         while(cursor.moveToNext())
 91         {
 92             //参数为第几个字段，注意第0个字段不是id 
 93             String name = cursor.getString(0);
 94             String age = cursor.getString(1);
 95             System.out.println("name:"+name+",age:"+age);
 96         }
 97         sqldb.close();
 98     }
 99     //清空数据库
100     public void clearData()
101     {
102         sqldb = dbhelper.getWritableDatabase();
103         sqldb.delete("mydatabase", null, null);
104         System.out.println("执行清空数据库操作");
105         sqldb.close();
106     }
107 }