一,说明
保存有一定结构的数据 文件类型 .db 保存路径:data/data/packageName/databases/xxx.db 默认其他应用不能访问,当前应用可通过Content Provider(四大组件之一)提供其他应用操作 随着应用卸载删除数据
SQLite介绍
优点:
安装文件小,最小只有几百K,Android中已安装 支持多种操作系统:Android,WP,IOS,Windows,Linux 支持多语言:Java,PHP,C#等 处理速度快 一个数据库就是一个.db文件(本质上.db的后缀可以不指定)
数据类型
int/integer:整数 float/double:小数 char/varchar/text:文本 blob:文件 date/datetime:日期/日期时间
使用示范
# 建表
create table employee(
_id integer primary key autoincrement,
name varchar ,
salary double ,
birthday date
)
# 增
insert into employee(name,salary,birthday) values ('Tom' ,12000.30 ,'1992-08-06' );
# 删
delete from employee where _id=3 ;
# 查
select * from employee;
# 改
update employee set salary=salary+500 where _id=1 ;
SQLite建表
创建表时可以不用指定字段类型,但除varchar外最好指定类型 主键名称建议用_id 基本语法与MySQL一样
adb操作:
adb shell
进入系统根目录cd data/data/…/databases
: 进入包含数据库文件的文件夹下sqlite3 contacts2.db
: 使用sqlite3命令连接指定的数据库文件, 进入连接模式.help
: 查看命令列表.tables
: 查看所有表的列表执行insert/delete/update/select
语句 .exit
: 退出数据库连接模式Ctrl + C : 直接退出shell模式
二,API
SQLiteOpenHelper:数据库操作的抽象帮助类
SQLiteOpenHelper(Context context,String name,CursorFactory factory,int version )
abstract void onCreate(SQLiteDatabase db)
abstract void onUpgrade()
SQLiteDatabase getReadableDatabase()
SQLiteDatabase :代表与数据库连接
SQLiteDatabase openDatabase(String path,CursorFactory factory,int flages)
long insert()
int delete()
Cursor query()
int update()
void execSql(Strng sql)
void beginTransaction()
void setTransactionSuccessful()
void endTransaction()
Cursor:包含查询记录的结果集对象
int getCount()
boolean moveToNext()
int getColumnIndex(String columnName)
Xxx getXxx(int columnIndex)
三,测试源码
DBHelper
public class DBHelper extends SQLiteOpenHelper {
public DBHelper (Context context, int version) {
super (context, "quantai.db" , null , version);
}
/**
* 什么时候调用? 数据库文件被创建时调用
*
* 什么时候数据库文件创建? 数据库文件不存在且尝试连接数据库时
*
* 在此方法中做什么? 1.创建表 2.表的初始化
*/
@Override
public void onCreate (SQLiteDatabase db) {
Log.i("TAG" , "onCreate" );
String sql="create table person(_id integer primary key autoincrement,name varchar,age int)" ;
db.execSQL(sql);
db.execSQL("insert into person(name,age) values('Tom','12')" );
db.execSQL("insert into person(name,age) values('Jane','15')" );
}
@Override
public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion) {
Log.i("TAG" , "onUpgrade" );
}
}
SQActivity
public class SQActivity extends Activity {
@Override
protected void onCreate (Bundle savedInstanceState) {
super .onCreate(savedInstanceState);
setContentView(R.layout.activity_sq);
}
/**
* 创建数据库
*
* @param v clicked view
*/
public void createDB (View v) {
DBHelper dbHelper = new DBHelper(this , 1 );
dbHelper.getReadableDatabase();
Toast.makeText(this , "create database" , Toast.LENGTH_SHORT).show();
}
/**
* 更新数据库
*
* @param v clicked view
*/
public void updateDB (View v) {
DBHelper dbHelper = new DBHelper(this , 2 );
dbHelper.getReadableDatabase();
}
/**
* 增
*
* @param v clicked view
*/
public void insert (View v) {
DBHelper dbHelper = new DBHelper(this , 2 );
SQLiteDatabase database = dbHelper.getReadableDatabase();
ContentValues values = new ContentValues();
values.put("name" , "quan" );
values.put("age" , 10 );
long id = database.insert("person" , null , values);
database.close();
Toast.makeText(this , "insert id= " + id, Toast.LENGTH_LONG).show();
}
/**
* 改
*
* @param v clicked view
*/
public void update (View v) {
DBHelper dbHelper = new DBHelper(this , 2 );
SQLiteDatabase database = dbHelper.getReadableDatabase();
ContentValues values = new ContentValues();
values.put("name" , "jack" );
values.put("age" , 99 );
int updateCount = database.update("person" , values, "_id=?" ,
new String[]{"4" });
database.close();
Toast.makeText(this , "updataCount: " + updateCount, Toast.LENGTH_LONG)
.show();
}
/**
* 删
*
* @param v clicked view
*/
public void delete (View v) {
DBHelper dbHelper = new DBHelper(this , 2 );
SQLiteDatabase database = dbHelper.getReadableDatabase();
int deleteCount = database.delete("person" , "_id=?" , new String[]{"4" });
database.close();
Toast.makeText(this , "deleteCount: " + deleteCount, Toast.LENGTH_LONG).show();
}
/**
* 查询
*
* @param v clicked view
*/
public void query (View v) {
DBHelper dbHelper = new DBHelper(this , 2 );
SQLiteDatabase database = dbHelper.getReadableDatabase();
Cursor cursor = database.query("person" , null , null , null , null , null , null );
int count = cursor.getCount();
while (cursor.moveToNext()) {
int id = cursor.getInt(0 );
String name = cursor.getString(1 );
int age = cursor.getInt(2 );
Log.e("TAG" , "id:" + id + "-name:" + name + "-age:" + age);
}
cursor.close();
database.close();
Toast.makeText(this , "QueryCount: " + count, Toast.LENGTH_SHORT).show();
}
/**
* 事务
* update person set age=13 where _id=1
* update person set age=13 where _id=1
* <p/>
* 一个功能中对数据库进行多个操作,要么都成功,要么都失败
* step1:开始事务(获取连接后)
* step2:设置事务成功(正常执行完成后)
* step3:结束事务(finally)
*
* @param v clicked view
*/
public void trans (View v) {
SQLiteDatabase database = null ;
try {
DBHelper dbHelper = new DBHelper(this , 2 );
database = dbHelper.getReadableDatabase();
database.beginTransaction();
ContentValues values = new ContentValues();
values.put("age" , 13 );
int updateCount = database.update("person" , values, "_id=?" , new String[]{"1" });
boolean flag = true ;
if (true ) {
throw new RuntimeException("a error occur!" );
}
values = new ContentValues();
values.put("age" , 15 );
int updateCount2 = database.update("person" , values, "_id=?" , new String[]{"3" });
database.setTransactionSuccessful();
Toast.makeText(this , "updataCount: " + updateCount, Toast.LENGTH_LONG).show();
} catch (RuntimeException e) {
e.printStackTrace();
Toast.makeText(SQActivity.this , "An exception occur!" , Toast.LENGTH_SHORT);
} finally {
if (database != null ) {
database.endTransaction();
database.close();
}
}
}
}