文章目录
一、SQLite介绍
SQLite是一个小型的内嵌于Android的数据库；我们不需要引入驱动即可访问它；
SQLite可视化工具：SQLite Expert Professional 3；
二、Android访问SQLite
主要步骤：
(1)创建某个类继承SQLiteOpenHelper,并重写
-- 带Context参数的构造函数;//用来创建数据库
-- onCreate(SQLiteDatabase db);//在创建数据库时调用
-- onUpgrade(SQLiteDatabase db,int old,int newversion); //数据库版本更改时调用
(2)创建数据库：SQLiteDatabase db = helper.getWritableDatabase();
(3)SQL语句：db.execSQL(String sql);
具体模板代码如下：
package org.xiazdong.db.util;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
public class DatabaseHelper extends SQLiteOpenHelper{
private static final String TAG = "DatabaseHelper";
private static int VERSION = 1;
public DatabaseHelper(Context context) {
super(context, "test.db", null, VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("create table 语句");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Log.i(TAG, "版本更新...");
}
}
当创建数据库时，database存放在/data/data/package/databases 目录中；
1.打开数据库
SQLiteDatabase db = helper.getWritableDatabase();
SQLiteDatabase db = helper.getReadableDatabase();//此函数内部其实也调用了getWritableDatabase()函数，即调用了此函数，也可以写数据
2.insert语句
(1)db.execSQL("insert into person(name,age) values(?,?)",new Object[]{"xiazdong",20});
(2)
ContentValues values = new ContentValues();
values.put("name","xiazdong");
values.put("age",20);
db.insert("tablename",null,values);
3.delete语句
(1)db.execSQL("delete from person where id=?",newObject[]{id});
(2)db.delete("tablename","id=?",new String[]{id+""});
4.update语句
(1)
db.exeSQL("update person set age=? where name=?",new Object[]{30,"xiazdong"});
(2)
ContentValues values = new ContentValues();
values.put("age",30);/*set子句*/
db.update("tablename",values,"name=?"/*where子句*/,new String[]{"xiazdong"}/*where子句参数*/);
5.query语句
(1)
Cursor cursor = db.rawQuery("select * from person where name=?",new Object[]{"xiazdong"});
while(cursor.moveToNext()){
//int index = cursor.getColumnIndex(String name);//根据name获得索引
//String name = cursor.getString(int index); //根据索引获得值
String name = cursor.getString(cursor.getColumnIndex("name"));
}
(2)
Cursor cursor = db.query("tablename",null/*表示select * */,"name=?"/*where语句*/,new String[]{"xiazdong"},null/*group
by 语句*/,null/*having 语句*/,null/*order by语句*/,null/*limit 语句*/);
by 语句*/,null/*having 语句*/,null/*order by语句*/,null/*limit 语句*/);
6.分页语句
Cursor cursor = db.rawQuery("select * from person limit ?,?",new Object[]{5,5}); //第一个5表示跳过5条记录，下一个5为查询结果的记录个数
while(cursor.moveToNext()){
String name = cursor.getString(cursor.getColumnIndex("name"));
}
7.获得记录个数语句
(1)
Cursor cursor = db.rawQuery("select count(*) from person", null);
cursor.moveToFirst();
int count = cursor.getInt(0);
(2)
db.query("person",new String[]{"count(*)"},null,null,null,null,null,null);
三、事务操作
模板代码如下：
db.beginTransaction();
try{
//事务操作
db.setTransactionSuccessful();//此句必须要有，不然db.endTransaction()默认为回滚
}
finally{
db.endTransaction();
}
Android入门：SQLite