数据存储在开发中是使用最频繁的,在这里主要介绍Android平台中实现数据存储的5种方式,分别是:
1 使用SharedPreferences存储数据
2 文件存储数据
3 SQLite数据库存储数据
4 使用ContentProvider存储数据
5 网络存储数据
本文介绍SQLite存储方法,
在Android平台上,集成了一个嵌入式关系型数据库-SQLite,SQLite3支持null,integer,real,text和blob数据类型,虽然支持的数据类型只有5种,但是实际上也接受varchar,char,decimal等数据类型,只是在运算和保持时会转化成相对应的5种数据类型,SQLite可以把任何类型存储在任意字段上,但是定义为integer primary key的字段只能是64为整数,并且SQLite支持大部分标准的sql语句
第一步我写一个类继承 SQLiteOpenHelper来实现数据库的操作
public class DBOpenHelper extends SQLiteOpenHelper {
private static final String DATABASE_NAME="mydb.db";
private static final int DATABASE_VERSION = 1;
private static final String DICTIONARY_TABLE_NAME = "dictionary";
private static final String DICTIONARY_TABLE_CREATE = "CREATE TABLE " + DICTIONARY_TABLE_NAME + " ( KEY_WORD TEXT, KEY_DEFINITION TEXT);";
private static final String PERSON_TABLE_CREATE = "create table person(id integer primary key autoincrement,name varchar(64),address varchar(64),sex varchar(4))";
public DBOpenHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
/**
* 当数据库创建的时候,是第一次被执行,完成对数据库的表的创建
* //支持的数据类型:整型数据,字符串类型,日期类型,二进制的数据类型,
* 可以说无数据类型
*/
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(DICTIONARY_TABLE_CREATE);
db.execSQL(PERSON_TABLE_CREATE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
String sql = "alter table person add sex varchar(8)";
db.execSQL(sql);
}
}
数据的添删改查分别可以通过2种途径来实现
数据的添加
1.使用insert方法
1
2
3
|
ContentValues cv =
new
ContentValues();
//实例化一个ContentValues用来装载待插入的数据cv.put("username","Jack Johnson");//添加用户名
cv.put(
"password"
,
"iLovePopMusic"
);
//添加密码
db.insert(
"user"
,
null
,cv);
//执行插入操作
|
2.使用execSQL方式来实现
1
2
|
String sql = "insert into user(username,password) values (
'Jack Johnson'
,
'iLovePopMuisc'
);
//插入操作的SQL语句
db.execSQL(sql);
//执行SQL语句
|
数据的删除
同样有2种方式可以实现
1
2
3
|
String whereClause =
"username=?"
;
//删除的条件
String[] whereArgs = {
"Jack Johnson"
};
//删除的条件参数
db.delete(
"user"
,whereClause,whereArgs);
//执行删除
|
使用execSQL方式的实现
1
2
|
String sql =
"delete from user where username='Jack Johnson'"
;
//删除操作的SQL语句
db.execSQL(sql);
//执行删除操作
|
数据修改
同上,仍是2种方式
1
2
3
4
5
|
ContentValues cv =
new
ContentValues();
//实例化ContentValues
cv.put(
"password"
,
"iHatePopMusic"
);
//添加要更改的字段及内容
String whereClause =
"username=?"
;
//修改条件
String[] whereArgs = {
"Jack Johnson"
};
//修改条件的参数
db.update(
"user"
,cv,whereClause,whereArgs);
//执行修改
|
使用execSQL方式的实现
1
2
|
String sql =
"update [user] set password = 'iHatePopMusic' where username='Jack Johnson'"
;
//修改的SQL语句
db.execSQL(sql);
//执行修改
|
数据查询
数据查询相对前面几种方法就复杂一些了,因为查询会带有很多条件
通过query实现查询的
public Cursor query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit)
各参数说明:
- table:表名称
- colums:列名称数组
- selection:条件子句,相当于where
- selectionArgs:条件语句的参数数组
- groupBy:分组
- having:分组条件
- orderBy:排序类
- limit:分页查询的限制
- Cursor:返回值,相当于结果集ResultSet
针对游标(Cursor)也提供了不少方法
方法名称 | 方法描述 |
---|---|
getCount() | 总记录条数 |
isFirst() | 判断是否第一条记录 |
isLast() | 判断是否最后一条记录 |
moveToFirst() | 移动到第一条记录 |
moveToLast() | 移动到最后一条记录 |
move(int offset) | 移动到指定的记录 |
moveToNext() | 移动到吓一条记录 |
moveToPrevious() | 移动到上一条记录 |
getColumnIndex(String columnName) | 获得指定列索引的int类型值 |
实现代码
1
2
3
4
5
6
7
8
|
Cursor c = db.query(
"user"
,
null
,
null
,
null
,
null
,
null
,
null
);
//查询并获得游标
if
(c.moveToFirst()){
//判断游标是否为空
for
(
int
i=
0
;i<c.getCount();i++){
c.move(i);
//移动到指定记录
String username = c.getString(c.getColumnIndex(
"username"
);
String password = c.getString(c.getColumnIndex(
"password"
));
}
}
|
通过rawQuery实现的带参数查询
1
2
3
4
|
Cursor c = db.rawQuery(
"select * from user where username=?"
,
new
Stirng[]{
"Jack Johnson"
});
if
(cursor.moveToFirst()) {
String password = c.getString(c.getColumnIndex(
"password"
));
}
|
方式一
package com.example.demo.datastore.dao;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import com.example.demo.datastore.db.PersonOpenHelper;
import com.example.demo.datastore.service.PersonService;
public class PersonDao implements PersonService {
private PersonOpenHelper helper = null;
public PersonDao(Context context) {
helper = new PersonOpenHelper(context);
}
@Override
public boolean addPerson(Object[] params) {
boolean flag = false;
//实现对数据库的添加删除和修改查询的功能
SQLiteDatabase database = null;
try {
String sql = "insert into person(name,address,sex) values(?,?,?)";
database = helper.getWritableDatabase();//实现对数据库的写的操作
database.execSQL(sql, params);
flag = true;
} catch (Exception e) {
e.printStackTrace();
}finally{
if(database!=null){
database.close();
}
}
return flag;
}
@Override
public boolean deletePerson(Object[] params) {
boolean flag = false;
SQLiteDatabase database = null;
try {
String sql = "delete from person where id = ? ";
database = helper.getWritableDatabase();
database.execSQL(sql, params);
flag = true;
} catch (Exception e) {
}finally{
if(database!=null){
database.close();
}
}
return flag;
}
@Override
public boolean updatePerson(Object[] params) {
boolean flag = false;
SQLiteDatabase database = null;
try {
String sql = "update person set name = ? ,address = ?, sex = ? where id = ? ";
database = helper.getWritableDatabase();
database.execSQL(sql, params);
flag = true;
} catch (Exception e) {
}finally{
if(database!=null){
database.close();
}
}
return flag;
}
@Override
public Map<String, String> viewPerson(String[] selectionArgs) {
Map<String,String> map = new HashMap<String, String>();
SQLiteDatabase database = null;
try {
String sql = "select * from person where id = ? ";
database = helper.getReadableDatabase();
Cursor cursor = database.rawQuery(sql, selectionArgs);
//获得数据库的列的个数
int colums = cursor.getColumnCount();
while(cursor.moveToNext()){
for(int i=0;i<colums;i++){
String cols_name = cursor.getColumnName(i);
String cols_value = cursor.getString(cursor.getColumnIndex(cols_name));
if(cols_value==null){
cols_value = "";
}
map.put(cols_name, cols_value);
}
}
} catch (Exception e) {
// TODO: handle exception
}finally{
if(database!=null){
database.close();
}
}
return map;
}
@Override
public List<Map<String, String>> listPersonMaps(String[] selectionArgs) {
// TODO Auto-generated method stub
List<Map<String,String>> list = new ArrayList<Map<String,String>>();
String sql = "select * from person ";
SQLiteDatabase database = null;
try {
database = helper.getReadableDatabase();
Cursor cursor = database.rawQuery(sql, selectionArgs);
int colums = cursor.getColumnCount();
while(cursor.moveToNext()){
Map<String,String> map = new HashMap<String, String>();
for(int i=0;i<colums;i++){
String cols_name = cursor.getColumnName(i);
String cols_value = cursor.getString(cursor.getColumnIndex(cols_name));
if(cols_value==null){
cols_value="";
}
map.put(cols_name, cols_value);
}
list.add(map);
}
} catch (Exception e) {
// TODO: handle exception
}finally{
if(database!=null){
database.close();
}
}
return list;
}
}
方式二
package com.example.demo.datastore.dao;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import com.example.demo.datastore.db.PersonOpenHelper;
import com.example.demo.datastore.service.PersonService2;
public class PersonDao2 implements PersonService2 {
private PersonOpenHelper helper = null;
public PersonDao2(Context context) {
helper = new PersonOpenHelper(context);
}
@Override
public boolean addPerson(ContentValues values) {
boolean flag = false;
SQLiteDatabase database = null;
long id = -1;
try {
database = helper.getWritableDatabase();
id = database.insert("person", null, values);
flag = (id != -1 ? true : false);
} catch (Exception e) {
// TODO: handle exception
} finally {
if (database != null) {
database.close();
}
}
return flag;
}
@Override
public boolean deletePerson(String whereClause, String[] whereArgs) {
boolean flag = false;
SQLiteDatabase database = null;
int count = 0;
try {
database = helper.getWritableDatabase();
count = database.delete("person", whereClause, whereArgs);
flag = (count > 0 ? true : false);
} catch (Exception e) {
} finally {
if (database != null) {
database.close();
}
}
return flag;
}
@Override
public boolean updatePerson(ContentValues values, String whereClause,
String[] whereArgs) {
boolean flag = false;
SQLiteDatabase database = null;
int count = 0;// 影响数据库的行数
try {
database = helper.getWritableDatabase();
count = database.update("person", values, whereClause, whereArgs);
flag = (count > 0 ? true : false);
} catch (Exception e) {
// TODO: handle exception
} finally {
if (database != null) {
database.close();
}
}
return flag;
}
@Override
public Map<String, String> viewPerson(String selection,
String[] selectionArgs) {
// select 返回的列的名称(投影查询) from
SQLiteDatabase database = null;
Cursor cursor = null;
Map<String, String> map = new HashMap<String, String>();
try {
database = helper.getReadableDatabase();
cursor = database.query(true, "person", null, selection,
selectionArgs, null, null, null, null);
int cols_len = cursor.getColumnCount();
while (cursor.moveToNext()) {
for (int i = 0; i < cols_len; i++) {
String cols_name = cursor.getColumnName(i);
String cols_value = cursor.getString(cursor
.getColumnIndex(cols_name));
if (cols_value == null) {
cols_value = "";
}
map.put(cols_name, cols_value);
}
}
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
} finally {
if (database != null) {
database.close();
}
}
return map;
}
@Override
public List<Map<String, String>> listPersonMaps(String selection,
String[] selectionArgs) {
// TODO Auto-generated method stub
List<Map<String, String>> list = new ArrayList<Map<String, String>>();
SQLiteDatabase database = null;
Cursor cursor = null;
try {
database = helper.getReadableDatabase();
cursor = database.query(false, "person", null, selection,
selectionArgs, null, null, null, null);
int cols_len = cursor.getColumnCount();
while (cursor.moveToNext()) {
Map<String, String> map = new HashMap<String, String>();
for (int i = 0; i < cols_len; i++) {
String cols_name = cursor.getColumnName(i);
String cols_value = cursor.getString(cursor
.getColumnIndex(cols_name));
if (cols_value == null) {
cols_value = "";
}
map.put(cols_name, cols_value);
}
list.add(map);
}
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
} finally {
if (database != null) {
database.close();
}
}
return list;
}
}