SQLite 数据库存储

SQLite是一款轻量级的关系型数据库，运算速度快，占用资源少，通常只需几百KB的内存就够了。SQLite不仅支持标准的SQL语法，还遵循数据库的ACID事务。

创建数据库

Android为了让使用者更加方便地管理数据库，提供了一个SQLiteOpenHelper帮助类。使用的时候只需要继承这个类就可以了。

 public class MyDatabaseHelper extends SQLiteOpenHelper {
    public static final String CREATE_BOOK="create table Book("
            +"id integer primary key autoincrement,"
            +"author text,"
            +"price real,"
            +"pages integer,"
            +"name text)";
    public static final String CREATE_CATEGORY="create table Category("
            +"id integer primary key autoincrement,"
            +"category_name text,"
            +"category_code integer)";
    private Context mContext;
    public MyDatabaseHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
        mContext=context;
    }
    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_BOOK);
        db.execSQL(CREATE_CATEGORY);
        Toast.makeText(mContext,"Create succeed",Toast.LENGTH_SHORT).show();
    }
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    }
}

重写SQLiteOpenHelper中参数少的构造方法。这个构造方法中接受4个参数，第一个是Context。第二个参数是数据库名，创建数据库时使用的就是这里制定的名称。第三个参数允许我们在查询数据时返回一个自定义的Cusor，一般传入null。第四个参数表示当前数据库的版本号，可以对数据库进行升级操作。

构建出SQLiteOpenHelper的实例后，再调用它的getReadableDatabase()方法或getWritableDatabase()方法就能创建数据库了，数据库文件存在/data/data/<package name>/databases/目录下。此时，重写的onCreate()方法也会得到执行，所以可以在这里去处理一些创建表的逻辑。

public class MainActivity extends AppCompatActivity implements View.OnClickListener {
    private Button createDatabase;
    private Button addData;
    private Button updateData;
    private Button deleteData;
    private Button queryData;
    private MyDatabaseHelper dbHelper;
    public SQLiteDatabase db;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        dbHelper=new MyDatabaseHelper(this,"BookStore.db",null,1);
        db=dbHelper.getWritableDatabase();
        createDatabase= (Button) findViewById(R.id.create_database);
        addData=(Button)findViewById(R.id.add_data);
        updateData=(Button)findViewById(R.id.update_data);
        deleteData=(Button)findViewById(R.id.delete_data);
        queryData=(Button)findViewById(R.id.query_data);
        createDatabase.setOnClickListener(this);
        addData.setOnClickListener(this);
        updateData.setOnClickListener(this);
        deleteData.setOnClickListener(this);
        queryData.setOnClickListener(this);
    }

    @Override
    public void onClick(View v) {
        switch (v.getId()){
            case R.id.create_database:
                dbHelper.getWritableDatabase();
                break;
            case R.id.add_data:
                ContentValues values=new ContentValues();
//                开始组装第1条数据
                values.put("name","The Da Vince Code");
                values.put("author","Dan Brown");
                values.put("pages",454);
                values.put("price",16.96);
                db.insert("Book",null,values);
                values.clear();
//                开始组装第2条数据
                values.put("name","The Lost Symbol");
                values.put("author","Dan Brown");
                values.put("pages",510);
                values.put("price",19.95);
                db.insert("Book",null,values);
                break;
            case R.id.update_data:
                ContentValues values1=new ContentValues();
                values1.put("price",16);
                db.update("Book",values1,"name=?",new String[]{"The Da Vince Code"});
                break;
            case R.id.delete_data:
                db.delete("Book","pages>?",new String[]{"500"});
                break;
            case R.id.query_data:
//                查询表中所以数据
                Cursor cursor=db.query("Book",null,null,null,null,null,null);
                if (cursor.moveToFirst()) {
                    do {
//                        遍历Cusor对象，取出数据并打印
                        String name=cursor.getString(cursor.getColumnIndex("name"));
                        String author=cursor.getString(cursor.getColumnIndex("author"));
                        int pages=cursor.getInt(cursor.getColumnIndex("pages"));
                        double price=cursor.getDouble(cursor.getColumnIndex("price"));
                    }while (cursor.moveToNext());
                }
                cursor.close();
        }
    }
}

SQlite数据类型简单，integer表示整型，real表示浮点数，text表示文本类型，blob表示二进制类型。

primary key将对应列设为主键，autoincrement关键字表示是自增长的。

升级数据库

在onUpgrade()方法中执行两条DROP语句，如果发现数据库中已经存在Book或Category表就将这两张表删除，然后再调用onCreate()方法重新创建。

@Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("drop table if exists Book");
        db.execSQL("drop table if exists Category");
        onCreate(db);

要让onUpgrade()方法执行，SQLiteOpenHelper的构造方法里的第四个参数表示数据库版本号，之前传入的是1，现在只要改个比1大的数就可以让onUpgrade()方法执行。

1	dbHelper=new MyDatabaseHelper(this,"BookStore.db",null,2);

添加数据

insert()方法接受3个参数，第一个参数是表名。第二个参数用于再未指定添加数据的情况下给某些可为空的自动列赋值null，一般用不到这个功能，直接传入null即可。第三个参数是一个ContentValues对象，它提供了一系列的put()方法重载，用于向ContentValues中添加数据，只需将表中的每个列名以及相应的待添加数据传入即可。

                SQLiteDatabase db=db=dbHelper.getWritableDatabase();
                ContentValues values=new ContentValues();
//                开始组装第1条数据
                values.put("name","The Da Vince Code");
                values.put("author","Dan Brown");
                values.put("pages",454);
                values.put("price",16.96);
                db.insert("Book",null,values);
                values.clear();
//                开始组装第2条数据
                values.put("name","The Lost Symbol");
                values.put("author","Dan Brown"); 
                values.put("pages",510);
                values.put("price",19.95);
                db.insert("Book",null,values);

更新数据

update()方法接受4个参数，第一个参数是表名，第二个参数是ContentValues对象，要把更新数据在这里组装进去。第三、四个参数用于约束更新某一行或某几行中的数据，不指定就默认更新所有行。

1
2
3

ContentValues values1=new ContentValues();
values1.put("price",16);
db.update("Book",values1,"name=?",new String[]{"The Da Vince Code"});

删除数据

delete()方法接受三个参数，第一个参数是表名，第二、三个参数用于约束删除某一行或几行的数据，不指定默认删除所有行。

1	db.delete("Book","pages>?",new String[]{"500"});

查询数据

query()方法参数非常复查，最短的一个重载方法也需要传入7个参数。第一个参数是表名；第二个参数用于指定查询哪几列，如果不指定默认查询所有列；第三、四个参数用于约束查询某一行或几行的数据，不指定则默认查询所有行的数据；第五个参数用于指定需要去group by的列，不指定则表示不对查询结果进行group by操作；第六个参数用于对group by之后的数据进行进一步的过滤，不指定则不进行过滤。第七个参数用于指定查询结果的排序方式，不指定则使用默认的排序方式。

query()方法参数	对应SQL部分	描述
table	from table_name	指定查询的表名
columns	select column1,column2	指定查询的列明
selection	where column=value	指定where的约束条件
selectionsArgs	-	为where中的占位符提供具体的值
groupBy	group by column	指定需要group by的列
having	having column=value	对group by后的进一步约束
orderBy	order by column1,column2	指定查询结果的排序方式

调用query()方法会返回一个Cursor对象，查询到的所有数据都将从这个对象中取出。

   Cursor cursor=db.query("Book",null,null,null,null,null,null);
                if (cursor.moveToFirst()) {
                    do {
//                        遍历Cusor对象，取出数据并打印
                        String name=cursor.getString(cursor.getColumnIndex("name"));
                        String author=cursor.getString(cursor.getColumnIndex("author"));
                        int pages=cursor.getInt(cursor.getColumnIndex("pages"));
                        double price=cursor.getDouble(cursor.getColumnIndex("price"));        
                    }while (cursor.moveToNext());
                }
                cursor.close();

使用SQL操作数据库

虽然Android提供了非常方便的API用于操作数据库，不过总会有人更喜欢直接使用SQL来操作数据库。

添加数据的方法：

1
2

db.execSQL("insert into Book(name,author,pages,price) values(?,?,?,?)",new String[]{"The Da Vinci Code","Dan Brown","454","16.96"});
db.execSQL("insert into Book(name,author,pages,price) values(?,?,?,?)",new String[]{"The Lost Symbol","Dan Brown","510","16.95"});

更新数据的方法如下：

1	db.exexSQL("update Book set price = ? where name = ?",new String[]{"10.99","The Da Vinci Code"});

删除数据的方法如下：

1	db.execSQL("delete from Book where pages > ?",new String[]{"500"});

查询数据

1	db.rawQuery("select * from Book",null);

可以看到，除了查询数据的时候调用的是SQLiteDatabase的rawQuery()方法，其他操作都是调用execSQL()方法。

使用LitePal操作数据库

LitePal简介

LitePal是一款开源的Android数据库框架，采用对象关系映射(ORM)的模式，并将我们平时开发最常用到的一些数据库功能进行了封装，使得不用编写SQL语句就可以完成各种建表和增删改查得操作。

配置LitePal

Edit your build.gradle file and add below dependency:

1
2
3

dependencies {
    compile 'org.litepal.android:core:1.5.1'
}

Configure litepal.xml

在assets目录下新建一个litepal.xml文件。编辑litepal.xml文件中得内容，如下所示：

<?xml version="1.0" encoding="utf-8"?>
<litepal>
    <!--
    	Define the database name of your application. 
    	By default each database name should be end with .db. 
    	If you didn't name your database end with .db, 
    	LitePal would plus the suffix automatically for you.
    	For example:    
    	<dbname value="demo" />
    -->
    <dbname value="demo" />

    <!--
    	Define the version of your database. Each time you want 
    	to upgrade your database, the version tag would helps.
    	Modify the models you defined in the mapping tag, and just 
    	make the version value plus one, the upgrade of database
    	will be processed automatically without concern.
			For example:    
    	<version value="1" />
    -->
    <version value="1" />

    <!--
    	Define your models in the list with mapping tag, LitePal will
    	create tables for each mapping class. The supported fields
    	defined in models will be mapped into columns.
    	For example:    
    	<list>
    		<mapping class="com.test.model.Reader" />
    		<mapping class="com.test.model.Magazine" />
    	</list>
    -->
    <list>
    </list>
    
    <!--
        Define where the .db file should be. "internal" means the .db file
        will be stored in the database folder of internal storage which no
        one can access. "external" means the .db file will be stored in the
        path to the directory on the primary external storage device where
        the application can place persistent files it owns which everyone
        can access. "internal" will act as default.
        For example:
        <storage value="external" />
    -->
    
</litepal>

其中，<dbname>标签用于指定数据库名；<version>标签用于指定数据库版本号，每次更新数据库只需要增加version值；<list>标签用于指定所有得映射模型。

Configure LitePalApplication

You don’t want to pass the Context param all the time. To makes the APIs simple, just configure the LitePalApplication in AndroidManifest.xml as below:

<manifest>
    <application
        android:name="org.litepal.LitePalApplication"
        ...
    >
        ...
    </application>
</manifest>

Of course you may have your own Application and has already configured here, like:

<manifest>
    <application
        android:name="com.example.MyOwnApplication"
        ...
    >
        ...
    </application>
</manifest>

That’s OK. LitePal can still live with that. Just call LitePal.initialize(context) in your own Application:

public class MyOwnApplication extends AnotherApplication {

    @Override
    public void onCreate() {
        super.onCreate();
        LitePal.initialize(this);
    }
    ...
}

Make sure to call this method as early as you can. In the onCreate() method of Application will be fine. And always remember to use the application context as parameter. Do not use any instance of activity or service as parameter, or memory leaks might happen.

Get Started

After setup, you can experience the powerful function now.

Create tables

Define the models first. For example you have two models, Album and Song. The models can be defined as below:

public class Album extends DataSupport {
	
    @Column(unique = true, defaultValue = "unknown")
    private String name;
	
    private float price;
	
    private byte[] cover;
	
    private List<Song> songs = new ArrayList<Song>();

    // generated getters and setters.
    ...
}

public class Song extends DataSupport {
	
    @Column(nullable = false)
    private String name;
	
    private int duration;
	
    @Column(ignore = true)
    private String uselessField;
	
    private Album album;

    // generated getters and setters.
    ...
}

Then add these models into the mapping list in litepal.xml:

<list>
    <mapping class="org.litepal.litepalsample.model.Album" />
    <mapping class="org.litepal.litepalsample.model.Song" />
</list>

OK! The tables will be generated next time you operate database. For example, gets the SQLiteDatabase with following codes:

1	SQLiteDatabase db = LitePal.getDatabase();

Now the tables will be generated automatically with SQLs like this:

CREATE TABLE album (
	id integer primary key autoincrement,
	name text unique default 'unknown',
	price real,
	cover blob
);

CREATE TABLE song (
	id integer primary key autoincrement,
	name text not null,
	duration integer,
	album_id integer
);

Upgrade tables

Upgrade tables in LitePal is extremely easy. Just modify your models anyway you want:

public class Album extends DataSupport {
	
    @Column(unique = true, defaultValue = "unknown")
    private String name;
	
    @Column(ignore = true)
    private float price;
	
    private byte[] cover;
	
    private Date releaseDate;
	
    private List<Song> songs = new ArrayList<Song>();

    // generated getters and setters.
    ...
}

A releaseDate field was added and price field was annotated to ignore. Then increase the version number in litepal.xml:

<!--
    Define the version of your database. Each time you want 
    to upgrade your database, the version tag would helps.
    Modify the models you defined in the mapping tag, and just 
    make the version value plus one, the upgrade of database
    will be processed automatically without concern.
    For example:    
    <version value="1" ></version>
-->
<version value="2" ></version>

The tables will be upgraded next time you operate database. A releasedate column will be added into album table and the original price column will be removed. All the data in album table except those removed columns will be retained.

But there are some upgrading conditions that LitePal can’t handle and all data in the upgrading table will be cleaned:

Add a field which annotated as unique = true.
Change a field’s annotation into unique = true.
Change a field’s annotation into nullable = false.

Be careful of the above conditions which will cause losing data.

Save data

The saving API is quite object oriented. Each model which inherits from DataSupport would have the save() method for free:

Album album = new Album();
album.setName("album");
album.setPrice(10.99f);
album.setCover(getCoverImageBytes());
album.save();
Song song1 = new Song();
song1.setName("song1");
song1.setDuration(320);
song1.setAlbum(album);
song1.save();
Song song2 = new Song();
song2.setName("song2");
song2.setDuration(356);
song2.setAlbum(album);
song2.save();

This will insert album, song1 and song2 into database with associations.

Update data

The simplest way, use save() method to update a record found by find():

1
2
3

Album albumToUpdate = DataSupport.find(Album.class, 1);
albumToUpdate.setPrice(20.99f); // raise the price
albumToUpdate.save();

Each model which inherits from DataSupport would also have update() and updateAll() method. You can update a single record with a specified id:

1
2
3

Album albumToUpdate = new Album();
albumToUpdate.setPrice(20.99f); // raise the price
albumToUpdate.update(id);

Or you can update multiple records with a where condition:

1
2
3

Album albumToUpdate = new Album();
albumToUpdate.setPrice(20.99f); // raise the price
albumToUpdate.updateAll("name = ?", "album");

Delete data

You can delete a single record using the static delete() method in DataSupport:

1	DataSupport.delete(Song.class, id);

Or delete multiple records using the static deleteAll() method in DataSupport:

1	DataSupport.deleteAll(Song.class, "duration > ?" , "350");

Query data

Find a single record from song table with specified id:

1	Song song = DataSupport.find(Song.class, id);

Find all records from song table:

1	List<Song> allSongs = DataSupport.findAll(Song.class);

Constructing complex query with fluent query:

1	List<Song> songs = DataSupport.where("name like ?", "song%").order("duration").find(Song.class);

Async operations

Every database operation is on main thread by default. If your operation might spent a long time, for example saving or querying tons of records. You may want to use async operations.

LitePal support async operations on all crud methods. If you want to find all records from song table on a background thread, use codes like this:

DataSupport.findAllAsync(Song.class).listen(new FindMultiCallback() {
    @Override
    public <T> void onFinish(List<T> t) {
        List<Song> allSongs = (List<Song>) t;
    }
});

Just use findAllAsync() instead of findAll(), and append a listen() method, the finding result will be callback to onFinish() method once it finished.

Abd saving asynchronously is quite the same:

Album album = new Album();
album.setName("album");
album.setPrice(10.99f);
album.setCover(getCoverImageBytes());
album.saveAsync().listen(new SaveCallback() {
    @Override
    public void onFinish(boolean success) {

    }
});

Just use saveAsync() instead of save(). It will save Album into database on a background, and the saving result will be callback to onFinish() method.

Multiple databases

If your app needs multiple databases, LitePal support it completely. You can create as many databases as you want at runtime. For example:

LitePalDB litePalDB = new LitePalDB("demo2", 1);
litePalDB.addClassName(Singer.class.getName());
litePalDB.addClassName(Album.class.getName());
litePalDB.addClassName(Song.class.getName());
LitePal.use(litePalDB);

This will create a demo2 database with singer, album and song tables.

If you just want to create a new database but with same configuration as litepal.xml, you can do it with:

1 2	LitePalDB litePalDB = LitePalDB.fromDefault("newdb"); LitePal.use(litePalDB);

You can always switch back to default database with:

1	LitePal.useDefault();

And you can delete any database by specified database name:

1	LitePal.deleteDatabase("newdb");